​MySQL优化学习笔记手札(二)

前言

对InnoDB表和索引的数据结构有一个基本的理解之后，我们就大可不必背诵一些索引优化准则，我们大可以直接从结构上推到，我将其称之为最小记忆原则。在《MySQL优化学习笔记手札(一)》中我们已经对MySQL表和索引的数据结构有了一个大致的了解了，这里我们再总结一下，然后由这些结构推导出MySQL的一般   SQL优化准则。

• InnoDB将数据划分为若干页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB
• InnoDB存储引擎会自动为主键(如果没有它会自动帮我们添加)建立聚簇索引，聚簇索引的叶子结点包含完整的用户记录。
• 每个索引都对应一颗B+树，B+树分为好多层，最下边一层是叶子结点，其余的是内结点。所有的用户记录都存储在B+树的叶子结点。所有的目录项记录都存储在内结点
• 我们也可以为自己感兴趣的列建立二级索引，二级索引的叶子结点包含的用户记录由索引列+主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
• B+树的每层结点都是按照索引列值的从小到达的顺序排序而组成了双向链表，而每个页内的记录(不论是用户记录还是目录项记录)都是按照索引列的值从小到达的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列的值相同，再按照联合索引后边的列进行排序。

索引相关的语法

• 建表的时候指定

CREATE TALBE 表名 (
    各种列的信息 ··· , 
    [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)

• 示例:

create table student_info(
  id int,
  name varchar(100) NOT NULL,
  number varchar(100) NOT NULL,
  phonerNumer varchar(100) NOT NULL,
    score int,
  PRIMARY KEY(id),
  index name_number_phoneNumber(name,number,phonerNumer)
)

• 建表之后指定

ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);
INDEX 和 KEY是同义词。

• 示例:

ALTER TABLE student_info  ADD INDEX index_score (score);

• 删除索引
  

ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

示例:

ALTER TABLE student_info  DROP INDEX  index_score (score);

由B+树到一般SQL优化准则

我们还是用student_info 这张表来解释， 从建表语句上我们可以看到student_info上有两个索引，一个是id做的聚簇索引，另一个是name，number、phoneNumber这三列做的聚簇索引。

列匹配

• 全值匹配

如果我们的搜索条件中的列和索引列一致的话，这种情况我们就称为全值匹配，比方说下边这个查找语句:

SELECT * FROM student_info  where name = '张三' and number = '100001' and  phonerNumer = '13555545064'

我们为这三列建的联合索引都用到了，我们可以想象一下这个查询过程,name_number_phoneNumber 这个索引对应的B+树，是先按name进行排序，name相同在按number排序，name和number相同使用phoneNumber来排序。大致画一下这颗树的基本结构:

B+树的数据页和记录先是按照name列的值进行排序，所以可先定位到name列的是张三的叶子结点记录，如果张三不唯一，再根据100001去匹配，如果还是存在相同的记录，再用13555545064去匹配。

在上面的例子中，我们的搜索条件顺序刚好和联合索引的位置一样，这样刚好就能充分利用这三个索引。那么如果调整这三个搜索条件的位置呢？我们是不是无法使用到了我们建立的联合索引呢？当然不会，记得我们在《MySQL优化学习手札(一)》讨论过的MySQL的基本结构吗？MySQL有一个查询优化器模块，即使你不小心将SQL语句写成了下面这样:

SELECT * FROM student_info  where number = '100001'  and  name = '张三' and    phonerNumer = '13555545064'

MySQL查询优化器也会将SQL给你调整为:

SELECT * FROM student_info  where name = '张三' and number = '100001' and  phonerNumer = '13555545064'

因为这三个搜索条件调换顺序不影响搜索结果。

• 最左匹配

那么有朋友会问了，如果我将SQL调整成了下面这样呢:

SELECT * FROM student_info  where name = '100001'

也是可以用的到，为什么呢？让我们将这个联合索引理解为我们手机中通讯录, 我们的通讯录是按照首字母来进行排序，首字母相同按照第二个字的首字母排序。只用name这一列，我们就可以理解为让我们去查找第一个姓陈的人一样。我们依然可以 根据字母轴来快速定位。那么如果是只用到了第二个字呢？也就是说你想找名字中第二个字是梦的人，你就只能扫描整个通讯录了。对应的SQL就是:

SELECT * FROM student_info  where number = '100001'

