MySQL 索引

摘要：另外，只有字段类型为和的字段才能设置全文索引。在中，主索引和辅助索引在结构上没有任何区别，只是主索引要求是唯一的，而辅助索引的可以重复。这个索引的是数据表的主键，因此表数据文件本身就是主索引。

我们知道，MySQL 查询数据是从第一条记录开始依次查找，直到读完整个表或者找到匹配的行。数据库表的数据量越大，MySQL 查询所花费的时间就越多。索引的出现就是为了改善查询性能的。MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。从本质上来讲，索引是一种以空间换时间的技术。因为索引的建立是需要占用磁盘物理空间的。

索引的目的在于提高查询效率，这个过程可以类比查字典：假如我们需要查询 mysql 这个单词，我们通常会先在字母表里找到字母 m，这样我们就定位到了 m 开头的单词在词典的位置，然后根据定位到的位置从下往下找到 y 字母，最后找到剩下的 sql。索引就相当于字典里的字母表。如果不使用字母表，想要找到mysql 这个单词，我们就需要把字典从头开始翻一遍，直到翻到m 开头的单词在词典的位置，这个过程重复而浪费时间。

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。因此，通过使用索引技术，MySQL 可以大大减少存储引擎需要扫描的数据量，把磁盘 IO 的随机读取变成顺序读取，从而加快查询效率。

索引有很多种类型，可以为不同场景提供更好的性能。在 MySQL 中，索引是在存储引擎层而不是在服务层实现的。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。下面介绍几种常见的 MySQL 索引类型：

1. 主键（PRIMARY KEY）

主键是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引，并且一个表只能有一个主键。

2. 普通索引（INDEX）

这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以在修改表结构的时候指定。

MySQL 允许对两个或两个以上的列上创建索引，我们习惯上称之为联合索引（又叫复合索引，或者组合索引）。实际上，单列索引可以看成联合索引元素数为1的特例。

3. 唯一索引（Unique）

唯一索引列的值必须唯一，但允许有空值。如果是联合索引，则列值的组合必须唯一。

4. 全文索引（FULLTEXT）

全文索引是一种特殊类型的索引，通过建立倒排索引，快速匹配文档的方式。它查找的是文本中的关键字，而不是直接比较索引中的值。全文索引跟其它几种索引类型的匹配方式完全不一样。它有许多需要注意的细节，比如停用词、词干和复数、布尔搜索等。另外，只有字段类型为 char、varchar 和 text 的字段才能设置全文索引。

5. 空间索引（SPATIAL）

空间索引用于地理数据存储，无需前缀查询，而是会从所有维度来索引数据。

下表是 MySQL 中常见的存储引擎对索引类型的支持情况：

前面说到，不同存储引擎的索引的工作方式并不一样，这是因为存储引擎底层使用的数据结构及算法不一样。在 MySQL 中，某些存储引擎允许在创建索引时指定索引的数据结构类型，下表是常见存储引擎支持的索引的数据结构类型：

需要注意的是，用户不能为全文索引和空间索引指定数据结构类型：全文索引的实现取决于存储引擎，而空间索引被实现为 RTREE 数据结构。

现在来介绍 MyISAM 和 InnoDB 这两个主要存储引擎索引的具体实现方式。

MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址。下图是 MyISAM 索引的原理图：

这里设表一共有三列，假设我们以 Col1 为主键，则上图是一个 MyISAM 表的主索引（Primary key）示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗 B+Tree，data 域保存数据记录的地址。因此，MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。

MyISAM 的索引方式也叫做“非聚集”的，之所以这么称呼是为了与 InnoDB 的聚集索引区分。

虽然 InnoDB 也使用 B+Tree 作为索引结构，但具体实现方式却与 MyISAM 截然不同。

第一个重大区别是 InnoDB 的数据文件本身就是索引文件。从上文知道，MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。

上图是 InnoDB 主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键（MyISAM 可以没有），如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。例如，下图为定义在 Col3 上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

MySQL 索引查询语法如下：

SHOW INDEX FROM table_name;

MySQL 创建索引语法如下：

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

index_col_name:
    col_name [(length)] [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}

比如，现有一个订单表 orders，对其 userId 列创建索引，SQL 语句如下：

CREATE INDEX order_customerId ON orders (userId);

创建索引时，可以指定多个列创建联合索引，如，为订单表 orders 创建 userId 和 productId 列的索引，SQL 语句如下：

CREATE INDEX order_customerId_productId ON orders (userId，productId);

MySQL 删除索引语法如下：

DROP INDEX index_name ON table_name;

或：

ALTER TABLE table_name DROP INDEX index_name;

MySQL 对联合索引的顺序是敏感的。例如我们建立了一个 (a,b,c,d) 顺序的索引，假如现在需要执行这样一个 a = 1 and b = 2 and c > 3 and d = 4 WHERE 查询语句，MySQL 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止索引的匹配，因此 d 字段是没有办法使用索引的。

另外，查询条件用到了索引中列的精确匹配，但是中间某个条件未提供，此时也是没有办法使用索引的。例如我们有一个 (a,b,c) 顺序的索引，假如现在需要执行一个 a = 1 and c = 2 的 WHERE 查询，因为 b 未提供，所以查询只用到了索引的第一列，而后面的 c 虽然也在索引中，但是由于 b 不存在而无法和左前缀连接，因此 c 字段也是没有办法使用索引的。

有一点需要注意，在精确匹配（= 和 IN）中索引的顺序可以是任意的，MySQL 的查询优化器会帮用户优化成索引可以识别的形式。比如对于 (a,b,c) 顺序的索引来说，a = 1 and b = 2 and c = 3 和 a = 1 and c = 3 and b = 2、c = 3 and a = 1 and b = 2 等 SQL 语句效果都是一样的。

尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是0.1以上，即平均1条扫描10条记录。

维护索引是需要空间和时间成本的，对于更新非常频繁的字段，例如通过该字段的查询并不是很多，可能几个小时或是更长才会执行一次，但是更新反而比查询更频繁，此时创建索引就失去了意义，反而降低了 MySQL 的性能。

尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。

现在我们知道索引能够极大地提高数据检索效率，大大减少存储引擎需要扫描的数据量，把磁盘 IO 的随机读取变成顺序读取。但是，索引并不是越多越好的，创建索引需要遵循一定的原则，否则会增加 MySQL 写操作的成本和查询优化器的选择时间，反而降低 MySQL 的性能。

以上内容整理自网络，参考文章：

MySQL8.0 参考手册

MySQL索引原理及慢查询优化

MySQL索引背后的数据结构及算法原理

MySQL索引使用－创建、查看、删除MySQL索引