Mysql高级(五) 索引失效

1.全值匹配

专注于为中小企业提供成都网站建设、网站设计服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业枣强免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了上1000家企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。

2.最佳左前缀法则

3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列

5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一直）），减少select *

6.mysql在使用不等于（!=或者）的时候无法使用索引会导致全表扫描

7.is null， is not null也无法使用索引

8.like以通配符开头（‘%abc...’）mysql索引失效会变成全表扫描的操作

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

10.少用or，用它来连接时索引会失效

Mysql索引失效的底层原理

1.索引失效的原因

联合索引排序的原理：先对第一个字段进行排序，在第一个字段相同的情况下考虑第二个字段，然后在第二个字段相同的情况下才考虑第三个字段...

CREATE TABLE 'test_user'(

'id' int(11) not null auto_increment comment '主键id',

‘user_id’ varchar(36) not null comment '用户id',

'phone' varchar(20) not null comment '用户名称',

'lan_id' int(9) not null comment '本地网',

'region_id' int(9) not null comment '区域'

)ENGINE=InnoDB Auto_increment=4057960 Default charset=utf8mb4;

假设将('phone', 'lan_id', 'region_id')组成的联合索引

Explain select * from test_user where lan_id = 1;

此时的索引是失效的，因为联合索引是遵循最左前缀法则即第一个字段有序的情况下lan_id才有序。现在是跳过phone,直接搜索lan_id相当于在一个无序的B+树上搜索，所以只能全表扫描。

例1下例范围查找的右边索引会失效

Explain select * from test_user where a 1 and b = 1;

为什么索引会失效？

因为我们可以找到a 1的所有的节点，但是此时的b索引是无序的，仍然不可以通过二分查找法来查找

例2. like查询中，如果%放在两边或者放到左边，它都是不走索引的。只有%放到右边，它某些情况才会走这个索引。这是什么原因？

字符串在B+树里面存储的时候，它也是按照字母的大小去排序。首先按照第一个字母去比较，如果第一个字母相同则按照第二个字母去比较和最佳左前缀法则相似。如果左边用了%，那后面的字符是无序的，此时就不能使用二分查找来定位元素还是退化为了全表扫描。

3.Mysql中的索引查询为什么使用了B+树结构，而不使用哈希索引或者B树？

首先哈希值是无序的，不能够进行范围查找。

平衡二叉树的缺点是当数据量非常大的时候，其深度也会非常深这样也会导致查找效率慢。其次其存在回旋查找的问题。比如说当存在范围查询5的时候定位到该元素之后还得回溯到前面的节点元素6，7

B树的最大特点是一个节点可以存储多个值，这样可以使得树的高度变矮，从而使得树的查找速度变快。但是其也存在回旋查找的问题。

B+树则解决了这个问题，它的非叶子节点存储的是key,其叶子节点既存储了key也存储了value并且其叶子节点是有序的，节点之间用指针相连也正是因为这一点使得B+树在范围查询的时候不存在回旋问题。

为什么MySQL字符串不加引号索引会失效？这个答案是我见过最靠谱

作为一名程序员，在求职面试时，不知你有没有遇到类似这样的问题。

张工是一名java程序员，最近到一家软件公司应聘软件开发岗位，面试官问了他关于MySql索引这样的一个问题。

对于这个问题张工之前在做项目时也曾遇到，那时候字段明明是加了索引，可不明白为什么还是很慢。后加上引号就正常了，为了赶项目进度，张工也没有再去留意。

现在面试官突然这么一问，张工也说不出个所以然来。

面试官让他回去等通知。

我们知道MySql索引可以加快数据检索速度，这也是使用的索引的最主要原因。但有时候使用不当就会遇到索引失效问题，譬如在MySQL字符串类型查询时不加引号索引会失效，是因为MySQL内部进行了隐式转换。

那为什么会发生隐式转换？又是怎么转换的呢？

今天我们来聊聊关于MySql索引失效的话题。

先来看看一般导致索引失效的有哪些？

如果一张表的索引有多个，要遵守最佳左前缀法则，即查询从索引的最左前列开始并且不跳过索引中的列。

用户表tb_user字段 id，name，age，sex

创建索引为idx_user_name

执行语句：

这时候就会导致索引失效

在索引列上做加工操作，查询时会导致索引失效，从而导致全表扫描。所以，建议不要在索引列上做任何操作。

举个例子，例如订单表tb_order有个索引是dt（日期）， 字段数据存放的格式是这样的2021-12-10 这样的，如果有个需求需要根据dt，格式是20220207这样的来查询，这时候就不要对dt进行格式转换了，

这样索引就失效了。

而是应该对 20220207做格式处理

这样dt索引才不会失效。

例如我们在订单表tb_order建立了索引idx_order_id，order_id字段类型为varchar

在查询时如果使用where order_id= 20220207123654100，这样的查询方式会直接造成索引失效。

要让索引生效，正确的用法为

假如有张用户表tb_user，创建的索引为idx_user_name_age_sex_phone 其中name、age、sex都加了索引。

执行语句

上面这条sql语句只会命中name和age索引，sex索引会失效，复合索引失效需要查看key_len的长度。

再来看一个例子：

从这两条SQL执行的结果我们可以看出，执行第一条SQL没有使用到索引，而执行第二条SQL时使用到了索引。这是为什么呢？

