索引与优化 - SQL 数据库

01. 索引是干什么的

数据库索引跟书的目录一个道理——没有目录你得从第一页翻到最后一页找内容，有了目录直接翻到对应页码。索引就是给表的某些列建一个快速查找的数据结构（通常是 B+ 树），让查询不用扫全表。一张表没有索引，查询就要全表扫描。几十万行还行，上千万行就等着吧。建了索引，数据库直接定位到那几行，速度从走遍全城变成 GPS 导航。但索引不是免费的——写操作（INSERT、UPDATE、DELETE）变慢，因为要同时更新索引。索引还占磁盘空间。所以不是列越多索引越好。

sql

-- 给 email 列建索引
CREATE INDEX idx_users_email ON users(email);

-- 建联合索引
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_phone ON users(phone);

建索引的原则：WHERE、JOIN、ORDER BY 里常用的列优先建。单列查询多的建单列索引，多列一起查的建联合索引。

02. B+ 树索引原理

数据库里最常用的索引结构叫 B+ 树——一种自平衡的多叉树。B+ 树的特点是：所有数据都存在叶子节点里，叶子节点之间用链表串起来，非叶子节点只存索引键和指针。这样设计的好处是：查询效率稳定（树的高度一般就 3 到 4 层，几百万数据也就几次磁盘 IO），同时支持范围查询（因为叶子节点有链表，找到起点顺着往下遍历就行）。对比一下哈希索引：查等值快（O(1)），但不支持范围查询和排序。所以 MySQL 默认用 B+ 树，除非你指定用哈希。不过大多数引擎（包括 InnoDB）就是用 B+ 树。

sql

-- 查看查询用没用索引
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- key 列显示用了哪个索引
-- type 列如果是 ALL（全表扫描），说明没走索引
-- rows 列表示预估要扫多少行

EXPLAIN 是优化的第一步——先用它看查询走没走索引、扫了多少行，再决定要不要加索引、改 SQL。

03. 联合索引与最左前缀原则

联合索引就是把多个列绑在一起建一个索引，比如 CREATE INDEX idx_a_b ON t(a, b)。联合索引有个铁律：最左前缀原则——查询条件必须从索引的最左边列开始匹配，不跳过中间列，索引才能生效。举个例子：索引是 (a, b, c)。WHERE a=1 AND b=2 能用索引；WHERE b=2 不能用（跳过了 a）；WHERE a=1 AND c=3 部分能用（只用到 a 列，c 用不到因为跳过了 b）。理解了最左前缀，你建联合索引时就能合理排列字段顺序——把查询频率最高的、区分度最大的放最左边。

sql

-- 建一个覆盖常用查询的联合索引
CREATE INDEX idx_orders_uid_status_date ON orders(user_id, status, created_at);

-- 这些查询都能走索引：
-- WHERE user_id = 1
-- WHERE user_id = 1 AND status = 'paid'
-- WHERE user_id = 1 AND status = 'paid' AND created_at > '2024-01-01'

-- 这个不能走：
-- WHERE status = 'paid'（跳过了 user_id）

联合索引列的顺序按三个原则排：等值查询在前面，范围查询在后面；区分度高的在前面；最常用查询的字段放前面。

04. 覆盖索引与回表

覆盖索引是一个性能优化概念——如果查询需要的所有列都包含在索引里了，数据库就不用回到原表去读数据，直接从索引里拿，少一次磁盘 IO。反之如果你查的列索引里没有（比如只索引了 name 但你要查 name 和 age），数据库先通过索引找到行位置，再回表去读缺少的列——这叫回表。回表多了速度就下来了。所以建索引时如果某个查询跑得特别频繁，考虑把 SELECT 里经常要的列也塞进索引。用 EXPLAIN 的 Extra 列看：Using index 就是覆盖索引，完美。

sql

-- 这个查询需要回表（索引只有 name）
SELECT id, name, age, email FROM users WHERE name = 'Alice';

-- 建覆盖索引，避免回表
CREATE INDEX idx_users_name_cover ON users(name, age, email);

-- 用 EXPLAIN 判断：
-- Using index = 覆盖索引，没回表
-- Using index condition = 用了索引但可能有回表

EXPLAIN 的 Extra 列显示 Using index，就说明走了覆盖索引，没回表。这是查询优化的目标状态。

05. 索引失效的常见情况

建了索引不等于查询一定会用它。以下几种情况索引会失效： 1. 在索引列上做运算或函数——WHERE YEAR(created_at)=2024 用不了索引，因为值被函数包裹了。改成 WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'。 2. 隐式类型转换——WHERE phone=13800000000 如果 phone 是 VARCHAR，数据库会把所有 phone 转成数字去比，索引失效。 3. LIKE 以百分号开头——WHERE name LIKE '%Alice' 用不了索引，因为 B+ 树只能从左往右匹配。LIKE 'Alice%' 可以。 4. OR 两边不是同一个索引列——WHERE a=1 OR b=2，优化器可能放弃索引选择全表扫描。

sql

-- 这些写法索引会失效
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE phone = 13800000000;
SELECT * FROM users WHERE name LIKE '%Alice';

-- 改成这样就对了
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
SELECT * FROM users WHERE phone = '13800000000';
SELECT * FROM users WHERE name LIKE 'Alice%';

函数索引（MySQL 8.0+ 支持）可以解决列上函数的问题，但不是所有数据库版本都有，谨慎使用。

知识测验

第 1/5 题正确 0

联合索引 (a, b, c)，哪个查询能用上索引？

事务与锁