系列十二、索引实战

2023-12-20 13:04:09

一、索引实战

1.1、前置说明

??????前边的系列文章中是基于Linux中的MySQL进行案例演示的,为了后续测试百万条数据的sql性能分析,接下来的案例将会在Windows的MySQL中进行演示,MySQL版本要求需在8.0以上,我的MySQL版本信息如下:

1.2、数据初始化

链接:https://pan.baidu.com/s/1TGLDb9awudyjhZDcu0HNfQ?pwd=yyds?
提取码:yyds?

?百万数据初始化:

前置说明

????????-- 查询是否开启了加载本地文件(默认没有开启,需要开启才能执行load指令)
????????show variables like 'local_infile';
????????-- 开启加载本地文件
????????set global local_infile=on;
????????-- 执行load指令(依次执行tb_sku1、tb_sku2、...tb_sku5,执行一个脚本差不多耗费100s,耐心等待即可!)
????????load data local infile 'D:/temp/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

1.3、案例

?创建索引:

(1)name字段为姓名字段,该字段的值可能会重复,为该字段创建普通索引;

create index idx_user_name on user(name);

(2)phone字段为手机号码,非空且唯一,为该字段创建唯一索引

create unique index idx_user_phone on user(phone);

(3)为profession、age、status字段创建联合索引;

create index idx_user_profession_age_status on user(profession,age,status);

(4)为email字段建立普通索引

create index idx_user_email on user(email);

查看索引:查看user表中所有的索引数据;

show index from user;

1.4、SQL性能分析

1.4.1、SQL执行频率

概述:

? ? ? ? MySQL客户端连接成功后,通过show [session|global] status 命令可以查询服务器的状态信息,通过如下指令可以查看当前数据库的insert、update、delete、select的访问频次。

查看当前数据库以哪种业务为主:

show session status like 'Com_______';

????????通过上述指令的执行结果,可以分析出当前业务是以查询为主还是以增删改为主,从而为数据库的优化提供参考依据,如果是以增删改为主,就可以考虑不对其进行索引优化了,如果是以查询为主,那么就要考虑对索引进行优化了。

说明:

? ? ? ? Com_insert:插入次数

????????Com_update:更新次数

????????Com_delete:删除次数

????????Com_select:查询次数

1.4.2、慢查询日志

概述:

? ? ? ? 慢查询日志记录了执行时间超过指定参数(long_query_time,单位:秒)的所有SQL语句的日志。MySQL默认的慢查询日志没有开启,我们可以看一下系统变量中该参数对应的值是什么:

(1)查看慢SQL配置:show variables like '%slow_query_log%';

(2)开启慢SQL配置:Windows ===> my.ini、Linux ===> /etc/my.cnf

添加如下内容:

# 开启MySQL慢查询日志开关
slow_query_log=1?? ?
# 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2

(3)重启MySQL

(4)执行如下sql,观察耗时情况

(5)观察慢sql日志中的信息

通过日志可以清晰的看出,哪些sql超时了,从而定位出效率低下的sql,进行针对性的优化;

1.4.3、profiles详情

概述:

? ? ? ? profiles详情用于帮助开发者定位sql执行的消耗时间都花在哪里了。

(1)查看当前mysql是否支持profile操作

select @@having_profiling;

说明:YES表示当前数据库支持profile操作。

(2)查看是否开启了profile支持

说明:1表示已经开启了profile支持,如果没有开启,可以通过如下指令进行开启:

set [session|global] profiling = 1;

(3)开关已经开启了,接下来我们执行的查询操作都将被记录下来;

select * from user;
select * from user where id = 1;
select * from user where name = '吕布';
select * from user where name = '吕不韦';
select count(*) from tb_sku;

(4)profile指令

-- 查看每一条sql的耗时情况
show profiles;

-- 查看指定sql的耗时情况
show profile for query 68;?

-- 查看指定query_id的sql语句的CPU使用情况
show profile cpu for query 103;

