深入浅出Mysql索引优化专题分享|面试怪圈

文章纲要

该文章结合18张手绘图例，21个SQL经典案例、近10000字，将Mysql索引优化经验予以总结，你可以根据纲要来决定是否继续阅读，完成这篇文章大概需要25-30分钟，相信你的坚持是不负时光的：

  • 前言
  • 开篇小例子
    单索引性能最优？
    索引越多越好？
  • 常用术语
    主键索引（聚簇索引）
    辅助索引
    覆盖索引
    最左匹配
    索引下推
  • 再谈优化
    覆盖索引，减少回表
    遵循最左匹配原则

    联合索引，字段顺序
    前缀索引
    索引失效
    大字段影响检索性能
    GROUP BY如何优化
    ORDER BY如何优化
    分页性能优化
    ORDER BY再分页BUG
    JOIN性能优化
  • 写在最后

前言

mysql是我们最常用的数据库，基本很多业务系统都在使用。可是往往在遇到性能问题的时候，总是束手无策。比如：

  • 明明知道有索引的概念，却不知道这样加索引是否能够真正生效？
  • 有的时候，想继续增加索引却又担心索引加的太多，那索引最多能加几个，加的太多有没有什么影响呢？
  • 一个表的多个索引中经常出现一些重复的字段，他们到底存在有没有意义？还是冗余的索引呢？

经过一周的梳理，我将工作中最常用的索引优化手段和方法梳理出来，足以解释上述疑问。同时，相信你跟着我的思路来阅读这篇文章，你对mysql索引的理解会有一个更高的层次提升，在工作中不再茫然。

今天这篇文章是根据我在京东内部分享的ppt整理而来，从很多很多角度来看待索引优化的问题，比如：索引为什么失效、order by的性能提升与避雷、group by是否能够提升性能、深分页存在哪些问题及如何优化、join的时候如何选择驱动表等。

除此之外呢，面试中也会提及一些常见的关于索引的概念，这篇文章也会通过一些例子来帮助你深入浅出索引中的奥秘，比如：索引下推、覆盖索引和回表等。

同时，通过这篇文章的理解，你在使用其他数据库，比如mongo或者类似的索引类型，也可以举一反三。文中有大量的例子和SQL语句以及执行的原理。如果你肯花上一点时间来跟我一起理解。相信你在SQL优化领域会与众不同！开始吧！

下文主要针对InnoDB存储引擎的B+树作为前提来阐述，不再敖述。

开篇小例子

为了能够更好地理解后续章节的优化介绍，我先通过一个小例子，让大家明白一个简单的查询语句的执行过程、逻辑及原理。

这里所说的执行过程不是指：Mysql语法词法解析器、优化器、执行器等宏观的维度，而是偏向索引树的维度。

我们依旧采用大家最熟悉的学生表(student)来举例吧，看下图：

图1：student表及索引说明

学生表，包含：id、number（学号）、name（姓名）、sex（性别）、age（年龄），并且id为主键，其他字段分别有一个单独索引。

类似这样的索引设计，在我的工作中经常遇到，当然也随着查询逻辑的复杂性提升，这种单字段索引也会变得越来越多。之所以出现这样的情况，是对索引的理解和用法并不深入导致的，在茫然的时候选择了：加单索引来解决性能问题的方法。

我猜大家可能会有这样的两个疑问：

  • 创建单字段索引性能最优？如果不是，那该如何加索引呢?
  • 是不是索引越多越好？因为越多越容易命中？

单索引性能最优？

首先来看单索引性能是否最优？为了说明这个问题，我还是先准备一点数据帮助理解：

图2：student表数据准备

student表有很多数据，1~7条数据如上图所示，其中第一条黄色背景的数据，是如下SQL的命中结果：

SELECT * 
  FROM student 
 WHERE age = 7 
   AND sex = '男' ;

