数据库核心术语知识库

定义

最左前缀原则 (Leftmost Prefix Rule) 是 B+Tree 复合索引的核心使用规则。它规定:对于复合索引 (col1, col2, col3, ...),查询条件必须从索引的最左边列开始匹配,才能有效利用索引进行加速。

简单来说,复合索引就像电话簿一样,先按姓氏排序,再按名字排序。如果你只知道名字而不知道姓氏,就无法快速查找。

详细笔记

核心原理

B+Tree 复合索引的存储结构

sql

CREATE INDEX idx_abc ON users(a, b, c);

索引中的数据按以下顺序排序:

首先按 a 排序
a 相同的情况下,按 b 排序
a 和 b 都相同的情况下,按 c 排序

示例数据:

(a=1, b=1, c=1)
(a=1, b=1, c=2)
(a=1, b=2, c=1)
(a=1, b=2, c=2)
(a=2, b=1, c=1)
(a=2, b=1, c=2)
(a=2, b=2, c=1)
(a=2, b=2, c=2)

可以看到,数据首先按 a 分组,然后在每个 a 组内按 b 排序,最后在每组 (a,b) 内按 c 排序。

查询场景分析

sql

CREATE INDEX idx_name_age_city ON users(name, age, city);

✅ 可以使用索引的场景

sql

-- 场景一:只使用第一列
SELECT * FROM users WHERE name = '张三';
-- 可以使用索引,name 是最左前缀

-- 场景二:使用前两列
SELECT * FROM users WHERE name = '张三' AND age = 25;
-- 可以使用索引,name + age 是最左前缀

-- 场景三:使用全部三列
SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京';
-- 可以使用完整索引

-- 场景四:范围查询(第一列)
SELECT * FROM users WHERE name LIKE '张%';
-- 可以使用索引,范围查询仍然符合最左前缀

-- 场景五:范围查询(第二列),但第一列是等值查询
SELECT * FROM users WHERE name = '张三' AND age > 20;
-- 可以使用索引,name 等值 + age 范围

-- 场景六:ORDER BY 优化
SELECT * FROM users WHERE name = '张三' ORDER BY age;
-- 可以使用索引排序,name 等值过滤,age 有序

-- 场景七:GROUP BY 优化
SELECT name, age, COUNT(*) FROM users 
WHERE name = '张三' 
GROUP BY age;
-- 可以使用索引分组

❌ 不能使用索引的场景

sql

-- 场景一:跳过第一列
SELECT * FROM users WHERE age = 25;
-- 无法使用索引,缺少最左前缀 name

-- 场景二:跳过前两列
SELECT * FROM users WHERE city = '北京';
-- 无法使用索引,缺少最左前缀 name

-- 场景三:跳过中间列
SELECT * FROM users WHERE name = '张三' AND city = '北京';
-- 只能使用到 name 列的索引,city 无法使用
-- 相当于只使用了 (name) 这个前缀

-- 场景四:第一列是范围查询,后续列无法使用
SELECT * FROM users WHERE name > '张' AND age = 25;
-- name 可以使用索引(范围扫描)
-- 但 age 无法使用索引(因为 name 是范围查询,后面的列无序)

形象比喻

电话簿类比:

假设有一个电话簿,按(姓氏, 名字, 年龄)排序:

张 三 25岁  138xxxx
张 三 30岁  139xxxx
张 伟 28岁  137xxxx
李 四 25岁  136xxxx
李 明 30岁  135xxxx

✅ 知道姓氏="张":可以快速定位到"张"的部分
✅ 知道姓氏="张"且名字="三":可以精确定位
❌ 只知道名字="三":无法快速查找,需要遍历整个电话簿
❌ 只知道年龄=25:无法快速查找,需要遍历整个电话簿

EXPLAIN 验证

sql

EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25;

输出:

+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys   | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE  | users | ref  | idx_name_age_city | idx_name_age_city | 157   | const,const |  1 | Using index condition |
+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-----------------------+

关键指标:

key_len: 157:表示使用的索引长度
- name VARCHAR(50):约 152 字节
- age INT:4 字节
- 总计 156-157 字节,说明使用了 name + age 两列

