本篇基于InnoDB引擎
官方文档:
InnoDB索引:https://dev.mysql.com/doc/refman/8.0/en/innodb-indexes.html
优化和索引:https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html
在处理大规模数据时,索引的选择和优化对数据库性能至关重要。本文将介绍MySQL 8.0中常用的索引类型以及优化索引的方法,帮助你在面对千万级数据时依然能够高效查询。
索引类型
InnoDB中的实现是B+ tree,适用于全键值、键值范围或键前缀(最左前缀)查找。
- 主键索引(聚簇索引):叶子节点存储的是整行数据,每张表只能有一个聚簇索引,因为数据行只能按一种顺序存储。查询主键时效率最高,因为数据直接在叶子节点上。
- 非主键索引(二级索引):叶子节点存储的是主键的值,使用非主键索引查询时,需要先通过非主键索引找到对应的主键值,然后再通过主键索引查找具体数据,这个过程称为“回表”。
索引优化
- 覆盖索引(Covering Index)
一个查询的所有列都在一个索引中,这样查询可以只通过索引获取数据,而无需访问数据表,减少了I/O次数。
- 最左前缀原则
最左前缀原则(Leftmost Prefixing)指的是复合索引在使用时,必须遵循从最左边开始匹配的原则。
一般来说,将选择性高(能够显著减少结果集)的列放在索引最前列,在大部分场景下会有帮助,但要考虑各种场景的话,考虑如何避免大量随机IO和排序可能更重要。
- 索引下推
而MySQL 5.6 引入了索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
索引的负面影响
- 插入和更新性能下降
索引会增加写操作的成本,因此在需要频繁插入或更新的列上要谨慎添加索引。
- 空间开销
索引占用磁盘空间,过多的索引会导致存储开销增加。
实用技巧:
使用索引可以显著提升数据库查询性能,但需要合理设计和使用。以下是一些实用技巧,帮助你更好地利用索引优化MySQL性能:
1. 索引选择性
- 高选择性列优先:选择性高的列(即不同值较多的列)更适合作为索引,因为它们能显著减少扫描的行数。
- 示例:对于包含数百万用户的表,
email
(唯一性高)比gender
(只有两个值)更适合作为索引。
- 示例:对于包含数百万用户的表,
2. 覆盖索引
- 减少回表次数:覆盖索引可以包含查询中涉及的所有列,避免回表,提高查询效率。
- 示例:
CREATE INDEX idx_user_email_name ON users (email, name); SELECT name FROM users WHERE email = 'example@example.com';
- 示例:
3. 最左前缀原则
- 复合索引使用顺序:复合索引的列顺序应按查询条件最左匹配。
- 示例:
可以用于以下查询:CREATE INDEX idx_user_city_age ON users (city, age);
SELECT * FROM users WHERE city = 'New York'; SELECT * FROM users WHERE city = 'New York' AND age > 25;
- 示例:
4. 避免函数和操作符
- 避免在索引列上使用函数或操作符:这样会导致索引失效。
- 示例:
可以改写为:SELECT * FROM users WHERE YEAR(birth_date) = 1990; -- 会导致索引失效
SELECT * FROM users WHERE birth_date BETWEEN '1990-01-01' AND '1990-12-31';
- 示例:
5. 合理使用前缀索引
- 适用于大文本列:对较长的字符串列,可以使用前缀索引,指定索引长度。
- 示例:
CREATE INDEX idx_user_email_prefix ON users (email(10));
- 示例:
6. 合理使用唯一索引
- 确保数据唯一性:唯一索引不仅能提升查询性能,还能确保数据的唯一性。
- 示例:
CREATE UNIQUE INDEX idx_user_email_unique ON users (email);
- 示例:
7. 避免冗余索引
- 减少不必要的索引:过多的索引会增加写操作的开销和存储空间。
- 示例:如果已有索引
(a, b)
,通常不需要单独再创建索引(a)
。
- 示例:如果已有索引
8. 索引选择顺序
- 查询条件顺序:在复合索引中,将选择性高的列放在前面,范围查询的列放在后面。
- 示例:
CREATE INDEX idx_user_city_age ON users (city, age);
- 示例:
9. 定期维护索引
- 重建和优化索引:随着数据量的增加和变化,定期重建和优化索引可以提升性能。
- 示例:
OPTIMIZE TABLE users;
- 示例:
10. 利用分析工具
- 使用
EXPLAIN
分析查询:通过EXPLAIN
查看查询执行计划,了解索引的使用情况,优化查询。- 示例:
EXPLAIN SELECT * FROM users WHERE city = 'New York' AND age > 25;
- 示例:
11. 避免过多的索引
- 平衡查询和写入性能:索引有助于查询,但会增加写入开销,避免在所有列上创建索引。
- 示例:对于一个表,如果有大量的写操作,选择性创建索引,平衡查询和写入性能。
总结
在索引的创建和选择上,虽然没有放之四海而皆准的经验法则,但以下几个通用原则可以帮助优化查询性能:
- 单行访问尽量减少: 尽量让读取的块中包含尽可能多的所需要的行。
- 顺序访问优先: 按顺序访问范围数据,因为顺序IO比随机IO快很多,且不需要进行额外的排序。
- 索引覆盖查询优先: 尽量使用覆盖索引,避免回表查找行,提高查询速度。
通过合理的索引设计和优化,可以显著提升MySQL在大规模数据环境下的查询性能。根据具体业务需求,灵活应用上述原则和技术,确保数据库在高负载下依然表现优异。