那这条SQL语句是如何执行的呢？前面我们给这个表加了4个非主键索引，既然我们用了两个查询条件，因此，为了提升检索性能，mysql的优化器会选择其中的一个索引树去查找。这里我们做一个假设，假设优化器选择idx_age这个索引,当然选择idx_sex这个索引，跟我们下面阐述的原理是一样的。

我手绘了idx_age和主键索引的简图，辅助理解：

图3：主键索引简图

图4：idx_age索引简图

  • 主键索引
    主键索引的叶子节点17是主键id，它下方的R1R7是我对行记录（也就是全字段内容）的简写。其中，红色的背景R1就是查询命中的结果。
  • idx_age索引
    idx_age为非聚簇索引，索引的叶子节点为年龄+主键Id，顺便提一下，不知道你有没有考虑为什么该索引的叶子节点不直接挂的是行记录呢？ 我想原因有二：1.主键已经有行记录，再次存储占用额外的空间，如果二级索引更多，存储冗余就更大 2.mysql的存储以page为存储单元，如果单索引键空间占用更多，一个page能容纳的键更少，导致树更深，检索需要更多的IO访问。

回过头说下，上述语句的执行过程吧：

  • 1.在age索引树查找age=7的记录，取得ID=1;
  • 2.继续到主键索引树查找ID=1对应的数据R1，判断R1.sex等于’男’，返回到结果集中;
  • 3.在age索引树查找下一个age=7的记录，取得ID=2;
  • 4.继续到主键索引树查找ID=2对应的数据R2，判断R2.sex不等于’男’，丢弃;
  • 5.继续第3步和第4步，发现ID=3的记录也不满足条件
  • 6.当在age索引树上找到age=10的记录时，不满足条件，循环结束。

你会发现，整个查找执行的过程中，查询主键索引树3次，查询主键索引的目的有两个：一是为了查询sex是否满足条件，二是为了返回需要的全字段。

单索引执行是这样，那我们继续看一下，如果我们基于SQL语句的两个查询字段age、sex建立联合索引，执行过程是怎样的呢？是否会提升性能呢？

同样，我手绘了一张联合索引的简图如下：

图5：idx_age_sex联合索引简图

联合索引中(5,'男')代表一个索引键 ，5是年龄,’男’是性别。同样叶子节点的绿色阴影部分为Id值。

联合索引的执行情况是这样的：

  • 1.在联合索引树上查找age=7并且sex=‘男’的记录ID=1;
  • 2.继续到主键索引树查找ID=1对应的数据R1, 返回到结果集中。

你会发现这次执行仅仅为了返回需要的全字段,才执行了一次主键索引树的查询，比单字段索引少了2次。少的这两次主键索引树查询你知道意味着什么吗？ 减少了至少两次的IO访问（因为mysql为了提升性能会将部分页缓存，暂时忽略这种情况）。我们都知道IO、CPU、内存是mysql性能优化的几大主要影响因素和考虑点。

因此，我们可以得出一个结论：使用联合索引能提升索引命中率，减少回表筛选带来的IO损耗。相反单索引需要更多的回表次数。

索引越多越好？

可能有人有这样的索引误区，索引越多越容易命中？是不是这样的呢？首先看下增加索引会带来什么影响呢？

  • 1.索引需要占用存储空间
    索引是一种性能优化的数据结构，本身也是一种采用空间换时间的思路来提升查询性能。因此，增加索引的数量一定会导致对应的增加存储空间。

  • 2.索引更新需要更多维护成本
    我们一直在讨论索引的查询，可我们不能忽略的一点是，当Insert、Update、Delete等操作也带来索引的更新和维护，因此，索引的数量也会带来更多的维护成本，你说呢？

因此，综合所有该表的SQL的查询条件，合理规划索引的个数，避免冗余索引的出现，有助于降低维护成本。比如下面的索引，可以将左图两个索引优化为右图一个索引：

图6：冗余索引去除

至于为什么可以这样优化，通过阅读下文，你会得到答案。

常用术语

