MySQL索引原理与优化

引言

索引是数据库性能优化的关键。本文深入讲解MySQL索引的原理和优化技巧。

一、索引基础

1.1 什么是索引？

索引是帮助MySQL高效获取数据的数据结构。

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

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

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

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

1.2 B+树结构

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

特点：
1. 所有数据存储在叶子节点
2. 叶子节点之间用链表连接
3. 非叶子节点只存储索引
4. 高度通常为3-4层

二、索引类型

2.1 主键索引（PRIMARY KEY）

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

2.2 唯一索引（UNIQUE）

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

2.3 普通索引（INDEX）

1	CREATE INDEX idx_name ON users(name);

2.4 全文索引（FULLTEXT）

CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 使用全文索引
SELECT * FROM articles WHERE MATCH(content) AGAINST('关键词');

2.5 复合索引

CREATE INDEX idx_name_age_city ON users(name, age, city);

-- 最左前缀原则
-- ✓ WHERE name = 'Alice'
-- ✓ WHERE name = 'Alice' AND age = 25
-- ✓ WHERE name = 'Alice' AND age = 25 AND city = 'Beijing'
-- ✗ WHERE age = 25 (不走索引)
-- ✗ WHERE city = 'Beijing' (不走索引)

三、索引优化

3.1 索引选择性

-- 计算选择性：不重复值数量 / 总数量
SELECT COUNT(DISTINCT column) / COUNT(*) FROM table_name;

-- 选择性越高，索引效果越好

3.2 前缀索引

-- 对于长字符串，可以只索引前缀
CREATE INDEX idx_email_prefix ON users(email(10));

-- 选择合适的前缀长度
SELECT 
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel_15
FROM users;

3.3 覆盖索引

-- 索引包含所有查询字段，无需回表
CREATE INDEX idx_name_age ON users(name, age);

-- 覆盖索引查询
SELECT name, age FROM users WHERE name = 'Alice';

3.4 索引下推（ICP）

-- MySQL 5.6+支持索引条件下推
CREATE INDEX idx_name_age ON users(name, age);

-- 索引下推
SELECT * FROM users WHERE name LIKE 'A%' AND age > 25;
-- 在索引层过滤age条件，减少回表次数

四、索引失效场景

4.1 不走索引的情况

-- 1. 使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2023; -- ✗

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

-- 2. 类型转换
SELECT * FROM users WHERE phone = 13800138000; -- ✗ phone是VARCHAR

SELECT * FROM users WHERE phone = '13800138000'; -- ✓

-- 3. 使用OR
SELECT * FROM users WHERE name = 'Alice' OR age = 25; -- 可能不走索引

SELECT * FROM users WHERE name = 'Alice'
UNION
SELECT * FROM users WHERE age = 25; -- ✓

-- 4. 模糊查询以%开头
SELECT * FROM users WHERE name LIKE '%Alice'; -- ✗

SELECT * FROM users WHERE name LIKE 'Alice%'; -- ✓

-- 5. 不等于操作
SELECT * FROM users WHERE status != 1; -- 可能不走索引

-- 6. IS NULL / IS NOT NULL
SELECT * FROM users WHERE email IS NULL; -- 可能不走索引

-- 7. 复合索引不遵循最左前缀
CREATE INDEX idx_name_age ON users(name, age);
SELECT * FROM users WHERE age = 25; -- ✗

五、EXPLAIN分析

5.1 EXPLAIN输出

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

/* 重要字段：
- type: 访问类型（system > const > eq_ref > ref > range > index > all）
- possible_keys: 可能使用的索引
- key: 实际使用的索引
- key_len: 索引长度
- rows: 扫描行数
- Extra: 额外信息
*/

5.2 type类型

-- const: 主键或唯一索引查询
EXPLAIN SELECT * FROM users WHERE id = 1;

-- ref: 非唯一索引查询
EXPLAIN SELECT * FROM users WHERE name = 'Alice';

-- range: 范围查询
EXPLAIN SELECT * FROM users WHERE age > 25;

-- index: 索引全扫描
EXPLAIN SELECT id FROM users;

-- all: 全表扫描（最差）
EXPLAIN SELECT * FROM users;

六、索引设计最佳实践

6.1 索引设计原则

1. 为查询频繁的列建立索引
2. 为排序和分组的列建立索引
3. 选择区分度高的列
4. 使用短索引（前缀索引）
5. 利用最左前缀原则
6. 避免过多索引（影响写入性能）
7. 定期分析和优化索引

6.2 复合索引设计

-- 设计原则：
-- 1. 区分度高的列在前
-- 2. 频繁查询的列在前
-- 3. 考虑最左前缀原则

-- 示例：查询条件通常是 WHERE city = ? AND age > ? ORDER BY name
CREATE INDEX idx_city_age_name ON users(city, age, name);

6.3 索引监控

-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引大小
SELECT 
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
ORDER BY stat_value DESC;

-- 重建索引
ALTER TABLE users ENGINE=InnoDB;