1.4.4、explain

??概述:

? ? ? ? explain或者desc命令,用于获取MySQL是如何执行select语句的,包括select语句执行过程中表是如何连接以及连接顺序的。

语法:直接在select语句前加上explain或者desc指令即可。

例如:explain select * from user;

explain各字段的含义解释:

1.5、索引使用

1.5.1、验证索引效率

?本系列文章前边使用了大面积篇幅介绍了索引的基本知识,那么索引它真的能够提升查询效率吗?以及它是如何提升查询效率的?带着这个疑问,我们先测试下相同的sql语句,在不使用索引和使用索引的情况下,查询效率有什么变化,通过对比即可知道索引对查询效率的影响。

先看一下表结构:

按照id查询,获得sn:select * from tb_sku where id = 1;

未建立索引的情况(根据sn查询):select * from tb_sku where sn = '100000003145001';

sn字段建立索引后,再次查询:

create index idx_tb_sku_sn on tb_sku(sn);

select * from tb_sku where sn = '100000003145001';

结果分析

? ? ? ? (1)通过索引可以极大地提高查询效率(提高了 16.261 / 0.035 = 464.6倍!);

? ? ? ? (2)大数据量情况下,建立索引是一个非常耗时的操作;

1.5.2、最左前缀法则

概述:

? ? ? ? 如果一个表中存在联合索引,那么查询的字段包含联合索引对应的字段时,要遵循最左前缀法则。所谓最左前缀法则值得是查询从索引的最左列开始,并且不跳过索引中的列,如果跳跃某一列,索引将会部分失效(后面的字段索引失效)。先来回顾一下user表中之前建立的索引情况:

????????在user表中存在一个联合索引idx_user_profession_age_status,这个索引涉及到3个字段,顺序分别为:profession、age、status。对于最左前缀法则,查询时最左边的列,也就是profession必须存在,否则索引全部失效,而且中间不能跳过某一列,否则该列后面的字段索引将失效,接下来用一组案例验证一下我们的结论:

索引有效案例:

(1)explain select * from user where profession = '软件工程' and age = 31 and status = '0';

(2)explain select * from user where profession = '软件工程' and age = 31;

(3)explain select * from user where profession = '软件工程';

结果分析:

? ? ? ? 通过上述三组的演示,可以发现只要联合索引最左边的字段profession字段存在,索引就会生效,只不过索引的长度不同罢了,并且经过以上三组测试,也可以得出如下结论:

? ? ? ? profession索引的长度为:47

????????age索引的长度为:2

? ? ? ? status索引的长度为:5

索引失效案例:

(1)explain select * from user where age = 31 and status = '0';

(2)explain select * from user where status = '0';

结果分析:

????????通过上述两组的测试结果,可以看到索引并未生效,原因是不满足最左前缀法则,联合索引最左边的列profession不存在。

再来看几组测试:

(1)explain select * from user where profession = '软件工程' and ?status = '0';

(2)?explain select * from user where status = '0' and profession = '软件工程';

结果分析:

? ? ? ? 通过上述两组的测试结果,可以知道由于profession字段存在,满足最左前缀法则,所以索引是生效的,但是查询时忽略了age这个字段,导致age和status字段对应的索引失效,也就是索引部分失效,另外对比(1)和(2),也可以得出结论:where后边的查询条件不一定非要按照联合索引的字段顺序写,只要满足最左前缀法则即可。

思考题:当执行?explain select * from user where age = 31 and status = '0' and profession = '软件工程';时,是否满足最左前缀法则,走不走上述的联合索引,索引长度?

答:走,索引长度为54。

注意事项:最左前缀法则指的是联合索引中最左边的列,在查询时必须存在,与我们编写sql时,条件的编写顺序无关;

1.5.3、范围查询

先说一组结论,然后再使用案例进行验证。结论:

