索引优化

索引原理

B+Tree 结构

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

                    [根节点]
                   /    |    \
              [非叶节点] [非叶节点] [非叶节点]
              /    |    \
         [叶子节点] [叶子节点] [叶子节点]
            ↓         ↓         ↓
         [数据]     [数据]     [数据]

聚簇索引 vs 非聚簇索引

类型	说明
聚簇索引	数据和索引存储在一起，主键自动创建
非聚簇索引	索引存储主键值，需要回表查询

索引类型

主键索引

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50)
);

唯一索引

CREATE UNIQUE INDEX idx_email ON users(email);
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

普通索引

CREATE INDEX idx_username ON users(username);
ALTER TABLE users ADD INDEX idx_username (username);

复合索引

CREATE INDEX idx_name_age ON users(username, age);

前缀索引

CREATE INDEX idx_email_prefix ON users(email(10));

全文索引

CREATE FULLTEXT INDEX idx_content ON articles(content);

索引设计原则

1. 选择合适的列

• WHERE 条件中的列
• JOIN 连接的列
• ORDER BY / GROUP BY 的列

2. 选择性高的列

SELECT COUNT(DISTINCT column) / COUNT(*) FROM table;

选择性越接近 1，索引效果越好。

3. 最左前缀原则

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

SELECT * FROM users WHERE a = 1;
SELECT * FROM users WHERE a = 1 AND b = 2;
SELECT * FROM users WHERE a = 1 AND b = 2 AND c = 3;

SELECT * FROM users WHERE b = 2;
SELECT * FROM users WHERE b = 2 AND c = 3;

4. 覆盖索引

CREATE INDEX idx_username_email ON users(username, email);

SELECT username, email FROM users WHERE username = '张三';

5. 避免索引失效

SELECT * FROM users WHERE username LIKE '%张';
SELECT * FROM users WHERE YEAR(created_at) = 2023;
SELECT * FROM users WHERE username + '' = '张三';
SELECT * FROM users WHERE username IS NULL;

EXPLAIN 分析

基本使用

EXPLAIN SELECT * FROM users WHERE username = '张三';
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE username = '张三';

输出字段

字段	说明
id	查询标识符
select_type	查询类型
table	表名
partitions	分区
type	访问类型
possible_keys	可能使用的索引
key	实际使用的索引
key_len	索引长度
ref	索引比较的列
rows	预估行数
filtered	过滤百分比
Extra	额外信息

type 类型（从好到差）

类型	说明
system	单行表
const	主键/唯一索引
eq_ref	唯一索引扫描
ref	非唯一索引扫描
range	范围扫描
index	索引扫描
ALL	全表扫描

Extra 信息

信息	说明
Using index	覆盖索引
Using where	WHERE 过滤
Using temporary	使用临时表
Using filesort	文件排序
Using index condition	索引下推

索引优化案例

案例1：避免 SELECT *

SELECT * FROM users WHERE username = '张三';

SELECT id, username, email FROM users WHERE username = '张三';

案例2：使用覆盖索引

CREATE INDEX idx_username_email ON users(username, email);

SELECT username, email FROM users WHERE username = '张三';

案例3：优化 LIKE 查询

SELECT * FROM users WHERE username LIKE '%张%';

SELECT * FROM users WHERE username LIKE '张%';

案例4：避免函数操作

SELECT * FROM users WHERE YEAR(created_at) = 2023;

SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

案例5：优化 OR 条件

SELECT * FROM users WHERE username = '张三' OR email = 'test@example.com';

SELECT * FROM users WHERE username = '张三'
UNION
SELECT * FROM users WHERE email = 'test@example.com';

索引监控

查看索引使用情况

SELECT
    object_schema,
    object_name,
    index_name,
    count_read,
    count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY count_read DESC;

查看未使用的索引

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';

查看索引统计信息

SHOW INDEX FROM users;
ANALYZE TABLE users;

下一步学习

• 事务处理 - 掌握事务处理
• 性能优化 - 学习性能优化