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

时间：2022-08-26 15:19:41

前言

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

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

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

SQL语句优化

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

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

记录慢查询SQL

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

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

?
1
2
3
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如下：

?
1
2
3
4
5
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优化方向的文章就介绍到这了,更多相关MySQL慢SQL优化方向内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://juejin.cn/post/7001076996352507934

标签：

相关文章