常见sql深入优化

1. Order by 与 Group by 优化

1.MySql支持两种方式的排序filesort和index,Using index是指MySql扫描索引本身完成排序。index效率高。filesort效率低。

2.order by 满足两种情况会使用Using index。

1)order by 语句使用索引最左前列。

2)使用where子句与order by子句条件列组合满足索引最左前列。

3.尽量在索引列上完成排序，遵循索引建立(索引创建的顺序)时的最左前缀法则。

4.如果order by的条件不在索引列上，就会产生Using filesort。

5.能用覆盖索引尽量用覆盖索引。

6.group by 与 order by很类似，其实质是先排序后分组，遵照索引创建的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having ,能写在where中的限定条件就不要去having限定了。

2.Using filesort文件排序原理详解

filesort文件排序方式

. 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；使用trace工具可以看到sort mode信息里显示或者

.双路排序(又叫回表排序模式):是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在sort buffer中进行排序，排序完后需要再次取回其他需要的字段；用trace工具可以看到sort_mode信息里显示

MySql通过比较系统变量max_length_for_sort_data（默认1024字节）的大小和需要查询的字段总大小来判断使用哪种排序模式。

. 如果max_length_for_sort_data比查询字段的总长度大，那么使用单路排序模式

. 如果max_length_for_sort_data比查询字段的总长度小，那么使用双路排序模式

示例：

explain select * from employees where name='xx' order by position

单路排序详细过程：

从索引name找到第一个满足name =’xx’条件的主键id
根据主键id取出整行，取出所有字段的值，存入sort_buffer中
从索引name找到下一个满足name=’xx’ 条件的主键id
重复步骤2，3直到不满足name=’xx’
对sort_buffer中的数据按照字段position进行排序
返回结果给客户端

双路排序详细过程：

从索引 name找到第一个满足name=’xx’的主键id
根据主键id取出整行，把排序字段position和主键id这两个字段放到sort buffer中
从索引name取下一个满足name=’xx’记录的主键id
重复3，4知道不满足name=’xx’
对sort_buffer中的字段position和主键id按照字段position进行排序
遍历排序号的id和字段position,按照id的值回到原表中取出所有字段的值返回给客户端

单路排序和双路排序的区别：

单路排序会把所有需要查询的字段都放到sort buffer中，而双路排序只会把主键和需要排序的字段都放到sort buffer中进行排序，然后再通过主键再回到原表查询需要的字段。

如果MySql排序内存配置的比较小并且没有条件继续增加了，可以适当把max_length_for_sort_data配置小点，让优化器选择双路排序算法，可以在sort_buffer中一次排序更多的行，只是需要再根据主键回到原表取数据。

如果MySql排序内存有条件可以配置比较大，可以适当增大max_length_for_sort_data的值，让优化器优先选择全字段排序（单路排序）,把需要的字段放到sort_buffer中，这样排序后就会直接从内存里返回查询结果了。

所以，Mysql通过max_length_for_sort_data这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。

注意：如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，MySql很多参数设置都是做过优化的，不要轻易调整。

3.分页查询优化

>> 常见的分页场景优化技巧：

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

select * from employees limit 9000,5; //没添加单独的order by,表示通过主键排序
可优化为 > select * from employees where id>90000 limit 5; //该sql走了索引，并且扫描的行数大大减少，执行效率更高。

缺点：这条改写的sql在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致。因此，如果主键不连续，不能使用上面描述的优化方法。

另外，如果原Sql是order by非主键的字段，按照上面说的方法改写会导致两条Sql的结果不一致。所以这种改写得满足两个条件：

. 主键自增且连续

. 结果是按照主键排序的

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

select * from employees order by name limit 9000,5

发现并没有使用name字段的索引（key字段对应的值为null）

原因：扫描整个索引并查找到没索引的行（可能要遍历多个索引树）的成本比扫描全表的成本更高，所以优化器放弃使用索引

优化：

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

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

4.Join关联查询优化

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

. Nested-Loop Join 算法

. Block Nested-Loop Join 算法

4.1 嵌套循环连接Nested-Loop Join（NLJ）算法

一行一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

explain  select* from t1 inner join t2 on t1.a=t2.a;  //a上有索引

磁盘扫描次数：n(t1数据量)+m(t2数据量) 【索引的查询时间可以忽略】

总结：

驱动表是t2,被驱动表是t1.先执行的就是驱动表（执行计划结果的id如果一样则按从上到下顺序执行sql）;优化器一般会优先选择小表作为驱动表。所以使用inner join 时，排在前面的表并不一定就是驱动表
使用了NLJ算法，一般join语句中，如果执行计划Extra中未出现Using join buffer则表示使用的join算法是NLJ
如果被驱动表的关联字段没有索引，使用NLJ算法性能会比较低，mysql会选择Block Nested-Loop Join 算法

4.2 基于块的嵌套循环连接（BNLJ）算法

把驱动表的数据读到join buffer 中，然后扫描被驱动表，然后被驱动表每一行取出来跟join buffer中的数据做对比。

explain  select* from t1 inner join t2 on t1.b=t2.b;  //b上没有索引

磁盘扫描次数：n(t1数据量)+m(t2数据量)

内存中的判断次数：n(t1数据量)*m(t2数据量)

被驱动表的关联字段没索引为什么要选择使用BNL算法而不使用Nested-Loop Join?

第二条sql使用Nested-Loop Join 扫描行为n*m ,这个是磁盘扫描/

很显然，用BNL扫描次数少很多，相对比磁盘扫描，BNL的内存计算会快很多

因此MySql对比被驱动表的关联字段没索引的关联查询，一般会使用BNLJ算法，如果有索引一般选择NLJ算法，有索引的情况下NLJ算法比BNLJ算法性能更高

对于关联sql的优化：

关联字段加索引，让mysql做join操作时尽量选择NLJ算法
小表驱动大表，写多表连接sql时如果明确知道哪张表时小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。

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 一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化器靠谱。

5.in和exists优化

原则：小表驱动大表，即小的数据集驱动大的数据集

in：当B表的数据集小于A表的数据集时，in优于exists —一般情况

select * from A where id in (select id from B)

等价于：

for(select id from B){
select * from A where A.id=B.id
}

exists: 当A表的数据集小于B表的数据集时，exists优于in

select * from A where exists (select 1 from B where A.id=B.id)

等价于：

for(select * from A){
select * from B where B.id=A.id
}

exists(subquery)只返回true或者false，因此子查询中的select * 可以用select 1来替换。

6.count(*)查询优化

临时关闭mysql查询缓存，为了查看sql多次执行的真实时间

set global query_chahe_size=0;
ser global query_cache_type=0;

explain select count(1) from employees;
explain select count(id) from employees;
explain select count(name) from employees;
explain select count(*) from employees;

四个sql的执行计划一样，说明这四个sql执行效率应该差不多，区别在于根据某个字段count不会统计字段为null值的数据行。

为什么mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索性能应该更高。

6.1 查询mysql自己维护的总行数

对于myisam存储引擎的表做不带where条件的count查询性能时很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算。

对于innodb存储引擎的表mysql不会存储表的总记录行数，查询count需要实时计算。

6.2 show table status

如果只需要知道表总行数的估计值可以用如下sql查询，性能很高。

show table status like 'employees';

6.3 总记录数维护到redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值（用incr或decr命令），但是这种方式可能不准，很难保证表操作和redi操作的事务一致性。

6.4 增加计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个mysql事务里操作。

MySql索引优化2