SQL优化 MySQL版 - 单表优化及细节详讲

单表优化及细节详讲

作者 : Stanley 罗昊

转载请注明出处和署名,谢谢!

注:本文章需要MySQL数据库优化基础或观看前几篇文章,传送门:

B树索引详讲(初识SQL优化,认识索引):https://www.cnblogs.com/StanleyBlogs/p/10413349.html

B树索引进阶(索引分类、创建方式、删除索引、查看索引、SQL性能问题):https://www.cnblogs.com/StanleyBlogs/p/10416865.html

SQL执行计划于笛卡尔积(了解什么是SQL执行计划,优化原理):https://www.cnblogs.com/StanleyBlogs/p/10422202.html

Type详讲(理解优化级别):https://www.cnblogs.com/StanleyBlogs/p/10426385.html

Extra(理解最终优化概念):https://www.cnblogs.com/StanleyBlogs/p/10429969.html

优化准备

首先我们需要有一个数据库,bookdb,还要有一张数据表book,有以下字段,我们接下来将用以下这张表来做优化实例;

SQL优化 MySQL版 - 单表优化及细节详讲

单表优化

此次教程不再使用可视化工具,因为效率太慢,我还是比较喜欢命令行操作;

下面我们需要编写以下条件的SQL语句:

查询authorid = 1并且 typeid为2或3的bid再根据typeid排序

SQL语句:select bid from book where typeid in (2,3) And authorid = 1 order by typeid desc;

我们执行这条SQL语句,然后在前面加上explatin查看sql执行计划:

SQL优化 MySQL版 - 单表优化及细节详讲

我们可以清楚的看到,查询级别是ALL,并且Useing where回表查询了并且后面还有一个Using filesort表示创建临时表了,可见此条SQL语句是多么的恐怖,效率极低

这个时候我们就来给它加个索引把,毕竟都知道,加索引可以提高效率,那我们就来分析一下以上这个sql语句;

首先该语句里面有 bid typeid authorid这三个字段,那么接下来我将给它们创建一个复合索引:

SQL优化 MySQL版 - 单表优化及细节详讲

索引名我起名为idx_bta代表它的顺序 b 代表 bid t 代表 typeid a 就代表authorid;

加上索引后,我们再执行一下,看看我们这条sql语句有没有被优化:

SQL优化 MySQL版 - 单表优化及细节详讲

首先,我们可以看见,type级别被优化了一些,到了index了,也就是ALL的上一个级别,我在之前的文章也说过,最好优化到ref级别,可见我们现在这条SQL还是不够优化,并且 我们后面还有Using filesirt,但是出现了Using index,说明还是优化了一些,但是远远不够!

那么为什么呢?我明明加了索引,它居然还性能这么差?

原来,我们忽略了一点,就是SQL解析过程!

我在前几篇文章重点说过,编写过程,解析过程是不一样的:

编写过程: 

select from join on where 条件 group by 分组 having过滤组 order by排序 limit限制查询个数

解析过程:

from on join where group by having select order by limit 

以上就是mysql的解析过程,我们发现,跟我们编写的过程完全不一致!

也就是说,我们尽管bid在前面,typeid跟authorid在后面,但是它实际执行的时候却是先执行where(type、authorid),而不是select(bid);

但是我们索引顺序是怎么建的?

是 b t a 的顺序(bid typeid authorid),既然where我现在非要先让bid先执行,很显然不满足最佳左前缀,就是从左向右依次执行,我现在的索引并没有满足,因为我现在却让最右边的先执行了(bid);

所以,我们需要改变一下索引的顺序,既然先解析where,我就让where后面的俩字段放在前面(typeid authorid),把select放在后面(bid);

根绝SQL实际解析的顺序,调整索引的顺序;

在建立这个索引之前,我们务必删掉没用的索引!

SQL优化 MySQL版 - 单表优化及细节详讲

删掉后,我们把索引的顺序改变一下,之前是 b t a 现在我改成 t a b(typeid authorid bid);

SQL优化 MySQL版 - 单表优化及细节详讲

添加索引后,我并且查询索引,发现创建成功了,我们再运行一下试试,这次我改变了索引顺序,顺序按照解析顺序排列,看看这次的效果如何:

SQL优化 MySQL版 - 单表优化及细节详讲

我们发现,type等级仍然是index,因为没有创建临时表了也就是额外的查询,性能明显提升了,但是我们的type等级仍是index,确实还没有达到我们想要的ref标准,接下来我们继续优化;

我们现在开始重点优化索引级别,很显然,我们的索引级别是index,距离ref还有点距离;

再次优化

system>const>eq_ref>ref>range>index>ALL

很明显啊,我们现在的这条sql才到index级别,我之前说过,最好达到ref或range级别;

我们来把之前的SQL拿过来:

 select bid from book where typeid in (2,3) And authorid = 1 order by typeid desc;

我现在将where条件后面这两个字段换一个顺序,为啥换顺序呢,看这个in

我之前讲过范围查询,in是有可能导致索引失效的,从而转为无索引;

我现在思路是,如果in失效了或typeid失效了,那你authorid也就跟着一起失效了,为什么呢?

我们来看一下索引顺序,我们是先typeid 后 authorid,如果你typeid都没了,那么authorid也可能也受干扰了,所以我把它顺序换换;

alter table book add index idx_atb(authorid,typeid,bid);

我现在让它先authorid后typeid,那如果先a 后 t 那即是你 t 失效了,无所谓啊,我先a,a你也用了,这是个思路;

既然typeid会失效,那我们改变一下where后面的顺序吧,既然你可能会失效,就把它往后放,别影响别人:

explain select bid from book where authorid = 1 And typeid in (2,3) order by typeid desc;

务必也把索引顺序也更改一下!

alter table book add index idx_atb(authorid,typeid,bid);

我们改变索引顺序跟SQL语句where后面的顺序后再执行:

SQL优化 MySQL版 - 单表优化及细节详讲

我们再查看type级别会发现已经达到了fef级别,并且也有Using index,但是还有Using where

因为我typeid虽然也有索引,但是我使用了in,就索引失效了,就跟typeid没有索引一样,这样就造成了又需要回原表查了,所以尽量避免使用in;

小结:之所以这条SQL语句能达到了ref,是因为我满足了最佳左前缀,跟处理了索引失效的问题,既然你要失效,我就不要让你影响后面的字段,我就把你往后排,尽管你失效了,你不影响前面的,所以我把SQL语句where后面的两个字段换了位置;

索引需要逐步优化,要实时查询sql执行计划,采取逐步优化措施;

补充

刚刚在上方我把SQL语句字段where后面的字段调换了位置,并且索引也跟着调换了,其实经过测试,其实SQL语句无需调换,仅需调换索引位置即可:

SQL优化 MySQL版 - 单表优化及细节详讲

今日感悟:

在你看不见的地方,总有你想不到的辛酸;

上一篇:数据表访问


下一篇:Django-ORM的基本操作