慢查询的诊断起点
MySQL慢查询是数据库性能问题的核心表现。一条慢查询可能占用大量IO和CPU资源，影响同一实例上所有业务的响应时间。优化慢查询的第一步不是加索引，而是准确定位问题SQL和理解其执行计划。
确认慢查询日志已开启：
-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢查询日志，阈值设为1秒
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;生产环境中long_query_time建议设为0.1到1秒之间，过低会产生大量日志影响IO，过高则漏掉频繁执行的”不太慢”但有累积影响的查询。
EXPLAIN执行计划深度解读
EXPLAIN是理解MySQL如何执行查询的核心工具。关键列及其含义：
EXPLAIN SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending' AND o.created_at > '2026-07-01';type列表示访问类型，从优到差排序：system > const > eq_ref > ref > range > index > ALL。ALL代表全表扫描，必须消除。
Extra列中的关键信息：Using index表示覆盖索引，无需回表；Using filesort表示额外排序，消耗大量内存和CPU；Using temporary表示使用了临时表，常见于GROUP BY无索引场景；Using where表示存储引擎返回的数据需要在Server层过滤。
复合索引的设计原则
索引设计遵循最左前缀原则。复合索引(a, b, c)可以支持a、(a,b)、(a,b,c)三种查询条件组合，但不支持(b,c)或(b)或(c)的查询。
-- 订单表查询模式：按状态+创建时间筛选，按金额排序
SELECT * FROM orders
WHERE status = 'pending'
AND created_at BETWEEN '2026-07-01' AND '2026-08-01'
ORDER BY total DESC
LIMIT 20;
-- 对应的最优复合索引
CREATE INDEX idx_status_created_total
ON orders(status, created_at, total);索引列顺序的三条规则：等值条件列在前，范围条件列在后，排序列紧跟范围列。这样索引既可用于WHERE过滤，也可用于ORDER BY避免filesort。
覆盖索引消除回表开销
当查询所需的所有列都包含在索引中时，InnoDB直接从索引树返回数据，无需回表读取主键索引。这是索引优化的最高级别：
-- 查询只需要id、status、total三列
SELECT id, status, total FROM orders
WHERE status = 'pending' AND created_at > '2026-07-01';
-- 覆盖索引：包含所有查询列
CREATE INDEX idx_cover_pending
ON orders(status, created_at, id, total);
-- EXPLAIN中Extra显示 "Using index" 表示覆盖索引生效覆盖索引的代价是索引体积增大，写入性能下降。对于频繁查询且列数有限的场景，覆盖索引收益远大于写入开销。
索引失效的常见原因
某些SQL写法会导致索引无法被使用，必须避免：
-- 1. 对索引列使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-01';
-- 修复：改写为范围查询
SELECT * FROM orders
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02';
-- 2. 隐式类型转换
-- phone字段是varchar，传入整数导致全表扫描
SELECT * FROM users WHERE phone = 13800138000;
-- 修复：传入字符串
SELECT * FROM users WHERE phone = '13800138000';
-- 3. OR条件中有一个无索引列
SELECT * FROM orders WHERE status = 'pending' OR remark = 'urgent';
-- 修复：使用UNION ALL替代
SELECT * FROM orders WHERE status = 'pending'
UNION ALL
SELECT * FROM orders WHERE remark = 'urgent' AND status != 'pending';
-- 4. LIKE前缀通配符
SELECT * FROM users WHERE name LIKE '%张';
-- 修复：使用全文索引或调整业务逻辑
ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张' IN BOOLEAN MODE);批量操作优化策略
大数据量的INSERT和UPDATE操作需要分批执行，避免长事务锁定和Binlog膨胀：
# Python分批更新示例
import pymysql
import time
conn = pymysql.connect(host='db-host', user='admin', password='pwd', db='orders_db')
cursor = conn.cursor()
batch_size = 1000
while True:
affected = cursor.execute(
"UPDATE orders SET status = 'new_value' "
"WHERE status = 'old_value' LIMIT %s", (batch_size,)
)
conn.commit()
if affected == 0:
break
time.sleep(0.1) # 批次间暂停释放锁资源
cursor.close()
conn.close()每批操作后检查锁等待和复制延迟，确保不影响在线业务。
慢查询优化是一个迭代过程：定位→分析→优化→验证。每次优化后用EXPLAIN确认执行计划改善，用查询耗时对比验证效果。盲目加索引比不优化更危险——多余的索引浪费存储、拖慢写入、增加优化器选择成本。
原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-man-cha-xun-you-hua-shi-zhan-cong-explain-fen-xi-dao/
