12.s查询截取分析

查询优化

1. 观察，至少跑一天，看看生产的慢SQL情况
2. 开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来
3. explain+慢SQL分析
4. show profile
5. 运维经理 or DBA，进行数据库服务器参数的调优

===总结

1. 慢查询的开启并捕获
2. explain+慢SQL分析
3. show profile查询SQL在mysql服务器里面执行细节和生命周期情况
4. SQL数据库服务器的参数调优

优化原则：小表驱动大表

   ####################原理(RBO)#######################
   select * from A where id in (select id from B)
   #等价于
   for select id from B
   for select * from A where A.id = B.id
   #当B表的数据集必须小于A表的数据集时，用in优于exist
   select * from A where id exists(select 1 from B where B.id = A.id)
   #等价于
   for select * from A
   for select * from B where B.id = A.id
   #当A表的数据集必须小于B表的数据集时，用exists优于in

注意查询顺序问题

• EXISTS

SELECT __ FROM table WHERE EXISTS(subquery)

**可以理解为:**将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE or FALSE）来决定著查询的数据结果是否保留

EXISTS(subquery)只返回TRUE or FALSE，因此子查询中的SELECT*也可以时SELECT 1或select ‘X’,官方的说法是实际执行时会忽略SELECT清单，因此没有区别。

EXISTS子查询执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。

EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体情况具体分析。

• ORDERBY

1. 尽量使用Index方式排序，避免使用filesort方式

   满足两种情况会使用Index排序

   • ORDER BY 语句使用引索最前列
   • 使用where子句与Order BY子句条件列组合满足最左前列

   #using index
   explain SELECT * FROM tblA WHERE age > 20 ORDER BY age;
   
   #using index,using filesort
   explain SELECT * FROM tblA WHERE age > 20 ORDER BY birth;
   
   #using index,using filesort
   explain SELECT * FROM tblA WHERE age > 20 ORDER BY birth,age;
   
   #using index
   explain SELECT * FROM tblA WHERE birth > '2016-01-28 00:00:00' ORDER BY birth;
   
   #using filesort  引索默认升序
   explain SELECT * FROM tblA ORDER BY age ASC,birth DESC;
   
   

2. 尽可能在索引列上完成排序操作，遵照索引最佳左前缀

3. 如果不在索引列上，filesort有两种算法，双路排序和单路排序

   • 双路(4,1之前)：两次扫描磁盘 ==> 读取行指针和orderby列，对他们进行排序，然后扫描已经排好的列表，按照表中的值重新从列表中读取对应数据输出，从磁盘中取排序字段，在buffer进行排序，再从磁盘读取其他字段。
   • 单路排序：从磁盘读取查询所需要的所有列，按照ORDER BY在buffer对它进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在了内存里。

有可能取出的数据总大小超出了sort_buffer 的总容量，导致每次只能取sort_buffer容量大小的数据，进行排序排完再取，从而进行多次I/O，反而导致大量I/O操作，反而得不偿失。

调优：

​ 1.order by时不要加select *

​ 1.1 当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BOLB类型时，会用改进后的算法，否则使用—多路排序

​ 1.2 两种算法的数据都有可能超出sort_buffer的容量时，超出之后，会创建tmp文件进行合并排序，导致多次I/O，用单路排序的风险更大

​ 2.尝试提高sort_buffer_size

​ 3.尝试提高max_length_for_sort_data

​ 如果设得太大，总容量超出sort_buffer_sizes的概率就增大，明显症状就是高的I/O活动和低的处理器使用率。