围绕着索引有很多术语，也许你经常听到或者在面试中被问到，但是却没有理解是什么意思。但是为了提升自身逼格和自我的虚荣心满足，那么，我们一起回顾下吧：

  • 主键索引

以下是student表的主键索引，它也叫做一级索引。有的时候也有人称它为聚簇索引，聚簇索引是因叶子节点的id和行数据聚簇在一起而得名，如下图：

图7：主键索引

  • 辅助索引

而与主键索引的对应的就是辅助索引，它也叫做二级索引。由于叶子节点上无行数据，只有一个id，因此它是非聚簇索引。

图8：辅助索引简图

  • 覆盖索引

覆盖索引并不是一种索引类型，而是一种索引查询的形式和行为。覆盖索引往往应用于联合索引。下图就是一个由age、sex组成的联合索引：

图9：联合索引简图

当查询条件运用了索引，并且SELECT的字段也覆盖在该索引树上，也就是一颗索引树既满足了检索也满足了结果，无需为了拿到需要的SELECT字段而去回表的一种方式。

细心的你会发现id在SELECT的字段中，也是可以走覆盖索引的。
以下SQL就是运用了覆盖索引的例子：

SELECT sex,age,id FROM student WHERE age=7;
  • 最左匹配

建立联合索引的时候是否要考虑字段的顺序？比如idx_A_B和idx_B_A是一样的吗？答案是否！
idx_A_B可以满足以下两个SQL走索引：

SELECT * FROM　t WHERE A=1 AND B=1;
SELECT * FROM　t WHERE A=1;

而idx_B_A可以满足这两个SQL走索引：

SELECT * FROM　t WHERE A=1 AND B=1;
SELECT * FROM　t WHERE B=1;

顺序不同导致的效果也截然不同。查询条件只能根据索引由左到右的顺序来匹配索引，而不可以跨索引字段。

提示：WHERE A=1 AND B=1 或者WHERE B=1 AND A=1是没有任何区别的。优化器已经帮助我们做好了优化。

  • 索引下推

我们来看下在下图这个联合索引前提下，根据文章开头给出的数据样例，看看这个SQL在不同的MYSQL版本中如何执行的呢？

图10：联合索引idx_name_age
SELECT name
 FROM student
WHERE name like ‘小%’                   
  AND age=7

5.6之前的版本

  • 1.根据name从联合索引查找到7条name以“小”开头的记录的ID；
  • 2.根据ID回表到主键索引查找全字段，筛选age=7的记录，返回。

图11：5.6之前服务层与引擎层流程图

因此，回表7次。

5.6及之后的版本

  • 1.根据name从联合索引查找到7条name以“小”开头的记录，由于索引上存储了age字段，因此在该索引上就可以过滤出age=7的记录，查找到符合条件的3条记录的ID。
  • 2.根据ID回表到主键索引查找全字段，返回结果集。

图12：5.6及以后服务层与引擎层流程图

因此，回表3次。

以上描述的就是索引下推，你可能会有疑问，为什么叫下推？不是左推、右推、上推呢?其实，这个概念是相对MYSQL的层次划分的，将MYSQL的服务层下推到存储引擎层来过滤。索引下推少了5和8这两步，这两步也就是服务端参与的，将age=7在存储引擎层完成了过滤。

再谈优化

覆盖索引，减少回表

最常用的查询操作就是Select * 操作，如果在二级索引进行条件筛选，但为了获取
全部字段，需要回表操作，前面提过，回表越多，性能较差。因此，按需select字段，让where后的条件字段和select字段覆盖索引减少回表次数，是非常重要的优化手段。

遵循最左匹配原则

前面讲到最左匹配原则，涉及两点：

假设student表上有这样的索引：idx_name_age。

  • 字段从左向右匹配，如下：
## 能使用索引的name部分
SELECT * FROM student WHERE name = '小一';

## 无法使用索引
SELECT * FROM student WHERE age = 7;
  • 字符从左向右匹配，如下：
