一、索引基础概念
1.1 什么是索引
索引是数据库表中一列或多列的值进行排序的一种数据结构，用于快速查询数据库表中的特定记录。它类似于书籍的目录，能极大加快数据检索速度，但会降低写入操作（INSERT、UPDATE、DELETE）的性能。
1.2 索引的工作原理
MySQL索引使用B+树数据结构实现，其特点是：
- 所有数据都存储在叶子节点
- 叶子节点之间形成有序链表
- 非叶子节点作为索引指引
当执行查询时，数据库通过索引的B+树结构快速定位到数据所在的物理位置，避免全表扫描。
1.3 索引的优缺点
优点：
- 大幅提高查询速度
- 加速表连接操作
- 减少分组和排序时间
缺点：
- 占用额外存储空间
- 降低INSERT、UPDATE、DELETE操作性能
- 索引维护需要消耗资源
二、索引类型详解
2.1 主键索引（PRIMARY KEY）
- 表中唯一标识每条记录的索引
- 自动创建，每张表只能有一个主键索引
- 主键字段值不允许为NULL
-- 创建表时定义主键
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(50),
PRIMARY KEY (id)
);
-- 为已有表添加主键
ALTER TABLE users ADD PRIMARY KEY (id);
2.2 唯一索引（UNIQUE）
- 确保索引列的值唯一，但允许为NULL
- 一张表可以有多个唯一索引
-- 创建表时定义唯一索引
CREATE TABLE users (
id INT NOT NULL,
email VARCHAR(100) UNIQUE,
username VARCHAR(50)
);
-- 为已有表添加唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
2.3 普通索引（INDEX）
- 最基本的索引类型，没有唯一性限制
- 可用于加速查询
-- 创建普通索引
CREATE INDEX idx_username ON users(username);
-- 或使用ALTER TABLE
ALTER TABLE users ADD INDEX idx_username (username);
2.4 复合索引（多列索引）
- 由多个列组合而成的索引
- 遵循"最左前缀原则"
-- 创建复合索引
CREATE INDEX idx_name_age ON users(last_name, first_name, age);
2.5 全文索引（FULLTEXT）
- 用于全文搜索，适用于长文本字段
- 仅支持InnoDB和MyISAM存储引擎
- 支持中文需要特殊配置
-- 创建全文索引
CREATE FULLTEXT INDEX idx_article_content ON articles(content);
-- 使用全文索引查询
SELECT * FROM articles
WHERE MATCH(content) AGAINST('数据库 索引' IN BOOLEAN MODE);
2.6 空间索引（SPATIAL）
- 用于地理空间数据类型的索引
- 仅适用于MyISAM存储引擎
CREATE SPATIAL INDEX idx_location ON locations(geo_point);
三、索引设计原则
3.1 选择合适的索引列
- 经常出现在WHERE子句中的列
- 经常用于JOIN的列（外键）
- 经常出现在ORDER BY、GROUP BY中的列
3.2 复合索引设计
- 遵循"最左前缀原则"
- 将选择性高的列放在前面（选择性=不重复值数量/总记录数）
- 控制复合索引的列数（建议不超过3-4列）
3.3 避免过度索引
- 每个索引都会占用存储空间
- 索引会降低写入性能
- 只为必要的查询创建索引
3.4 索引列的特性
- 避免在索引列上使用函数或表达式
- 避免对大字段创建索引（如TEXT、BLOB）
- 对于字符串，可考虑指定索引长度
-- 为字符串指定索引长度
CREATE INDEX idx_title ON articles(title(50));
四、索引失效场景分析
4.1 违反最左前缀原则
-- 复合索引 (a, b, c)
SELECT * FROM table WHERE b = 5; -- 索引失效
SELECT * FROM table WHERE a = 1 AND c = 3; -- 仅a字段使用索引
4.2 使用函数或表达式
SELECT * FROM users WHERE YEAR(register_time) = 2023; -- 索引失效
SELECT * FROM products WHERE price * 1.1 > 100; -- 索引失效
4.3 使用不等于操作符
SELECT * FROM users WHERE status != 1; -- 可能导致索引失效
SELECT * FROM products WHERE price <> 100; -- 可能导致索引失效
4.4 使用OR连接条件
-- 如果OR连接的字段中有一个没有索引，可能导致整个查询不使用索引
SELECT * FROM users WHERE username = 'test' OR age = 25;
4.5 使用LIKE以通配符开头
SELECT * FROM users WHERE username LIKE '%test'; -- 索引失效
SELECT * FROM users WHERE username LIKE 'test%'; -- 索引有效
4.6 隐式类型转换
-- 假设phone是字符串类型
SELECT * FROM users WHERE phone = 13800138000; -- 索引失效，发生类型转换
SELECT * FROM users WHERE phone = '13800138000'; -- 索引有效
4.7 ORDER BY与索引
-- 索引 (a, b)
SELECT * FROM table WHERE a = 1 ORDER BY b; -- 索引有效
SELECT * FROM table WHERE a > 1 ORDER BY b; -- 可能使用filesort
五、索引优化实战技巧
5.1 分析查询性能
使用EXPLAIN命令分析查询执行计划：
EXPLAIN SELECT * FROM users WHERE username = 'john' AND age > 30;
关注EXPLAIN结果中的关键列：
type：访问类型（system > const > eq_ref > ref > range > index > ALL）key：实际使用的索引rows：估计扫描的行数Extra：额外信息（Using index、Using where、Using filesort等）
5.2 优化分页查询
-- 低效的分页查询
SELECT * FROM articles ORDER BY create_time DESC LIMIT 100000, 10;
-- 优化方案1：使用索引覆盖
SELECT a.* FROM articles a
INNER JOIN (SELECT id FROM articles ORDER BY create_time DESC LIMIT 100000, 10) b
ON a.id = b.id;
-- 优化方案2：基于上一页最后一条记录的ID
SELECT * FROM articles
WHERE id < 100000
ORDER BY create_time DESC LIMIT 10;
5.3 优化JOIN查询
-- 确保JOIN字段有索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
ALTER TABLE users ADD PRIMARY KEY (id);
-- 小表驱动大表
SELECT u.name, o.order_no
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;
5.4 使用索引覆盖
当查询的所有字段都包含在索引中时，MySQL可以直接从索引获取数据，无需访问表数据：
-- 创建包含所需字段的复合索引
CREATE INDEX idx_name_age_email ON users(name, age, email);
-- 此查询可以使用索引覆盖，无需访问表数据
SELECT name, age, email FROM users WHERE name = 'john';
5.5 索引维护
定期维护索引以保持性能：
-- 分析表，更新索引统计信息
ANALYZE TABLE users;
-- 优化表，整理索引碎片（InnoDB）
OPTIMIZE TABLE users;
-- 删除无用索引
DROP INDEX idx_unused ON users;
六、特殊场景索引优化
6.1 大表索引策略
- 避免在高峰期创建索引
- 考虑使用pt-online-schema-change等工具在线创建索引
- 批量导入数据时，先删除索引，导入后再重建
6.2 读写分离环境的索引
- 确保主从库索引一致
- 可以在从库添加额外的查询索引，不影响主库写入性能
6.3 历史数据归档与索引
- 对历史数据进行分区或归档
- 为不同时期的数据设计不同的索引策略
七、索引监控与调优
7.1 监控索引使用情况
-- 开启索引使用监控
SET GLOBAL userstat = 1;
-- 查看索引使用统计
SELECT
table_name,
index_name,
rows_read,
rows_found
FROM
information_schema.index_statistics
WHERE
table_name = 'users';
7.2 识别冗余索引
-- 查找冗余索引
SELECT
s.table_schema,
s.table_name,
s.index_name,
s.column_name,
d.index_name AS redundant_index_name,
d.column_name AS redundant_column_name
FROM
statistics s
JOIN
statistics d ON s.table_schema = d.table_schema
AND s.table_name = d.table_name
AND s.index_name != d.index_name
AND s.seq_in_index = d.seq_in_index
AND s.column_name = d.column_name
WHERE
s.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');
7.3 慢查询分析
- 开启慢查询日志：
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
- 使用pt-query-digest分析慢查询日志：
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
八、总结与最佳实践
- 索引不是越多越好：只为常用查询创建必要的索引
- 定期审查索引：移除未使用或冗余的索引
- 关注查询性能变化：使用慢查询日志和执行计划分析
- 索引与业务匹配：根据实际业务查询模式设计索引
- 平衡读写性能：索引提升读性能，但会降低写性能
- 复合索引顺序很重要：遵循最左前缀原则，将选择性高的列放在前面
- 避免索引失效场景：注意查询语句的编写方式
通过合理的索引设计和持续的性能监控，可以显著提升MySQL数据库的查询性能，为应用提供更高效的数据访问支持。
回复