湖南常德广宇建设网站,做网站公司哪个比较好,阿里云安装网站,周口住房和城乡建设网站前言今天我接着上一期数据库的话题#xff0c;更进一步聊聊索引的相关问题#xff0c;因为索引是大家都比较关心的公共话题#xff0c;确实有很多坑。不知道你在实际工作中#xff0c;有没有遇到过下面的这两种情况#xff1a;明明在某个字段上加了索引#xff0c;但实际… 前言今天我接着上一期数据库的话题更进一步聊聊索引的相关问题因为索引是大家都比较关心的公共话题确实有很多坑。不知道你在实际工作中有没有遇到过下面的这两种情况明明在某个字段上加了索引但实际上并没有生效。索引有时候生效了有时候没有生效。今天就跟大家一起聊聊mysql数据库索引失效的10种场景给曾经踩过坑或者即将要踩坑的朋友们一个参考。1. 准备工作所谓空口无凭如果我直接把索引失效的这些场景丢出来可能没有任何说服力。所以我决定建表和造数据给大家一步步演示效果尽量做到有理有据。我相信如果大家耐心的看完这篇文章一定会有很多收获的。1.1 创建user表创建一张user表表中包含id、code、age、name和height字段。CREATE TABLE user (id int NOT NULL AUTO_INCREMENT,code varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,age int DEFAULT 0,name varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,height int DEFAULT 0,address varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,PRIMARY KEY (id),KEY idx_code_age_name (code,age,name),KEY idx_height (height)
) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_bin此外还创建了三个索引id数据库的主键idx_code_age_name由code、age和name三个字段组成的联合索引。idx_height普通索引1.2 插入数据为了方便给大家做演示我特意向user表中插入了3条数据INSERT INTO sue.user (id, code, age, name, height) VALUES (1, 101, 21, 周星驰, 175,香港);
INSERT INTO sue.user (id, code, age, name, height) VALUES (2, 102, 18, 周杰伦, 173,台湾);
INSERT INTO sue.user (id, code, age, name, height) VALUES (3, 103, 23, 苏三, 174,成都);周星驰和周杰伦是我偶像在这里自恋了一次把他们和我放到一起了。哈哈哈。1.3 查看数据库版本为了防止以后出现不必要的误会在这里有必要查一下当前数据库的版本。不说版本就直接给结论是耍流氓哈哈哈。select version();查出当前的mysql版本号为8.0.211.4 查看执行计划在mysql中如果你想查看某条sql语句是否使用了索引或者已建好的索引是否失效可以通过explain关键字查看该sql语句的执行计划来判断索引使用情况。例如explain select * from user where id1;执行结果从图中可以看出由于id字段是主键该sql语句用到了主键索引。当然如果你想更深入的了解explain关键字的用法可以看看我的另一篇文章《explain | 索引优化的这把绝世好剑你真的会用吗》里面更为详细的介绍。2. 不满足最左匹配原则之前我已经给code、age和name这3个字段建好联合索引idx_code_age_name。该索引字段的顺序是codeagename如果在使用联合索引时没注意最左前缀原则很有可能导致索引失效喔不信我们一起往下看。2.1 哪些情况索引有效先看看哪些情况下能走索引。explain select * from user
where code101;explain select * from user
where code101 and age21explain select * from user
where code101 and age21 and name周星驰;执行结果上面三种情况sql都能正常走索引。其实还有一种比较特殊的场景explain select * from user
where code 101 and name周星驰;执行结果查询条件原本的顺序是code、age、name但这里只有code和name中间断层了掉了age字段这种情况也能走code字段上的索引。看到这里不知道聪明的你有没有发现这样一个规律这4条sql中都有code字段它是索引字段中的第一个字段也就是最左边的字段。只要有这个字段在该sql已经就能走索引。这就是我们所说的最左匹配原则。2.2 哪些情况索引失效前面我已经介绍过建立了联合索引后在查询条件中有哪些情况索引是有效的。接下来我们重点看看哪些情况下索引会失效。explain select * from user
where age21;explain select * from user
where name周星驰;explain select * from user
where age21 and name周星驰;执行结果从图中看出这3种情况下索引确实失效了。说明以上3种情况不满足最左匹配原则说白了是因为查询条件中没有包含给定字段最左边的索引字段即字段code。3. 使用了select *在《阿里巴巴开发手册》中明确说过查询sql中禁止使用select * 。那么你知道为什么吗废话不多说按照国际惯例先上一条sqlexplain
select * from user where name苏三;执行结果在该sql中用了select *从执行结果看走了全表扫描没有用到任何索引查询效率是非常低的。如果查询的时候只查我们真正需要的列而不查所有列结果会怎么样非常快速的将上面的sql改成只查了code和name列太easy了explain
select code,name from user
where name苏三;执行结果从图中执行结果不难看出该sql语句这次走了全索引扫描比全表扫描效率更高。其实这里用到了覆盖索引。如果select语句中的查询列都是索引列那么这些列被称为覆盖索引。这种情况下查询的相关字段都能走索引索引查询效率相对来说更高一些。而使用select *查询所有列的数据大概率会查询非索引列的数据非索引列不会走索引查询效率非常低。4. 索引列上有计算介绍本章节内容前先跟大家一起回顾一下根据id查询数据的sql语句explain select * from user where id1;执行结果从图中可以看出由于id字段是主键该sql语句用到了主键索引。但如果id列上面有计算比如explain select * from user where id12;执行结果从上图中的执行结果能够非常清楚的看出该id字段的主键索引在有计算的情况下失效了。5. 索引列用了函数有时候我们在某条sql语句的查询条件中需要使用函数比如截取某个字段的长度。假如现在有个需求想查出所有身高是17开头的人如果sql语句写成这样explain select * from user where height17;该sql语句确实用到了普通索引但该sql语句肯定是有问题的因为它只能查出身高正好等于17的但对于174这种情况它没办法查出来。为了满足上面的要求我们需要把sql语句稍稍改造了一下explain select * from user where SUBSTR(height,1,2)17;这时需要用到SUBSTR函数用它截取了height字段的前面两位字符从第一个字符开始。执行结果你有没有发现在使用该函数之后该sql语句竟然走了全表扫描索引失效了。6. 字段类型不同在sql语句中因为字段类型不同而导致索引失效的问题很容易遇到可能是我们日常工作中最容易忽略的问题。到底怎么回事呢请大家注意观察一下t_user表中的code字段它是varchar字符类型的。在sql语句中查询数据时查询条件我们可以写成这样explain
select * from user where code101;执行结果从上图中看到该code字段走了索引。温馨提醒一下查询字符字段时用双引号“和单引号都可以。但如果你在写sql时不小心把引号弄掉了把sql语句变成了explain
select * from user where code101;执行结果你会惊奇的发现该sql语句竟然变成了全表扫描。因为少写了引号这种小小的失误竟然让code字段上的索引失效了。这时你心里可能有一万个为什么其中有一个肯定是为什么索引会失效呢答因为code字段的类型是varchar而传参的类型是int两种类型不同。此外还有一个有趣的现象如果int类型的height字段在查询时加了引号条件却还可以走索引explain select * from user
where height175;执行结果从图中看出该sql语句确实走了索引。int类型的参数不管在查询时加没加引号都能走索引。这是变魔术吗这不科学呀。答mysql发现如果是int类型字段作为查询条件时它会自动将该字段的传参进行隐式转换把字符串转换成int类型。mysql会把上面列子中的字符串175转换成数字175所以仍然能走索引。接下来看一个更有趣的sql语句select 1 1;它的执行结果是2还是11呢好吧不卖关子了直接公布答案执行结果是2。mysql自动把字符串1转换成了int类型的1然后变成了112。但如果你确实想拼接字符串该怎么办答可以使用concat关键字。具体拼接sql如下select concat(1,1);接下来关键问题来了为什么字符串类型的字段传入了int类型的参数时索引会失效呢答根据mysql官网上解释字符串1、 1 、1a都能转换成int类型的1也就是说可能会出现多个字符串对应一个int类型参数的情况。那么mysql怎么知道该把int类型的1转换成哪种字符串用哪个索引快速查值?感兴趣的小伙伴可以再看看官方文档https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html7. like左边包含%模糊查询在我们日常的工作中使用频率还是比较高的。比如现在有个需求想查询姓李的同学有哪些?使用like语句可以很快的实现select * from user where name like 李%;但如果like用的不好就可能会出现性能问题因为有时候它的索引会失效。不信我们一起往下看。目前like查询主要有三种情况like %alike a%like %a%假如现在有个需求想查出所有code是10开头的用户。这个需求太简单了吧sql语句如下explain select * from user
where code like 10%;执行结果图中看出这种%在10右边时走了索引。而如果把需求改了想出现出所有code是1结尾的用户。查询sql语句改为explain select * from user
where code like %1;执行结果从图中看出这种%在1左边时code字段上索引失效了该sql变成了全表扫描。此外如果出现以下sqlexplain select * from user
where code like %1%;该sql语句的索引也会失效。下面用一句话总结一下规律当like语句中的%出现在查询条件的右边时索引会失效。那么为什么会出现这种现象呢答其实很好理解索引就像字典中的目录。一般目录是按字母或者拼音从小到大从左到右排序是有顺序的。我们在查目录时通常会先从左边第一个字母进行匹对如果相同再匹对左边第二个字母如果再相同匹对其他的字母以此类推。通过这种方式我们能快速锁定一个具体的目录或者缩小目录的范围。但如果你硬要跟目录的设计反着来先从字典目录右边匹配第一个字母这画面你可以自行脑补一下你眼中可能只剩下绝望了哈哈。8. 列对比上面的内容都是常规需求接下来来点不一样的。假如我们现在有这样一个需求过滤出表中某两列值相同的记录。比如user表中id字段和height字段查询出这两个字段中值相同的记录。这个需求很简单sql可以这样写explain select * from user
where idheight执行结果意不意外惊不惊喜索引失效了。为什么会出现这种结果id字段本身是有主键索引的同时height字段也建了普通索引的并且两个字段都是int类型类型是一样的。但如果把两个单独建了索引的列用来做列对比时索引会失效。感兴趣的朋友可以找我私聊。9. 使用or关键字我们平时在写查询sql时使用or关键字的场景非常多但如果你稍不注意就可能让已有的索引失效。不信一起往下面看。某天你遇到这样一个需求想查一下id1或者height175的用户。你三下五除二就把sql写好了explain select * from user
where id1 or height175;执行结果没错这次确实走了索引恭喜被你蒙对了因为刚好id和height字段都建了索引。但接下来的一个夜黑风高的晚上需求改了除了前面的查询条件之后还想加一个address成都。这还不简单sql走起explain select * from user
where id1 or height175 or address成都;执行结果结果悲剧了之前的索引都失效了。你可能一脸懵逼为什么我做了什么答因为你最后加的address字段没有加索引从而导致其他字段的索引都失效了。注意如果使用了or关键字那么它前面和后面的字段都要加索引不然所有的索引都会失效这是一个大坑。10. not in和not exists在我们日常工作中用得也比较多的还有范围查询常见的有inexistsnot innot existsbetween and今天重点聊聊前面四种。10.1 in关键字假如我们想查出height在某些范围之内的用户这时sql语句可以这样写explain select * from user
where height in (173,174,175,176);执行结果从图中可以看出sql语句中用in关键字是走了索引的。10.2 exists关键字有时候使用in关键字时性能不好这时就能用exists关键字优化sql了该关键字能达到in关键字相同的效果explain select * from user t1
where exists (select 1 from user t2 where t2.height173 and t1.idt2.id)执行结果从图中可以看出用exists关键字同样走了索引。10.3 not in关键字上面演示的两个例子是正向的范围即在某些范围之内。那么反向的范围即不在某些范围之内能走索引不话不多说先看看使用not in的情况explain select * from user
where height not in (173,174,175,176);执行结果你没看错索引失效了。看如果现在需求改了想查一下id不等于1、2、3的用户有哪些这时sql语句可以改成这样explain select * from user
where id not in (173,174,175,176);执行结果你可能会惊奇的发现主键字段中使用not in关键字查询数据范围任然可以走索引。而普通索引字段使用了not in关键字查询数据范围索引会失效。10.4 not exists关键字除此之外如果sql语句中使用not exists时索引也会失效。具体sql语句如下explain select * from user t1
where not exists (select 1 from user t2 where t2.height173 and t1.idt2.id)执行结果从图中看出sql语句中使用not exists关键后t1表走了全表扫描并没有走索引。11. order by的坑在sql语句中对查询结果进行排序是非常常见的需求一般情况下我们用关键字order by就能搞定。但我始终觉得order by挺难用的它跟where或者limit关键字有很多千丝万缕的联系一不小心就会出问题。Let go11.1 哪些情况走索引首先当然要温柔一点一起看看order by的哪些情况可以走索引。我之前说过在code、age和name这3个字段上已经建了联合索引idx_code_age_name。11.1.1 满足最左匹配原则order by后面的条件也要遵循联合索引的最左匹配原则。具体有以下sqlexplain select * from user
order by code limit 100;explain select * from user
order by code,age limit 100;explain select * from user
order by code,age,name limit 100;执行结果从图中看出这3条sql都能够正常走索引。除了遵循最左匹配原则之外有个非常关键的地方是后面还是加了limit关键字如果不加它索引会失效。11.1.2 配合where一起使用order by还能配合where一起遵循最左匹配原则。explain select * from user
where code101
order by age;执行结果code是联合索引的第一个字段在where中使用了而age是联合索引的第二个字段在order by中接着使用。假如中间断层了sql语句变成这样执行结果会是什么呢explain select * from user
where code101
order by name;执行结果虽说name是联合索引的第三个字段但根据最左匹配原则该sql语句依然能走索引因为最左边的第一个字段code在where中使用了。只不过order by的时候排序效率比较低需要走一次filesort排序罢了。11.1.3 相同的排序order by后面如果包含了联合索引的多个排序字段只要它们的排序规律是相同的要么同时升序要么同时降序也可以走索引。具体sql如下explain select * from user
order by code desc,age desc limit 100;执行结果该示例中order by后面的code和age字段都用了降序所以依然走了索引。11.1.4 两者都有如果某个联合索引字段在where和order by中都有结果会怎么样explain select * from user
where code101
order by code, name;执行结果code字段在where和order by中都有对于这种情况从图中的结果看出还是能走了索引的。11.2 哪些情况不走索引前面介绍的都是正面的用法是为了让大家更容易接受下面反面的用法。好了接下来重点聊聊order by的哪些情况下不走索引11.2.1 没加where或limit如果order by语句中没有加where或limit关键字该sql语句将不会走索引。explain select * from user
order by code, name;执行结果从图中看出索引真的失效了。11.2.2 对不同的索引做order by前面介绍的基本都是联合索引这一个索引的情况。但如果对多个索引进行order by结果会怎么样呢explain select * from user
order by code, height limit 100;执行结果从图中看出索引也失效了。11.2.3 不满足最左匹配原则前面已经介绍过order by如果满足最左匹配原则还是会走索引。下面看看不满足最左匹配原则的情况explain select * from user
order by name limit 100;执行结果name字段是联合索引的第三个字段从图中看出如果order by不满足最左匹配原则确实不会走索引。11.2.4 不同的排序前面已经介绍过如果order by后面有一个联合索引的多个字段它们具有相同排序规则那么会走索引。但如果它们有不同的排序规则呢explain select * from user
order by code asc,age desc limit 100;执行结果从图中看出尽管order by后面的code和age字段遵循了最左匹配原则但由于一个字段是用的升序另一个字段用的降序最终会导致索引失效。好了今天分享的内容就先到这里我们下期再见。最后说一句(求关注别白嫖我)如果这篇文章对您有所帮助或者有所启发的话帮忙扫描下发二维码关注一下您的支持是我坚持写作最大的动力。求一键三连点赞、转发、在看。往期推荐保姆级教程终于搞懂脏读、幻读和不可重复读了经典回顾查询MySQL字段注释的 5 种方法面试官this和super有什么区别this能调用到父类吗