## 能使用索引的name部分
SELECT * FROM student WHERE name like '小%';

## 无法使用索引
SELECT * FROM student WHERE name like '%小';

因此，我建议大家在设计索引的时候一定要考虑该原则，保证索引设计的合理性。不仅仅该原则涉及到索引的设计，同时也涉及到功能的设计。比如，某表数据量较大，产品建议左右模糊匹配，出于性能考虑，可以建议产品的设计改为仅使用右模糊匹配。

联合索引，字段顺序

往往建立联合索引，不管是idx_A_B或者idx_B_A都能满足设计要求，那么联合索引字段的顺序，怎样设计才是最合理的？才能够更长远呢？这里我给出两点参考建议：

  • 考虑索引的复用能力

复用能力很好理解，比如查询有这几种情况：A=1 AND B=1 AND C=1、A=1 AND B=1、A=1或者A>=1 再或者A=1 AND B>=1等等，这些情况下，索引idx_A_B_C肯定是最合适，相反，idx_B_A_C或者idx_A_C_B等的复用能力要差一些。不妨，你使用上面讲到的最左原则来思考下吧。

  • 考虑空间的占用情况

为了说明这点，先看下面这些SQL：

SELECT * FROM student WHERE name ='小一' and age = 7;
SELECT * FROM student WHERE name ='小一';
SELECT * FROM student WHERE age = 7;

满足这些SQL可以有以下两种设计，你可以先思考下，你会选择哪一种呢？

图13：索引1和索引2设计

你会发现索引1和索引2都可以满足三个SQL，从复用能力上来说是等同的，那该如何抉择呢？答案是可以从储存空间占用上考虑。

idx_name_age和idx_age_name字段相同，空间占用没有太大差别，而idx_age和idx_name一个为int类型，一个为varchar(10)类型，int占用4字节，而varchar(10)占用32字节，相差了8倍，相信选择哪一个索引，你已经有了答案。

一个非空的varchar字段，在UTF8编码下的长度计算公式为：3*len+2。

前缀索引

联合索引字段顺序，我们提及了存储空间的考虑。不知道你没有发现，在邮箱字段上建立索引相同的后缀占用了重复的空间。比如@xixihaha.com,那是否考虑仅使用前缀呢？

图14：邮箱前缀索引

就像上图这样去设计索引，可以节省空间。但是要重点考虑选择前缀的大小，比如这个选择5、6还是7合适呢？你可以根据前缀的区分度来考虑。比如我们选择了email(1)这样的前缀，那么区分度只有26个英文字母，显然达不到索引设计预期的效果。

下面是添加前缀索引的语法，你可以参考：

mysql> ALTER TABLE student 
         ADD INDEX idx_email_6(email(6));

索引失效

索引往往会在某些情况下不按照我们预期的执行方式执行，导致失效。我列举了几种常见的失效情况,SQL直白明了，请看以下示例：

  • 索引字段函数操作
SELECT * FROM student WHERE left(name,1) = '小';
  • 索引字段隐式类型强转
    ps:学号为varchar类型。
SELECT * FROM student WHERE number = 2021007;
  • 索引字段运算符操作
SELECT * FROM student WHERE age+1 = 7;
  • 负向查询
    包含：!=、<>、not in、not like、!>、!<等。
SELECT * FROM student WHERE age != 7;
  • 隐字符字符编码
    a表采用uft8编码，b表采用utf8mb4编码，当使用字符串字段进行join操作。
SELECT * FROM t1 a join t2 b on a.name = b.name;

大字段影响检索性能

在设计表时，我们会使用TEXT或者BLOB等类型来存储大文本或者二进制，而这些大字段对查询性能的影响是比较大的。那是为什么呢？

回表查询，需要将整行数据读取，由于大字段占用空间较大，带来大量IO操作，影响数据读取性能。

