MySQL 索引优化

索引是数据库优化中最重要的一环。本文将深入探讨 MySQL 索引的原理、类型、失效场景以及优化技巧，帮助你构建高效的数据库查询。

索引基础

什么是索引

索引是帮助 MySQL 高效获取数据的数据结构。就像书的目录一样，通过索引可以快速定位到数据的位置，而不需要扫描整张表。

索引的优势：

大大减少服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随机 I/O 变为顺序 I/O

索引的代价：

占用额外的存储空间
降低写入性能（INSERT、UPDATE、DELETE）
需要维护索引结构

B+树索引原理

MySQL InnoDB 引擎使用 B+树作为索引结构。B+树具有以下特点：

多路平衡查找树：每个节点可以有多个子节点
所有数据存储在叶子节点：非叶子节点只存储索引值
叶子节点通过链表连接：支持范围查询
高度低：一般 3-4 层即可支持千万级数据

B+树查找过程：

从根节点开始，二分查找目标值
根据比较结果，进入相应的子节点
重复步骤 1-2，直到到达叶子节点
在叶子节点中查找目标数据

索引类型

主键索引（Primary Key）

主键索引是特殊的唯一索引，不允许有空值。InnoDB 中主键索引是聚簇索引。

-- 创建表时指定主键
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 为已有表添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

唯一索引（Unique Index）

唯一索引保证索引列的值唯一，但允许有空值（可以有多个空值）。

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建表时指定唯一索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

普通索引（Normal Index）

最基本的索引类型，没有任何限制。

-- 创建普通索引
CREATE INDEX idx_name ON users(name);

-- 创建表时指定索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    INDEX idx_name (name)
);

联合索引（Composite Index）

联合索引是在多个列上创建的索引，遵循最左前缀原则。

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 以下查询可以使用索引
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 25;

-- 以下查询无法使用索引
SELECT * FROM users WHERE age = 25;  -- 违反最左前缀原则

最左前缀原则：

联合索引 (a, b, c) 可以支持：a、ab、abc 的查询
不支持：b、c、bc、ac 等查询
支持范围查询，但范围查询后的列无法使用索引

全文索引（Fulltext Index）

全文索引用于全文搜索，支持对文本内容进行高效的搜索。

-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 使用全文搜索
SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL 索引');

聚簇索引与非聚簇索引

聚簇索引（Clustered Index）

聚簇索引的索引结构和数据存储在一起。InnoDB 中，主键索引就是聚簇索引。

特点：

一张表只能有一个聚簇索引
数据按照索引顺序物理存储
主键查询效率最高
插入速度依赖于主键顺序

非聚簇索引（Secondary Index）

非聚簇索引的索引结构和数据分开存储。InnoDB 中，除主键外的所有索引都是非聚簇索引。

特点：

一张表可以有多个非聚簇索引
索引节点存储的是主键值
查询需要回表：先查索引，再通过主键查数据

-- 假设有以下表结构
CREATE TABLE users (
    id INT PRIMARY KEY,        -- 聚簇索引
    name VARCHAR(100),
    email VARCHAR(100),
    INDEX idx_name (name)      -- 非聚簇索引
);

-- 查询过程
SELECT * FROM users WHERE name = '张三';
-- 1. 通过 idx_name 找到 id
-- 2. 通过 id 回表查询完整数据（回表）

覆盖索引

覆盖索引是指查询的列都包含在索引中，不需要回表查询数据。

-- 假设有联合索引 idx_name_age (name, age)

-- 覆盖索引查询（不需要回表）
SELECT name, age FROM users WHERE name = '张三';

-- 非覆盖索引查询（需要回表）
SELECT * FROM users WHERE name = '张三';

覆盖索引的优势：

避免回表操作，提高查询效率
减少 I/O 操作
可以利用索引的有序性避免排序

索引失效场景

1. 使用函数或表达式

-- 索引失效
SELECT * FROM users WHERE YEAR(create_time) = 2024;

