深入浅出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的存储以pag