(一)联合索引中,出现范围查询(>,<)时,范围右侧的列索引将失效;

(二)联合索引中,出现范围查询(>=,<=)时,索引不会失效;

验证一:

explain select * from user where profession = '软件工程' and age > 30 and status = '0';

验证二:

explain select * from user where profession = '软件工程' and age >= 30 and status = '0';

小总结:?

? ? ? ? 当表中存在联合索引时,如果想使用范围查询,而又不想让联合索引失效,尽量使用 >= 或者 <=,而避免使用 > 或者 < !!!

1.5.4、索引失效

除了上述演示的索引失效外,还有其他场景的索引失效,下面为大家一 一演示:

先看一下当前user表中的索引情况:

(一)索引列运算

结论:不要在索引列上进行运算操作, 否则索引将失效!!!

(二)字符串不加引号

结论:?字符串类型字段使用时,不加引号,虽然查询结果一致,但是由于数据库存在隐式类型转换,将会导致索引将失效!!!

(三)模糊查询

结论:如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引将失效!!!

(四)or连接条件

age字段建立索引后,再次执行上述查询语句;

create index idx_user_age on user(age);

结论:?当or连接的条件,左右两侧字段都有索引时,索引才会生效!!!

(五)数据分布影响

如果 MySQL 评估使用索引比全表更慢,则不使用索引。

结论:?

? ? ? ? 通过上述测试我们发现,相同的SQL语句,只是传入的字段值不同,最终的执行计划也完全不一样,这是为什么呢? 就是因为MySQL在查询时,会评估使用索引的效率与走全表扫描的效率,如果走全表扫描更快,则放弃索引走全表扫描。 因为索引是用来索引少量数据的,如果通过索引查询返回大批量的数据,则还不如走全表扫描来的快,此时索引就会失效!!!

(六) is null 与 is not null

????????查询时MySQL 会评估,走索引快,还是全表扫描快,如果全表扫描更快,则放弃索引走全表扫描。 因此,is null is not null 是否走索引,得具体情况具体分析,并不是固定的。

1.6、SQL提示

1.6.1、数据初始化

当前user表的数据情况和索引情况如下:

删除idx_user_age和idx_user_email索引:

1.6.2、案例

(一)explain select * from user where profession = '软件工程';

结果:查询走了联合索引

(二)创建profession的单例索引后再次查询:create index idx_user_profession on user(profession);

结果:?

????????我们可以看到,possible_keys中idx_user_profession_age_status,idx_user_profession这两个索引都可能用到,但是最终MySQL选择了idx_user_profession_age_status索引。这是MySQL自动选择的结果。 那么,我们能不能在查询的时候,自己来指定使用哪个索引呢? 答案是肯定的,此时就可以借助于 MySQL SQL 提示来完成。 接下来介绍一下 SQL 提示:

1.6.3、SQL提示

概述:

????????SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的。常见命令如下:

(一)use index

指令解释:建议MySQL使用哪一个索引来完成此次查询(注意:仅仅是建议,MySQL内部还是会再次评估);

案例:explain select * from user use index(idx_user_profession) where profession = '软件工程';

(二)ignore index?

指令解释:忽略指定的索引

案例:explain select * from user ignore index(idx_user_profession) where profession = '软件工程';

(三) force index

指令解释:强制使用索引

案例:explain select * from user force index(idx_user_profession) where profession = '软件工程';

1.7、覆盖索引

1.7.1、概述

????????尽量使用覆盖索引,减少select *的使用 。 那么什么是覆盖索引呢? 所谓覆盖索引是指查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到。

1.7.2、案例

执行如下sql:

????????explain select id, profession from user where profession = '软件工程' and age =31 and status = '0' ;

????????explain select id,profession,age, status from user where profession = '软件工程'and age = 31 and status = '0' ;

????????explain select id,profession,age, status, name from user where profession = '软件工程' and age = 31 and status = '0' ;

