创建二级索引

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

在开始之前

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

什么是二级索引

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

在已有表中添加二级索引

如果需要对已有表中添加二级索引,可使用 CREATE INDEX 语句。在 TiDB 中,CREATE INDEX 为在线操作,不会阻塞表中的数据读写。二级索引创建一般如以下形式:

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

参数描述

  • {index_name}: 二级索引名。
  • {table_name}: 表名。
  • {column_names}: 将需要索引的列名列表,以半角逗号分隔。

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

如果你希望在创建表的同时,同时创建二级索引,可在 CREATE TABLE 的末尾使用包含 KEY 关键字的子句来创建二级索引:

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

参数描述

  • {index_name}: 二级索引名。
  • {column_names}: 将需要索引的列名列表,以半角逗号分隔。

创建二级索引时应遵守的规则

索引的最佳实践

例子

假设你希望 bookshop 应用程序有 查询某个年份出版的所有书籍 的功能。books 表如下所示:

字段名类型含义
idbigint(20)书籍的唯一标识
titlevarchar(100)书籍名称
typeenum书籍类型(如:杂志、动漫、教辅等)
stockbigint(20)库存
pricedecimal(15,2)价格
published_atdatetime出版时间
  1. CREATE TABLE `bookshop`.`books` (
  2. `id` bigint(20) AUTO_RANDOM NOT NULL,
  3. `title` varchar(100) NOT NULL,
  4. `type` enum('Magazine', 'Novel', 'Life', 'Arts', 'Comics', 'Education & Reference', 'Humanities & Social Sciences', 'Science & Technology', 'Kids', 'Sports') NOT NULL,
  5. `published_at` datetime NOT NULL,
  6. `stock` int(11) DEFAULT '0',
  7. `price` decimal(15,2) DEFAULT '0.0',
  8. PRIMARY KEY (`id`) CLUSTERED
  9. ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

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

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

可以使用 EXPLAIN 进行 SQL 语句的执行计划检查:

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

运行结果为:

  1. +-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------+
  2. | id | estRows | task | access object | operator info |
  3. +-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------+
  4. | TableReader_7 | 346.32 | root | | data:Selection_6 |
  5. | └─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) |
  6. | └─TableFullScan_5 | 20000.00 | cop[tikv] | table:books | keep order:false |
  7. +-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------+
  8. 3 rows in set (0.61 sec)

可以看到返回的计划中,出现了类似 TableFullScan 的字样,这代表 TiDB 准备在这个查询中对 books 表进行全表扫描,这在数据量较大的情况下,几乎是致命的。

books 表增加一个 published_at 列的索引:

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

添加索引后,再次运行 EXPLAIN 语句检查执行计划:

  1. +-------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------+
  2. | id | estRows | task | access object | operator info |
  3. +-------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------+
  4. | IndexLookUp_10 | 146.01 | root | | |
  5. | ├─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 |
  6. | └─TableRowIDScan_9(Probe) | 146.01 | cop[tikv] | table:books | keep order:false |
  7. +-------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------+
  8. 3 rows in set (0.18 sec)

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

创建二级索引 - 图1

注意

上方执行计划中的的 TableFullScanIndexRangeScan 等在 TiDB 内被称为算子。这里对执行计划的解读及算子等不做进一步的展开,若你对此感兴趣,可前往 TiDB 执行计划概览文档查看更多关于执行计划与 TiDB 算子的相关知识。

执行计划并非每次返回使用的算子都相同,这是由于 TiDB 使用的优化方式为 基于代价的优化方式 (CBO),执行计划不仅与规则相关,还和数据分布相关。你可以前往 SQL 性能调优文档查看更多 TiDB SQL 性能的描述。

TiDB 在查询时,还支持显式地使用索引,你可以使用 Optimizer Hints执行计划管理 (SPM) 来人为的控制索引的使用。但如果你不了解它内部发生了什么,请你暂时先不要使用它

可以使用 SHOW INDEXES 语句查询表中的索引:

  1. SHOW INDEXES FROM `bookshop`.`books`;

运行结果为:

  1. +-------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
  2. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | Clustered |
  3. +-------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
  4. | books | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | YES |
  5. | books | 1 | idx_book_published_at | 1 | published_at | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | NO |
  6. +-------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
  7. 2 rows in set (1.63 sec)

更进一步

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