-- 优化后
SELECT * FROM users WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31';

2. 隐式类型转换

-- 假设 phone 是 VARCHAR 类型
-- 索引失效（隐式类型转换）
SELECT * FROM users WHERE phone = 123456;

-- 优化后
SELECT * FROM users WHERE phone = '123456';

3. 使用 OR 连接条件

-- 索引失效
SELECT * FROM users WHERE name = '张三' OR age = 25;

-- 优化后（使用 UNION）
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 25;

4. LIKE 以通配符开头

-- 索引失效
SELECT * FROM users WHERE name LIKE '%张%';

-- 部分失效（只有前缀可以使用索引）
SELECT * FROM users WHERE name LIKE '张%';

-- 优化方案：使用覆盖索引或全文索引
SELECT id, name FROM users WHERE name LIKE '%张%';

5. 使用 NOT、!=、<>

-- 索引失效
SELECT * FROM users WHERE status != 1;

-- 优化后
SELECT * FROM users WHERE status IN (0, 2, 3);

6. 违反最左前缀原则

-- 联合索引 idx_name_age_email (name, age, email)

-- 索引失效
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE email = 'test@example.com';

-- 索引有效
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 25;
SELECT * FROM users WHERE name = '张三' AND age = 25 AND email = 'test@example.com';

7. 范围查询后的列

-- 联合索引 idx_name_age (name, age)

-- 只有 name 使用索引，age 范围查询后的列无法使用索引
SELECT * FROM users WHERE name = '张三' AND age > 20 AND age < 30;

Explain 分析

使用 EXPLAIN 命令可以分析 SQL 语句的执行计划，了解索引的使用情况。

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

关键列说明：

列名	说明
id	查询的标识符
select_type	查询类型（SIMPLE、PRIMARY、SUBQUERY 等）
table	访问的表
type	访问类型（ALL、index、range、ref、eq_ref、const）
possible_keys	可能使用的索引
key	实际使用的索引
key_len	使用的索引长度
rows	预估扫描的行数
Extra	额外信息（Using index、Using filesort、Using temporary）

type 列（从好到坏）：

const：主键或唯一索引查询，最多返回一行
eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录匹配
ref：非唯一索引扫描，返回匹配某个单独值的所有行
range：索引范围扫描
index：索引全扫描
ALL：全表扫描（最差）

Extra 列：

Using index：使用覆盖索引（好）
Using filesort：需要额外排序（坏）
Using temporary：使用临时表（坏）
Using where：使用 WHERE 过滤

索引优化技巧

1. 选择合适的索引列

区分度高的列：选择区分度高的列作为索引（如手机号、身份证）
频繁查询的列：在 WHERE、ORDER BY、GROUP BY 中频繁使用的列
避免过多索引：索引不是越多越好，一般 3-5 个即可

-- 计算列的区分度
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
-- 区分度越接近 1，越适合作为索引

2. 优化联合索引的顺序

将区分度高的列放在前面
将经常单独查询的列放在前面
考虑查询的频率和模式

-- 假设 name 区分度为 0.9，age 区分度为 0.3
-- 创建联合索引时，name 应该放在前面
CREATE INDEX idx_name_age ON users(name, age);

3. 使用覆盖索引

-- 原始查询（需要回表）
SELECT * FROM orders WHERE user_id = 100;

-- 优化后（只查询需要的列）
SELECT id, user_id, amount FROM orders WHERE user_id = 100;

-- 创建覆盖索引
CREATE INDEX idx_user_id_amount ON orders(user_id, amount);

4. 避免 SELECT *

-- 避免
SELECT * FROM users WHERE name = '张三';

-- 推荐
SELECT id, name, email FROM users WHERE name = '张三';

5. 优化 LIKE 查询

-- 避免
SELECT * FROM users WHERE name LIKE '%张%';

-- 优化方案 1：使用覆盖索引
SELECT id, name FROM users WHERE name LIKE '%张%';

