Skip to main content

第 30 篇 数据库索引

imodouAbout 5 minMySQL

《MySQL 入门教程》第 30 篇 数据库索引

数据库索引（Index）就像书籍后面的关键字索引，按照关键字进行排序，并且提供了指向具体内容的页码。索引可以用于提高数据库的查询性能；但是索引需要占用额外的磁盘空间，修改数据时也需要进行索引的维护。了解并适当利用索引对于数据库的优化至关重要，本篇我们介绍 MySQL 索引的管理。

📝关于 B-树索引的原理以及利用索引优化 SQL 语句的详细介绍和注意事项，可以参考这篇文章。

30.1 创建索引

MySQL 自动为主键字段创建一个索引（PRIMARY），这个索引被称为聚集索引（clustered index）。实际上 MySQL 聚集索引包含了表中的数据，也就是说表按照索引的顺序进行组织存储。因此，通过主键进行查找时的性能最好。

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

CREATE [UNIQUE] INDEX index_name
ON table_name(col1 [ASC | DESC], ...);

ALTER TABLE table_name ADD INDEX index_name (col1 [ASC | DESC], ...);

其中，UNIQUE 表示唯一索引，用于实现唯一约束；ASC（默认值）表示索引值按照升序进行存储，DESC 表示索引值按照降序进行存储；如果指定了多个字段，表示创建多列索引或者复合索引。例如：

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

CREATE INDEX idx_c1 ON t_index(c1);

字段 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|          |ref |idx_c1       |idx_c1|5      |const|   1|   100.0|     |

输出结果中的 key = idx_c1 表示通过索引进行查找，关于 MySQL 执行计划的介绍可以参考这篇文章。

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

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

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

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

以上语句基于 c3 的前 20 个字符创建了一个前缀索引。

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

CREATE INDEX idx_c3_func ON t_index( (upper(c3)) );

以上索引可以用于优化查询条件中的 upper(c3)，例如：

SELECT *
FROM t_index
WHERE upper(c3) = 'ABC';

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

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

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

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

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

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

SELECT *
FROM t_index
WHERE c1 = 100
ORDER BY c2 DESC;

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

30.2 查看索引

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

mysql> 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: 
      Visible: YES
   Expression: NULL
...
*************************** 6. row ***************************
        Table: t_index
   Non_unique: 1
     Key_name: idx_c3_func
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: upper(`c3`)
6 rows in set (0.00 sec)

其中，

  • 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。

30.3 修改索引

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;

第一个语句用于修改索引的可见性，第二个语句用于修改索引的名称。以下语句将索引 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|          |ref |idx_c1_c2    |idx_c1_c2|5      |const|   1|   100.0|     |

由于 idx_c1 不可见，优化器选择了索引 idx_c1_c2。

30.4 删除索引

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

DROP INDEX index_name ON table_name;

ALTER TABLE table_name DROP INDEX index_name;

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

DROP INDEX idx_c1 ON t_index;
Default footer
Copyright © 2024 imodou