【从面试题建立知识体系】Mysql面试题------------Sql优化流程（一）

关于SQL优化通常最先会想到的是MySql的索引。首先说结论，当发现一条慢SQL时通过explan执行计划查看sql是否有走索引，如果发现没有走索引的话，是因为查询条件中没有索引字段，还是因为不符合索引匹配规则导致走了全表扫描。如果是因为where条件中没带索引字段是否需要合理添加索引，如果where条件中带了索引的话，为什么mysql没有走索引。

mysql中索引是指帮助Mysql高效快速获取数据一种排好序的数据结构。
索引的作用类似一本书的目录，通过目录我们可以快速定位到我们需要到达的页面数。那么在数据库中我们就可以通过索引快速找到我们需要的数据。形象来说索引就是数据的目录。

当我们执行一条SQL语句的时候where条件中没有带上索引字段的话，那么mysql只能通过全表扫描将所有的数据全部从磁盘中读取出来并且一条条遍历比对是否符合查询条件来确认是否属于目标数据。当表中数据过多时全表扫描效率是非常低下的，并且mysql中存储是以页为单位来的，每页的大小默认是16kb，当进行全表扫描时需要进行大量的磁盘IO，将磁盘中的数据读取到内存，这个操作是非常耗时的。所以为了避免全表扫描，通过合理使用建立索引来达到快速定位目标数据。

1. 按数据结构分类

从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。
每一种存储引擎支持的索引类型不一定相同，我在表中总结了 MySQL 常见的存储引擎 InnoDB、MyISAM 和 Memory 分别支持的索引类型。

2. 按物理存储分类

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

• 聚簇索引
  如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
  如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
  在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；
  主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
• 二级索引
  二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。通过聚簇索引的主键值回表定位到具体需要的数据位置。

3. 按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

• 主键索引
  主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

• 唯一索引
  唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

• 普通索引
  普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

• 前缀索引
  前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
  使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

4. 按字段长度分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。
建立在单列上的索引称为单列索引，比如主键索引；
建立在多列上的索引称为联合索引；

• 二叉树
  二叉树会以第一个插入的数据作为根节点来执行后续的存储，在极端情况下，例如顺序插入时，二叉树会有严重倾斜的情况，从而变成了链表的结构。最坏情况可能时间复杂度达到O(n)。并且节点过深的情况会造成IO严重的情况，而通常IO就是查询的瓶颈。
  

• Hash
  hash结构的检索效率比较优秀，一次查询就可以定位到数据，但是可能会存在hash冲突的问题，并且无法支持范围查询。

• 红黑树
  红黑树是一颗平衡二叉树，树的深度比二叉树提升了很多，但是由于每个节点只有两个子节点，在大数据下，比如一千万的数据，这颗红黑树的高度就很高了。所以红黑树依然不能满足mysql索引的要求

• 平衡查找树(B树)
  B树即平衡查找树，一般理解为平衡多路查找树。它所有的索引元素不重复，节点中的数据索引从左到右递增排列
  B树在非叶子节点上冗余存了date数据，且包含key值。
  当存储的数据量很大的时候会导致深度变大，增加查询磁盘的io次数，进而影响查询性能
  

• B+树
  B+树是在B树的基础上做的一种优化，变化如下：
  B+树每个节点可以包含更多的节点，这个做的原因有两个，第一个原因是为了降低树的高度，第二个原因是将数据范围变成多个区间，区间越多，数据检索越快。
  非叶子节点只存储key，叶子节点存储key和数据。
  叶子节点通过指针连接(符合磁盘预读的特性)，顺序查询性能更高
  

数据类型的每一个节点其实就是存储引擎的最小存储单位(页)，每查找一个节点就是跟磁盘进行一次IO的交互操作，提高检索效率那么就需要减少跟磁盘的交互操作，结合以上的数据结构来看，B+树符合存储最优的特性。

B+树在B树上进行优化，保留了B树一个子节点可以存储多个元素的特性，同时在叶子节点上存储了所有的索引元素，通过双向指针连接构成一个双向链表的结构。并且排好序依次递增指向下一个元素。在innoDB中，假设指定索引key为int类型，在存储分表为8byte，节点存储地址空间大小为6byte，每个页节点16kb可以存储1170个索引元素。叶子节点中假设data存储空间为1kb，那么每个叶子节点可以存储16个索引元素，可以得出当一棵高度为3的b+树，可以存储约2千万左右的索引元素。

B+树中叶子节点的存储的实际上就是页数据。


这7个部分的作用如下：

数据页的文件头

