详细聊聊MySQL中慢SQL优化的方向

OStack程序员社区-中国程序员成长平台 › 门户 › 编程›数据库

原作者: [db:作者] 来自: [db:来源] 收藏邀请

前言

影响一个系统的运行速度的原因有很多，是多方面的，甚至可能是偶然性的，或前端，或后端，或数据库，或中间件，或服务器，或网络等等等等，真正的去定位一个问题需要对系统有一定的认知，可以根据自身的判断去缩小问题范围。

今天不说其他的优化，单独把数据库的优化拿出来说几个优化方向。

跟系统的优化方向一样，数据库的优化，同样也是多方面的，其中涵盖着SQL语句的执行情况，数据库自身的情况等等，下面我们就来说一下MySQL数据库中的慢SQL语句优化方向，希望也能给到大家一些优化思路。

SQL语句优化

SQL语句的优化，有很多文章说起，也有很多在SQL编写上的指导；但是那种只能支持基本开发，如果要排查问题，那就不能单单的只是停留在SQL编写上了，而是有一个整体的发现问题的流程。

本次优化方向，大概分为发现慢查询SQL，查看并解析SQL执行计划，SQL编写上的优化，索引优化等几个方面。

记录慢查询SQL

MySQL中记录慢查询SQL是可以利用MySQL内部配置来实现的，这个配置就是slow_query_log配置。

可利用show variables like '%query%';查询出以下三个相关结果。

long_query_time     | 1.00000
slow_query_log      | off
slow_query_log_file | /data/mysql/mysql_slow.log

解释一下这三个参数，

long_query_time：如何区分SQL查询是慢查询，就要规定一个查询时间，超过这个时间的就归类于慢查询，此参数就是来设置时间范围的；以秒为单位，可以设置小数。
slow_query_log：此参数为是否开启记录慢查询SQL的开关，两个选择，on或者off，默认为off，所以在这里我们就知道如果要开启慢查询SQL记录，需要手动设置开启。
slow_query_log_file：慢查询SQL日志的文件路径，可以自行指定。

如何修改配置

有两个方法。

其一：修改my.ini或者是my.cnf文件，将此三项配置进行一个配置。

其二：直接在sqlplus中，使用set语法来修改参数，但是重启mysql数据库后就会失效，sql如下：

set global long_query_time = 10;

set global slow_query_log = on;

set global slow_query_log_file = /data/mysql/mysql_slow.log;

因为这个方法会重启失效，所以还是建议使用第一种方式。

查看慢查询日志

如何查询慢查询日志呢，如果量很小的情况下，其实是不需要使用工具的，完全可以直接打开即可。

如果量比较大，就需要mysqldumpslow工具查询会更方便。

mysqldumpslow是和mysqld相同类型的执行脚本，可以直接在命令行中执行，具体的使用方法如下：

mysqldumpslow参数：

-s，是order的顺序
-----al 平均锁定时间
-----ar 平均返回记录时间
-----at 平均查询时间（默认）
-----c 计数
-----l 锁定时间
-----r 返回记录
-----t 查询时间

-t，top，即为返回前面多少条的数据
-g，自定义正则表达式

举个例子，如下：

mysqldumpslow -s r -t 5 /data/mysql/mysql_slow.log

查询出返回记录集最多的5个慢查询SQL。

更多用法之后我建个测试库单独写篇文章细说一下。

查看SQL执行计划

查看执行计划关键词：EXPLAIN

如何使用

就是直接执行 EXPLAIN SELECT * FROM TABLE_NAME;

这个一开始我是打算简单说一下的，后来发现篇幅太长了，这个留待下篇文章里，感谢理解。

SQL编写优化

SQL的编写优化就很多了，我这里也整理出了一些，请大家自行查漏补缺。

查询语句无论是使用哪种判断条件等于、小于、大于, where左侧的条件查询字段不要使用函数或者表达式。
不要直接使用select *，而应该使用具体需要查询的表字段；select * 使用的是全表扫描，不会走索引的。
避免在 WHERE 字句中对字段进行 NULL 判断。
避免在 WHERE 中使用 != 或 <> 操作符。
使用 BETWEEN AND 替代 IN。
为常用搜索条件创建索引
选择正确的存储引擎, InnoDB 、MyISAM 、MEMORY 等，不同的场景下使用不同的存储引擎会有更好的效果。
使用 like %123% 不会走索引, 而使用 like 123% 会走索引。非常重要！！！
选择合适的字段类型。
设计字段时，要尽量使用NOT NULL。

为何要对慢SQL进行治理

从数据库角度看：每个SQL执行都需要消耗一定I/O资源，SQL执行的快慢，决定资源被占用时间的长短。假设总资源是100，有一条慢SQL占用了30的资源共计1分钟。那么在这1分钟时间内，其他SQL能够分配的资源总量就是70，如此循环，当资源分配完的时候，所有新的SQL执行将会排队等待。

从应用的角度看：SQL执行时间长意味着等待，在OLTP应用当中，用户的体验较差

治理的优先级上

master数据库->slave数据库
- 目前数据库基本上都是读写分离架构，读在从库（slave）上执行，写在主库（master）上执行。
- 由于从库的数据都是从主库上复制过去的，主库等待较多的，会加大与从库的复制时延。
执行次数多的SQL优先治理
如果有一类SQL高并发集中访问某一张表，应当优先治理。