慢sql优化必看 – 编程之家

前言：当希望mysql能够以更高的性能运行查询时，最好的办法就是弄清楚mysql是如何优化和执行查询的。一旦理解这点，很多查询优化工作实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行 （ps：我这里主要分析以innoDB作为数据库表的执行引擎的情况下的各种可以做的优化措施）

- 慢sql的危害
- 一、一条sql在mysql当中执行的旅途
- 二、索引的数据结构
- - 1.聚簇索引（又称一级索引或者主键索引）
  - 2.非聚簇索引（又称二级索引，辅助索引）
  - - 单值索引
    - 联合索引
- 三、explain的使用
- - 1.什么是explain？
  - 2.explain得到的结果中各列表示的含义是什么？
  - - 1.id
    - 2.select_type
    - 3.table
    - 4.type
    - 5.possible_keys
    - 6.key
    - 7.key_len
    - 8.ref
    - 9.rows
    - 10.Extra
    - - Using index
      - Using index condition
      - Using where
      - Using temporary
      - Using filesort
      - Select tables optimized away
      - Using where; Using index
- 四、最基本的索引优化措施以及实践
- - 1.联合索引遵循最左前缀原则
  - 2.不要在索引列上进行函数操作或者相关数值计算
  - - 数值操作
    - 函数运算
  - 3.不要在区分度不高的列上建立索引
  - 4.使用联合索引时，尽量不要使用范围查询
  - 5.like模糊查询时，通配符%使用位置不要放在第一位
  - 6.in跟or在表数据量较大的情况下会走索引（也不一定，要根据具体的表大小跟in数值做评估）
  - 7查询尽量使用覆盖查询
- 五、order by，group by优化
- - 1.order by的列上建立索引
  - 2.where的字句跟order by的列都要遵循最左优化原则
  - 3.group by跟order by的优化原则也是一样的，遵循最左优化原则
  - 4.就算是排序，也可以使用覆盖索引，尽量要使用覆盖索引
- 六、分页查询优化
- 七、Join关联优化
- - 1.MySQL关联查询算法
  - - A.Block Nested-Loop Join(BLJ)算法
    - B.Nested-Loop Join(NLJ)算法
  - 2.优化方式
  - - A.小表驱动大表
    - B.关联字段上建立索引
- 八、总结

慢sql的危害

1.增加方法调用链执行耗时，影响调用链性能，影响用户体验
2.占据数据库连接，数据库连接池占满还有可能导致其他涉及sql操作的功能全部等待。无法正常使用

一、一条sql在mysql当中执行的旅途

这里引用一张《高性能Mysql》的图。

1.客户端（也就是我们的JDBC程序，或者是Navicat等一系列可以链接数据库的工具）发送一条查询给服务器

2.服务端先检查查询缓存（这里会根据mysql的一个配置来决定到底需不需要查询缓存。如果query_cache_type表示为ON则已经开启缓存，为OFF则没有开启缓存）如果命中缓存，则立即返回存储在缓存当中的结果。否则进入下一阶段

3.服务端进行SQL解析，预处理，再有优化器生产对应的执行计划。

4.MYSQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。

5.将结果返回客户端

二、索引的数据结构

索引是一种有序的数据结构（B+Tree）

1.聚簇索引（又称一级索引或者主键索引）

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B+Tree索引和数据行。当表有聚簇索引时，它的每行数据实际存储在索引的叶子页当中。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。为了能更深入的理解聚簇索引结构，大家看一下下面这张图。
假设我们有一个employee表，表结构如下

