MySQL 开发随笔

MySQL 服务器配置

MySQL 8 设置默认字符集

为了让 MySQL 8 支持存储 Emoji 表情包(占用 4 个字节),可以在 MySQL 服务端的 /etc/my.conf 配置文件中指定默认使用的字符集为 utfmb4,如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[client]
# 设置客户端的默认字符集
default-character-set=utf8mb4

[mysql]
# 设置命令行客户端的默认字符集
default-character-set=utf8mb4

[mysqld]
# 设置每个客户端在连接时默认使用的字符集,如何客户端在连接时明确指定了字符集,则客户端指定的字符集具有较高优先级
init_connect = 'SET NAMES utf8mb4'
# 设置服务端的默认字符集
character-set-server = utf8mb4
# 设置服务端的默认排序规则
collation-server=utf8mb4_unicode_ci

如何设置事务隔离级别

事务隔离级别的介绍

事务表示多个数据操作组成一个完整的事务单元,在这个事务内的所有数据操作要么同时成功,要么同时失败。数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响,避免各种并发问题。一个事务与其他事务隔离的程度称为 隔离级别。SQL 标准中规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但数据库的并发性能越差。为了解决事务并发问题(脏读、不可重复读、幻读),主流的关系型数据库都会提供以下四种事务隔离级别。

  • 读未提交(Read Uncommitted)
    • 在该隔离级别,所有事务都可以看到其他未提交事务所做的改变
    • 该隔离级别是最低的隔离级别,虽然拥有超高的并发处理能力及很低的系统开销,但很少用于实际应用。
    • 该隔离级别只能解决第一类更新丢失问题,不能解决脏读、不可重复读、幻读的问题。
  • 读已提交(Read Committed)
    • 这是大多数数据库系统的默认隔离级别(但不是 MySQL 默认的),例如 Oracle 数据库。
    • 该隔离级别满足了隔离的简单定义:一个事务只能看见已提交事务所做的改变
    • 该隔离级别可以解决脏读问题,但会出现不可重复读、幻读问题。
  • 可重复读(Repeatable Read)
    • 这是 MySQL 的默认事务隔离级别,它可以确保在整个事务过程中,对同一条数据的读取结果是相同的,不管其他事务是否在对共享数据进行更新,也不管其他事务更新提交与否
    • 该隔离级别可以解决脏读、不可重复读的问题,但会出现幻读问题。
  • 串行化(Serializable)
    • 这是最高的事务隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决脏读、不可重复读、幻读、第一类更新丢失、第二类更新丢失问题
    • 该隔离级别可以解决所有的事务并发问题,但可能导致大量的超时现象和锁竞争,通常数据库不会用这个隔离级别,可用其他的方案来解决这些问题,例如乐观锁和悲观锁。

事务隔离级别的案例

上图中是典型的第二类丢失更新问题,后果非常严重。当数据库隔离级别为读已提交(Read Committed)及以下隔离级别时,会出现不可重复读的现象。从上面的表格可以看出,当事务隔离级别设置为可重复读(Repeatable Read)时,可以避免不可重复读的现象出现。

事务隔离级别的设置

1
2
3
4
5
6
7
8
-- 设置下一次事务的隔离级别
set transaction isolation level repeatable read;

-- 设置当前会话的事务隔离级别
set session transaction isolation level repeatable read;

-- 设置全局的事务隔离级别
set global session transaction isolation level repeatable read;

事务隔离级别的总结

上述四种事务隔离级别会产生的并发问题如下(YES 表示存在对应的问题,NO 表示不存在对应的问题):

各种关系型数据库对事务隔离级别的支持程度如下(YES 表示支持,NO 表示不支持):

OracleMySQL
Read UncommittedNOYES
Read CommittedYES (默认)YES
Repeatable ReadNOYES (默认)
SerializableYESYES

提示

  • 在 Spring 框架中,事务隔离级别可以通过 @Transactional 注解中的 isolation 属性定义。
  • MySQL 的默认隔离级别是可重复读 (Repeatable Read),而 Oracle 的默认隔离级别是读已提交 (Read Committed)。

数据库表如何删除重复数据

面试题

线上数据库表存在重复数据,如何删除同一字段下的所有重复数据,或者仅仅留下一条数据。

  • 创建表,并插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `student` (`id`, `name`, `age`) VALUES ('1', 'cat', 22);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('2', 'dog', 33);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('3', 'fish', 44);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('4', 'cat', 55);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('5', 'dog', 11);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('6', 'cat', 45);
  • 查看 name 字段的重复数据
1
2
3
SELECT NAME, COUNT(1) FROM student GROUP BY NAME HAVING COUNT(1) > 1;
-- 或者
SELECT NAME, COUNT(NAME) FROM student GROUP BY NAME HAVING COUNT(NAME) > 1;
1
2
3
4
5
+------+-------------+
| NAME | COUNT(NAME) |
+------+-------------+
| cat | 3 |
| dog | 2 |
1
2
-- 或者
SELECT * FROM student WHERE NAME IN ( SELECT NAME FROM student GROUP BY NAME HAVING COUNT(1) > 1 );
1
2
3
4
5
6
7
8
9
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | cat | 22 |
| 2 | dog | 33 |
| 4 | cat | 55 |
| 5 | dog | 11 |
| 6 | cat | 45 |
+----+------+------+
  • 删除 name 字段下的所有重复数据,只要有重复都删除掉
