MySQL 中的索引

[wayou]

MySQL 中的索引

索引用来加速查询。正常来说，当查询数据时，MySQL 需要从表的第一条记录开始，读取整个表的内容，进行查询。

但如果有索引，MySQL 可根据索引快速定位需要查询条目的具体位置，加快了查询速度。

原理

索引的原理是将被索引列的值，单独取出来存到另一种结构中以获取快速查询的效果。

当列有这些属性中任意一个时，会被索引， PRIMARY KEY, UNIQUE, INDEX, 以及 FULLTEXT。

大部分索引以 B-trees 结构存储。但有些例外：

• Spatial Data 数据类型，一种基于地理坐标使用数字来标识现实中对象的数据类型，使用 R-trees。
• MEMORY 内存表同时支持使用 hash 索引 。
• InnoDB 引擎中对于全文本索引（FULLTEXT）直接使用列表（lists）结构。

MySQL 使用索引的场景

以下场景将借助或依赖于索引：

• 使用 WHERE 进行条件查询时
• 用于排除一些记录。存在多个索引可选时，MySQL 选择会获取最小记录的那个索引（the most selective index）。
• 存在多列索引的情况下，从左排开始的列的组合都可用于查询时的优化。比如三个索引列 （col1, col2, col3），查询时可以有这些组合的优化查询 (col1), (col1, col2), and (col1, col2, col3)，详见 Section 8.3.6, “Multiple-Column Indexes”。
• 联表查询时。特别地，如果列的类型及大小相同的话，查询时会更加高效。比如 VARCHAR 和 CHAR 会当成同一类型，VARCHAR(10) and CHAR(15) 则不是同一类型，因为长度不同。
• 使用 MIN()，MAX() 函数时。
• 进行排序或分组时。
• 某些情况下，仅仅通过索引就能完成查询操作，无须操作真实的表记录。这种提供了查询中足够信息的索引被称为 covering index。

索引并不是万能的，对于数据量小的表以及对于那些查询全部数据的操作，索引的效果并不明显。相反，对于那些查询时涉及到表中大部分数据的情况下，逐条查询比使用索引要快。

索引的类型

主要有以下四种索引类型，关于创建索引的其他详情可参见 MySQL Manual - 13.1.15 CREATE INDEX Syntax。

• Index Prefixes
• FULLTEXT 索引
• Spatial 索引
• MEMORY Storage Engine 中的索引

Index Prefixes

对于字符串类型的列，在索引创建语法中指定 col_name(N)，可将该列中前 N 个字符进行索引。通过只索引列中前 N 个字符 而非整列，可有效减小索引大小。比如索引 BLOB or TEXT 类型的列：

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

查询时，如果查询项超过了索引长度，索引将用来排除掉那些在索引长度范围内匹配失败的记录，剩下的记录则正常查询。

FULLTEXT 索引

全文本索引用于全文本（full-text）的搜索。只 InnoDB 和 MyISAM 两种引擎下的 CHAR，VARCHAR，TEXT 数据类型支持全文本索引。不像 Index Prefixes，该类型的索引是会对整列的。

Spatial 索引

Spatial Data 数据类型 上创建的索引。

MEMORY Storage Engine 中的索引

 MEMORY 存储引擎默认使用 HASH 索引，但也支持 BTREE 索引。

索引的创建

索引可在创建表时创建，参考 13.1.20 CREATE TABLE Syntax，也可针对已有的表进行创建，使用 CREATE 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}

index_type:
    USING {BTREE | HASH}

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

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

有如下类型的索引指定方式，

• 以列前缀的方式/Column Prefix Key Parts
• 函数形式/Functional Key Parts
• Unique 索引
• Full-Text 索引
• Spatial 索引

以列前缀的方式

可用于创建列中指定前缀部分 col_name(length) 的索引。

示例：

CREATE INDEX part_of_name ON customer (name(10));

以上语句对名为 name 的例索引其前 10 个字符。

函数形式

普通形式的索引只能索引列中的值，比如：

CREATE TABLE t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

以上语句对 col1 整列 及 col2 前 10 个字符进行索引。

但使用函数形式，可创建针对表达式的索引，而不是表中的列。

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

函数形式的索引在定义时需要满足以下的条件，否则抛错：

• 表达式使用括号包裹，以和列名或列的其他前缀属性区别开来。

/* 🚨 */
INDEX (col1 + col2, col3 - col4)

/* ✅ */
INDEX ((col1 + col2), (col3 - col4))

• 函数形式的表达式不能形成一个单独的列名，譬如：

/* 🚨 */
INDEX ((col1), (col2))

/* ✅ */
INDEX (col1, col2)

• 函数中不能引用列前缀（column prefixes）。
• 外键（foreign key）中不使用使用函数形式。

Unique 索引

指定为 UNIQUE 的列约束了列中的值在记录中是唯一的，尝试插入重复值时会抛错。但允许存在多个 NULL 值，如果该列允许为空的话。

如果一个表拥有 PRIMARY KEY 或 UNIQUE NOT NULL 类型的单列整型形成的索引，在 SELECT 语句中可使用 _rowid 关键词来获取索引的列：

• 如果存在一个整型的 PRIMARY KEY 列，_rowid 则指代该列。
• 否则 _rowid 指代第一个 UNIQUE NOT NULL 列。如果不存在一个 UNIQUE NOT NULL 类型的整型列，则不能使用 _rowid。

Full-Text 索引

详细的操作参见 12.9.7 Adding a Collation for Full-Text Indexing。

Spatial 索引

不同存储引擎对其支持情况不一，详见 Spatial Indexes。

索引的查看

通过如下语句查看有哪些索引：

SHOW INDEX FROM table_name;

e.g.:

以 employees 示例数据库中 titles 表为例，先创建索引

mysql> CREATE INDEX part_of_name ON titles (title(10));

查看索引

mysql> show index from titles;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| titles |          0 | PRIMARY      |            1 | emp_no      | A         |      300519 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| titles |          0 | PRIMARY      |            2 | title       | A         |      442783 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| titles |          0 | PRIMARY      |            3 | from_date   | A         |      442783 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| titles |          1 | part_of_name |            1 | title       | A         |           6 |       10 |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

查看索引是否命中

索引创建后，可通过 explain 语句分析查询是否命中索引。

mysql> EXPLAIN SELECT * FROM titles WHERE title = 'Engineer';
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+--------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key          | key_len | ref   | rows   | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+--------+----------+-----------------------+
|  1 | SIMPLE      | titles | NULL       | ref  | part_of_name  | part_of_name | 42      | const | 221391 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+--------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

其中 possible_keys 为可选的索引，key 为真实命中的索引。

相关资源

• MySQL Manual - 8.3.1 How MySQL Uses Indexes
• MySQL Manual - 11.5 Spatial Data Types
• spatial data
• MySQL Manual - 8.3.5 Column Indexes
• MySQL Manual - 13.1.15 CREATE INDEX Syntax