mysql两个字段不相等的sql语句_MySQL索引失效问题
索引在我们使⽤MySQL数据库时可以极⼤的提⾼查询效率，然⽽，有时候因为使⽤上的⼀些瑕疵就会导致索引的失效，⽆法达到我们使⽤索引的预期效果，今天介绍⼏种MySQL中⼏种常见的索引失效的原因，可以在以后的⼯作中尽可能避免因索引失效带来的坑。
⼀、被索引字段，发⽣了隐式类型转换
insert语句字段顺序MySQL在sql执⾏过程中，会将sql语句中与字段原类型不匹配的值，进⾏⼀个类型转换
看个例⼦说明，我们创建⼀个user表，并且添加⼀个主键id索引，两个⼆级索引age和phone
CREATE TABLE `t_user`(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`age` int(10) NOT NULL,
`phone` varchar(30) NOT NULL,
PRIMARY KEY(`id`),
KEY `idx_age` (`age`),
KEY `ids_phone` (`phone`)
) ENGINE=INNODB
#插⼊⼀条数据
insert into t_user (`name`,`age`,`phone`) values('zhangsan','20','133********')
我们执⾏⼀个查询sql看⼀下结果
select * from t_user where phone = 133********;
我们可以看到虽然查询语句中的phone的值是⼀个数字类型的值，与phone的字符串类型不匹配，依然可以查到我们想要的结果，但是在sql执⾏过程中并没有使⽤到索引。
我们可以通过MySQL的explain关键字来分析SQL语句执⾏的细节。在explain的分析结果中有⼀条结果是key，这代表的是使⽤的哪个索引，我们可以看到key的值是null说明这条SQL语句在执⾏过程中并没有⽤到索引。
我们将SQL语句修改⼀下，将phone字段的值修改成⼀个字符串，再来执⾏⼀下，分析⼀下SQL语句执⾏的细节。
select * from t_user where phone = "133********";
我们可以看到修改后的SQL语句在执⾏过程中使⽤到了索引，这个是因为SQL语句中的数据类型与phone字段本事的类型⼀致，就不需要进⾏类型转换，是可以使⽤到索引的，所以代表使⽤索引的key是idx_phone
通过这个例⼦我们可以知道，在SQL语句中被索引字段与所对应值的类型不匹配时，在SQL语句执⾏过程中，会进⾏隐式类型转换，会导致这个索引变得失效。
⼆、被索引字段使⽤了表达式计算
还是使⽤刚刚的数据表，我们再来看⼀个例⼦，来查询年龄超过18岁⼜刚好满2年的⼈
select * from t_user where age -2 = 18;
在这个sql中age字段⽤到了表达式计算，执⾏会发现是可以正常执⾏的，但是这是⼀种错误的⽰范，我们⽤explain关键字分析这条SQL的时候，会发现这个查询并没有使⽤我们添加的age字段的索引。
然后我们换⼀种写法，让age直接等于20
select * from t_user where age = 20;
再来使⽤explain关键字分析SQL执⾏过程，会发现key值变成了idx_age
这个例⼦说明了，SQL查询语句中，如果被索引字段进⾏了表达式计算，也会引起索引的失效。
三、被索引字段使⽤了函数
还是使⽤刚刚的t_user表，我们来查询电话以133开头的⽤户
select * from t_user where left(phone,3) = '133';
执⾏SQL我们可以看到是正确的。
使⽤explain查看⼀下SQL执⾏情况。
可以看到key值为null并没有使⽤到我们添加的索引，所以以上是个错误⽰范，我们修改⼀下SQL再来看⼀下执⾏情况。
可以看到key值为null并没有使⽤到我们添加的索引，所以以上是个错误⽰范，我们修改⼀下SQL再来看⼀下执⾏情况。
修改后的SQL中索引字段没有⽤到函数，key值为idx_phone正确的使⽤到了我们添加的索引。
当被索引字段使⽤到了函数，这个索引字段上的索引也会失效。
⼩结
以上三种索引失效的情况可以归于⼀类，进⾏⼀下总结，被索引字段的隐式转换、被索引字段的表达式计算、被索引字段使⽤函数，都会引起索引字段对应的索引发⽣失效，这是因为索引的使⽤是依赖于B-tree索引树的遍历，⽽索引树的遍历是依赖于索引树底层叶⼦节点的有序性，当被索引字段进⾏了隐式类型转换、表达式计算或函数计算后，有可能这个字段新的排列顺序和原来在索引树的叶⼦节点层的排列顺序不⼀样了，这就破坏了索引树叶⼦节点层的有序性，当SQL语句被执⾏时，MySQL数据库的SQL语句执⾏器就⽆法判断原来的索引树是否还能被检索使⽤，所以就是SQL执⾏器不使⽤该索引了，⽽我们看到的就是我们期望使⽤的索引失效了。
四、在like关键字后使⽤左模糊匹配'%##'
还是使⽤刚刚的数据表，我们新增⼀列address，并建⽴idx_address索引。
CREATE TABLE `t_user`(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`age` int(10) NOT NULL,
`phone` varchar(30) NOT NULL,
`address` varchar(255) NOT NULL，
PRIMARY KEY(`id`),
KEY `idx_age` (`age`),
KEY `idx_phone` (`phone`),
KEY `idx_address` (`address`)
) ENGINE=INNODB
然后执⾏SQL，查询⽤户地址中包含“海淀区”关键字的⽤户
通过explain关键字查看SQL执⾏情况发现address添加的索引并没有使⽤，我们修改SQL，去掉右模糊，只使⽤左模糊查询，然后分析SQL执⾏情况，发现结果还是⼀样的，没有使⽤索引。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。