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