CREATE TABLE `employee_test02` (`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '自增主键',`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(10) NOT NULL DEFAULT '0' COMMENT '年龄',`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',PRIMARY KEY (`id`),KEY `idx_name_age_position` (`name`,`position`,`age`) USING BTREE,KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1521 DEFAULT CHARSET=utf8 COMMENT='员工记录表'

那么这张表用B+Tree组织起来将会是以下这种数据结构形式，data当中存储的就是非主键以外的其他数据。
（这里引用了一张隔壁同学的图 https://www.cnblogs.com/vianzhang/p/7922426.html）

//如果我们执行一条sql查询语句，比如
select * from employee_test02 where id = 15那么对于在B+Tree当中的执行顺序是：
1.首先将根节点磁盘块1读取进内存，然后根据id主键定位到该索引可能存在于磁盘块1的P1子节点当中（也就是左边的磁盘块2），然后把磁盘块2加载进内存
2.根据id=15再次定位该索引可能存在于磁盘块2的P2子节点当中（也就是磁盘块5），然后把磁盘块5加载进内存
3.在磁盘块5当中，最终定位到id=15的索引存在于这里。于是将data数据进行返回。对于mysql来说，一页（这里的一页可以简单的理解为一个磁盘块，比如磁盘块1）的索引占据的内存控件大概是16KB左右
（这是mysql官方通过多次优化设定的一个合理的值）。不要小看这16KB.就拿磁盘块1来举个例子。28这个索引节点，对于int类型的主键索引来说，只占用4个字节的存储空间，P1指针占用差不多6个字节，那么磁盘块1根节点就可以存放差不多1638个索引节点。（16KB x 1024 / (6 + 4)）计算得来。那么磁盘块2这个节点也可以存放1638个索引，假如加载到磁盘块5的时候已经找到了对于的数据行，按每个数据行差不多1KB的大小来进行计算。磁盘块5可以存储16个索引。（因为磁盘块5已经到了叶子节点，则这一行的大小不仅包含索引的大小，同时也包含数据行所占用的大小。）这么算下来聚簇索引，进行三次IO操作就能从差不多 1638 x 1638 x 16 = 42928704（四千多万）条数据当中找到我们需要的那一条。这下你可以想象到B+Tree数据结构构成的聚簇索引是多么强大了吗？

2.非聚簇索引（又称二级索引，辅助索引）

非聚簇索引，跟聚簇索引不一致的地方在于，存储的叶子节点只保留聚簇索引的索引健。其结构任然是一颗B+Tree的结构。如下图所示（这是以表的name字段作为二级索引所组成的一颗B+Tree）

现在我们来看看这样的一条使用二级索引的sql语句是如何进行查找的。
select * from employee_test02 where name = 'Bin';
1.首先将最上面的根节点读取进内存，通过mysql内部的排序比对发现Bin存在于JoDan与Jack两个二级索引值之间的指针当中。
2.将该指针所在的子节点加载到内存当中，发现Bin存在于LiLi于Tom两个二级索引值之间的指针当中。
3.将该指针所在的子节点加载到内存当中，最终找到了Bin所在的索引建值。
4.这个时候，我们发现查询语句是查询所有该列信息，那么这里会做一个回表的操作，也就是通过Bin下面的主键索引建值，再回到由主键索引组织的B+Tree当中进行一次查找。
5.最终找出所有名字叫Bin的所有信息，返回。

单值索引

单值索引，顾名思义，由一个字段来当成索引，组建B+Tree结构。聚簇索引是单值索引，非聚簇索引也可以是单值索引。

联合索引

联合索引，也就是由多个字段所组成的联合索引。非聚簇索引组合的联合索引跟单值索引有一点不一样，也就是他的索引健值是由所有组成该联合索引的健值构成。并且联合索引遵循最左前缀原则。什么是最左前缀原则，也就是想要用到联合索引，那么查询条件至少得包含组成联合索引的最左边的索引的列值。给大家举几个例子。

例如employee_test02表的联合索引 KEY `idx_name_age_position` (`name`,`position`,`age`) USING BTREE,

 结果1 联合索引的最左边第一个索引健值name作为查询条件，使用到了联合索引,但是索引使用范围只用到了name的部分，key_len为74
EXPLAIN select * from employee_test02 where name = 'LiLei';

 结果2 联合索引的最左边第一个索引健值name作为查询条件，并且联合索引其他条件都使用到了。使用到了联合索引,索引使用范围包含整个联合索引，key_len为140
EXPLAIN select * from employee_test02 where name = 'LiLei' and age = 10 and position = 'hunan';

 结果3 联合索引非最左边索引建值age作为查询条件，没有使用到联合索引
EXPLAIN select * from employee_test02 where age = 1;

 结果4 联合索引非最左边索引建值age跟position作为查询条件，没有使用到联合索引
EXPLAIN select * from employee_test02 where age = 2 and position = 'hunan';

 结果5 联合索引最左边索引建值name跟position作为查询条件，使用到了联合索引,但是索引使用范围只用到了name的部分，key_len为74
EXPLAIN select * from employee_test02 where name = 'LiLei' and position = 'hunan';

三、explain的使用

1.什么是explain？

explain是mysql提供的可以分析sql执行计划的工具，通过这个工具我们可以分析sql的执行顺序，比如是否由用到索引，扫描了多少行数等等。explain可以说是优化sql的第一步，要想知道sql如何优化，就得先知道sql如何执行。

2.explain得到的结果中各列表示的含义是什么？

先创建对应的测试表跟测试数据

 测试表以及测试数据
CREATE TABLE `employee_test02` (`id` int(10) NOT NULL,`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(10) NOT NULL DEFAULT '0' COMMENT '年龄',`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',PRIMARY KEY (`id`),KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE,KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工记录表'测试数据
INSERT INTO `xms_operation`.`employee_test02` (`id`, `name`, `age`, `position`, `hire_time`) VALUES ('1', 'LiLei', '22', 'manager', '2020-10-19 14:59:03');
INSERT INTO `xms_operation`.`employee_test02` (`id`, `name`, `age`, `position`, `hire_time`) VALUES ('2', 'HanMeimei', '23', 'dev', '2020-10-19 14:59:03');
INSERT INTO `xms_operation`.`employee_test02` (`id`, `name`, `age`, `position`, `hire_time`) VALUES ('3', 'Lucy', '23', 'dev', '2020-10-19 14:59:03');CREATE TABLE `employee_address` (`id` int(10) NOT NULL,`user_id` int(10) NOT NULL DEFAULT 0 COMMENT '员工id',`province` varchar(24) NOT NULL DEFAULT '' COMMENT '所在省',`city` varchar(24) NOT NULL DEFAULT '' COMMENT '所在城市',`street` varchar(24) NOT NULL DEFAULT '' COMMENT '所在街道',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工地址表'测试数据
insert into employee_address VALUES(1,1,'湖南','长沙','天心街道')CREATE TABLE `employee_hobby` (`id` int(10) NOT NULL,`user_id` int(10) NOT NULL DEFAULT 0 COMMENT '员工id',`hobby` varchar(24) NOT NULL DEFAULT '' COMMENT '爱好',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工兴趣表'测试数据
insert into employee_hobby VALUES(1,1,'打篮球')

下面我们通过几条sql来分析一下，explain执行语句之后得到的结果集各个列对应的含义

1.id

id表示当前sql的执行顺序，当前只是简单查询，所以只有一列记录，id为1。如果是复杂查询，则在explain结果当中会存在多列记录。要记住的是，id越大，执行优先级越高，id相同，sql优先级一致，从上往下执行。

2.select_type

表示当前sql查询的类型，我们可以从图中看到当前这条sql的查询类型为SIMPLE。表示这是一条简单查询，不包括任何关联查询也不包含子查询等。select_type在mysql官网有很多种的查询类型，有一些可能我们一辈子也遇不上。所以没必要去全记，所以在这里我只列举几种比较常见的。比如

PRIMARY 		复杂查询sql最外层的查询语句

SUBQUERY  		select 列中的子查询

DERIVED    		from后的子查询

3.table

表示当前执行的sql语句查询的是哪张表。例如

在这条sql当中，我们可以看到from当中的子查询是查询的employee_test02表，所以table展示的employee_test02。有些同学可能会有疑问，那么derived2又是什么意思呢？ derived2表示的是当前的table是一个临时表，也就是由(select * from employee_test02)查询组成的临时表。

4.type

这一行表示mysql要以何种方式对sql进行查询。这里要认识几个关键值。这几个关键值的优先顺序如下，优先级越高，执行速率越快。
system > const > eq_ref > ref > range > index > ALL

system与const 速度最快，一般很少达到这两个查询类型。
eq_ref表示至少是使用到了主键索引，或者是唯一索引
ref表示非主键索引，可以是其他辅助索引。
range表示使用到索引的范围查询
index表示根据索引进行全部扫描
ALL表示进行了不使用索引的范围查询(速度极慢！)

5.possible_keys

表示这个sql语句在执行查询过程中，可能会使用到的索引。例如

这里的possible_keys为idx_name_age_position，也就是我们employee_test02的联合索引。
如果想强制sql不走或者走possible_keys可能会用到的索引，可以使用ignore index或者force index来执行。这里举几个使用的例子（employee_test02表中有idx_name_age_position跟idx_name两个索引）
例子一

例子二

例子三

6.key

表示这个sql语句在执行查询过程中，实际会使用到的索引。例如

7.key_len

这里的key_len表示的是，你这条sql执行过程中用到的索引所占的长度。是需要用某种计算公式得知。
例如：

这里表示的是用到了联合索引idx_name_age_position的name字段的索引长度。
那么为什么是74呢？这里教大家一个计算公式吧，varchar类型的字段，如果用其组建索引，其索引长度则为： 例如 varchar（N）其索引长度为（3N+2） 。不信你可以试试，嘿嘿！

8.ref

表示当前sql进行查询时，条件中所用到的列或者常量。const表示为常量，如果是表中的某列字段，则会显示使用的哪一列的字段
const常量

用的某列的字段

9.rows

表示当前sql执行，可能需要扫描的行数，并不是实际查出来的结果集。另外要注意的是，这个rows只是一个参考值，绝大多数情况下是正常的，但是也有可能会出现不太准的情况。实际结果还是要根据查询的sql以及表大小来确定。

10.Extra

这一列表示的是当前sql执行计划的一些额外的信息。这里认识一下几个比较常见的重要的值。

Using index

覆盖索引是一种概念，并不是一种索引类型！
覆盖索引，表示当前查询的sql语句，查询的列都包含在当前使用的索引树当中（一般发生在辅助索引当中）。那么什么叫包含在当前的索引树呢？例如，辅助索引是一颗B+Tree，叶子节点包含的值除了本身索引健的值以外还包括了主键索引值。那么我们查询辅助索引列的值就会使用到覆盖索引。
举个例子

还是用到上面的三张表结构(注意我下面的例子都是以楼上三张表作为依据进行)
给employee_hobby增加一个辅助索引
alter table employee_hobby add index idx_userId(user_id)
然后执行如下sql
EXPLAIN select user_id from employee_hobby where user_id = 1
请注意，当前查询的列就是user_id,并且user_id是我们建立的辅助索引
执行结果如下图

Using index condition

不完全覆盖索引，跟Using index有一点不同就是查询的列未完全被辅助索引所覆盖。
举个例子

还是用到上面的三张表结构
employee_test02有两个辅助索引（idx_name,idx_name_age_position）
这里实际走的key是idx_name辅助索引，然后查询的列值并不止name一列。
执行如下sql
EXPLAIN select name,age,position from employee_test02 where name = 'LiLi'

Using where

表示当前查询的列值并没有被索引覆盖

Using temporary

表示mysql需要创建一张临时表来处理查询。一般需要建立索引来进行优化
employee_address.province列没有索引

给employee_address.province列添加索引，此时的查询变成了使用覆盖索引。

Using filesort

表示当前使用的查询的排序方式是文件排序，这种排序方式的是在磁盘当中进行排序的，效率较低，一般碰到这种情况，需要考虑添加索引进行优化

给employee_address.user_id添加索引，此时变成了使用索引进行排序。

Select tables optimized away

表示当前的查询使用的min或者max等聚合函数进行查询

Using where; Using index

这里可能同学们会有疑惑，我上面不是说Using where表示的是查询的列没有用到索引，Using index表示查询是使用的覆盖索引，也就是查询的列是使用了索引查找的吗？这两个值不应该是互斥的吗？为什么现在同时出现了？
原因是这样的， Using where; Using index同时出现，是意味着使用了索引扫描，或者全表扫描的动作。我这里虽然使用了索引作为条件进行查找，但是是在一个范围内进行查找，所以就同时出现了Using where; Using index

四、最基本的索引优化措施以及实践

1.联合索引遵循最左前缀原则

即where条件当中至少要包含联合索引最左边的第一个健值，并且不跳过联合索引当中的其他索引列。在第二节的联合索引当中有例子体现。
这里要注意的一个点是，当我们使用联合索引的时候，虽然需要遵循最左原则，但是where当中的索引列的顺序可以打乱，mysql在执行查询的时候会优化where中的索引顺序，将其与联合索引的组成顺序保持一致。但是打乱是很不好的习惯，虽然看上去打乱跟不打乱好像没什么耗时差别，并且mysql会帮你善后，但是这会增加mysql优化的负担（ps:能减少mysql的优化负担就要尽量减少）
错误示范

ps：为了示范先去掉单值辅助索引 
alter table employee_test02 drop index idx_name
employee_test02的联合索引顺序
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
sql查询时，使用的列值顺序
EXPLAIN select * from employee_test02 where age = 10 and name = 'LiLei' and position = 'hunan'
查询结果没什么不一样，但是这是不好的查询习惯

2.不要在索引列上进行函数操作或者相关数值计算

数值操作

两者查询的结果是一致的，但是explain出来的结果却截然不同
EXPLAIN select * from employee_hobby where user_id - 1 = 0; 
EXPLAIN select * from employee_hobby where user_id = 1

进行的数值运算的，及时我们在user_id上有索引存在，但是不会用到索引

没有进行数值运行的正常用到了索引

函数运算

EXPLAIN select * from employee_test02 where LEFT(name, 3) = 'LeLei' 
EXPLAIN select * from employee_test02 where name = 'LeLei'

进行了函数运算的没有使用索引

没有进行函数运算的使用到了索引

3.不要在区分度不高的列上建立索引

不要在区分度不高的列上面建立索引(比如性别(男，女))，即使会走索引，但是效率也不一定高，因为就相当于扫描了整表一半的数据了。
这里再举个例子，如何分辨索引列的区分度。可用下面这条类似的sql比较列在整个表的区分度情况，越接近1说明该列区分度越高，到1说明该表每列的值都是不同的（从敖丙大佬那里扣过来的经营）

4.使用联合索引时，尽量不要使用范围查询

使用联合索引，使用到范围会导致该联合索引当中的其他组成的索引列值不能走索引树的查询。联合索引使用的key_len越长，其查询效率也就越快，筛选率也就越高。

只用到了索引name的长度，也就是74

这里用到了整个联合索引，长度为140

如果一定要使用范围查询，那么可以适当的调整联合索引的组成顺序，比如age，可以将联合索引从
KEY idx_name_age_position (name,age,position) USING BTREE,
改为
KEY idx_name_age_position (name,position,age) USING BTREE,
这样在多条件联合查询的时候可以最大程度的使用到联合索引。

注意
另外需要注意的一点是，在表的数据量不同时，联合索引的范围查询会带来不一样的效果。
当表数据量较少时，例如我目前的employee_test02表中数据量只有三条。第一个字段就用范围进行查询，是会走索引的。但是当我将employee_test02表数据量加到十万以上时，第一个字段就用范围查询反而不会走索引。

举例如图：
employee_test02数据为3条

employee_test02数据为100003条


这里的原因简单跟大家说一下，不知道大家还记不记得在第一章一条sql的路途当中我跟大家有说过，Mysql的服务端会有一个优化器，这个优化器会对sql进行一个优化计划的选择，最终得到要执行的计划然后调用执行引擎的API进行查询。当employee_test02表中数据量较多时，Mysql认为第一个条件就是范围查找，要查找的符合的数据集可能很多，还是进行的整列查询，使用辅助索引还需要进行回表操作，还不如直接进行全表扫描效率来的快，所以直接进行的全表扫描。

那么在employee_test02数据量只有三条时候，Mysql评估计算，数据量很小，就算要回表也没关系，因为最多也不可能超过3条，所以即使第一个条件就走范围查找还是可以使用到联合索引。

那么数据量大的表要使用范围查找又想要使用到索引该怎么办呢？

在这里其实有两种处理方式可以尝试着去解决大数据量的表的范围查找问题：

解决方式一：强制走索引
从下面这张图我们可以看到possible_keys是存在索引的，但是key当中又没有使用索引。
那么我们可以通过force index 来让mysql强制这条语句走索引查询。

使用force index，可以看到扫描行数有了明显的下降

那我们再来对比一下实际的执行时间

--63ms
select * from employee_test02 force index(idx_name_age_position) where name > 'LiLei' and age = 10 and position = 'hunan' 
--79ms
select * from employee_test02 where name > 'LiLei' and age = 10 and position = 'hunan'  
可以发现强制走索引的sql查询比没有强制走索引的快了16ms左右。
但是要注意的一点是，force index不一定强制走索引就会比不走索引要更快。因为mysql优化选择的执行计划绝大多数情况下是最优的执行方案，我这个可能是特例，刚好让我碰上了  -.-!

解决方式二：使用覆盖索引

5.like模糊查询时，通配符%使用位置不要放在第一位

模糊查询，通配符放在第一位时会导致查询时索引失效，转为全表扫描

通配符不放在第一位，是可以走索引

如果通配符一定要放第一位，又想走索引，可以考虑使用覆盖索引的方式，也是可以走索引的。

6.in跟or在表数据量较大的情况下会走索引（也不一定，要根据具体的表大小跟in数值做评估）

7查询尽量使用覆盖查询

覆盖查询是直接从当前辅助索引树就可以找到需要的结果集，不需要进行回表等操作，可以节省查询时间

五、order by，group by优化

order by 通过某个字段进行排序
group by 通过某个字段先排序后分组
这两种优化说白了就是不要在排序时候产生Using filesort，因为文件排序是在磁盘是进行排序，是一种比较慢的排序方式
优化措施：使其能够在索引上就完成排序。

1.order by的列上建立索引

使用没有索引的列，直接走文件排序

2.where的字句跟order by的列都要遵循最左优化原则

不论是单值索引还是联合索引，where的字句跟order by的列都要遵循最左优化原则
单值索引

给employee_address添加单值索引idx_province与idx_user_idCREATE TABLE `employee_address` (`id` int(10) NOT NULL,`user_id` int(10) NOT NULL DEFAULT '0' COMMENT '员工id',`province` varchar(24) NOT NULL DEFAULT '' COMMENT '所在省',`city` varchar(24) NOT NULL DEFAULT '' COMMENT '所在城市',`street` varchar(24) NOT NULL DEFAULT '' COMMENT '所在街道',PRIMARY KEY (`id`),KEY `idx_province` (`province`),KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工地址表'

符合最左优化原则，没有使用Using filesort文件排序

打乱最左优化原则之后，使用了文件排序

联合索引
联合索引也是一样的。

符合最左优化原则，没有使用Using filesort文件排序

打乱最左优化原则之后，使用了文件排序

3.group by跟order by的优化原则也是一样的，遵循最左优化原则

4.就算是排序，也可以使用覆盖索引，尽量要使用覆盖索引

六、分页查询优化

在做查询的时候，我们经常会用到分页这个动作，不论是做管理界面，还是做批量数据处理。因为如果不分页的话，不仅会造成查询数据缓慢，更严重的还可能导致内存溢出。
那么我们就经常性的会用到这样的语句
select * from xxx order by xxx limit xx, xx
那么对于这样的例子我们如何进行优化呢？

这里举个例子，还是以employee_test02这张表来进行
假如我们现在有这样的一个需求，老板说，我想知道最近入职的5名员工的个人信息
那这句话对于我们来说，无非就是按时间降序排序，找最后五条记录，那么对应的查询sql如下(目前员工信息表有 100005条员工记录)
按正常思路来说，我们会这样写
select * from employee_test02 order by hire_time DESC limit 100000, 5
来看看这个语句的查询计划，全表扫描，磁盘排序，10万条记录左右执行时间在150~160毫秒左右

现在我们换一种写法，一条sql拆分成两条。虽然hire_time这个时候还没加索引，但是查询执行时间已经提升到了 80~110ms左右。

再给hire_time列上加上索引。再看sql的执行计划。这里的磁盘排序就变成了索引排序。这时执行时间已经提升到了50ms~70ms左右了。

七、Join关联优化

join就是涉及到多表操作了，往往是单张表的结果已经不能满足我们的需求。
多表其实能优化的不多，主要涉及两个点。在讲这两个点时，我先跟大家提一下mysql在进行关联查询时用到的两种算法。

1.MySQL关联查询算法

A.Block Nested-Loop Join(BLJ)算法

空讲概念估计可能大家会听不太明白，直接举例给大家看看
来看这么一条sql语句

// An highlighted block
两个表的user_id目前都是没有索引的。
employee_hobby02插入了100条数据，employee_hobby插入了10000条数据
EXPLAIN select * from employee_hobby02 a INNER JOIN employee_hobby b on a.user_id = b.user_id

执行计划

大家可以看到，在extra中出现了这么几个词语，(Block Nested Loop)
这就表示当前的关联查询，mysql使用的算法是BLJ算法，那么BLJ算法又是怎么计算的呢？这种算法查询速率怎么样呢？这里简单给大家描述一下过程
1.先执行a表的查询，因为这条sql没有where条件，所以是全表扫描a表，会循环100次，查出所有a表记录，将其放在一个buffer缓存区。
2.全表扫描b表，循环取出每条就记录，然后去buffer对比符合条件的记录(这里的buffer区的a表的顺序是杂乱的，所以取出b表数据进入a表对比时，最差的情况可能还会出现查找100次之后才找到符合的记录)
3.返回满足条件的join列表

那么按最差的情况来看，总共的查询次数是 100(a表查询总数) + 10000(b表查询总数) +100 * 10000(buffer缓冲区最差的情况)，结果大家可以算一次。这种情况显然是需要进行一定的优化的。常用的方法是，关联字段建立对应的索引。

B.Nested-Loop Join(NLJ)算法

NLJ算法，说简单点，就是关联字段上加上索引之后，mysql选择的一种查询优化方式。
还是这条sql，同样的数据，同样的场景，区别在于关联字段user_id已经加上了索引。

// An highlighted block
两个表的user_id已经加上索引。
employee_hobby02插入了100条数据，employee_hobby插入了10000条数据
EXPLAIN select * from employee_hobby02 a INNER JOIN employee_hobby b on a.user_id = b.user_id

来看看执行计划

简单描述一下过程
1.全表扫描a表，每次取出一条记录
2.将当前取出的单条记录去b表进行匹配，注意，加了索引之后这里是根据索引去进行匹配，所以不需要全表扫描
3.将join匹配的记录进行返回

那么来看看这里的查询次数
100(循环a表) + 100(根据user_id索引到b表进行匹配) = 200次
是不是感觉这种算法瞬间就高大上了很多？

2.优化方式

A.小表驱动大表

小表驱动大表，这个一般mysql会帮我们自动优化。但是要注意的一个点是，在使用 inner join或者where的时候需要查看sql的执行计划，排在第一条的查询的表可以认为是驱动表，而不是排在 inner join 或者 where前面的表才是驱动表。

left join 表示以左表作为驱动表
right join 表示以右表作为驱动表

B.关联字段上建立索引

这里直接放例子跟查询时间对比了。
关联字段没有索引，查询时间在 160ms~200ms之间

关联字段添加索引，查询时间在 40~50ms之间

瞬间快了两倍多，是不是很美滋滋？

八、总结

总而言之，慢sql的优化可以很好的利用索引去解决，利用好索引就是让mysql优化器以一种最优的、查询效率最快的方式去解析、执行客户端发送的sql，从而优化查询时间，达到优化慢sql的目的

目录