sql

EXPLAIN SELECT * FROM users WHERE age = 25 AND city = '北京';

输出:

+----+-------------+-------+------+---------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys   | key  | key_len | ref  | rows | Extra   |
+----+-------------+-------+------+---------------------+------+---------+------+------+-------------+
|  1 | SIMPLE  | users | ALL  | idx_name_age_city | NULL | NULL  | NULL | 1000 | Using where |
+----+-------------+-------+------+---------------------+------+---------+------+------+-------------+

关键指标:

type: ALL:全表扫描
key: NULL:未使用索引
原因:缺少最左前缀 name

范围查询对最左前缀的影响

重要规则:范围查询后的列无法使用索引

sql

CREATE INDEX idx_abc ON t(a, b, c);

-- 场景一:第一列范围查询
SELECT * FROM t WHERE a > 1 AND b = 2 AND c = 3;
-- a: 可以使用索引(范围扫描)
-- b: 无法使用索引(a 是范围查询,b 在 a 的每个值内部无序)
-- c: 无法使用索引

-- 场景二:第二列范围查询
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3;
-- a: 可以使用索引(等值查询)
-- b: 可以使用索引(范围扫描)
-- c: 无法使用索引(b 是范围查询,c 在 b 的每个值内部无序)

-- 场景三:所有列都是等值查询
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
-- a, b, c: 都可以使用索引

实际示例

sql

CREATE INDEX idx_name_age ON users(name, age);

-- 查询一:name 等值,age 等值
SELECT * FROM users WHERE name = '张三' AND age = 25;
-- key_len: 157 (name + age 都使用了)

-- 查询二:name 等值,age 范围
SELECT * FROM users WHERE name = '张三' AND age > 25;
-- key_len: 157 (name + age 都使用了,age 是范围扫描)

-- 查询三:name 范围,age 等值
SELECT * FROM users WHERE name > '张' AND age = 25;
-- key_len: 152 (只使用了 name,age 无法使用)

设计建议

最佳实践

高频等值查询的列放在前面

sql

-- 场景:name 查询频率高于 age
CREATE INDEX idx_name_age ON users(name, age);

-- 而不是
CREATE INDEX idx_age_name ON users(age, name);

区分度高的列放在前面(但不是绝对规则)

sql

-- 如果 user_id 区分度远高于 status
-- 且两个字段都经常查询
CREATE INDEX idx_user_status ON orders(user_id, status);

将范围查询的列放在后面

sql

-- 如果 create_time 经常范围查询
CREATE INDEX idx_user_time ON orders(user_id, create_time);
-- user_id 等值过滤,create_time 范围扫描

考虑覆盖索引的需求

sql

-- 如果经常查询:SELECT user_id, status FROM orders WHERE user_id = ?
CREATE INDEX idx_user_status ON orders(user_id, status);
-- 形成覆盖索引,无需回表

常见误区

误区一:区分度最高的列一定放最前面

sql

-- 错误观点:user_id 区分度最高,应该放最前面

-- 实际情况:需要考虑查询模式
-- 如果大部分查询是 WHERE status = 1 AND user_id = ?
-- 那么 (status, user_id) 可能更合适

误区二:复合索引可以替代所有单列索引

sql

CREATE INDEX idx_abc ON t(a, b, c);

-- 以下查询无法使用该复合索引:
SELECT * FROM t WHERE b = 1;    -- 缺少最左前缀 a
SELECT * FROM t WHERE c = 1;    -- 缺少最左前缀 a
SELECT * FROM t WHERE b = 1 AND c = 1;  -- 缺少最左前缀 a

-- 如果这些查询也很频繁,可能需要额外的单列索引

索引列顺序的选择策略

策略一:等值查询优先

sql

-- 查询模式
WHERE user_id = ? AND status = ? AND create_time > ?

-- 推荐索引
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
-- user_id: 等值查询,放最前
-- status: 等值查询,放中间
-- create_time: 范围查询,放最后

策略二:考虑排序需求

sql

-- 查询模式
SELECT * FROM orders 
WHERE user_id = ? 
ORDER BY create_time DESC;

