索引最佳实践
本文介绍在 TiDB 中创建和使用索引的一些最佳实践。
在开始之前
本节以 bookshop 数据库中的 books
表为例。
CREATE TABLE `books` (
`id` bigint AUTO_RANDOM NOT NULL,
`title` varchar(100) NOT NULL,
`type` enum('Magazine', 'Novel', 'Life', 'Arts', 'Comics', 'Education & Reference', 'Humanities & Social Sciences', 'Science & Technology', 'Kids', 'Sports') NOT NULL,
`published_at` datetime NOT NULL,
`stock` int DEFAULT '0',
`price` decimal(15,2) DEFAULT '0.0',
PRIMARY KEY (`id`) CLUSTERED
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
创建索引的最佳实践
创建包含多个列的组合索引,这是一种优化方法,称为 covering index optimization。Covering index optimization 允许 TiDB 直接在索引上查询数据,有助于提升性能。
避免在不常查询的列上创建辅助索引。一个有用的辅助索引可以加快查询速度,但也要注意它的副作用。每添加一个索引,在插入行时会额外增加 Key-Value。索引越多,写入越慢,占用空间也越大。此外,过多的索引会影响优化器的运行时间,不合适的索引还可能误导优化器。因此,索引越多并不一定意味着性能越好。
根据你的应用场景创建合适的索引。原则上,只在查询中会用到的列上创建索引以提升性能。以下情况适合创建索引:
- 具有高区分度的列可以显著减少过滤的行数。例如,建议在身份证号码上创建索引,但不要在性别上创建。
- 在使用多个条件查询时使用组合索引。注意,具有等值条件的列应放在组合索引的前面。示例:如果经常执行
select * from t where c1 = 10 and c2 = 100 and c3 > 10
,可以考虑创建组合索引Index cidx (c1, c2, c3)
,这样可以通过索引前缀进行条件扫描。
给你的辅助索引起有意义的名字,建议遵循你所在公司或组织的命名规范。如果没有此类规范,可以参考 Index Naming Specification 中的规则。
使用索引的最佳实践
索引的目的是加快查询速度,因此要确保现有的索引确实被某些查询使用。如果某个索引没有被任何查询使用,则该索引没有意义,应将其删除。
使用组合索引时,遵循左前缀规则。
假设你在
title
和published_at
列上创建了新的组合索引:CREATE INDEX title_published_at_idx ON books (title, published_at);下面的查询仍然可以使用该组合索引:
SELECT * FROM books WHERE title = 'database';但以下查询不能使用该组合索引,因为没有指定索引最左前缀的条件:
SELECT * FROM books WHERE published_at = '2018-08-18 21:42:08';在查询中使用索引列作为条件时,不要对其进行计算、函数调用或类型转换,否则会阻止 TiDB 优化器使用索引。
假设你在
published_at
时间类型列上创建了索引:CREATE INDEX published_at_idx ON books (published_at);但以下查询无法利用
published_at
索引:SELECT * FROM books WHERE YEAR(published_at)=2022;为了使用
published_at
索引,可以将查询改写为避免在索引列上使用函数的形式:SELECT * FROM books WHERE published_at >= '2022-01-01' AND published_at < '2023-01-01';你也可以使用表达式索引,为
YEAR(published_at)
创建表达式索引:CREATE INDEX published_year_idx ON books ((YEAR(published_at)));这样,执行
SELECT * FROM books WHERE YEAR(published_at)=2022;
时,查询可以利用published_year_idx
索引加快执行速度。尽量使用覆盖索引,即索引中的列包含查询所需的列,避免使用
SELECT *
进行全列查询。下面的查询只需扫描索引
title_published_at_idx
即可获取数据:SELECT title, published_at FROM books WHERE title = 'database';虽然以下查询语句可以利用
(title, published_at)
组合索引,但会额外产生查询非索引列的开销,因为需要 TiDB 根据索引存储的引用(通常是主键信息)查询行数据。SELECT * FROM books WHERE title = 'database';当查询条件中包含
!=
或NOT IN
时,查询无法使用索引。例如,以下查询不能利用任何索引:SELECT * FROM books WHERE title != 'database';当
LIKE
条件以通配符%
开头时,查询也不能使用索引。例如,以下查询不能利用任何索引:SELECT * FROM books WHERE title LIKE '%database';当查询条件存在多个可用索引,并且你知道哪个索引在实际中表现最佳时,建议使用 Optimizer Hint 强制 TiDB 优化器使用该索引,以避免因统计信息不准确或其他问题导致选择了错误的索引。
例如,假设
id_idx
和title_idx
分别在id
和title
列上可用,如果你知道id_idx
更优,可以在 SQL 中使用USE INDEX
提示强制使用id_idx
:SELECT * FROM t USE INDEX(id_idx) WHERE id = 1 and title = 'database';在使用
IN
表达式作为查询条件时,建议匹配的值数量不超过 300,否则执行效率会变差。