关于MySQL中慢SQL优化的方向介绍

ゝ一世哀愁。 2024-03-24 21:30 71阅读 0赞

![8c7221ce6946b228add70b94217c17f2.jpeg][]

由于在MySQL日常查询中，查询类型的语句占慢sql的大部分,所以下面这篇文章主要给大家介绍了关于MySQL中慢SQL优化方向的相关资料,文中通过示例代码介绍的非常详细,需要的朋友可以参考下

**导读**

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

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

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

### SQL语句优化 ###

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

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

#### 记录慢查询SQL ####

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

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>long_query_time&nbsp;&nbsp;&nbsp;&nbsp; | 1.00000</code></p> <p><code>slow_query_log&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | </code><code>off</code></p> <p><code>slow_query_log_file | /data/mysql/mysql_slow.log</code></p> </td> 
  </tr> 
 </tbody> 
</table>

解释一下这三个参数，

*  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如下：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> </td> 
   <td> <p><code>set</code> <code>global</code> <code>long_query_time = 10;</code></p> <p></p> <p><code>set</code> <code>global</code> <code>slow_query_log = </code><code>on</code><code>;</code></p> <p></p> <p><code>set</code> <code>global</code> <code>slow_query_log_file = /data/mysql/mysql_slow.log;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

##### 查看慢查询日志 #####

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

如果量比较大，就需要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优化方向的文章就介绍到这了,希望可以帮到你

转自：[微点阅读][Link 1] [https://www.weidianyuedu.com][https_www.weidianyuedu.com]

[8c7221ce6946b228add70b94217c17f2.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/8ab118f0dea7434e81dd0f74941a15fa.png
[Link 1]: https://m.weidianyuedu.com/
[https_www.weidianyuedu.com]: https://www.weidianyuedu.com