MySQL高阶调优，一文让你从入门到精通！

【前言】

经常有一些朋友向我咨询，如何写出高效的SQL，这不是三言两语能说得清的，索性认真来写一下，增删查改方面的知识我不再赘述，如果有基础薄弱的同学，可以好好的补一补再来看。

以MySQL为基础，MySQL调优篇内容主要包含MySQL逻辑架构、索引知识、表关联算法、explain执行计划解读及SQL调优实战等。

文章受众主要为两类人：

第一类人是工作中不可避免的会接触到MySQL的人，比如说一些项目人员、开发人员、测试人员等。

第二类人是专职DBA。

其实不管是专职的还是非专职的，就我接触到的情况而言，很多DBA平时维护MySQL看起来没什么问题，但其实没有很好的理论支撑，知其然而不知其所以然，解释一个简单的问题就能问倒一大部分的人。

比如说：MySQL的逻辑架构，分析当前业务架构优缺点？SQL工作原理是什么样的？

而且很多公司招聘面试的时候，考验的也是背后的原理居多，基本上没有机试。面试官问一个问题，即便你会解决但就是说不出原理，那么你肯定要不了高薪。

理论+实战=高薪

文章能够让大家有所收获、有所借鉴那是最好的。

【MySQL逻辑架构】

1、整体架构图

MySQL的架构不同于其他数据库，它的插件式的存储引擎架构可以在多种不同场景中应用并发挥良好作用。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

各层介绍：

1.1 连接层

最上层是客户端，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。

1.2 服务层

1.3.引擎层

存储引擎负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

show engines:查看所有的数据库引擎

show variables like '%engine%'?查看默认的数据库引擎

MyISAM和InnoDB对比

1.4.存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

2、查询流程

MySQL的查询流程大致是：

客户端通过协议与DB服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析。

语法解析器和预处理：首先MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。解析器将使用语法规则验证和解析查询；预处理器则根据一些规则进一步检查解析数是否合法。

查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

流程图：

3、SQL的执行顺序

一般SQL语法：

SQL解析：

真正执行的顺序：

4、SQL性能问题

常见的几个点：

• SQL逻辑复杂且糟糕

• 无效的索引

• 服务器调优和各个参数的配置（缓冲，线程数等）

SQL逻辑复杂且糟糕，通常表现在嵌入很多子查询，各种表关联等。

无效的索引，通常表现为索引建立了但没用上，索引分为单值索引和复合索引和唯一索引，所谓单值索引指的是一个索引有且只包含一个列，一个表中可以有多个单列索引。复合索引是指索引能够同时覆盖多个数据列；唯一索引是指索引列的值必须唯一，但可以为null。

服务器调优一般部署MySQL阶段就调整好了。

【索引知识】

面试的时候，提起索引不要再拿目录类比索引的优点了，这样显的很菜。一句话：索引是数据结构，是一个排好序且快速查找的数据结构。

1、索引的本质

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

查询是数据库的最主要功能之一，速度当然是越快越好，因此数据库系统的设计者会从查询算法的角度进行优化。常见的查询算法有顺序查找（linear search）、二分查找（binary search）、二叉树查找（binary tree search）等。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。

这种数据结构，就是索引。

看一个例子：

图1

图1展示了一种可能的索引方式。

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

2、索引结构

MySQL 索引一般是哈希表或 B+ 树，常用的 InnoDB 引擎默认使用的是 B+ 树来作为索引的数据结构。

2.1为什么是B+树

B+树是B树的变种，是基于B树来改进的。为什么B+树会比B树更加优秀呢？

B树：有序数组+平衡多叉树；?

B+树：有序数组链表+平衡多叉树；

B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。

B+ 树查找过程

磁盘块 1 中存储 17 和 35 数据项，还有 P1、P2、P3 指针，P1 表示数据项小于 17 的磁盘块，P2 表示数据项在 17 和 35 之间的数据项，P3 表示数据项大于 35 的数据项。非叶子节点不储存数据，只储存指引搜索方向的数据项。

我们知道每次 IO 读取一个数据页的大小，也就是一个磁盘块。

