MySQL索引应用篇：建立索引的正确姿势与使用索引的最佳指南

引言

数据库索引，绝对是MySQL的核心功能之一，如果没有索引机制的数据库，那数据的检索效率绝对是令人无法接受的mssql 按关键字排序，毕竟没有索引的表数据，就如同一个普通的文本文件存储在磁盘中，我们对于MySQL提供的索引机制，从引入，到创建、使用、分类、管理....等进行了全面阐述，大家对MySQL索引机制建立了系统化的认知，对索引机制进一步加深掌握。

下述一些关于索引的问题：

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

一、MySQL各索引的优劣分析

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

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

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

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中当之无愧的魁首！因为采用哈希结构的索引，会以哈希表的形式存储索引字段值，当基于该字段查询数据时，只需要经过一次哈希计算就可获取到数据。

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

因此如果你确定一个表中，不会做排序这类的工作，那可以适当选用哈希结构作为索引的数据结构，它会给你带来意想不到的性能收益~

二、建立索引的正确姿势

经过上述一系列分析后，简单讲明了每种索引类型存在的缺陷问题，但这跟我们本篇有啥关系呢？其实关系很大，因为只有当你了解了每种索引存在的劣势，才能更好地考虑并设计出合理的索引，而不是一股脑的盲目创建索引。

那么在创建索引时，咱们应当遵守那些原理原则，才能创建出合理的索引呢？

在实际项目场景中，当SQL查询性能较慢时，我们常常会有一个疑惑：表中哪个字段建立一个索引能带来最大的性能收益呢？一般来说，判断字段是否要添加的索引的依据，是看这个字段是否被经常当做查询条件使用，但也不能光依靠这一个依据来判断，比如用户表中的性别字段，就会经常被用做查询条件，但如果对性别字段建立一个索引，那对查询的性能提升并不大，因为性别就两个值：男/女（不包含泰国在内），那对其建立索引，索引文件中就只会有两个索引节点，大致情况如下：

这种情况下，为性别建立一个索引，带来的性能收益显然不是太大。同时，上图中给出的案例，也不是索引真正的样子，如果表中存在主键索引或聚簇索引，对其他字段建立的索引，都是次级索引，也被称为辅助索引，其节点上的值，存储的并非一条完整的行数据，而是指向聚簇索引的索引字段值。

如果基于辅助索引查询数据，最终数据会以何种方式被检索出来，这里就牵扯到MySQL中的一个新概念，也就是SQL执行时的回表问题。

2.1、索引查询时的回表问题

什么叫做回表呢？意思就是指一条SQL语句在MySQL内部，要经过两次查询过程才能获取到数据。这是跟索引机制有关的，先来看看索引在MySQL内部真正的面貌：

在上图用户表中，基于ID字段先建立了一个主键索引，然后又基于name字段建立了一个普通索引，此时MySQL默认会选用主键索引作为聚簇索引，将表数据和主键索引存在同一个文件中，也就是主键索引的每个索引节点，都直接对应着行数据。而基于name字段建立的索引，其索引节点存放的则是指向聚簇索引的ID值。

在这种情况下，假设有一条下述SQL，其内部查询过程是啥样的呢？

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

首先会走name字段的索引，然后找到对应的ID值，然后再基于查询到的ID值，再走ID字段的主键索引，最终得到一整条行数据并返回。

在这个案例中，一条查询SQL经历了两次查询才获取到数据，这个过程则被称之为回表。

回表动作会导致额外的查询开销，因此尽量可以基于主键做查询，如果实在需要使用非主键字段查询，那么尽量不要写明查询的结果字段，而并非使用*。

当然，实际情况中建立联合索引，利用索引覆盖特性，从而避免使用辅助索引，这样也能够消除回表动作，但关于这点后面再聊，先来说说建立索引需要遵循的一些原则。

2.2、建立索引时需要遵守的原则

前面说过一点，当建立索引仅考虑一个字段是否被经常用于查询是不够的，往往一个合适的索引需要更为细致与长远的思考，例如使用多个字段建立是否会更好？创建其他类型的索引性能是否会更佳？下面我们就一起来看看建立索引时，需要遵守的一些原则：

同时，除开上述一些建立索引的原则外，在建立索引时还需有些注意点：

对于索引机制，在建立时应当参考上述给出的意见，这每一条原则都是从实际经验中总结出来的，前面八条不一定要全面思考，但后面七条注意点，一定要牢记，如若你的索引符合后面七条中的描述，那一定要更改索引。

