第22期：索引设计（组合索引适用场景）

最新推荐文章于 2025-11-18 08:00:00 发布

原创

最新推荐文章于 2025-11-18 08:00:00 发布 · 902 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL 优化 #组合索引 #联合索引

本文详细解析了如何在MySQL中合理利用组合索引来提升SQL查询性能，重点讲解了必备条件、使用场景、索引选择与优化策略，包括单值索引与部分字段索引的区别。通过实例演示，揭示了索引连续性和范围过滤的重要性。

建立在多个列上的索引即组合索引（联合索引），适用在多个列必须一起使用或者是从左到右方向部分连续列一起使用的业务场景。

组合索引和单值索引类似，索引上的每个键值按照一定的大小排序。比如针对三个字段的组合索引有以下组合：

(f1, f2, f3)
(f1, f2, f3 desc)
(f1, f2 desc, f3)
(f1 desc, f2, f3)
(f1 desc, f2 desc, f3 desc)
…

今天讨论的组合索引只基于默认排序方式，也就是 (f1,f2,f3)，等价于 (f1 asc, f2 asc, f3 asc)。

组合索引的语法：

alter table t1 add key idx_multi(f1 [asc/desc],f2 [asc/desc],f3 [asc/desc]) [using btree/using hash]

MySQL 里，组合索引最大支持 16 个列。可以基于 B+ 树，也可以基于哈希，这篇主要讨论基于 B 树，并且索引顺序默认升序，基于 HASH 只有一种用法，就是所有列的都必须等值过滤【仅限于下面 SQL 3】。

使用组合索引的必备条件为：列 f1 必须存在于 SQL 语句过滤条件中！也就是说组合索引的第一个列（最左列）在过滤条件中必须存在，而且最好是等值过滤。

考虑以下 15 条 SQL 语句，分别对表 t1 字段 f1、f2、f3 有不同的组合过滤，并且都包含了列 f1，也就是说满足了组合索引使用的必备条件。

# SQL 1
select * from t1 where f1 = 1;

# SQL 2
select * from t1 where f1 = 1 and f2 = 1;

# SQL 3
select * from t1 where f1 = 1 and f2 = 1 and f3 = 1 ;

# SQL 4
select f1,f2 from t1 where 1 order by f1,f2;

# SQL 5
select f1,f2,f3 from t1 where 1 order by f1,f2,f3;

# SQL 6
select f1,f2,count(*) from t1 group by f1,f2;

# SQL 7
select f1,f2,f3,count(*) from t1 group by f1,f2,f3;

# SQL 8
select * from t1 where f1 = 10 and f2 = 5 and f3 > 10

# SQL 9
select  * from t1 where f1 = 10 and f2 > 5;

# SQL 10
select * from t1 where f1 < 10;

# SQL 11
select * from t1 where f1 < 10 and f2 > 5;

# SQL 12
select * from t1 where f1 < 10 and f2 > 5 and f3 < 10;

# SQL 13
select * from t1 where f1 < 10 and f2 = 5 and f3 < 10;

# SQL 14
select * from t1 where f1 < 10 and f2 = 5 and f3 = 10;

# SQL 15
select * from t1 where f1 = 1 and f3 = 1;

SQL 1、SQL 2、 SQL 3 三条 SQL 分别基于组合索引 idx_multi 过滤后回表；其中 SQL 3 是组合索引中每个字段都能过滤到的最完美查询。来看看 SQL 3的执行计划：

(127.0.0.1:3400)|(ytt)>explain  select * from t1 where f1 = 1 and

最低0.47元/天解锁文章