联合索引，最左前缀原则，特殊场景案例

原创

wx64422c161e8a1 2025-10-22 13:52:11 ©著作权

文章标签 #索引 #联合索引 #多字段索引 #最左前缀原则字段 文章分类 JavaScript 前端开发

©著作权归作者所有：来自51CTO博客作者wx64422c161e8a1的原创作品，请联系作者获取转载授权，否则将追究法律责任

文章目录

一、联合索引的注意点
二、最左前缀原则
三、常见联合索引命中
四、特殊，且常见使用场景

一、联合索引的注意点

查询条件顺序：尽量按照索引字段顺序编写查询条件。
避免跳过字段：三个字段及以上是，当查询时经常需要跳过左边字段时，考虑建立单独的索引。
覆盖索引：尽量只查询索引包含的字段。
范围查询放后：把等值（=）查询放在范围查询前面。
核心原理：MySQL的联合索引是按照字段顺序构建的B+树，必须从最左字段开始使用，不能跳过任何前面的字段。

二、最左前缀原则

最左前缀原则有两个核心，分别是最左，顺序性。

想要命中索引必须从最左字段开始使用，保持固定的顺序性，不能跳过前面的字段单独使用后面的字段，否则会导致索引失效。

如果遇到范围查询(>、<、between、like) 就会停止匹配。

创建联合索引时,尽可能把查询出现频率高的字段放在前面(左侧)，尽可能多的满足最左原则。

三、常见联合索引命中

假设有一个用户表，存在联合索引, 索引的三个字段及顺序是name, age, sex

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    sex ENUM('男', '女') NOT NULL,
    -- 创建联合索引，字段顺序为 name, age, sex
    INDEX idx_name_age_sex (name, age, sex)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

根据最左前缀原则，下面是一些能命中索引的情况：

WHERE name = '张三' -- 命中name
WHERE name = '张三' AND age = 25 -- 命中name、age
WHERE name = '张三' AND age = 25 AND sex = '男' --完全所有字段，最优的索引使用
WHERE name = '张三' AND sex = '男'  -- 仅仅命中name，由于跳过了age，顺序性中断，sex无法命中
ORDER BY name -- 排序命中name
ORDER BY name, age -- 排序命中name, age
ORDER BY name, age, sex -- 排序命中name, age, sex
WHERE name = '张三' ORDER BY age --条件匹配命中name、排序命中age

创建索引时，字段及顺序是name, age, sex 和 name, sex，age 是不一样的。索引的B+树结构是按照一定的顺序组织的。如果左边的没命中索引，之后的字段也就不会命中了（和打怪升级一样，第一关都没通过，后面的二、三关也就没通过）
顺序性指的联合索引多个字段之间的顺序性，和我们自己编写sql字段的顺序无关.

执行sql时,执行器会优化sql，WHERE name = ‘张三’ AND age = 20 等效于 WHERE age = 20 AND name = ‘张三’

四、特殊，且常见使用场景

sql语句	命中情况	原因
WHERE age = 25	未命中	`由于跳过了name，顺序性中断`，age无法命中；使用ORDER BY时同理
WHERE sex = ‘女’	未命中	由于跳过了name和age ，顺序性中断，sex无法命中；使用ORDER BY时同理
WHERE age = 25 AND sex = ‘男’	未命中	由于跳过了name，顺序性中断，sex无法命中；使用ORDER BY时同理
WHERE name = ‘张三’ AND sex = ‘男’	仅仅命中name	由于跳过了age，顺序性中断，sex无法命中
WHERE name = ‘张三’ AND age > 20	命中name、age	name字段等值查询，age字段范围查询
WHERE name = ‘张三’ AND age > 20 AND sex = ‘男’	命中name、age，未命中sex	name字段等值查询，age字段范围查询，`遇到范围查询(>、<、between、like) 就会停止匹配`
WHERE name = ‘张三’ AND age BETWEEN 18 AND 30	命中name、age	name字段等值查询，age字段范围查询
WHERE name = ‘张三’ OR address = ‘北京’	未命中	`address不在索引中，OR条件会导致无法有效使用索引`
WHERE name > ‘张三’	命中name	范围查询可以命中索引
WHERE name > ‘张’ AND age = 25	命中name，未命中age	`范围查询可以命中索引，后续索引将会失效`，`遇到范围查询(>、<、between、like) 就会停止匹配`
WHERE name LIKE ‘%张三%’	不命中name	LIKE模糊也遵循最左原则，左侧是模糊。需要逐个内容匹配，所以不命中索引
WHERE name LIKE ‘%张三’	不命中name	LIKE模糊也遵循最左原则，左侧是模糊，需要逐个内容匹配，所以不命中索引
WHERE name LIKE ‘张三%’	命中name	`LIKE模糊也遵循最左原则，左侧是非模糊，所以命中索引`
WHERE name LIKE ‘张三’	命中name	`LIKE模糊也遵循最左原则，左侧是非模糊，所以命中索引`，等效于“=”符号匹配
WHERE UPPER(name)=‘ZHANGSAN’	未命中	`对索引字段使用函数或表达式会使索引失效`
WHERE WHERE name + ‘三’ = ‘张三’	未命中	对索引字段使用函数或表达式会使索引失效
WHERE name != ‘张三’	可能命中、可能不会命中	不等于条件需要扫描大部分索引，优化器可能选择全表扫描，依赖优化器的自己选择
WHERE name IS NULL	可能命中、可能不会命中	理论上可以使用索引，但通常效果不佳，优化器可能选择其他方式，依赖优化器的自己选择
WHERE name IS NULL	可能命中、可能不会命中	理论上可以使用索引，但通常效果不佳，优化器可能选择其他方式，依赖优化器的自己选择