慢查询详解

朴灿烈づ我的快乐病毒、 2023-07-10 11:21 22阅读 0赞

### 文章目录 ###

*  一、为何要对慢 SQL 进行治理
 *  二、MySQL 执行原理
 *  三、慢查询
 *  四、SQL 语句常见优化

# 一、为何要对慢 SQL 进行治理 #

**从数据库角度看**：每个 SQL 执行都需要消耗一定 I/O 资源，SQL 执行的快慢，决定资源被占用时间的长短。假设总资源是 100，有一条慢 SQL 占用了 30 的资源共计 1 分钟。那 么在这 1 分钟时间内，其他 SQL 能够分配的资源总量就是 70，如此循环，当资源分配完的时候，所有新的 SQL 执行将会排队等待。

**从应用的角度看**：SQL 执行时间长意味着等待，在 OLTP 应用当中，用户的体验较差。 治理的优先级上

*  master 数据库->slave 数据库
 *  目前数据库基本上都是读写分离架构，读在从库(slave)上执行，写在主库  
    (master)上执行。
 *  由于从库的数据都是从主库上复制过去的，主库等待较多的，会加大与从库的复制时  
    延。
 *  执行次数多的SQL优先治理。
 *  如果有一类SQL高并发集中访问某一张表，应当优先治理。

# 二、MySQL 执行原理 #

绿色部分为 SQL 实际执行部分，可以发现 SQL 执行 2 大步骤:解析，执行。

以 com\_query 为例，dispatch\_command 会先调用 alloc\_query 为 query buffer 分配 内存，之后调用解析

解析:词法解析->语法解析->逻辑计划->查询优化->物理执行计划

检查是否存在可用查询缓存结果，如果没有或者缓存失效，则调用 mysql\_execute\_command 执行

执行:检查用户、表权限->表上加共享读锁->取数据到 query cache->取消共享读锁  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MDk1MDU1_size_16_color_FFFFFF_t_70]

# 三、慢查询 #

**查询 mysql 的操作信息**

show status; -- 显示全部 mysql 操作信息 
    show status like "com_insert%"; -- 获得 mysql 的插入次数;
    show status like "com_delete%"; -- 获得 mysql的删除次数;
    show status like "com_select%"; -- 获得 mysql 的查询次数;
    show status like "uptime"; -- 获得 mysql 服务器运行时间 
    show status like 'connections'; -- 获得 mysql 连接次数

`show [session|global] status like ....`如果你不写 `[session|global]`默认是 `session` 会话， 只取出当前窗口的执行，如果你想看所有(从 mysql 启动到现在，则应该 global)。

通过查询 mysql 的读写比例,可以做相应的配置优化;

**慢查询**

当 Mysql 性能下降时，通过开启慢查询来获得哪条 SQL 语句造成的响应过慢，进行分析处理。当然开启慢查询会带来 CPU 损耗与日志记录的 IO 开销，所以我们要间断性的打开慢查询日志来查看 Mysql 运行状态。

慢查询能记录下所有执行超过 long\_query\_time 时间的 SQL 语句, 用于找到执行慢的 SQL, 方便我们对这些 SQL 进行优化.

show variables like "%slow%";-- 是否开启慢查询;
     show status like "%slow%"; -- 查询慢查询 SQL 状况;
     show variables like "long_query_time"; -- 慢查询时间

**慢查询开启设置**

![在这里插入图片描述][20200302102730717.png]

mysql> set global slow_query_log='ON';-- 启用慢查询 ,加上global，不然会报错的;
    mysql> set global slow_query_log_file=’/var/lib/mysql/db-slow.log’; -- 设置日志目录
    mysql> set global long_query_time=2; -- 修改慢查询时间
    mysql> set global log_queries_not_using_indexes=1; -- 记录没有使用索引查询的语句

也可以在配置文件中更改  
修改 mysql 配置文件 my.ini\[windows\]/my.cnf\[Linux\]加入,注意必须在\[mysqld\]后面加 入

slow_query_log = on -- 开启日志; 
    slow_query_log_file = /var/lib/mysql/db-slow.log -- 记录日志的 log 文件; 注意:window 上必须写绝对路径,比如 D:/wamp/bin/mysql/mysql5.5.16/data/show-slow.log
    long_query_time = 2 -- 最长查询的秒数; 
    log-queries-not-using-indexes = 1 -- 表示记录没有使用索引的查询

