目录

chen 的个人博客

VX:TiAmo151028
Phone:13403656751
Email:zxydczzs@gmail.com

X

尝试一下mysql索引优化!!!

最近看了不少的 MySQL 调优,其中提到了索引,现在来尝尝鲜。

这是一个 10W+ 数据的表,没有带任何索引的时候去精准查询 name,它的执行时间为 11.122s,很慢

运行的 SQL 语句为:

1select * from book_chapter where chapter_name = '第两千五百三十六章 玄雷之命(终章)';

image.png

这个时候它的执行计划是 ALL,也就是全表扫描。

image.png

然后尝试给这个字段加索引

使用命令

1alter table book_chapter add index chapter_name(chapter_name);

加完索引咱们再次尝试。执行时间为 0.139s。这里貌似触发了回表操作,可以知道我这个版本的 MySQL 默认存储引擎为 innodb,底层数据结构为 B+ 树,而 MySQL 一个索引就是一颗 B+ 树,默认主键就是一个索引,也就是一棵树,而 chapter_name 为另一颗 B+ 树,innodb 在存储数据和索引的时候,只有一颗 B+ 树存储的是主键 + 数据,而 chapter_name 这颗 B+ 树存储的是主键 ID,所以在执行这条 SQL 的时候,会先去找到 chapter_name 普通索引的 B+ 树,找到对应的主键值,然后再根据主键值去找另一颗带有数据的 B+ 树,所以这里触发了两次遍历索引,叫做回表。

image.png

这时候在查看执行计划

image.png

ref 的解释:不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行

感觉还是慢,那怎么办呢?这行数据我们又不一定全部都要,可以只要一些必要的字段。所以再次对 SQL 语句进行优化。

运行的 SQL 语句,而这种方式貌似称之为覆盖索引,就是反正来的回表,不去触发 chapter_name 的 B+ 树,只触发了主键的那颗存储了数据的 B+ 树,所以效率比上方经过了回表操作的查询速度快。

1select chapter_id, book_id,chapter_name from book_chapter where chapter_name = '第两千五百三十六章 玄雷之命(终章)';

image.png

可以看到查询时间为 0.030s。基本上这个速度已经完全符合业务的响应速度了。

大家想要练习的话,自己弄一张数据量大的表,如果没有,俺给你们提供我现在这个供你们练习

链接:https://pan.baidu.com/s/1oQLwJ85CkX9LTaDhbpMdXg
提取码:nf6q

下面是我练习使用到的 SQL 语句。

以上均是自己理解,有不对之处请在下方留言指出来,虚心接受指教 🙏 🙏 🙏 ****

 1-- 精准查询chapter_name,不带索引,时间较长,11.122s;
 2SELECT * FROM book_chapter WHERE chapter_name = '第两千五百三十六章 玄雷之命(终章)'; 
 3
 4-- 执行计划,type显示ALL,全表扫描;
 5EXPLAIN SELECT * FROM book_chapter WHERE chapter_name = '第两千五百三十六章 玄雷之命(终章)'; 
 6
 7-- 创建索引,索引名称chapter_name,被索引的字段chapter_name;
 8ALTER TABLE book_chapter ADD INDEX chapter_name(chapter_name);
 9
10-- 创建索引后精准查询chapter_name,时间变短,0.139s;
11SELECT * FROM book_chapter WHERE chapter_name = '第两千五百三十六章 玄雷之命(终章)'; 
12
13-- 创建索引后执行计划,type显示ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行;
14EXPLAIN SELECT * FROM book_chapter WHERE chapter_name = '第两千五百三十六章 玄雷之命(终章)'; 
15
16-- 带查询字段的语句,时间更短,0.030s;
17SELECT chapter_id, book_id, chapter_name FROM book_chapter WHERE chapter_name = '第两千五百三十六章 玄雷之命(终章)';
18
19-- 创建索引并且带查询字段的执行计划
20EXPLAIN SELECT chapter_id, book_id, chapter_name FROM book_chapter WHERE chapter_name = '第两千五百三十六章 玄雷之命(终章)';
21
22-- 删除索引;
23DROP INDEX chapter_name ON book_chapter;

标题:尝试一下mysql索引优化!!!
作者:zzzzchen
地址:https://dczzs.com/articles/2021/08/26/1629986797346.html