mysql索引失效有哪些（mysqlunionall无法走索引）

本文目录

• mysqlunionall无法走索引
• mysql 索引失效的原因有哪些
• mysqldate_sub索引失效
• mysqlloaddata索引失效吗
• mysql 避免索引失效
• 为什么MySQL字符串不加引号索引会失效这个答案是我见过最靠谱

mysqlunionall无法走索引

mysqlunionall无法走索引11 27
1. like %%失效。 方案:改为like %,只写后面的%就能走索引。
2. 虽然有索引,但是查询条件没有索引列或者order by 排序没有索引列。 方案:让查询条件有索引列
3. 索引列存在null值的情况。 方案:索引列如果没有值,则给空字符串或者数字的0,总之就是不要设置null

mysql 索引失效的原因有哪些

1.索引无法存储null值
　　a.单列索引无法储null值，复合索引无法储全为null的值。
　　b.查询时，采用is null条件时，不能利用到索引，只能全表扫描。
　　 为什么索引列无法存储Null值？
　　a.索引是有序的。NULL值进入索引时，无法确定其应该放在哪里。（将索引列值进行建树，其中必然涉及到诸多的比较操作，null 值是不确定值无法　　
　　比较，无法确定null出现在索引树的叶子节点位置。）　
　　b.如果需要把空值存入索引，方法有二：其一，把NULL值转为一个特定的值，在WHERE中检索时，用该特定值查找。其二，建立一个复合索引。例如　
　　create index ind_a on table(col1,1); 通过在复合索引中指定一个非空常量值，而使构成索引的列的组合中，不可能出现全空值。　
　　
2.不适合键值较少的列（重复数据较多的列）
　　假如索引列TYPE有5个键值，如果有1万条数据，那么 WHERE TYPE = 1将访问表中的2000个数据块。
　　再加上访问索引块，一共要访问大于200个的数据块。
　　如果全表扫描，假设10条数据一个数据块，那么只需访问1000个数据块，既然全表扫描访问的数据块
　　少一些，肯定就不会利用索引了。
3.前导模糊查询不能利用索引(like ’%XX’或者like ’%XX%’)
　　假如有这样一列code的值为’AAA’,’AAB’,’BAA’,’BAB’ ,如果where code like ’%AB’条件，由于前面是
　　模糊的，所以不能利用索引的顺序，必须一个个去找，看是否满足条件。这样会导致全索引扫描或者全表扫
　　描。如果是这样的条件where code like ’A % ’，就可以查找CODE中A开头的CODE的位置，当碰到B开头的
　　数据时，就可以停止查找了，因为后面的数据一定不满足要求。这样就可以利用索引了。
4.索引失效的几种情况
　　1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
　　要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
　　2.对于多列索引，不是使用的第一部分，则不会使用索引
　　3.like查询以%开头
　　4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
　　5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
5.MySQL主要提供2种方式的索引：B-Tree索引，Hash索引
　　B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN)。相当于二分查找。
　　哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。
　　显然，如果值的差异性大，并且以等值查找（=、 、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。
　　如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。
***隐藏网址***

mysqldate_sub索引失效

因为该函数会对日期进行改变。根据查询相关公开信息显示，UNIX_TIMESTAMP函数可以替代date_sub函数，从而避免索引失效。

mysqlloaddata索引失效吗

是。在《mysqll》软件中进行oaddata索引超过1小时后会出现失效，工程中，零部件失去原有设计所规定的功能称为失效。失效包括完全丧失原定功能。

mysql 避免索引失效

where条件==》order by 条件==》group by 条件 按顺序遵守 最佳左前缀法则

假设创建了复合索引：a,b,c

不在索引列上做任何的操作（计算、函数、显式或隐式的类型转换），否则会导致索引失效而转向全表扫描

1、字符不加单引号会导致索引失效

name字段为varchar类型

这条sql发生了隐式的类型转换：数值==》字符串。所以导致了全表扫描，索引失效

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

mysql中的范围条件有：in/not in、 like、 《》 、BETWEEN AND ；

《》后面的索引失效

in会导致索引全部失效！！！

BETWEEN AND 范围条件不会导致索引失效！！！

尽量让索引列和查询列一致；减少select * 的使用

1、查询表结构

2、查询表的索引结构
联合索引：name,age,post；说明add_time字段没有添加索引

3、查看select * 的执行计划

4、查看 select name,age,pos的执行计划

5、如果select只用一部分索引
like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作。

解决：可以使用 覆盖索引 来解决这个问题！
1、先查看表上的索引
id、name、age、pos 四个字段上都有索引； 注意：name是联合索引中的第一个，带头大哥！

2、查看表结构
有个add_time字段没有用到索引

3、查看执行计划

使用UNION ALL

假设创建了联合索引 x(a,b,c)

ps：like虽然也是范围查询但是区别于》、《，%用在最前面就只用到索引a了；%用在最后面可以用到a+b+c！

下面的sql几乎违背了上面的所有原则，索引依然全部生效。因为select是索引覆盖的，select里不包含没有建立索引的字段。因此总是用到索引的。可以看出来索引覆盖在sql优化中的作用性

为什么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、联合索引：

索引删除