我们的索引是先按照name排序的，只用到了number就无法用到我们建立的联合索引，只能扫描全表。如果你想加速number这一列的查找，只能单独再针对number这一列建立索引。

那如果我们的SQL用到了name这一列呢，像下面这样:

SELECT * FROM student_info  where name = '张三' and number = '100001'

这是可以的，因为我们的索引是按照name进行排序的，先定位到name为张三的记录，再根据number去匹配。

那么如果是下面这样呢:

SELECT * FROM student_info  where name = '张三' and phonerNumer = '100001'

这种情况下，也是可以用的到我们建立的联合索引的，只不过只能用到联合索引的第一列，我们定位到张三这一批记录还要遍历这些记录。

可以理解为你在通讯录中寻找第一个字是陈，第二字是君的人。

这也就是我们的最左匹配。

• 匹配列前缀

在MySQL中为某个列建立索引的意思其实就是为该列建立一颗用该列的值排序的B+树，上面我们为student_info建立的name_number_phoneNumber就会先按name列的值进行排序，你还可以将这个排序理解为通讯录, name是字符串，一般比较字符串大小的过程是这个样子的:

• 先比较字符串的第一个字符，第一个字符小的那个字符串就小
• 如果相等就比较下一个字符，直到最后一个字符。

所以一个排列好的字符串，其前缀都是排好序的，对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的，比方说我们想查找姓张的人，我们其实可以这么写:

SELECT * FROM student_info  where name  like  '张%'

如果我们想匹配中缀或者后缀，就无法用到索引了，因为这就像你在通讯录定位中定位名字中间包含某个字或者最后几个是哪几个一样，你无法利用字母轴，只能扫描全表。

范围匹配

条件搜索的另一种形式是范围查询, 像下面这样:

SELECT * FROM  student_info  where  '张' < name and name < '李'

我们上面的索引刚好是按name排序的，所以我们的查询过程是这样的：

• 先找到B+树中第一条name值大于张的二级索引记录，读取该记录的主键值进行回表操作，获得对应的聚簇索引记录。
• 根据上一步找到的记录，沿着记录所在的单向链表查找下一条二级索引记录，页与页之间是双向链表，判断该记录是否符合name < ‘李’条件，如果符合，取出该条记录
• 重复上一步骤，直到某条二级索引记录不符合name < ‘李’条件为止

同上面的列匹配一样，使用联合索引进行范围查找的时候需要注意，如果需要对联合索引的列进行范围查找的时候，只有包含最左边的那个列进行范围查找的时候才能用到联合索引。原因同列匹配一样。

那么如果我将上面的语句调整成下面的语句呢?

SELECT * FROM  student_info  where  name = '张三' and  '1001' <  number  and number < '1002'  and  phonerNumer = '150'

这个SQL的执行过程是大致是这样的，首先根据对name列进行精确查找，成功用到了B+树索引，然后找到了name都是张三的二级索引记录，然后我们知道name相同，我们按照number排序，所以对number列的范围查找也可以用到我们的索引列，但是在number这个范围的记录未必是按phonerNumer排序的，所以我们就phoneNumber无法用到索引列，只能遍历number范围中的叶子结点记录。

排序

我们在获得查询记录之后，通常还会再执行排序，一般情况下MySQL都需要将记录扫描进入内存，然后使用对应的排序算法，按照ORDER BY对应的列进行排序。

如果ORDER BY 后面跟的列刚好是索引列，单列索引，这个列刚好是排完序的，MySQL就直接从表中索取记录就好。如果是非索引列，且扫描的记录比较多，内存不够用的情况下，MySQL就需要借助磁盘来存放排序结果，在MySQL中，把这种在内存中或者在磁盘上进行排序的方式统称为文件排序(file sort)。我们知道磁盘的读写速度是非常慢的，所以一旦用到了文件排序，那么你的SQL就没有那么快了。

如果是ORDER BY 之后跟的是联合索引中的字段，且顺序和联合索引中的顺序相同，那么可能就不需要执行排序操作，像下面这样:

SELECT *    FROM  student_info   ORDER BY NAME,number,phonerNumer

但是在我使用的MySQL 5.7.22-log版本上，它是文件排序，这个时候可能有朋友就会问了，你是咋知道的呢？MySQL有一个执行计划的东西，可以看到SQL是怎么执行的。这里先让MySQL 的执行计划先出个场：

