1. 索引重建的预期与现实的落差

作为数据库优化的常规操作，索引重建（REBUILD INDEX）常被寄予厚望。DBA们期待它能像魔法般解决查询卡顿问题，但有时执行完ALTER TABLE ... ENGINE=INNODB后，EXPLAIN计划依然显示全表扫描。上周某电商系统就遇到这种情况：重建商品表索引后，促销查询响应时间反而从800ms飙升至1.2秒。

2. 基础排查三板斧

2.1 索引碎片率验证

-- 查看索引碎片率（适用于InnoDB）
SHOW TABLE STATUS LIKE 'orders'\G

-- 重建索引标准操作
ALTER TABLE orders ENGINE=INNODB;

注意观察Data_free字段值，当该值超过数据大小的10%时才真正需要重建。某物流系统曾误对碎片率仅3%的运单表进行重建，导致30万条记录的表锁死7分钟。

2.2 执行计划对比分析

-- 重建前执行计划
EXPLAIN SELECT * FROM products WHERE category_id = 1024;

-- 重建后执行计划对比
EXPLAIN SELECT * FROM products FORCE INDEX(idx_category) WHERE category_id = 1024;

某社交平台案例显示，重建后优化器错误选择新建的覆盖索引，反而增加回表次数。通过FORCE INDEX强制使用原索引后，查询速度恢复。

2.3 系统参数校验

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

mysql> SHOW VARIABLES LIKE 'innodb_stats_persistent';

某金融系统将innodb_buffer_pool_size设为2GB，而数据量已达50GB，导致索引重建后无法有效加载到内存。

3. 高级问题定位指南

3.1 隐式类型转换陷阱

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

-- 问题查询（phone字段是varchar类型）
SELECT * FROM users WHERE phone = 13800138000; -- 数字类型触发隐式转换

某O2O平台就因此导致索引失效，改为WHERE phone = '13800138000'后，索引命中率从15%提升至99%。

3.2 统计信息失真

-- 强制更新统计信息
ANALYZE TABLE transactions;

-- 设置采样页数（默认20）
SET GLOBAL innodb_stats_persistent_sample_pages = 100;

某物联网设备日志表包含周期性数据波动，默认采样导致优化器误判数据分布，调整后索引选择准确率提升40%。

3.3 索引合并的副作用

-- 查看optimizer_switch设置
SHOW VARIABLES LIKE 'optimizer_switch';

-- 禁用索引合并优化
SET SESSION optimizer_switch='index_merge=off';

某ERP系统开启index_merge后，多个单列索引的合并操作反而增加CPU消耗，关闭后整体QPS提升22%。

4. C#应用层排查示例

当怀疑是应用层导致索引失效时，可使用Dapper配合分析：

// 使用Dapper执行SQL分析（需安装Dapper和MySqlConnector）
using(var conn = new MySqlConnection(connStr)){
    var sql = @"EXPLAIN 
                SELECT * FROM orders 
                WHERE user_id = @userId AND status IN @statusList";
    
    // 参数化查询检测类型匹配
    var parameters = new {
        userId = 1024, // 确保与表字段类型一致
        statusList = new[] {1,3,5} 
    };
    
    var plan = conn.Query(sql, parameters).First();
    Console.WriteLine($"索引使用情况：{plan.key}");
}

某电商系统曾因C#端将int型参数传给varchar字段，导致索引失效，修正后查询速度提升8倍。

5. 特殊场景应对策略

5.1 JSON字段索引失效

-- 创建函数索引
ALTER TABLE products 
ADD INDEX idx_specs ((CAST(specs->'$.weight' AS DECIMAL(10,2))));

某智能硬件平台对JSON字段的直接查询导致索引失效，通过函数索引优化后，筛选效率提升60%。

5.2 分区表索引陷阱

-- 查看分区索引状态
SELECT TABLE_NAME, PARTITION_NAME, INDEX_NAME 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'sensor_data';

某工业物联网系统的range分区表存在局部索引失效问题，重建全局索引后查询耗时从3秒降至200ms。

6. 性能监控闭环建设

推荐部署以下监控体系：

  • 慢查询日志分析（long_query_time=1秒）
  • 每秒索引使用统计（SHOW GLOBAL STATUS LIKE 'Handler_read%'）
  • InnoDB缓冲池命中率监控（需保持>98%）

某视频平台通过监控发现，索引重建后read_next值异常升高，定位到存在隐式的排序操作消耗资源。

7. 重建失败的替代方案

当索引重建无效时，可尝试：

-- 使用OPTIMIZE TABLE（注意锁表风险）
OPTIMIZE TABLE customer_log;

-- 在线DDL工具（gh-ost）
gh-ost \
--alter="DROP KEY idx_created_at, ADD KEY idx_created_at_new(created_at)" \
--database=prod \
--table=comments \
--execute

某社交平台使用gh-ost在线修改索引结构，实现2000万级表零停机优化。

8. 技术选型对比

方案 适用场景 耗时对比 锁表风险
ALTER TABLE <100GB表 中等 高
pt-online-schema-change 大表修改 较长 低
分区表重建 时间序列数据 短 中
数据归档+新建表 历史数据占比高 长 无

9. 最佳实践总结

  1. 重建前务必验证碎片率（Data_free/Data_length > 10%）
  2. 使用EXPLAIN FORMAT=JSON获取详细成本分析
  3. 结合SHOW PROFILE确认资源消耗点
  4. 定期使用Percona Toolkit进行索引优化建议
  5. 重要操作前使用FLUSH TABLES WITH READ LOCK创建还原点

10. 常见认知误区纠正

  • 误区1：所有索引都需要定期重建
    事实：只有B+Tree的叶子节点碎片率>30%才需要

  • 误区2：重建索引能解决所有性能问题
    事实：索引失效可能是由错误查询引起

  • 误区3：在线DDL不会影响业务
    事实：虽然不锁表，但可能引起实例IO过载

上周某银行系统在业务高峰期重建索引，导致磁盘IOPS飙升至9000，最终触发高可用切换。经过此次教训，他们建立了变更窗口审批制度，并要求所有DDL操作必须附带IOPS评估报告。

通过系统化的排查流程，我们能准确定位索引失效的深层原因。记住，索引优化不是简单的体力活，而是需要结合数据库原理、业务特征、数据分布的精细技术活。下次当你的索引重建没有达到预期时，不妨按照这个检查清单走一遍，或许会发现意想不到的问题根源。