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

zzfer · 8 小时 24 分钟前 · 602 次点击

下午测试那边说有个项目列表查询有点慢，于是我就开始排查。列表 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)

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

Recommend