排查MySQL查询慢需先开启慢查询日志，通过mysqldumpslow分析日志定位慢SQL，再用EXPLaiN查看执行计划，重点观察type和Extra字段判断是否全表扫描或使用临时表；接着优化索引，如在WHERE条件列建索引、使用复合索引并注意顺序，避免对索引列使用函数；同时优化SQL语句，如避免SELECT *、用UNION替代OR、优先使用JOIN和EXISTS；还需监控QPS、连接数等性能指标，处理死锁时统一加锁顺序，最终根据业务选择InnoDB等合适存储引擎。

MySQL查询慢？这可能是每个DBA都头疼的问题。简单来说，排查慢查询，就是要找到瓶颈，然后对症下药。

排查MySQL查询慢的问题，可以从多个维度入手，从开启慢查询日志到分析执行计划，再到优化索引和SQL语句，最终找到性能瓶颈并解决。

如何开启MySQL慢查询日志？

慢查询日志是定位慢查询的利器。开启它很简单，但需要根据你的MySQL版本和配置方式进行调整。

首先，你需要登录MySQL服务器。然后，可以通过以下命令查看慢查询日志的状态：

SHOW VARIABLES LIKE 'slow_query_log%';

如果slow_query_log是OFF，你需要开启它。有两种方式：

1. 临时开启：

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL slow_query_log_file = '/path/to/your/slow_query.log'; -- 设置日志文件路径

这种方式重启MySQL服务后会失效。

2. 永久开启：

修改MySQL的配置文件（通常是my.cnf或my.ini），在[mysqld]部分添加或修改以下配置：

slow_query_log = ON slow_query_log_file = /path/to/your/slow_query.log long_query_time = 2  # 设置慢查询阈值，单位秒，这里设置为2秒 log_output = FILE      # 将日志输出到文件，也可以设置为TABLE

修改配置文件后，需要重启MySQL服务才能生效。

设置long_query_time非常重要，它决定了哪些查询会被记录到慢查询日志中。通常建议设置为1-5秒，具体根据你的业务需求调整。

另外，log_output设置为FILE会将慢查询记录到文件中，方便后续分析。设置为TABLE会将慢查询记录到mysql.slow_log表中，可以使用SQL语句进行查询和分析。

如何使用mysqldumpslow分析慢查询日志？

有了慢查询日志，下一步就是分析它。OFF0是一个非常有用的工具，它可以帮你统计慢查询日志中的查询，并按照不同的指标进行排序。

OFF0通常位于MySQL的OFF2目录下。使用方法如下：

mysqldumpslow -s t -t 10 /path/to/your/slow_query.log

这条命令会按照查询时间（OFF3）排序，显示前10条（OFF4）慢查询。

常用的选项包括：

• OFF5: 排序方式，常用的有OFF6 (查询时间), OFF7 (查询次数), OFF8 (锁定时间), OFF9 (返回记录数)
• my.cnf0: 显示的条数
• my.cnf1: 使用正则表达式过滤查询语句，例如my.cnf2

OFF0的输出结果会告诉你哪些SQL语句执行次数最多、执行时间最长，从而帮助你快速定位问题。

例如，输出结果可能如下：

Reading mysql slow query log from /path/to/your/slow_query.log Count: 1  Time=3.21s (3s)  Lock=0.00s (0s)  Rows=1000 (1k), root[root]@localhost   SELECT * FROM user WHERE id = N

这条记录表示有一个查询my.cnf4执行了1次，花费了3.21秒，锁定了0秒，返回了1000行数据。

如何使用EXPLAIN分析SQL语句？

定位到慢查询后，下一步就是分析SQL语句的执行计划。my.cnf5命令可以告诉你MySQL是如何执行你的SQL语句的，包括使用了哪些索引、扫描了多少行数据等等。

使用方法很简单，只需要在你的SQL语句前面加上my.cnf5即可：

EXPLAIN SELECT * FROM user WHERE id = 1;

my.cnf5会返回一个结果集，其中包含多个字段，例如：

• my.cnf8: 查询的标识符，表示查询中执行select子句或操作表的顺序。
• my.cnf9: 查询的类型，例如my.ini0 (简单查询), my.ini1 (主查询), my.ini2 (子查询) 等。
• my.ini3: 访问的表名。
• my.ini4: 表分区信息。
• my.ini5: 访问类型，这是最重要的字段之一，表示MySQL是如何查找表中的行的。常见的类型有my.ini6 (全表扫描), my.ini7 (索引扫描), my.ini8 (范围扫描), my.ini9 (使用非唯一索引), [mysqld]0 (使用唯一索引), [mysqld]1 (常量) 等。
• [mysqld]2: 可能使用的索引。
• [mysqld]3: 实际使用的索引。
• [mysqld]4: 索引的长度。
• my.ini9: 索引的哪一列被用于查找值。
• [mysqld]6: MySQL估计需要扫描的行数。
• [mysqld]7: 过滤的百分比。
• [mysqld]8: 额外的信息，例如[mysqld]9 (使用了覆盖索引), long_query_time0 (使用了WHERE条件过滤), long_query_time1 (使用了临时表), long_query_time2 (使用了文件排序) 等。

重点关注my.ini5和[mysqld]8字段。如果my.ini5是my.ini6，表示全表扫描，性能通常很差，需要优化。如果[mysqld]8包含long_query_time2或long_query_time1，也表示需要优化。

