MySQL语句调优（高级篇）

1、多表查询

1. A left join B中，A表必定是驱动表；理论上只加B表索引即可，因为A表始终需要全表扫描；
   

2. A inner join B中，MySQL会自动选择结果集少的当做驱动表
   

3. A straight_join B中，查询结果和inner join一样，但是会强制将A表作为驱动表

4. 子查询尽量不要放在被驱动表，可能会不走索引；所以left join时，尽量使用实体表作为被驱动表，而不是子查询产生的虚表当做被驱动表。

5. 能够多表直接关联的，就不要使用子查询；

子查询分为相关子查询与非相关子查询：
1、非相关子查询与外部查询的数据无关，只会执行一次，之后将得到的结果传递给外部查询）；
2、相关子查询依赖于外部查询的数据，外部查询执行一次，相关子查询就会跟着执行一次！要注意子查询所处的位置，select后的可以理解为left join，而where后的可理解为inner join，位置不同产生的结果也不同。

2、子查询优化

1. 在范围判断时，尽量不要使用 not in 和 not exists，使用 left join on xxx is null 代替。
   
   改写后
   

3、排序与分组的优化

1. 没有使用过滤条件的，不会走索引。where、limit都算过滤条件

2. 索引顺序与排序字段的顺序保持一致
   

3. 排序的方式必须一致，要么一起升，要么一起降，否则会导致文件内排序
   

4. 索引的选择：当范围过滤与order by或者group by冲突时，查看范围过滤条件是否能过滤大部分结果集，因为order by与group by总是在where过滤完之后才执行。由于范围过滤之后的索引会失效，所以优先选用范围过滤的字段作为索引。
   

5. MySQL排序算法：4.1之前是双路排序，也就是扫描2次磁盘得到结果。4.1之后是单路排序，只扫描一次磁盘获取需要查询的列，将需要order by的列加载到内存进行排序后再返回。

1、增大 sort_butter_size 参数的设置
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进
程的 1M-8M 之间调整。
2、增大 max_length_for_sort_data 参数的设置
mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data。
提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，
明显症状是高的磁盘 I/O 活动和低的处理器使用率。（1024-8192 之间调整）。
3、减少 select 后面的查询的字段当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的
算法——单路排序， 否则用老算法——多路排序。
两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，
但是用单路排序算法的风险会更大一些,所以要提高 sort_buffer_size。

6. 覆盖索引，select需要查询的列与索引一致即可
7. group by与order by一样，唯一不同是它可以不经过条件过滤使用索引。

select * from emp group by age;

4、慢日志查询

1. 某些sql查询很慢，所以我们需要单独剔出来对它进行分析。默认情况下，MySQL不会开启这个，会影响性能，调优完成后记得关闭！

show variables like '%slow_query_log%';

2. 开启慢查询日志

set global slow_query_log=1;

3. 查看慢查询阈值（默认超过10秒的sql，就记录到慢查询日志）

show variables like '%long_query_time%';

4. 修改慢查询阈值（修改为3s）

set long_query_time=3;

5、日志分析工具mysqldumpslow（用来从日志中找到自己需要的）

1. 参数及描述

-s 是表示按照何种方式排序
c 访问次数
l 锁定时间
r 返回记录
t 查询时间
al 平均锁定时间
ar 平均返回记录数
at 平均查询时间
-t 即为返回前面多少条的数据
-g 后边搭配一个正则匹配模式，大小写不敏感的

2. 用法示例

1、得到返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 G:\soft\mysql-5.7.25\data\SD--20151014VSR-slow.log
2、得到访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 G:\soft\mysql-5.7.25\data\SD--20151014VSR-slow.log
3、得到按照时间排序的前 10 条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" G:\soft\mysql-5.7.25\data\SD--20151014VSR-slow.log
4、另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 G:\soft\mysql-5.7.25\data\SD--20151014VSR-slow.log | more

6、processlist与show profiling

1. processlist用来查看当前MySQL的进程列表，杀死故障进程

show processlist;
kill 7;

2. 诊断SQL的每个步骤，show profile

-- 1、开启
set profiling=on;
show variables like 'profiling';
-- 2、执行SQL
select * from emp;
-- 3、诊断最近执行的15条SQL
show profiles;
-- 4、根据第三步的id，进一步诊断
show profile cpu,block io for query 262;

3. show profile 的参数有如下选择：

all				 	--显示所有的开销信息
block io 			--显示块IO相关开销
context switches 	-- 上下文切换相关开销
cpu 				--显示CPU相关开销
ipc 				--显示发送和接收相关开销信息
memory 				--显示内存相关开销
page faults			--显示页面错误相关开销
source 				--显示source_function等相关开销
swaps 				--显示交换次数相关开销

4. show profile 的结果出现如下4个，必须优化！

1、converting HEAP to MyIASM：查询结果太大，内存不够用了往磁盘上搬
2、Creating tmp table：创建临时表，拷贝数据到临时表，用完再删除
3、Coping to tmp table on disk：把内存中临时表复制到磁盘
4、Locked