创建二级索引

在这个章节当中,将开始介绍如何使用 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)

更进一步

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

下载 PDF文档反馈社区交流
文档内容是否有帮助?
产品
TiDB
学习
客户案例
PingCAP Education
TiDB in Action
© 2022 PingCAP. All Rights Reserved.