04-27MySql索引优化（二）

分页查询优化

分页sql：

select * from employees limit 10000,10;

这条 SQL 是不是只查询从 10001 行开始的 10 行记录，而是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。

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

select * from employees limit 90000,5;

该 SQL 表示查询从第 90001开始的5行数据，没有order by，默认用通过主键排序。可以看出用了全表扫描

主键是自增并且连续的，可以这样优化：

select * from employees where id > 90000 limit 5;

这里的查询结果一致，但是扫描的行数少了，而且还用到了主键索引。

但是根据连续的主键排序的分页查询的，如果中间的记录出现删除，那么上面两种sql的写法返回结果是不一致的。如果主键不连续，不能使用上面的优化方法

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

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

EXPLAIN分析结果：

查询时间：

表是有建立name的索引的，但是上面的EXPLAIN信息的key字段为null，也就是没有走索引，而且出现了filesort。原因是：扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。

这个的优化关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录。

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

EXPLAIN分析结果：

查询时间：

优化后的sql和原本的sql执行结果是一致的，但是查询时间缩短了一半，而且原sql的排序是用filesort，优化后的sql排序是索引排序。

Join关联查询优化

mysql的表关联常见有两种算法

什么是驱动表

• left join时，左表是驱动表，右表是被驱动表
• right join时，右表是驱动表，左表是被驱动表
• join/inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表
  • 在id相同的情况下，根据从上到下的顺序，排在上面的驱动表，下面的被驱动表

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

循环从驱动表读取一行，在这行数据中取得关联字段，根据关联字段在被驱动表里取出符合条件的行，然后取出2张表的结果合集。

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

• 驱动表是 t2，被驱动表是 t1(在id相同的情况下，按照从上到下的执行顺序，所以t2是驱动表)
• 在join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ

上面sql的流程：

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

整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历每行数据中字段a的值，然后根据t2表中a的值索引去扫描t1表中的对应行（扫描100次 t1 表的索引，只要1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)，因此整个过程扫描了 200 行。

如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低。如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低

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

就是把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比

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

上面sql的流程：

1. 把 t2 的所有数据放入到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
3. 返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。因为 join_buffer 里的数据是无序的，t1的每行记录都要和join_buffer中的t2(100行)做比较，也就是：10000(t1行数)*100((t2行数)=100w次。

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。

• 例如t2有1000条记录，但join_buffer每次最多放800条记录，那就会先join_buffer放800条记录，然后跟t1做比较。然后清空join_buffer，再把剩下的200条再放入join_buffer，然后再继续跟t1做比较。所以就多扫了一次 t1 表

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 NLJ 呢

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