Mysql优化---常见优化方法和慢查询排查

8.一些其他的优化方法

（1）exist 和 in

select ..from table where exist (子查询)

    select ..from table where 字段 in (子查询)

如果主查询的数据集大，则使用in 如果子查询的数据集大，则使用exist exist语法：将主查询的结果放到子查询中进行条件校验，看是否有数据，如果有数据，则校验成功，如果复合校验，则保留数据； （2）order by 优化 using filesort 有两种算法：双路排序、单路排序（根据IO的次数） 注意：表最终是以文件形式保存在磁盘中 MySQL4.1之前 默认使用 双路排序； 扫描磁盘两次 （1. 从磁盘读取排序字段，对排序字段进行排序（在buffer中进行排序）2. 扫描其他字段）—IO较消耗性能 MySQL4.1之后 默认使用 单路排序：只读取一次（全部字段），在buffer中进行排序。但这种单路排序会有一定的隐患（不一定会是单路（IO），可能多次IO操作）。如果数据量大的话，无法将数据全部读入缓冲区，因此需要分片读取，多次IO。 注意：单路排序比多路排序会多占用buffer， 因此可以调整buffer容量。 提高order by查询的策略： 选择使用单路、双路：调整buffer的容量大小; 比如：set max_length_for_sort_data = 1200 避免select * … 复合索引，不要跨列使用。避免using filesort 保证全部的排序字段 排序的一致性（都是升序或者降序）

9. SQL排序 - 慢查询日志：MySQL提供的一种日志记录，用于记录MySQL响应时间超过阈值的SQL语句（long_queue_time, 默认10秒） 慢查询日志默认是关闭的，建议：开发调优是关闭的，而最终部署是关闭的。

检查是否开启了慢查询日志：

show variables like "%slow_query_log%";

临时开启：

set global slow_query_log = 1; --在内存中开启`
   
   exit
   
   service mysql restart

永久开启：

/etc/my.cnf 中追加配置
   vi /etc/my.cnf
   [mysqld]
   slow_query_log = 1
   slow_query_log_file = /var/lib/mysql/localhost-slow.log

慢查询阈值：

show variables like %long_query_time%;

临时设置阈值：

set global long_query_time = 5; --设置完毕后，重新登录后起效（不需要重启服务）

永久设置阈值：

/etc/my.cnf 中追加配置
vi /etc/my.cnf
[mysqld]
long_query_time=3