📣

TiDB Cloud Serverless 现已更名为
Starter
!此页面由 AI 自动翻译,英文原文请见
此处。

创建二级索引

本文档介绍了如何使用 SQL 及各种编程语言创建二级索引,并列出了索引创建的规则。以 Bookshop 应用为例,带你了解二级索引的创建步骤。

在开始之前

在创建二级索引之前,请完成以下操作:

什么是二级索引

二级索引是 TiDB 集群中的一个逻辑对象。你可以简单地将其视为 TiDB 用于提升查询性能的一种排序类型的数据。在 TiDB 中,创建二级索引是一个在线操作,不会阻塞对表的任何数据读写操作。对于每个索引,TiDB 会为表中的每一行创建引用,并按所选列进行排序,而不是直接对数据排序。

关于二级索引的更多信息,参见 Secondary Indexes

在 TiDB 中,你可以选择 为现有表添加二级索引,或在 创建新表时同时创建二级索引

为现有表添加二级索引

要为现有表添加二级索引,可以使用 CREATE INDEX 语句,示例如下:

CREATE INDEX {index_name} ON {table_name} ({column_names});

参数说明:

  • {index_name}:二级索引的名称。
  • {table_name}:表名。
  • {column_names}:要索引的列名,多个列名用半角逗号分隔。

在创建新表时同时创建二级索引

在创建表的同时添加二级索引,可以在 CREATE TABLE 语句的末尾添加包含 KEY 关键字的子句:

KEY `{index_name}` (`{column_names}`)

参数说明:

  • {index_name}:二级索引的名称。
  • {column_names}:要索引的列名,多个列名用半角逗号分隔。

二级索引创建规则

请参见 索引最佳实践

示例

假设你希望 bookshop 应用支持 根据某个年份搜索所有出版的书籍

books 表中的字段如下:

字段名类型字段描述
idbigint书籍的唯一 ID
titlevarchar(100)书名
typeenum书的类型(例如,杂志、动画、教学辅助等)
stockbigint库存
pricedecimal(15,2)价格
published_atdatetime出版日期

books 表的创建 SQL 语句如下:

CREATE TABLE `bookshop`.`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;

为了支持按年份搜索的功能,你需要编写 SQL 语句 搜索某个年份出版的所有书籍。以 2022 年为例,SQL 语句如下:

SELECT * FROM `bookshop`.`books` WHERE `published_at` >= '2022-01-01 00:00:00' AND `published_at` < '2023-01-01 00:00:00';

你可以使用 EXPLAIN 语句查看该 SQL 的执行计划。

EXPLAIN SELECT * FROM `bookshop`.`books` WHERE `published_at` >= '2022-01-01 00:00:00' AND `published_at` < '2023-01-01 00:00:00';

以下是执行计划的示例输出:

+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------+ | TableReader_7 | 346.32 | root | | data:Selection_6 | | └─Selection_6 | 346.32 | cop[tikv] | | ge(bookshop.books.published_at, 2022-01-01 00:00:00.000000), lt(bookshop.books.published_at, 2023-01-01 00:00:00.000000) | | └─TableFullScan_5 | 20000.00 | cop[tikv] | table:books | keep order:false | +-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.61 sec)

在输出中,TableFullScan 出现在 id 列,表示 TiDB 准备对 books 表进行全表扫描。但在数据量较大的情况下,全表扫描可能会非常缓慢,带来严重影响。

为了避免这种影响,可以为 published_at 列添加索引,示例如下:

CREATE INDEX `idx_book_published_at` ON `bookshop`.`books` (`bookshop`.`books`.`published_at`);

添加索引后,再次执行 EXPLAIN 语句以查看执行计划。

示例输出如下:

+-------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------+ | IndexLookUp_10 | 146.01 | root | | | | ├─IndexRangeScan_8(Build) | 146.01 | cop[tikv] | table:books, index:idx_book_published_at(published_at) | range:[2022-01-01 00:00:00,2023-01-01 00:00:00), keep order:false | | └─TableRowIDScan_9(Probe) | 146.01 | cop[tikv] | table:books | keep order:false | +-------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------+ 3 rows in set (0.18 sec)

在输出中,显示 IndexRangeScan 而非 TableFullScan,表示 TiDB 已准备好使用索引执行此查询。

执行计划中的 TableFullScanIndexRangeScan 等词是 TiDB 中的 operators。关于执行计划和操作符的更多信息,参见 TiDB 查询执行计划概述

执行计划每次返回的操作符可能不同。这是因为 TiDB 采用的是 Cost-Based Optimization (CBO) 方法,执行计划依赖于规则和数据分布。关于 TiDB SQL 性能的更多信息,参见 SQL 调优概述

你可以使用 SHOW INDEXES 语句查询表上的索引,例如:

SHOW INDEXES FROM `bookshop`.`books`;

示例输出如下:

+-------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | Clustered | +-------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+ | books | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | YES | | books | 1 | idx_book_published_at | 1 | published_at | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | NO | +-------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+

文档内容是否有帮助?