目录

• 一、什么是索引
• 二、索引的分类
  • 2.1 按数据结构分类
  • 2.2 按物理存储分类
  • 2.3 按字段特性分类
  • 2.4 按字段个数分类
• 三、索引的适用场景
  • 3.1 索引的优缺点
  • 3.2 什么时候适用索引？
  • 3.3 什么时候不需要创建索引？
• 四、索引失效
• 五、索引优化
  • 5.1 前缀索引优化
  • 5.2 覆盖索引优化
  • 5.3 主键索引最好是自增的
  • 5.4 索引`NOT NULL`
  • 5.5 防止索引失效

---

参考：

• 链接1：MySQL详细学习教程 (opens new window)
• 链接2：MySQL数据库 (opens new window)

---

MySQL：索引

---

一、什么是索引

索引的定义是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高效查找，这些数据结构就是索引。

简单来说索引的出现就是为了提高数据的查询效率，就像书的目录一样。在书籍中，用户不必翻阅完整个书就能根据目录迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

---

二、索引的分类

要对这些索引进行分类，要清楚这些索引的使用和实现方式，然后再针对有相同特点的索引归为一类。我们可以按照四个角度来分类索引。

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
• 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

2.1 按数据结构分类

从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

每一种存储引擎支持的索引类型不一定相同，如下表中总结了 MySQL 常见的存储引擎 InnoDB、MyISAM 和 Memory 分别支持的索引类型。

2.2 按物理存储分类

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

• 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
• 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

2.3 按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

# 主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

在创建表时，创建主键索引的方式如下：

CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

# 唯一索引

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

在创建表时，创建唯一索引的方式如下：

CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

建表后，如果要创建唯一索引，可以使用这面这条命令：

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...); 

# 普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

在创建表时，创建普通索引的方式如下：

CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

建表后，如果要创建普通索引，可以使用这面这条命令：

CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...); 

# 前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

在创建表时，创建前缀索引的方式如下：

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
); 

建表后，如果要创建前缀索引，可以使用这面这条命令：

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

2.4 按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

• 建立在单列上的索引称为单列索引，比如主键索引；
• 建立在多列上的索引称为联合索引；

三、索引的适用场景

「什么时候需要 / 不需要创建索引？」

3.1 索引的优缺点

索引最大的好处是降低了磁盘IO成本，提高了查询速度，但是索引也是有缺点的，比如：

• 索引需要占用物理空间，数量越大，占用空间越大；
• 创建索引和维护索引要时间成本，性能良好的索引需要不断的去尝试以找到最优解，且时间成本随数据量的增加而增大；
• 会降低表的增删改的效率，因为每次增删改操作的同时，还动态维护着数据和索引的关系。

所以，索引不是万能钥匙，它也是根据场景来使用的。

3.2 什么时候适用索引？

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于 GROUP BY 、 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为建立索引之后记录都是排序好的（B+Tree）。

3.3 什么时候不需要创建索引？

• WHERE、GROUP BY、ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引会占用物理空间。
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改会频繁的重建索引，这个过程是会影响数据库性能的。

四、索引失效

1. like以通配符开头('%abc')会导致索引失效，违反最左前缀法则【最左前缀法则：如果索引了多列（联合索引）。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）】

2. 在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描

3. 存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = '张三'，会导致name索引失效

4. 尽量使用覆盖索引，不要select *

5. MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。

6. IS NULL、IS NOT NULL无法使用索引，理由同上

7. 字符串不加单引号索引失效

8. 用or连接时会导致索引失效

五、索引优化

几种常见优化索引的方法：

• 前缀索引优化；
• 覆盖索引优化；
• 主键索引最好是自增的；
• 防止索引失效；

5.1 前缀索引优化

前缀索引：使用某个字段中字符串的前几个字符建立的索引。

为什么需要使用前缀来建立索引呢？

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

前缀索引的局限性：

1. order by 无法使用前缀索引；
2. 无法把前缀索引用作覆盖索引；

5.2 覆盖索引优化

覆盖索引：查询的所有字段，从二级索引中都能直接查询得到，而不需要通过聚簇索引查询获得，可以避免回表的操作。

使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

5.3 主键索引最好是自增的

我们在建表的时候，都会默认将主键索引设置为自增的，具体为什么要这样做呢？又什么好处？

InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

5.4 索引NOT NULL

索引最好设置为NOT NULL

第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。

第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，会导致更多的存储空间占用，因为 InnoDB 默认行存储格式COMPACT，会用 1 字节空间存储 NULL 值列表。

5.5 防止索引失效

防止索引失效