创建二级索引

在这个章节当中,将开始介绍如何使用 SQL 来创建二级索引,及创建二级索引时应遵守的规则。将在这个章节中围绕 Bookshop 这个应用程序来对 TiDB 的创建二级索引部分展开介绍。

在开始之前

在阅读本页面之前,你需要准备以下事项:

什么是二级索引

二级索引是集群中的逻辑对象,你可以简单地认为它就是一种对数据的排序,TiDB 使用这种有序性来加速查询。TiDB 的创建二级索引的操作为在线操作,不会阻塞表中的数据读写。TiDB 会创建表中各行的引用,并按选择的列进行排序。而并非对表本身的数据进行排序。可在二级索引中查看更多信息。二级索引可跟随表进行创建,也可在已有的表上进行添加

在已有表中添加二级索引

如果需要对已有表中添加二级索引,可使用 CREATE INDEX 语句。在 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(20)书籍的唯一标识
titlevarchar(100)书籍名称
typeenum书籍类型(如:杂志、动漫、教辅等)
stockbigint(20)库存
pricedecimal(15,2)价格
published_atdatetime出版时间
CREATE TABLE `bookshop`.`books` ( `id` bigint(20) 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(11) DEFAULT '0', `price` decimal(15,2) DEFAULT '0.0', PRIMARY KEY (`id`) CLUSTERED ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

因此,就需要对 查询某个年份出版的所有书籍 的 SQL 进行编写,以 2022 年为例,如下所示:

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 的字样,这代表 TiDB 准备在这个查询中对 books 表进行全表扫描,这在数据量较大的情况下,几乎是致命的。

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)

可以看到执行计划中没有了 TableFullScan 的字样,取而代之的是 IndexRangeScan,这代表已经 TiDB 在进行这个查询时准备使用索引。

可以使用 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 | +-------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+ 2 rows in set (1.63 sec)

更进一步

至此,你已经完成数据库、表及二级索引的创建,接下来,数据库模式已经准备好给你的应用程序提供写入读取的能力了。

文档内容是否有帮助?