极客时间mysql45讲实践篇(09-19章节)，重点提取，总结

普通索引和唯一索引，怎么选择，change buffer

查询过程

执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

  • 对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。

  • 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

这个不同带来的性能差距会有多少呢？答案是，微乎其微。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

当然，如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

但是，我们之前计算过，对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低。所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计

更新过程

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

如果要在这张表中插入一个新记录 (4,400) 的话，InnoDB 的处理流程是怎样的。

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

  • 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
  • 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

但，这不是我们关注的重点。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

  • 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
  • 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

普通索引插入更快点 不会随机io

change buffer 的使用场景

普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。 这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用

在实际使用中，你会发现，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

change buffer的前身是insert buffer,只能对insert 操作优化；后来升级了，增加了update/delete的支持，名字也改叫change buffer.

change buffer 和 redo log

1insert into t(id,k) values(id1,k1),(id2,k2);

我们假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。带 change buffer 的更新状态图如下。

它涉及了四个部分：内存、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）。

Page 1 在内存中，直接更新内存；

Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息

将上述两个动作记入 redo log 中（图中 3 和 4）。

做完上面这些，事务就可以完成了。所以，你会看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

1 select * from t where k in (k1, k2)

如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关了

  1. 读 Page 1 的时候，直接从内存返回

  2. 要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果

可以看到，直到需要读 Page 2 的时候，这个数据页才会被读入内存。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

总结

  • 唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用,主键索引也不能使用change buffer.
  • change buffer跟普通数据页一样也是存在磁盘里，区别在于change buffer是在共享表空间ibdata1里
  • change buffer 适合写多读少的场景
  • xxx.frm 表结构定义文件，格式为 表名.frm
  • xxx.ibd 表空间文件，格式为 表名.ibd （存放数据）
  • 系统表空间就是用来放系统信息的，比如数据字典什么的，对应的磁盘文件是ibdata1
  • redolog有两种，一种记录普通数据页的改动，一种记录changebuffer的改动

MySQL为什么有时候会选错索引？

选择索引是优化器的工作

扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断

一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

MySQL 是怎样得到索引的基数的呢？

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

analyze table table_name 命令，可以用来重新统计索引信息

MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行。如果 force index 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。

对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。

而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

怎么给字符串字段加索引？

1alter table SUser add index index1(email);
2-- 或
3alter table SUser add index index2(email(6));

如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：

  1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
  2. 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
  3. 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：

  1. 从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
  2. 到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
  3. 取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
  4. 重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。

在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

在建立索引时关注的是区分度，区分度越高越好