**使用慢查询:**

**Example:**  
![在这里插入图片描述][20200302104042723.png]  
开启慢查询后每天都有可能有好几 G 的慢查询日志,这个时候去人工的分析明显是不实际的;可以使用慢查询分析工具mysqldumpslow

**查询分析工具mysqldumpslow**

该工具是慢查询自带的分析慢查询工具，一般只要安装了 mysql，就会有该工具;

**mysqldumpslow用法：**

mysqldumpslow[OPTS...][LOGS...] --后跟参数以及log文件的绝对地址;
    -s 按照那种方式排序
        c：访问计数
        l：锁定时间
        r:返回记录
        al：平均锁定时间
        ar：平均访问记录数
        at：平均查询时间
    -t 是top n的意思，返回多少条数据。
    -g 可以跟上正则匹配模式，大小写不敏感。

得到返回记录最多的20个sql

mysqldumpslow -s r -t 20 sqlslow.log

得到平均访问次数最多的20条sql

mysqldumpslow -s ar -t 20 sqlslow.log

得到平均访问次数最多,并且里面含有ttt字符的20条sql

mysqldumpslow -s ar -t 20 -g "ttt" sqldlow.log

我们使用上面提到的第二种方式修改好mysql的配置文件：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MDk1MDU1_size_16_color_FFFFFF_t_70 1]  
如图执行慢查询语句：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MDk1MDU1_size_16_color_FFFFFF_t_70 2]  
回到服务器 ，通过mysqldumpslow指令可以查看记录慢日志所在的日志。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MDk1MDU1_size_16_color_FFFFFF_t_70 3]

# 四、SQL 语句常见优化 #

1.  慢 SQL 消耗了 70%~90%的数据库 CPU 资源;
2.  SQL 语句独立于程序设计逻辑，相对于对程序源代码的优化，对 SQL 语句的优化在时 间成本和风险上的代价都很低;
3.  SQL 语句可以有不同的写法;

**以下是优化意见:**

**1.不使用子查询**

SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');

子查询在 MySQL5.5 版本里，内部执行计划器是这样执行的:先查外表再匹配内表，而不是  
先查内表 t2，当外表的数据很大时，查询速度会非常慢。  
在 MariaDB10/MySQL5.6 版本里，采用 join 关联方式对其进行了优化，这条 SQL 会自动转  
换为

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

但请注意的是：优化只针对 SELECT 有效，对 UPDATE/DELETE 子查询无效，故生产环境应  
避免使用子查询

**2.避免函数索引**

例:

SELECT * FROM t WHERE YEAR(d) >= 2016;

由于 MySQL 不像 Oracle 那样支持函数索引，即使 d 字段有索引，也会直接全表扫描。  
应改为----->

SELECT * FROM t WHERE d >= '2016-01-01';

**3.用 IN 来替换 OR**  
低效查询

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

\----->  
高效查询

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

**4.LIKE 双百分号无法使用到索引**

SELECT * FROM t WHERE name LIKE '%de%';

\----->

SELECT * FROM t WHERE name LIKE 'de%';

**5、读取适当的记录 LIMIT M,N**

SELECT * FROM t WHERE 1;

\----->

SELECT * FROM t WHERE 1 LIMIT 10;

**6、避免数据类型不一致**

SELECT * FROM t WHERE id = '19';

\----->

SELECT * FROM t WHERE id = 19;

**7、分组统计可以禁止排序**

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

默认情况下，MySQL 对所有 GROUP BY col1，col2…的字段进行排序。如果查询包括  
GROUP BY，想要避免排序结果的消耗，则可以指定 ORDER BY NULL 禁止排序。  
\----->

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

**9、禁止不必要的 ORDER BY 排序**

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;

\----->

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MDk1MDU1_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200302102249223.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MDk1MDU1,size_16,color_FFFFFF,t_70
[20200302102730717.png]: https://img-blog.csdnimg.cn/20200302102730717.png
[20200302104042723.png]: https://img-blog.csdnimg.cn/20200302104042723.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MDk1MDU1_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200303104743830.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MDk1MDU1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MDk1MDU1_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200303104816269.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MDk1MDU1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MDk1MDU1_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200303104850997.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MDk1MDU1,size_16,color_FFFFFF,t_70