假设我们要查找 29 这个数据项，首先进行第一次 IO 将磁盘块 1 读进内存，发现17 < 29 < 35，然后选用 P2 指针进行第二次 IO 将磁盘块 3 读进内存，发现26 < 29 < 30，然后选用 P2 指针将磁盘块 8 读进内存，在内存中做二分查找，找到 29，结束查询。

通过分析查询过程，我们可以知道 IO 次数和 B+ 树的高度成正比。H 为树的高度，M 为每个磁盘块的数据项个数，N 为数据项总数。

从下面的公式可以看出如果数据量N一定，M越大相应的H越小。

M 等于磁盘块的大小除以数据项大小，由于磁盘块大小一般是固定的，所以减小数据项大小才能使得 M 更大从而让树更矮胖。这也是为什么 B+ 树把真实数据放在叶子节点而不是非叶子节点的原因。

如果真实数据放在非叶子结点，磁盘块存储的数据项会大幅度减少，树就会增高相应查询数据时的 IO 次数就会变多。

3、索引实现

在MySQL中，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

3.1、MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

下图是MyISAM索引的原理图：

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

3.2、InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

1、InnoDB的数据文件本身就是索引文件。

2、InnoDB的辅助索引data域存储相应记录主键的值而不是地址。

这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

4、如何使用索引

说了那么多原理，总结一下索引的优缺点和使用时机。

4.1、优点

• 提高数据检索的效率，降低数据库的IO成本；

• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗；

所以记住，索引功能是：搜索+排序

4.2、缺点

• 索引提升查询效率的同时也会降低更新的效率，更新表时，MySQL不仅要保存数据，还要保存一下索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

4.3、索引使用时机

• 主键自动建立唯一索引；

• 频繁作为查询条件的字段应该创建索引；

• 查询中与其它表关联的字段，外键关系建立索引；

• 单键/组合索引的选择问题， 组合索引性价比更高；

• 查询中排序的字段，如order by create_time，排序字段若通过索引去访问将大大提高排序速度；

• 查询中统计或者分组字段；

【总结】

索引调优是一项技术活，没有理论不行，仅靠理论也不行，实际情况千变万化，结合实践中不断的实验和摸索，从而真正达到高效使用MySQL索引的目的。

【表连接方式】

常见的七种Join理论，如图所示：

1、左连接：A独有+在A中的B部分

语法：

select * from A left join B on A.key = B.key

没有满足A的B补Null。

2、内连接：A和B的交集

语法：

select * from A inner join B on A.key = B.key

3、右连接：B独有+在B中的A部分

语法：

select * from A right join B on A.key = B.key

没有满足B的A补Null。

上面三个是非常常见且常用的Join，那么还有四类Join：

4、A去掉B的部分，A的独有

对比左连接，其实是把中间属于A的B部分给干掉了。

语法：

select * from A LEFT JOIN B on A.aid = B.bid 左连接where B.bid is null B不在A的部分

因为本身左连接已经把所有A的值都包含出来了，同时多了的部分，就是B在A的部分，只要拿到为空的部分，其实就是B不在A的部分。

5、B去掉A的部分，B的独有

对比右连接，其实是把中间属于B的A部分给干掉了

语法：

select * from A RIGHT JOIN B on A.aid = B.bid 右连接where?A.aid?is?null???A不在B的部分

6、A和B去掉交集部分

语法：

select * from A LEFT JOIN B on A.aid = B.bid 左连接where B.bid is null B不在A的部分unionselect * from A RIGHT JOIN B on A.aid = B.bid 右连接where A.aid is null A不在B的部分

实际上是不是上面两个的合体？

7、外连接：A和B的并集

语法：???????

select?*?from?A?LEFT?JOIN?B?on?A.aid?=?B.bid??unionselect * from A RIGHT JOIN B on A.aid = B.bid

【表连接算法】

MySQL数据库根据不同的使用场合，支持两种Nested-Loops Join算法，一种是Simple Nested-Loops Join（NLJ）算法，另一种是Block Nested-Loops Join（BNL）算法。

1、简单嵌套循环连接（Simple?Netsted-Loop Join）

对于两表连接，驱动表只会被访问一遍，被驱动表具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。