2.3、联合索引的最左前缀原则

首先在讲最左前缀原则之前，先看看上述给出的一条原则：

对于这一点是为什么呢？举个栗子理解，比如此时基于X、Y、Z字段建立了一个联合索引，实际上也相当于建立了三个索引：X、X、Y、X、Y、Z，因此只要查询中使用了这三组字段，都可以让联合索引生效。

但如若查询中这三个字段不以AND形式出现，而是单独作为查询条件出现，那单值索引性能会好一些，但三个不同的索引，维护的代价也会高一些。

其实联合索引的最左前缀原则，道理很简单的，就是组成联合索引的多个列，越靠左边优先级越高，同时也只有SQL查询条件中，包含了最左的字段，才能使用联合索引，例如：

-- 基于上面的哪个X、Y、Z联合索引
SELECT * FROM tb WHERE Y = "..." AND Z = "...";
复制代码

上面这条SQL就显然并不会使用联合索引，因为不符合最左前缀原则，最左侧的X字段未曾被使用。也正由于MySQL在使用联合索引时会遵循最左前缀原则，所以才在前面建立索引的建议中给出了一条：

因为将查询频率越高的字段放首位，就代表着查询时命中索引的几率越大。同时，MySQL的最左前缀原则，才匹配到范围查询时会停止匹配，比如>、范围查询，因此这里只能使用X索引，而不能使用X、Y或X、Y、Z索引。

对于一条查询SQL是否用到了索引，或者一条查询SQL到底用了那个索引，其实可以通过MySQL自带的explain工具分析（后续讲解）。

最后再来一个简单的栗子，加深一下对于联合索引的认知：

select * from user where name = '竹子';
select * from user where name = '竹子' and age = 18;
A. create index index_name on user(name);
   create index index_name on user(age);
B. create index index_name on user(name,age);
复制代码

比如上述这个案例中，对于这两条SQL选第一种方式创建索引，还是第二种呢？答案是B，因为两条sql完全能够利用到第二个创建的联合索引。

select * from user where name = '竹子' and age = 18;
select * from user where  age = 18 and name = '竹子';
复制代码

同时选B建立联合索引后，如上两条SQL都会利用到上面创建的联合索引，SQL是否走索引查询跟where后的条件顺序无关，因为MySQL优化器会优化，对SQL查询条件进行重排序。

三、索引失效与使用索引的正确姿势

相信这一点大家看了有些懵，啥叫使用索引的正确姿势？索引不是MySQL执行SQL时自动选择的吗？我们只能建立索引，怎么使用啊？其实这里是指我们编写SQL时，要注意的点，毕竟MySQL查询时到底使不使用索引，这完全取决于你编写的SQL。

但很多小伙伴在平时写SQL的时候，一般只追求实现业务功能，只要能够查询出相应的数据即可，压根不会过度考虑这条SQL应用到索引，那么这里就是给出一些经验之谈，讲清楚几点写SQL时的方法论。

其实索引本身是一把双刃剑，用的好能够给我们带来异乎寻常的查询效率，用的不好则反而会带来额外的磁盘占用及写入操作时的维护开销。因此大家一定要切记，既然选择建了索引，那一定要利用它，否则还不如干脆别建，既能节省磁盘空间，又能提升写入效率。

3.1、索引失效的那些事儿

想要用好索引，那一定要先搞清楚那些情况会导致索引失效，弄明白这些事项之后，在写SQL的时候刻意避开，那你写出来的SQL十有八九是会用到索引的，那么在数据库中那些情况下会导致索引失效呢？下面一起来聊一聊，但单纯的讲概念会有种纸上谈兵的感觉，因此下面简单的举个案例，然后来说明索引失效的一些情况。

SELECT * FROM `zz_users`;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
|       2 | 竹子      | 男       | 1234     | 2022-09-14 16:17:44 |
|       3 | 子竹      | 男       | 4321     | 2022-09-16 07:42:21 |
+---------+-----------+----------+----------+---------------------+
ALTER TABLE `zz_users` ADD PRIMARY KEY `p_user_id`(`user_id`);
ALTER TABLE `zz_users` ADD KEY `unite_index`(`user_name`,`user_sex`,`password`);
复制代码

此时对这张用户表，分别创建两个索引，第一个是基于user_id创建的主键索引，第二个是使用user_name、user_sex、password三个字段创建的联合索引。

