MySQL优化——SQL语句优化策略

Order By优化

在order by中，如果排序会造成文件排序（在磁盘中完成排序，性能差），那么就说明SQL没有命中索引。

优化方式：

• 如果排序字段使用了联合索引，那么尽量在业务不冲突的情况下，遵循最左前缀法则。
• 如果文件排序没法避免，那么尽量想办法使用覆盖索引。all->index

文件排序

MySQL通过比较系统变量max_length_for_sort_data(默认1024byte)的大小和需要查询的结果集大小来判断使用哪种排序模式。

• 单路排序：max_length_for_sort_data大于查询字段总长度。此时MySQL将查询结果集放入内存缓冲区sort_buffer中，在内存中将数据进行排序，得到最终的结果。
• 多路排序：max_length_for_sort_data小于查询字段总长度。此时MySQL将排序的字段和主键字段放到内存缓冲区sort_buffer进行排序，然后按照内存中的排序后的主键字段，做一次回表查询。

Group By优化

实质上是先排序后分组，参考Order By。

分页查询优化

--主键连续场景（极少见）,使用主键范围查询
select * from demo where id > 100000 limit 10;
--非连续场景，通过一个覆盖索引的子查询，找出主键字段，再通过连接查询得到结果
select * from demo a inner join (select id from demo order by name limit 1000000,10) b on a.id = b.id;

关联查询优化

在JOIN查询中，如果关联字段建立了索引，MySQL就会使用NLJ（Nested Loop Join）算法，去找小表（数据量小的表）作为驱动表，先从驱动表中读取一行数据，然后拿着一行数据去被驱动表（数据量比较大的表）中做查询。这是由MySQL内部优化器来决定的，与SQL语句中表的书写顺序无关。

如果表没有索引，会创建一个join buffer内存缓冲区，把小表数据存进来，用内存缓冲区中的记录去和大表中的记录进行比较，比较过程依然是在内存中进行的。这就是BNLJ（Block Nested Loop Join）算法。

因此，如果使用join查询，那么关联字段一定要创建索引，而且字段的长度和类型一定要一致，否则索引将失效，从而进行全表扫描。

IN&EXISTS优化

遵循小表驱动大表原则。

• 当B表数据量＜A表数据量时，使用：

select * from A where id in (select id from B);
--相当于：
for(select id from B){
	select * from A where A.id = B.id
}

• 当B表数据量＞A表数据量时，使用：

select * from A where exists (select 1 from B where B.id = A.id);
--相当于
for(select * from A){
	select * from B where B.id = A.id
}

Count优化

对count的优化应该是架构层的优化，因为count的统计在一个产品中会经常出现，而且每个用户都会访问，所以对于访问频率过高的数据，应该维护在缓存中。