对于内连接，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左外连接的驱动表就是左边的那个表，而右外连接的驱动表就是右边那个表。

两表连接的大致过程：

• 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。

• 对上一步中查询驱动得到的结果集中每一条记录都被分别到被驱动表中查找匹配的记录。

如图：

如果有3个表进行连接的话，那么步骤2中得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，重复上边过程。

这种驱动表只访问一次，但被驱动表却可能被多次访问的连接执行方式称之为嵌套循环连接（Nested-Loop Join）。

2、索引嵌套循环连接（Index Nested Loops Join），

有一方在连接字段上有索引，这种场景在MySQL的使用中见的比较多。

优化器会考虑选择有索引的一方作为被驱动表，双方都有索引则选择索引高度低的，索引高度一样则选择记录数多的作为被驱动表，对于驱动表的每一条记录，在被驱动表中使用索引查询，大大减少了比较次数，提高了查询效率。

索引是主键时效率更高。

如图：

3、基于块的嵌套循环连接（Block Nested-Loop Join）

扫描一个表的过程其实就是把这个表的数据从磁盘上加载到内存中，然后在内存中比较匹配条件。

在实际环境中，面对百千万的数，内存放不下，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描后面的记录的时候可能内存不足，需要把前面的数据在内存中释放掉。

而采用嵌套循环连接算法的两个表中，被驱动表要被访问好多次，如果被驱动表中的数据特别多而且不能使用索引访问的话，那就相当于从磁盘上读好多次这个表，这个IO代价就非常大，所以我们应该尽量减少访问被驱动表的次数。

在嵌套循环连接中，驱动表查询结果集中有多少条记录，就需要驱动表数据被加载多少次来进行匹配，那可不可以把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。

所以提出可join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录进行匹配，因为匹配的过程是在内存中完成的，所以这样可以减少被驱动表的IO代价。

这种只需要访问一次被驱动表就可以完成的连接操作称为基于块的嵌套连接算法；

4、批量键访问联接（Batched Key Access Join）

当被驱动表的链接字段有非主键索引时，而是通过范围扫描读取一部分记录放入内存中，然后按照主键排序，这样匹配到数据后需要按对应的主键索引去查询被驱动表的真实数据时，可以按照排好序的主键进行顺序访问，因为InnoDB叶子节点的数据也是按主键排序的，所以这种读取方式能提高查询效率。

MRR的使用流程中用到了排序，有一定的开销，有些sql中效率可能没有那么高。

5、Hash Join

MySQL8.0正式引入了Hash Join的连接方式，Hash Join可以在被驱动表没有索引的情况下进行快速的连接并查询。

• Hash Join首先使用了Join Buffer，把驱动表相关字段存入内存。这一步和块嵌套循环连接套路相同。

• 把Join Buffer中对应的字段值生成一个散列表，保存在内存中。这一步叫build。

• 扫描被驱动表，对被驱动表中的相关字段进行散列并比较。这一步叫probe。

可见，Hash Join也依赖Join Buffer，在最好的场景下，如果Join Buffer能覆盖驱动表所有相关字段，那么在查询的过程中驱动表和被驱动表都只需要扫描一次，如果散列算法够好，比较次数也只是被驱动表的记录数。

Hash Join只能用于等值连接，大表连接Hash Join的优化效果比较明显。

优化思路

• 用小结果集驱动大结果集，尽量减少 join 语句中的Nested Loop循环总次数。

• 优先优化 Nested Loop 内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能。

• 对被驱动表的 join 字段上建立索引，并且Join ON 条件的字段应该是相同类型的。

• 当被驱动表的 join 字段上无法建立索引的时候，设置足够的 Join Buffer Size。

• 对于非主键的连接查询，如果被驱动表数据特别多，建议先使用子查询查出一个临时的结果集然后再连接。（待验证）

• 对于可以直接从一个表中取数据的情况。（例如同一个表中取交集，例如好友表，互相关注才是好友）这样的情况，使用 Join 效率是要高于子查询的。

【总结】

掌握表连接算法，结合实践中不断的实验和摸索，从而真正达到高效使用MySQL算法的目的。

【EXPLAIN执行计划解读】

