SQL深入优化

Order by 和 Group by

Order by

通过 EXPLAIN 关键字展示的 Extra 字段中的内容可以判断排序是否使用了索引，这个索引指的是包含排序字段的辅助索引，如果出现了 Using filesort 说明排序过程没有使用辅助索引，而是使用了主键索引(聚簇索引)，Using index 则表示使用了辅助索引，Using index 效率会比 Using filesort 效率高。

示例(InnoDB 引擎)

Case 1:

Order by 语句使用了索引最左前列，因为 Extra 中没有出现 Using index，说明在排序中使用了 age 索引列。

Case 2:

where 和 Order by 组合之后符合索引的最左前列，排序时也可以使用索引，没有出现 Using filesort。

Case 3:

使用了覆盖索引后，排序使用了索引，如果是 select *，则出现 Using filesort，所以能使用覆盖索引尽量使用覆盖索引。

Group by

和 Order by 很类似，Group by 的本质是先排序后分组，排序规则和 Order by 一样，如果不想排序使用 order by null 禁止排序。

注意：能使用 where 完成的筛选就不要用 having。

分页查询优化

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

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

这条查询语句的执行流程是首先根据主键进行排序，然后查询出前 90005 条数据，然后抛弃前 90000 条数据，读取最后的 5 条数据展示出来。

优化方向：不要查询那么多条数据。

mysql> select * from employees where id > 90000 limit 5;

计算好当前页面需要展示哪几条，利用主键值进行限制。

 优化后的语句走了索引，效率提升。

但是这种优化方式是有针对性的，要求主键必须是自增且连续的，如果主键值删除了一条，就不能用这种方法，因为结果会不一致，还需要查询语句是按照主键排序的，如果查询语句是按照其他字段排序的，这个优化方法也没用。

使用非主键排序的分页查询

mysql>  select * from employees ORDER BY name limit 90000,5

通过 Extra 可以看出这个排序没有走索引，因为 MySQL 评估后发现走了二级索引再去主键索引查询数据的性能没有直接全表扫描的性能高。

优化方向：让排序可以走索引，这时可以考虑覆盖索引。

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

通过 explain 可以发现排序走了索引，通过对执行时间的对比，也会发现优化后的语句用时更短。

Join 关联查询优化

驱动表：先执行的表。使用 inner join，MySQL 一般会选查询结果数据量少的那张表作为驱动表；使用 left join，左边的表为驱动表；使用 right join，右边的表为驱动表。

被驱动表：后执行的表。使用 inner join，MySQL 一般会选查询结果数据量多的那张表作为被驱动表；使用 left join，右边的表为被驱动表；使用 right join，左边的表为被驱动表。

示例前置条件：表 t1， t2 结构相同，为字段 a 设置了索引，t1 表插入 10000 条数据， t2表插入 100 条数据。

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

一次一行的从驱动表中读取一行数据，利用这行数据中的关联字段的值，去被驱动表查询满足条件的行，然后将这两张表的结果合并起来返回给客户端，循环这个步骤，直至读取完驱动表的全部数据。

当关联字段有索引时 MySQL 会选择这个算法，通过 Extra 字段中没有出现 Using join buffer 也可以判断这个语句使用了 NLJ 算法。

示例：

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

执行步骤：

1、从 t2 表读取一条数据。

2、根据第一步那行数据中字段 a 的值，去 t2 表查询满足条件的行。

3、将两张表的结果结合到一起作为结果返回给客户端。

4、重复上面 3 个步骤直至读取完 t2 表的全部数据。

这条语句扫描了多少行数据？

因为需要遍历 t2 表，所以会扫描 100 行 t2 表的数据，因为 t1 表的字段 a 有索引，所以 t1 表每次只需要扫描一行就可以得到结果，即 t1 表也扫描了 100 行，加起来就是 200 行。

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

将驱动表的数据读取到 join buffer 内存中，循环被驱动表的全部数据，去 join buffer 中寻找满足条件的那行数据。

当关联字段没有索引时 MySQL 会选择这个算法，通过 Extra 字段中出现 Using join buffer 可以判断这个语句使用了 BNL 算法。

示例：

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

执行步骤：

1、把 t2 表的全部数据读取到 join buffer(mysql 内存，默认 256k) 中。

2、循环取出 t1 表的每一行数据，然后去 join buffer 中寻找字段 b 有相同值的那行数据。

3、返回满足条件的数据 。

这条语句扫描了多少行数据？

因为需要遍历 t1, t2 表，所以会扫描 (10000+100) 行数据，但是内存中判断的次数是 (10000*100) 次，因为 t1 表中的每行数据都要与 t2 表的全部数据做对比。

如果 join buffer 放不下 t2 表的数据怎么办？

将 t2 表拆分为多段，即先把 80 条数据放入 join buffer，循环 t1 表匹配这 80 条数据，匹配完成之后把 join buffer 清空，再把剩下的 20 条数据放入 join buffer，再循环 t1表进行匹配。

结论：可以明显看出被驱动表的关联字段有索引效率会高，所以优化方向就是为被驱动表的关联字段加上索引；还有就是驱动表选择查询结果数据量少的那个表，这样循环的数据量少，效率也会增加。