chen 的个人博客

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

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

运行的 sql 语句为：

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

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

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

使用命令

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+ 树，所以这里触发了两次遍历索引，叫做回表。

这时候在查看执行计划

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

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

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

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

可以看到查询时间为 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;