【 MySQL】 MySQL联合索引最左前缀原则：十种场景彻底搞懂索引生效规则

开篇故事：小明设计了一个（城市+区号+姓名）的电话簿索引，却发现查「深圳的张三」很快，但直接查「张三」却很慢。这就是最左前缀原则的经典体现——就像查字典必须先找首字母，再找第二个字母。

---

一、联合索引的基本原理

核心规则：
当建立(a,b,c)联合索引时，相当于同时创建了3个索引：

1. (a)
2. (a,b)
3. (a,b,c)

类比理解：
把索引看作组合密码锁：(城市, 区号, 楼栋号)
必须按顺序旋转密码盘才能开锁

---

二、10种典型场景示例

场景1：完美匹配最左列

-- 索引：(name, age, city)
SELECT * FROM users WHERE name = '张三'; ✅ 使用索引

原理：用到了索引的最左列name

场景2：跳过最左列

SELECT * FROM users WHERE age = 25; ❌ 不使用索引

原因：就像直接翻电话簿找第3页内容

场景3：中间断档

SELECT * FROM users WHERE name = '李四' AND city = '北京'; ❓

结果：仅使用name列的索引（断档后条件失效）

场景4：范围查询打断

SELECT * FROM users 
WHERE name = '王五' AND age > 20 AND city = '上海'; ❓

分析：city条件无法使用索引（范围查询age导致后续中断）

场景5：包含所有列

SELECT * FROM users 
WHERE name = '赵六' AND age = 30 AND city = '广州'; ✅ 全索引使用

特点：最理想的索引命中情况

场景6：仅排序使用

SELECT * FROM users ORDER BY name, age; ✅ 利用索引排序

优势：避免filesort排序操作

场景7：函数操作破坏

SELECT * FROM users WHERE UPPER(name) = 'LISI'; ❌ 索引失效

教训：对索引列做任何计算都会使索引失效

场景8：Like前缀匹配

SELECT * FROM users WHERE name LIKE '张%'; ✅ 使用索引
SELECT * FROM users WHERE name LIKE '%涛'; ❌ 索引失效

记忆口诀：百分号在前不走索引

场景9：多列顺序不同

-- 索引：(province, city)
SELECT * FROM users WHERE city='杭州' AND province='浙江'; ✅

惊喜：优化器会自动调整顺序匹配索引

场景10：覆盖索引奇迹

-- 索引：(age, salary)
SELECT age, salary FROM users WHERE age > 25; ✅

效果：无需回表查询，性能提升明显

---

三、避坑指南

误区1：越多索引列越好

-- 错误做法
CREATE INDEX idx_all ON users(name,age,city,phone);
-- 正确做法
根据实际查询场景设计索引

误区2：随意调整列顺序

-- 错误索引顺序
(order_date, user_id)
-- 正确顺序（如果主要按user_id查）
(user_id, order_date)

---

四、最佳实践手册

1. 黄金法则：把高频查询条件放在索引最左边
2. 范围查询放在索引列的最后
3. 区分度高的列（如user_id）优先放左边
4. 定期使用EXPLAIN验证索引使用情况

检查工具：

EXPLAIN SELECT * FROM users WHERE name='张三' AND age=25;
-- 查看key_len字段判断实际使用的索引长度

---

五、新特性拓展（MySQL 8.0+）

跳跃索引扫描：

-- 索引：(gender, age)
SELECT * FROM users WHERE age > 20;
-- 8.0+版本可能触发跳跃扫描

注意：该功能有限制条件，不能替代合理设计

---

你的下一步：
1️⃣ 打开数据库查看现有的联合索引
2️⃣ 用EXPLAIN检查常用查询的索引使用情况
3️⃣ 尝试调整一个不合理的联合索引顺序

思考题：如果索引是(a,b,c)，查询条件是a=1 and c=3，这个查询能用到哪些索引列？