关于java:搞定面试官-你可以介绍一下在-MySQL-中哪些情况下-索引会失效嘛

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

* MySQL InnoDB 索引模型

• 在 MySQL InnoDB 中，为什么 delete 删除数据之后表数据文件大小没有变
• 如何计算一个索引的长度
• 如何查看 SQL 的执行打算

以上几篇都是偏理论知识，从明天开始，咱们开始 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='书籍表';

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 没有利用到索引，而是走了全表扫描。

那么咱们最正当的应用，就是应用最左前缀匹配，查问条件改成这样：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 的三个残缺字段的(对于索引长度的计算形式能够参考这篇文章)

索引长度的计算公式：

因为联结索引的构造特点，咱们须要确认命中索引 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 字段，导致局部索引生效。

同时 Extra 为 Using index condition，阐明应用了索引，然而须要回表查问数据。

笼罩索引

在索引应用过程中，尤其是联结索引的应用中，咱们如何正当的建设索引，再加上正当的查问条件的话，咱们是能够应用到笼罩索引的，缩小回表次数，也就是缩小了 IO 次数，能够成倍的进步查问效率。

下边咱们来演示下应用笼罩索引的状况，比方应用如下查问语句：explain select id, title from tb_item where title = '编译原理' and num = 12000;

这个时候咱们能够看到 Extra 值为：Using where; Using index，这意味着这次查问时应用了索引的，同时因为要查问的列曾经在索引中能够间接获取到，所以不须要回表去获取数据，能够间接在索引中找到须要的字段，这也是个别要求不容许 select * 查问的起因，因为这样的话须要获取所有字段，没法利用笼罩索引来提高效率。

对于执行打算中 Extra 字段的阐明，参考我之前的这篇文文章。

Extra 字段阐明：

using index：应用笼罩索引的时候就会呈现 using where：在查找应用索引的状况下，须要回表去查问所需的数据 using index condition：查找应用了索引，然而须要回表查问数据 using index ; using where：查找应用了索引，然而须要的数据都在索引列中能找到，所以不须要回表。

同时，阿里开发标准中对于索引标准的倡议，也是有对于笼罩索引的阐明的：

范畴查问

在理论开发中，范畴查问也是咱们须要常常应用的一个货色，比方统计过来 3 天、过来 7 天的用户量等等。

但这个时候有个问题须要留神，那就是在应用范畴查问的时候，范畴查问 左边的 列索引会生效。

比方下图中的几种查问条件，咱们能够看到写法上差不多的，然而最初索引字段的长度是齐全不一样的。

其中第一条 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) = '组成原理';

看一下它的执行打算：

能够看到是齐全没有用到索引的，间接开始全表扫描，你试想一下，如果你的表就几十上百万数据，这一个全表扫描上来，你怕是中午都不敢睡觉吧。

字符串不加引号

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';

通过执行打算能够看到，索引全副生效了。

反之，如果 or 两边的字段都有索引，则索引仍然能够失效

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

数据分布影响

其实前边说了好几个准则，然而在具体应用中，咱们还是须要用理论状况来剖析，首先如何抉择索引是 MySQL 本人做的事件，如果 MySQL 评估应用索引会比全表更慢，则不应用索引。

那么，什么状况下它评估应用索引还不如间接全表扫描呢？

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

比方应用这条查问语句 explain select * from tb_item where title = '高等数学';

执行打算显示能够看到是没有走索引的，按理来说咱们建设了联结索引 tb_item_title_price_num (title,price,num)，同时也遵循最左前缀匹配准则，是能够走到索引的，可当初的执行打算说没有用到索引。

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

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

能够看到，表中总共 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. 前缀索引的毛病

   • 应用前缀索引就用不上笼罩索引对查问性能的优化了，这也是你在抉择是否应用前缀索引时须要思考的一个因素

阿里开发标准中对于前缀索引的规约阐明

明天的内容到此就要完结了，简略总结一下：

对于索引生效和索引应用准则，须要遵循最最前缀匹配准则，这是 B+ 树的索引模型决定的。此外，不当的应用形式，会造成索引的局部生效，比方范畴查问、字符串不加引号，应用索引列字段进行函数运算以及应用 Or 查问条件时其中某个字段没有索引等等。

内容比拟多，而且是偏实战型的，尽管我提供了具体的示例，但还是心愿你能本人入手操作一遍，这样能力记得更可靠，下次看见面试官你就间接糊他脸上。

我是程序员啊粥，关注我，咱们一起在技术陆地中向上成长。