既然无法回避使用大字段，我有两点建议送给你吧：

  • 覆盖索引
    使用覆盖索引，避免回表对大字段的读取，从而避免带来过多的IO操作。

  • 表超过10万行，将大字段单独放置一张表
    我们在分表策略中，有一种纵向拆分，就是针对此种场景的一种分表设计思路。

GROUP BY如何优化

不知道你有没有使用关键字EXPLAIN去查看GROUP BY操作的执行计划，你会发现在EXTRA字段中出现类似filesort的关键字。这是因为默认情况下，MySQL对所有GROUP BY col1，col2….的字段进行排序，类似在查询中指定 ORDER BY col1，col2…一样。因此，GROUP BY是默认排序的。

因此，我们可以让GROUP BY后的字段利用索引排序，或者你的业务场景不需要排序的情况下，可以使用以下语句禁用默认排序：

SELECT age,count(*) 
  FROM student 
GROUP BY age 
ORDER BY NULL;

ORDER BY如何优化

ORDER BY是最常用的场景，因为很多业务都需要排序，比如取排行TOP5，根据年龄排序，按照创建时间排序等。我们知道索引树是有序的。如果ORDER BY能够使用索引树有序的先天特性，从而避免二次排序带来的时间和空间的复杂度。明显是提升排序性能的重要手段。

因此，MySQL 可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。但需要遵守以下三个原则：

  • WHERE 条件和 ORDER BY 使用相同的索引。
  • ORDER BY 字段的顺序和索引顺序一致。
  • ORDER BY 的字段都是升序或者都是降序。

以下这个排序语句，很好的结合了idx_age_name索引使用的三个原则：

SELECT * 
  FROM student 
WHERE age = 7
ORDER BY age ASC,name ASC;

分页性能优化

深分页的时候，MYSQL查询几秒钟的情况，你遇到过吗？不知道MYSQL在分页时处于何种考虑，LIMIT n,m,这个操作跳过n条数据需要进行回表，导致我们下面这个SQL需要回表10万次。

SELECT * FROM student where age = 10 LIMIT 100000,10

办法总是有的，可换种思路避免这10万次回表，来看SQL的优化吧：

SELECT *
  FROM student s1
INNER JOIN(
  SELECT id FROM student where age = 10 LIMIT 100000,10
) s2 on s1.id = s2.id ;

ORDER BY再分页BUG

工作中，有人被这个BUG坑过吗？ORDER BY后分页，相邻两页存在重复数据。无数次检查SQL和代码逻辑无误，BUG始终无法定位。 这是由于ORDER BY后的字段存在重复值的情况，比如age字段存在重复的值，导致分页时，顺序被打乱。

SELECT * 
  FROM student  
 ORDER BY age ASC 
 LIMIT 1,15;

因此，解决该问题的方法很简单，基于age的排序后增加一个能确定唯一值的排序字段，比如我采用id字段再次排序：

SELECT * 
  FROM student  
 ORDER BY age ASC,ID ASC 
 LIMIT 1,15;

JOIN性能优化

JOIN也是多表关联的常用的关键字，有LEFT JOIN、RIGHT JOIN、JOIN等。在了解JOIN性能优化前，需要明确：驱动表和被驱动表。

  • LEFT JOIN
    左表是驱动表，右表是被驱动表

  • RIGHT JOIN
    右表时驱动表，左表是被驱动表

  • INNER JOIN
    MYSQL会选择数据量比较小的表作为驱动表，大表作为被驱动表

你会发现INNER JOIN的时候，MYSQL选择小表为驱动表，为什么呢？在弄清楚原因之前，我们了解JOIN的三种算法，我们用这个SQL来观察三种算法的执行过程：

SELECT t1.*,t2.* 
  FROM table1 t1 
  LEFT JOIN table2 t2 on t1.a=t2.a;

假设：table1有100行数据，table2有1000行数据。

  • Index Nested-Loop Join（索引嵌套查询连接）

既然是索引嵌套查询连接，那肯定是依赖索引，我们假设这两个表都有索引：idx_a。执行过程是这样的：

1.从表t1中读入一行数据 R1；

