后端性能优化：数据库索引的七个关键点

索引不是银弹但绝大多数慢查询的根源恰恰是索引没建对。开发十年我见过太多系统在流量冲击下崩塌DBA拉出慢日志满屏的typeALL和rows134562。而修复方式往往简单到令人羞愧加一个复合索引查询从12秒降到30毫秒。这七千字我把数据库索引的七个关键点拆开揉碎每一处都是血泪换来的经验。1. 选择性是索引的灵魂但别被唯一值骗了很多人建索引第一反应是找区分度高的列比如订单号、身份证号。这没错但选择性不是越高越好而是够用就好。一个只有0和1的布尔列选择性极低可如果业务上90%的查询都带WHERE is_deleted0那这个低选择性列放在索引最左端反而能过滤掉海量已删数据——前提是剩余10%的历史数据不常被访问。真正的选择性问题出现在前缀重复度上比如电话号码前三位是运营商号段前七位是地区码如果你只对前六位建索引那区分度可能只有几十完全无法限制扫描范围。我建议用COUNT(DISTINCT LEFT(col,N)) / COUNT()去实际测算曲线的拐点找到那个增加前缀长度几乎不再提升区分度的位置。别用直觉判断选择性用SQL跑一遍基数统计。2. 复合索引的最左前缀原则恰恰是最常被违反的MySQL、PostgreSQL都遵循最左前缀匹配。但这带来一个隐蔽陷阱把等值查询的列放在前面范围查询的列放在后面。比如WHERE status1 AND create_time 2024-01-01复合索引应该建(status, create_time)因为status是精确匹配create_time是范围。可一旦调换顺序MySQL只能用到索引里status那一截create_time的范围条件会退化成文件排序。更微妙的是多个范围条件同时存在时只能有一个范围列用到索引剩下的全靠回表。所以建复合索引前先问自己这条SQL里哪些列是哪些是或或BETWEEN把等值列全部前置范围列放最后且一个索引最多容忍一个范围列。3. 覆盖索引是最廉价的性能核弹为什么覆盖索引效果炸裂因为它让查询完全绕开回表操作。普通二级索引找到主键后还得再去聚簇索引里捞整行数据这中间是随机I/O。而覆盖索引的叶子节点已经包含了查询需要的所有列InnoDB直接返回索引数据。设计时要刻意让索引覆盖高频查询的字段。比如列表页通常只显示id, title, create_time那你建(status, create_time, title)时如果查询只挑这三列这个索引直接变成覆盖索引。加一个看似多余的列能省掉每次查询几千次随机I/O。但也要克制索引不是越宽越好每个索引都会拖慢写入速度并占据内存缓冲池。覆盖索引只对高频且固定的查询值得堆字段低频宽表查询不如直接建普通索引。4. 前缀索引能救大字段但有三个致命前提遇到VARCHAR(255)的URL、邮箱、备注这种超长列直接建完整索引会吃掉大量空间缓冲池热数据被挤占。前缀索引把前N个字符提取出来做索引键体积骤降。但前缀索引无法用于ORDER BY和GROUP BY也无法覆盖查询因为索引里存的不是完整值。更坑的是前缀索引让选择性计算变得不可见你看着t.猜了10个字符实际可能基数和5个字符没区别。我见踩坑最多的场景是把email列建了前缀索引但查询条件是WHERE email abcexample.comMySQL会先按前缀匹配到几百条再回表逐行比对——这其实比全表扫描快了但远没有完整索引快。只有在列长度极大、且该列基本只做等值查询、且不做排序分组时才值得用前缀索引否则宁可把列改成冗余计算字段直接存哈希值。5. 索引失效的六个真凶每一个都让人头皮发麻很多开发者背熟了不要对索引列做函数操作、不要隐式类型转换但实践中还有更阴险的场景。最隐蔽的是字符集不一致表A的user_id是utf8mb4表B的user_id是utf8JOIN时MySQL只能把其中一个转成另一个索引直接失效扫描行数从几千飙到几十万。其次是IS NULL不等于IS NOT NULLInnoDB对WHERE col IS NULL其实可以利用索引但IS NOT NULL通常不走因为索引里不存储NULL值相反方向扫描代价更高。还有LIKE %keyword这个经典的左模糊任何索引都救不了除非你改成全文索引或者反转存储。别忘了数据分布会让优化器放弃索引当索引列有大量重复值比如status1占了全表90%优化器一算发现走全表扫描比读索引快于是执行计划无视索引。这种情况的正确解法是分区表或partial index而不是抱怨优化器傻。最后IN加子查询的坑当IN列表超过某个阈值MySQL 8.0之前是200左右优化器会从索引扫描退化成全表扫描。6. 索引不是越多越好写入放大是隐形杀手每个索引在InnoDB里都是一棵独立的B树。插入一行数据聚簇索引写一次每一棵二级索引树都要各写一次。如果表上有5个索引单条INSERT的磁盘写入次数就从1变成6。高并发写入场景下索引数量直接决定TPS上限。更糟的是二级索引树的叶子节点是有序的随机插入会引发大量页分裂和页合并产生写放大。读多写少的表可以堆索引写多读少的表必须砍索引。我见过一个订单表开发为了应对各种运营报表建了12个索引结果高峰期每次插入要更新12棵B树磁盘I/O直接饱和。后来砍到4个核心索引把报表需求改成走只读从库系统立刻稳了。索引管理要像代码评审一样每个索引都必须有明确的业务SQL背书。7. 监控与维护索引的健康度比建索引更关键索引建好就能高枕无忧大错特错。统计信息会过期MySQL通过采样统计的基数会随数据增删而变化旧的统计信息会让优化器做出错误选择。定期ANALYZE TABLE是必需品但别做太勤每天一次即可。索引碎片化发生在高频更新场景页分裂留下太多空洞逻辑顺序和物理顺序偏离严重导致范围扫描时随机I/O激增。ALTER TABLE ... ENGINEInnoDB可以重建表消除碎片但锁表时间长得用gh-ost这类在线工具。还有冗余索引检测idx_a_b_c和idx_a_b是重复的后者完全没必要却白白承担写入成本。用sys.schema_unused_indexes查看从未被使用的索引果断删除。别忘了索引监控要关联慢日志每一条慢SQL背后都可能藏着一个没走索引的查询。七个关键点讲完你会发现索引设计的本质是权衡——用存储空间换查询速度用写入延迟换读延迟用维护复杂度换运行时简单。没有放之四海而皆准的规则只有针对具体业务负载的取舍。下次再遇到慢查询别急着在索引上堆砌先看执行计划再用PROCEDURE ANALYSE()分析字段分布最后模拟最坏情况的写入压力。真正高手的索引设计永远是对业务查询模式的深刻理解而不是对索引语法的机械套用。回头再看那些被DBA骂SQL都不会写的夜晚其实每一条慢SQL都在教你认识数据的形状。数据库优化没有终点只有下一场流量高峰。索引是你与磁盘之间的谈判筹码——你多建一个索引磁盘就得多流一滴汗水。学会用最少的结构支撑最锋利的查询这才是后端性能优化的艺术。