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

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

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

跟系统的优化方向一样,数据库的优化,同样也是多方面的,其中涵盖着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的编写优化就很多了,我这里也整理出了一些,请大家自行查漏补缺。

  1. 查询语句无论是使用哪种判断条件 等于、小于、大于, where左侧的条件查询字段不要使用函数或者表达式。

  2. 不要直接使用select *,而应该使用具体需要查询的表字段;select * 使用的是全表扫描,不会走索引的。

  3. 避免在 WHERE 字句中对字段进行 NULL 判断。

  4. 避免在 WHERE 中使用 !=<> 操作符。

  5. 使 用 BETWEEN AND 替代 IN

  6. 为常用搜索条件创建索引

  7. 选择正确的存储引擎, InnoDBMyISAMMEMORY 等,不同的场景下使用不同的存储引擎会有更好的效果。

  8. 使用 like %123% 不会走索引, 而使用 like 123% 会走索引。非常重要!!!

  9. 选择合适的字段类型。

  10. 设计字段时,要尽量使用NOT NULL。

总结

这里面远远还没有讲全,还有很多种编写规则,同时还有索引的建立并没有聊,留给大家一些自己看书的时间,希望大家有所进步。

收藏 (0)
评论列表
正在载入评论列表...
我是有底线的
为您推荐
    暂时没有数据