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
- 保证全部的排序字段 排序的一致性(都是升序或者降序)
- 选择使用单路、双路:调整buffer的容量大小;
- 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