MySQL 索引设计原则

索引设计概述

索引设计是 MySQL 性能优化的核心，好的索引可以大幅提升查询速度，但过多或不合理的索引会影响写入性能和增加存储开销。在实际生产环境中，索引设计需要结合业务需求、查询模式和数据分布进行综合考虑。

索引设计的核心原则

针对查询设计索引

原则：根据实际查询需求设计索引，而不是单纯依据表结构。

示例：

如果查询是 SELECT * FROM users WHERE age > 30 AND city = 'Beijing' ORDER BY created_at DESC;

推荐索引：idx_age_city_created_at (age, city, created_at)

避免：只为 age 创建索引，或顺序错误的索引（如 idx_city_age_created_at）。

版本差异：

MySQL 5.6：索引长度限制为767字节（单字节字符集）
MySQL 5.7：支持3072字节索引长度，引入虚拟列
MySQL 8.0：增强了索引统计信息，优化了索引选择

选择高选择性列

原则：选择性 = 唯一值数量 / 总行数，选择性越高，索引效果越好。

示例：

列名	唯一值	总行数	选择性	适合索引
id	1000000	1000000	1.0	是（主键）
email	999000	1000000	0.999	是
gender	2	1000000	0.000002	否
status	5	1000000	0.000005	否（除非用于分组统计）

计算索引选择性：

sql

-- 计算city列的选择性
SELECT COUNT(DISTINCT city) / COUNT(*) AS selectivity FROM users;

联合索引的最左前缀原则

原则：联合索引中，查询条件需包含最左前缀列，否则索引可能无法被有效使用。

示例：索引 idx_a_b_c (a, b, c)

查询条件	是否使用索引	使用索引的列
a = 1	是	a
a = 1 AND b = 2	是	a, b
a = 1 AND b = 2 AND c = 3	是	a, b, c
b = 2	否	无
a = 1 AND c = 3	是	a（仅 a 列）
b = 2 AND c = 3	否	无

覆盖索引优先

原则：如果查询的所有列都包含在索引中，MySQL 不需要回表查询，性能更高。

示例：

sql

-- 索引：idx_id_name (id, name)
SELECT id, name FROM users WHERE id = 1; -- 使用覆盖索引，Extra: Using index

SELECT id, name, email FROM users WHERE id = 1; -- 需要回表，Extra: NULL

版本差异：

MySQL 5.6：支持覆盖索引，但优化器对覆盖索引的选择不够智能
MySQL 5.7：增强了覆盖索引的优化，支持更多场景
MySQL 8.0：进一步优化了覆盖索引的使用，减少了不必要的回表操作

索引列的顺序

原则：

等值查询列在前，范围查询列在后
选择性高的列在前
排序和分组列在联合索引末尾

示例：

查询：SELECT * FROM orders WHERE status = 'completed' AND created_at > '2023-01-01' ORDER BY amount DESC;

推荐索引：idx_status_created_at_amount (status, created_at, amount)

解析：

status 是等值查询，放在最前面
created_at 是范围查询，放在中间
amount 用于排序，放在最后

避免列函数操作

原则：索引列上不要使用函数，否则会导致索引失效。

错误示例：

sql

SELECT * FROM users WHERE YEAR(created_at) = 2023; -- 索引失效

正确示例：

sql

SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31'; -- 使用索引

替代方案：使用生成列（MySQL 5.7+）

sql

-- 创建生成列
ALTER TABLE users ADD COLUMN created_year INT GENERATED ALWAYS AS (YEAR(created_at)) STORED;

-- 为生成列创建索引
ALTER TABLE users ADD INDEX idx_created_year (created_year);

-- 使用生成列查询
SELECT * FROM users WHERE created_year = 2023; -- 使用索引

避免隐式类型转换

原则：确保查询条件的类型与索引列类型一致。

示例：

sql

-- 索引列 mobile 是 VARCHAR 类型
SELECT * FROM users WHERE mobile = 13800138000; -- 隐式转换，索引失效
SELECT * FROM users WHERE mobile = '13800138000'; -- 正确，使用索引

控制索引数量

原则：

每个表的索引数量建议不超过 5-8 个
考虑索引的维护成本（写入操作需要更新索引）
优先删除不常用的索引

示例：

sql

-- 查看索引使用情况（MySQL 8.0+）
SELECT index_name, table_name, rows_read, rows_changed FROM information_schema.table_statistics;

