一个Sql重写优化器（一）原理

background

通过观察业务中一些慢查询的SQL以及他们的执行计划，可以发现PostgreSQL的默认优化器在执行limit的语句中生成的执行计划有问题，他是先做了join再limit，但是jion动辄上百万行记录，但是limit一般只需要几十条记录，其实limit可以内推但是由于left join本身可能让结果集的行数增多所以PostgreSQL没有这样做，（其实如果更Smart一点可以观察如果是join的主键则不会增多，另外left join只会增多，所以可以先limit，再join 再limit即可）。
所以针对业务中常见的场景，就是从一个主业务表出发，join各种表来完善相关信息，最终通过时间来倒序取前几条就有了一个特定的优化器。这里用这个DSL一方面是因为这个优化器是针对上面这种特定场景，另一方面避免了SQL解析的问题

Sql重写

也就是说这个优化器是针对是业务中常见的情况：由开发人员写SelectA，然后由框架自动执行SelectA1和SelectA2

---SelectA
select table0.column1,table2.column3...tableq.columnp
from table0
left join table2 on table0.column2=table2.primaryKey
left join table3 on table0.column3=table3.primaryKey
left join table24 on table2.column4=table24.primaryKey
...其他left join
left join tablepq on tablep.columnq=tablepq.primaryKey
...关联聚合后的子表
left join (select aggration(columnr),aggration(columns),columnu
from tablev group by columnu) v on tableu.primaryKey=v.columnu
where tablem.columnn={query.fieldm} and ...
order by tablew.datecolumn desc

---SelectA1
select count(1) from(
---SelectA
)

---SelectA2
select * from(
---SelectA
)
limit pageSize offset (pageNumber-1)*pageSize

也就是以下三点

• 都是left join（也可以是inner join，主要是不能造成行数减少）
• 通过主键来关联（这一点限制可以放宽，主要是行数不能变）
• 子表数据汇总后附加到父表
• where条件的表的范围小于（甚至远远小于）select需要的表

那么这个时候可以将Sql重写成以下几个

--SelectB1
select count(1)
from table0
left join --仅仅关联在where中需要的表
where --可以将where somecolumn in (一个元素)改为where somecolumn = 一个元素
-- 可以将where somecolumn in (所有元素）去掉，例如前端勾选了下拉框的所有项

--SelectB2
select table0.primaryKey...tablex.primaryKey---仅仅选左表主键和下面SelectB22需要的主键
from table0
left join ---和SelectB1类似，仅仅关联where中需要的表（一般会自动覆盖select需要的表）
where ---和SelectB1一样
limit pageSize offset (pageNumber-1）*pageSize

--selectB21
select table0.column1,table2.column3...tableq.columnp--选取除了SelectA中除了通过子表聚合外的所有列
from table0
left join table2 on table0.column2=table2.primaryKey
left join table3 on table0.column3=table3.primaryKey
left join table24 on table2.column4=table24.primaryKey
...其他left join，除了SelectA对子表的聚合子查询外的所有表
left join tablepq on tablep.columnq=tablepq.primaryKey
where table0.primaryKey in (selectB2的table0.primaryKey)

--SelectB22
select columnr,columns,columnu
from tablev where columnu in (selectB2中的primaryKey)

然后将SelectB1，SelectB2的结果在程序中拼接在一起。这样就避免了子查询的全表扫描和最后被舍弃的join。另外在使用了父子表或者union all的情况下，在B21的join中也可以去join更明确的表或者使用更明确的字段去join。

reactor

借助reactor可以将SelectB中的多个查询同时执行，特别是在做了分库的基础上

对于其中最耗时的数据库操作从4降为2

一点讨论

能够将B21和B22合并成以下情况呢？

with id_view as (
--SelectB21
)
SelectB22 
where primaryKey in (select primaryKey from id_view) 

答案是并不行，因为数据库仍然是先join再过滤，没有使用top方法