-- 优化方案 2：使用全文索引
CREATE FULLTEXT INDEX idx_name ON users(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张');

6. 优化排序

-- 避免 filesort
SELECT * FROM users ORDER BY age;

-- 优化：创建索引
CREATE INDEX idx_age ON users(age);

-- 联合索引优化排序
CREATE INDEX idx_name_age ON users(name, age);
SELECT * FROM users WHERE name = '张三' ORDER BY age;

7. 使用索引提示

-- 强制使用某个索引
SELECT * FROM users USE INDEX(idx_name) WHERE name = '张三';

-- 忽略某个索引
SELECT * FROM users IGNORE INDEX(idx_age) WHERE name = '张三' AND age > 20;

索引维护

索引碎片整理

随着数据的增删改，索引会产生碎片，影响查询性能。

-- 查看索引碎片
SELECT
    table_name,
    index_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND(data_free / 1024 / 1024, 2) AS data_free_mb
FROM information_schema.tables
WHERE table_schema = 'your_database';

-- 重建表（整理碎片）
ALTER TABLE users ENGINE=InnoDB;

-- 优化表
OPTIMIZE TABLE users;

删除无用索引

-- 查看未使用的索引
SELECT
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
AND object_schema = 'your_database';

-- 删除索引
DROP INDEX idx_unused ON users;

实战案例

案例1：优化慢查询

-- 原始慢查询（执行时间 2.5s）
SELECT * FROM orders
WHERE user_id = 100
AND status = 1
AND create_time > '2024-01-01'
ORDER BY create_time DESC
LIMIT 20;

-- Explain 分析
-- type: ALL（全表扫描）
-- Extra: Using filesort, Using where

-- 优化方案：创建联合索引
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

-- 优化后查询（执行时间 0.05s）
SELECT * FROM orders
WHERE user_id = 100
AND status = 1
AND create_time > '2024-01-01'
ORDER BY create_time DESC
LIMIT 20;

-- Explain 分析
-- type: range
-- key: idx_user_status_time
-- Extra: Using where; Using index

案例2：优化分页查询

-- 原始分页查询（深度分页很慢）
SELECT * FROM articles
ORDER BY create_time DESC
LIMIT 100000, 10;

-- 优化方案 1：使用覆盖索引
SELECT id FROM articles
ORDER BY create_time DESC
LIMIT 100000, 10;

SELECT * FROM articles
WHERE id IN (SELECT id FROM articles ORDER BY create_time DESC LIMIT 100000, 10);

-- 优化方案 2：使用游标分页（推荐）
SELECT * FROM articles
WHERE create_time < '2024-03-20 10:00:00'
ORDER BY create_time DESC
LIMIT 10;

案例3：优化 JOIN 查询

-- 原始 JOIN 查询
SELECT *
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 1;

-- 优化：确保 JOIN 字段有索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_id ON users(id);

-- 进一步优化：只查询需要的列
SELECT o.id, o.amount, o.create_time, u.name, u.email
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 1;

最佳实践

不要过度索引：索引越多，写入性能越差
定期分析慢查询：使用 slow_query_log 找出需要优化的查询
监控索引使用情况：定期检查未使用的索引并删除
考虑数据量：小表不需要索引，大表必须要有索引
使用 EXPLAIN：在上线前用 EXPLAIN 分析执行计划
选择合适的主键：使用自增 ID 或 UUID，避免频繁更新主键
定期维护：定期执行 OPTIMIZE TABLE 整理碎片

总结

MySQL 索引优化是一个持续的过程，需要结合实际业务场景和数据特点来制定优化策略。本文介绍了索引的基本原理、类型、失效场景以及优化技巧，希望能够帮助你更好地理解和应用 MySQL 索引。

记住：

索引不是万能的，需要权衡读写性能
使用 EXPLAIN 分析执行计划是优化的基础
覆盖索引是提升查询性能的有效手段
定期维护索引，删除无用索引