在文件头中的指针构成了页的双向链表结构，采用链表的结构是让数据页之间不需要是物理上的连续的，而是逻辑上的连续。

数据页的用户数据区域

数据页的主要作用是存储记录，也就是数据库的数据。
数据页的记录按照主键顺序组成单向链表，单向链表的特点就是插入，删除非常方便，但是检索效率不高，最差的情况需要遍历链表上的所有节点才能完成检索。
因此，数据页有一个页目录，起到记录的索引作用。

页目录创建的过程如下：
1.将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为"已删除"的记录；
2.每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为n_owned字段(上图中粉色字段)。
3.页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽，每个槽相当于指针指向了不同组的最后一个记录。

从图可以看到，页目录就是由多个槽组成的，槽相当于分组记录的索引。然后因为记录是按照主键值从小到大排序的，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，在遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。

当主键为非自增UUID等插入时会发生什么

当我们使用雪花算法等自增有序的数据作为id来存储时，一条数据需要写入到表中，由于id是有序且自增的那么在插入时只需要获取到当前表中最大id所存在的位置，再将next指针指向当前数据即可。mysql的定位寻址效率很高不需要其他过多的操作。并且由于每次一的插入都是在上一次操作之后的节点位置，主键页会几近于填满状态，极大的节省了空间以及减少碎片的产生。

那么当使用非自增类型的主键进行数据行新增时会发生什么。

首先由于无法保证当前数据的主键一定是最大的，因此需要先定位到当前主键需要插入哪一页中。并且当前页可能并不存在于内存中，需要先将数据页读取到内存中。由于数据库中的页并不是物理上连续性的，而是逻辑连续，所以当插入一条非自增性的主键数据时将会产生大量的随机IO，这是一个相当耗时的过程。且由于因为新增的数据插入到原本已存在数据的位置上，这个过程会发生页分裂。

由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片。在把随机值载入到聚簇索引以后,有时候会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充，这将又需要一定的时间消耗。这个过程被称为页合并。

因为一个数据页中的记录是有限的，且主键值是有序的，所以通过对所有记录进行分组，然后将组号(槽号)存储到页目录，使其起到索引的作用，通过二分查找的方法快速检索到记录在哪个分组，来降低检索的时间复杂度。

但是，当我们需要存储大量记录时，就需要多个数据也，这时我们需要考虑如何建立合适的索引才能方便定位记录所在的页。

为了解决这个问题，InnoDB采用了B+树作为索引结构。磁盘的IO次数对索引的使用至关重要，因此在构建索引时，更倾向于采用矮胖的B+树数据结构，这样所需要进行的磁盘IO次数更少，而且B+树更适合进行关键字的范围查询。

InnoDB里的B+树中的每个节点都是一个数据页，示意图如下。

通过上图，我们看出 B+ 树的特点：

只有叶子节点（最底层的节点）才存放了数据，非叶子节点（其他上层节）仅用来存放目录项作为索引。
非叶子节点分为不同层次，通过分层来降低每一层的搜索量；
所有节点按照索引键大小排序，构成一个双向链表，便于范围查询；
我们再看看 B+ 树如何实现快速查找主键为 6 的记录，以上图为例子：

从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为 6，在[1, 7)范围之间，所以到页 30 中查找更详细的目录项；
在非叶子节点（页30）中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于 5，所以就到叶子节点（页16）查找记录；
接着，在叶子节点（页16）中，通过槽查找记录时，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为 6 的记录。
可以看到，在定位记录所在哪一个页时，也是通过二分法快速定位到包含该记录的页。定位到该页后，又会在该页内进行二分法快速定位记录所在的分组（槽号），最后在分组内进行遍历查找。

索引下推是索引下推是 MySQL 5.6 及以上版本上推出的，用于对查询进行优化。

假设现在有这样一条sql语句，使用联合索引（name，age）。
select * from t_user where name like ‘L%’ and age = 17;

这条语句从最左匹配原则上来说是不符合的，原因在于只有name用的索引，但是age并没有用到。

那么在执行这条语句时，存储引擎首先通过name列的索引去查找到符合条件的数据，由于单列索引存储的数据并不是完整的，而当前需要查询为全部字段，那么就需要根据当前符合条件的数据的主键索引值一个个查询聚簇索引获取到对应的字段数据，这个过程被称为回表，在通过聚簇索引中age列过滤掉数据，剩下目标数据返回。

