创建二级索引

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

在开始之前

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

• 使用 TiDB Cloud (DevTier) 构建 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 表如下所示:

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)

更进一步

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