MySQL慢查询分析

慢查询分析

了解MySQL数据库请求的处理流程后，我们通过开启profiling可以看到流程慢在哪。

mysql> set profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

开启后 SQL 语句的执行时间都会被记录下来，随后可以使用show profiles;查看具体的语句

mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.06811025 | select * from user where age>=60                  |
|        2 | 0.00151375 | select * from user where gender = 2 and age = 80  |
|        3 | 0.00230425 | select * from user where gender = 2 and age = 60  |
|        4 | 0.00070400 | select * from user where gender = 2 and age = 100 |
|        5 | 0.07797650 | select * from user where age!=60                  |
+----------+------------+---------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

随后根据 query id 查看具体耗时

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000074 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000034 |
| init                 | 0.000032 |
| System lock          | 0.000027 |
| optimizing           | 0.000020 |
| statistics           | 0.000058 |
| preparing            | 0.000018 |
| executing            | 0.000013 |
| Sending data         | 0.067701 |
| end                  | 0.000021 |
| query end            | 0.000015 |
| closing tables       | 0.000014 |
| freeing items        | 0.000047 |
| cleaning up          | 0.000027 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

1. starting: 查询开始，数据库准备执行查询的初始阶段。
2. checking permissions: 检查当前用户是否有权限执行。
3. Opening tables: 打开所需的表。
4. init: 初始化，设置执行所需的初始状态和变量。
5. System lock: 获取必要的系统锁，以确保在执行查询时数据的一致性和完整性。
6. optimizing: 查询优化阶段，数据库会评估不同的执行计划，以选择最优的查询策略。
7. statistics: 收集统计信息，数据库会分析表的结构和数据分布，以帮助生成更有效的查询计划。
8. preparing: 准备执行查询，包括解析 SQL 语句并准备执行所需的参数。
9. executing: 执行查询的核心阶段。数据库实际对数据进行操作，包括读取、更新、插入或删除数据。
10. Sending data: 将查询结果发送回客户端。通常是耗时最长的阶段。
11. end: 查询执行结束，数据库准备清理资源。
12. query end: 表示查询结束。
13. closing tables: 关闭之前打开的表。
14. freeing items: 释放与查询相关的内存资源，以防止内存泄漏。
15. cleaning up: 进行最终的清理工作，确保数据库状态的恢复和保持稳定。

索引分析

mysql 会在优化器阶段选择索引执行语句，explain可以帮助审查查询语句性能。

例如：explain select * from user where age>=60

通过它能看到用了哪些索引，大概会扫描多少行之类的信息，一般可以考虑几个因素：

• 选择这个索引大概要扫描多少行（rows）
• 为了把这些行取出来，需要读多少个 16kb 的页
• 走普通索引需要回表，主键索引则不需要，回表成本大不大？

可以关注的字段有type，extra，key，row

• type：ALL-全表扫描、index-全索引扫描、range-索引范围扫描
• key：表示查询中使用的索引
• rows：表示MySQL估计需要检索的行数

当索引不符合预期

可以通过force index指定索引

例如：explain select * from user force index(idx_age) where age>=60

走了索引还是慢

有些 sql，用explain命令看，明明是走索引的，但还是很慢。

一般是两种情况：

1. 索引区分度太低。比如网页全路径的 URL 链接，此类数据做索引会导致前缀大量重复。

   如果前缀索引的长度建得不够长，走索引跟走全表扫描一样，正确姿势是尽量让索引的区分度更高，比如大部分重复、固定的域名去掉，只拿后面 URI 部分去做索引。

   或是对长字符串取哈希，建立前缀索引，解决前缀相似性导致的热点。

2. 索引中匹配到的数据太大。这时候需要关注的是explain里的rows字段了。

   它用于预估这个查询语句需要查的行数的，它不一定完全准确，但可以体现个大概量级。

   当它很大时，一般常见的是下面几种情况。

   • 如果这个字段具有唯一的属性，比如电话号码等，一般是不应该有大量重复的，那可能是你代码逻辑出现了大量重复插入的操作，你需要检查下代码逻辑，或者需要加个唯一索引限制。
   • 如果这个字段下的数据就是会很大，是否需要全部拿？
     • 如果不需要，加个limit限制。
     • 如果确实要拿全部，建议分批次取，先用order by id排序，拿到一批数据后取最大id作为下次取数据的起始位置。

更多见小白、以及小白评论区