MySQL中慢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高并发集中访问某一张表，应当优先治理。

感谢各位的阅读！关于“MySQL中慢SQL优化的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！