造成慢查询的原因:
- 没有索引或者没有用到索引(常见,程序设计缺陷)
- I/O 吞吐量小,形成瓶颈效应
- 没有创建计算列导致查询不优化
- 内存不足
- 网络速度慢
- 查询出的数据量过大(可采用多次查询或其他方法降低数据量)
- 锁或者死锁(常见,程序设计缺陷)
- sp_lock,sp_who,活动的用户查看,原因是读写竞争资源(可采用读写分离)
- 返回了不必要的行和列
- 查询语句不好,没有优化
可以通过如下方法来优化查询:
- 把数据、日志、索引放到不同的 I/O 设备上,增加读取速度,数据量越大,提高 I/O 越重要
- 纵向、横向分割表,减少表的尺寸(sp_spaceuse)
- 升级硬件
- 根据查询条件,建立索引,优化索引,优化访问方式,限制结果集的数据量。注意填充因子要适当(最好是使用默认值)。索引应该尽量小,使用字节数小的列建立索引,不要对有限的几个值得字段建立单一索引
- 提高网速
- 扩大服务器内存
定位慢查询的方法:
- 使用 explain 等工具分析 SQL(explain select ......)
- 慢查询日志操作
慢日志查询参数代表含义:
- slow_query_log:是否开启慢查询日志,ON-开启,OFF-关闭
- slow_query_log_file:MySQL数据库慢查询日志存储路径(5.6及以上版本),可以不设置该参数,系统或默认给一个缺省的文件host_name_slow.log
- long_query_time:慢查询阈值,当查询时间多于设定的阈值时,记录日志。
- log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)
- log_output:日志存储方式,FILE-表示将日志存入文件,是默认值,TABLE-表示将日志存入数据库,这样日志信息会被写入到mysql.slow_log 表中
MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如 'FILE,TABLE',日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,建议优先记录到文件。
查看慢日志是否开启(ON-开启,OFF-关闭):show variables like 'slow_query_log';
开启慢日志查询:set global slow_query_log = 'ON';
设置慢查询阈值:set global long_query_time = 5;
默认值为10秒,可以使用命令修改,也可以在 my.cnf 参数里修改,只记录大于设定阈值的查询,等于阈值时不会被记录,如果记录到表中,只会记录整数部分,不会记录微秒部分。
开启系统变量:set global log_queries_not_using_indexes = 1;
如果调优的话,建议开启这个选项,开启了这个参数,使用 full index scan 的 sql 也会被记录到慢查询日志。
注意:命令行操作 MySQL 重启后会失效,如果要永久生效,需要修改配置文件 my.ini 对应配置