但想要查看一条SQL是否使用了索引，需要用到一个自带的分析工具ExPlain，下面简单介绍一下。

3.1.1、执行分析工具 - ExPlain

这里就对explain工具做一个简单介绍，先来看看这个工具/命令的作用，当在一条SQL前加上explain命令，执行这条SQL后会列出所有的执行方案：

EXPLAIN SELECT * FROM `zz_users`;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | zz_users | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
复制代码

对于上述这么多的字段，其实目前不需要完全弄懂，本文只需要记住里面的type字段即可，all表示走全表扫描，const、ref...表示通过索引查询。

下面一起来聊一聊索引失效的一些场景。

3.1.2、查询中带有OR会导致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE user_id = 1 OR user_name = "熊猫";
复制代码

例如上述这条SQL，其中既包含了主键索引的字段，又包含了联合索引的第一个字段，按理来说是会走索引查询的对吗？但看看执行结果：

从结果中可看到type=ALL，显然并未使用索引来查询，也就代表着，虽然所有查询条件都包含了索引字段，但由于使用了OR，最终导致索引失效。

3.1.3、模糊查询中like以%开头导致索引失效

众所周知，使用like关键字做模糊查询时，是可以使用索引的，那来看看下述这条SQL：

EXPLAIN SELECT * FROM `zz_users` WHERE user_name LIKE "%熊";
复制代码

在这条SQL中以联合索引中的第一个字段作为了查询条件，此时会使用索引吗？看看结果：

结果中显示依旧走了全表扫描，并未使用索引，但like不以%开头，实际上是不会导致索引失效的，例如：

在这里以%结尾，其实可以使用联合索引来检索数据，并不会导致索引失效。

3.1.4、字符类型查询时不带引号导致索引失效

-- 先插入一条user_name = 1111 的数据
INSERT INTO `zz_users` VALUES(4,"1111","男","4321","2022-09-17 23:48:29");
EXPLAIN SELECT * FROM `zz_users` WHERE user_name = 111;
复制代码

上述这条SQL按理来说是没有半点问题的，目前是符合联合索引的最左匹配原则的，但来看看结果：

从结果中很明显的可以看出，由于user_name是字符串类型的，因此查询时没带引号，竟然直接未使用索引，导致了索引失效（上面也放了对比图，大家可以仔细看看区别）。

3.1.5、索引字段参与计算导致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE user_id - 1 = 1;
复制代码

上面这条SQL看着估计有些懵，但实际上很简单，就是查询ID=2的数据，理论上因为查询条件中使用了主键字段，应该会使用主键索引，但结果呢？

由于索引字段参与了计算，所以此时又导致了索引失效，因此大家要切记，千万不要让索引字段在SQL中参与计算，也包括使用一些聚合函数时也会导致索引失效，其根本原因就在于索引字段参与了计算导致的。

这里的运算也包括+、-、*、/、!.....等一系列涉及字段计算的逻辑。

3.1.6、字段被用于函数计算导致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE SUBSTRING(user_name,0,1) = "竹子";
复制代码

上述中，我们使用SUBSTRING函数对user_name字段进行了截取，然后再用于条件查询，此时看看执行结果：

很显然，并未使用索引查询，这也是意料之中的事情，毕竟这一条和3.1.5的原因大致相同，索引字段参与计算导致失效。

3.1.7、违背最左前缀原则导致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE `user_sex` = "男" AND `password` = "1234";
复制代码

上述这条SQL中，显然用到了联合索引中的性别和密码字段，此时再看看结果：

由于违背了联合索引的最左前缀原则，因为没使用最左边的user_name字段，因此也导致索引失效，从而走了全表查询。

3.1.8、不同字段值对比导致索引失效

从一张表中查询出一些值，然后根据这些值去其他表中筛选数据，这个业务也是实际项目中较为常见的场景，下面为了简单实现，就简单用姓名和性别模拟一下字段对比的场景：

EXPLAIN SELECT * FROM `zz_users` WHERE user_name = user_sex;
复制代码

按理来说，因为user_name属于联合索引的第一个字段，所以上述这条SQL中规中矩，理论上会走索引的，但看看结果：

显然，这个场景也会导致索引无法使用，因此之后也要切记这点。

3.1.9、反向范围操作导致索引失效

一般来说，如果SQL属于正向范围查询，例如>、

（编辑：上海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!