explain  SELECT *  FROM  student_info  ORDER BY NAME,number,phonerNumer limit 10

type = all. 也就是扫描全表，为什么MySQL同学不走我们建立的联合索引呢？原因在于我的表中只有一条记录，从执行成本上来说，走我们建立的联合索引，还要回表一次，不如直接全表扫描。让我们将数据增加到一万条：

再看执行计划我们就可以看到，type = index，这代表MySQL走了我们创建的索引。如果我们只查了索引列的值，且搜索条件中用到了索引列，那么同样也是可以用到索引的，我们称这种SQL为覆盖索引:

select NAME  from student_info  order by name,number

索引失效的场景

• 排序列使用了复杂的表达式

select  * from student_info  order  by UPPER(name)

按name这一列进行排序形成的B+树和按name的全大写形成的B+树，是不一样的。这样的情况下，MySQL完全没有办法用到我们建立的联合索引。

• asc、desc混用

对于使用联合索引进行排序的场景，我们要求各个排序列的排列顺序是一致定位，也就是要么各个列都是按ASC规则排，要么都是按照DESC规则排序。ORDER BY子句后的列不加ASC或者DESC默认是按照ASC排序规则排序的，也就是升序排序的。

想想这也是蛮自然的事情，我们的索引name_number_phoneNumber的B+树结构是:

1. 先按照记录的name列值进行升序排列
2. name列相同，再按照number进行升序排序
3. number相同，再按照phoneNumber升序排列

ORDER BY name,number limit 10.
这就省去了排序，从左往右直接取记录就行

ORDER BY name DESC, number DESC LIMIT 10，
从右往左取记录即可

如果是order by name asc，number desc limit 10。这种情况首先与B+树的索引列顺序是不一致的，很难用到B+树的索引，如果要用的过程话，大致是这样的:

1. 先从B+树最左边开始确定最小列的值，然后找到等于该值的所有记录(name相同才按照number排序)。然后从name列等于该值的最右边那条记录开始往左找10条记录。
   
2. 如果name列等于最小的值记录不足10条，再继续找name值第二小的记录，重复上述的过程。如果遍历了整个name列还是无法找到
   name相同的10条记录，name按照name升序取10条出来。
   

这样的匹配过程不能高效的使用索引，MySQL的开发人员认为这样还不如不走索引。所以就有了上面规定，使用联合索引的索引列进行排序时，各个排序列必须和联合索引中的索引列顺序相同。

• 排序列包含非同一个索引的列

order by name,score

name 和 score 是两棵B+树，走索引该怎么走呢？合并两棵B+树，将name和score做成联合索引？似乎太麻烦了，还是不走索引了。

• 分组顺序和索引顺序不同

分组也是我们写SQL的日常操作，比如像下面这样:

SELECT name,phoner_Numer,count(1) FROM student_info  group by name, phoner_Numer

这个语句事实上做了两次分组，即先按name进行分组，所有name相同的记录算做一组。

在每个name值相同的组内，再phoner_numer算做一组。所以从整体上来看，像是先把记录分成一个大分组，然后把大分组分成若干个小组。

如果这两列不是索引列，那么就需要走全表扫描进行分组。有了做好的索引，MySQL走索引即可。

如果调整了分组顺序，即分组顺序和索引列顺序不一样，我们就无法根据索引来查找数据。

• 让索引列在比较表达式中单独

1. SELECT * FROM student_info  where score *  2 = 4
2. SELECT * FROM student_info  where score  = 4 / 2

1. 是用不到索引的，2可以用到，尽管1和2等价，score上我们加了索引，按score从小到大排序，让score乘以2去匹配? 如果是更复杂的表达式呢，我再用这个表达式的列再形成一颗B+树？还不如走全表扫描呢。

回表的代价

我们知道二级索引的叶子结点只存储了索引列和主键，所以我们如果想找非索引列，MySQL就需要用索引列对应的主键回到聚簇索引对应的B+树找到其他列。

以下面这个SQL为例:

SELECT * FROM student_info  where '张'  < name  and name < '李'