????????explain select * from user where profession = '软件工程' and age = 31 and status= '0';

结果分析:

??????从上述的执行计划我们可以看到,这四条SQL语句的执行计划前面所有的指标都是一样的,看不出来差 异。但是此时,我们主要关注的是后面的Extra,前面两条SQL的结果为 Using where; Using Index ; 而后面两条SQL的结果为: Using index condition,那么它们的区别是什么?请看下图:

?

????????因为,在user表中有一个联合索引idx_user_profession_age_status,该索引关联了三个字段profession、 age status ,而这个索引也是一个二级索引,所以叶子节点下面挂的是这一行的主键id 。 所以当我们查询返回的数据在 id profession age status 之中,则直接走二级索引直接返回数据了。 如果超出这个范围,就需要拿到主键id,再去扫描聚集索引,再获取额外的数据了,这个过程就是回表。 而我们如果一直使用 select * 查询返回所有字段值,很容易就会造成回表查询(除非是根据主键查询,此时只会扫描聚集索引)。
????????为了大家更清楚的理解,什么是覆盖索引,什么是回表查询,我们一起再来看下面的这组SQL 的执行过程。

?表结构示意图:

id 是主键,是一个聚集索引。 name 字段建立了普通索引,是一个二级索引(辅助索引)。
(一)执行SQL : select * from user where id = 2;
根据 id 查询所有,直接走聚集索引查询,一次索引扫描,直接返回数据,性能高。
(二)执行SQLselet id,name from tb_user where name = 'Arm';
????????虽然是根据name 字段进行查询二级索引,但是由于查询返回的字段为 id和 name ,在 name 的二级索引中,这两个值都是可以直接获取到的,因为覆盖索引,所以不需要回表查询,性能高。
(三)执行SQLselet id,name,gender from tb_user where name = 'Arm';
由于在 name 的二级索引中,不包含 gender ,所以,需要两次索引扫描,也就是需要回表查询,性能相对较差一点。 ????????????????

1.8、前缀索引

1.8.1、概述

????????当字段类型为字符串(varchartextlongtext等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO, 影响查询效率。此时可以只将字符串的一部分前缀建立索引,这样可以大大节约索引空间,从而提高索引效率。

1.8.2、案例

语法:create index idx_xxxx on table_name(column(n)) ;

需求:为user表的email字段建立长度为5的索引

create index idx_user_email on user(email(5));

1.8.3、前缀长度

????????可以根据索引的选择性来决定,所谓选择性是指不重复的索引值(基数)和数据表的记录总数的比值, 索引选择性越高则查询效率越高, 唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
select count ( distinct email) / count (*) from user ;
select count ( distinct substring(email, 1 , 5 )) / count (*) from user ;

1.8.4、前缀索引的查询流程

1.9、单列索引 & 联合索引

1.9.1、概述

单列索引:即一个索引只包含单个列。
联合索引:即一个索引包含了多个列。
当前user表中的索引情况:
从查询结果可以看出,红色框的为联合索引,紫色框的为单列索引

1.9.2、案例

????????通过上述的执行计划我们可以看出,在and 连接的两个字段 phone name 上都有单列索引,但是最终mysql只会选择一个索引,也就是说,只能走一个字段的索引,此时是会走回表查询的。

小总结:在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引,而非单列索引。

如果查询使用的是联合索引,具体的结构示意图如下:

1.10、索引设计原则

(1) 针对于数据量较大,且查询比较频繁的表建立索引。
(2)针对于常作为查询条件( where )、排序( order by )、分组( group by )操作的字段建立索 引。
(3) 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
(4)如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
(5)尽量使用联合索引,减少单列索引查询时,联合索引很多时候可以覆盖索引,节省存储空间, 避免回表,提高查询效率。
(6) 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
(7) 如果索引列不能存储 NULL 值,请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。

?

文章来源:https://blog.csdn.net/HelloWorld20161112/article/details/135073783
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。