为什么MySQL字符串不加引号索引会失效？这个答案是我见过最靠谱
发布时间：2022-09-15 15:16:22 所属栏目：MySql教程 来源：
导读： 作为一名程序员，在求职面试时，不知你有没有遇到类似这样的问题。
张工是一名java程序员，最近到一家软件公司应聘软件开发岗位，面试官问了他关于MySql索引这样的一个问题。
面试官：为什么MyS
张工是一名java程序员，最近到一家软件公司应聘软件开发岗位，面试官问了他关于MySql索引这样的一个问题。
面试官：为什么MyS
|
作为一名程序员，在求职面试时，不知你有没有遇到类似这样的问题。 张工是一名java程序员，最近到一家软件公司应聘软件开发岗位，面试官问了他关于MySql索引这样的一个问题。 面试官：为什么MySQL字符串类型查询时不加引号索引会失效？ 张工：MySQL内部进行了隐式转换。 面试官：那为什么会发生隐式转换？ 张工：…… 对于这个问题张工之前在做项目时也曾遇到，那时候字段明明是加了索引，可不明白为什么还是很慢。后加上引号就正常了，为了赶项目进度，张工也没有再去留意。 现在面试官突然这么一问，张工也说不出个所以然来。 面试官让他回去等通知。 我们知道MySql索引可以加快数据检索速度，这也是使用的索引的最主要原因。但有时候使用不当就会遇到索引失效问题，譬如在MySQL字符串类型查询时不加引号索引会失效，是因为MySQL内部进行了隐式转换。 那为什么会发生隐式转换？又是怎么转换的呢？ 今天我们来聊聊关于MySql索引失效的话题。 先来看看一般导致索引失效的有哪些？ 1. 最佳左前缀法则 如果一张表的索引有多个，要遵守最佳左前缀法则，即查询从索引的最左前列开始并且不跳过索引中的列。 2.like查询使用不当会导致索引失效 用户表tb_user字段 id，name，age，sex 创建索引为idx_user_name 执行语句： select * from tb_user where name like ''%xiaoai"; 这时候就会导致索引失效 3.在索引列上做加工操作 在索引列上做加工操作，查询时会导致索引失效，从而导致全表扫描。所以，建议不要在索引列上做任何操作。 举个例子，例如订单表tb_order有个索引是dt（日期）， 字段数据存放的格式是这样的2021-12-10 这样的，如果有个需求需要根据dt，格式是20220207这样的来查询，这时候就不要对dt进行格式转换了， select * from tb_order where DATE_FORMAT(dt,'%Y%m%d') ='20220207' 这样索引就失效了。 而是应该对 20220207做格式处理 select * from tb_order where dt=DATE_FORMAT('20220207','%Y-%m-%d') 这样dt索引才不会失效。 4.查询条件两边数据类型不一致 例如我们在订单表tb_order建立了索引idx_order_id，order_id字段类型为varchar 在查询时如果使用where order_id= 20220207123654100，这样的查询方式会直接造成索引失效。 要让索引生效，正确的用法为 where order_id=’20220207123654100’ 5.范围条件之后的索引会失效 假如有张用户表tb_user，创建的索引为idx_user_name_age_sex_phone 其中name、age、sex都加了索引。 执行语句 select * from tb_user where name = 'xiaoai' and age > 18 and sex =0; 上面这条sql语句只会命中name和age索引，sex索引会失效，复合索引失效需要查看key_len的长度。 再来看一个例子： explain select * from tb_user where phone = 12345678936 explain select * from tb_user where phone = '12345678936' 从这两条SQL执行的结果我们可以看出，执行第一条SQL没有使用到索引，而执行第二条SQL时使用到了索引。这是为什么呢？ 我们需要先了解下mysql索引优化器工作的原理。选择索引是优化器工作，优化器工作有自己的一套规则，如果等号两边的数据类型不一致，则会发生隐式转换。 基于这条规则，我们回过头看看 explain select * from tb_user where phone = 12345678936; 这条SQL语句执行时就会变为 explain select * from tb_user where cast(phone as signed int) = 12345678936; 由于对索引列进行了函数操作，所以才导致索引失效，从而全表扫描了。 那么问题来了，细心的你不知有没有留意到为什么是把左侧的列转为int类型，而不是把右侧的值转成字符串类型呢？ 什么情况下把数字转为字符串，什么情况下把字符串转为数字，优化器它是根据什么规则来进行判断的？其实规则也并不复杂。 根据这个规则，我们再回过头看看之前的查询语句 select * from tb_user where phone = 12345678936 select '12345678936' = 12345678936 返回1 所以这时候就把左侧的列值12345678936转成数字。 关于MySql索引失效的问题先简单写到这，建议平时在做项目时还是要多了解下原理，如果你了解其背后的原理，求职面试时和面试官交流起来就会很舒服了，相信能为这次面试加分MySQL 索引，提高被录用的概率。 为什么MySQL字符串类型查询时不加引号索引会失效？这是因为要查询的字符串字段没有加引号时，MySQL内部进行了隐式转换，此次查询会导致全表扫描，所以慢了。 总结： 在索引列上进行了函数操作，MySQL内部会进行了隐式转换，导致索引失效，从而产生全表扫描。 由于笔者知识及水平有限,文中错漏之处在所难免,如有不足之处，欢迎交流。 拓展 索引创建 1、主键索引： alter table table_name add primary key (column) 2、唯一索引： alter table table_name add unique (column) 3、普通索引： alter table table_name add index index_name (column) 4、全文索引： alter table table_name add fulltext (column) 5、联合索引： alter table table_name add index index_name (column1,column2,column3) 索引删除 alter table table_name drop index index_name; （编辑：云计算网_汕头站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
浙公网安备 33038102330478号