1. 当索引不再"保鲜"会发生什么？

上周三，我收到某电商平台的紧急求助：他们的订单表在清理了200万历史数据后，商品搜索响应时间从50ms暴增到2秒。通过EXPLAIN查看执行计划，发现原本应该走索引的查询变成了全表扫描。这就像超市里明明有货架标签，收银员却要逐个货架找商品——问题就出在那些"过期"的索引上。

2. 索引为何会"失忆"？

2.1 存储引擎的"记忆方式"

以MyISAM为例，执行以下批量删除：

DELETE FROM orders WHERE create_time < '2023-01-01';

此时.MYD数据文件会立即缩小，但.MYI索引文件却像被橡皮擦随意涂抹——留下大量空洞。InnoDB虽然支持事务回滚，但它的MVCC机制会让已删除记录在purge线程清理前继续占用索引空间。

2.2 查看索引健康度的体检报告

-- 查看索引碎片率
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROUND(DATA_FREE / (1024 * 1024), 2) AS '碎片空间(MB)',
    ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) * 100, 2) AS '碎片率%'
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_SCHEMA = 'your_db'
    AND DATA_FREE > 0;

当碎片率超过30%时，查询优化器可能认为扫描全表比使用索引更高效。

3. 给索引做"大扫除"的三种姿势

3.1 官方推荐套餐：OPTIMIZE TABLE

OPTIMIZE TABLE orders;

这相当于给数据库做了一次深度SPA：

  1. 创建临时表复制数据
  2. 重建所有索引
  3. 统计信息更新
  4. 原表替换

但要注意MyISAM会锁表，而InnoDB在5.6+版本支持ONLINE DDL。

3.2 精准打击方案：ALTER TABLE

ALTER TABLE orders ENGINE=InnoDB; -- 重建表
ALTER TABLE orders DROP INDEX idx_name, ADD INDEX idx_name (name); -- 重建单个索引

适合业务高峰期的折中方案，比如只重建最关键的索引。

3.3 自动化维护脚本

创建维护存储过程：

DELIMITER $$
CREATE PROCEDURE auto_rebuild_index()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tname VARCHAR(64);
    DECLARE cur CURSOR FOR 
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'your_db' 
          AND ENGINE = 'InnoDB';
        
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tname;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('ALTER TABLE ', tname, ' ENGINE=InnoDB');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END$$
DELIMITER ;

通过事件调度器每月执行一次。

4. C#开发者的运维工具箱

使用MySqlConnector类库实现智能维护：

using MySql.Data.MySqlClient;

public class IndexMaintainer
{
    // 连接配置
    private const string connStr = "server=localhost;user=root;database=order_db;port=3306;password=123456";
    
    // 异步重建索引
    public async Task RebuildIndexAsync(string tableName)
    {
        using var conn = new MySqlConnection(connStr);
        await conn.OpenAsync();
        
        // 获取索引列表
        var indexCmd = new MySqlCommand(
            $"SHOW INDEX FROM {tableName}", conn);
        using var reader = await indexCmd.ExecuteReaderAsync();
        
        var indexes = new List<string>();
        while (await reader.ReadAsync())
        {
            string indexName = reader.GetString(2);
            if (!indexes.Contains(indexName))
                indexes.Add(indexName);
        }
        await reader.CloseAsync();
        
        // 逐个重建
        foreach (var index in indexes)
        {
            var rebuildCmd = new MySqlCommand(
                $"ALTER TABLE {tableName} DROP INDEX `{index}`, ADD INDEX `{index}` (...)", conn);
            await rebuildCmd.ExecuteNonQueryAsync();
            Console.WriteLine($"已重建索引 {index}");
        }
    }
}

注意：实际使用需要根据表结构动态生成列定义。

5. 不同场景下的选择恐惧症解药

5.1 电商大促期间

  • 选择：ALTER TABLE ... ALGORITHM=INPLACE
  • 优势：在线操作，不阻塞写入
  • 代价：执行时间可能较长

5.2 金融系统月末结算

  • 选择：pt-online-schema-change工具
  • 优势：完全不停机
  • 代价：需要额外磁盘空间

5.3 物联网历史数据归档

  • 选择：分区表+TRUNCATE PARTITION
  • 优势：毫秒级删除
  • 副作用：需要提前规划分区策略

6. 这些坑你千万别踩

  1. 锁表惊魂：某物流系统在双11前夜执行OPTIMIZE TABLE，导致订单积压3小时

    • 对策：使用SHOW PROCESSLIST监控阻塞情况

  2. 磁盘空间刺客：重建500GB的表需要额外500GB空间

    • 检查：df -h确认磁盘剩余空间

  3. 主从延迟黑洞：在主库执行ALTER导致从库延迟10小时

    • 方案：先在从库测试，使用MASTER_DELAY参数控制

  4. 统计信息陷阱：某次重建后查询更慢，发现是基数估算错误

    • 修复：ANALYZE TABLE重新收集统计信息

7. 性能参数调优秘籍

在my.cnf中增加这些配置：

[mysqld]
innodb_online_alter_log_max_size=256M
innodb_sort_buffer_size=64M
tmp_table_size=64M

定期检查：

mysql> SELECT * FROM sys.schema_unused_indexes;
mysql> SELECT * FROM information_schema.INNODB_TRX WHERE TIME > 60;

8. 总结：让索引永葆青春的秘诀

就像汽车需要定期保养，数据库索引也需要周期性维护。通过本文的三种重建方案，配合智能化的监控策略，可以有效解决数据删除后的索引失效问题。记住这些关键点：

  1. 碎片率超过30%必须处理
  2. 业务高峰期优先使用在线DDL
  3. 结合分区表设计减少维护压力
  4. 每次维护后验证执行计划

最后送大家一个自查清单： ✅ 每周检查碎片率 ✅ 大删除操作后立即重建索引 ✅ 使用Percona Toolkit监控索引健康度 ✅ 重要表配置自动维护任务

当你的索引始终"保鲜"，查询性能就会像刚开封的薯片一样保持酥脆！