今日遇到的神奇的 sql 优化问题

[zzfer]

下午测试那边说有个项目列表查询有点慢，于是我就开始排查。列表 sql 长这样（简化版）（好像 v2markdown 格式出问题了）

select * from (select * from table1 where xxx )

我一看这 sql 当时觉得问题就在这了，复制粘贴，navicat 执行，0.1 秒？嗯？没问题？那可能是业务有问题，仔细看完业务流程，并打断点执行，发现也并不慢。

看下日志，发现还执行了分页插件查询的 count 语句，继续复制粘贴，navicat 执行，我靠，居然要四五秒才执行完，万万没想到查询慢的居然是 count 语句，count 语句长这样（简化版）

select count(*) from (select * from (select * from table1 where xxx) table2 where xxx)

执行 explain 发现 count 的语句查询行数有好几万行

想着优化方案既然查询列表语句很快,我在查询结果上加上序号，然后直接 max （序号），这样应该很快

于是我在子查询加上了 @x:=IFNULL(@x,0)+1 as rownum，执行了下里面的子查询，我发现 rownum 里都是 1 ，我寻思既然没自增，我直接 count （ rownum ）试试，一试，嘿 0.1 秒。数量也对。感觉很神奇，下面是 sql

select count(*) from (select @x:=IFNULL(@x,0)+1 as rownum,table2.* from (select * from table1 where xxx) table2 where xxx)

我想，那自增既然没生效，我直接改成 1 as rownum 行不行，试了下，不行，语句速度又慢了，下面是 sql

select count(*) from (select 1 as rownum,table2.* from (select * from table1 where xxx) table2 where xxx)

我虽然优化了这个问题，但我并不知道原理，因此想问问大佬们，有没有知道原理的？或者遇到过相同情况

附加一下，优化前的 explain 和优化后的 explain。

优化前

select_type	table	type	row	filtered
PRIMARY	<derived2>	ALL	55859	100.00
DERIVED	t1	ALL	149	10.00
DERIVED	t3	ALL	160	10.00
DERIVED	t2	ALL	703	33.00

优化后

select_type	table	type	row	filtered
PRIMARY	<derived2>	ALL	5585	100.00
DERIVED	t3	ALL	160	10.00
DERIVED	<derived3>	ALL	3491	10.00
DERIVED	t1	ALL	149	10.00
DERIVED	t2	ALL	703	33.00

不知道为什么仅仅是多加了一个查询字段 @x:=IFNULL(@x,0)+1 as rownum ，explain 就边了这么多，多了一个临时表，数量上变化也很大。

[2i2Re2PLMaDnghL]

0. SQL 是声明式语言，其优化依赖于具体实现。你的数据库和版本号也建议发出来
1. 显然这时候我们首先需要分别 explain 一下

[Sasasu]

> 执行 explain 发现 count 的语句查询行数有好几万行
> @x:=IFNULL(@x,0)+1 as rownum ，执行了下里面的子查询，直接 count(rownum ) 0.1 秒。数量也对

你最后结果是好几万么？

[evilboy]

查一个表用得着这么嵌套吗？

[heliotrope]

mark 一下

count 就是会比较慢 特别是有条件的时候

offset 数量一多也慢的不行

搜出来的解决方案都是什么 先查出 id 再 in id 根本就不可行

[zzfer]

@2i2Re2PLMaDnghL 数据库版本就是 mysql5.7 。我分别 explain 了，查询慢的，count 的时候 是好几万。优化过后的 count 只有几千。

@Sasasu 最后的结果才一百多

@evilboy 不止一个表，具体业务不是我负责的，我没了解，负责这的同事休婚假去了

@heliotrope 我这次优化就很奇怪，就多加了一行查询字段，就变快了

[liangcj]

我也遇到和你同样的问题，但是我在字查询中加上了自增序号，外面用了 count （ rownum ）,其执行计划还是和之前一样。复现不出你这种多了一个临时表的情况。

[zzfer]

@liangcj 解决了吗，太久了我也忘了

[liangcj]

@zzfer 没，解决不了。难顶

[zzfer]

@liangcj 问问 gpt 吧