分页查询、JOIN关联查询优化

一、分页查询优化

现有如下表：

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) 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
) 

1.1 场景

这张表目前有10万条数据，如果谈到分页，可能大部分小伙伴都会使用如下SQL实现：

select * from employees limit 100000,10;

但是这种最常规的写法是有弊端的。
如果数据量不多，没有什么太大问题；
但是如果数据量几百万甚至上千万的时候，若如果还用这种方式查询，并且查询的数据比较靠后（翻到了几十页甚至上百页），越往后翻速度越慢。


为什么会这样呢？
看起来只查了几条数据，但是实际这条 SQL 是先读取 100010条记录，然后抛弃前 100000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

1.2 优化技巧

这种分页的优化，需要看具体的场景，不是全都适用，以下来进行具体介绍

1.2.1 根据自增且连续的主键排序的分页查询

前提条件：查询是根据主键来查询的，并且主键是自增且连续的
SQL可以优化如下形式：

select * from employees where id > 100000 limit 10;

优化之后，可以使用explain来看下两个SQL执行的结果。

我们可以看到如果使用limit，查询没有走索引，从rows可看出相当于走了全表扫描（表数据共有102834行，扫描了102872行）

优化过后的SQL，使用了主键索引，而且扫描的行数也大大减少了，使用了B+ Tree的索引树，所以效率会高很多。

缺点
这种优化的局限性很明显，要求主键自增且连续，这种情况是很少见的。
因为大多数情况下会存在删数据的情况，如果把中间某条数据删了，SQL查询在该条记录之前是没有问题的，但是如果到比该条数据大的情况，结果集是有问题的。
可以模拟下这种场景：
先把删除某条数据，再次执行两个SQL语句，查看结果：


可以看到两条SQL的结果集并不一样

因此，如果主键不连续，不能使用上面描述的优化方法。 另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致。
所以这种改写得满足以下两个条件：
主键自增且连续
结果是按照主键排序的

1.2.2根据非主键字段排序的分页查询

有以下SQL：

select * from employees ORDER BY name limit 90000,5;

使用Explain查看分析：

这里走的也是全表扫描，而且排序也用到了文件排序：filesort

      到这里，可能有小伙伴会产生疑问，这个表明明有idx_name_age_position索引，现在使用的是name字段，是符合最左前缀原则，为什么没有走索引，而且还用到了文件排序？需要怎么优化呢？
      具体原因之前文章有介绍过：扫描整个索引并查找到没索引 的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以MySQL优化器放弃使用索引。
      其实这种语句要使用索引的话，可以使用覆盖索引，把要查的字段指定出来。那我们就可以使用这种思路：在排序的时候让字段尽可能的少，排完序之后把需要查询的字段指定出来。
所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下：

select * from employees e 
inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

接着我们可以使用explain来分析下性能：

从结果中我们可以看到，使用到了设置的索引，并且排序也使用的索引排序。

从表面上看，最终的SQL查询了两次，里面查询了一次，最终又把ID回表再查一次，性能会不会低呢？？
答案是不会。首先最先执行的SQL：select id from employees order by name limit 90000,5充分的使用到了覆盖索引而且还是用的索引排序，这条SQL查出结果其实代表最终的数据集的ID已经出来了而且是排好序的，最终外面那层的回表查询性能也是很高的，大多是情况下，我们的分页数据都是10条或者20条，这几条数据回表用主键关联性能是很高的。

二、JOIN关联查询优化

现有如下表：
t1表有10000条数据

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=10201 DEFAULT CHARSET=utf8;

t2有100条数

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

2.1 MySQL表关联常见的两种算法

Nested-Loop Join 算法
Block Nested-Loop Join 算法

下面我们来详细介绍下这两种算法

2.1.1嵌套循环连接 Nested-Loop Join(NLJ) 算法

现有如下SQL：

select*from t1 inner join t2 on t1.a= t2.a;

使用explain查看查询情况，我们可以看到是先查询的t2表，然后查询的t1表（之前的博客有写过id大的先执行，id一样的从上往下执行）。

那么为什么会先查t2表呢？如果把t1和t2交换一下位置，会先查哪张表呢？

我们可以看到结果是一样的。都是优先查询t2表。
其实，MySQL在选择的时候有自己的算法，对于表关联，关联的字段但凡有索引，mysql会在底层选择嵌套循环连接 Nested-Loop Join(NLJ) 算法（这种算法底层并不是笛卡尔积）。

嵌套循环连接 Nested-Loop Join(NLJ) 算法：一次一行循环地从第一张表（称为驱动表（ps:也就是t2表））中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动 表）里取出满足条件的行，然后取出两张表的结果合集。

总结如下：
从上述的执行计划中我们可以看到这些信息：

①、驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。

②、使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算 法是 NLJ

上面sql的大致流程如下：

1. 从表 t2 中读取一行数据；
2. 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
3. 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
4. 重复上面 3 步。

整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表 中的对应行(扫描100次 t1 表的索引 ps:扫描索引是非常快的，时间几乎可以忽略不计，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100 行)。因此整个过程扫描了 200 行。

解释：结果扫描100+100=200的原因。
因为先扫描t2表的所有数据，也就是100次扫描。
每次从t2表中扫描出一条记录，就会和t1表去对比，因为是有索引的，这里忽略索引扫描的时间，认为从t1表中扫描一次就可获取可t2表相对应的那条数据。
所以，也就是说，从t1表中扫描一次，t2表中扫描一次，就可以获得一条结果集。t2表总共100行数据，所以就是总共扫描了200次（两张表各100次）。

如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block Nested-Loop Join 算法

2.1.2基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

现有如下SQL：

select*from t1 inner join t2 on t1.b= t2.b;

这次使用的是非索引字段关联

Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。
基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法：把驱动表（ps:驱动表就是小表，从执行计划中可以看出：这里的驱动表还是t2）的数据读入到 join_buffer （ps:可以把这个当做一个内存，也就是mysql new出来的一小块连接缓存区域）中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

上面sql的大致流程如下：

1. 把 t2 的所有数据放入到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比 （对比是在内存中进行的。比较完一条数据就会释放内存，不是所有数据一直存在内存里，所以没有内存飙高的风险。）
3. 返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100，并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以 内存中的判断次数是 100 * 10000= 100 万次。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？
如果关联字段没索引的sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。
很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。
因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有 索引的情况下 NLJ 算法比 BNL算法性能更高

2.2 对于关联sql的优化

①关联字段加索引，让mysql做join操作时尽量选择NLJ算法
②小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去 mysql优化器自己判断的时间。
straight_join：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。

MySQL内部查询引擎会大概去优化下SQL，但是有时不一定优化的准确。
比如上述的查询，MySQL优化后先执行t2表，这是一般的情况，优化器可以正确优化，让小表先执行。但是有的时候就不一定会优化为小表先查询。

所以，如果某些情况下，已经明确知道连接的表哪个是小表，就可以使用straight_join语法来做连接，明确指定让哪个表先执行。
上述的SQL就可以改写为：

select * from t2 straight_join t1 on t2.a = t1.a; 

这样就不需要MySQL内部的优化（判断连接的表哪个数据量大，哪个数据量小），明确告诉优化器哪个是驱动表，尽量少让优化器去选择
需要注意：上述的t2是小表，前提是两张表没有任何条件。 真正的小表驱动大表并不是说哪张表的总数据量小哪张表就是驱动表，而是真正参与关联的数据量大小来决定哪个是驱动表。

straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）

尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因 为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

更多优化，可前往in和exsits、count(*)查询优化查看。