由于大量的回表可能造成查询效率的低下，在开启索引下推后。通过name列索引获取到数据，在根据 age = 17 这个条件进行过滤，过滤掉排除掉大部分不符合数据，在进行回表。索引下推的方式极大的减少了回表次数，从而提高了查询的效率。

假设执行一条sql,对name列加上索引
select name,age from t_user where name = ‘zhangsan’;
那么在查询索引时获取不到age数据，需要通过回表来获取到age列。
而如果使用联合索引，直接在索引中就可以得到查询结果，从而不需要回表查询聚簇索引中的行数据信息。

索引覆盖可以带来很多的好处：

辅助索引不包含行数据的所有信息，故其大小远小于聚簇索引，因此可以减少大量的IO操作。
索引覆盖只需要扫描一次索引树，不需要回表扫描聚簇索引树，所以性能比回表查询要高。
索引中列值是按顺序存储的，索引覆盖能避免范围查询回表带来的大量随机IO操作。

什么是最左匹配原则？我们知道索引使用到的数据结构是B+树，是一种有序的数据结构，那么当我们建立联合索引(a,b)时，优先通过a列排序，a列是有序的，而b列是相对无序的。当需要使用到联合索引时，必须where条件中存在a列才能使用到联合索引，反之则失效。最左匹配原则与列查询顺序无关，由于有优化器的存在，因此where条件的顺序不会影响最左匹配原则。简单来说在联合索引中，只有左边的字段被用到，右边的才能够被使用到。

当需要对查询语句优化时通常会索引来提高查询效率，我们知道索引是一种高效的存储结构，不过当查询语句写法存在问题时依然可能出现走的是全表扫描而不是走的索引，那么什么时候会导致索引失效？

1. 对索引使用左或者左右模糊匹配
   当我们对一条查询语句使用like进行模糊匹配时，like %xx 或者 like %xx% 这两种方式都会造成索引失效。由于索引列是有序排序的，当使用到最右匹配或者中间匹配时，索引列匹配不上，会走全表扫描

2. 对索引使用函数
   查询条件中对索引字段使用函数，就会导致索引失效。

   // name 为二级索引
   select * from t_user where length(name)=6;

   为什么对索引使用函数，就无法走索引了呢？

   因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

   不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

   举个例子，我通过下面这条语句，对 length(name) 的计算结果建立一个名为 idx_name_length 的索引。
   alter table t_user add key idx_name_length ((length(name)));
   然后我再用下面这条查询语句，这时候就会走索引了。

3. 对索引进行表达式计算
   在查询条件中对索引进行表达式计算，也是无法走索引的。

比如，下面这条查询语句，执行计划中 type = ALL，说明是通过全表扫描的方式查询数据的：

explain select * from t_user where id + 1 = 10;

但是，如果把查询语句的条件改成 where id = 10 - 1，这样就不是在索引字段进行表达式计算了，于是就可以走索引查询了。

为什么对索引进行表达式计算，就无法走索引了呢？

原因跟对索引使用函数差不多。

因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

有的同学可能会说，这种对索引进行简单的表达式计算，在代码特殊处理下，应该是可以做到索引扫描的，比方将 id + 1 = 10 变成 id = 10 - 1。

是的，是能够实现，但是 MySQL 还是偷了这个懒，没有实现。

我的想法是，可能也是因为，表达式计算的情况多种多样，每种都要考虑的话，代码可能会很臃肿，所以干脆将这种索引失效的场景告诉程序员，让程序员自己保证在查询条件中不要对索引进行表达式计算。

4. 对索引隐式类型转换
   如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描。

   我在原本的 t_user 表增加了 phone 字段，是二级索引且类型是 varchar。

然后我在条件查询中，用整型作为输入参数，此时执行计划中 type = ALL，所以是通过全表扫描来查询数据的。

但是如果索引字段是整型类型，查询条件中的输入参数即使字符串，是不会导致索引失效，还是可以走索引扫描。

我们再看第二个例子，id 是整型，但是下面这条语句还是走了索引扫描的。

为什么第一个例子会导致索引失效，而第二例子不会呢？

要明白这个原因，首先我们要知道 MySQL 的数据类型转换规则是什么？就是看 MySQL 是会将字符串转成数字处理，还是将数字转换成字符串处理。

我在看《mysql45讲的时候》看到一个简单的测试方式，就是通过 select “10” > 9 的结果来知道MySQL 的数据类型转换规则是什么：

