搞定面试官 – 你可以介绍一下在 MySQL 中，哪些情况下 索引会失效嘛？

  • 2022 年 8 月 14 日
  • 笔记

大家好，我是程序员啊粥，前边给大家分享了

* MySQL InnoDB 索引模型

以上几篇都是偏理论知识，从今天开始，我们开始 MySQL 索引实战内容，具体介绍一下 MySQL 索引的用法。

首先介绍一下索引的相关语法：

索引语法

-- 创建索引
CREATE INDEX indexName ON table_name (column_name);
ALTER table tableName ADD INDEX indexName(columnName);
-- 删除索引
DROP INDEX [indexName] ON mytable;

语法还是非常简单的，没什么太多说的，遵循相关语法规定即可，当然你也可以使用相关的一些 MySQL 客户端管理工具去创建，比如 Navicat 等。

下边介绍一下具体的一些使用语法：

索引使用

今天的实战内容以如下表为例：

CREATE TABLE `tb_item` (
  `id` bigint NOT NULL COMMENT '书籍id，同时也是书籍编号',
  `title` varchar(100) NOT NULL COMMENT '书籍名称',
  `sell_point` varchar(500) DEFAULT NULL COMMENT '书籍卖点',
  `price` bigint NOT NULL COMMENT '书籍价格，单位为：分',
  `num` int NOT NULL COMMENT '库存数量',
  `barcode` varchar(30) DEFAULT NULL COMMENT '书籍条形码',
  `image` varchar(500) DEFAULT NULL COMMENT '书籍图片',
  `cid` bigint NOT NULL COMMENT '所属类目，叶子类目',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '书籍状态，1-正常，2-下架，3-删除',
  `created` datetime NOT NULL COMMENT '创建时间',
  `updated` datetime NOT NULL COMMENT '更新时间',
  `upload_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cid` (`cid`),
  KEY `status` (`status`),
  KEY `updated` (`updated`),
  KEY `tb_item_title_price_num` (`title`,`price`,`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='书籍表';

file

InnoDB 索引因为使用了 B+ 树数据结构，所以在使用上我们就需要了解这种结构，具体你可以回顾我前边这篇文章：MySQL InnoDB 索引模型。

也正是因为使用了这种结构，所以在使用上我们需要遵循一些原则，才能让索引不失效。

最左前缀法则

如果你是建立联合索引，那么我们在使用查询条件的时候，需要从这个索引的最左列开始，并且不跳过索引中的列；如果跳跃某一列，那么就会造成索引部分失效；比如你建立的联合索引字段是 (a, b , c)，那么，你的查询条件就必须是 where a = and b = and c = 这样的格式(具体 a b c 还是 c b a 的顺序不会影响索引，MySQL 优化器会自动优化这种顺序)；

当然，你如果直接把前缀去掉的话，那整个索引就会失效，不再是部分失效。

比如上述表，我们使用查询语句为 explain select * from tb_item where price = 45 and num = 23232 ;

我们建立的索引字段是 title, price, num，但是我们查询条件直接跳过了 title 这个字段，使用 explain 可以看到这条 SQL 的执行计划，key 的值是 Null，意味着这句 SQL 没有利用到索引，而是走了全表扫描。

file

那么我们最合理的使用，就是使用最左前缀匹配，查询条件改成这样：explain select * from tb_item where title = '编译原理' and price = 45 and num = 23232;

再来看一下执行计划，我们看到 key 这俩变为了 tb_item_title_price_num，同时索引长度为 314 ，证明是使用到了联合索引 tb_item_title_price_num 的三个完整字段的(关于索引长度的计算方式可以参考这篇文章)

file

索引长度的计算公式：

因为联合索引的结构特点， 我们需要确认命中索引 tb_item_title_price_num 是命中了 title 列、price 列，还是 num 列。

想要会分析，就需要掌握索引长度的计算方法了。

1、索引长度公式

  1. 所有的索引字段，如果没有设置 not null，则需要加一个字节。

  2. 定长字段，int 占四个字节、date 占三个字节、char(n) 占 n 个字符。

  3. 对于变成字段 varchar(n)，则有 n 个字符 + 两个字节。

  4. 不同的字符集，一个字符占用的字节数不同。latin1 编码的，一个字符占用一个字节，gbk 编码的，一个字符占用两个字节，utf8 编码的，一个字符占用三个字节，utf8mb4 编码的，一个字符占四个字节

  5. 索引长度 char()、varchar() 索引长度的计算公式：

Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许 null) + 2(变长列)

综上可得：上述 tb_item 表中，使用的 utf8 编码

所以 title 字段的索引长度是 3 * 100 + 0 + 2 = 302

price 字段的索引长度是 8

num 字段的索引长度是 4

tb_item_title_price_num 索引总共长度是 302 + 8 + 4 = 314

接下来我们修改查询条件为 explain select * from tb_item where title = '编译原理' and num = 23232;

此时 Key len 变为 302，说明只利用到了 title 的索引，因为查询条件跳过了 price 字段，导致部分索引失效。

file

同时 Extra 为 Using index condition，说明使用了索引，但是需要回表查询数据。

覆盖索引

在索引使用过程中，尤其是联合索引的使用中，我们如何合理的建立索引，再加上合理的查询条件的话，我们是可以使用到覆盖索引的，减少回表次数，也就是减少了 IO 次数，可以成倍的提高查询效率。

下边我们来演示下使用覆盖索引的情况，比如使用如下查询语句：explain select id, title from tb_item where title = '编译原理' and num = 12000;

file

这个时候我们可以看到 Extra 值为：Using where; Using index，这意味着这次查询时使用了索引的，同时因为要查询的列已经在索引中可以直接获取到，所以不需要回表去获取数据，可以直接在索引中找到需要的字段，这也是一般要求不允许 select * 查询的原因，因为这样的话需要获取所有字段，没法利用覆盖索引来提高效率。

关于执行计划中 Extra 字段的说明，参考我之前的这篇文文章。

Extra 字段说明：

using index ：使用覆盖索引的时候就会出现 using where：在查找使用索引的情况下，需要回表去查询所需的数据 using index condition：查找使用了索引，但是需要回表查询数据 using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表。

同时，阿里开发规范中对于索引规范的建议，也是有对于覆盖索引的说明的：

file

范围查询

在实际开发中，范围查询也是我们需要经常使用的一个东西，比如统计过去 3 天、过去 7 天的用户量等等。

但这个时候有个问题需要注意，那就是在使用范围查询的时候，范围查询右边的列索引会失效。

比如下图中的几种查询条件，我们可以看到写法上差不多的，但是最后索引字段的长度是完全不一样的。

file

其中第一条 select id from tb_item where title = '编译原理' and price = 56 and num = 10000,是我们常用的等值查询，这在上一步最左前缀的时候已经说了，肯定是可以完整用到索引的，执行计划也验证了我们的结论。

第二条查询语句 select id from tb_item where title = '编译原理' and price > 56 and num = 10000, 我们使用了范围查询，这个时候可以看到 key_len 变成了 310，这说明部分索引失效了，也就是范围查询右边的列，num 这个列的索引失效了。

第三条查询语句select id from tb_item where title = '编译原理' and price >= 56 and num = 10000，是一种很好的规避这种索引失效的一种手段，在业务允许的情况下我们可以使用大于等于或者小于等于来代替大于或者小于，这种情况下是可以完整使用到索引的。

索引列运算

我遇到过很多开发人员，会在 SQL 中掺杂运算，这在你的数据量不大的前提下，确实可以为你提供方便，但是一旦你的数据量起来之后，你如果在索引列上做计算，这会直接导致索引的失效，进而引发全表扫描。

因为 MySQL 在做索引的时候是对你的字段值本身做索引，而不是对你运算后的值做索引，你可以回顾下关于 B+ 树的索引模型，TODO，

所以我们在实际使用中需要彻底避免在索引列上做计算，因为没有任何一个理由支持我们必须要这么做。

比如这个查询语句 explain select * from tb_item where substring(title,4, 4) = '组成原理';

看一下它的执行计划：

file

可以看到是完全没有用到索引的，直接开始全表扫描，你试想一下，假如你的表就几十上百万数据，这一个全表扫描下去，你怕是半夜都不敢睡觉吧。

字符串不加引号

MySQL 在索引查询中，会自动的进行的字段类型转换，如果我们对于一个数字格式的字符串字段，在查询的时候没有用单引号，那么会触发 MySQL 查询优化器的类型自动转换。

比如你有张表存的是手机号，字段叫 phone，然后针对查询语句 select id from tb_user where phone = 1888888888，那怕你在 phone 字段上额外建了索引，它也是不会走索引的。

因为这条语句在查询优化器的处理下会变成 select id from tb_user where cast(phone as signed int) = 1888888888 去执行。

这个时候因为对索引列做了函数运算，就导致了索引的失效。

模糊查询

关于模糊查询，这个也就等同于最左前缀原则，你如果是在字段的头部位置进行模糊搜索的话，首先不遵循最左前缀匹配原则，那索引自然就失效了。

反之，如果是尾部字段进行模糊匹配的话，那么索引还是同样生效的。

因此，我们真的需要模糊搜索功能的话，最佳的方式是使用搜索引擎，而不是在 MySQL 中直接 like 查询。

or 连接条件

用 or 分割开的条件，如果 or 前的条件列中有索引，而后面的列中没有索引，那么索引会失效，不管是这两个字段中的任何一个索引，都会失效。

比如我们这张表 tb_item 表中 barcode 列没有索引，使用如下查询语句

explain select id, title from tb_item where title = '编译原理' or barcode = '202457815';

file

通过执行计划可以看到，索引全部失效了。

反之，如果 or 两边的字段都有索引，则索引依然可以生效

explain select id, title from tb_item where title = '编译原理' or price = 128;

file

数据分布影响

其实前边说了好几个原则，但是在具体使用中，我们还是需要用实际情况来分析，首先如何选择索引是 MySQL 自己做的事情，如果 MySQL 评估使用索引会比全表更慢，则不使用索引。

那么，什么情况下它评估使用索引还不如直接全表扫描呢？

常见的一种情况是表中的数据分析分布，如果这个字段的值区分度不够明显，那么 MySQL 极有可能进行全表扫描。

比如使用这条查询语句 explain select * from tb_item where title = '高等数学';

file

执行计划显示可以看到是没有走索引的，按理来说我们建立了联合索引 tb_item_title_price_num (title,price,num) ，同时也遵循最左前缀匹配原则，是可以走到索引的，可现在的执行计划说没有用到索引。

接下来我们修改查询条件为：explain select * from tb_item where title = '编译原理';

file

可以看到同样的查询语句，只不过是值不同，就会造成一个索引生效，一个索引失效，究其原因，是因为表中 title 为’高等数学’的数据占比太多，MySQL 判断与其走索引还不如直接全表扫描，所以索引失效了。

file

可以看到，表中总共 841 行数据，其中高等数据就占了 803 条。

前缀索引

前边我有篇文章提到过，InnoDB 引擎对于索引的字段长度是有限制的，TODO，所以在我们遇到字段类型过长的时候，可以截取一部分来建立索引，从而节约索引空间，提高查询效率。

关于前缀索引，我们需要明确以下几年内容：

  1. 创建索引，指定索引长度语法：create index idx_xxx on table_name(column(n))
  2. 前缀长度的选择：可以根据索引的选择性来决定，选择性越高则查询效率越高，唯一索引的选择性是 1 ，这是最好的索引选择性，性能也是最好的。
  3. 索引列区分度查询，类似如下
    • select count(distinct email)/count(*) from tb_user;
    • select count(distinct substring(email,1,5))/cont(*) from tb_user
  4. 前缀索引的好处：
    • 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
  5. 前缀索引的缺点
    • 使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素

阿里开发规范中对于前缀索引的规约说明

file

今天的内容到此就要结束了，简单总结一下：

关于索引失效和索引使用原则，需要遵循最最前缀匹配原则，这是 B+ 树的索引模型决定的。此外，不当的使用方式，会造成索引的部分失效，比如范围查询、字符串不加引号，使用索引列字段进行函数运算以及使用 Or 查询条件时其中某个字段没有索引等等。

内容比较多，而且是偏实战型的，虽然我提供了具体的示例，但还是希望你能自己动手操作一遍，这样才能记得更牢靠，下次看见面试官你就直接糊他脸上。

我是程序员啊粥，关注我，我们一起在技术海洋中向上生长。

VirMach 便宜 VPS

QNews

QNews