-- 查看未使用的索引（需要开启 Performance Schema）
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 NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') 
ORDER BY 
    OBJECT_SCHEMA, OBJECT_NAME;

不同场景下的索引设计

单表查询

查询类型	索引设计	示例
精确查询	为查询列创建索引	`SELECT * FROM users WHERE id = 1;` → 主键索引
范围查询	为范围列创建索引	`SELECT * FROM users WHERE age > 30;` → `idx_age (age)`
排序查询	为排序列创建索引	`SELECT * FROM users ORDER BY created_at DESC;` → `idx_created_at (created_at)`
分组查询	为分组列创建索引	`SELECT city, COUNT(*) FROM users GROUP BY city;` → `idx_city (city)`

多表连接

原则：

为连接列创建索引
小表驱动大表（减少外层循环次数）
考虑连接顺序

示例：

sql

SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.city = 'Beijing';

推荐索引：

users 表：idx_city_id (city, id)（覆盖查询条件和连接列）
orders 表：idx_user_id (user_id)（连接列）

版本差异：

MySQL 5.6：支持嵌套循环连接和基于块的嵌套循环连接
MySQL 5.7：引入了哈希连接（仅支持等值连接）
MySQL 8.0：增强了哈希连接，支持更多场景

子查询

原则：

为子查询中的条件列创建索引
考虑使用连接替代子查询（性能更稳定）

示例：

sql

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

推荐索引：

orders 表：idx_amount_user_id (amount, user_id)（覆盖子查询条件和返回列）

优化方案：使用连接替代子查询

sql

SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 1000 GROUP BY u.id;

分页查询

原则：

为排序和分页列创建联合索引
避免大偏移量查询

示例：

sql

SELECT * FROM articles ORDER BY created_at DESC LIMIT 10000, 10; -- 性能差，需要扫描10010行

优化方案：

sql

-- 索引：idx_created_at_id (created_at, id)
SELECT * FROM articles WHERE created_at < (SELECT created_at FROM articles ORDER BY created_at DESC LIMIT 10000, 1) ORDER BY created_at DESC LIMIT 10;

-- 或使用延迟关联
SELECT a.* FROM articles a JOIN (
    SELECT id FROM articles ORDER BY created_at DESC LIMIT 10000, 10
) b ON a.id = b.id ORDER BY a.created_at DESC;

索引设计工具

自动索引建议

MySQL 8.0 索引顾问

sql

-- 启用索引顾问
SET optimizer_switch = 'index_merge=on';

-- 查看索引建议
EXPLAIN SELECT * FROM users WHERE age > 30 AND city = 'Beijing';
SHOW WARNINGS; -- 可能包含索引建议

Percona Toolkit

bash

# 分析慢查询日志，建议索引
pt-index-usage /var/log/mysql/slow.log

# 分析表结构和查询，建议索引
pt-duplicate-key-checker --host=localhost --user=root --password=password

MySQL Enterprise Monitor

提供自动索引建议功能，基于实际查询负载和索引使用情况生成建议。

手动分析

查看查询频率

sql

-- 通过 Performance Schema 查看查询频率
SELECT query_sample_text, COUNT(*) as execution_count
FROM performance_schema.events_statements_summary_by_digest
GROUP BY query_sample_text
ORDER BY execution_count DESC LIMIT 10;

分析查询执行计划

使用 EXPLAIN 或 EXPLAIN ANALYZE（MySQL 8.0.18+）分析查询，确定是否需要添加索引。

sql

-- 基本执行计划分析
EXPLAIN SELECT * FROM users WHERE age > 30 AND city = 'Beijing';

-- 详细执行计划分析（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30 AND city = 'Beijing';

索引设计的常见误区

为所有列创建索引

问题：增加写入开销，占用存储空间，降低查询优化器效率。

解决：只为频繁查询的列创建索引，定期清理未使用的索引。

忽略索引选择性

问题：低选择性列的索引效果差，甚至会降低性能。

解决：计算索引选择性，只为选择性高的列创建索引，对于低选择性列可考虑联合索引。

不考虑联合索引的顺序

问题：索引无法被有效使用，导致全表扫描或索引失效。

解决：根据查询条件的顺序和选择性设计联合索引，遵循最左前缀原则。

忽略覆盖索引

问题：导致回表查询，增加I/O开销，降低查询性能。

解决：设计包含查询所需所有列的联合索引，减少回表操作。

