索引这块内容我前后踩过的坑比很多新手写的代码都多。刚入行那会儿觉得建个索引就是加一行CREATE INDEX的事结果上线之后查询没快多少写入反而慢了一截磁盘空间还蹭蹭往上涨。后来才慢慢搞明白普通索引、唯一索引、复合索引这三兄弟各有各的脾气用错了地方就是给自己挖坑。这篇内容我打算把索引这件事从头到尾捋一遍重点讲清楚三种索引到底差在哪、什么时候该用哪种、复合索引的字段顺序为什么能决定一条 SQL 的生死以及怎么通过执行计划去验证索引到底有没有被用上。不管你是刚接触数据库不久、还在被慢查询折磨的新手还是已经能写几句 SQL 但没系统梳理过索引逻辑的开发者看完应该都能直接拿去对照自己项目里的表结构做一轮排查。索引不是建得越多越好这句话谁都听过但真正理解它背后的代价和取舍得把原理和实操都过一遍才行。1. 索引整体设计与选型思路拆解1.1 索引到底在解决什么问题先把最基础的事情说透。数据库里的数据存在磁盘上一张表几十万行的时候你要找其中某几行如果没有索引数据库只能从头到尾扫一遍这叫全表扫描。数据量小的时候无所谓几万行扫一下也就毫秒级但一旦到了百万级、千万级全表扫描的时间会线性增长一条查询卡住几秒甚至几十秒都很正常。索引的本质是一棵排好序的数据结构常见的是 B 树。你可以把它理解成书的目录想找某一章不用从第一页翻起直接看目录定位页码就行。数据库通过索引快速定位到目标数据所在的页避免逐行扫描。代价是什么呢目录本身要占篇幅而且每次书的内容有改动目录也得跟着更新。对应到数据库就是索引占磁盘空间、写入时要维护索引结构。所以索引设计的核心矛盾就一句话用额外的存储空间和写入开销换取查询效率的提升。所有的选型决策本质上都是在这个矛盾里找平衡点。理解了这个后面普通索引、唯一索引、复合索引的选择就有了判断依据而不是死记硬背。1.2 三种索引的定位差异普通索引是最基础的一种它只负责加速查询对字段值没有任何约束。一个字段上可以有重复值NULL 值也允许出现。它的存在纯粹是为了让WHERE条件里的等值查询和范围查询走索引而不是全表扫描。唯一索引在普通索引的基础上加了一层约束索引列的值必须唯一不允许重复。但要注意唯一索引对 NULL 的处理在不同数据库里行为不完全一致多数实现里允许多个 NULL 值存在因为 NULL 不等于 NULL。唯一索引除了加速查询还承担了数据完整性的职责比如用户表的手机号、订单号这类天然唯一的字段用唯一索引既保证了不重复又顺带有了查询加速。复合索引是建立在多个字段上的索引也叫联合索引。它的关键不在于多个字段这个表面特征而在于字段顺序。复合索引遵循最左前缀原则查询条件必须从索引的第一个字段开始匹配才能用上索引。这个特性决定了复合索引的设计必须紧密结合实际的查询模式顺序排错了索引基本等于白建。索引类型是否允许重复值是否允许 NULL主要作用典型场景普通索引允许允许加速查询状态字段、分类字段唯一索引不允许视实现而定加速查询 唯一约束手机号、订单号、邮箱复合索引组合内允许视字段而定加速多条件查询多条件组合筛选1.3 为什么不能无脑多建索引我见过不少项目表上挂了七八个索引问起来就是怕查询慢多建几个保险。这种做法的问题在于每一个索引都是一棵独立的 B 树插入、更新、删除数据时所有相关的索引树都要同步维护。写入频繁的表上索引过多写性能下降会非常明显。还有一个容易被忽略的点索引过多会让查询优化器在选择执行计划时犯难。候选索引太多优化器可能选错索引反而导致查询变慢。另外每个索引都占磁盘空间数据量大的表上索引占用的空间甚至可能超过数据本身。我的经验是一张表的索引数量控制在五个以内比较稳妥核心业务表可以适当放宽但每加一个索引都要能说清楚它服务于哪条具体的查询。说不清楚的大概率就是冗余索引该删就删。2. 普通索引的核心细节与实操要点2.1 普通索引的创建与验证普通索引的创建语法很直接以常见的 MySQL 为例-- 建表时创建 CREATE TABLE t_order ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, INDEX idx_status (status) ); -- 已有表上追加 ALTER TABLE t_order ADD INDEX idx_create_time (create_time);建完之后一定要验证索引是否真的被用上不能建完就当万事大吉。用EXPLAIN看执行计划是最直接的手段EXPLAIN SELECT * FROM t_order WHERE status 1;重点看几个字段type如果是ALL说明走了全表扫描索引没生效key显示实际使用的索引名rows是预估扫描行数Extra里如果出现Using index说明是覆盖索引效率更高。我习惯在每次加完索引后都跑一遍EXPLAIN确认type从ALL变成了ref或range才算真正落地。注意EXPLAIN的rows是优化器的预估值不是精确值。数据分布不均匀时预估值可能偏差较大必要时可以用ANALYZE TABLE更新统计信息后再看。2.2 普通索引失效的几种典型情况索引建了不代表一定用得上下面这几种情况是我在实际排查中最常遇到的失效场景。第一种是在索引列上做运算或函数操作。比如WHERE DATE(create_time) 2024-01-01对索引列套了函数索引直接失效。正确写法是改成范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02。这个改写看着简单但实际项目里因为图省事写函数导致慢查询的例子太多了。第二种是隐式类型转换。字段是字符串类型查询时传了数字或者反过来数据库会做隐式转换索引可能失效。比如手机号字段存的是VARCHAR查询写成WHERE phone 13800138000没加引号就可能触发转换。养成类型匹配的习惯能省掉很多麻烦。第三种是使用LIKE以通配符开头。WHERE name LIKE %张这种前置模糊匹配用不上 B 树索引因为索引是按前缀排序的前缀不确定就没法定位。如果业务确实需要后缀匹配可以考虑倒序存储字段再建索引或者用专门的全文检索方案。第四种是**OR连接的条件中有一侧没有索引**。WHERE a 1 OR b 2如果b上没有索引整个查询可能退化成全表扫描。这种情况要么给b也补上索引要么用UNION改写。2.3 普通索引的实操心得说几个文档里不太会写、但实际很管用的经验。低选择性的字段单独建索引意义不大。比如性别字段只有男女两种值状态字段只有三五种状态单独建索引后优化器很可能判断走索引还不如全表扫描直接放弃使用。这类字段更适合作为复合索引的一部分配合高选择性字段一起用。区分度可以用一个简单的 SQL 估算SELECT COUNT(DISTINCT status) / COUNT(*) FROM t_order;结果越接近 1 说明区分度越高越适合建索引。一般区分度低于 0.1 的字段单独建索引要慎重。另外NULL值在索引里的处理也值得留意。索引会记录 NULL 值但WHERE col IS NULL这类查询能否用上索引取决于数据分布和优化器判断。如果表里 NULL 值占比很高这类查询走索引的收益有限。3. 唯一索引的约束价值与使用边界3.1 唯一索引不只是查询加速很多人把唯一索引当成普通索引的加强版只关注它查询快不快忽略了它最重要的价值——数据完整性约束。在应用层做唯一性校验是有漏洞的高并发场景下先查后插存在竞态条件两个请求同时查到不存在、同时插入就产生了重复数据。唯一索引把校验下沉到数据库层由存储引擎保证原子性这是应用层逻辑替代不了的。-- 用户表手机号唯一 ALTER TABLE t_user ADD UNIQUE INDEX uk_phone (phone); -- 插入重复数据会直接报错 INSERT INTO t_user (phone, nickname) VALUES (13800138000, A); INSERT INTO t_user (phone, nickname) VALUES (13800138000, B); -- 报错报错信息通常是 duplicate key 相关的提示应用层捕获这个错误后转换成友好的业务提示即可。这种依赖数据库约束兜底的做法比在代码里写一堆查重逻辑要可靠得多。3.2 唯一索引与普通索引的性能差异从查询角度看唯一索引和普通索引的效率差异其实很小因为都是 B 树结构查找路径基本一致。有一个细微差别唯一索引找到第一个匹配值就可以停止检索因为不可能有重复普通索引找到匹配值后还要继续往后找确认没有更多匹配。但这个差异在绝大多数场景下可以忽略。真正有差异的是写入性能。唯一索引在插入时需要额外做一次唯一性检查这个检查在数据量大、并发高的时候会带来可感知的开销。所以对于写入极其频繁、且业务上能保证唯一性的字段是否一定要加唯一索引需要权衡。我的判断标准是如果这个字段的唯一性是业务硬性要求那必须加性能开销是必要成本如果只是大概率不重复那普通索引加应用层校验可能更合适。3.3 唯一索引的常见坑第一个坑是唯一索引与软删除的冲突。很多表用is_deleted标记删除删除后想重新插入相同手机号的用户唯一索引会拦住。解决办法是把唯一索引建在(phone, is_deleted)这样的复合字段上或者用删除时间戳代替布尔标记让已删除记录的索引值不冲突。第二个坑是批量插入时的部分失败。批量插入多条数据其中一条违反唯一约束不同数据库的行为不一样有的整批回滚有的跳过冲突行继续。做数据迁移或批量导入时一定要先确认目标库的行为必要时用INSERT IGNORE或ON DUPLICATE KEY UPDATE这类语法明确处理冲突。第三个坑是唯一索引字段的长度。字符串字段建唯一索引时如果字段很长索引本身会很大。可以考虑只对前缀建索引但前缀索引无法保证全局唯一这个取舍要提前想清楚。提示给已有数据的表加唯一索引前务必先跑一遍重复值检查SELECT phone, COUNT(*) FROM t_user GROUP BY phone HAVING COUNT(*) 1;有重复数据的话加索引会直接失败。4. 复合索引的最左前缀原则与字段顺序设计4.1 最左前缀原则到底怎么理解复合索引是三种索引里最需要动脑子的一种。假设建了INDEX idx_abc (a, b, c)它相当于同时建了(a)、(a, b)、(a, b, c)三个索引。查询条件必须包含a才能用上这个索引只查b或c用不上。-- 能用上 idx_abc SELECT * FROM t WHERE a 1; SELECT * FROM t WHERE a 1 AND b 2; SELECT * FROM t WHERE a 1 AND b 2 AND c 3; -- 用不上 idx_abc缺少最左字段 a SELECT * FROM t WHERE b 2; SELECT * FROM t WHERE c 3; SELECT * FROM t WHERE b 2 AND c 3;这里有个容易误解的点WHERE a 1 AND c 3这种跳过了b的查询能用上索引吗答案是能用上a的部分但c用不上因为b断了。索引只能从最左字段开始连续匹配中间断了后面的字段就无法用于索引定位。还有一个细节范围查询会截断后续字段的索引使用。WHERE a 1 AND b 2 AND c 3a和b能用上索引但c用不上因为b是范围条件范围之后的字段无法继续用于精确定位。所以复合索引设计时等值查询的字段要放在范围查询字段的前面。4.2 字段顺序怎么排才合理字段顺序是复合索引设计的核心排错了索引基本白建。我的排序原则是等值条件字段在前范围条件字段在后排序字段紧随其后。举个实际例子。假设有个订单查询场景常见 SQL 是SELECT * FROM t_order WHERE user_id 100 AND status 1 ORDER BY create_time DESC LIMIT 20;这里user_id和status是等值条件create_time用于排序。合理的复合索引是(user_id, status, create_time)。这样查询时user_id和status用于定位create_time用于排序整个查询可以完全走索引连排序都省了。如果顺序排成(status, user_id, create_time)虽然也能用上索引但status区分度低定位效率不如user_id在前。如果排成(create_time, user_id, status)那user_id和status的等值条件反而用不上了因为最左字段是create_time查询条件里没有它。查询模式推荐索引顺序理由等值 等值 排序(等值1, 等值2, 排序字段)等值定位后排序可直接利用索引等值 范围(等值字段, 范围字段)范围字段放最后避免截断等值 等值 范围(等值1, 等值2, 范围字段)等值在前保证定位精度仅范围查询(范围字段)单字段索引即可4.3 复合索引的覆盖索引优化复合索引还有一个杀手锏用法覆盖索引。如果查询需要的所有字段都包含在索引里数据库可以直接从索引返回数据不用回表查主键对应的行。回表是随机 IO代价比顺序读索引高得多能避免回表对性能提升非常明显。-- 索引 idx_user_status_time (user_id, status, create_time) -- 下面这条查询只需要索引里的字段可以走覆盖索引 SELECT user_id, status, create_time FROM t_order WHERE user_id 100 AND status 1;EXPLAIN的Extra字段出现Using index就说明走了覆盖索引。设计复合索引时如果某条高频查询只需要少数几个字段可以考虑把这些字段都放进索引用空间换时间。但要注意索引字段越多索引越大写入维护成本越高不能滥用。4.4 复合索引的实操避坑第一个坑是索引冗余。建了(a, b, c)之后再单独建(a)或(a, b)就是冗余的因为复合索引已经覆盖了这些前缀。冗余索引浪费空间还拖慢写入定期用工具或手工排查清理很有必要。第二个坑是字段顺序频繁变动。业务查询模式变了索引顺序可能要跟着调整。但改索引在大表上是有成本的ALTER TABLE可能锁表或者耗时很长。线上大表加索引要用在线 DDL 工具或者选低峰期操作这个流程要提前规划好。第三个坑是过度追求覆盖索引。把所有查询字段都塞进索引索引体积膨胀缓存命中率下降反而得不偿失。覆盖索引适合高频、字段少的核心查询不是所有查询都值得覆盖。5. 索引查询验证与执行计划排查实录5.1 用 EXPLAIN 读懂执行计划索引建得好不好最终要靠执行计划说话。EXPLAIN输出的字段里我重点关注这几个type访问类型从好到差大致是system const eq_ref ref range index ALL。出现ALL就是全表扫描必须优化index是全索引扫描也不理想ref和range是正常走索引的表现。key实际使用的索引。如果是NULL说明没走索引。key_len索引使用的字节数可以判断复合索引用到了几个字段。rows预估扫描行数越小越好。Extra额外信息Using index是覆盖索引Using filesort是额外排序可能要优化Using temporary是用了临时表通常要优化。EXPLAIN SELECT * FROM t_order WHERE user_id 100 AND status 1 ORDER BY create_time DESC LIMIT 20;如果Extra里出现Using filesort说明排序没走索引需要检查复合索引的字段顺序是否把排序字段放在了合适位置。5.2 常见问题速查表现象可能原因排查方向解决思路type 为 ALL无可用索引或索引失效检查 WHERE 条件字段是否有索引补索引或改写 SQLkey 为 NULL索引未命中检查是否函数运算、类型转换改写条件避免列上运算出现 Using filesort排序未走索引检查 ORDER BY 字段与索引顺序调整复合索引字段顺序出现 Using temporary分组或去重用了临时表检查 GROUP BY、DISTINCT优化索引或改写查询索引建了但没生效选择性低或统计信息过期看区分度跑 ANALYZE TABLE考虑复合索引或更新统计写入变慢索引过多统计索引数量和使用频率清理冗余索引5.3 一次真实的慢查询排查记录之前遇到过一个列表查询数据量大概两百万行查询条件有用户 ID、状态和时间范围还带分页排序。最初表上只有主键索引查询要三秒多。我先加了(user_id)单字段索引降到八百毫秒左右但还是不理想。用EXPLAIN一看type是ref但Extra里有Using filesort说明排序没走索引。于是把索引改成(user_id, status, create_time)再跑EXPLAINtype变成rangeExtra里的Using filesort消失了查询降到几十毫秒。这个案例说明单字段索引只能解决定位问题排序和范围条件要靠复合索引一起解决。排查的时候不要只看有没有走索引还要看走得彻不彻底Extra里的信息往往才是性能瓶颈的真正线索。5.4 索引维护的日常习惯索引不是建完就一劳永逸的。业务查询模式会变数据分布会变索引也需要定期审视。我习惯每隔一段时间做几件事查一下库里索引的使用统计不同数据库有不同方式有的提供sys库或性能视图找出从未被使用的索引检查是否有重复或前缀重叠的索引对大表的索引做一次碎片整理或重建。还有一点测试环境的索引策略要和线上保持一致。我见过测试库索引建得齐全、线上漏建的情况结果测试一切正常上线直接慢查询。索引变更要纳入发布流程用脚本管理避免手工操作遗漏。6. 三种索引的选型决策与组合策略6.1 按业务场景选索引类型选型的起点永远是业务查询模式而不是索引类型本身。我的判断路径大致是这样先看字段是否需要唯一约束需要就上唯一索引顺带解决查询不需要唯一约束但查询频繁上普通索引多个字段经常组合出现在查询条件里上复合索引。举个综合例子。用户表有手机号、昵称、注册时间、状态几个字段。手机号天然唯一建唯一索引状态字段区分度低单独建索引意义不大但如果经常和注册时间组合查询可以建(status, create_time)复合索引昵称如果支持模糊搜索普通索引对前置模糊无效要考虑其他方案。6.2 索引组合的优先级当一张表需要多个索引时优先级排序很重要。高频核心查询的索引优先低频查询的索引可以往后放甚至不建。复合索引能覆盖的场景就不要再用多个单字段索引去凑前者往往更高效。还有一个策略是用复合索引替代部分单字段索引。比如有了(user_id, status)单独的(user_id)索引就是冗余的可以删掉。但要注意如果存在只查status的查询那(user_id, status)帮不上忙这种情况要么接受全表扫描要么单独给status建索引取决于查询频率和性能要求。6.3 索引设计的检查清单最后整理一份我实际会用的检查清单建索引或审查索引时逐条过一遍每个索引是否对应至少一条明确的查询说不清用途的考虑删除。复合索引的字段顺序是否匹配最左前缀原则等值条件字段是否排在范围条件字段之前排序字段是否包含在索引中以避免 filesort是否存在前缀重叠的冗余索引唯一约束是否由唯一索引在数据库层保证大表加索引是否走了在线 DDL 或低峰期操作索引变更是否纳入了发布流程和版本管理这套清单看着琐碎但每一条背后都是实际踩过的坑。索引这件事原理不复杂难的是结合具体业务做取舍以及在变更时保持谨慎。我个人的体会是与其事后对着慢查询日志救火不如在建表设计阶段就把查询模式想清楚把索引规划好。真到了线上出问题EXPLAIN和执行计划永远是最可靠的排查入口别凭感觉猜让数据说话。
