冷热分离
冷数据与热数据
冷数据指不经常访问，但是需要长期保存的数据
可以按照两个常见维度来区分冷数据与热数据：
- 时间维度：按照时间来区分冷热数据，例如，对于订单系统，1 年前的订单可以认为是冷数据
- 访问频率维度：按照内容的访问频率来区分冷热数据，例如，对于帖子系统，可以认为很长一段时间内，浏览量很低的文章是冷数据
当然这仅仅按照这两个维度有些时候不太适用，还是要根据业务实际情况来决定
为什么要冷热分离
如果将冷热数据均存放在一个 DB 中，随着数据的增多，检索速率会下降，用户体验差
如果引入冷热分离，将冷数据和热数据分开存储，能保证大部分用户的使用体验
冷热分离的优缺点
优点：
- 热数据的查询性能提高，大部分用户的使用体验较好
- 降低存储成本：热数据可以存储在 SSD 中，冷数据可以存储在 HDD 中
缺点：
- 不稳定因素增加
- 统计整体数据时，速度较慢
如何实现
业务层
业务层代码可以写一个后台任务，定期判断存在的冷数据，将其迁移到冷库，并从热库删除
监听 binlog
可以通过监听 binlog 来分析存在的冷数据，将其迁移到冷库，并从热库删除
深度分页
概念
来看一下这个 SQL 语句：
SELECT * FROM user LIMIT 1000000, 10
这种查询就叫做深度分页
由于这类查询在分页的时候无法利用索引，要想获取第 1000000 ～ 1000010 条数据，就需要先 获取前 1000010 数据，然后再跳过前 1000000 条记录，再返回第 1000000 ～ 1000010 条数据
可以看到效率很低，那么在无法避免深度分页的情况下，应该如何优化？
如何优化
优化方式很简单：延迟关联
- 使用子查询先获取第 1000000 ～ 1000010 条数据的 id
- 然后再使用内连接
即：
select *
from user,
(select id from user limit 1000000, 10) tmp
where user.id = tmp.id;
优化的点在于：
- 深度分页时，获取的只是 id，而不是所有数据
- 使用到了「覆盖索引」，避免回表查询（这也是 select id 的原因所在）
当然这里用到了子查询，需要创建一张临时表，也会产生一定的性能损失
普通索引和唯一索引，应该如何选择？
查询过程
- 普通索引，查到满足条件的第一个记录后，继续查找下一个记录，直到第一个不满足条件的记录
- 唯一索引，由于索引唯一性，查到第一个满足条件的记录后，停止检索
实际上，二者的性能差距是很小的，因为 InnoDB 读取数据的基本单位是「页」
也就是说，虽然普通索引看起来查找记录的次数较多，但是由于一次性读取了一整张页（包括许多记录），性能不会比唯一索引差
更新过程
在讨论更新过程的性能差异，需要引入一个概念：change buffer
当需要更新一个 数据页：
- 如果数据页在内存中就直接更新
- 如果不在内存中，需要将该数据页从磁盘读取到内存，然后在内存中更新（回写操作可以在后台执行）
可以发现，如果数据页不在磁盘中，就会涉及到 随机读 IO，这对性能的影响是很大的
change buffer
那么，引入 change buffer，在使用 普通索引 的条件下，就可以解决这个问题
来看看引入 change buffer 的更新数据页的操作：
- 如果数据页在内存中就直接更新
- 如果不在内存中，在不影响数据一致性的前提下，InnoDB 会将这些更新操作 缓存在 change buffer 中。
也就是说，使用 change buffer，避免了大量随机读 IO，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。
change buffer 是 可以持久化 的数据。在内存中有拷贝，也会被写入到磁盘上
change buffer 用的是 buffer pool 里的内存，change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
使用 change buffer，什么时候更新磁盘的数据呢？
我们将 change buffer 中的操作应用到原数据页上，得到最新结果的过程，称为 merge
merge 的时机：
- 后台线程定期 merge
- 数据库正常关闭，merge
- 访问该数据页的时候，会触发 merge
change buffer 一开始是在内存的，如果掉电，会不会导致 change buffer 丢失？
不会，分两种情况讨论：
change buffer 的操作会被记录到 redo log 里
如果 redo log 已经落盘，那么重启可以根据 redo log 来恢复 change buffer
如果 redo log 还没来得及落盘，重启后，会因为 redo log 没有落盘而回滚事务，丢失了 change buffer 也没关系
change buffer 的操作会被记录到 redo log 里，有了 redo log，为什么还要将 change buffer 持久化到磁盘？
其实我也不太清楚，但感觉有点类似 Linux 的 Swap 机制？
如果执行更新操作以后，一直没有 merge，那么 change buffer 的剩余空间会越来越少
于是可以将 change buffer 持久化到磁盘，为新的更新操作流出空间，就好像 Linux 的 Swap 一样
为什么唯一索引的更新不能使用 change buffer？
唯一索引在更新数据时，需要 先判断更新的数据会不会与现有数据冲突，如果待更新数据页不在内存，会 不可避免的读取磁盘到内存 来判断
既然数据都已经读到内存了，也就没有使用 change buffer 的必要了
什么时候用 change buffer？
在一个数据页做 merge 之前，change buffer 记录的变更越多，收益就越大。
对于 写多读少 的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
反过来，假设一个业务的更新模式是 写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。
这样 随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。
因此是否使用 change buffer，还需要结合业务需求
与 redo log 对比
你可能会问：redo log 的 WAL 机制，不也是减少随机 IO 吗，还要 change buffer 干嘛
实际上，使用 redo log，可以将事务执行期间涉及到的 随机写 IO 转换成 顺序写 IO，真正的随机 IO 被推迟到后台线程进行
但使用 redo log 不能减少随机读 IO：增删改一个数据还是要先从磁盘读取到内存
而对于非唯一索引，在有 change buffer 的前提下，增删改就可以不从磁盘读取到内存，而是直接记录到 change buffer 中，减少的是 随机读 IO
用一句话简单总结：redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。
总结
将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。
change buffer 因为减少了随机磁盘读取，所以对更新性能的提升很明显。
由于唯一索引无法使用到 change buffer 来优化，因此 唯一索引的更新性能可能会不如普通索引
如果业务允许的情况下，尽量使用普通索引，以提高读性能
当然，使用了 change buffer，在某些场景下，不但不会提升性能，还会导致性能的下降，此时应该禁用 change buffer
如何给字符串建索引
直接建立索引
如果单个字符串的长度不是很长，直接建立索引，方便
前缀索引
如果要考虑索引占用的空间，可以考虑建立前缀索引
在建立前缀索引时，要考虑 区分度，可以给定一个可以接受的区分度，然后确定索引字符串的长度
-- 看看有多少个不一样的字段
select count(distinct email) as L from SUser;
-- 看看 L4、L5 与 L 的差距，选择一个合适的
select
count(distinct left(email,4)) as L4,
count(distinct left(email,5)) as L5,
count(distinct left(email,6)) as L6,
count(distinct left(email,7)) as L7,
from SUser;
但是建立前缀索引，就无法用到覆盖索引的优化了，必然回表查询
哈希
可以给表加一个字段，存储字符串的哈希值
- 在存储数据时，记录下这个字符串的哈希值
- 在取出数据时，根据哈希值来取，然后 在判断字段是否完全一致
这种方式适合前缀区分度较低的场景，但是会引入一个额外字段，占用一定的存储空间
倒序存储
例如身份证，前缀的区分度低，就可以将 ID 倒序存储，然后建立前缀索引
redo log 设置得太小会怎么样
这里引用 12 | 为什么我的 MySQL 会“抖”一下？ 的一条评论：
redo log 是关系型数据库的核心啊，保证了 ACID 里的 D。所以 redo log 是牵一发而动全身的操作
按照老师说的当内存数据页跟磁盘数据页不一致的时候，把内存页称为’脏页’。如果 redo log 设置得太小，redo log 写满.那么会涉及到哪些操作呢，我认为是以下几点:
- 把相对应的数据页中的脏页持久化到磁盘，checkpoint 往前推
- 由于 redo log 还记录了 undo 的变化，undo log buffer 也要持久化进 undo log
- 当 innodb_flush_log_at_trx_commit 设置为非 1，还要把内存里的 redo log 持久化到磁盘上
- redo log 还记录了 change buffer 的改变，那么还要把 change buffer purge 到 idb 以及 merge change buffer.merge 生成的数据页也是脏页，也要持久化到磁盘
上述 4 种操作，都是占用系统 I/O，影响 DML，如果操作频繁，会导致’抖’得向现在我们过冬一样。但是对于 select 操作来说，查询时间相对会更快。因为系统脏页变少了，不用去淘汰脏页，直接复用干净页即可。还有就是对于宕机恢复，速度也更快，因为 checkpoint 很接近 LSN，恢复的数据页相对较少
所以要控制刷脏的频率，频率快了，影响 DML I/O，频率慢了，会导致读操作耗时长。
因此，如果 redo log 设置太小，redo 很容易写满，会导致频繁刷盘，系统锁死，触发 checkpoint 推进，导致写操作卡住。由于主机 IO 能力很强，checkpoint 推进会很快完成，卡住的写操作又很快可以执行。循环往复，现象就是 写操作每隔一小段时间执行就会变慢几秒。
合适的 redo log 大小非常重要
此外，还有一个
innodb_io_capacity选项，这个选项是告诉 InnoDB ：你的磁盘的 IO 能力，建议设置为磁盘的 IOPSInnoDB 会根据 innodb_io_capacity 来控制刷盘的速率，如果设置得太小，会导致写入速率很慢，表现就是：IO 没满，但是脏页很多，来不及写入
order by 是怎么排序的？
现在有一张 user 表：
create table user
(
id int auto_increment
primary key,
name varchar(10) not null,
sex char null,
phoneNum varchar(20) not null,
email varchar(128) null,
age int null,
);
且有主键索引、idx_sex
注意，这里的 idx_sex 只是为了实验而创建
由于 sex 的区分度太低，在实际应用不要根据 sex 创建索引
执行以下 sql 语句：
explain select name, sex, age from user where sex = 'F' order by age;
输出:
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | user | NULL | ref | idx_sex | idx_sex | 5 | const | 6 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+---------------------------------------+
在 Extra 字段，提示 Using filesort，这是什么意思呢？
全字段排序
在对没有建立索引的字段排序时，就会出现 Using filesort
MySQL 会为每一个线程分配一个缓冲区用于存放待排序的数据，成为 sort_buffer
对于上面的 SQL 语句，具体来说，MySQL 是这样做的：
- 初始化 sort_buffer
- 根据 where 条件，利用 idx_sex，定位到第一个满足 sex = ‘F’ 的节点，得到主键 id
- 根据主键 id，利用主键索引，取出 name、sex、age，将这个节点的行数据存放到 sort_buffer 中
- 定位到下一个节点，得到主键 id
- 重复 3、4，直到遍历完所有节点
- 对
sort_buffer中的数据根据 age 字段来做 快速排序 - 返回数据给客户端
sort_buffer 是有大小限制的（sort_buffer_size），如果待排序的数据在 sort_buffer 无法完全存下，MySQL 会：
- 将剩余数据存放到 磁盘 上的若干临时文件中
- 分别对这些文件的内容做 归并排序
- 递归地合并文件内容，直到合并成一个有序大文件
- 合并 sort_buffer 和文件内容，返回给客户端
rowid 排序
可以发现：如果待排序的数据很大，会使用到 磁盘 辅助排序，性能很差
为了减少存储在 sort_buffer 的数据量，可以使用 rowid 排序
与全字段排序不同，rowid 排序仅会在 sort_buffer 存储：主键 id、排序字段
对于之前的 SQL 语句，使用 rowid 排序的过程如下：
- 初始化 sort_buffer
- 根据 where 条件，利用 idx_sex，定位到第一个满足 sex = ‘F’ 的节点，得到主键 id
- 根据主键 id，利用主键索引，取出 id、age，将这个节点的行数据存放到 sort_buffer 中
- 定位到下一个节点，得到主键 id
- 重复 3、4，直到遍历完所有节点
- 对 sort_buffer 的数据进行快速排序
- 遍历 sort_buffer 的数据，根据主键 id，回表查询 name、sex 字段
- 返回结果给客户端
虽然 rowid 排序在相同的 sort_buffer 下，可以在对更多行排序的情况下，不创建临时文件，降低磁盘 IO
但是与全字段排序相比，rowid 多了一次回表查询
因此，MySQL 不会优先选择 rowid 排序
当单行数据大小超过 max_length_for_sort_data ，MySQL 才会使用 rowid 排序
避免在 sort_buffer 排序
为什么要在 sort_buffer 排序呢？是因为数据是无序的
这看起来是一句废话，但也表明了：如果数据本身就是有序的，那自然就不需要在 sort_buffer 排序
我们可以为 sex、age 字段建立索引
create index idx_sex_age on user(sex, age);
创建索引后，order by 的执行流程如下：
- 执行器发现有 idx_sex_age 联合索引，于是判断不需要使用 filesort
- 利用 idx_sex_age 定位到第一个 sex = ‘F’ 的节点，得到主键 id
- 根据主键 id 回表查询 name
- 由于 idx_sex_age 能保证在 sex 相同的情况下，age 有序，因此无需排序，重复 2、3 即可
- 返回结果给客户端
再来看看 explain 的结果：
mysql> explain select name, sex, age from user where sex = 'F' order by age;
+----+-------------+-------+------------+------+---------------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+-------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | ref | idx_sex,idx_sex_age | idx_sex_age | 5 | const | 6 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
可以看到：没有 Using filesort，只有 Using index condition 了
当然，可以创建联合索引 idx_sex_age_name，走覆盖索引，避免一次回表查询
mysql> explain select name, sex, age from user where sex = 'F' order by age;
+----+-------------+-------+------------+------+--------------------------------------+------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------------------+------------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | user | NULL | ref | idx_sex,idx_sex_age,idx_sex_age_name | idx_sex_age_name | 5 | const | 6 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+------+--------------------------------------+------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
总结
- 建议在待排序字段建立索引，避免 filesort
- 如果无法避免使用到 filesort，那么可以：
- 适当扩大 sort_buffer 的大小，避免在文件排序
- 适当提高 max_length_for_sort_data 的大小，避免使用 rowid 排序，多一次回表查询
基于临时表的排序机制
引例
现在有一张表：
create table words
(
id int auto_increment
primary key,
word varchar(64) null
);
-- 只有主键索引
并向其插入了 10k 行数据
有一个业务需求：随机获取 words 中的三个单词，返回给用户
一个简单的实现方式如下：
select word from words order by rand() limit 3;
那么这句话实际上做了什么呢？
内存临时表
执行 explain 查询计划：
mysql> explain
-> select word from test0.words order by rand() limit 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | words | NULL | ALL | NULL | NULL | NULL | NULL | 9980 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
可以发现，这句话使用到了 临时表 辅助查询，并且 需要排序，具体执行过程如下：
- 创建临时表（memory 存储引擎），包含两个字段：
随机值，单词 - 遍历 words 表，计算出每个 word 的 rand 值，插入到临时表中
- 依据
随机值对临时表进行排序（排序过程可以参考 order by 是怎么排序的 ） - 取出临时表的前三行数据，返回 word 给客户端
上述过程一共会扫描 20003 行数据（10000 + 10000 + 3），效率低，并且，随着 words 表的行数增加，执行速度会更慢
磁盘临时表
所有的临时表都是内存临时表吗？
并不是，如果内存临时表存放不下待排序的数据，会将内存临时表转化为磁盘临时表
这个大小是由 tmp_table_size 参数控制的，默认为 16M
对磁盘临时表的排序，规则与 order by 是怎么排序的 一致
Topk 问题（优先队列排序算法）
执行以下 SQL 语句：
set tmp_table_size=1024; -- 将当前会话的内存临时表设置为 1024 字节
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';
/* 执行语句 */
select word from words order by rand() limit 3;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE` \G;
正常来说，rand_val + word 一行要占 6 + 8 = 14 字节，那么 10000 行数据应该会占用 140000 字节的空间，超过了内存临时表的大小，应该会使用磁盘临时表，做归并排序
事实真的如此吗？
OPTIMIZER_TRACE 有关 filesort 的输出如下：
并没有使用磁盘临时表
事实上，排序方式的选择还 与 limit 参数有关
当 limit 后的数据（对于上面的示例，就是 3 行 rand_val + word）如果可以存放在内存临时表，那么 MySQL 会将其优化 TopK 问题，体现在 OPTIMIZER_TRACE 的输出上，就是这两行：
关于 TopK 问题，可以看看 这道 leetcode 题目
为什么要使用优先队列优化呢？
因为 limit 后，只要元素的前 n 个有序即可，利用优先队列解决，空间复杂度低，减少排序所需空间，一定程度上避免使用磁盘辅助排序
随机排序的实现方式
无论是使用内存临时表还是磁盘临时表，扫描的数据都太多了，性能不佳，如何优化？
方案一
-- 获取 min_id、max_id
select max(id),min(id) into @M,@N from t ;
-- 产生一个介于 min_id、max_id 之间的随机值
set @X= floor((@M-@N+1)*rand() + @N);
-- 获取随机值对应的行数据
select * from t where id >= @X limit 3;
这个方案效率很高，只扫描了 3 行数据，但是存在「空洞问题」
如果 id 不连续，例如 1、2、6、7、8，那么选到 6、7、8 的概率会高很多，即概率分布不均匀
要想解决这个问题，可以：
- 保证 id 连续自增
- 删除数据时，采用逻辑删除
方案二
-- 获取行数 c
select count(*) into @C from t;
-- 生成介于 1～c 之间的随机数
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
-- 获取对应行数据
select * from t limit @Y1，1;
select * from t limit @Y2，1;
select * from t limit @Y3，1;
总扫描行数为：C+(Y1+1)+(Y2+1)+(Y3+1)，可以利用「大表分页查询思想」进一步优化为：
-- 获取行数 c
select count(*) into @C from t;
-- 生成介于 1～c 之间的随机数
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
-- 获取对应行数据
select * from t limit @Y1，1; -- 应用层可以保存 id1
select * from t where id > id1 limit @Y2 - @Y1，1; -- 应用层可以保存 id2
select * from t where id > id2 limit @Y3 - @Y2，1;
总扫描行数为：C+Y3
为什么 limit n order by <字段> 会有重复值？
前面提到了：order by 的排序规则与 limit 的参数有关：
- 如果 limit 后的数据 可以 存放在内存临时表，采用优先队列（堆）排序
- 如果 limit 后的数据 不可以 存放在内存临时表，采用归并排序
例如这个场景：
- 第一次分页查询：
order by rand() limit n, a，无法存放在内存临时表，采用归并排序 - 第二次分页查询：
order by rand() limit n + a, a，可以存放在内存临时表（最后一页，数据较少），采用优先队列（堆）排序
就有可能出现两次分页查询包含重复值的情况，本质上还是因为 优先队列排序不是稳定的
如果要避免这种情况，可以在排序依据加上唯一字段（例如主键 id）
总结
- 在查询使用到临时表时，如果还要排序，并且排序字段没有建立索引，会触发临时表排序机制
- 内存临时表有大小限制，超过后，会换成磁盘临时表，采用归并排序
- 基于临时表排序时，如果有 limit，并且 limit 后的数据 可以 存放在内存临时表，采用优先队列（堆）排序
- 如果要避免
limit n order by <字段> 会有重复值，可以在排序依据加上唯一字段（例如主键 id）
参考资料
如何临时“提高” MySQL 的性能
- 如果连接数过多导致 MySQL 无法连接，可以关闭一些空闲连接
- 如果是连接请求 QPS 过高，可以临时关闭 MySQL 的连接鉴权
- 如果是单个 SQL 语句的 QPS 过高，可以重写该 SQL 语句为
select 1，将 QPS 降低为 0
详细内容参考 MySQL 有哪些“饮鸩止渴”提高性能的方法？
误删数据如何及时补救？
分情况讨论：
误删了某一行的数据
这种情况可以使用 FlashBack 来做数据恢复
FlashBack 做数据恢复的原理是通过修改 binlog，然后拿到主库重放，要求 binlog 的行格式为 row
对于 delete 语句，在 binlog event 的类型为 delete_rows event ，可以修改为 write_rows event
注意：恢复数据时，应该先恢复到临时实例，确定没问题再同步到主库
误删了一整张表
删除了整张表，使用 binlog 来恢复数据就不可能了，因为仅仅记录了一个 drop
这种情况，要求定期做全量备份，并且实时备份 binlog
例如：
- 最近的一个备份点为今天的 0:00
- 取出这个全量备份，创建一个临时库
- 取出今天 0:00 到现在的所有 binlog 备份
- 使用 binlog 做增量恢复（去除误删除的语句）
全量同步可能比较耗费时间，如果业务不允许这么长恢复时间，可以 搭建一个延迟复制的从库
例如，一个从库与主库的延迟为 1h，那么在这 1h 内，只要发现了有误删除，就可以使用延迟复制的从库快速恢复数据：
- 发现误删除，stop slave
- 取出最近 1h 内的 binlog，跳过误删除语句，做数据恢复
可以看出：恢复速度与主从延迟时间相关，延迟时间越长，恢复时间越长，但允许了更长的时间来发现误删除现象
无论如何，这些都是补救措施，我们需要优先思考的是如何避免，而不是如何补救
- 开发的 权限控制
- 确保 SQL 语句的 where 条件
- SQL 审计
- …
修改生产的数据，或者添加索引优化，都要先写好四个脚本：备份脚本、执行脚本、验证脚本和回滚脚本。备份脚本是对需要变更的数据备份到一张表中，固定需要操作的数据行，以便误操作或业务要求进行回滚；执行脚本就是对数据变更的脚本，为防 Update 错数据，一般连备份表进行 Update 操作；验证脚本是验证数据变更或影响行数是否达到预期要求效果；回滚脚本就是将数据回滚到修改前的状态。
虽说分四步骤写脚本可能会比较繁琐，但是这能够很大程度避免数据误操作。
— 来自 误删数据后除了跑路，还能怎么办？ 下的评论
MySQL 发送查询结果的过程
MySQL 是 边读边发 的，这意味着，如果客户端不及时接收 MySQL Server 的数据，MySQL 就无法读取剩余数据并发送，导致整个事务时间变长
有两个参数控制客户端接收数据的方式
mysql_store_result
启用 mysql_store_result 参数，客户端会将查询结果 暂存到本地缓存 中，直到将所有查询结果接收完毕后，才会返回给应用程序
这种方式可以防止 MySQL Server 因为 Client 无法及时接收数据导致事务「阻塞」
mysql_use_result
如果单个查询结果集太大，客户端的内存可能不足以接收所有数据
这种情况就只能启用 mysql_use_result 参数，客户端 逐行处理 查询结果
但是如果单行结果的处理时间过长，会导致整个事务的时间变长，加锁时间变长，影响 MySQL Server 的并发能力
总结
如果客户端的内存比较充足，尽量使用 mysql_store_result 选项，将查询结果缓存起来，减少 MySQL Server 等待 Client 接收数据的时间，进而减少单个事务的时间，提高 MySQL Server 的效率
当然，如果单次返回的数据太多，那就只能用 mysql_use_result 了
到底能不能使用 join？
先创建两张表：
create table t0 (
id int primary key,
a int,
b int,
key idx_a(a)
);
create table t1 like t0;
假设 t0 有 100 行数据，t1 有 1000 行数据
被驱动表上，join … on 字段有索引
例如：
select t0.* from t0 straight_join t1 on t0.a = t1.a;
执行过程如下：
- 遍历 t0 的所有行数据
- 对 t0 的每一行数据，在 t1 根据 idx_a 去查询，看看有没有符合条件的行，如果有，扔到结果集
- 返回结果给客户端
总扫描行数近似为 100 + 100 * log(100)，整个过程的时间复杂度为 100 * log(1000)
如果不使用 join，而是：
- 应用程序执行
select * from t0; - 对于每一行数据，执行
select * from t1 where t1.a = ?;
总扫描行数与时间复杂度可以认为不变，但是客户端与 MySQL Server 需要 101 次交互，效率较低
因此，这种情况下，使用 join 比不使用 join 会更好
被驱动表上，join … on 字段无索引
例如：
select t0.* from t0 straight_join t1 on t0.a = t1.b;
执行过程如下：
- 遍历 t0 的所有行数据
- 对 t0 的每一行数据，在 t1 全表查询，看看有没有符合条件的行，如果有，扔到结果集
- 返回结果给客户端
总扫描行数近似为 100 + 100 * 1000，时间复杂度为 100 * 1000（平方级）
可以发现，在 join … on 字段无索引的情况下，执行效率极低
为了提高速度，MySQL 引入了 join_buffer，用于暂存驱动表的数据
执行过程如下：
- 将 t0 所有行数据存到 join_buffer
- 扫描 t1 的所有行，逐行与 join_buffer 的数据做对比，看看有没有符合条件的行，如果有，扔到结果集
- 返回结果给客户端
虽然总扫描行数与时间复杂度没有变化，但是在内存中比对的速度会更快
如果 join_buffer 存不下 t0 的数据，会发生什么？
与之前的处理方式不同，如果存不下，不会借用磁盘，而是将 t0 的数据分成多个段，存放到 join_buffer
执行过程如下：
- 将 t0 第 1 ～ n 行存到 join_buffer
- 扫描 t1 的所有行，逐行与 join_buffer 的数据做对比，看看有没有符合条件的行，如果有，扔到结果集
- 清空 join_buffer
- 将 t0 第 n+1 ～ m 行存到 join_buffer
- 扫描 t1 的所有行，逐行与 join_buffer 的数据做对比，看看有没有符合条件的行，如果有，扔到结果集
- …
- 返回结果给客户端
可以发现，如果分段越多，全表扫描 t1 的次数会越多，效率就越低
注意： join_buffer 是无序的，要想知道一个 t1.b 是否与 join_buffer 中的某一行的 b 相等，需要遍历 join_buffer 的所有数据
join_buffer 太小，会发生什么
根据上面的分析，如果 join_buffer 太小，会导致分段次数增加，进而导致 全表扫描被驱动表的次数增加，会造成一系列连锁反应
如果 被驱动表 是一张 大表 ，并且存放的是 冷数据，会发生什么？
根据上面的理论，如果 join_buffer 不足，会多次全表扫描被驱动表
又因为被驱动表是一张大表，全表扫描的时间可能较长，会在 LRU 链表的 old 区域停留超过 1s，那么 LRU 的优化就不起作用了，导致大量冷数据进入 young 区，把原来的热点数据挤出来，进而导致 buffer pool 的命中率严重下降，磁盘 IO 迅速上升，给整个系统带来很大压力
此外，由于系统的负载上升，所有 事务的执行都会变慢，进而导致：
- undo log 回收慢，导致 undo log 版本链过长，快照读的时间变长（这会影响许多查询）
- 需要更长的时间后，才能释放锁，系统的 并发能力下降
join 时，应该让小表驱动大表，还是大表驱动小表
经过上面的分析，可以总结出：
- 被驱动表上，join … on 字段有索引：应该让 小表驱动大表，降低时间复杂度
- 被驱动表上，join … on 字段无索引：应该让 小表驱动大表，减少 join 时的分段次数，进而减少全表扫描的次数
小表指的就是行数少的表吗？
这个说法并不绝对，例如：
select t0.*, t1.id from t0 straight_join t1 on t0.a = t1.a;虽然 t0 只有 100 行，但是查询的是所有字段，而 t1 查询的仅仅是 id 字段，可能 1000 行 id 占的总空间 还没有 100 行
t0.*多在这种情况下，可以认为 t1 是小表
总结
到底能不能用 join？
如果被驱动表上，join … on 字段 有索引，那么就可以使用 join，效率更高
相反的，如果没有索引，那么尽量不要使用 join，因为如果多次分段，代表着多次全表扫描，会引起一系列雪崩反应
使用 join 时，应该让小表驱动大表，提高效率（当然 MySQL 会选择合适的表来做驱动表，如果发现不符合预期，可以使用 straight_join 来指定）
join 如何优化？
MRR
先来看看回表查询的一个问题：
在回表查询时，是一行一行的回表，还是一批一批的回表？
以 select * from user where age >= 18; 作为示例
一行一行地回表，由于 id 不是连续的，每一次回表都是一次 随机 IO，效率低
为了将随机 IO 近似 转化为顺序 IO，MySQL 引入了 MRR（Multi-Range Read）的概念
使用 MRR 优化后，执行过程如下：
- 开一个
read_rnd_buffer - 将符合条件的主键 id 集合放到
read_rnd_buffer中 - 对
read_rnd_buffer的主键 id 排序 - 按照排序后的主键 id 一行一行的回表，得到结果集
- 如果
read_rnd_buffer不足以存放下所有的主键 id，清空read_rnd_buffer，重复 2、3、4
MRR 的适用场景是：主键 id 是 有序 的，且是 范围查询
如果主键 id 是随机的，例如 UUID，那么即使对主键 id 排序，回表时也还是随机 IO，MRR 的优势就体现不出来了
因此，为了利用 MRR 优化，建议将主键 id 设置为自增的
BKA
前面提到：在 被驱动表上，join … on 字段有索引 的情况下，会先扫描驱动表，然后 一行一行 的去被驱动表查
如果一行行地查，就无法使用到 MRR 的优化
于是引入 BKA（Batched Key Access），一次性查一批数据，就好像范围查询一样
使用 BKA 优化后，执行过程如下：
- BKA 复用 join_buffer
- 将驱动表的数据放到 join_buffer
- 将 join_buffer 的数据作为查询条件，批量地在被驱动表查询（这个过程会使用到 MRR），得到结果集
- 如果 join_buffer 不够，重复 2、3
根据上述分析：使用 BKA 优化的前提，是启用了 MRR 优化
因此，启用 BKA 的方法如下：
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
BNL 的优化
前面提到的 被驱动表上，join … on 字段无索引 这种情况，使用的算法称作 BNL（Block Nested-Loop Join）
BNL 算法的性能很不理想，如果多次分段，还有可能会引起一系列雪崩反应
优化 BNL 的方式，就是 将 BNL 转化为 BKA，即在被驱动表的 join on 字段 加上索引
但是如果被驱动表太大，并且这个 SQL 还是个低频 SQL，那么加索引的成本就太高了
select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;
可以创建临时表来解决这个问题：
-- 创建临时表
create table tmp like t2;
-- 建索引
create index idx_b on t2(b);
-- 插入数据
insert into tmp (select * from t2 where t2.b>=1 and t2.b<=2000);
-- 得到结果
select * from t1 join tmp on (t1.b=tmp.b);
hash join
使用 BNL 算法时，在查找 join_buffer 中是否包含满足条件的行，这个操作是 O(n) 的时间复杂度
这是因为 join_buffer 维护的是一个无序列表（可以看成数组），查找的过程是线性的
这种查找很慢，因此在 MySQL 8.0.18 版本引入了 hash join，将 join_buffer 维护成一个哈希表，这样查找效率得到大幅提高，可以大幅减少扫描行数
hash join 是默认启用的
总结
- MRR 将回表查询的随机 IO 近似转化为顺序 IO
- BKA 使用 join_buffer 缓存驱动表的数据，保证可以利用 MRR 优化 IO，建议启用
- 优化 BNL 算法的方式是将 BNL 转化为 BKA，即建立索引
- 如果建立索引的成本过高，可以通过创建临时表来优化
- hash join 的原理是维护一个 join_buffer 哈希表，减少扫描行数
group by 优化
group by 的原理
以这个 SQL 语句说说 group by 的流程：
mysql> explain
-> select (id % 10) m, count(*) from user group by m;
+----+-------------+-------+------------+-------+-----------------------------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------------------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | user | NULL | index | PRIMARY,idx_phone,idx_email,idx_name,idx_age,idx_sex,idx_sex_age,idx_sex_age_name | idx_age | 5 | NULL | 7 | 100.00 | Using index; Using temporary |
+----+-------------+-------+------------+-------+-----------------------------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
执行过程如下：
- 创建内存临时表 tmp，包含两个字段：
m, count - 根据 idx_age 全表扫描，如果：
- tmp 表包含 m，那么 count + 1
- 否则，插入一条新的数据
优化
使用 group by，避免不了全表查询，那么，就只能 避免创建临时表 了
如何避免？
先要弄清楚为什么要创建临时表
由于 id % 10 这个字段是 无序 的，要想统计不同的 id % 10 的 count，就只能创建一个临时表来暂存数据
如果 id % 10 是有序的，那么就不用创建临时表，扫描一遍即可：
因此，最直接的方法就是给 group by 的字段 创建索引
如果创建索引的代价太大呢？
- 可以适当调大 tmp_table_size，避免转换为磁盘临时表
- 如果数据量太大，使用
SQL_BIG_RESULT提示 MySQL 直接使用磁盘临时表，避免多一次不必要的转换过程：select SQL_BIG_RESULT (id % 10) m, count(*) from user group by m;
有没有必要使用 Memory 引擎？
Memory 引擎最大的优势是读写速度快，但缺点也有几个：
- 锁的粒度比较粗，只支持表锁
- 断电丢失数据
- 断电重启，主从数据不一致
因此，不建议在生产环境上使用 Memory 引擎，虽然读写速度快，但是并发能力弱，断电还会丢数据
此外，InnoDB 有 buffer pool 的加持，缓存命中后，读写速度也是很不错的，并发能力也比较强
但是有一个例外，就是在 BNL 的优化 提到的创建临时表
在这种情况，如果数据量不大，就可以使用 Memory 引擎，快速得到结果，用完删掉即可：
-- 创建临时表，并在字段 b 上建立 hash 索引
create table tmp(id int primary key, a int, b int, index (b))engine=memory;
-- 插入数据
insert into tmp (select * from t2 where t2.b>=1 and t2.b<=2000);
-- 得到结果
select * from t1 join tmp on (t1.b=tmp.b);
-- 删除表
drop table tmp;
自增主键为什么不是连续的？
插入数据时，出现冲突
例如：
要想弄清楚原因，需要知道 MySQL 是如何申请一个 id 的
以 insert into t0 values (null, 5, 4); 为例：
- 加 AUTO_INC 锁
- 用户没有指定主键 id，因此，读取 内存 中，表 t0 的自增值 5
- 将自增值 + 1
- 释放 AUTO_INC 锁
- 插入数据，冲突
可以发现：自增值 + 1 这个操作是在插入数据 之前 的，这是导致主键不连续的第一个原因
事务回滚
例如：
为什么在插入冲突、事务回滚时，MySQL 不回滚自增 id 呢？
主要还是 性能因素
假设在插入冲突、事务回滚时，MySQL 回滚自增 id
- 事务 A 申请了一个主键 id = 2
- 事务 B 申请了一个主键 id = 3
- 事务 B 提交
- 事务 A 插入冲突，或者回滚，MySQL 回滚自增 id = 2（这里的回滚，相当于 id = 2 可用）
那么下一个事务要申请新的主键 id 就有可能出现重复的问题，为了解决，有两种方案：
- 方案一：在申请主键 id 时，从 1 开始申请，逐个判断有没有使用，如果没有，申请成功
- 方案二：AUTO_INC 锁的范围扩大到整个插入语句，即事务提交才释放
方案一的效率太低了，方案二的锁粒度大，并发能力太弱了
因此，MySQL 选择不回滚自增 id 以保证性能
批量申请 id 策略
在批量插入数据时，如果插入的行数比较多，例如 10w 行，那么就要申请 10w 次 AUTO_INC 锁，并发能力不强
MySQL 在申请自增 id 采取批量申请的策略，即第一次申请 1 个，第二次申请 2 个，第三次申请 4 个…
如果申请的 id 没有用完，也不会回滚，原因同上
自增 id 用完了，会发生什么？
对于主键自增 id，如果用完了，再次申请，它的值不会发生改变，因此，插入时会报错（主键冲突）
如果一张表没有主键，MySQL 会生成一个隐式的 row_id
一个 row_id 占用 6 字节，如果用完了，再次申请，会 从头开始，也就是说，会产生 数据覆盖
简单总结一下：
主键自增 id 的策略保证的是数据的可靠性，而 row_id 的策略是保证的可用性
生产上建议每张表都要有一个主键 id，保证数据可靠性