《MySQL实战45讲》笔记

1. 索引常见模型

索引的出现是为了提高查询效率，用于提高读写效率的数据结构很多，如哈希表、有序数组和搜索树。

(1). 哈希表

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。基本思路是用一个哈希函数把 key 映射成一个确定的位置，然后把 value 放在数组的这个位置。当不同的 key 值经过哈希函数的换算变为同一个值，可以用开放地址法和链表法解决冲突。哈希表这种结构适用于只有等值查询的场景。

(2). 有序数组

用二分法遍历有序数组时间复杂度是 O(log(N))，有序数组适用于等值查询和范围查询场景。但是，在需要往中间插入一个记录时就必须得挪动后面所有的记录，成本太高。

(3). 搜索树

MySql 的默认存储引擎 InnoDB 使用 B+ 树来存储数据，而不是二叉树，因为二叉树树高过高，每次查询都需要访问过多节点，即访问数据块过多，而从磁盘随机读取数据块过于耗时。

2. InnoDB 的索引模型

在 InnoDB 中，每一个索引在 InnoDB 里面对应一棵 B+ 树。根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

通过如下两个例子来说明基于主键索引和非主键索引查询的不同：

创表语句如下：


mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。回到主键索引树搜索的过程，我们称为回表。

如果我们没有显示定义主键(PRIMARY KEY)，那么InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的主键索引(ROWID随着行记录的写入而主键递增)。

3. mysql为什么建议使用自增主键

(1). 性能角度：页是InnoDB存储引擎的最小管理单位，每页大小默认是16KB，如果主键为自增 id 的话，mysql 在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。如果主键是非自增 id，为了确保索引有序，mysql 就需要将每次插入的数据都放到合适的位置上。当往一个快满或已满的数据页中插入数据时，新插入的数据会将数据页写满，mysql 就需要申请新的数据页，并且把上个数据页中的部分数据挪到新的数据页上。这就造成了页分裂，这个大量移动数据的过程增加了很多开销。

(2). 空间角度：由于每个非主键索引的叶子节点上都是主键的值，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

4. 覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

5. 联合索引

假设有个市民表的定义是这样的：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，(身份证号、姓名)联合索引可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

在建立联合索引的时候，如何安排索引内的字段顺序：

(1). 考虑索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

(2). 考虑空间。比如上面这个市民表的情况，name 字段是比 age 字段大的，则建议创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

另外，二级索引会默认和主键作联合索引。InnoDB会把主键字段放到索引定义字段后面，当然同时也会去重。所以，当主键是(a,b)的时候，定义为c的索引，实际上是（c,a,b); 定义为(c,a)的索引，实际上是(c,a,b); 定义为(c,b）的索引，实际上是（c,b,a)。

6. 普通索引 VS 唯一索引

(1). 查询过程

普通索引跟唯一索引执行上的区别：普通索引的等值查询，会继续遍历到第一个不相等的值才会结束，而唯一索引等值查询，命中则结束（性能差距微乎其微）

(2). 更新过程

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

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

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

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

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

(3). change buffer 使用场景

change buffer 对更新过程有加速作用，并且只限于用在普通索引的场景下，而不适用于唯一索引。

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。

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

(4). redo log 和 change buffer

redo log 与 change buffer 这2个机制，不同之处在于优化了整个变更流程的不同阶段。先不考虑redo log、change buffer机制，简化抽象一个变更(insert、update、delete)流程： 1、从磁盘读取待变更的行所在的数据页，读取至内存页中。 2、对内存页中的行，执行变更操作 3、将变更后的数据页，写入至磁盘中。步骤1，涉及随机读磁盘IO；步骤3，涉及随机写磁盘IO。redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的IO消耗。

7. 关于redo log

InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作 redo log（重做日志），在更新内存写完 redo log 后，就返回给客户端，本次更新成功。

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

引发数据库flush的四种场景：

(1). InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。

(2). 系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

(3). MySQL 认为系统“空闲”的时候，也要见缝插针地找时间，只要有机会就刷一点“脏页”。

(4). MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

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

(1). 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；

(2). 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

InnoDB 刷脏页的控制策略:

要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力。这个值建议设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试，下面的语句是用来测试磁盘随机读写的命令：

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

虽然我们现在已经定义了“全力刷脏页”的行为，但毕竟磁盘能力不能只用来刷脏页，还需要服务用户请求。所以接下来，我们就一起看看 InnoDB 怎么控制引擎按照“全力”的百分比来刷脏页。

InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。如图所示：

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

8. 索引字段的函数操作

假设现在维护了一个交易系统，其中交易记录表 tradelog 包含交易流水号（tradeid）、交易员 id（operator）、交易时间（t_modified）等字段，这个表的建表语句如下：

