mysql中索引的大小与性能的平衡

索引字段越短、B+树层级越浅，磁盘I/O越少；前缀索引、联合索引顺序、索引数量、覆盖索引冗余、唯一索引锁机制均需权衡读写性能。

索引字段越短，B+树层级越浅

MySQL 的 PRIMARY KEY 和二级索引都基于 B+ 树，树的高度直接决定磁盘 I/O 次数。每层节点能存的键值数量，和单个索引项大小强相关：字段越长（比如用 VARCHAR(255) 存邮箱但只用前 20 位区分），页内能放的键就越少，树就更容易变高。

实操建议：

• 对字符串字段建索引时，优先考虑 INDEX (email(32)) 而非全字段，前提是业务上前 N 位已具备足够区分度
• 避免在 TEXT 或长 VARCHAR 上直接建普通索引；若必须搜索，改用前缀索引或倒排（如 FULLTEXT）
• 联合索引中把等值查询字段放前面，排序/范围字段放后面——顺序错会导致索引截断失效

索引不是越多越好，写放大和内存压力会反噬读性能

每次 INSERT/UPDATE/DELETE 都要同步更新所有相关索引。一个表有 5 个索引，写操作实际可能触发 5 次 B+ 树分裂+页写入。更隐蔽的问题是：索引总大小超过 innodb_buffer_pool_size，会导致频繁换页，SHOW ENGINE INNODB STATUS 中能看到大量 Pages made young 或 Pages read ahead 异常值。

判断依据：

• 用 SELECT SUM(index_length) FROM information_schema.TABLES WHERE table_schema = 'db' AND table_name = 't'; 查索引总字节数
• 对比 SHOW VARIABLES LIKE 'innodb_buffer_pool_size';，若索引体积 > 70% 缓冲池，就要警惕
• 慢查日志里反复出现 Using index condition 却仍慢，可能是索引太多导致缓存命中率低

覆盖索引能省掉回表，但字段冗余会拖慢写入和备份

覆盖索引（SELECT a,b FROM t WHERE a=1，且 INDEX(a,b) 存在）确实避免了聚簇索引回查，但 b 字段被重复存两份：一份在聚簇索引叶子页，一份在二级索引叶子页。这意味着：

• 插入一行，b 值要写两次；更新 b，也要同步改两个位置
• mysqldump 或物理备份时，数据文件体积增大，网络传输和恢复时间拉长
• 如果 b 是 JSON 或大文本，这种冗余代价远超查询收益

权衡做法：

-- 好：高频查询且 b 很小（如 status TINYINT）
CREATE INDEX idx_a_b ON t(a, b);
-- 小心：b 是 VARCHAR(500) 或 TEXT
-- 更优解：只建 idx_a，用 JOIN 或应用层二次查询补 b

唯一索引和普通索引在并发写入时表现不同

唯一索引（UNIQUE KEY）要求插入前做唯一性校验，InnoDB 必须加 next-key lock 锁住插入间隙；而普通索引只需在插入后加记录锁。高并发 INSERT 场景下，前者更容易引发锁等待甚至死锁。

典型现象：

• 批量导入时，Duplicate entry 报错少但 Lock wait timeout 多 → 可能是唯一索引锁冲突
• 用 INSERT ... ON DUPLICATE KEY UPDATE 时，唯一索引会强制走唯一扫描，比普通索引多一次查找
• 如果业务能接受应用层去重（如先 SELECT 再 INSERT），有时宁可去掉唯一约束，靠代码逻辑保一致性

真正难平衡的，从来不是“要不要加索引”，而是“这个索引在最差写负载下，会让主库延迟多少秒”。线上调优时，pt-index-usage 和 sys.schema_unused_indexes 只能告诉你“没用过”，没法告诉你“加了会不会拖垮写入”。得看 SHOW PROFILE FOR QUERY 里的 innodb_rows_inserted 和锁等待时间。