在这个查询过程中，用到了两棵索引，name和number对应联合索引B+树，回表查其他列的以主键做为索引的B+树。name_number_phoneNumber索引会首先按照name进行排序，所以张和李之间的记录在磁盘上的存储，逻辑上是相连的，集中分布在一个数据页或几个数据页中，这种读取方式我们称之为顺序I/O。这一步拿到记录对应的主键可能不是连续的,所以根据这些并不连续的id值到聚簇索引中访问到完整的用户记录可能分布在不同的数据页中，为了匹配记录，我们可能要读取更多的数据页，这种读取方式我们称之为随机I/O. 一般来说，顺序I/O比随机I/O的性能要高很多。

需要回表的记录越多，使用二级索引获得的性能提升就越低，在某些情况下，MySQL宁愿扫描全表也不走二级索引。比如说name值在张和李之间的记录占全表记录数量的百分之九十以上，那么还使用我们建立的联合索引的话，百分之九十的id值需要回表，这完全得不偿失，还不如扫描全表。

那什么时候采取全表扫描，什么时候采用二级索引+回表的方式去执行查询呢？由查询优化器来裁定，查询优化器会事先对表中的记录计算一些统计数据，然后利用这些统计数据根据查询条件来计算一下需要回表的记录书，需要回表的记录数越多，查询优化器越倾向于做全表索引。一般情况下，限制查询获取较少的记录数会让优化器倾向于走二级索引+回表来进行查询，回表记录越少，性能提升越多。

索引的代价

我们知道索引对应一颗B+树是需要占用存储空间的，每建立一个索引都要为它建立一颗B+树，每一棵B+树的结点都是一个数据页，一个数据页会占用16KB的创业板成功空间。

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引，B+树的每层结点都是按照索引列的值都从小到大组成了双向链表。而为了维护顺序，存储引擎会花一些时间对记录移位，页面分裂、页面回收。假如你建了很多索引，那么存储引擎需要对每个索引对应的B+树做相关的的维护操作。这也会影响性能。

如何挑选索引

• 选择基数大的列，
  列的基数指的是某一列中不重复数据的个数, 选择一列建立索引可以理解为为该列建立一颗B+树，并且按照该列进行排序，
  从这个意义上来讲，重复项越多，遍历的就越多，二分查找所发挥的余地就少。
  
• 索引列的类型尽量小
  数据类型越小，在查询的时候进行比较操作越快。
  数据类型越小，索引占用的存储空间越少，一个数据页就可以放下更多的记录，从而减少磁盘I/O带来的性能消耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
  
• 尽可能的索引字符串值的前缀
  一个字符串其实由若干字符组成，如果我们在MySQL中使用utf8字符集去存储字符串的话，编码一个字符需要1~3个字节。假设我们的字符串很长，那么存储就需要占用很大存储空间。对于字符串列对应的B+树就会有以下两个问题:
  对于这种情况，我们可以只对字符串列的前几个字符建立索引，这样在匹配记录的时候，虽然根据字符串去匹配的时候，只能定位到相应前缀的所在的位置，然后根据前缀相同的记录的主键值查询完整的字符串，再对比就好了。我们在建表语句对name列的前十个字符进行索引可以这么写:

create table student_info(
  id int,
  name varchar(100) NOT NULL,
  number varchar(100) NOT NULL,
  phonerNumer varchar(100) NOT NULL,
    score int,
  PRIMARY KEY(id),
  index name_number_phoneNumber(name(10),number,phonerNumer)
)

• 但是这种方案有缺陷，如果我们想对name列进行排序，我们只对name的前10个字符建立了索引，无法对前十个字符相同，后边的字符不同的记录进行排序，只能扫描全表。

• B+树中索引中的记录需要把该列的完整字符串存储起来，字符串越大，数据页中存储的记录越少，占用空间越大。
• 如果B+树索引中索引列存储的字符串很长，那么在做字符串比较时会占用更多的时间。

• 为搜索、排序或分组的列创建索引
  其他非搜索列、排序列、分组列的列可以借助搜索列的回表操作得到，因此我们只为搜索、排序或分组的列创建索引，索引加快查找速度。
  

写在最后

本篇的写作思路基本上是我对自己提问，然后去《MySQL 是怎样运行的：从根儿上理解 MySQL》找对应的答案，找到答案之后，按照自己的方式组合起来。所以跟《MySQL 是怎样运行的：从根儿上理解 MySQL》会很像，可以说本篇是《MySQL 是怎样运行的：从根儿上理解 MySQL》的读书笔记吧，写本篇是因为我只想从中取走索引而已。