MySQL 开发随笔

MySQL 服务器配置

MySQL 8 设置默认字符集

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

[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）时，可以避免不可重复读的现象出现。

事务隔离级别的设置

-- 设置下一次事务的隔离级别
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 表示不支持）：

	Oracle	MySQL
Read Uncommitted	NO	YES
Read Committed	YES (默认)	YES
Repeatable Read	NO	YES (默认)
Serializable	YES	YES

提示

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

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

面试题

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

• 创建表，并插入数据

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 字段的重复数据

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;

+------+-------------+
| NAME | COUNT(NAME) |
+------+-------------+
| cat  |           3 |
| dog  |           2 |

-- 或者
SELECT * FROM student WHERE NAME IN ( SELECT NAME FROM student GROUP BY NAME HAVING COUNT(1) > 1 );

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | cat  |   22 |
|  2 | dog  |   33 |
|  4 | cat  |   55 |
|  5 | dog  |   11 |
|  6 | cat  |   45 |
+----+------+------+

• 删除 name 字段下的所有重复数据，只要有重复都删除掉

DELETE FROM student WHERE NAME IN (
    SELECT t.NAME FROM ( SELECT NAME FROM student GROUP BY NAME HAVING COUNT(1) > 1 ) t
)

-- 查询删除重复数据后的结果
select * from student;

+----+------+------+
| id | name | age  |
+----+------+------+
|  3 | fish |   44 |
+----+------+------+

• 删除 name 字段下的重复数据，仅仅留下一条数据

-- 此方法要求主键（ID）为自增长
DELETE FROM student WHERE id NOT IN ( 
    SELECT t.id FROM ( SELECT MIN( id ) AS id FROM student GROUP BY `name` ) t
)

-- 查询删除重复数据后的结果
select * from student;

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | cat  |   22 |
|  2 | dog  |   33 |
|  3 | fish |   44 |
+----+------+------+

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

优化的关键

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

• offset：指定要返回的第一行数据的偏移量。第一行数据的偏移量为 0，而不是 1。
• count：指定要返回的最大行数。

SELECT column1, column2, ... FROM table LIMIT offset, count;

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

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 值查询数据。

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 开始扫描表进行查询。

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），则可能会导致客户端读取到不一致或错误的数据。因此，对应大数据量的表，需要使用 "腾笼换鸟" 的方式来添加索引。