单列索引与多列索引从典型查询看索引设计文章目录单列索引与多列索引从典型查询看索引设计一、从一个常见查询说起二、单列索引是什么三、多列索引是什么四、最左前缀原则五、单列索引和多列索引的核心区别六、典型场景到底该建哪种索引场景 1只按一列查询场景 2固定组合条件查询场景 3组合条件 排序场景 4已有复合唯一约束最左列查询七、什么时候才需要额外建单列索引八、如何验证是否需要新建索引九、生产环境建索引建议十、总结在数据库优化中索引是最常用、也最容易用错的手段之一。很多人遇到慢查询时第一反应是“加个索引”但索引并不是越多越好。尤其是当表里已经有复合索引时再盲目加单列索引可能不仅没有收益还会增加写入成本、占用更多存储空间。本文从几个常见业务场景出发讲清楚单列索引和多列索引的区别以及什么时候该用哪种索引。一、从一个常见查询说起假设有一张订单表CREATETABLEorders(idbigintPRIMARYKEY,user_idbigintNOTNULL,statustextNOTNULL,created_attimestampNOTNULL,amountnumeric(18,2));常见查询可能有-- 查询某个用户的所有订单SELECT*FROMordersWHEREuser_id123;-- 查询某个用户已支付的订单SELECT*FROMordersWHEREuser_id123ANDstatusPAID;-- 查询某个用户已支付的订单按下单时间排序SELECT*FROMordersWHEREuser_id123ANDstatusPAIDORDERBYcreated_atDESC;面对这些查询应该建单列索引还是多列索引要回答这个问题先要理解两者的本质区别。二、单列索引是什么单列索引只包含一个列例如CREATEINDEXidx_orders_user_idONorders(user_id);它按照user_id排序。适合这类查询WHEREuser_id123WHEREuser_idIN(123,456,789)ORDERBYuser_id单列索引的优点是结构简单索引体积相对小对单列等值、范围、排序查询友好写入维护成本相对低。但它也有明显局限如果查询同时过滤多个列单列索引通常只能选其中一个使用或者通过多个单列索引做位图扫描效率不一定理想。三、多列索引是什么多列索引也叫复合索引它包含多个列例如CREATEINDEXidx_orders_user_status_createdONorders(user_id,status,created_at);这个索引不是简单地“同时给三列建索引”而是按照定义的顺序组织数据先按 user_id 排序 user_id 相同再按 status 排序 前两列相同再按 created_at 排序可以把它想象成电话簿先按姓氏排序姓氏相同再按名字排序。这种结构决定了复合索引的一个核心规则最左前缀原则。四、最左前缀原则对于复合索引(a,b,c)它能较好支持以下查询条件WHEREa?WHEREa?ANDb?WHEREa?ANDb?ANDc?WHEREa?ANDb?ANDc?-- c 通常不能有效缩小扫描范围但通常不能单独高效支持WHEREb?WHEREc?WHEREb?ANDc?原因很简单索引是先按a排序的。如果查询条件里没有a数据库很难直接定位到目标数据区域。所以复合索引的列顺序非常关键。一般建议等值过滤条件放前面范围过滤条件放后面排序字段可以放在最后尤其是与等值条件配合时选择性高的列不一定要放最前要结合查询模式综合判断。五、单列索引和多列索引的核心区别对比项单列索引多列索引包含列一列多列排序方式按该列排序按定义顺序逐列排序支持查询单列条件、排序组合条件、排序、覆盖索引最左前缀不涉及必须遵循索引体积通常较小通常较大写入成本较低较高冗余风险可能与复合索引最左列重复列顺序不合理时效果差适用场景高频单列查询固定组合查询、排序分页一句话概括单列索引解决“一列怎么查”的问题多列索引解决“多列怎么组合查、怎么排序”的问题。六、典型场景到底该建哪种索引场景 1只按一列查询SELECT*FROMordersWHEREuser_id123;如果这是最高频查询且没有其他复合索引可用那么可以建CREATEINDEXidx_orders_user_idONorders(user_id);场景 2固定组合条件查询SELECT*FROMordersWHEREuser_id123ANDstatusPAID;更合适的是复合索引CREATEINDEXidx_orders_user_statusONorders(user_id,status);因为数据库可以先定位user_id再在相同user_id内定位status效率通常比只用单列索引更好。场景 3组合条件 排序SELECT*FROMordersWHEREuser_id123ANDstatusPAIDORDERBYcreated_atDESC;可以考虑CREATEINDEXidx_orders_user_status_createdONorders(user_id,status,created_at);这样既能过滤又能利用索引顺序避免额外排序。场景 4已有复合唯一约束最左列查询很多表会有类似唯一约束UNIQUE(user_id,order_no)数据库会自动为它创建复合唯一索引(user_id, order_no)此时如果查询是WHEREuser_id123通常可以直接走这个复合唯一索引因为user_id是它的最左列。也就是说不一定需要再单独给user_id建一个单列索引。这是一个常见误区看到查询条件里只有user_id就立刻想建(user_id)单列索引。实际上如果已有(user_id, order_no)这样的复合索引单列索引很可能只是冗余。七、什么时候才需要额外建单列索引虽然复合索引的最左列可以支持单列查询但并不是所有情况都能完全替代单列索引。以下情况可以考虑额外建单列索引复合索引最左列不是该列例如已有索引(status, user_id)但高频查询是WHERE user_id ?。这时user_id不是最左列复合索引通常帮不上忙。复合索引太大单列索引更小、缓存更友好如果复合索引包含很多列体积很大而单列查询又极其高频单独建一个小索引可能减少 I/O。高频单列查询且现有复合索引选择性不足例如复合索引最左列基数很低单独查询该列时区分度差优化器可能更倾向全表扫描。外键列查询某些业务会频繁按外键列查询而现有复合索引的最左列不是该外键可能需要单独索引。实测证明现有索引不够快最终判断标准不是理论而是执行计划和实际耗时。八、如何验证是否需要新建索引不要凭感觉加索引先用执行计划验证。以 PostgreSQL 为例EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREuser_id123;重点看是否出现Index Scan或Bitmap Index Scan使用了哪个索引actual rows和预估行数差异大不大Buffers显示读了多少数据块是否出现Seq Scan以及表有多大。如果执行计划已经走了已有的复合索引并且性能可接受就不需要再建单列索引。如果发现表很大查询很频繁现有索引没有用上或用了但扫描行数过多再考虑新建索引并继续用EXPLAIN ANALYZE对比优化前后效果。九、生产环境建索引建议避免冗余索引已有(a, b)时再建(a)通常是冗余的除非有明确实测理由。使用CONCURRENTLY创建索引PostgreSQL 中生产环境建议CREATEINDEXCONCURRENTLY idx_nameONtable_name(column_name);避免长时间锁表。关注索引使用情况可以通过pg_stat_user_indexes查看索引扫描次数找出长期未被使用的索引。考虑覆盖索引PostgreSQL 支持INCLUDECREATEINDEXidx_orders_user_status_includeONorders(user_id,status)INCLUDE(created_at,amount);可以减少回表但会增加索引体积。考虑部分索引如果只查询某类状态的数据CREATEINDEXidx_orders_active_userONorders(user_id)WHEREstatusACTIVE;索引更小维护成本更低。索引不是越多越好每个索引都会增加INSERT、UPDATE、DELETE的成本。写入频繁的表尤其要控制索引数量。十、总结单列索引和多列索引不是互相替代的关系而是服务于不同查询模式单列索引适合单列过滤、单列排序多列索引适合固定组合条件、组合排序、覆盖查询复合索引遵循最左前缀原则最左列可以支持单列查询但非最左列通常不能单独高效使用已有复合索引时不要习惯性再建最左列单列索引先看执行计划最终是否建索引要靠EXPLAIN ANALYZE和真实业务查询验证。索引设计的核心不是“多”而是“准”。理解查询模式合理安排列顺序避免冗余索引才能让数据库在读写之间取得更好的平衡。
单列索引与多列索引：从典型查询看索引设计
单列索引与多列索引从典型查询看索引设计文章目录单列索引与多列索引从典型查询看索引设计一、从一个常见查询说起二、单列索引是什么三、多列索引是什么四、最左前缀原则五、单列索引和多列索引的核心区别六、典型场景到底该建哪种索引场景 1只按一列查询场景 2固定组合条件查询场景 3组合条件 排序场景 4已有复合唯一约束最左列查询七、什么时候才需要额外建单列索引八、如何验证是否需要新建索引九、生产环境建索引建议十、总结在数据库优化中索引是最常用、也最容易用错的手段之一。很多人遇到慢查询时第一反应是“加个索引”但索引并不是越多越好。尤其是当表里已经有复合索引时再盲目加单列索引可能不仅没有收益还会增加写入成本、占用更多存储空间。本文从几个常见业务场景出发讲清楚单列索引和多列索引的区别以及什么时候该用哪种索引。一、从一个常见查询说起假设有一张订单表CREATETABLEorders(idbigintPRIMARYKEY,user_idbigintNOTNULL,statustextNOTNULL,created_attimestampNOTNULL,amountnumeric(18,2));常见查询可能有-- 查询某个用户的所有订单SELECT*FROMordersWHEREuser_id123;-- 查询某个用户已支付的订单SELECT*FROMordersWHEREuser_id123ANDstatusPAID;-- 查询某个用户已支付的订单按下单时间排序SELECT*FROMordersWHEREuser_id123ANDstatusPAIDORDERBYcreated_atDESC;面对这些查询应该建单列索引还是多列索引要回答这个问题先要理解两者的本质区别。二、单列索引是什么单列索引只包含一个列例如CREATEINDEXidx_orders_user_idONorders(user_id);它按照user_id排序。适合这类查询WHEREuser_id123WHEREuser_idIN(123,456,789)ORDERBYuser_id单列索引的优点是结构简单索引体积相对小对单列等值、范围、排序查询友好写入维护成本相对低。但它也有明显局限如果查询同时过滤多个列单列索引通常只能选其中一个使用或者通过多个单列索引做位图扫描效率不一定理想。三、多列索引是什么多列索引也叫复合索引它包含多个列例如CREATEINDEXidx_orders_user_status_createdONorders(user_id,status,created_at);这个索引不是简单地“同时给三列建索引”而是按照定义的顺序组织数据先按 user_id 排序 user_id 相同再按 status 排序 前两列相同再按 created_at 排序可以把它想象成电话簿先按姓氏排序姓氏相同再按名字排序。这种结构决定了复合索引的一个核心规则最左前缀原则。四、最左前缀原则对于复合索引(a,b,c)它能较好支持以下查询条件WHEREa?WHEREa?ANDb?WHEREa?ANDb?ANDc?WHEREa?ANDb?ANDc?-- c 通常不能有效缩小扫描范围但通常不能单独高效支持WHEREb?WHEREc?WHEREb?ANDc?原因很简单索引是先按a排序的。如果查询条件里没有a数据库很难直接定位到目标数据区域。所以复合索引的列顺序非常关键。一般建议等值过滤条件放前面范围过滤条件放后面排序字段可以放在最后尤其是与等值条件配合时选择性高的列不一定要放最前要结合查询模式综合判断。五、单列索引和多列索引的核心区别对比项单列索引多列索引包含列一列多列排序方式按该列排序按定义顺序逐列排序支持查询单列条件、排序组合条件、排序、覆盖索引最左前缀不涉及必须遵循索引体积通常较小通常较大写入成本较低较高冗余风险可能与复合索引最左列重复列顺序不合理时效果差适用场景高频单列查询固定组合查询、排序分页一句话概括单列索引解决“一列怎么查”的问题多列索引解决“多列怎么组合查、怎么排序”的问题。六、典型场景到底该建哪种索引场景 1只按一列查询SELECT*FROMordersWHEREuser_id123;如果这是最高频查询且没有其他复合索引可用那么可以建CREATEINDEXidx_orders_user_idONorders(user_id);场景 2固定组合条件查询SELECT*FROMordersWHEREuser_id123ANDstatusPAID;更合适的是复合索引CREATEINDEXidx_orders_user_statusONorders(user_id,status);因为数据库可以先定位user_id再在相同user_id内定位status效率通常比只用单列索引更好。场景 3组合条件 排序SELECT*FROMordersWHEREuser_id123ANDstatusPAIDORDERBYcreated_atDESC;可以考虑CREATEINDEXidx_orders_user_status_createdONorders(user_id,status,created_at);这样既能过滤又能利用索引顺序避免额外排序。场景 4已有复合唯一约束最左列查询很多表会有类似唯一约束UNIQUE(user_id,order_no)数据库会自动为它创建复合唯一索引(user_id, order_no)此时如果查询是WHEREuser_id123通常可以直接走这个复合唯一索引因为user_id是它的最左列。也就是说不一定需要再单独给user_id建一个单列索引。这是一个常见误区看到查询条件里只有user_id就立刻想建(user_id)单列索引。实际上如果已有(user_id, order_no)这样的复合索引单列索引很可能只是冗余。七、什么时候才需要额外建单列索引虽然复合索引的最左列可以支持单列查询但并不是所有情况都能完全替代单列索引。以下情况可以考虑额外建单列索引复合索引最左列不是该列例如已有索引(status, user_id)但高频查询是WHERE user_id ?。这时user_id不是最左列复合索引通常帮不上忙。复合索引太大单列索引更小、缓存更友好如果复合索引包含很多列体积很大而单列查询又极其高频单独建一个小索引可能减少 I/O。高频单列查询且现有复合索引选择性不足例如复合索引最左列基数很低单独查询该列时区分度差优化器可能更倾向全表扫描。外键列查询某些业务会频繁按外键列查询而现有复合索引的最左列不是该外键可能需要单独索引。实测证明现有索引不够快最终判断标准不是理论而是执行计划和实际耗时。八、如何验证是否需要新建索引不要凭感觉加索引先用执行计划验证。以 PostgreSQL 为例EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREuser_id123;重点看是否出现Index Scan或Bitmap Index Scan使用了哪个索引actual rows和预估行数差异大不大Buffers显示读了多少数据块是否出现Seq Scan以及表有多大。如果执行计划已经走了已有的复合索引并且性能可接受就不需要再建单列索引。如果发现表很大查询很频繁现有索引没有用上或用了但扫描行数过多再考虑新建索引并继续用EXPLAIN ANALYZE对比优化前后效果。九、生产环境建索引建议避免冗余索引已有(a, b)时再建(a)通常是冗余的除非有明确实测理由。使用CONCURRENTLY创建索引PostgreSQL 中生产环境建议CREATEINDEXCONCURRENTLY idx_nameONtable_name(column_name);避免长时间锁表。关注索引使用情况可以通过pg_stat_user_indexes查看索引扫描次数找出长期未被使用的索引。考虑覆盖索引PostgreSQL 支持INCLUDECREATEINDEXidx_orders_user_status_includeONorders(user_id,status)INCLUDE(created_at,amount);可以减少回表但会增加索引体积。考虑部分索引如果只查询某类状态的数据CREATEINDEXidx_orders_active_userONorders(user_id)WHEREstatusACTIVE;索引更小维护成本更低。索引不是越多越好每个索引都会增加INSERT、UPDATE、DELETE的成本。写入频繁的表尤其要控制索引数量。十、总结单列索引和多列索引不是互相替代的关系而是服务于不同查询模式单列索引适合单列过滤、单列排序多列索引适合固定组合条件、组合排序、覆盖查询复合索引遵循最左前缀原则最左列可以支持单列查询但非最左列通常不能单独高效使用已有复合索引时不要习惯性再建最左列单列索引先看执行计划最终是否建索引要靠EXPLAIN ANALYZE和真实业务查询验证。索引设计的核心不是“多”而是“准”。理解查询模式合理安排列顺序避免冗余索引才能让数据库在读写之间取得更好的平衡。