玩转MySQL：如何正确应用索引

引言

数据库索引，绝对是MySQL的核心功能之一，如果没有索引机制的数据库，那数据的检索效率绝对是令人无法接受的，毕竟没有索引的表数据，就如同一个普通的文本文件存储在磁盘中。在《索引上篇》中，我们对于MySQL提供的索引机制，从引入，到创建、使用、分类、管理....等进行了全面阐述，相信经过上一篇的讲解后，大家对MySQL索引机制建立了系统化的认知，而本篇则会以上篇为基础，对索引机制进一步加深掌握。

不过在上篇中虽对数据库索引机制有了完善认知，但还不够，因为上篇仅是单纯的认知阶段，能否真正的在实际项目中运用好索引机制，还需要具备丰富的经验以及一些原则与方法论，比如下述一些关于索引的问题：

• 索引虽然能给MySQL检索数据的效率带来质的飞跃，但加入索引没有带来新问题吗？

• 既然索引能够提升查询性能，那是不是为表中每个字段建立索引，性能会更好？

• 一张数据表中，哪些类型的字段不适合建立索引呢？又是因为什么原因呢？

• 表中会存在大量的字段，但其中哪些字段建立索引才能够最大的性能收益呢？

• MySQL提供的索引种类也不少，一个字段上建立什么类型的索引才最好呢？

• 当表中存在多个索引时，一条查询SQL有多条路径可走，此时走哪条索引最好？

• .......

对于这些问题，如果仅靠上篇索引的知识，相信是很难回答具体的，那在本篇中，则重点讲解索引应用相关的方式方法，例如各索引优劣分析、建立索引的原则、使用索引的指南以及索引失效与索引优化等内容。

一、MySQL各索引的优劣分析

首先来聊聊索引机制带来的利害关系，有句古话曾说过：“凡事有利必有弊”，而MySQL的索引机制也不例外，引入索引机制后，能够给数据库带来的优势很明显：

• ①整个数据库中，数据表的查询速度直线提升，数据量越大时效果越明显。

• ②通过创建唯一索引，可以确保数据表中的数据唯一性，无需额外建立唯一约束。

• ③在使用分组和排序时，同样可以显著减少SQL查询的分组和排序的时间。

• ④连表查询时，基于主外键字段上建立索引，可以带来十分明显的性能提升。

• ⑤索引默认是B+Tree有序结构，基于索引字段做范围查询时，效率会明显提高。

• ⑥从MySQL整体架构而言，减少了查询SQL的执行时间，提高了数据库整体吞吐量。

看着上面一条又一条的好处，似乎感觉索引好处很大啊，对于这点确实毋庸置疑，但只有好处吗？No，同时也会带来一系列弊端，如：

• ①建立索引会生成本地磁盘文件，需要额外的空间存储索引数据，磁盘占用率会变高。

• ②写入数据时，需要额外维护索引结构，增、删、改数据时，都需要额外操作索引。

• ③写入数据时维护索引需要额外的时间开销，执行写SQL时效率会降低，性能会下降。

当然，但对数据库整体来说，索引带来的优势会大于劣势。不过也正由于索引存在弊端，因此索引不是越多越好，合理建立索引才是最佳选择。

之前聊过，MySQL的索引也会分为多种类型，每个类型的索引多多少少都存在一些弊端，接下来聊聊其他类型的索引。

1.1、主键索引存在的陷阱

相信大家数据库的表中，主键一般都是使用自增ID，但这是为什么呢？有人可能会回答自增ID不会重复，确保了主键唯一性。这样也确实没错，但不会重复的又不仅仅只有自增ID，比如我使用随机的UUID也不会重复，为何不使用UUID呢？这是由于索引存在一个陷阱！

众所周知，一张表中大多数情况下，会将主键索引以聚簇的形式存在磁盘中，上篇文章也聊到过，聚簇索引在存储数据时，表数据和索引数据是一起存放的。同时，MySQL默认的索引结构是B+Tree，也就代表着索引节点的数据是有序的。

此时结合上面给出的一些信息，主键索引是聚簇索引，表数据和索引数据在一块、索引结构是有序的，那再反推前面给出的疑惑，为何不使用UUID呢？因为UUID是无序的，如果使用UUID作为主键，那么每当插入一条新数据，都有可能破坏原本的树结构，如下：

​比如上图中的灰色节点，是一条新插入的数据，此时经过计算后，应该排第二个位置，那就代表着后面的三个节点需要移动，然后给灰色节点挪出一个位置存储，从而确保索引的有序性。

这里只是伪逻辑，目的是用于举例演示，实际上B+树索引结构不长这样，在《索引原理篇》会重新说一下这个点的。