1 select 
2  count(distinct left(email,4)）as L4,
3  count(distinct left(email,5)）as L5,
4  count(distinct left(email,6)）as L6,
5  count(distinct left(email,7)）as L7,
6from SUser

当然，使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

使用前缀索引就用不上覆盖索引对查询性能的优化了

但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

**第一种方式是使用倒序存储。**如果你存储身份证号的时候把它倒过来存，每次查询的时候，你可以这么写：

1select field_list from t where id_card = reverse('input_id_card_string');

由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足够的区分度。当然了，实践中你不要忘记使用 count(distinct) 方法去做个验证。

**第二种方式是使用 hash 字段。**你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

1 alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

1 select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这样，索引的长度变成了 4 个字节，比原来小了很多。

总结

  1. 直接创建完整索引，这样可能比较占用空间；
  2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
  3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
  4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

为什么我的MySQL会“抖”一下？

刷脏页

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

刷脏页 就是把内存的数据刷到磁盘上

MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush)

  1. 第一种场景是，粉板满了，记不下了。这时候如果再有人来赊账，掌柜就只得放下手里的活儿，将粉板上的记录擦掉一些，留出空位以便继续记账。当然在擦掉之前，他必须先将正确的账目记录到账本中才行。 这个场景，对应的就是 InnoDB 的 redo log 写满了
  2. 第二种场景是，这一天生意太好，要记住的事情太多，掌柜发现自己快记不住了，赶紧找出账本把孔乙己这笔账先加进去。 这种场景，对应的就是系统内存不足。
  3. 第三种场景是，生意不忙的时候，或者打烊之后。这时候柜台没事，掌柜闲着也是闲着，不如更新账本。 这种场景，对应的就是 MySQL 认为系统“空闲”的时候,刷脏页
  4. 对应的就是 MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

第三、第四 其实我们不太关心这个。 不会出现性能问题。

第一种是“redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。

第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。

InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

  • 第一种是，还没有使用的；
  • 第二种是，使用了并且是干净页；
  • 第三种是，使用了并且是脏页。

InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。

而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

  • 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
  • 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

InnoDB 刷脏页的控制策略 innodb_io_capacity参数

innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力

建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试，下面的语句是我用来测试磁盘随机读写的命令：

1filename=1.txt
2 fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

取iops 的平均值即可

 1vagrant@ubuntu2204:~$ sudo  fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
 2mytest: (g=0): rw=randrw, bs=(R) 16.0KiB-16.0KiB, (W) 16.0KiB-16.0KiB, (T) 16.0KiB-16.0KiB, ioengine=psync, iodepth=1
 3...
 4fio-3.28
 5Starting 10 threads
 6mytest: Laying out IO file (1 file / 500MiB)
 7Jobs: 10 (f=10): [m(10)][27.3%][r=95.1MiB/s,w=95.7MiB/s][r=6086,w=6126 IOPS][etaJobs: 10 (f=10): [m(10)][36.4%][r=97.6MiB/s,w=96.2MiB/s][r=6246,w=6159 IOPS][etaJobs: 10 (f=10): [m(10)][45.5%][r=96.5MiB/s,w=97.4MiB/s][r=6177,w=6235 IOPS][etaJobs: 10 (f=10): [m(10)][54.5%][r=92.0MiB/s,w=92.7MiB/s][r=5890,w=5933 IOPS][etaJobs: 10 (f=10): [m(10)][63.6%][r=92.4MiB/s,w=94.3MiB/s][r=5910,w=6033 IOPS][etaJobs: 10 (f=10): [m(10)][72.7%][r=93.1MiB/s,w=95.3MiB/s][r=5960,w=6102 IOPS][etaJobs: 10 (f=10): [m(10)][81.8%][r=90.9MiB/s,w=89.3MiB/s][r=5816,w=5717 IOPS][etaJobs: 10 (f=10): [m(10)][90.9%][r=94.1MiB/s,w=94.9MiB/s][r=6022,w=6072 IOPS][etaJobs: 10 (f=10): [m(10)][100.0%][r=93.6MiB/s,w=94.9MiB/s][r=5989,w=6076 IOPS][eta 00m:00s]
 8mytest: (groupid=0, jobs=10): err= 0: pid=3532: Sat Dec  3 07:00:12 2022
 9  read: IOPS=6003, BW=93.8MiB/s (98.4MB/s)(938MiB/10002msec)
10    clat (usec): min=43, max=18487, avg=1299.47, stdev=1614.60
11     lat (usec): min=43, max=18487, avg=1299.70, stdev=1614.69
12    clat percentiles (usec):
13     |  1.00th=[  210],  5.00th=[  273], 10.00th=[  322], 20.00th=[  396],
14     | 30.00th=[  478], 40.00th=[  562], 50.00th=[  676], 60.00th=[  816],
15     | 70.00th=[ 1074], 80.00th=[ 1663], 90.00th=[ 3294], 95.00th=[ 5276],
16     | 99.00th=[ 7504], 99.50th=[ 8291], 99.90th=[11338], 99.95th=[12649],
17     | 99.99th=[15926]
18   bw (  KiB/s): min=81244, max=112163, per=100.00%, avg=96078.21, stdev=788.59, samples=190
19   iops        : min= 5076, max= 7008, avg=6002.63, stdev=49.28, samples=190
20  write: IOPS=6060, BW=94.7MiB/s (99.3MB/s)(947MiB/10002msec); 0 zone resets
21    clat (usec): min=38, max=16455, avg=356.35, stdev=665.81
22     lat (usec): min=38, max=16456, avg=356.89, stdev=666.09
23    clat percentiles (usec):
24     |  1.00th=[   51],  5.00th=[   53], 10.00th=[   55], 20.00th=[   60],
25     | 30.00th=[   79], 40.00th=[   97], 50.00th=[  127], 60.00th=[  174],
26     | 70.00th=[  269], 80.00th=[  449], 90.00th=[  857], 95.00th=[ 1434],
27     | 99.00th=[ 3294], 99.50th=[ 4228], 99.90th=[ 7046], 99.95th=[ 8455],
28     | 99.99th=[10814]
29   bw (  KiB/s): min=81790, max=113379, per=100.00%, avg=97094.89, stdev=816.03, samples=190
30   iops        : min= 5110, max= 7084, avg=6066.11, stdev=51.00, samples=190
31  lat (usec)   : 50=0.20%, 100=20.89%, 250=15.05%, 500=21.34%, 750=14.75%
32  lat (usec)   : 1000=7.59%
33  lat (msec)   : 2=10.55%, 4=5.28%, 10=4.26%, 20=0.09%
34  cpu          : usr=0.72%, sys=6.59%, ctx=100983, majf=0, minf=0
35  IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
36     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
37     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
38     issued rwts: total=60046,60615,0,0 short=0,0,0,0 dropped=0,0,0,0
39     latency   : target=0, window=0, percentile=100.00%, depth=1
40
41Run status group 0 (all jobs):
42   READ: bw=93.8MiB/s (98.4MB/s), 93.8MiB/s-93.8MiB/s (98.4MB/s-98.4MB/s), io=938MiB (984MB), run=10002-10002msec
43  WRITE: bw=94.7MiB/s (99.3MB/s), 94.7MiB/s-94.7MiB/s (99.3MB/s-99.3MB/s), io=947MiB (993MB), run=10002-10002msec
44
45Disk stats (read/write):
46    dm-0: ios=59476/60264, merge=0/0, ticks=55400/7320, in_queue=62720, util=99.42%, aggrios=60046/60795, aggrmerge=0/37, aggrticks=54943/7713, aggrin_queue=62678, aggrutil=99.29%
47  sda: ios=60046/60795, merge=0/37, ticks=54943/7713, in_queue=62678, util=99.29%

合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。

1
2select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
3select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
4select @a/@b;

在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。

找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。

建议你把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间

在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了。

为什么表数据删掉一半，表文件大小不变

参数 innodb_file_per_table

这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中 ，一定要开启这个。

通过 drop table 命令，系统就会直接删除这个文件

数据页复用、记录复用

数据页的复用跟记录的复用是不同的。

如果删除一个页面上的一条记录，那在这个页面范围内的，插入可能是会服用这个位置的，不在范围内则无法服用这个位置。

当整个数据页被删除，那整个页面是可以被服用的。

如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

实际上，不止是删除数据会造成空洞，插入数据也会。

重建表

试想一下，如果你现在有一个表 A，需要做空间收缩，为了把表中存在的空洞去掉，你可以怎么做呢？

你可以新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。

由于表 B 是新建的表，所以表 A 主键索引上的空洞，在表 B 中就都不存在了。显然地，表 B 的主键索引更紧凑，数据页的利用率也更高。如果我们把表 B 作为临时表，数据从表 A 导入表 B 的操作完成后，用表 B 替换 A，从效果上看，就起到了收缩表 A 空间的作用。

这里，你可以使用 alter table A engine=InnoDB 命令来重建表 （MySQL 会自动完成转存数据、交换表名、删除旧表的操作。）

这个不是ddl 不是online 的 （“原始表"期间不能有更新，否则会丢数据）

Online DDL

Online DDL 之后，重建表的流程：

  1. 建立一个临时文件，扫描表 A 主键的所有数据页；
  2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
  3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
  4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
  5. 用临时文件替换表 A 的数据文件。

alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。

为什么要退化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。

那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做 DDL。

而对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的。

总结

  • DDL 过程如果是 Online 的，就一定是 inplace 的；
  • DML语句加的是MDL读锁，读读不冲突
  • 从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）默认的就是上面图的流程了；
  • analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
  • optimize table t 等于 recreate+analyze。
  • alter table t engine=InnoDB == alter table t engine=innodb,ALGORITHM=inplace;
  • alter table t engine=innodb,ALGORITHM=copy; 强制copy表，是非online 的
  • alter 操作(增删字段、增删索引等)是支持 online ddl , 加全文索引不online

count(*)这么慢，我该怎么办？

count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。

count函数统计的是 不为null的

缓存计数不准，用mysql 计数器 (通过事务保证插入和更新统计数一致性)

MySQL 5.7.18 前后 count(*) 的区别

在 MySQL 5.7.18 之前，InnoDB 通过扫描聚簇索引来处理 count(*) 语句。

从 MySQL 5.7.18 开始，通过遍历最小的可用二级索引来处理 count(*) 语句。如果不存在二级索引，则扫描聚簇索引。但是，如果索引记录不完全在缓存池中的话，处理 count(*) 也是比较久的。

新版本为什么会使用二级索引来处理 count(*) 语句呢？

原因是 InnoDB 二级索引树的叶子节点上存放的是主键，而主键索引树的叶子节点上存放的是整行数据，所以二级索引树比主键索引树小。因此优化器基于成本的考虑，优先选择的是二级索引。所以 count(主键) 其实没 count (*) 快。

order by 原理与优化、group by 优化

创建测试表

 1CREATE TABLE `t1` (	      /* 创建表t1 */
 2  `id` int(11) NOT NULL AUTO_INCREMENT,
 3  `a` int(20) DEFAULT NULL,
 4  `b` int(20) DEFAULT NULL,
 5  `c` int(20) DEFAULT NULL,
 6  `d` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 7  PRIMARY KEY (`id`),
 8  KEY `idx_a_b` (`a`,`b`),
 9  KEY `idx_c` (`c`)
10) ENGINE=InnoDB CHARSET=utf8mb4 ;
11
12drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */
13delimiter ;;
14create procedure insert_t1()        /* 创建存储过程insert_t1 */
15begin
16  declare i int;                  /* 声明变量i */
17  set i=1;                        /* 设置i的初始值为1 */
18  while(i<=10000)do			      /* 对满足i<=10000的值进行while循环 */
19    insert into t1(a,b,c) values(i,i,i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
20    set i=i+1;                       /* 将i加1 */
21  end while;
22end;;
23delimiter ;
24call insert_t1();               /* 运行存储过程insert_t1 */
25
26update t1 set a=1000 where id >9000;    /* 将id大于9000的行的a字段更新为1000 */

MySQL 的排序方式

按照排序原理分，MySQL 排序方式分两种：

  • 通过有序索引直接返回有序数据
  • 通过 Filesort 进行的排序

怎么确定某条排序的 SQL 所使用的排序方式？

使用 explain 来查看该排序 SQL 的执行计划，重点关注 Extra 字段：

如果该字段里显示是 Using index，则表示是通过有序索引直接返回有序数据。比如：

1explain select id,c from t1 order by c; -- Using index

如果该字段里显示是 Using filesort，则表示该 SQL 是通过 Filesort 进行的排序，比如：

1explain select id,d from t1 order by d; --  Using Filesort

Filesort 是在内存中还是在磁盘中完成排序的？

MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和 sort_buffer_size 配置的大小。

  • 如果 “排序的数据大小” < sort_buffer_size: 内存排序
  • 如果 “排序的数据大小” > sort_buffer_size: 磁盘排序

怎么确定使用 Filesort 排序的 SQL 是在内存还是在磁盘中进行的排序操作？

 1/* 打开 optimizer_trace，只对本线程有效 */
 2SET optimizer_trace='enabled=on'; 
 3 
 4/* @a 保存 Innodb_rows_read 的初始值 */
 5select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
 6 
 7/* 执行语句 */
 8select id,d from t1 order by d; 
 9
10/* 查看 OPTIMIZER_TRACE 输出 */
11SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
12 
13/* @b 保存 Innodb_rows_read 的当前值 */
14select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
15 
16/* 计算 Innodb_rows_read 差值 */
17select @b-@a;

  • rows：预计扫描的行数
  • examined_rows：参与排序的行
  • number_of_tmp_files：使用临时文件的个数
  • sort_buffer_size：sort_buffer 的大小
  • sort_mode：排序模式

再看一个用到临时文件的例子，如下图，因为 number_of_tmp_files 等于 7，所以表示使用的是磁盘排序。对于 number_of_tmp_files 等于 7 表示该 SQL 将需要排序的数据分为 7 份，然后每份单独排序，再存放在 7 个临时文件中，最后把 7 个临时文件合并成一个大的有序文件。

如果有临时文件个数，一般都是通过归并排序算法搞定的。

Filesort下的排序模式sort_mode

Filesort 下的排序模式有三种，具体介绍如下：（参考《MySQL 5.7 Reference Manual》8.2.1.14 ORDER BY Optimization）

  • < sort_key, rowid >双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
  • < sort_key, additional_fields >单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
  • < sort_key, packed_additional_fields >打包数据排序模式：与单路排序相似，区别是将 char 和 varchar 字段存到 sort buffer 中时，更加紧缩。

因为打包数据排序模式是单路排序的一种升级模式。

探讨双路排序和单路排序的区别

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和需要查询的字段总大小来判断使用哪种排序模式。

  • 如果 max_length_for_sort_data 比查询字段的总长度大，那么使用 < sort_key, additional_fields >排序模式；
  • 如果 max_length_for_sort_data 比查询字段的总长度小，那么使用 <sort_key, rowid> 排序模式。
1set session optimizer_trace="enabled=on",end_markers_in_json=on;
2
3SET max_length_for_sort_data = 20;
4
5select a,d from t1 order by d;    /* 查询表t1的id、a、d三个字段的值，按照字段d进行排序  */
6
7SELECT * FROM information_schema.OPTIMIZER_TRACE\G

OPTIMIZER_TRACE 结果中排序信息如下图：

发现使用的排序模式是 < sort_key, additional_fields >

怎么让这条 SQL 的排序模式变成 <sort_key, rowid> 呢？下面我们来试验下：

因为 a、d 两个字段的总长度为 12，可以尝试把 max_length_for_sort_data 改为小于 12 的值，看排序模式是否有改变。

1set session optimizer_trace="enabled=on",end_markers_in_json=on;
2
3set max_length_for_sort_data = 4;
4
5select a,d from t1 order by d;
6
7SELECT * FROM information_schema.OPTIMIZER_TRACE\G

OPTIMIZER_TRACE 结果,发现使用的排序模式确实变成了 <sort_key, rowid>。

单路排序

单路排序的详细过程：

  1. 从索引 a 找到第一个满足 a = 1000 条件的主键 id
  2. 根据主键 id 取出整行，取出 a、c、d 三个字段的值，存入 sort_buffer 中
  3. 从索引 a 找到下一个满足 a = 1000 条件的主键 id
  4. 重复步骤 2、3 直到不满足 a = 1000
  5. 对 sort_buffer 中的数据按照字段 d 进行排序
  6. 返回结果给客户端

双路排序

  1. 从索引 a 找到第一个满足 a = 1000 的主键 id
  2. 根据主键 id 取出整行，把排序字段 d 和主键 id 这两个字段放到 sort buffer 中
  3. 从索引 a 取下一个满足 a = 1000 记录的主键 id
  4. 重复 3、4 直到不满足 a = 1000
  5. 对 sort_buffer 中的字段 d 和主键 id 按照字段 d 进行排序
  6. 遍历排序好的 id 和字段 d，按照 id 的值回到原表中取出 a、c、d 三个字段的值返回给客户端

MySQL常见字段类型及所占字节

1字段类型	字节
2INT	4
3BIGINT	8
4DECIMAL(M,D)	M+2
5DATETIME	8
6TIMESTAMP	4
7CHAR(M)	M
8VARCHAR(M)	 实际长度+1 (M<=255) or  实际长度+2   (M>255)   +的是用来存储长度

order by优化总结

  • 如果 MySQL 排序内存配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用 rowid 排序算法，可以在 sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。
  • 如果 MySQL 排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器优先选择全字段排序，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。
  • 排序字段添加索引
  • 多个字段排序优化,考虑索引顺序，以及考虑联合索引去优化
  • 去掉不必要的返回字段
    • 扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引
  • 修改参数 max_length_for_sort_data、sort_buffer_size 。
    • max_length_for_sort_data：如果觉得排序效率比较低，可以适当加大 max_length_for_sort_data 的值，让优化器优先选择全字段排序。当然不能设置过大，可能会导致 CPU 利用率过低或者磁盘 I/O 过高；
    • sort_buffer_size：适当加大 sort_buffer_size 的值，尽可能让排序在内存中完成。但不能设置过大，可能导致数据库服务器 SWAP。
  • 使用范围查询再排序
    • a、b 两个字段的联合索引，对于单个 a 的值，b 是有序的。而对于 a 字段的范围查询，也就是 a 字段会有多个值，取到 a，b 的值 b 就不一定有序了，因此要额外进行排序。
  • ASC 和 DESC 混合使用将无法使用索引
    • 对联合索引多个字段同时排序时，如果一个是顺序，一个是倒序，则使用不了索引，如下sql
    • explain select id,a,b from t1 order by a asc,b desc;

group by 优化

默认情况，会对 group by 字段排序，因此优化方式与 order by 基本一致，如果目的只是分组而不用排序，可以指定 order by null 禁止排序。

如何正确地显示随机消息？

1explain select word from words order by rand() limit 3;

Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

磁盘临时表 tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表

归并排序算法

我们现在的 SQL 语句，只需要取 R 值最小的 3 个 rowid。但是，如果使用归并排序算法的话，虽然最终也能得到前 3 个值，但是这个算法结束后，已经将 10000 行数据都排好序了。

也就是说，后面的 9997 行也是有序的了。但，我们的查询并不需要这些数据是有序的。这浪费了非常多的计算量。

优先队列排序算法

最大堆，先取3个， 然后取第四个和最大值比较，如果小于最大值，踢出最大值 。

随机排序方法

取得这个表的主键 id 的最大值 M 和最小值 N;

用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;

取不小于 X 的第一个 ID 的行。

1select max(id),min(id) into @M,@N from t ;
2set @X= floor((@M-@N+1)*rand() + @N);
3select * from t where id >= @X limit 1;

可以认为就只扫描了 3 行

但是ID 中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机。

得到严格随机

取得整个表的行数，并记为 C。

取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。

再用 limit Y,1 取得一行。

1select count(*) into @C from t;
2set @Y = floor(@C * rand());
3set @sql = concat("select * from t limit ", @Y, ",1");
4prepare stmt from @sql;
5execute stmt;
6DEALLOCATE prepare stmt;

取得整个表的行数，记为 C；

根据相同的随机方法得到 Y1、Y2、Y3；

再执行三个 limit Y, 1 语句得到三行数据。

1select count(*) into @C from t;
2set @Y1 = floor(@C * rand());
3set @Y2 = floor(@C * rand());
4set @Y3 = floor(@C * rand());
5select * from t limit @Y1，1； -- 在应用代码里面取 Y1、Y2、Y3 值，拼出 SQL 后执行
6select * from t limit @Y2，1；
7select * from t limit @Y3，1；

为什么这些SQL语句逻辑相同，性能却差异巨大

条件字段函数操作

无法利用索引

1select count(*) from tradelog where month(t_modified)=7;

如果你的 SQL 语句条件用的是 where t_modified=‘2018-7-1’的话，引擎就会按照上面绿色箭头的路线，快速定位到 t_modified=‘2018-7-1’需要的结果。

实际上，B+ 树提供的这个快速定位能力，来源于同一层兄弟节点的有序性。

然而使用month函数后，在树的第一层就不知道该怎么办了。

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

在这个例子里，放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引 t_modified，优化器对比索引大小后发现，索引 t_modified 更小，遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified。

隐式类型转换

字符串类型 ，使用 = 数字

1select * from tradelog where tradeid=110717;
2
3-- == 
4
5select * from tradelog where  CAST(tradid AS signed int) = 110717;

交易编号 tradeid 这个字段上，本来就有索引，但是 explain 的结果却显示，这条语句需要走全表扫描。你可能也发现了，tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。

1select "10" > 9  --  1

如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；

如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。

mysql使用的是把字符串转成数字去比较

隐式字符编码转换

字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。

连表的时候，使用统一的字符编码,避免字段使用函数造成索引不命中。（

推荐统一使用utf8mb4

查询出来读结果，在server层还要做判断

假设现在表里面，有 100 万行数据，其中有 10 万行数据的 b 的值是’1234567890’， 假设现在执行语句是这么写的:

1select * from table_a where b='1234567890abcd';

在传给引擎执行的时候，做了字符截断。因为引擎里面这个行只定义了长度是 10，所以只截了前 10 个字节，就是’1234567890’进去做匹配；

这样满足条件的数据有 10 万行；

因为是 select *， 所以要做 10 万次回表；

但是每次回表以后查出整行，到 server 层一判断，b 的值都不是’1234567890abcd’;

返回结果是空。

虽然执行过程中可能经过函数操作，但是最终在拿到结果后，server 层还是要做一轮判断的

为什么我只查一行的语句，也执行这么慢 ？

查询长时间不返回情况

等meta lock （情况一）

基本是因为被锁住了 show processlist 查看原因

等待meta lock 锁释放。 Waiting for table metadata lock 状态

这类问题的处理方式，就是找到谁持有 MDL 写锁，然后把它 kill 掉。

1 select blocking_pid from schema_table_lock_waits;
2-- blocking_pid = 4   线程号
3
4KILL QUERY 4 ; -- 停止 4 号线程当前正在执行的语句
5-- or 
6KILL 4;

等flush

情况二，但是一般人不会手动去lock吧

1flush tables t with read lock;
2flush tables with read lock;

等待行锁

1-- session A 
2begin ;
3update set c=c+1 where id=1;
4
5
6-- session B
7select * from table where id =1 in share mode ;

查询慢

lock in share mode 当前读

session A 先用 start transaction with consistent snapshot 命令启动了一个事务，之后 session B 才开始执行 update 语句。

session B 执行完 100 万次 update 语句后，id=1 这一行处于什么状态呢

session B 更新完 100 万次，生成了 100 万个回滚日志 (undo log)。

带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。

注意，undo log 里记录的其实是“把 2 改成 1”，“把 3 改成 2”这样的操作逻辑，画成减 1 的目的是方便看图。

mysql一些分析sql

用来遗忘快速复制。

 1
 2-- 正在执行的慢sql 
 3show processlist
 4
 5
 6-- 锁等待sql
 7select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G
 8select blocking_pid from t sys.innodb_lock_waits;
 9-- kill query `blocking_pid查出来等值`  -- 停止 4 号线程当前正在执行的语句
10
11-- kill `blocking_pid查出来等值`  -- 直接断开这个连接 
12
13set long_query_time=0 ;--  所有sql 都计入到慢查询