CREATE TABLE `tradelog` ( `id` int(11) NOT NULL, 
`tradeid` varchar(32) DEFAULT NULL, 
`operator` int(11) DEFAULT NULL, 
`t_modified` datetime DEFAULT NULL, 
PRIMARY KEY (`id`), 
KEY `tradeid` (`tradeid`), 
KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

要统计发生在所有年份中 7 月份的交易记录总数，SQL 语句可能会这么写：

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

下面是这个 t_modified 索引的示意图。方框上面的数字就是 month() 函数对应的值。

如果你的 SQL 语句条件用的是 where t_modified='2018-7-1’的话，引擎就会按照上面绿色箭头的路线，快速定位到 t_modified='2018-7-1’需要的结果。实际上，B+ 树提供的这个快速定位能力，来源于同一层兄弟节点的有序性。但是，如果计算 month() 函数的话，你会看到传入 7 的时候，在树的第一层就不知道该怎么办了。也就是说，对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

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

为了能够用上索引的快速定位能力，我们就要把 SQL 语句改成基于字段本身的范围查询。

mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

9. 隐式类型转换

mysql> select * from tradelog where tradeid=110717;

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

如何确定数据类型转换的规则？

看 select “10” > 9 的结果：如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。结果如图所示：

所以在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。

所以 mysql> select * from tradelog where tradeid=110717;这条语句对于优化器来说，相当于：

mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

也就是说，这条语句触发了上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

id 的类型是 int，对于下面这条语句：

select * from tradelog where id="83126";

因为当字符串和数字比较时会把字符串转化为数字，所以隐式转换不会应用到索引字段上，优化器选择走索引。另外，select 'a' = 0 ; 的结果是1，说明无法转换成数字的字符串都被转换成0来处理了。

10. 隐式字符编码转换

假设系统里还有另外一个表 trade_detail：

mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
  `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这时候，如果要查询 id=2 的交易的所有操作步骤信息，SQL 语句可以这么写：

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

在这个执行计划里，是从 tradelog 表中取 tradeid 字段，再去 trade_detail 表里查询匹配字段。因此，我们把 tradelog 称为驱动表，把 trade_detail 称为被驱动表，把 tradeid 称为关联字段。

这个 explain 结果表示的执行流程如下：

第 1 步，是根据 id 在 tradelog 表里找到 L2 这一行，这个步骤用上了主键索引，rows=1 表示只扫描一行；

第 2 步，是从 L2 中取出 tradeid 字段的值；

第 3 步，是根据 tradeid 值到 trade_detail 表中查找条件匹配的行。explain 的结果里面第二行的 key=NULL 表示的就是，这个过程是通过遍历主键索引的方式，一个一个地判断 tradeid 的值是否匹配。

因为表 trade_detail 里 tradeid 字段上是有索引的，我们本来是希望通过使用 tradeid 索引能够快速定位到等值的行。但这里并没有。原因是这两个表的字符集不同，一个是 utf8，一个是 utf8mb4，所以做表连接查询的时候用不上关联字段的索引。

字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。类似地，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。

因此，在执行上面语句的时候，需要将被驱动数据表里的字段一个个地转换成 utf8mb4，等同于下面这个写法：

select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

CONVERT() 函数，在这里的意思是把输入的字符串转成 utf8mb4 字符集。这就再次触发了我们上面说到的原则：对索引字段做函数操作，优化器会放弃走树搜索功能。

理解了原理以后，我们可以用如下方法优化语句：

(1). 比较常见的优化方法是，把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4，这样就没有字符集转换的问题了。

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

(2). 如果能够修改字段的字符集的话，是最好不过了。但如果数据量比较大，或者业务上暂时不能做这个 DDL 的话，那就只能采用修改 SQL 语句的方法了。

mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

总结：

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。MySQL 的优化器确实有“偷懒”的嫌疑，即使简单地把 where id+1=1000 改写成 where id=1000-1 就能够用上索引快速查找，也不会主动做这个语句重写。因此，每次你的业务代码升级时，把可能出现的、新的 SQL 语句 explain 一下，是一个很好的习惯。

11. Multi-Range Read

假设有数据表t1，里面插入了 1000 行数据，每一行的 a=1001-id 的值。也就是说，表 t1 中字段 a 是逆序的，a为索引字段。

select * from t1 where a>=1 and a<=100;

InnoDB 在普通索引 a 上查到主键 id 的值后，再根据一个个主键 id 的值到主键索引上去查整行数据的过程。主键索引是一棵 B+ 树，在这棵树上，每次只能根据一个主键 id 查到一行数据。因此，回表肯定是一行行搜索主键索引的。如图所示：

如果随着 a 的值递增顺序查询的话，id 的值就变成随机的，那么就会出现随机访问，性能相对较差。

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

语句的执行流程如下所示：

根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中;
将 read_rnd_buffer 中的 id 进行递增排序；
排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

如果要稳定地使用 MRR 优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。

使用了 MRR 优化后的执行流程和 explain 结果：

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

[MySql 系列] - 索引必知必会