例如，如果my.cnf5的结果显示my.ini5是my.ini6，[mysqld]2为空，[mysqld]3也为空，表示没有使用索引，需要考虑添加索引。

如何优化索引？

索引是提高查询性能的关键。但是，索引并不是越多越好，过多的索引会增加写操作的负担，并且占用额外的存储空间。

优化索引需要考虑以下几个方面：

1. 选择合适的索引列：

通常应该在log_output5子句中经常使用的列上创建索引。例如，如果经常执行log_output6，应该在log_output7列上创建索引。

采风问卷

采风问卷是一款全新体验的调查问卷、表单、投票、评测的调研平台，新奇的交互形式，漂亮的作品，让客户眼前一亮，让创作者获得更多的回复。

20

查看详情

2. 使用复合索引：

如果经常使用多个列进行查询，可以考虑创建复合索引。例如，如果经常执行log_output8，可以创建一个包含log_output7和FILE0两列的复合索引。

创建复合索引的顺序也很重要，应该将选择性更高的列放在前面。选择性是指不同值的数量与总行数的比例。例如，log_output7列的选择性可能比FILE0列更高，因为log_output7列的不同值更多。

3. 避免在索引列上使用函数或表达式：

如果在索引列上使用函数或表达式，MySQL将无法使用索引。例如，FILE4将无法使用FILE5列上的索引。

4. 定期维护索引：

随着数据的增加和删除，索引可能会变得碎片化，影响查询性能。可以使用FILE6命令来优化表，重建索引。

5. 考虑前缀索引：

对于FILE7或FILE8类型的列，无法创建完整的索引，但可以创建前缀索引。例如，FILE9表示创建log_output7列的前10个字符的索引。

创建索引的语法如下：

CREATE INDEX idx_name ON user(name);  -- 创建单列索引 CREATE INDEX idx_name_age ON user(name, age);  -- 创建复合索引

如何优化SQL语句？

除了优化索引，优化SQL语句本身也很重要。以下是一些常见的SQL优化技巧：

1. *避免使用`SELECT `：**

只选择需要的列，减少数据传输量。

2. 使用TABLE1限制返回结果：

如果只需要少量数据，使用TABLE1可以减少扫描的行数。

3. 避免在log_output5子句中使用TABLE4：

TABLE4可能会导致全表扫描，可以使用TABLE6或TABLE7代替。

4. 尽量使用TABLE8代替子查询：

TABLE8通常比子查询更有效率。

5. *使用mysql.slow_log0代替`COUNT()`：**

如果只需要判断是否存在满足条件的记录，使用mysql.slow_log0更高效。

6. 优化mysql.slow_log2和mysql.slow_log3：

确保mysql.slow_log2和mysql.slow_log3使用的列上有索引。

例如，以下SQL语句可以优化：

-- 优化前 SELECT * FROM user WHERE name = 'xxx' OR age = 20;  -- 优化后 SELECT * FROM user WHERE name = 'xxx' UNION ALL SELECT * FROM user WHERE age = 20;

如何监控MySQL性能？

除了排查慢查询，定期监控MySQL性能也很重要。可以使用各种工具来监控MySQL的性能指标，例如：

• MySQL Enterprise Monitor： 官方提供的监控工具，功能强大，但需要付费。
• Percona Monitoring and Management (PMM)： 开源的监控工具，可以监控MySQL、MongoDB等数据库。
• Grafana + Prometheus： 开源的监控解决方案，可以自定义监控指标。

监控的指标包括：

• CPU使用率： 关注CPU是否过高。
• 内存使用率： 关注内存是否足够。
• 磁盘IO： 关注磁盘IO是否瓶颈。
• 连接数： 关注连接数是否达到上限。
• QPS/TPS： 关注查询和事务的吞吐量。
• 慢查询数： 关注慢查询的数量。

通过监控这些指标，可以及时发现潜在的性能问题，并采取相应的措施。

如何处理死锁？

死锁是数据库并发操作中常见的问题。当两个或多个事务相互等待对方释放资源时，就会发生死锁。

MySQL会自动检测死锁，并选择一个事务回滚，释放资源，让其他事务继续执行。但是，频繁的死锁会影响数据库的性能。

处理死锁的方法包括：

1. 避免长时间持有锁： 尽量缩短事务的执行时间，减少锁的持有时间。
2. 使用相同的加锁顺序： 确保所有事务都按照相同的顺序加锁，避免循环等待。
3. 设置合理的锁超时时间： 如果事务长时间无法获得锁，可以设置超时时间，自动回滚事务。
4. 使用乐观锁： 乐观锁是一种无锁并发控制机制，通过版本号或时间戳来判断数据是否被修改。

可以使用mysql.slow_log6命令查看死锁日志，分析死锁的原因。

如何选择合适的存储引擎？

MySQL支持多种存储引擎，例如InnoDB、MyISAM、Memory等。不同的存储引擎有不同的特点，适用于不同的场景。

• InnoDB： 支持事务、行级锁、外键，适用于ACID要求高的应用。
• MyISAM： 不支持事务、表级锁，但查询性能较好，适用于读多写少的应用。
• Memory： 数据存储在内存中，速度快，但数据易丢失，适用于临时表或缓存。

选择合适的存储引擎需要根据你的应用场景进行权衡。通常情况下，建议使用InnoDB，因为它提供了更好的数据一致性和可靠性。

总而言之，MySQL查询慢是一个复杂的问题，需要从多个方面入手进行排查和优化。希望以上信息能帮助你解决MySQL查询慢的问题。