【MySQL】性能调优（三）：SQL。慢查询日志及SQL优化建议

迈不过友情╰ 2022-11-22 05:17 208阅读 0赞

问题：在我们做项目的时候，如何找到那些耗时比较长的查询语句呢？我们的服务层每天执行了这么多SQL语句，它怎么知道哪些SQL语句比较慢呢？第一步，我们要把SQL执行情况记录下来 ⇒ 慢查询日志 [slow query log][] .

## 1.慢日志 ##

### 1.1 开启慢日志 ###

因为开启慢查询日志是有代价的（跟 binlog、optimizer-trace一样），所以它默认是关闭的：

show variables like 'slow_query%';
    set GLOBAL slow_query_log="ON";

![在这里插入图片描述][20201103221300455.png_pic_center]

除了这个开关，还有一个参数，控制执行超过多长时间的SQL才记录到慢日志，默认是10秒。

show variables like '%long_query%';  -- 默认10s
    
    -- 可以直接动态修改参数（重启后失效）：
    set @@ global.slow_query_log=1; -- 1 开启，0 关闭，重启后失效 
    set @@ global.long_query_time=3; -- mysql 默认的慢查询时间是 10 秒，另开一个窗口后才会查到最新值  
    
    show variables like '%long_query%';
    show variables like '%slow_query%';

或者修改配置文件my.cnf。以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。

slow_query_log=ON 
    long_query_time=2 
    slow_query_log_file=/var/lib/mysql/localhost-slow.log

### 1.2 慢日志内容 ###

模拟慢查询：

SELECT SLEEP(10); -- 过10s后再查询，所以后面的sql语句都会进入慢查询日志
    SELECT * FROM user_innodb WHERE name='张三';

show global status like 'slow_queries'; -- 查看有多少慢查询 
    show variables like '%slow_query%'; -- 获取慢日志目录

然后在服务器上打开相应日志文件

cat /var/lib/mysql/bab8d9419ec6-slow.log

![在这里插入图片描述][20201103223445436.png_pic_center]

有了慢查询日志，怎么去分析统计呢？比如SQL语句的出现的慢查询次数最多，平均每次执行了多久？

### 1.3 分析工具：[mysqldumpslow][] ###

MySQL提供了mysqldumpslow 的工具，在MySQL的bin目录下。

mysqldumpslow --help

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]

例如：查询用时最多的20条慢SQL：

mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/bab8d9419ec6-slow.log > /data/slow.log

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 1][]

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Count</td> 
   <td>代表这个SQL执行了多少次</td> 
  </tr> 
  <tr> 
   <td>Time</td> 
   <td>代表执行的时间，括号里面是累计时间</td> 
  </tr> 
  <tr> 
   <td>Lock</td> 
   <td>表示锁定的时间，括号是累计</td> 
  </tr> 
  <tr> 
   <td>Rows</td> 
   <td>表示返回的记录数，括号是累计</td> 
  </tr> 
 </tbody> 
</table>

除了慢查询日志之外，还有一个 SHOW PROFILE工具可以使用。

### 1.4 分析工具：[SHOW PROFILE][] ###

SHOW PROFILE 是谷歌高级架构师 JeremyCole 贡献给 MySQL 社区的，可以查看 SQL 语句执行的时候使用的资源，比如 CPU、IO 的消耗情况。在 SQL 中输入 help profile 可以得到详细的帮助信息。

1.  查看是否开启

select @@profiling;
    set @@profiling=1;

1.  查看 profile 统计

show profiles; -- 命令最后带一个s
    show profile;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]  
5.7E-5，小数点左移5位，代表0.000057秒。也可以根据ID查看执行详细信息，在后面带上for query + ID。

show profile for query 1;

除了慢日志和show profile，如果要分析出当前数据库中执行的慢的SQL，还可以通过查看运行线程状态和服务器运行信息、存储引擎信息来分析。

## 2.SQL 优化建议 ##

当我们的SQL语句比较复杂，有多个关联和子查询的时候，就要分析SQL语句有没有改写的方法。举个简单的例子，一模一样的数据：

-- 大偏移量的 limit
    SELECT * FROM user_innodb LIMIT 900000,10;
    
    -- 改成先过滤 ID，再 limit 
    SELECT * FROM user_innodb WHERE id>=900000 LIMIT 10;

对于具体的 SQL 语句的优化，MySQL官网也提供了很多建议。所以，下面就简单列举工作中可能会用到的几条。

### 2.1 使用预编译 ###

程序中通常是根据用户的输入来动态执行SQL，这时应该尽量使用参数化SQL，这样不仅可以避免SQL注入漏洞攻击，最重要数据库会对这些参数化SQL进行预编译，这样第一次执行的时候DBMS会为这个SQL语句进行查询优化，并且执行预编译，这样以后再执行这个SQL的时候就直接使用预编译的结果，这样可以大大提高执行的速度。

### 2.2 使用表别名 ###

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些有列名歧义引起的语法错误。

### 2.3 多条SQL语句压缩到一句SQL中 ###

每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果，这个过程是非常耗时的，因此应该尽量避免过多的执行SQL语句，能够压缩到一句SQL执行的语句就不要用多条来执行。

### 2.4 关于in和exists ###

在A表中查出所有B表的记录，`in` 和 `exists`（注：`exists`返回的是true / false）都可以实现，但一般需要根据两表大小关系判断具体使用哪个：

*  A > B : `select * from A where id in (select id from B)`
 *  A < B : `select * from A where exists(select 1 from B.id = A.id)`

### 2.5 union all 替换 union ###

当SQL语句需要union两个查询结果集合时，即使检索结果中不会有重复的记录，如果使用union这两个结果集，同样会尝试进行合并，然后在输出最终结果前进行排序。

因此如果可以判断检索结果中不会有重复的记录时候，应该用union all，这样效率就会因此得到提高。

### 2.6 where 替换 having ###

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。

HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

### 2.7 只在必要的情况下才使用事务begin transaction ###

SQL Server中**一句SQL语句默认就是一个事务**，在该语句执行完成后也是默认commit的。其实，这就是begin tran的一个最小化的形式，好比在每句语句开头隐含了一个begin tran，结束时隐含了一个commit。

有些情况下，我们需要显式声明begin tran，比如做“插、删、改”操作需要同时修改几个表，要求要么几个表都修改成功，要么都不成功。begin tran 可以起到这样的作用，它可以把若干SQL语句套在一起执行，最后再一起commit。 好处是保证了数据的一致性，但任何事情都不是完美无缺的。Begin tran付出的代价是在提交之前，所有SQL语句锁住的资源都不能释放，直到commit掉。

可见，如果Begin tran套住的SQL语句太多，那数据库的性能就糟糕了。在该大事务提交之前，必然会阻塞别的语句，造成block很多。

Begin tran使用的原则是，在保证数据一致性的前提下，begin tran 套住的SQL语句越少越好！有些情况下可以采用触发器同步数据，不一定要用begin tran。

### 2.8 考虑使用“临时表”暂存中间结果 ###

简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。但是也得避免频繁创建和删除临时表，以减少系统表资源的消耗。

[slow query log]: https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
[20201103221300455.png_pic_center]: /images/20221120/551fb39a98644bb2a13b7fd0e702da2d.png
[20201103223445436.png_pic_center]: /images/20221120/d21b987ae27c433cb2255135be2b946a.png
[mysqldumpslow]: https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/872b5189e809432481fe82102e4b9444.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/d0de4f74122049b28a69b30c9874e698.png
[SHOW PROFILE]: https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/adc4c094d61340d6b46be02128116295.png