欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

《MySQL面试小抄》索引失效场景验证

程序员文章站 2022-06-25 09:55:16
面试官考点之谈谈索引维护过程?页分裂?页合并?面试官考点之简述一下查询时B+树索引搜索过程?面试官考点之什么是回表?面试官考点之什么是索引覆盖?使用场景?面试官考点之什么情况下会索引失效?面试官考点之哪些情况下,可能会面临索引失效的问题?面试官考点之or走索引和索引失效分别是什么场景?... ......

我是肥哥,一名不专业的面试官!

我是囧囧,一名积极找工作的小菜鸟!

囧囧表示:小白面试最怕的就是面试官问的知识点太笼统,自己无法快速定位到关键问题点!!!


本期主要面试考点

面试官考点之什么情况下会索引失效?

本期验证以下索引失效的常见场景

1、like通配符,左侧开放情况下,全表扫描
2、or条件筛选,可能会导致索引失效
3、where中对索引列使用mysql的内置函数,一定失效
4、where中对索引列进行运算(如,+、-、*、/),一定失效
5、类型不一致,隐式的类型转换,导致的索引失效
6、where语句中索引列使用了负向查询,可能会导致索引失效。负向查询包括:not、!=、<>、!<、!>、not in、not like等,其中:!< !> sqlserver语法。
7、索引字段可以为null,使用is null或is not null时,可能会导致索引失效
8、隐式字符编码转换导致的索引失效
9、联合索引中,where中索引列违背最左匹配原则,一定会导致索引失效
10、mysql优化器的最终选择,不走索引

《MySQL面试小抄》索引失效场景验证

《MySQL面试小抄》索引失效场景验证

验证准备

准备数据表,同时建立普通索引 idx_user_name

create table `t_user` (
  `id` int(11) not null,
  `user_name` varchar(32) character default null comment '用户名',
  `address` varchar(255) character default null comment '地址',
  `create_time` datetime default null on update current_timestamp comment '创建时间',
  primary key (`id`)
) engine=innodb default charset=utf8;

插入1万条数据(注意:数据多些,mysql不走索引情况之一是数据量非常少,mysql查询优化器认为全表扫描比使用索引更快,导致索引失效,explain检查是否使用索引时,发现无法走索引)

-- 创建存储过程,插入10000用户信息
create procedure user_insert()
-- 定义存储过程开始
begin
	-- 定义变量 i ,int 类型,默认值为 1
	declare i int default 1;
	
	while i <= 10000
		-- 定义循环内执行命令
		do insert into t_user(id, user_name, address, create_time) values(i, concat('mayun', i), concat('浙江杭州', i), now());
		set i=i+1;
	end while;
	
	commit;
end;
-- 定义存储过程结束


-- 调用存储工程
call user_insert();

一、or索引失效验证

好多人说where条件中使用 or ,那么索引一定失效,是否正确?

《MySQL面试小抄》索引失效场景验证

or 连接的是同一个字段,相同走索引

explain select * from t_user where user_name = 'mayun10' or user_name = 'mayun1000'

《MySQL面试小抄》索引失效场景验证

or 连接的是两个不同字段,不同索引失效

explain select * from t_user where user_name = 'mayun10' or address = '浙江杭州12'

《MySQL面试小抄》索引失效场景验证

给address列增加索引

alter table t_user add index idx_address (address)

or 连接的是两个不同字段,如果两个字段皆有索引,走索引

《MySQL面试小抄》索引失效场景验证

验证总结

or 可能会导致索引失效,并非一定,这里涉及到mysql index merge 技术。

1、mysql5.0之前,查询时一个表一次只能使用一个索引,无法同时使用多个索引分别进行条件扫描。

2、但是从5.1开始,mysql引入了 index merge 优化技术,对同一个表可以使用多个索引分别进行条件扫描。然后将它们各自的结果进行合并(intersect/union)。

or索引生效有哪些情况?

第一种 or两边连接的是同一个索引字段

第二种 or两边连接的是两个索引字段,即两个字段分别都建立了索引

二、like通配符索引失效验证

一个最常见的查询场景,建立idx_user_name索引

select * from t_user where user_name like '%mayun100%';

这条查询是否走索引?

《MySQL面试小抄》索引失效场景验证

select * from t_user where user_name like 'mayun100%';

这条查询是否走索引?

《MySQL面试小抄》索引失效场景验证

验证总结

like 通配符特性是可以左右开闭匹配查询当左边开放使用 % 或者 _ 匹配的时候都不会走索引,会进行全表扫描

为什么左开情况下会索引失效?请介绍一下原理!

我们知道建立索引后,mysql会建立一棵有序的b+tree,索引树是有序的,索引列进行查询匹配时是从左到右进行匹配。使用 % 和 _ 匹配,这表示左边匹配值是不确定的。不确定,意味着充满可能,怎么比较?

当然只能一个一个的比较,那就相当于,全匹配了,全匹配在优化器看来,与其走索引树查询,再进行不断的回表操作,还不如直接进行全表扫描划算!

三、where中对索引列使用mysql的内置函数

建立 idx_age 索引,

alter table t_user add index idx_age(age);

不使用内置函数

explain select * from t_user where age = 80

《MySQL面试小抄》索引失效场景验证

使用内置函数

explain select * from t_user where abs(age) = 80

