慢查询日志配置与采集
MySQL慢查询日志是定位性能瓶颈的首要工具。通过记录执行时间超过阈值的SQL语句，配合EXPLAIN执行计划分析，可系统性地优化查询性能。慢查询日志的配置分动态参数和持久化配置两步：
-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 动态开启慢查询日志（运行时生效，重启失效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1; -- 超过1秒的查询记录
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未使用索引的查询
SET GLOBAL log_slow_admin_statements = ON;
SET GLOBAL min_examined_row_limit = 100; -- 扫描行数小于100不记录
-- 持久化配置（写入my.cnf）
-- [mysqld]
-- slow_query_log = ON
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 1
-- log_queries_not_using_indexes = ON
-- log_slow_admin_statements = ON
-- min_examined_row_limit = 100
生产环境中long_query_time建议设置为1-2秒，开发环境设为0.1秒以捕获更多慢查询。log_queries_not_using_indexes开启后可能产生大量日志，需配合min_examined_row_limit过滤小表全表扫描。
使用mysqldumpslow或pt-query-digest分析慢查询日志：
# mysqldumpslow按执行次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# 按总执行时间排序（找出最耗时的SQL）
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# pt-query-digest（Percona Toolkit，推荐）
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
# pt-query-digest按特定数据库过滤
pt-query-digest --filter '($event->{db} || "") =~ /^production/' /var/log/mysql/slow.log
pt-query-digest的输出报告中，RANK列按总时间排序，可快速定位需优先优化的SQL。注意同一SQL模板的不同参数会被聚合统计，通过指纹（fingerprint）去重。
EXPLAIN执行计划深度解读
EXPLAIN是MySQL查询优化器的执行计划输出，包含12列关键信息。以下通过实际案例解析各列含义：
EXPLAIN SELECT
o.order_id, o.total_amount, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'shipped' AND o.created_at >= '2026-01-01'
ORDER BY o.total_amount DESC
LIMIT 100;
+----+-------------+-------+------------+--------+---------------------+---------------------+---------+--------------------------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------+---------------------+---------+--------------------------+------+----------+----------------------------------+
| 1 | SIMPLE | o | NULL | range | idx_status,idx_date | idx_date | 5 | NULL | 8520 | 11.11 | Using index condition; Using filesort |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | prod.o.customer_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | oi | NULL | ref | idx_order_id | idx_order_id | 4 | prod.o.order_id | 3 | 100.00 | NULL |
| 1 | SIMPLE | p | NULL | eq_ref | PRIMARY | PRIMARY | 4 | prod.oi.product_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------------+---------------------+---------+--------------------------+------+----------+----------------------------------+
各关键字段解读：
- type：访问类型，从优到差依次为system > const > eq_ref > ref > range > index > ALL。range表示范围扫描，eq_ref表示主键或唯一索引等值关联，ALL表示全表扫描需重点优化。
- key：实际使用的索引。
possible_keys列列出可用的索引，key列为优化器最终选择。如果possible_keys有值但key为NULL，说明优化器认为全表扫描更快，需检查统计信息。 - rows：预估扫描行数。多表关联时，总扫描行数为各表rows的乘积，该值越小越好。
- filtered：过滤后剩余行数百分比。低filtered值意味着大量扫描行被WHERE条件过滤掉，索引可能未覆盖条件列。
- Extra：附加信息。
Using index condition表示ICP（Index Condition Pushdown）生效；Using filesort表示需要额外排序；Using temporary表示需要临时表；Using join buffer表示关联未走索引。
上述案例中orders表出现Using filesort，说明ORDER BY total_amount DESC无法利用索引排序。需创建覆盖排序字段的联合索引。
索引优化策略与联合索引设计
联合索引遵循最左前缀匹配原则，索引列顺序直接影响查询效率。设计联合索引时，遵循等值条件在前、范围条件在后、排序列衔接的原则：
-- 原始查询条件：WHERE status = 'shipped' AND created_at >= '2026-01-01' ORDER BY total_amount DESC
-- 创建联合索引（status, created_at, total_amount）
CREATE INDEX idx_status_date_amount ON orders(status, created_at, total_amount);
-- 索引设计分析：
-- 1. status = 'shipped' -> 等值匹配，作为索引第一列
-- 2. created_at >= '...' -> 范围扫描，作为索引第二列
-- 3. total_amount DESC -> 排序列，作为索引第三列（利用索引有序性消除filesort）
-- 验证优化效果
EXPLAIN SELECT
o.order_id, o.total_amount, c.customer_name, p.product_name
FROM orders o FORCE INDEX (idx_status_date_amount)
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'shipped' AND o.created_at >= '2026-01-01'
ORDER BY o.total_amount DESC
LIMIT 100;
-- 优化后执行计划
+----+-------------+-------+------------+--------+--------------------------+--------------------------+---------+--------------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------------+--------------------------+---------+--------------------------+------+----------+-----------------------+
| 1 | SIMPLE | o | NULL | range | idx_status_date_amount | idx_status_date_amount | 10 | NULL | 8520 | 100.00 | Using index condition |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | prod.o.customer_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | oi | NULL | ref | idx_order_id | idx_order_id | 4 | prod.o.order_id | 3 | 100.00 | NULL |
| 1 | SIMPLE | p | NULL | eq_ref | PRIMARY | PRIMARY | 4 | prod.oi.product_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+--------------------------+--------------------------+---------+--------------------------+------+----------+-----------------------+
优化后Using filesort消失，查询利用idx_status_date_amount的索引有序性直接返回排好序的结果。filtered从11.11%提升至100%，消除了无效行扫描。
覆盖索引与回表优化
当查询所需的列全部包含在索引中时，InnoDB可直接从索引返回数据，无需回表查询聚簇索引。覆盖索引对高并发查询的性能提升可达3-5倍：
-- 场景：订单列表页需展示order_id, status, total_amount，按创建时间排序
SELECT order_id, status, total_amount FROM orders
WHERE customer_id = 12345 AND status IN ('pending', 'shipped')
ORDER BY created_at DESC LIMIT 20;
-- 方案1：单列索引（需要回表）
CREATE INDEX idx_customer_id ON orders(customer_id);
-- 执行计划：type=ref, rows=1500, Extra=Using filesort, Using index condition
-- 方案2：覆盖索引（无需回表）
CREATE INDEX idx_cust_status_date ON orders(customer_id, status, created_at, order_id, total_amount);
-- 执行计划：type=ref, rows=50, Extra=Using where; Using index
方案2的Using index表示覆盖索引生效，全部数据从二级索引返回。但索引列过多会增加索引体积和写入开销，需在读写性能之间权衡。对于频繁查询但更新较少的列，覆盖索引性价比最高。
判断是否需要覆盖索引的经验法则：如果查询返回的列数不超过5个，且这些列的数据类型较小（整型、短字符串），适合构建覆盖索引。返回大量列或包含TEXT/BLOB的查询不适合覆盖索引。
统计信息更新与优化器提示
MySQL优化器基于统计信息选择执行计划，统计信息过期会导致索引选择错误。定期更新统计信息是维护查询性能的基础：
-- 查看表的统计信息
SHOW TABLE STATUS LIKE 'orders'\G
-- 更新统计信息（采样百分比控制精度）
ANALYZE TABLE orders;
-- 或指定采样行数
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, customer_id WITH 100 BUCKETS;
-- 查看直方图信息
SELECT * FROM information_schema.column_statistics
WHERE table_name = 'orders';
-- 使用优化器提示强制索引
SELECT /*+ FORCE INDEX(o idx_status_date_amount) */
o.order_id, o.total_amount
FROM orders o
WHERE o.status = 'shipped' AND o.created_at >= '2026-01-01';
-- 使用优化器提示控制JOIN顺序
SELECT /*+ JOIN_ORDER(o, oi, p, c) */
o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'shipped';
-- 使用SET_VAR调整单条查询的优化器行为
SELECT /*+ SET_VAR(optimizer_switch='index_merge=on') */ *
FROM orders
WHERE customer_id = 123 OR status = 'shipped';
当优化器选择错误索引时，优先使用FORCE INDEX或USE INDEX提示而非删除索引——错误的索引可能对其他查询有用。对于复杂查询，结合EXPLAIN ANALYZE（MySQL 8.0.18+）查看实际执行统计，比预估值更准确：
EXPLAIN ANALYZE SELECT
o.order_id, o.total_amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'shipped'
ORDER BY o.total_amount DESC
LIMIT 100;
EXPLAIN ANALYZE输出实际执行的行数、循环次数和耗时，可验证优化器预估值与实际值的偏差。偏差大于10%时建议执行ANALYZE TABLE更新统计信息。
建立定期巡检机制：每周使用pt-query-digest分析慢查询日志，对TOP 10慢查询执行EXPLAIN分析，更新统计信息，检查索引使用率。长期不使用的索引通过sys.schema_unused_indexes视图识别后清理，减少写入开销。
原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-man-cha-xun-ri-zhi-fen-xi-yu-zhi-xing-ji-hua-diao-you/
