高级索引：复合索引、覆盖索引、被忽略的索引

掌握最左前缀规则、覆盖索引，以及那些让索引对规划器隐形的陷阱。

11 分钟 · 3 题

复合索引会按声明的顺序，在多个列上对行进行排序。决定性的规则是最左前缀：一个建立在 (a, b) 上的索引可以服务于对 a 的过滤，或者对 a 和 b 的过滤，但绝不能单独服务于对 b 的过滤。因此，列的顺序是一个设计上的选择，而不是细节。请先创建下面的数据集。

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  salary INTEGER NOT NULL
);

INSERT INTO employees
  (id, name, department_id, salary)
VALUES
  (1, 'Ada',    1, 65000),
  (2, 'Grace',  1, 72000),
  (3, 'Alan',   2, 58000),
  (4, 'Edsger', 1, 90000);

让我们在 (department_id, salary) 上创建一个索引。一个以 department_id 开头的过滤会触发 SEARCH。但一个仅针对 salary 的过滤破坏了最左前缀：规划器无法直接跳到正确的行，于是退回到 SCAN。对比这两个计划。

CREATE INDEX idx_dept_salary
  ON employees (department_id, salary);

-- Leftmost prefix present: index used
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE department_id = 1
  AND salary > 60000;
-- SEARCH ... USING INDEX idx_dept_salary

-- Leading column missing: index ignored
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE salary > 60000;
-- SCAN employees

覆盖索引包含了查询所读取的每一个列。这样规划器就能直接从索引给出答案，无需打开表：这正是 USING COVERING INDEX 的含义。这里，索引 (department_id, salary) 覆盖了一个只读取这两列的查询。请记住，id（即 rowid）隐式地存在于每个索引中，所以它也被免费覆盖了。

-- Every column read is in the index
EXPLAIN QUERY PLAN
SELECT department_id, salary
FROM employees
WHERE department_id = 1;
-- SEARCH employees USING COVERING INDEX
-- idx_dept_salary (department_id=?)

知识检测

确认你已牢记本课的重点内容。

1. 有一个建立在 (department_id, salary) 上的复合索引，哪个查询无法使用它？
   • WHERE department_id = 1
   • WHERE department_id = 1 AND salary > 60000
   • WHERE salary > 60000
   • WHERE department_id = 1 AND name = 'Ada'
2. 什么是覆盖索引？
   • 一个包含所有被读取列、从而避免打开表的索引
   • 一个同时覆盖多张表的索引
   • 一个在每次 INSERT 后自动重建的索引
   • 主键上唯一索引的同义词
3. 以下哪个条件会阻止使用 email 上的一个简单索引？
   • WHERE email = 'a@b.co'
   • WHERE email > 'm'
   • WHERE lower(email) = 'a@b.co'
   • WHERE email IN ('a@b.co', 'c@d.co')