MySQL数据库索引优化指南

数据库性能是网站性能的核心，而索引是数据库性能优化的关键。一个好的索引可以让查询速度提升几十倍甚至上百倍，一个差的索引反而会拖慢整个系统。

今天康哥工作室就给大家深入讲解MySQL数据库索引优化的完整方法。

一、索引是什么

索引就像书的目录，有了目录，找内容就不用一页一页翻，直接根据目录定位到页码。

MySQL索引的本质是一种数据结构，常用的是B+树。B+树是一种平衡多路查找树，特点是：
- 非叶子节点只存索引，不存数据
- 叶子节点存所有数据，并且用链表连接
- 查询效率稳定，都是O(log n)

没有索引时，查询需要全表扫描，一行一行比对，数据量大了非常慢。有了索引，可以直接定位到数据所在位置，速度快很多。

二、索引的类型

1. 按数据结构分
- B+树索引：最常用，适合范围查询、排序
- Hash索引：等值查询快，不支持范围查询和排序
- 全文索引：用于文本搜索（FULLTEXT）
- R树索引：空间数据索引（地理信息）

2. 按字段数量分
- 单列索引：一个字段的索引
- 联合索引（复合索引）：多个字段组合的索引

3. 按约束分
- 主键索引：主键自动创建，唯一且非空
- 唯一索引：值唯一，允许空值
- 普通索引：没有约束，纯粹加速查询
- 外键索引：关联其他表的字段

三、索引设计原则

1. 最左前缀原则
联合索引遵循最左前缀原则。比如有索引(a,b,c)，以下查询能用到索引：
- WHERE a = 1
- WHERE a = 1 AND b = 2
- WHERE a = 1 AND b = 2 AND c = 3

以下查询用不到索引：
- WHERE b = 2（跳过了a）
- WHERE c = 3（跳过了a和b）
- WHERE b = 2 AND c = 3（跳过了a）

2. 选择区分度高的字段
区分度 = 不重复值数量 / 总记录数。区分度越高，索引效果越好。
- 好的字段：用户ID、手机号、订单号
- 差的字段：性别（只有男/女）、状态（只有几个值）

3. 不要在索引字段上用函数
在索引字段上用函数会导致索引失效。
错误：WHERE YEAR(create_time) = 2026
正确：WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01'

4. 避免隐式类型转换
字段是字符串，查询用数字，会导致索引失效。
错误：WHERE phone = 13800138000（phone是varchar）
正确：WHERE phone = '13800138000'

5. 控制索引数量
不是索引越多越好。
- 每个索引都占用存储空间
- 插入、更新、删除时要维护所有索引，降低写性能
- 建议单表索引不超过5个

四、索引优化实战

1. 查看索引
SHOW INDEX FROM table_name;

2. 创建索引
-- 普通索引
CREATE INDEX idx_username ON users(username);

-- 联合索引
CREATE INDEX idx_status_create ON orders(status, create_time);

-- 唯一索引
CREATE UNIQUE INDEX idx_phone ON users(phone);

3. 删除索引
DROP INDEX idx_username ON users;

4. 查看执行计划
EXPLAIN SELECT * FROM users WHERE username = 'test';

重点看这几列：
- type：访问类型，system > const > eq_ref > ref > range > index > ALL，ALL是全表扫描，需要优化
- key：实际使用的索引，NULL表示没用到索引
- rows：扫描的行数，越少越好
- Extra：额外信息，Using filesort、Using temporary需要优化

5. 慢查询排查
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒记录

-- 查看慢查询
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

五、常见索引优化场景

1. 分页查询优化
大偏移量分页很慢：
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

优化方法：用子查询先定位ID
SELECT * FROM orders WHERE id >= (
SELECT id FROM orders ORDER BY id LIMIT 100000, 1
) LIMIT 20;

2. OR查询优化
OR会导致索引失效：
SELECT * FROM users WHERE username = 'a' OR email = 'b';

优化方法：用UNION ALL
SELECT * FROM users WHERE username = 'a'
UNION ALL
SELECT * FROM users WHERE email = 'b';

3. LIKE查询优化
左模糊查询用不到索引：
WHERE name LIKE '%张%' -- 用不到索引
WHERE name LIKE '张%' -- 可以用到索引

需要左模糊时，用全文索引或搜索引擎（Elasticsearch）。

4. 排序优化
ORDER BY的字段如果有索引，可以避免filesort：
-- status和create_time有联合索引，排序快
SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC;

-- 没有索引，需要filesort，慢
SELECT * FROM orders WHERE status = 1 ORDER BY amount DESC;

5. 覆盖索引
查询的字段都在索引里，不需要回表，速度更快。
-- 有联合索引(username, email)
SELECT username, email FROM users WHERE username = 'test';
-- Extra显示Using index，说明用了覆盖索引

六、索引失效的常见原因

1. 在索引字段上使用函数、运算
2. 隐式类型转换
3. LIKE以%开头
4. OR连接的条件有一个没索引
5. 联合索引不满足最左前缀
6. 使用NOT IN、!=、<>
7. 数据量小，优化器认为全表扫描更快

七、数据库配置优化

1. InnoDB缓冲池
innodb_buffer_pool_size = 物理内存的50%~70%

2. 日志缓冲
innodb_log_buffer_size = 16M~64M

3. 连接数
max_connections = 500~1000（根据服务器配置）

4. 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

5. 慢查询日志
slow_query_log = ON
long_query_time = 1

八、索引优化总结

索引优化的核心思路：
1. 用EXPLAIN分析查询，找出慢SQL
2. 在WHERE、JOIN、ORDER BY的字段上加索引
3. 联合索引遵循最左前缀原则
4. 选择区分度高的字段
5. 避免索引失效的写法
6. 控制索引数量，定期清理无用索引
7. 用覆盖索引减少回表
8. 大分页用子查询优化

记住：索引优化不是一次性的，要持续监控慢查询，不断优化。一个好的索引设计，可以让你的网站性能提升一个档次。

康哥工作室在MySQL数据库优化方面有丰富的实战经验，如果你有数据库性能问题，或者需要开发高性能的网站系统，欢迎联系我们咨询。