1
2
3
DELETE FROM student WHERE NAME IN (
SELECT t.NAME FROM ( SELECT NAME FROM student GROUP BY NAME HAVING COUNT(1) > 1 ) t
)
1
2
-- 查询删除重复数据后的结果
select * from student;
1
2
3
4
5
+----+------+------+
| id | name | age |
+----+------+------+
| 3 | fish | 44 |
+----+------+------+
  • 删除 name 字段下的重复数据,仅仅留下一条数据
1
2
3
4
-- 此方法要求主键(ID)为自增长
DELETE FROM student WHERE id NOT IN (
SELECT t.id FROM ( SELECT MIN( id ) AS id FROM student GROUP BY `name` ) t
)
1
2
-- 查询删除重复数据后的结果
select * from student;
1
2
3
4
5
6
7
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | cat | 22 |
| 2 | dog | 33 |
| 3 | fish | 44 |
+----+------+------+

优化千万级数据的分页查询

优化的关键

SELECT 语句中,可以使用 LIMIT 子句来约束结果集中的行数。LIMIT 子句接受一个或两个参数,两个参数的值都必须为零或者正整数,具体的参数说明如下:

  • offset:指定要返回的第一行数据的偏移量。第一行数据的偏移量为 0,而不是 1。
  • count:指定要返回的最大行数。
1
SELECT column1, column2, ... FROM table LIMIT offset, count;

假设 emp 表中有 600 万条的数据,使用以下 SQL 进行分页查询,大概需要花费 18 秒才能查询得到结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT SQL_NO_CACHE
a.empno,
a.ename,
a.job,
a.sal,
b.deptno,
b.dname
FROM
emp a
LEFT JOIN dept b
ON a.deptno = b.deptno
ORDER BY a.id
LIMIT 4950000, 30;

为什么上面的查询会耗费那么多时间呢?其实这是因为 LIMIT 后面的偏移量太大导致的。比如 LIMIT 4950000, 30,这等同于数据库要扫描出 4950030 条数据,然后再丢弃前面的 49500000 条数据,最后返回剩下的 30 条数据给用户,显然这种做法是不合理的。MySQL 的分页操作通常会使用 LIMIT 加上偏移量的办法来实现,可能还会加上合适的 ORDER BY 子句。当偏移量非常大的时候,它会导致 MySQL 扫描大量不需要的行,然后再抛弃掉,这在生产环境存在一定的性能隐患。在阿里巴巴的开发手册中,提到以下的分页查询优化方案:

优化的方案

方案一,使用覆盖索引 + 子查询

如果数据库表中有 ID 字段,并且在上面建立了索引(主键索引),那么可以先在索引树中找到开始位置的 ID 值,然后再根据找到的 ID 值查询数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
a.empno,
a.ename,
a.job,
a.sal,
b.deptno,
b.dname
FROM
emp a
LEFT JOIN dept b
ON a.deptno = b.deptno
WHERE a.id >= (SELECT id FROM emp ORDER BY id LIMIT 4950000, 1)
ORDER BY a.id
LIMIT 30;

方案二,起始位置重定义

记住上次分页查询结果的主键位置,避免使用 LIMIT 的偏移量 offset。比如,记住上次分页查询结果的最后一条数据的 ID 是 4950000,然后就直接跳过 4950000,从 4950001 开始扫描表进行查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
a.empno,
a.ename,
a.job,
a.sal,
b.deptno,
b.dname
FROM
emp a
LEFT JOIN dept b
ON a.deptno = b.deptno
WHERE a.id > 4950000
ORDER BY a.id
LIMIT 30;

方案三,服务降级不可用

  • 这是阿里巴巴推荐的解决方案,首先配置 LIMIT 的偏移量和获取行数的最大值,超过这个最大值,就返回空数据。因为按照业务来说,超过这个最大值后,用户已经不是在分页了,而是在刷数据。如果确认要查询数据,应该输入合适的查询条件来缩小范围,而不是一页一页地分页查询。

  • 当用户发出请求后,如果 offset 大于某个数值就直接返回一个 4xx 的错误,避免黑客攻击或者刷单行为。毕竟,正常人很少翻查 10 页以后的内容。另外,该有的服务降级、限流也应该考虑进去。比如,用户利用多线程工具调用接口,在短时间内执行 5000 次调用,那么就可以简单地使用计数器进行判断,并反馈给用户调用过于频繁,最后直接抛弃用户的请求。

如何往拥有千万级数据量的表添加索引

在千万级数据量的表中添加索引,操作步骤为:创建新表 + 创建索引 + 导入旧数据 + 废弃旧表。简而言之,” 腾笼换鸟”,稳定性压倒一切,保守一点不出错即可。

  • (1) 先创建一张跟原表 A 数据结构相同的新表 B。
  • (2) 在新表 B 上建立新的索引。
  • (3) 将原表 A 的数据迁移到新表 B。
  • (4) 将原表 A 改为别的表名,并将新表 B 改为原表的表名 A。

特别注意

在给表添加索引的时候,是会对表加锁的(表锁)。如果不谨慎操作,可能会出现严重的生产事故。比如,在添加索引的过程中,如果发生了数据修改(如 DELETE、UPDATE),则可能会导致客户端读取到不一致或错误的数据。因此,对应大数据量的表,需要使用 "腾笼换鸟" 的方式来添加索引。