2.从数据行R1中，取出a字段到表t2里去查找；

3.根据idx_a索引取出表t2中满足条件的行，跟R1组成一行，作为结果集的一部分；

4.重复执行步骤1到3，直到表t1的末尾循环结束。

你会发现,总扫描行数为：200次，包括遍历t1表的100次和嵌套查询idx_a索引的100次，因此，扫描次数受驱动表t1影响

图15：索引嵌套查询连接
  • Simple Nested-Loop Join（简单嵌套查询连接）

依然是这个SQL，如果没有idx_a这个索引，执行过程是什么样的呢？

  1. 从表t1中读入一行数据 R1；

  2. 从数据行R1中，取出a字段到表t2里去查找；

  3. 全表扫描取出表t2中满足条件的行，跟R组成一行，
    作为结果集的一部分

  4. 重复执行步骤1到3，直到表t1的末尾循环结束。

失去了索引，形成了笛卡尔扫描，扫描次数为100100次，100次的t1表的扫描和与t2表全表扫描比对，因此，这个性能太差了，MYSQL并未采用此种算法。

图16：简单嵌套查询连接
  • Block Nested-Loop Join（分块嵌套查询连接）

分块嵌套查询链接是针对简单嵌套查询的解决方案，采用Join Buffer缓存的方式，提升性能。执行过程是这样的：

1.把表t1的数据读入线程内存join_buffer中

2.扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

因此，尽量比对次数是10万次，但表扫描次数为1100次，是table1和table2的数据总行数。

图17：采用JoinerBuffer嵌套查询连接

然后，似乎我们遗漏了一个概念分块，这里并未提及它。你试想下table1数据量比较大，会把所有数据装载到Joiner Buffer中吗？当然会采用分而治之的方法。这种方法就是分块。

按照分块的方式，我们重新看下，假设table1的50条数据装满Joiner Buffer,再次看下执行流程吧：

图18：采用JoinerBuffer分块嵌套查询连接

table1将分为两次装载到Join Buffer与table2比对，你会发现扫描次数是这样计算：table1的行数+块数×table2的行数=100+2×1000=2100。而块数是扫描次数的一个重要影响系数，而这个系数是由table1的行数决定，也就是说驱动表的行数决定。

经过三种算法的比对，你是否发现，扫描次数由驱动表的大小决定，这也就是为什么InnerJoiner会选择小表作为驱动表的原因。

那么，最后我们总结下优化Join的手段有：

  • 将小表作为驱动表
    无论是否使用索引，小表作为驱动表都能够减少扫描次数。

  • 调整join_buffer_size大小
    MYSQL该参数的默认值大小为512k,调整该参数的大小，可以减少分块嵌套查询的块数，能够成倍的减少扫描次数。

  • 关联时使用索引
    关联时使用索引避免扫描和笛卡尔判断，是提升join性能的绝对杀手锏！

写在最后

SQL调优虽然说起来理论比较多也相对来说好理解，当问题来的时候，还可能束手无策。你可以在SQL语句上增加force index或者ignore index来强制或者忽略某个索引，来验证是不是MYSQL优化器给出了错误的优化。

当然，可以通过explainSQL语句来观察语句的执行过程、索引的使用情况等，帮助你综合分析。explain是优化的非常重要的技巧，不妨你百度找篇文章来仔细研究一下。

好了，今天这篇文章就分享到这里啦。有什么疑问可以关注我，留言加我好友。

作者介绍

  • keaizhuzhu，公众号面试怪圈小编，网站面试怪圈站长，曾就职于阿里巴巴本地生活，目前就职于京东做后端开发。
  • 编写过《Java面试怪圈内卷手册》面试秘籍，全网阅读量过万次。
  • 官网：//www.msgqer.com。旨在分享前端、后端、大数据、各种中间件技术的面试资料，总访问量数万次。点击【阅读原文】可直达。
Tags:

VirMach 便宜 VPS

QNews

QNews