作为程序员，你一定听过这样的矛盾：DBA总想删索引提升写性能，开发总想加索引优化查询速度。一张表到底该建多少个索引？这个让无数团队头疼的问题，今天我们用「空间换时间」的底层逻辑来破解。

作为程序员，你一定听过这样的矛盾：DBA总想删索引提升写性能，开发总想加索引优化查询速度。一张表到底该建多少个索引？这个让无数团队头疼的问题，今天我们用「空间换时间」的底层逻辑来破解。

1.索引的双面性：天使与魔鬼的共生体

索引的价值证明

• 查询速度提升10-100倍（B+树时间复杂度O(log n)）
• 覆盖索引避免回表（Extra: Using index）
• 排序优化（避免filesort）

看不见的成本账单

• 写操作代价：每个INSERT/UPDATE/DELETE需要更新所有相关索引
• 空间开销：每个二级索引约占用表数据的20%-30%
• 内存压力：InnoDB缓冲池需要缓存热索引页
• 维护成本：索引碎片、统计信息更新

2.索引数量的黄金分割线

危险警戒线

• 超过10个索引：写入性能可能下降50%+
• 单个索引超过5个字段：联合索引边际效益锐减
• 重复索引：(a,b)与(a)同时存在

最佳实践区间

• OLTP系统推荐3-5个精选索引
• 数据仓库可放宽至7-10个
• 每个索引不超过3个字段

3.索引设计的六大军规

高频查询优先法则

-- 查询频率统计示例
SELECT query_pattern, COUNT(*) 
FROM slow_query_log
WHERE table_name='orders'
GROUP BY query_pattern 
ORDER BY COUNT(*) DESC LIMIT 5;

联合索引左前缀原则

• 正确案例：WHERE a=1 AND b>2 ORDER BY c → INDEX(a,b,c)
• 错误案例：WHERE b=2 AND c=3 → 无法命中上述索引

区分度计算公式

# 字段区分度评估
selectivity = COUNT(DISTINCT column)/COUNT(*)
# 值>30%适合单独建索引

热点数据隔离策略

• 大字段单独存储（如JSON/text）
• 冷热数据分离（按时间分表）

索引复用艺术

• 排序复用：WHERE a=? ORDER BY b → INDEX(a,b)
• 覆盖查询：SELECT a,b WHERE c=? → INDEX(c,a,b)

动态调整机制

• 季度索引健康检查
• 使用ALTER TABLE ... ALGORITHM=INPLACE在线变更

4.实战案例：电商订单表索引优化

原始结构

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id INT,
  product_id INT,
  status TINYINT,
  price DECIMAL(10,2),
  created_at DATETIME,
  INDEX idx_user (user_id),
  INDEX idx_product (product_id),
  INDEX idx_status (status),
  INDEX idx_created (created_at)
);

优化方案

-- 删除单列索引
DROP INDEX idx_user, idx_product, idx_status, idx_created;


-- 创建复合索引
ADD INDEX idx_main_query (user_id, status, created_at);
ADD INDEX idx_product_query (product_id, status);
ADD INDEX idx_time_cover (created_at, price);

优化效果

• 索引数量从4→3
• 查询性能提升20%
• 写入速度提高40%

5.索引监控工具箱

索引利用率分析

SELECT OBJECT_NAME, INDEX_NAME, ROWS_READ
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA='your_db';

冗余索引检测

pt-duplicate-key-checker --user=root --password=xxx --database=your_db1.

索引健康度检查

SELECT TABLE_NAME, INDEX_NAME, 
       ROUND(STAT_VALUE*@@innodb_page_size/1024/1024,2) AS MB 
FROM mysql.innodb_index_stats 
WHERE stat_name='size';

6.新时代的索引哲学

当遇到索引抉择困境时，请记住

• 数据访问模式决定索引形态（而不是表结构）
• 索引是活的有机体，需要随业务进化
• 有时候不加索引才是最优解（如极低频查询）

最后送大家一个决策树

是否需要排序？ → 是否高频查询？ → 字段区分度如何？
   ↓            ↓               ↓
建联合索引     监控观察         拒绝索引