最左前缀原则

题目描述

给定联合索引 (name, age, email)，以下哪些查询会使用索引？

-- 查询 1
SELECT * FROM users WHERE name = 'Alice';

-- 查询 2
SELECT * FROM users WHERE name = 'Alice' AND age = 18;

-- 查询 3
SELECT * FROM users WHERE age = 18;

-- 查询 4
SELECT * FROM users WHERE name = 'Alice' AND email = 'alice@example.com';

-- 查询 5
SELECT * FROM users WHERE age = 18 AND email = 'alice@example.com';

-- 查询 6
SELECT * FROM users WHERE email = 'alice@example.com';

提示

• 联合索引按顺序存储
• 必须从左到右匹配
• 跳过中间字段会导致索引失效

解法

-- 联合索引 (name, age, email)
-- B+树叶子节点存储：
-- | name | age | email | 主键id |

-- 数据排序规则：
-- 1. 先按 name 排序
-- 2. name 相同时，按 age 排序
-- 3. age 相同时，按 email 排序

联合索引 (name, age, email)：

(name='Alice', age=18, email='a@ex.com') ─┐
(name='Alice', age=18, email='b@ex.com') ─┤
(name='Alice', age=20, email='c@ex.com') ─┤─ name='Alice' 分支
(name='Alice', age=20, email='d@ex.com') ─┘
(name='Bob', age=19, email='e@ex.com')   ─┐
(name='Bob', age=21, email='f@ex.com')   ─┤─ name='Bob' 分支
(name='Bob', age=22, email='g@ex.com')   ─┘
(name='Charlie', age=25, email='h@ex.com') ─ name='Charlie' 分支

SELECT * FROM users WHERE name = 'Alice';

-- 使用索引：name
-- 匹配：name='Alice'
-- 扫描：name='Alice' 的所有记录

SELECT * FROM users WHERE name = 'Alice' AND age = 18;

-- 使用索引：name, age
-- 匹配：name='Alice' AND age=18
-- 扫描：name='Alice' AND age=18 的记录

SELECT * FROM users WHERE age = 18;

-- 不使用索引（全表扫描）
-- 原因：跳过了最左列 name
-- age=18 的数据分散在不同 name 分支中

SELECT * FROM users WHERE name = 'Alice' AND email = 'alice@example.com';

-- 使用索引：name
-- 匹配：name='Alice'
-- 但 email 不能使用索引（跳过了 age）
-- 扫描：name='Alice' 的所有记录，再过滤 email

SELECT * FROM users WHERE age = 18 AND email = 'alice@example.com';

-- 不使用索引（全表扫描）
-- 原因：跳过了最左列 name

SELECT * FROM users WHERE email = 'alice@example.com';

-- 不使用索引（全表扫描）
-- 原因：跳过了 name 和 age

扩展：索引使用场景

1. 范围查询

-- ✅ 使用索引
SELECT * FROM users WHERE name > 'Alice' AND name < 'Bob';

-- ✅ 使用索引（name, age）
SELECT * FROM users 
WHERE name = 'Alice' AND age > 18 AND age < 25;

-- ⚠️ 部分使用索引（name）
-- age > 18 之后的部分不能使用索引
SELECT * FROM users 
WHERE name = 'Alice' AND age > 18 AND email = 'alice@ex.com';

2. 排序

-- ✅ 使用索引（避免 filesort）
SELECT * FROM users WHERE name = 'Alice' ORDER BY age, email;

-- ✅ 使用索引（最左前缀）
SELECT * FROM users WHERE name = 'Alice' ORDER BY age;

-- ❌ 不使用索引（跳过 age）
SELECT * FROM users WHERE name = 'Alice' ORDER BY email;

3. 覆盖索引

-- ✅ 覆盖索引（不回表）
SELECT name, age, email FROM users WHERE name = 'Alice';

-- ✅ 覆盖索引
SELECT age, email FROM users WHERE name = 'Alice';

-- ❌ 需要回表（SELECT *）
SELECT * FROM users WHERE name = 'Alice';

4. 索引下推（ICP）

-- MySQL 5.6+ 支持索引下推
-- 存储引擎层过滤数据

SELECT * FROM users WHERE name = 'Alice' AND age > 18;

-- 无 ICP：
-- 1. 索引查找 name='Alice'
-- 2. 返回所有记录给 Server 层
-- 3. Server 层过滤 age > 18

-- 有 ICP：
-- 1. 索引查找 name='Alice' AND age > 18
-- 2. 只返回符合条件的记录给 Server 层

索引设计原则

1. 区分度高的列放左边

-- ❌ 不合理（gender 区分度低）
CREATE INDEX idx_gender_age ON users(gender, age);

-- ✓ 合理（name 区分度高）
CREATE INDEX idx_name_age ON users(name, age);

2. 经常查询的列放左边

-- ✓ 按查询频率设计
CREATE INDEX idx_user_status_time ON logs(user_id, status, created_at);

3. 考虑排序需求

-- ✓ 支持排序
CREATE INDEX idx_status_time ON orders(status, created_at);

SELECT * FROM orders WHERE status = 1 ORDER BY created_at;

4. 避免冗余索引

-- ❌ 冗余
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_name_age ON users(name, age);

-- ✓ 只保留联合索引
CREATE INDEX idx_name_age ON users(name, age);

索引失效场景