前言
上篇讲解了索引搜索优化，其实索引只是sql查询优化的一部分，本篇主要讲解的是sql优化主要要优化的部分！
内容
一.order by 优化 |
- orderby最好使用index排序方式，避免使用FileSort方式排序；
- 在索引列上完成排序，遵照索引最佳做前缀
- orderby最好不要使用select *；
- 如果使用fileSort方式，尝试提高sort_buffer_size
- 如果使用fileSort方式，尝试提高max_length_for_sort_data
使用filesort排序优化策略
orderby最好不要使用select *；
提高max_length_for_sort_data
提高sort_buffer_size
【解答】：
1.为什么不用select *？
当query的字段大小总和小于max_length_sort_data，而且排序字段不是text|blob类型，会用改进后的算法单路排序，否则多路排序。
1.双路排序，两次扫描磁盘，最终得到数据；读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的数据从磁盘中取排序的字段，在buffer进行排序，再从磁盘中去其他字段（mysql4.1之前）
2.单路排序，从磁盘读取需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且报随机I/O变成了顺序I/O，但是它会使用更多的空间。
2.为什么要提高sort_buffer_size?
上诉两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次的i/O操作，但是单路排序的算法风险会更大一些,所以提高sort_buffer_size.
3.如何提高sort_buffer_size和max_length_for_sort_data参数值？
查看本sql的sort_buffer_size;
show variables like '%sort_buffer%';+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size | 262144 |
+-------------------------+---------+其中以后字节为单位的，所以sort_buffer_size=262144/1024=256KB
设置本sql的中sort_buffer_size:
SET GLOBAL sort_buffer_size = 1024*1024; //2MB的buffer大小3.是不是buffer的大小越大越好？
Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。
参考博客：
order by索引方式优化策略
orderby最好不要使用select *；
在索引列上完成排序，遵照索引最佳做前缀
创建复合索引：
create index idx_three on tblA(age,birth,addr);1.索引最左前缀，orderby 使用索引有效
select * from tblA order by age;
select * from tblA order by age,birth;
select * from tblA order by age, birth,addr;2.where索引的最左前缀为常量，orderby 使用索引有效
第一种情况最普遍：
explain select * from tblA where age=24 order by birth,addr;第二种情况，如果birth使用了范围，但是由于头索引生效了，所以order by使用索引生效。
mysql> explain select * from tblA where age=24 and birth >'2018-07-11 18:09:10' order by birth,addr;| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+--------------------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tblA | NULL | range | idx_A_ageBirth,idx_three | idx_three | 9 | NULL | 1 | 100.00 | Using where; Using index |【注意】
三种情况导致索引失效：
1.复合索引头索引不存在；
explain select * from tblA order by birth,addr;+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | tblA | NULL | index | NULL | idx_three | 100 | NULL | 3 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+1.复合索引中间的索引不存在
explain select * from tblA where age=24 and birth >'2018-07-11 18:09:10' order by addr;+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | tblA | NULL | index | NULL | idx_three | 100 | NULL | 3 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+报告中分析extra指标中存在 using filesort说明这条语句写的糟糕
3.order by中存在了不是复合索引中的字段
explain select * from tblA order by age,birth,addr,xing1;4.范围查询也会导致索引失效;
explain select * from tblA where age in (22,24) order by birth,addr;+----+-------------+-------+------------+-------+--------------------------+-----------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+--------------------------+-----------+---------+------+------+----------+------------------------------------------+
| 1 | SIMPLE | tblA | NULL | range | idx_A_ageBirth,idx_three | idx_three | 5 | NULL | 2 | 100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+--------------------------+-----------+---------+------+------+----------+------------------------------------------+查看分析中type为range，说明是范围查询，实际用了idx_three，但是依旧存在filesort说明语句还是糟糕。像这种情况我们只能根据需求的不同，创建合适的索引了。
优化原则：
小表驱动大表，小的数据集驱动大的数据集。
【分析】：
子查询结果集必须要比主查询结果集大，因为in和exists本质是一个for循环，内部多次循环可以减少从内部到外部的时间，减少执行的时间。
in和exists语句中，in 后面的语句先执行，所以是外循环，必须要保证in后面的表比in前面的表小；exists前面的语句先执行，所以是外循环，必须要保证前面表的数据集小于后面的表：
select * from tb1_emp e where e.deptId in (select id from tb1_dept d);select * from tb1_emp e where exists(select 1 from tb1_dept d where d.id=e.deptiD);二.索引优化 |
可以参照我之前博客：【mysql学习三】——索引搜索优化
三.group by |
- 本质排序好了进行分组，遵照索引建的最佳前索引
- 使用filesort方式时，增大max_length_for_sort_data参数设置
- 使用filesort方式时，增大sort_buffer_size参数设置
- where高于having，能写where限定条件就不要去写having的限定
【分析】
由图中中可以看出来，执行的顺序依次是from,on,(join,where),group by ,having,select ,order by limit;
四.join优化 |
1，尽可能减少Join 语句中的Nested Loop 的循环总次数，用小结果集驱动大结果集； 注意不是：小表连接大的快，而是结果集
2，优先优化Nested Loop 的内层循环； 做索引
3，保证Join 语句中被驱动表上Join 条件字段已经被索引；
4，扩大join buffer的大小；五.limit优化 |
数据量少的时候我们可以使用
Select * from A order by id limit 1,10;但是数据量大的时候，可以在id上建立一个索引，写成如下的方式：
Select * from A where id>=(Select id from a limit 10000000,1) limit 10;或者写成如下：
Select * from A where id between 10000000and 10000010;六.where子句优化 |
- 去除不必要的括号
- 去除不必要的条件判断
- 需要取出一条语句，使用limit：
Select * from A where namelike ‘%xxx’ limit 1; // 防止引擎继续扫描表或者索引参考博客：
总结
sql查询优化讲解完了，如果你遇到其他的优化解决方案，咱们再一起讨论！欢迎访问我的博客，希望对你有帮助
