MySQL慢查询优化实战：索引失效与覆盖索引应用案例

结果或目标

在生产环境中遇到数据库响应缓慢，触发负载报警，表现为大量SQL执行时间超过10秒，甚至出现连接阻塞。目标是定位执行计划中的低效环节，通过调整SQL写法、增加/修改索引以及优化表结构，将核心查询耗时降至秒级以下。

准备条件

1. mysql客户端权限，用于查看执行计划和执行测试语句。
2. 目标库中存在存在性能问题的业务表（如交易流水表、会话缓存表）。
3. 了解表的基本数据结构，特别是主键类型和大字段（如text、长varchar）的使用情况。

真实操作顺序

第1步：分析第一类慢查询的执行计划

目的： 识别基于主键排序+过滤条件引发的全表扫描问题。

入口： SQL终端。

动作： 观察如下SQL的执行表现与执行计划：

SELECT * FROM `sitevipdb`.`game_shares_buy_list` 
WHERE price >= '2.00' 
ORDER BY tran_id DESC LIMIT 10;

原表结构包含自增主键tran_id和普通索引ind_username。执行EXPLAIN后发现，MySQL选择了主键索引进行降序扫描（Using where），未使用其他索引。

问题分析： 虽然利用了主键有序性避免了文件排序，但需要在海量不满足price >= 2.00条件的记录中逐条扫描，直到找到10条符合条件的数据，造成严重的IO浪费。

第2步：评估索引添加效果与强制索引

目的： 尝试通过price字段索引进而优化，处理数据分布不均的问题。

入口： SQL终端。

动作： 在price列上创建索引：

ALTER TABLE game_shares_buy_list ADD INDEX ind_game_shares_buy_list_price(price);

再次执行原始SQL时，观察到即使添加了索引，Optimizer依然倾向于选择主键扫描。

关键参数/观察状态： 此时需手动干预，使用FORCE INDEX指示优化器使用新建索引：

SELECT * FROM ... FORCE INDEX(ind_game_shares_buy_list_price) WHERE ... LIMIT 10;

结果证实强制走索引后耗时从7秒左右降至1秒，说明索引本身是有效的，只是优化器估算成本有误。同时，展示了添加索引后的执行计划变化对比。

第3步：改写SQL实现极速返回

目的： 彻底消除“回表”带来的额外IO开销。

入口： SQL终端。

动作： 将查询拆分为先查索引值再关联主表的方式：

select * from game_shares_buy_list t1,
( SELECT tran_id FROM sitevipdb.game_shares_buy_list 
  WHERE price >= '2.0' ORDER BY tran_id DESC LIMIT 10) t2
where t1.tran_id = t2.tran_id;

完成状态： 该写法仅在索引上筛选出10个ID，然后仅对这10条数据进行主键回表查询。执行时间瞬间降至0.00秒级别。

此外，进一步分析了这种改写对扫描行数的巨大削减。对于类似场景，也可考虑联合索引(tran_id, price)以消除排序。

第4步：重构第二类会话表结构

目的： 解决由长字符串主键和大文本字段引起的插入死锁、锁竞争与读取延迟。

入口： 数据库DDL变更（建议在维护窗口操作）。

风险提醒： 涉及表结构修改，变更前务必备份表数据及依赖该表的接口定义。

原始问题： game_session表以session_id（30位以上长串）为主键，且存储session_data text字段。导致INSERT频繁请求主键锁等待，SELECT时需要回表读取大文本。

动作： 实施三项改造：

1. 替换主键： 引入自增整数id作为新主键，减小聚簇索引体积。
2. 缩减数据类型： 若业务允许，将session_data从TEXT改为固定长度或最大长度的VARCHAR(200)。
3. 构建覆盖索引： 新建联合索引ind_session_id(session_id, session_data, session_expires)。

完成判断： 新的建表语句如下：

CREATE TABLE `game_session` (
  id int auto_increment,
  session_id varchar(30) NOT NULL DEFAULT '',
  session_expires int unsigned NOT NULL DEFAULT '0',
  client_ip varchar(16) DEFAULT NULL,
  session_data varchar(200),
  PRIMARY KEY (id),
  KEY ind_session_id(session_id, session_data, session_expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

通过上述改造，查询可通过覆盖索引直接返回所需字段，无需回表；插入操作使用紧凑的自增主键，有效缓解了行锁冲突和IO压力。

简要总结

这两个案例揭示了MySQL优化的两个经典方向：一是面对复杂的WHERE+ORDER BY过滤时，利用子查询隔离“获取ID集合”和“填充详细信息”，避免不必要的回表扫描；二是针对高频读写且包含大字段或宽主键的热点表，通过引入自增主键、精简数据类型并利用覆盖索引来降低系统底层的IO消耗与锁竞争。以上就是MySQL id关联和索引使用的实际优化案例的详细过程，更多关于MySQL高性能调优的资料请关注本站其它相关文章！