MySQL 索引

数据库索引是提高数据查询性能和检索速度的关键技术之一。本文将介绍 MySQL 索引的基本概念、如何创建索引以及如何使用索引来优化查询。

1.什么是索引？

数据库索引是一种数据结构，用于加速数据库查询操作。它是一个单独的数据结构，存储了特定列的值以及指向包含这些值的数据行的指针。通过使用索引，数据库可以更快速地定位和检索数据，而不必扫描整个表。

2.为什么使用索引？

提高 SELECT 操作性能的最佳方法是在查询中测试的一个或多个列上创建索引。索引条目的作用就像指向表行的指针，允许查询快速确定哪些行与 WHERE 子句中的条件匹配，并检索这些行的其他列值。所有 MySQL 数据类型都可以建立索引。

尽管为查询中可能使用的每个列创建索引可能很诱人，但不必要的索引会浪费空间，并且会浪费 MySQL 确定要使用哪些索引的时间。 索引还会增加插入、更新和删除的成本，因为每个索引都必须更新。 您必须找到适当的平衡点，以使用最佳索引集实现快速查询。

3.创建索引

MySQL InnoDB 自动为主键字段创建一个索引（PRIMARY），这个索引被称为聚集索引（Clustered Index）。

聚集索引包含了表中的数据，也就是说表按照索引的顺序进行组织存储。因此，通过主键进行查找时性能最好。

除此之外，我们可以通过CREATE INDEX语句或者相应的ALTER TABLE ADD INDEX语句创建其他索引，也就是二级索引（Secondary Index）或者非聚集索引（Non-clustered Index）。

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

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

ALTER TABLE table_name ADD {INDEX | KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

UNIQUE（可选）：用于创建唯一索引。唯一索引确保表中的索引列不包含重复的值。

FULLTEXT（可选）：用于创建全文索引。全文索引用于全文搜索，通常用于文本搜索。

SPATIAL（可选）：用于创建空间索引。空间索引用于处理地理空间数据。

index_name：这是要创建的索引的名称。索引名称必须在表内唯一。

index_type（可选）：这是索引的类型。MySQL支持不同类型的索引，如 BTREE 和 HASH。通常情况下，你不需要指定索引类型，MySQL会自动选择适当的类型。

tbl_name：这是要在其上创建索引的表的名称。

(key_part,…)：这是指定要包含在索引中的列或列的列表。你可以在索引中指定一个或多个列。如果指定了多个字段，表示创建多列索引或者复合索引。ASC（默认值）表示索引值按照升序进行存储，DESC 表示索引值按照降序进行存储。

index_option（可选）：这是一组可选的索引选项，用于指定索引的其他属性，如索引的块大小、全文索引的解析器和注释等。

algorithm_option（可选）：用于指定索引创建算法的选项。不同的算法可以影响索引的创建速度和行为。

lock_option（可选）：这是用于指定索引创建时的锁定选项。你可以选择不同的锁定级别来控制索引创建的并发性。

下面是一个创建索引的示例。

CREATE TABLE t_index(
   id INT PRIMARY KEY,
   c1 INT,
   c2 INT,
   c3 VARCHAR(50)
);

CREATE INDEX idx_c1 ON t_index(c1);
1
2
3
4
5
6
7
8

字段 c1 上创建了一个索引，如果使用该字段作为查询条件，MySQL 执行计划如下：

EXPLAIN SELECT * FROM t_index WHERE c1 = 100;
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_index | NULL       | ref  | idx_c1        | idx_c1 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1
2
3
4
5
6

输出结果中的 key = idx_c1 表示通过索引进行查找。

MySQL 允许基于 CHAR、VARCHAR、BINARY 以及 VARBINARY 字段的最前面一部分内容创建索引，同时对于 BLOB 和 TEXT 字段必须指定索引使用的字段长度。这一特性被称为前缀索引（prefix index）。

CREATE INDEX index_name
ON table_name(column_name(length));
1
2

对于 CHAR、VARCHAR 以及 TEXT 字段，length 表示字符数量；对于 BINARY、VARBINARY 以及 BLOB字段，length 表示字节数量。

例如基于 c3 的前 20 个字符创建一个前缀索引。

CREATE INDEX idx_c3_prefix ON t_index(c3(20));
1

MySQL 8.0 增加了函数索引，也就是基于函数或者表达式的值创建索引。例如：

SELECT *
FROM t_index
WHERE UPPER(c3) = 'ABC';
1
2
3

MySQL 8.0 还增加了不可见索引，不可见索引不会被优化器用于优化查询，但是系统仍然会正常维护索引。

CREATE [UNIQUE] INDEX index_name
ON table_name(col1 [ASC | DESC], ...)
VISIBLE | INVISIBLE;
1
2
3

MySQL 8.0 还增加了不可见索引，不可见索引不会被优化器用于优化查询，但是系统仍然会正常进行索引维护。

CREATE [UNIQUE] INDEX index_name
ON table_name(col1 [ASC | DESC], ...)
VISIBLE | INVISIBLE;
1
2
3

默认选项为 VISIBLE，INVISIBLE 表示不可见索引，主键索引不允许设置为不可见。不可见索引可以用于测试删除索引对性能的影响，但不需要真的删除，避免了再次重新创建索引的消耗。

MySQL 8.0 还增加了降序索引，DESC 选项不再被忽略。降序索引可以用于优化降序排序，尤其是多个字段的排序。例如：

CREATE INDEX idx_c1_c2 ON t_index(c1 ASC, c2 DESC);
1

以上复合索引基于 c1 升序和 c2 降序存储，可以优化以下查询：

SELECT *
FROM t_index
WHERE c1 = 100
ORDER BY c2 DESC;
1
2
3
4

创建表的时候也可以直接创建索引，主键约束和唯一约束自动创建相应的索引。

创建索引是提高数据库查询性能的重要手段之一。通过创建适当的索引，可以加速数据检索操作，但需要谨慎使用，因为不必要的索引可能会导致性能下降。因此，需要根据具体的查询需求和数据模型来决定是否创建索引以及如何创建索引。

4.查看索引

MySQL 提供了SHOW INDEX语句，用于查看索引信息。

SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]
1
2
3
4

