sql子查询优化

最近突然发现一个功能的查询时间很慢很慢,甚至长达好几分钟,由于之前这段代码是以前人写的,并未仔细了解过,所以开始着手优化
使用的thinkphp框架

1、优化第一步,上索引
进数据库看了下,发现之前人连索引都没有加,怪不得这么慢,所以直接加上索引
首先将真正查询的sql语句拉出来,然后查看下使用了哪些字段,由于这次查询是使用了两张表,所以两张表的字段都要建立索引
索引的优势就极大提高读取效率,但会降低增删改的效率,同时还有额外的空间消耗,大部分情况下,只需要对一些常用的字段添加索引即可

成果:由5分钟降低到90秒左右

效率提升不少,但仍然慢得发指,在一次web请求中,会直接超时,接下来继续优化

2、explain展开查询
首先使用explain展开查询语句

可以看到两次预估查询行数不多,分别是660和23783行,数据量很少,按理来说应该不会是这个样子
但是请注意到第二行
select_type DEPENDENT SUBQUERY
这个表明这个子查询依赖于外面的select,这样复杂度就会是m*n(其中m为原本查询,n为子查询),按照上面来说,计算量就是660*23787=15699420次

sql语句如下


这并非真正的sql语句,但逻辑都一样,当中的表名和字段名适当做了处理,可以看到逻辑确实是用外面每一次的select后的数据,进行一次子查询,那么,下面开始进行优化

3、join改写
通读了这个逻辑后,发现可以直接使用join,于是用join改写
以下是更改后的sql语句

逻辑上差不多,而且效率更高

成果:由90秒降低到5秒左右

下面是explain展开

可以看到两次单查询,同时后面的extra表明是如何查询的,现在复杂度变成了m+n,效率提高不少,效率还能更高吗?
可以看到第二次的join查询时并没有用到index,所以给对应字段加上索引是个好办法(之前因为我加的是联合索引,可以单独为这两个字段加上索引)

4、临时表改写

这里是先进行子查询,然后将结果作为临时表,最后再从临时表里面查询数据,速度还能更快

成果:由5秒降低到0.8秒左右

explain展开

可以看到,虽然没有使用到索引,但效率极高,复杂度依旧是m+n,但免除了join的性能开销,完全变成了两次纯粹的select,自然会快很多

引用:
[慢查优化]慎用MySQL子查询,尤其是看到DEPENDENT SUBQUERY标记时

留下评论

biubiubiu~

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据