MySQL 创建索引

索引可以提高对表的数据检索速度。在 MySQL 数据库中,您可以使用 CREATE INDEX 语句向表添加索引。

对于一个具有大量数据行的表,如果你根据某个查询条件检索数据时很慢,可能是因为你没有在检索条件相关的列上创建索引。

索引类似于词典中的目录。如果您想要在词典中查询一个词,正确的做法是先查看目录,再根据目录中的指示到指定的页面找到相关的词。正确的索引可以显著提高从数据库表中检索数据行的速度。

MySQL 允许您使用 CREATE INDEX 语句在指定的表上为指定的列创建索引。

索引介绍

索引是一种数据结构,例如 B-Tree,它提高了从表中检索数据行的速度,但需要额外的写入和存储来维护它。

查询优化器可以使用索引来快速定位数据,而不必针对给定查询扫描表中的每一行。

当您使用主键唯一键创建表时,MySQL 会自动创建一个名为 PRIMARY 的索引。 该索引称为聚集索引

PRIMARY 索引是特殊的,因为索引本身与数据一起存储在同一个表中。聚集索引强制执行表中行的顺序。

PRIMARY 索引以外的其他索引称为二级索引或非聚集索引。

MySQL CREATE INDEX 语法

您应该按照如下的语法使用 CREATE INDEX 为一个表添加一个索引:

CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_list)
[algorithm_option | lock_option];

这里,

  • UNIQUE 关键字表明此索引为唯一索引。它是可选的。

  • index_name 是索引的名字。一个表中不应该出现两个相同名字的索引。

  • table_name 是表的名字。

  • column_list 是表中的列名。多个列名使用逗号分隔。

  • USING 子句指定索引的类型。可选值:BTREEHASH。 它是可选的。

  • algorithm_option 指定删除索引的算法。它使用以下的语法:

    ALGORITHM [=] {DEFAULT | INPLACE | COPY}
    

    ALGORITHM 子句是可选的。默认为 INSTANT。如果不支持 INSTANT,则使用 INPLACE

    使用 DEFAULT 和省略 ALGORITHM 子句效果相同。

    以下是对各个算法的说明:

    • COPY:对原表的副本进行操作,将原表中的表数据逐行复制到新表中。不允许并发 DML。
    • INPLACE: 操作避免复制表数据,但可能会就地重建表。在操作的准备和执行阶段,可能会短暂地对表进行独占元数据锁定。通常,支持并发 DML。
    • INSTANT: 操作只修改数据字典中的元数据。在操作的执行阶段,可能会短暂地对表进行独占元数据锁定。表数据不受影响,使操作瞬间完成。允许并发 DML。(在 MySQL 8.0.12 中引入)
  • lock_option 指定删除索引的并发控制策略。它使用以下的语法:

    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
    

    LOCK 子句是可选的。以下是对各个并发策略的说明:

    DEFAULT
    给定 ALGORITHM 子句(如果有)和 ALTER TABLE 操作的最大并发级别:如果支持,则允许并发读取和写入。如果不是,则允许并发读取(如果支持)。如果不是,则强制执行独占访问。
    NONE
    如果支持,允许并发读取和写入。否则,会发生错误。
    SHARED
    如果支持,允许并发读取但阻止写入。即使存储引擎支持给定 ALGORITHM 子句(如果有)和 ALTER TABLE 操作的并发写入,写入也会被阻止。如果不支持并发读取,则会发生错误。
    EXCLUSIVE
    强制执行独占访问。即使存储引擎支持给定 ALGORITHM 子句(如果有)和 ALTER TABLE 操作的并发读/写,也会这样做。

在 MySQL 内部,CREATE INDEX 语句被映射为 ALTER TABLE ... ADD INDEX ... 语句。

MySQL 索引类型

默认情况下,如果您不指定索引类型,MySQL 将创建 B-Tree 索引。下面显示了基于表的存储引擎允许的索引类型:

存储引擎 允许的索引类型
InnoDB BTREE
MyISAM BTREE
MEMORY/HEAP HASH, BTREE

MySQL CREATE INDEX 实例

在以下实例中,我们使用 Sakila 示例数据库中的 actor进行演示。

以下语句查找名字为 NICK 的演员:

SELECT * FROM sakila.actor WHERE first_name = 'NICK';

这是输出:

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
|       44 | NICK       | STALLONE  | 2006-02-15 04:34:33 |
|      166 | NICK       | DEGENERES | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

您可以是使用 EXPLAIN 来查看以上 SELECT 语句的语句的执行计划,以了解 MySQL 在内部如何执行此查询,如下所示:

EXPLAIN SELECT * FROM sakila.actor WHERE first_name = 'NICK';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  201 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

如您所见,MySQL 必须扫描由 201 行组成的整个表才能找到具有符合条件的行。

现在,让我们使用以下 CREATE INDEX 语句为该列 first_name 创建索引 :

CREATE INDEX first_name ON actor(first_name);

这里,我们在 actor 表上为 first_name 列创建了一个索引,索引的名字为 first_name

要查看索引是否创建成功,请使用以下 SHOW INDEXES 语句显示表 actor 的索引,例如:

SHOW INDEXES FROM actor;

这是输出:

+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| actor |          0 | PRIMARY             |            1 | actor_id    | A         |         201 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| actor |          1 | idx_actor_last_name |            1 | last_name   | A         |         122 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| actor |          1 | first_name          |            1 | first_name  | A         |         129 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

然后,再次执行上面的 EXPLAIN 语句:

EXPLAIN SELECT * FROM sakila.actor WHERE first_name = 'NICK';

输出是:

+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ref  | first_name    | first_name | 182     | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

如您所见,MySQL 只需从 first_name 指示的索引中定位其中的 3 行, 而无需扫描整个表。

结论

在 MySQL 中,索引能提高从表中查询数据的效率。您可以使用 CREATE INDEX 为表创建索引。