-- 推荐索引
CREATE INDEX idx_user_time ON orders(user_id, create_time);
-- 可以利用索引排序,避免 filesort

策略三:考虑分组聚合

sql

-- 查询模式
SELECT user_id, status, COUNT(*) 
FROM orders 
GROUP BY user_id, status;

-- 推荐索引
CREATE INDEX idx_user_status ON orders(user_id, status);
-- 可以利用索引分组,提高性能

实际案例分析

案例一:电商订单系统

sql

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  status TINYINT,
  create_time DATETIME,
  amount DECIMAL(10, 2),
  INDEX idx_user_status_time (user_id, status, create_time)
);

-- 查询一:用户订单列表(分页)
SELECT * FROM orders 
WHERE user_id = 12345 
ORDER BY create_time DESC 
LIMIT 20;
-- ✅ 完美利用索引:user_id 等值 + create_time 排序

-- 查询二:用户待支付订单
SELECT * FROM orders 
WHERE user_id = 12345 AND status = 0 
ORDER BY create_time DESC;
-- ✅ 完美利用索引:user_id + status 等值 + create_time 排序

-- 查询三:某时间段订单统计
SELECT user_id, COUNT(*) 
FROM orders 
WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY user_id;
-- ❌ 无法有效利用索引:create_time 是范围查询,且不是最左前缀
-- 优化方案:如果需要频繁执行此查询,考虑添加 idx_create_time 单列索引

案例二:日志系统

sql

CREATE TABLE logs (
  log_id BIGINT PRIMARY KEY,
  app_id INT,
  log_level TINYINT,
  create_time DATETIME,
  message TEXT,
  INDEX idx_app_level_time (app_id, log_level, create_time)
);

-- 查询一:某应用的错误日志
SELECT * FROM logs 
WHERE app_id = 100 AND log_level = 3 
ORDER BY create_time DESC 
LIMIT 50;
-- ✅ 完美利用索引

-- 查询二:某应用某时间段的日志
SELECT * FROM logs 
WHERE app_id = 100 
  AND create_time BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY create_time DESC;
-- ⚠️ 部分利用索引:app_id 等值,但 log_level 被跳过
-- create_time 可以使用(范围扫描),但无法利用索引排序
-- 优化方案:根据查询频率,考虑添加 idx_app_time (app_id, create_time)

监控与诊断

检查索引使用情况

sql

-- 查看执行计划中的 key_len
EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25;

-- key_len 计算:
-- name VARCHAR(50): 50 * 3 + 2 = 152 字节(UTF8MB3)
-- age INT: 4 字节
-- 如果 key_len = 157,说明使用了 name + age
-- 如果 key_len = 152,说明只使用了 name

Performance Schema 统计

sql

SELECT 
  OBJECT_NAME,
  INDEX_NAME,
  COUNT_READ,
  SUM_TIMER_READ
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_NAME = 'users'
  AND INDEX_NAME = 'idx_name_age_city'
ORDER BY SUM_TIMER_READ DESC;

关联术语

[[覆盖索引]]
[[索引选择性]]
[[索引下推]]
[[回表]]
[[执行计划]]

参考资料

MySQL 官方文档: Multiple-Column Indexes
《高性能 MySQL》第 5 章:创建高性能索引
Use The Index, Luke: The Leftmost Prefix Rule
Percona Blog: MySQL Order By Index Selection

定义 ​

详细笔记 ​

核心原理 ​

B+Tree 复合索引的存储结构 ​

查询场景分析 ​

✅ 可以使用索引的场景 ​

❌ 不能使用索引的场景 ​

形象比喻 ​

EXPLAIN 验证 ​

范围查询对最左前缀的影响 ​

重要规则:范围查询后的列无法使用索引 ​

实际示例 ​

设计建议 ​

最佳实践 ​

常见误区 ​

索引列顺序的选择策略 ​

策略一:等值查询优先 ​

策略二:考虑排序需求 ​

策略三:考虑分组聚合 ​

实际案例分析 ​

案例一:电商订单系统 ​

案例二:日志系统 ​

监控与诊断 ​

检查索引使用情况 ​

Performance Schema 统计 ​

关联术语 ​

参考资料 ​

定义