我们需要先了解下mysql索引优化器工作的原理。选择索引是优化器工作，优化器工作有自己的一套规则，如果等号两边的数据类型不一致，则会发生隐式转换。

基于这条规则，我们回过头看看

这条SQL语句执行时就会变为

由于对索引列进行了函数操作，所以才导致索引失效，从而全表扫描了。

那么问题来了，细心的你不知有没有留意到为什么是把左侧的列转为int类型，而不是把右侧的值转成字符串类型呢？

什么情况下把数字转为字符串，什么情况下把字符串转为数字，优化器它是根据什么规则来进行判断的？其实规则也并不复杂。

根据这个规则，我们再回过头看看之前的查询语句

select '12345678936' = 12345678936

返回1 所以这时候就把左侧的列值12345678936转成数字。

关于MySql索引失效的问题先简单写到这，建议平时在做项目时还是要多了解下原理，如果你了解其背后的原理，求职面试时和面试官交流起来就会很舒服了，相信能为这次面试加分，提高被录用的概率。

为什么MySQL字符串类型查询时不加引号索引会失效？这是因为要查询的字符串字段没有加引号时，MySQL内部进行了隐式转换，此次查询会导致全表扫描，所以慢了。

总结：

在索引列上进行了函数操作，MySQL内部会进行了隐式转换，导致索引失效，从而产生全表扫描。

由于笔者知识及水平有限,文中错漏之处在所难免,如有不足之处，欢迎交流。

拓展

索引创建

1、主键索引：

2、唯一索引：

3、普通索引：

4、全文索引：

alter table table_name add fulltext (column)

5、联合索引：

索引删除

mysql为啥建选不了普通索引

mysql数据库索引是一种能够让mysql数据查询更加快速的数据结构，我们在新建数据库的时候，如果设置了某个字段的Primary key主键，那么数据库会默认为我们的主键字段创建一个唯一索引(Unique Index)的东西，所以你就不需要再为此字段创建mysql数据库索引了，当然了，如果你想设置别的字段索引，那么就要额外加入该字段的数据库索引了。

一：Mysql数据库索引的创建

1)如何为我们的mysql数据库添加索引呢？很简单，请看下面的创建mysql普通索引格式：

CREATE INDEX [index name] ON [table name]([column name]);

参数说明：NameDescription

index name索引名称.

table name需要添加索引的表名称.

column name需要添加索引的列名称.

例如我们要创建一个索引，就可以使用如下的mysql命令运行即可：CREATE INDEX myindex ON mytable(aut_id);

2)唯一索引的创建，和mysql普通索引差不多，只需要在INDEX关键词前面加入UNIQUE关键词即可，mysql命令如下：CREATE UNIQUE INDEX myindex ON mytable(aut_id);

我们也可以使用USING BTREE关键字，B-tree算法减少定位记录时所经历的中间过程，从而加快存取速度，mysql命令如下：CREATE UNIQUE INDEX myindex ON mytable(aut_id) USING BTREE;

二：查看mysql数据库索引

我们可以使用以下命令查询刚刚新建的mysql数据库索引，“from mytable”代表查询该数据库表里面有哪些索引，mysql命令如下：show index from mytable;

创建成功之后，会在“key_name”字段中显示刚刚创建的数据库索引名称，如图所示：

三：删除mysql数据库索引

我们也可以使用mysql命令删除某张表的索引，命令如下：drop index english on mytable

四：查询sql语句是否使用了mysql索引

我们上面常见好mysql数据库索引之后，如果想要知道我们在执行sql语句时是否使用了数据库索引，就要在sql语句前面加入“EXPLAIN”关键词，命令如下所示：EXPLAIN SELECT * FROM `allword` where english='America';

如果使用到了我们创建的字段索引，就会看到如图所示的字段都不会是NULL空值，如下：

在有些情况下mysql索引会失效，也就是在执行到sql语句时没有使用到我们创建的数据库表字段索引，可能有以下这些情况：

1：使用了OR关键字查询

2：或者LOWER()，UPPER()函数，

3：还有一种就是使用了LIKE关键字查询，像(like '%XX'或者like '%XX%')这样的语句，但是像(like 'XX%')这种情况的mysql表索引是不会失效的。

mysql 索引失效总结

首先我们还是先把表结构说下：用户表tb_user结构如下：

1、 不要在索引列上进行运算操作， 索引将失效。

手机号phone字段有唯一索引，当根据phone字段进行函数运算操作之后，索引失效:

2、 字符串类型字段使用时，不加引号，索引将失效。

如果字符串不加单引号，对于查询结果，没什么影响，但是数 据库存在隐式类型转换，索引将失效。

3、 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

接下来，我们来看一下这三条SQL语句的执行效果，查看一下其执行计划：

由于下面查询语句中，都是根据profession（专业）字段查询，profession字段是一个普通的索引， 我们主要看一下，模糊查询时，%加在关键字之前，和加在关键字之后的影响。

经过上述的测试，我们发现，在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字 前面加了%，索引将会失效。

4、 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会 被用到。

由于age没有索引，所以即使id有索引，索引也会失效。所以需要针对于age也要建立索引。

5、 数据分布影响：如果MySQL评估使用索引比全表更慢，则不使用索引。

当前文章：mysql索引怎么失效 mysql 索引失效
文章出自：http://kswjz.com/article/dodcdcg.html