使用explain分析SQL的执行计划，从而知道MySQL是如何处理SQL语句的，有助于分析SQL语句的性能瓶颈。

本文截图基于MySQL版本5.7.27。

1、explain的作用

通过explain+sql语句可以知道如下内容：

• 表的读取顺序。（id）

• 数据读取操作的操作类型。（select_type）

• 显示sql操作属于哪张表的（table)

• 哪些索引可以使用。（possible_keys）

• 哪些索引被实际使用。（key）

• 表直接的引用。（ref）

• 每张表有多少行被优化器查询。（rows）

2、explain包含的信息

通过执行explain可以获得sql语句执行的相关信息。

下面对explain的表头字段含义进行解释。

2.1、ID

ID列：描述select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序；

1> id相同，执行顺序从上到下

2> id不同，如果是子查询，id的序号会递增，id值越大执行优先级越高。

3> id相同不同，同时存在。

总结：id的值表示select子句或表的执行顺序，id相同，执行顺序从上到下，id不同，值越大的执行优先级越高。

2.2、select_type

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：

2.3、table

显示sql操作属于哪张表的。

2.4、type

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

需要记住的

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

1> system

System：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

Const：表示通过索引一次就找到了。

const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。

注：对于system和const可能实际意义并不是很大，因为单表单行查询本来就快，意义不大。

2> eq_ref

唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描。

3> ref

非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体。

4> range

只检索给定范围的行，使用一个索引来检索行，可以在key列中查看使用的索引，一般出现在where语句的条件中，如使用between、>、<、in等查询。

这种索引的范围扫描比全索引扫描要好，因为索引的开始点和结束点都固定，范围相对较小。

5> index

全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取。

6> ALL

全表扫描。

2.5、possible_keys和key、key_len

possible_keys:可能使用的key。

Key:实际使用的索引。如果为NULL，则没有使用索引

key_len：表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。

简单理解：possible_keys表示理论上可能用到的索引，key表示实际中使用的索引。

2.6、ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

2.7、rows

根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。当然该值越小越好。

2.8、filtered

百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例。

2.9、Extra

包含不适合在其他列中显示但十分重要的额外信息。

【总结】

• id，select子句或表执行顺序，id相同，从上到下执行，id不同，id值越大，执行优先级越高。

• type，type主要取值及其表示sql的好坏程度（由好到差排序）：system>const>eq_ref>ref>range>index>ALL。保证range，最好到ref。

• key，实际被使用的索引列。

• ref，关联的字段，常量等值查询，显示为const，如果为连接查询，显示关联的字段。

• Extra，额外信息，使用优先级Using index>Using filesort>Using temporary。

着重关注上述五个字段信息，结合实践中不断的实验和摸索，对调优十分有用。

【SQL调优实战】

1、环境准备

每张表模拟一些数据进去。???????

article表