可选的 EXTENDED 关键字导致输出包含 MySQL 内部使用的、用户无法访问的隐藏索引的信息。

tbl_name FROM db_name 语法的替代方法是 db_name.tbl_name。这两个语句是等价的：

SHOW INDEX FROM mytable FROM mydb;
SHOW INDEX FROM mydb.mytable;
1
2

可选的 WHERE子句可以用来使用更一般的条件过滤要返回的索引信息。如果不提供WHERE子句，SHOW INDEX将返回指定表的所有索引信息。比如使用 WHERE 子句来查找具有特定名称的索引：

SHOW INDEX FROM t_index WHERE Key_name = 'idx_c1';
1

除了上述示例，你还可以根据其他条件来过滤索引信息，例如索引的类型、列名等。

查看表 t_index 的的所有索引。

SHOW INDEX FROM t_index\G
*************************** 1. row ***************************
        Table: t_index
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: t_index
   Non_unique: 1
     Key_name: idx_c1
 Seq_in_index: 1
  Column_name: c1
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

Table 是表的名称。
Non_unique 表示是否唯一索引，0 表示非唯一索引。
Key_name 是索引名称，主键索引的名称为 PRIMARY。
Seq_in_index 表示字段在索引中的顺序，从 1 开始。SHOW INDEX 对于复合索引会返回多行结果。
Column_name 是字段名称，对于函数索引显示为 NULL。
Collation 表示索引中该字段的存储顺序，A 表示升序，D 表示降序，NULL 表示未排序。
Cardinality 表示索引中唯一值的估计，运行 ANALYZE TABLE 或者 myisamchk -a（MyISAM 表）命令可以更新统计值。
Sub_part 表示前缀索引的长度，普通索引为 NULL。
Packed 表示索引键的打包方式，NULL 表示未打包。
Null 表示索引是否允许空值，YES 表示允许。
Index_type 是索引方法，包括 BTREE、FULLTEXT、HASH、RTREE 等。
Comment 是关于索引的描述信息，例如索引被禁用时显示为 disabled。
Index_comment 是索引的备注信息，通过 COMMENT 属性添加备注。
Visible 表示索引对于优化器的可见性。
Expression 是函数索引的表达式，普通索引显示为 NULL。

5.修改索引

MySQL 通过ALTER TABLE语句修改索引的属性。

ALTER TABLE table_name ALTER INDEX index_name {VISIBLE | INVISIBLE};

ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name;
1
2
3

第一个语句用于修改索引的可见性，第二个语句用于修改索引的名称。

以下语句将索引 idx_c1 设置为不可见：

ALTER TABLE t_index ALTER INDEX idx_c1 INVISIBLE;

EXPLAIN
SELECT *
FROM t_index
WHERE c1 = 100;
+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+------------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_index | NULL       | ref  | idx_c1_c2     | idx_c1_c2 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+------------------+--------+---------+-------+------+----------+-------+
1
2
3
4
5
6
7
8
9
10
11

由于 idx_c1 不可见，优化器选择了前文创建的复合索引 idx_c1_c2。

6.删除索引

MySQL 使用DROP INDEX或者ALTER TABLE DROP INDEX语句删除索引。

DROP INDEX index_name ON table_name [algorithm_option | lock_option] ...

ALTER TABLE table_name DROP INDEX index_name [algorithm_option | lock_option] ...

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
1
2
3
4
5
6
7
8
9

可选的 ALGORITHM 子句用于指定删除索引的算法。默认情况下，MySQL会选择一个适合的算法来删除索引，通常是一种快速的算法。

• DEFAULT：由 MySQL 自动选择最合适的算法。
• INPLACE：尽可能使用就地删除索引的算法，以最小化锁定时间。
• COPY：创建一个新表，然后复制数据并删除旧表的索引。

可选的 LOCK 子句用于指定删除索引时的锁定选项，控制其他会话对表的访问。锁定选项可以影响其他读写的并发性和等待时间。

• DEFAULT：由 MySQL 自动选择适当的锁定级别。
• NONE：不锁定表，允许其他会话并发访问。
• SHARED：获取共享锁，允许其他会话读取表，但不允许写入。
• EXCLUSIVE：获取独占锁，阻止其他会话对表进行读取或写入。

NONE、SHARED 和 EXCLUSIVE 锁定选项通常用于 MyISAM 存储引擎。InnoDB 存储引擎通常会使用行级锁定，而不需要显式指定锁定选项。

ALGORITHM 和 LOCK 在 ALTER TABLE 语句中的含义相同。

我们将 t_index 表上的不可见索引 idx_c1 删除：

DROP INDEX idx_c1 ON t_index;
1

要删除主键，索引名总是 PRIMARY，必须指定为带引号的标识符，因为 PRIMARY 是保留字：

DROP INDEX 'PRIMARY' ON t;
1

---

参考文献

8.3 Optimization and Indexes
13.1.15 CREATE INDEX Statement
13.7.7.22 SHOW INDEX Statement
13.1.9 ALTER TABLE Statement
13.1.27 DROP INDEX Statement