索引列使用函数

问题：索引失效，导致全表扫描。

解决：避免在索引列上使用函数，或使用生成列（MySQL 5.7+）。

索引设计最佳实践

设计流程

收集查询需求：分析业务查询，确定查询模式和频率
选择索引列：根据查询条件、排序和分组选择列
确定索引类型：选择合适的索引类型（B+树、哈希等）
设计索引结构：联合索引的顺序和覆盖范围
验证索引效果：使用 EXPLAIN 分析查询执行计划
监控索引使用：定期检查索引使用率，删除不常用索引

定期维护

sql

-- 查看索引使用率
SELECT 
    t.table_schema,
    t.table_name,
    i.index_name,
    s.rows_selected,
    s.rows_inserted,
    s.rows_updated,
    s.rows_deleted
FROM 
    information_schema.tables t
JOIN 
    information_schema.statistics i ON t.table_schema = i.table_schema AND t.table_name = i.table_name
LEFT JOIN 
    information_schema.table_statistics s ON t.table_schema = s.table_schema AND t.table_name = s.table_name
WHERE 
    t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

-- 重建索引（优化索引碎片）
ALTER TABLE users ENGINE=InnoDB; -- MySQL 5.5+
-- 或
ALTER TABLE users FORCE; -- 所有版本
-- 或
REINDEX TABLE users; -- MySQL 8.0.17+

案例分析

场景：电商网站的订单查询

核心查询：

SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY created_at DESC;
SELECT * FROM orders WHERE order_no = ?;
SELECT * FROM orders WHERE created_at BETWEEN ? AND ? AND status = ?;

索引设计：

idx_user_id_status_created_at (user_id, status, created_at)：优化第一个查询
idx_order_no (order_no)：优化第二个查询（精确查询）
idx_created_at_status (created_at, status)：优化第三个查询（范围+等值）

版本差异考虑：

MySQL 5.6：需要注意索引长度限制，避免超过767字节
MySQL 5.7：可以使用虚拟列和JSON索引（如果需要）
MySQL 8.0：可以利用直方图统计信息，优化索引选择

总结

索引设计是 MySQL 性能优化的关键，需要根据实际查询需求、数据分布和业务场景进行设计。好的索引设计应该：

针对查询需求，避免过度设计
选择高选择性列，提高索引效率
遵循最左前缀原则，优化联合索引
优先使用覆盖索引，减少回表操作
合理控制索引数量，平衡查询和写入性能
定期监控和维护索引，清理未使用的索引

通过遵循这些原则和最佳实践，并结合MySQL不同版本的特性，您可以设计出高效的索引，提升数据库的整体性能。

MySQL 索引设计原则 ​

索引设计概述 ​

索引设计的核心原则 ​

针对查询设计索引 ​

选择高选择性列 ​

联合索引的最左前缀原则 ​

覆盖索引优先 ​

索引列的顺序 ​

避免列函数操作 ​

避免隐式类型转换 ​

控制索引数量 ​

不同场景下的索引设计 ​

单表查询 ​

多表连接 ​

子查询 ​

分页查询 ​

索引设计工具 ​

自动索引建议 ​

MySQL 8.0 索引顾问 ​

Percona Toolkit ​

MySQL Enterprise Monitor ​

手动分析 ​

查看查询频率 ​

分析查询执行计划 ​

索引设计的常见误区 ​

为所有列创建索引 ​

忽略索引选择性 ​

不考虑联合索引的顺序 ​

忽略覆盖索引 ​

索引列使用函数 ​

索引设计最佳实践 ​

设计流程 ​

定期维护 ​

案例分析 ​

总结 ​

MySQL 索引设计原则

索引设计概述

索引设计的核心原则

针对查询设计索引

选择高选择性列

联合索引的最左前缀原则

覆盖索引优先

索引列的顺序

避免列函数操作

避免隐式类型转换

控制索引数量

不同场景下的索引设计

单表查询

多表连接

子查询

分页查询

索引设计工具

自动索引建议

MySQL 8.0 索引顾问

Percona Toolkit

MySQL Enterprise Monitor

手动分析

查看查询频率

分析查询执行计划

索引设计的常见误区

为所有列创建索引

忽略索引选择性

不考虑联合索引的顺序

忽略覆盖索引

索引列使用函数

索引设计最佳实践

设计流程

定期维护

案例分析

总结