CREATE TABLE IF NOT EXISTS `article`(`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`author_id` INT (10) UNSIGNED NOT NULL,`category_id` INT(10) UNSIGNED NOT NULL , `views` INT(10) UNSIGNED NOT NULL , `comments` INT(10) UNSIGNED NOT NULL,`title` VARBINARY(255) NOT NULL,`content` TEXT NOT NULL);
class表?CREATE TABLE IF NOT EXISTS `class`(`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL);
book表?CREATE TABLE IF NOT EXISTS `book`(`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL);?phone表CREATE TABLE IF NOT EXISTS `phone`(`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL)ENGINE?=?INNODB;
staffs表CREATE TABLE staffs(id INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间')CHARSET utf8 COMMENT'员工记录表';
ALTER?TABLE?staffs?ADD?INDEX?index_staffs_nameAgePos(`name`,`age`,`pos`)

2、单表优化案例

需求分析：

查询category_id为1且comments大于1的情况下，views最多的article_id???????

select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;

执行计划：

5.7版本后添加了列filtered，意思是：指返回结果的行占需要读到的行(rows列的值)的百分比，filtered的数值其实越高，表示通过索引直接返回的行很多，数值较低时，一般出现在type=ALL或者index的情况。

分析下这个执行计划，type=ALL全表扫，而且产生了filesort。

where条件加个复合索引看看：

create index idx_atc_ccv on article(category_id,comments,views);

再看执行计划：

虽然走了索引，但也走到了filesort，还是不够好；这个索引不起作用吗？

在Mysql中，索引中出现了范围查找，后面就失效，comments出现了范围，索引在找的时候，发现comments无法直接定位到，影响了order by views的索引排序，进而出现了filesort。

那假设我们把sql调整为comments = 1再看看执行计划。

filesort没有了，type一下从range变成了ref，执行计划是好的，但是业务变了，不行！

那么怎么创建索引合适呢？既然范围之后索引失效，那么我们能不能绕过去？直接新建category_id, views的复合索引呢。（删除之前创建的索引）

执行计划告诉我们，这个索引加的很合适！

结论：type变成了range，这是可以忍受的，但是Extra里出现了filesort是无法接受的，但是我们建立了索引为什么没有用，这是因为按照Mysql的BTREE工作原理，先排序category_id，如果遇到相同的，再排序comments，如果遇到相同的，再排序views，当comments位置处于联合（复合）索引的中间位置时，Mysql无法对范围（range）后面的字段进行索引排序，从而后面的字段索引失效！

3、两表优化案例

来看个SQL：

select * from class left join book on class.card = book.card;

执行计划：

明显这个type为ALL，索引也没有加。问题来了，索引加哪边？是加class.card还是book.card？

我们都试试，先添加右边book表的索引：

alter table book add index idx_b_card(card);

执行计划走下：book的很明显的改变，type变成了ref

此时我把book表的索引删掉，而建立class左表的索引看看执行计划：

明显，加了class表的索引后，发现type是index，并且rows20行记录，全索引扫描，性能不会有刚刚的好！

同样的sql，同样的索引列，左连接的时候，加的索引所在的表不同，效果不同；

结论：上面出现效果不同，这个是由左连接的特性决定的，left join 条件用于确定如何从右边搜索行，而左边一定是都有的；左边全有，确定核心的点在于确定如何从右表中搜索数据行，右边是关键点，要加索引！所以左连接索引加在右表上，同理，右连接也是相反加！

有没有人好奇，如果两个索引都建呢会是什么样？我们尝试下加上看看：

现在book和class表上的card字段都加了索引，效果比上面两个都好！

4、三表优化案例

? ? ? ??

先把之前创建的索引都清除掉。

SQL如下：???????

select * from class left join book on class.card = book.card left join phone on book.card = phone.card; 

执行计划：

此时三个表都没有索引：我们走下执行计划后发现，Extra字段多了Using join buffer；首先join buffer意思是使用了连接缓存。

在5.7之后，Mysql对表和表之间的连接，做了优化缓存，实际上在A left join B的过程，Mysql会更在意B的表往A中相同的部分，所以类似一个for循环，最外层for A，内层是for B，找到B中的每一行满足A行的记录，因为是要A的全部，所以最外层一定是A，然后合并行，最后输出；而在3表中，等于3个for循环。

其中其实发现有个Block Nested-Loop Join——BNL算法，这个算法将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。所以最外层的表是class，先for整个class，然后放在join buffer里，接下来循环内表的时候，直接取buffer的行去比对，减少对磁盘的IO。

但是整个type=ALL，rows都是20，全表扫，这是我们无法接受的。

那么三张表怎么加索引呢？可以想想，左连接建右表上，那么这个是不是说class左表，建立索引在book和phone上？试试！

走下执行计划看看：很明显，改善很多!

那么很明显这个原则也成立，总结下：

尽可能减少join语句中的NestedLoop循环总次数，永远用小结果集驱动大的结果集，这里的例子，就是左表尽量数据小于右表，外层for的次数就减少了，IO次数也会降低。

其实你可以试试，如果class表加了索引，效果会比右连接稍微好点，哈哈

5、索引失效案例

5.1建个复合索引

create index idx_s_nap on staffs(name,age,pos);

SQL如下：

select * from staffs where name='July'; 

索引会不会失效？执行计划：

没问题。

再来一个SQL：

select * from staffs where name='July' and age = 25; 

执行计划：

一样没问题。?

但是这里其实有个问题，Extra为Null。Extra为Null的时候，如果走了索引，说明这个查询，进行了回表！

那么什么是回表呢？

简单来说，如果你查询的字段，存在非索引字段，那么查询的时候，Mysql虽然根据了你的条件得到了这个记录，但是不在索引的字段无法通过索引的方式直接得到，只能通过拿到该条记录的主键索引，再从数据行里读，我们知道Mysql索引文件和数据文件是在两个不同的文件里的，要去读磁盘；所以索引文件建立的效果，就是帮助我们对数据进行排序和查找效率的优化，不至于去读数据行进行额外的IO开销；

所以这里字段我用select *，因为复合索引里没有add_time这个字段，所以无法直接查出来add_time这个列的记录，要通过定位到主键，然后再读一次数据行才可以得到这个记录，称为回表。

如果SQL这么写，就不会出现回表，因为pos在索引列中！

select pos from staffs where name='July' and age = 25; 

执行计划：

我们来看一些特殊场景！

SQL如下：

select * from staffs where age = 23 and pos = 'dev';

执行计划：

再来一个sql：

select * from staffs where name = 'zhangsan';

走索引了。

总结：如果查询中没有开头的索引，不好意思，只能全表扫。违背了【最佳左前缀法则】

再看下这个sql：

select * from staffs where name = 'zhangsan' and pos = 'dev'; 

执行计划显示这个key_len和只有name的时候一样，说明只走了name索引，Extra中出现Using index condition，这个是5.6后新加的特性，会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；就是走到了索引上的意思。

5.2、勿在索引列做任何操作

不要在索引列上做任何操作，包括计算，函数，自动或者手动类型转换，会导致索引失效而转向全表扫描。

SQL：

select * from staffs where left(name, 4) = 'July';

查找name左往右4个字符为July的行。索引失效了！

5.3、范围之后全失效

SQL：

select * from staffs where name = 'July' and age > 14 and pos = 'manager'; 

age用到了索引，进行范围查找，但是后面的索引pos就失效了，这里要注意，5.7以前的优化，是如果出现了范围查找，则当前范围的索引也不走，而5.7后，范围索引之后的才失效，所以这里的key_len=78，单个name话是74，三个都走是140。

5.4、不等于场景下索引失效

SQL：

select * from staffs where name != 'July'; 

select * from staffs where name <> 'July';

? ?

在使用不等于的场景下，无法使用索引导致全表扫描。

5.5、is null、is not null无法使用索引

SQL：???????

select * from staffs where name is null; select * from staffs where name is not null;

5.6、Like百分写最右

like以通配符开头('%abc...')时，Mysql索引会失效变成全表扫！

SQL：???????

select?*?from?staffs?where?name?like?'%July%';select * from staffs where name like '%July'; select * from staffs where name like 'July%';?

因为like是范围查找，百分号在后面，Mysql会拿到字典序进行排序的方式查找对应的情况，而百分号在前面，Mysql就不知道从哪个字母开始找，于是便全表扫描。

实际面试中经常会这么问：如何解决like '%xxx%' 字符时索引不被使用的情况？

答案是用覆盖索引避免索引失效，我们这里的索引是(name, age, pos)，索引我们在查询的时候不要写select *，只要写具体的字段值，任何一个列被覆盖索引覆盖，就可以解决两边百分号的问题！！！?

5.7、字符串不加单引号索引失效

SQL：

explain select * from staffs where name = 222; 

索引失效。

而这个是成功走到索引的：

select * from staffs where name = '222';

Mysql很聪明，你以为你给我的我就查不到了，你给我的Int型的时候，实际这个字段是varchar型，传入数字会隐式的帮你转换成varchar类型，前面说过不要让Mysql做这些自动或者手动的类型转换，否则索引失效！当然查询的结果，是不会有变化的，只是sql执行上有转换。

5.8、少用or

? ? ?

SQL：

select * from staffs where name = 'July' or name = 'z3'; 

少用or，会导致索引失效，不是不用；

【结语】

MySQL调优篇写到这里就差不多告一段落了，希望大家都能真真正正能写出高性能的SQL，结合实践中不断的实验和摸索，早日晋级资深或者架构师

更多精彩内容，关注我们▼▼