扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
2020-02-27
创新互联从2013年创立，先为元宝山等服务建站，元宝山等地企业，进行企业商务咨询服务。为元宝山企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
最近一个日志页面查询很慢，然后去跟踪了查询sql，发现日期字段上即使建了索引，查询还是很慢，执行语句还是使用了全表扫描，于是继续分析下去。
查询语句类似:
select * from logs where createtime = '2020-01-01' ;
起初因为date上没检索，查询执行的是全表扫描，给条件字段createtime建上索引：
再次执行:
查询执行的还是全表扫描：
网上查询有说是因为在查询数据条数约占总条数五分之一以下时能够使用到索引，但超过五分之一时，使用全表扫描。于是把日期范围缩小：
果真，查询执行的是range：
由此可知，在进行范围查询时，比如：、 、=、=等, 如果数据量过大的话，即使where条件字段已经建立了索引，查询语句执行时还是有可能进行全表扫描的。
实际上是不是全表的五分之一以下才会使用索引，这个不能确定，以后再研究了。
作为一名程序员，在求职面试时，不知你有没有遇到类似这样的问题。
张工是一名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、联合索引：
索引删除
mysqlunionall无法走索引11 27
1. like %%失效。 方案:改为like %,只写后面的%就能走索引。
2. 虽然有索引,但是查询条件没有索引列或者order by 排序没有索引列。 方案:让查询条件有索引列
3. 索引列存在null值的情况。 方案:索引列如果没有值,则给空字符串或者数字的0,总之就是不要设置null
不一定，要看情况，具体是由MySQL优化器内部决定是全表扫描还是索引查找，用效率较高的一种方式。
针对索引字段的唯一性不高的情况下（索引的"区分度"低），优化器可能会选择全表扫描，而不是走索引。这可能是因为等值查询符合条件的记录太多了，导致了mysql认为全表扫描比用索引查找更快。
比如你对唯一性不高的字段（如性别：男/女）加了索引，这样通过索引去查找可能还需回表，还不如直接全表扫描！
若in中的数据量较大时，基本就不走索引了。如果你索引字段是一个unique，in可能就会用到索引。
如果你一定要用索引，可以用 force index。可能也和MySQL版本有关（5.6以后有做in的查询优化）
1、如果MySQL估计使用索引比全表扫描更慢，则不适用索引，
ex：列key_part1均匀的分布在1-100之间。下面的sql则不会使用索引
select * from table_name where key_part1 1 and key_part1 90
2、如果使用memory/heap表，并且where语句中不适用“=”进行索引，则不会使用索引。heap表只有在“=”的条件下，才使用索引。
3、用or分割开的条件，如果or左右两个条件中有一个列没有索引，则不会使用索引。
ex：select * from table_name where key1='a' or key2='b'；
如果在key1上有索引而在key2上没有索引，则该查询也不会走索引
4、复合索引，如果索引列不是复合索引的第一部分，则不使用索引（即不符合最左前缀）
ex：复合索引为(key1,key2) ，一下sql将不会使用索引
select * from table_name where key2='b'；
5、如果like是以‘%’开始的，则该列上的索引不会被使用。
ex：select * from table_name where key1 like '%a'；
6.如果列为字符串，则where条件中必须将字符常量值加引号，否则即使该列上存在索引，也不会被使用。
ex：select * from table_name where key1=1;
如果key1列保存的是字符串，即使key1上有索引，也不会被使用。
从上面可以看出，即使我们建立了索引，也不一定会被使用，那么我们如何知道我们索引的使用情况呢？？在MySQL中，有Handler_read_key和Handler_read_rnd_key两个变量，如果Handler_read_key值很高而Handler_read_rnd_key的值很低，则表明索引经常不被使用，应该重新考虑建立索引。可以通过:show status like 'Handler_read%'来查看着连个参数的值.
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网