为什么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 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

集一个索引包含多个列（最左前缀匹配原则）

索引列的值必须唯一，但允许有空值

全文索引为FUllText，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值，全文索引可以在CHAR,VARCHAR,TEXT类型列上创建

设定主键后数据会自动建立索引，InnoDB为聚簇索引

即一个索引只包含单个列，一个表可以有多个单列索引

覆盖索引是指一个查询语句的执行只用从所有就能够得到，不必从数据表中读取，覆盖索引不是索引树，是一个结果，当一条查询语句符合覆盖索引条件时候，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后的回表 *** 作，减少了I/O效率

查看索引

列名解析：

删除索引

查看：

删除前:

删除后：

普通的索引，没有什么介绍

查看:（注意和前缀索引Sub_part的区别）

当索引的列是unique的时候，会生成唯一索引，唯一索引关于null有下列两种情况

SQLSERVER 下的唯一索引的列，允许null值，但最多允许有一个空值

MYSQL下的唯一索引的列，允许null值，并且允许多个空值

查看:

会建立两个索引，一个非聚簇索引，一个是唯一索引

结果:

可以插入两个空值（明人不说暗话，我喜欢MySQL）

一方面，它不会索引所有字段所有字符，会减小索引树的大小.

另外一方面，索引只是为了区别出值，对于某些列，可能前几位区别很大，我们就可以使用前缀索引。

一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

查看：

查看：

复合索引的最左前缀匹配原则 ：

对于复合索引，查询在一定条件才会使用该索引

减少开销。 建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写 *** 作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。 对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io *** 作。减少io *** 作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高。 索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w。

在模糊搜索中很有效，搜索全文中的某一个字段，可以参考这篇博文

: https://zhuanlan.zhihu.com/p/88275060

我们先进行下面一个实验看看InnoDB下的主键索引的一个现象。

查看:

我们插入进去的时候，数据的id都是乱序的，为什么这里最后select查询出来的结果都是进行了排序？

这是因为InnoDB索引底层实现的是B+tree，B+tree具有下列的特点：

所以上面的排序是为了使用B+tree的结构 ，B+tree为了范围搜索，将主键按照从小到大排序后，拆分成节点。后续还有新的节点进入的时候，和B-tree相同的 *** 作，会进行分裂。

一般来说，聚簇索引的B+tree都是三层

InnoDB中主键索引一定是聚簇索引，聚簇索引一定是主键索引。

为什么这里辅助索引叶子结点不直接存储数据呢？

MYISAM只有非聚簇索引，索引最终指向的都是物理地址。

Q:既然有回表的存在，那么聚簇索引的优势在哪里？

Q:主键索引作为聚簇索引需要注意什么

在查询语句中使用LIke关键字进行查询时，如果匹配字符串的第一个字符为"%"，索引不会使用。如果“%”不是在第一位，索引就会使用

多列索引是在表的多个字段上创建的索引，满足最左前缀匹配原则，索引才会被使用

查询语句只有Or关键字时候，如果OR前后的两个条件都是索引，这这次查询将会使用索引，否则Or前后有一个条件的列不是索引，那么查询中将不使用索引

---