MySQL 索引原理与优化

TIP

索引是加速查询的重要手段。合理的索引可以大幅提升查询性能，但不合理的索引反而会拖慢写入速度。

索引类型

-- 普通索引
CREATE INDEX idx_name ON user(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON user(email);

-- 联合索引（最左前缀原则）
CREATE INDEX idx_name_age ON user(name, age);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON article(content);

-- 删除索引
DROP INDEX idx_name ON user;

B+ 树结构

MySQL InnoDB 使用 B+ 树作为索引结构：

              [根节点]
             /   |   \
         [分支] [分支] [分支]
         /  |    |  \   |  \
      [叶子] [叶子] [叶子] [叶子] [叶子] [叶子]
      数据  数据   数据   数据   数据   数据

特点：
- 非叶子节点只存索引键值
- 叶子节点存完整行数据
- 叶子节点间双向链表连接

最左前缀原则

-- 联合索引 (a, b, c)

-- 走索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 ORDER BY b

-- 不走索引（跳过最左列）
WHERE b = 2            -- 不走
WHERE c = 3            -- 不走
WHERE b = 2 AND c = 3  -- 不走

慢查询分析

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒的记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM user WHERE name = '张三';

EXPLAIN 列解读

列名	说明	好的信号
type	访问类型	const > ref > range > index > ALL
key	使用的索引	不为 NULL
rows	扫描行数	越小越好
Extra	额外信息	Using index（覆盖索引）

索引优化建议

-- 1. 为 WHERE、ORDER BY、GROUP BY 的字段建索引
CREATE INDEX idx_create_time ON order(create_time);

-- 2. 使用覆盖索引（避免回表）
-- 如果查询只需要 name 和 age，建立联合索引
CREATE INDEX idx_name_age ON user(name, age);
-- 则以下查询只需扫描索引，无需回表
SELECT name, age FROM user WHERE name = '张三';

-- 3. 避免索引列上做计算
-- ❌ 不走索引
SELECT * FROM user WHERE age + 1 = 20;
-- ✅ 走索引
SELECT * FROM user WHERE age = 19;

TIP

• 索引不是越多越好，每个索引都会降低写入性能
• 建议单表索引不超过 5 个
• 定期使用 OPTIMIZE TABLE 整理索引碎片