如果规则是 MySQL 会将自动「字符串」转换成「数字」，就相当于 select 10 > 9，这个就是数字比较，所以结果应该是 1；
如果规则是 MySQL 会将自动「数字」转换成「字符串」，就相当于 select “10” > “9”，这个是字符串比较，字符串比较大小是逐位从高位到低位逐个比较（按ascii码） ，那么"10"字符串相当于 “1”和“0”字符的组合，所以先是拿 “1” 字符和 “9” 字符比较，因为 “1” 字符比 “9” 字符小，所以结果应该是 0。

上面的结果为 1，说明 MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。

前面的例子一中的查询语句，我也跟大家说了是会走全表扫描：

//例子一的查询语句

这是因为 phone 字段为字符串，所以 MySQL 要会自动把字符串转为数字，所以这条语句相当于：

select * from t_user where CAST(phone AS signed int) = 1300000001;
可以看到，CAST 函数是作用在了 phone 字段，而 phone 字段是索引，也就是对索引使用了函数！而前面我们也说了，对索引使用函数是会导致索引失效的。

例子二中的查询语句，我跟大家说了是会走索引扫描：

//例子二的查询语句
select * from t_user where id = “1”;
这时因为字符串部分是输入参数，也就需要将字符串转为数字，所以这条语句相当于：

select * from t_user where id = CAST(“1” AS signed int);
可以看到，索引字段并没有用任何函数，CAST 函数是用在了输入参数，因此是可以走索引扫描的。
5. 联合索引非最左匹配

对主键字段建立的索引叫做聚簇索引，对普通字段建立的索引叫做二级索引。

那么多个普通字段组合在一起创建的索引就叫做联合索引，也叫组合索引。

创建联合索引时，我们需要注意创建时的顺序问题，因为联合索引 (a, b, c) 和 (c, b, a) 在使用的时候会存在差别。

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

有一个比较特殊的查询条件：where a = 1 and c = 3 ，符合最左匹配吗？

这种其实严格意义上来说是属于索引截断，不同版本处理方式也不一样。

MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，Server 层从存储引擎层获取到数据行后，然后在 Server 层再比对 c 字段的值。

从 MySQL 5.6 之后，有一个索引下推功能，可以在存储引擎层进行索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返还给 Server 层，从而减少回表次数。

索引下推的大概原理是：截断的字段不会在 Server 层进行条件判断，而是会被下推到「存储引擎层」进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

比如下面这条 where a = 1 and c = 0 语句，我们可以从执行计划中的

图片

为什么联合索引不遵循最左匹配原则就会失效？

原因是，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。
6. WHERe 子句中的 OR
在 WHERe 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

举个例子，比如下面的查询语句，id 是主键，age 是普通列，从执行计划的结果看，是走了全表扫描。

这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

要解决办法很简单，将 age 字段设置为索引即可。

可以看到 type=index merge， index merge 的意思就是对 id 和 age 分别进行了扫描，然后将这两个结果集进行了合并，这样做的好处就是避免了全表扫描。

当我们需要对一条SQL语句去进行优化，对于索引部分基本可以分为几种情况。

1. 未合理添加索引
   对于一些高频使用的字段，在进行查询时未建立索引，mysql在执行检索时不存在索引树，只能通过全表扫描来筛选目标数据，由于innoDB引擎数据存储在磁盘文件(页)中，需要与磁盘交互产生大量IO读取到内存来进行逐一比对，耗时非常大。通过合理建立索引来提高查询效率，需要注意的是，对于字段存储值重复率较高的情况的，优化器会认为全表扫描效率更高，因为大量重复相等值可能会需要存在不断回表的情况，因此并不会走索引查询，并且在进行增删改操作需要对该索引树进行维护更新，增加了额外的操作时间。因此需要对该字段判断是否添加索引。
2. 未正确使用索引
   • 大量回表
     当我们对某一列加上单列索引或者联合索引时，可能发生回表的情况，例如使用select * 查询时，查询条件中使用到了索引，且通过执行计划查看时也是使用到了索引，由于索引中并非存储了完整的用户数据，需要通过索引中存储的主键索引值逐一去主键索引中获取到相应的数据。当数据量较大时，过多的回表也会导致查询效率的低下。因此当我们查询时应尽量避免检索全部字段，而是使用到哪些字段就查询哪些字段，避免出现大量的回表情况。
   • 未按照最左匹配原则
     在我们建立索引时会通过经常组合一起查询的字段建立联合索引，当sql语句未正确使用索引查询时，会导致优化器走全表扫描，具体失效逻辑见文中最左匹配原则部分。
   • 未使用索引下推 本文地址：http://dfvalve.xrbh.cn/quote/2924.html    迅博思语资讯 http://dfvalve.xrbh.cn/ , 查看更多