由于主键索引是聚簇索引，因此上述案例中，当后续节点需要挪动时，也就代表着还需要挪动表数据，如果是偶尔需要移动还行，但如果主键字段值无序，那代表着几乎每次插入都有可能导致树结构要调整。

但使用自增ID就不会有这个问题，所有新插入的数据都会放到最后。

因此大家数据表的主键，最好选用带顺序性的值，否则有可能掉入主键索引的“陷阱”中。

1.2、联合索引存在的矛盾

为了多条件查询时的效率更高，一般都会同时对多个字段建立联合索引，但之前也聊到过，联合索引存在一个致命的问题，比如在用户表中，通过id、name、age三个字段建立一个联合索引，此时来了一条查询SQL，如下：

SELECT * FROM `zz_user` WHERE name = "竹子" AND age = "18"; 复制代码

而这条SQL语句是无法使用联合索引的，为什么呢？因为查询条件中，未包含联合索引的第一个字段，想要使用联合索引，那么查询条件中必须包含索引的第一个字段，如下：

SELECT * FROM `zz_user` WHERE name = "竹子" AND id = 6; 复制代码

上面这条SQL才是能命中多列索引的语句，因此在建立索引时也需要考虑这个问题，确保建立出的联合索引能够命中率够高。

1.3、前缀索引存在的弊端

前缀索引的特点是短小精悍，我们可以利用一个字段的前N个字符创建索引，以这种形式创建的索引也被称之为前缀索引，相较于使用一个完整字段创建索引，前缀索引能够更加节省存储空间，当数据越多时，带来的优势越明显。

不过前缀索引虽然带来了节省空间的好处，但也正由于其索引节点中，未存储一个字段的完整值，所以MySQL也无法通过前缀索引来完成ORDER BY、GROUP BY等分组排序工作，同时也无法完成覆盖扫描等操作。

1.4、全文索引存在的硬伤

之前做模糊查询时，通常都会使用like%语法，不过这种方式虽然能够实现效果，但随着表越来越大，数据越来越多时，其性能会出现明显下降，而全文索引的推出则能够完美解决该问题，可以利用全文索引代替like%语法实现模糊查询，它的性能会比like%快上N倍。

全文索引虽然可以实现模糊查询，但也存在一系列硬伤，一起来看看。

①由于全文索引是基于分词实现的，所以对一个字段建立全文索引后，MySQL会对该字段做分词处理，这些分词结果也会被存储在全文索引中，因此全文索引的文件会额外的大！

②由于全文索引对每个字段值都会做分词，因此当修改字段值后，分词是需要时间的，所以修改字段数据后不会立马自动更新全文索引，此时需要咱们写存储过程，并调用它手动更新全文索引中的数据。

③除开上述两点外，全文索引最大的硬伤在于对中文支持不够友好，类似于英文可以直接通过符号、空格来分词，但中文呢？一个词语来形容就是博大精深，无法精准的对一段文字做分词，因此全文索引在检索中文时，存在些许精准度问题。

因此如果你项目规模较大，通常再引入ElasticSearch、Solr、MeiliSearch等搜索引擎是一个更佳的选择。

1.5、唯一索引存在的快慢问题

唯一索引有个很大的好处，就是查询数据时会比普通索引效率更高，因为基于普通索引的字段查询数据，例如：

SELECT * FROM TABLE_XX WHERE COLUMN_XX = "XX"; 复制代码

假设COLUMN_XX字段上建立了一个普通索引，此时基于这个字段查询数据时，当查询到一条COLUMN_XX = "XX"的数据后，此时会继续走完整个索引树，因为可能会存在多条字段值相同的数据。

但如果COLUMN_XX字段上建立的是唯一索引，当找到一条数据后就会立马停下检索，因此本身建立唯一索引的字段值就具备唯一性。

因此唯一索引查询数据时，会比普通索引快上一截，但插入数据时就不同了，因为要确保数据不重复，所以插入前会检查一遍表中是否存在相同的数据。但普通索引则不需要考虑这个问题，因此普通索引的数据插入会快一些。

1.6、哈希索引的致命问题

哈希索引，也就是数据结构为Hash类型的索引，不过估计大家接触的比较少，毕竟创建索引时都默认用的B+树结构。但要比起查询速度，哈希索引绝对是MySQL中当之无愧的魁首！因为采用哈希结构的索引，会以哈希表的形式存储索引字段值，当基于该字段查询数据时，只需要经过一次哈希计算就可获取到数据。

但哈希结构的致命问题在于无序，也就是无法基于哈希索引的字段做排序、分组等工作。

因此如果你确定一个表中，不会做排序这类的工作，那可以适当选用哈