一、当我们的数据库开始"喘粗气"

最近遇到个有意思的案例：某电商平台的商品搜索功能，在促销期间响应时间从200ms暴涨到8秒。DBA团队排查后发现，原本运行良好的索引突然"罢工"，导致全表扫描近百万条数据。这就像你家的指纹锁突然失灵，每次开门都要试遍所有钥匙，怎能不让人抓狂？

二、索引失效的七种典型场景

场景1：联合索引的"左右为难"

-- 创建联合索引
ALTER TABLE orders ADD INDEX idx_status_amount (order_status, total_amount);

-- 失效查询（跳过了status字段）
SELECT * FROM orders WHERE total_amount > 1000;

解析：就像查字典不按首字母直接翻页，当查询条件未包含联合索引的最左列时，索引就会失效。优化方案是调整查询条件或重建索引顺序。

场景2：类型转换的"隐形杀手"

-- 创建索引
ALTER TABLE users ADD INDEX idx_phone (phone);

-- phone字段是varchar类型
SELECT * FROM users WHERE phone = 13800138000; -- 数字查询导致失效

解析：字段类型不匹配就像用人民币支付美元账单，数据库必须做隐式转换才能比较。解决方法是在应用层确保类型一致，或使用显式转换函数。

场景3：函数操作的"魔法失效"

-- 创建日期索引
ALTER TABLE logs ADD INDEX idx_create_time (create_time);

-- 使用日期函数导致失效
SELECT * FROM logs WHERE DATE_FORMAT(create_time, '%Y-%m') = '2023-08';

解析：对索引列使用函数就像把书页撕下来重排，破坏了原有结构。可优化为范围查询：

SELECT * FROM logs 
WHERE create_time >= '2023-08-01' 
AND create_time < '2023-09-01';

场景4：OR条件的"双重陷阱"

-- 单个字段索引
ALTER TABLE products ADD INDEX idx_price (price);

-- OR查询导致全表扫描
SELECT * FROM products 
WHERE price > 1000 OR stock < 10;

解析：OR条件就像同时要求厨师做川菜和粤菜，必须两边都准备好。解决方案是拆分为UNION查询：

SELECT * FROM products WHERE price > 1000
UNION
SELECT * FROM products WHERE stock < 10;

场景5：模糊查询的"精确谜题"

-- 普通索引
ALTER TABLE articles ADD INDEX idx_title (title);

-- 前导通配符查询
SELECT * FROM articles WHERE title LIKE '%数据库%';

解析：前导通配符就像蒙着眼睛找人，必须逐个排查。建议使用全文索引：

ALTER TABLE articles ADD FULLTEXT idx_fulltext_title (title);
SELECT * FROM articles 
WHERE MATCH(title) AGAINST('+数据库' IN BOOLEAN MODE);

场景6：范围查询的"多米诺效应"

-- 联合索引
ALTER TABLE sales ADD INDEX idx_date_region (sale_date, region);

-- 范围查询阻断后续索引
SELECT * FROM sales 
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
AND region = '华东';

解析：范围查询就像推倒第一张骨牌，后续索引列无法继续使用。优化方案是调整索引顺序或使用覆盖索引。

场景7：统计误差的"信任危机"

-- 检查索引健康状况
SHOW INDEX FROM orders;

-- 当Cardinality值接近表记录数时
ALTER TABLE orders ANALYZE TABLE;

解析：统计信息过期就像用旧地图找新路，优化器可能做出错误判断。定期执行ANALYZE TABLE更新统计信息。

三、索引重建的三种武器

方法1：ALTER TABLE重建

-- 最彻底的索引重建方式
ALTER TABLE orders DROP INDEX idx_old;
ALTER TABLE orders ADD INDEX idx_new (new_columns);

优点：完整重建索引结构
缺点：锁表时间长（实测5GB表需要3分钟）

方法2：OPTIMIZE TABLE优化

-- 同时优化表和索引
OPTIMIZE TABLE orders;

优点：自动处理碎片和统计信息
缺点：需要MyISAM或InnoDB存储引擎支持

方法3：在线重建工具

-- 使用pt-online-schema-change
pt-online-schema-change --alter "DROP KEY idx_old, ADD KEY idx_new(col)" D=db,t=orders

优点：几乎不影响线上业务
缺点：需要安装Percona工具包

四、慢查询分析的黄金搭档：EXPLAIN

实战解读执行计划

EXPLAIN SELECT * FROM orders 
WHERE order_date > '2023-01-01' 
AND customer_id = 12345;

关键字段解读：

  • type：ALL表示全表扫描，range表示范围扫描
  • key：实际使用的索引名称
  • rows：预估扫描行数
  • Extra：Using where表示需要回表查询

五、技术方案的权衡之道

索引优化的双面性

优势：

  • 查询速度提升10-100倍
  • 减少服务器资源消耗
  • 提升并发处理能力

局限：

  • 增加写操作耗时（测试显示写入速度降低15%）
  • 需要持续维护成本
  • 可能引入新的死锁问题

六、操作时的保命守则

  1. 备份先行：重大变更前务必执行mysqldump
  2. 灰度发布：先在从库执行变更，观察效果
  3. 监控三件套：QPS、慢查询日志、锁等待情况
  4. 避免高峰期：选择业务低谷时段操作（推荐凌晨2-4点）
  5. 版本验证：不同MySQL版本对索引策略有差异（实测5.7与8.0存在20%性能差异）

七、真实案例的启示

某社交平台的消息表在优化前：

  • 数据量：1.2亿条
  • 慢查询占比：38%
  • 平均响应时间：2.3秒

通过索引重建和优化后：

  • 有效索引使用率提升至92%
  • 慢查询占比降至3%
  • 平均响应时间缩短到180ms
  • 服务器CPU负载从70%降至35%

八、总结与展望

索引优化就像给数据库做定期体检，需要持续监测和及时干预。随着MySQL 8.0新增的不可见索引、降序索引等特性，我们有了更多优化手段。但核心原则始终不变：理解业务场景，掌握数据特征，用合适的工具解决具体问题。