星图导航
首页 文章 排行榜 申请收录 友链申请 关于
首页 / 文章资讯 / 源码资源 / 数据库索引设计:什么字段该建、什么不该建

数据库索引设计:什么字段该建、什么不该建

星图导航配图

索引是数据库优化里最容易做错的一件事。有人一条索引都不建,查询慢得离谱;也有人见字段就建,写数据慢、磁盘占用高,性能反而下降。判断标准其实不复杂,难在坚持按标准做。

该建索引的字段

高频出现在查询条件里的字段,是第一优先级。翻慢查询日志,看哪些条件反复出现,那些字段就是候选。凭感觉建索引是浪费时间,日志才是依据。

选择性高的字段,比如唯一标识。一个字段如果有一万种不同的值,建索引的过滤效果极好;如果只有三种值,索引帮不上什么忙。选择性是索引价值的核心指标。

用于排序和分组的字段。按时间倒序取最近十条,这个排序如果用不上索引,数据库就要把所有数据读出来再排。加了索引,直接按顺序取前十条。

关联查询的连接字段。两张表做关联,连接字段没索引,就会退化成全表扫描。这类问题在数据量上来之后特别明显。

还有一类容易漏:经常用于精确匹配的状态字段。比如查询未审核的记录,虽然状态只有几种值,但如果九成数据都是已审核,查未审核的反而选择性很高。

最后一种浪费是给临时表建索引。跑一次分析就删的表,建索引的时间比查询本身还长。

不该建的情况

字段值种类极少,比如性别。只有两种值,索引几乎起不到过滤作用,查询还是要扫大半个表。这种字段建索引纯属负担。

表数据量很小,全表扫更快。几百条数据的表,加索引反而多一层查找开销。这种表不用管,等数据量上来再说。

更新极频繁的字段。每次更新都要维护索引,写入性能会明显下降。如果一个字段每秒都在变,又不需要按它查询,就不要建。

已经能被复合索引覆盖的前缀。复合索引的最左字段单独建一条索引,是重复的。这种冗余索引除了占空间没有任何作用。

还有一个常见浪费:为了一张只在后台用的报表查询建索引,结果拖慢了所有前台写入。要权衡主次,不能为了一个低频需求影响主流程。

复合索引的顺序

等值条件放最左。查询条件是状态等于某某并且时间大于某某,状态是等值,时间范围,那么状态放左边。这样能先用等值条件把范围缩小。

范围条件放后面。范围条件一旦出现,后面的字段就没法用索引做精确过滤了。所以范围字段要尽量往后排。

排序字段顺序要和索引一致。索引是状态加时间正序,查询要按时间倒序,那索引就用不上排序。顺序必须严格对应。

遵循最左前缀原则。复合索引只在最左边的字段开始连续匹配才有效。查询只用第二个字段,索引就失效。这个原则决定了字段的排列顺序。

实际设计时,先列出这个表最常见的三到五种查询组合,找出它们的共同前缀,用一条复合索引覆盖多数场景。这比建五条单列索引高效得多。

还有个高频场景:联合查询里对索引字段做了排序却没走索引。查询条件用了索引,但排序字段不是索引的一部分,数据库会先取数据再排序,索引的过滤作用还在,排序的收益没拿到。

索引的统计信息过期也会造成误判。数据库基于统计信息选择执行计划,统计信息陈旧时,它可能认为全表扫描更便宜,明明有索引却不用。定期更新统计信息是必要的维护动作。

索引失效的常见原因

字段上用了函数或运算。日期字段加了个函数做格式化,索引立刻失效。改写成对常量做运算,让字段保持原样。

隐式类型转换,字符串当数字用。字段是字符串,查询传了数字,数据库会做转换,转换过程导致索引失效。类型要严格对应。

前导通配的模糊查询。像百分号开头加关键词这种写法,索引没法用。要么改成后缀匹配,要么上全文检索。

OR 条件里有字段没索引。一个 OR 的任一侧没索引,整个条件都可能退化成全表扫描。要么两侧都建,要么改成两次查询合并。

实用建议

先看慢查询日志再决定建索引。没有日志就建索引,是在猜。日志里耗时最长的几条查询,才是你该优化的对象。

定期检查未使用的索引并清理。数据库里有统计信息,能看出哪些索引从未被使用。这些索引只占空间、拖慢写入,该删。

大表加索引要选低峰期。给千万级的表加索引,数据库会锁表或者消耗大量 IO,白天操作直接影响线上。

改索引后复测查询耗时。加了索引不一定快,可能因为统计信息没更新或者顺序不对而没生效。测一遍才知道。

最后一句:索引是给查询用的,不是给字段用的。先想清楚你要怎么查,再决定建什么索引。反过来做,建出来的索引十有八九用不上。