MySQL性能优化实战：EXPLAIN用法详解与慢查询分析

利用EXPLAIN分析执行计划

要查看MySQL如何执行一条查询语句，可以使用 EXPLAIN 命令。它将返回关于SELECT语句执行的详细信息，帮助我们判断索引是否生效、全表扫描还是走索引以及大致的成本。

EXPLAIN SELECT * FROM test1 WHERE id = 1;

执行后会返回多列信息，关键字段说明如下：

• id: 查询序列号。ID越大优先级越高，相同ID则从上到下执行。
• select_type: 查询类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
• table: 显示的这行数据是关于哪个表的。
• type: 访问类型，反映查询的效率好坏。从好到坏依次为：system > const > eq_ref > ref > range > index > ALL。其中 const 表示通过索引一次就找到了（如主键或唯一索引），ALL 表示全表扫描，性能最差。一般要求至少达到 range 级别，最好能达到 ref 或更好。
• possible_keys: 显示可能应用的索引。如果为空，说明没有相关索引，此时可通过检查WHERE子句或建立索引来提升性能。
• key: MySQL实际使用的索引。如果是NULL，表示未使用索引。
• key_len: 使用的索引长度。在不损失精度的情况下越短越好，也可用于判断联合索引中实际使用了哪些列。
• rows: 估计需要读取的记录行数。在InnoDB中此值为估算值，仅供参考。
• Extra: 额外信息的描述。
  • Using index: 表示覆盖索引，只使用索引树中的数据即可检索，无需回表，效率高。
  • Using where: 使用WHERE限制条件过滤数据。
  • Using filesort 或 Using temporary: 性能警告信号。表明MySQL需要在外部进行排序或创建临时表，通常是因为索引无法满足ORDER BY或GROUP BY的要求。

理解最左前缀原则与组合索引

当表中存在多个字段构成的复合索引时（例如由 key1, key2, key3 组成的联合索引），MySQL遵循最左前缀原则。这意味着查询条件必须从左到右匹配索引中的列，才能有效利用索引。

假设有一个联合索引 (key1, key2, key3)：

• WHERE key1 = 1: 可以使用索引。
• WHERE key1 = 1 AND key2 = 2: 可以使用索引。
• WHERE key1 = 1 AND key3 = 3 AND key2 = 2: 尽管顺序不同，但优化器通常会重写逻辑，依然可以利用索引。
• WHERE key2 = 1 (跳过key1): 无法使用该联合索引，因为违反了最左前缀规则。

在实际使用中，即使WHERE条件命中了索引，后续的ORDER BY可能会引发文件排序。例如 FROM test WHERE key1=1 ORDER BY key3，虽然WHERE用到了 key1 部分的索引，但由于 key3 不是紧接在 key1 之后的字段，ORDER BY阶段可能需要额外的排序操作。这时需权衡是先过滤再排序划算，还是构建更合适的联合索引。

开启与分析慢查询日志

对于复杂的线上环境，肉眼观察所有SQL是不现实的。可以通过配置慢查询日志，自动记录执行时间超过阈值的SQL，然后使用专业工具（如mysqlsla、mysqlreport）进行分析。

在 my.ini 或 my.cnf 中添加以下配置：

long_query_time = 1
log_slow_queries = D:\mysql5\logs\mysqlslow.log

上述配置表示将执行时间超过1秒的SQL记录到指定日志文件中。之后可使用工具分析这些日志，查看高频率慢SQL的类型分布（Select/Update/Delete占比）及具体语句，从而针对性地进行索引优化或代码重构。

存储引擎特性对比：锁机制与事务提交

MySQL常用的两种存储引擎MyISAM和InnoDB在锁行为和事务处理上有显著差异，选择合适的引擎对性能至关重要。

锁机制差异

• MyISAM: 采用表锁。当对一个表进行写操作（UPDATE/INSERT）时，会锁定整张表，直到写操作完成其他读操作（SELECT）才能进行。这在高并发读写场景下容易形成瓶颈。
• InnoDB: 采用行锁（配合间隙锁）。仅锁定受影响的行，极大地提高了并发性能，支持多用户同时读写不同的数据行。

InnoDB事务日志刷新策略

InnoDB通过参数 innodb_flush_log_at_trx_commit 控制事务提交时的日志写入行为，该设置直接影响数据安全性和性能：

1. 值为0: 每秒将事务日志刷写到磁盘，而不是每次事务提交都写。性能最高，但如果MySQL崩溃，可能丢失最近一秒的数据。
2. 值为1: 每次事务提交时，都将事务日志写入磁盘并强制刷新（fsync）。这是最安全的方式，保证ACID中的持久性，但性能开销最大。
3. 值为2: 每次事务提交时将日志写入操作系统缓存（OS Buffer），但不立即刷入磁盘，而是由操作系统每秒刷写一次。安全性介于0和1之间，性能较好。

在需要严格数据一致性的业务系统中，建议设置为1；对性能极度敏感且允许极小概率数据丢失的场景可考虑2或0。

以上就是MySQL性能分析及EXPLAIN用法的详细内容，更多关于MySQL优化与慢查询调优的资料请关注本站其它相关文章！