《MySQL面试小抄》索引失效场景验证

验证总结

如果对索引字段做了函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。

mysql 无法再使用索引快速定位功能,而只能使用全索引扫描。

四、where中对索引列进行运算(如,+、-、*、/),一定失效

不涉及索引列的运算

alter table t_user add index idx_age(age);explain select * from t_user where age = 80;

《MySQL面试小抄》索引失效场景验证

索引列进行运算操作

explain select * from t_user where age + 5 = 80

《MySQL面试小抄》索引失效场景验证

五、类型不一致,隐式的类型转换,导致的索引失效

alter table t_user add index idx_user_name(user_name);explain select * from t_user where user_name = 'mayun1';

《MySQL面试小抄》索引失效场景验证

修改数据,再次explain

update t_user set user_name = '100' where user_name = 'mayun1';explain select * from t_user where user_name = 100;

user_name = 100 ,因为user_name 字段定义的是varchar,索引在where进行匹配时会先隐式调用 case() 函数进行类型转换 将匹配条件变成,user_name = '100'

《MySQL面试小抄》索引失效场景验证

六、where语句中索引列使用了负向查询,可能会导致索引失效。

负向查询包括:not、!=、<>、!<、!>、not in、not like等,其中:!< !> sqlserver语法。

alter table t_user add index idx_age(age);explain select * from t_user where age in (100, 50);

《MySQL面试小抄》索引失效场景验证

explain select * from t_user where age not in (100, 50);

《MySQL面试小抄》索引失效场景验证

七、索引字段可以为null,使用is null或is not null时,可能会导致索引失效

第一种情况,表结构规定允许user_name 字段可以为null

《MySQL面试小抄》索引失效场景验证

explain select * from t_user where user_name is null;

《MySQL面试小抄》索引失效场景验证

explain select * from t_user where user_name is not null;

《MySQL面试小抄》索引失效场景验证

第二种情况,表结构规定user_name 字段不可以为null

《MySQL面试小抄》索引失效场景验证

explain select * from t_user where user_name is null;

《MySQL面试小抄》索引失效场景验证

explain select * from t_user where user_name is not null;

《MySQL面试小抄》索引失效场景验证

八、隐式字符编码转换导致的索引失效

当两个表进行连接join 时,如果两张表的字符编码不同,可能会导致索引失效。

这个索引失效场景尚未遇到,网上很多文章说会导致索引失效,查阅发现大量的博客说utf8mb4字符集的表mb4与utf8字符集的表utf8 关联会产生索引失效的问题,但是我根据大量博文所述操作,发现暂时还是无法复现,读者可自行查阅。

如果读者复现到此场景,欢迎评论讨论或关注如果读者复现到此场景,欢迎评论或关注公众号囧么肥事讨论

九、联合索引中,where中索引列违背最左匹配原则,一定会导致索引失效

创建联合做引 idx_user_name_deposit, 遵循最左匹配原则

alter table t_user add index idx_user_name_deposit(user_name, deposit);explain select * from t_user where user_name like 'mayun86%'

《MySQL面试小抄》索引失效场景验证

遵循最左匹配之 a b 类型

explain select * from t_user where user_name like 'mayun86%' and deposit = 5620.26;

《MySQL面试小抄》索引失效场景验证

调换索引位置,测试联合索引书写规则

explain select * from t_user where deposit = 5620.26 and user_name like 'mayun86%';

《MySQL面试小抄》索引失效场景验证

违反最左匹配原则

explain select * from t_user where deposit = 5620.26;

《MySQL面试小抄》索引失效场景验证

验证总结

联合索引依据最左匹配原则建立索引树,在查询时依据联合索引顺序依次匹配索引值,查询时如果违背最左匹配原则,将导致索引失效。

假设建立索引 idx_a_b_c,相当于建立了 (a), (a,b), (a,b,c)三个索引查询匹配时匹配顺序是 a b c 查询时如果没有 a 字段筛选,那么索引将失效

举栗子,走索引情况

select * from test where a=1 select * from test where a=1 and b=2 select * from test where a=1 and b=2 and c=3

索引失效呢?

select * from test where b=2 and c=3

联合索引如果要走索引,查询条件中必须要包含第一个索引,否则索引失效

select * from test where b=1 and a=1select * from test where m='222' and a=1

这两条查询走索引的原因是什么?

最左前缀指的是查询时匹配索引列要按照联合索引创建的顺序,但是在书写时不需要严格按照联合索引创建的顺序,mysql优化器会自动调整,所以上面两条查询索引有效!

十、mysql优化器的最终选择,不走索引

explain select * from t_user where age > 59;

《MySQL面试小抄》索引失效场景验证

explain select * from t_user where age > 99;

《MySQL面试小抄》索引失效场景验证

验证总结

mysql查询索引失效的情况有很多,即使其他情况都规避,但是在经过了优化器的确定查询方案的时候,依然可能索引失效。

优化器会考虑查询成本,来确认它认为的最佳方案来执行查询

当数据量较少,或者需要访问行很多的时候

优化器会认为走索引树来进行回表,还不如直接进行全表扫描的时候,优化器将会抛弃走索引树。

随缘更新,大神请绕路!

更多精彩内容,欢迎关注微信公众号:囧么肥事 (或搜索:jiongmefeishi)

《MySQL面试小抄》索引失效场景验证