SQL复合索引的最左前缀原则是指在使用复合索引进行查询时，查询条件必须从索引的最左列开始，并且连续匹配索引中的列，才能有效命中索引，发挥索引的加速作用。复合索引本质上是将多个列组合成一个索引键，数据库会按照索引创建时列的顺序对数据进行排序和存储。
最左前缀原则的核心逻辑
假设我们创建了一个包含三列的复合索引idx_a_b_c (a, b, c)，数据库会先按照a列排序，a列相同的情况下再按照b列排序，b列相同的情况下最后按照c列排序。因此查询条件只有匹配了前面的列，才能基于已有的排序快速定位数据。
以下是不同查询条件对应的索引命中情况：
- 查询条件包含a：可以命中索引，因为从最左列开始匹配
- 查询条件包含a和b：可以命中索引，连续匹配了前两列
- 查询条件包含a、b、c：可以命中索引，完整匹配所有列
- 查询条件包含b：无法命中索引，没有从最左列a开始
- 查询条件包含b和c：无法命中索引，跳过了最左列a
- 查询条件包含a和c：只能命中a列的索引部分，c列无法利用索引，因为跳过了b列
实际案例演示
我们先创建一张测试表，并插入部分测试数据：
-- 创建用户表
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age INT,
city VARCHAR(50),
create_time DATETIME
);
-- 创建复合索引，顺序为name、age、city
CREATE INDEX idx_name_age_city ON user_info(name, age, city);
-- 插入测试数据
INSERT INTO user_info (name, age, city, create_time) VALUES
('张三', 20, '北京', '2023-01-01 10:00:00'),
('李四', 25, '上海', '2023-01-02 11:00:00'),
('张三', 30, '广州', '2023-01-03 12:00:00'),
('王五', 20, '北京', '2023-01-04 13:00:00');
命中索引的查询示例
以下查询都会遵循最左前缀原则，成功命中复合索引：
-- 仅使用最左列name查询 EXPLAIN SELECT * FROM user_info WHERE name = '张三'; -- 使用name和age两列查询 EXPLAIN SELECT * FROM user_info WHERE name = '张三' AND age = 20; -- 使用全部三列查询 EXPLAIN SELECT * FROM user_info WHERE name = '张三' AND age = 20 AND city = '北京'; -- 使用name查询并排序，排序字段为age，符合最左前缀 EXPLAIN SELECT * FROM user_info WHERE name = '张三' ORDER BY age;
无法命中索引的查询示例
以下查询没有遵循最左前缀原则，无法有效利用复合索引：
-- 跳过最左列name，直接查询age EXPLAIN SELECT * FROM user_info WHERE age = 20; -- 跳过name，查询age和city EXPLAIN SELECT * FROM user_info WHERE age = 20 AND city = '北京'; -- 跳过中间的age列，查询name和city EXPLAIN SELECT * FROM user_info WHERE name = '张三' AND city = '北京'; -- 查询name后按city排序，跳过了age列 EXPLAIN SELECT * FROM user_info WHERE name = '张三' ORDER BY city;
最左前缀原则的常见误区
条件顺序不影响索引命中
很多开发者认为查询条件中列的顺序需要和索引列顺序一致才能命中，实际上数据库优化器会自动调整条件的顺序，只要条件中包含索引的最左列且连续，就可以命中。比如以下两个查询都能命中索引：
-- 条件顺序和索引顺序一致 SELECT * FROM user_info WHERE name = '张三' AND age = 20; -- 条件顺序和索引顺序不一致，优化器会自动调整 SELECT * FROM user_info WHERE age = 20 AND name = '张三';
范围查询后的列无法使用索引
如果查询条件中对某一列使用了范围查询（比如>、<、LIKE 'xxx%'等），那么该列之后的索引列无法继续使用索引。例如索引是(name, age, city)，如果查询条件是name = '张三' AND age > 20 AND city = '北京'，那么只有name和age列能使用索引，city列无法使用。
-- age是范围查询，city无法使用索引 EXPLAIN SELECT * FROM user_info WHERE name = '张三' AND age > 20 AND city = '北京';
复合索引的创建建议
在创建复合索引时，建议按照以下优先级排列索引列：
- 最左列选择查询频率最高的列，确保大部分查询都能命中最左前缀
- 其次选择区分度高的列，区分度越高，索引的过滤效果越好
- 如果查询中有范围查询条件，尽量把范围查询的列放在索引的最后，避免影响后续列的索引使用
- 不要创建过多冗余的复合索引，比如已经有
(a,b,c)索引，就不需要再创建(a,b)索引，因为前者已经可以覆盖后者的查询场景
总结
最左前缀原则是使用SQL复合索引的核心规则，理解索引的存储逻辑就能快速判断查询是否能命中索引。在实际开发中，创建复合索引前先梳理常用的查询场景，按照合理的列顺序创建索引，同时避免常见的使用误区，才能最大化发挥复合索引的性能优势，提升数据库查询效率。