postgresql SQL 优化 -- 理论与原理

这里写的是一个系列，关于POSTGRESQL SQL 优化的问题，这篇是这个系列的第二篇，第一篇可以在文字的末尾的连接中找到，之前有同学提出，希望有一个历史文字的连接。

这期就进入正题，一个SQL 语句撰写出来是怎么开始工作的，也就是查询的过程 query processing ，这里从几个步骤入手

1  一个SQL 是如何转换成数据库系统可以识别的语句

2  对于转换的语句，数据库系统是怎么对如何解释SQL语句进行工作的

3  最终根据什么方式来对给定的语句执行的计划，进行语句的执行和返回结果

与任何的程序语言有类似的过程，就是SQL的编译 compilation , 如JAVA ，PYTHON 程序员撰写的语句本身也有事要经过JAVA 的编译器，PYTHON 通过python的编译器来将这些语句进行编译后，才能在计算机上对应的系统中执行。但这里面与程序语言的不同之处在于程序语言在经过编译器编译后的程序Coding 是可以被执行的，而SQL 进行编译后的命令依然是命令而非直接可以执行的代码。只是对于SQL语句编写的格式进行了规整和匹配。

我们以SQL的编译举例，不同地方对于你要去做什么的表达是不一样的，

有说 ， 你这似干嘛？  你作甚？你去干什么？ 你咋即去？  弄了该租啥？

方言这么多，其实就表达一个意思，你去干什么？1  询问你地点  2 你要做什么？  包含了两个疑问点。

SQL 也是， 如  

select  * from table where id >= 10 and id =< 12;

select * from  table where id between 10 and 12;

select * from table where id > 10 and id <12

union 

select * from table where id = 10 

union

select * from table where id = 12;

上面的三种写法其实都是要查询同一批数据

所以SQL 的compilation 重要的意义就是将这些写的不同的SQL 但表达同一个意思的SQL 进行翻译，让他们变成同一种意思让数据库的分析器，执行器能明白到底要做什么。

所以编译器异常的重要如果将上面的不同的写法翻译成不同的意思，那么得到的结果可能就是有误的。

在编译器对不同SQL撰写者，撰写的语句翻译完毕后，下面就需要对统一的执行命令进行路径的选择，也就是optimization.

此时就体现了一个数据库（单体）数据库是否优秀的关键，如何找到将上面的命令用什么样的方式，怎么个先来后到的，那些条件在什么时间对收集上来的数据起作用，这就是体现数据库中 算法的精妙之处，截止目前ORACLE 还是这方面的王者， 当然PG 也不差（在开源数据库的圈子）。optimizaiton 主要的工作有两个，1 将SQL 语句表达的逻辑结构，转换为SQL 执行器的逻辑执行结构， 2  自主根据SQL 撰写的语句的人能理解的执行顺序，转换为适应数据库SQL执行器可以执行的顺序，可能与人能理解的顺序有出入，但最终保证了与人类撰写SQL时理解的执行顺序一致的状态。

总结优化器就像一个保险行业的精算师，如果你想发布一个保险产品，首先精算师的从上到下，从成本的角度，从几率的角度，等等考虑你的保险产品到底该怎么做。

之所以这是数据库的核心，是因为这是最能体现数据库性能和功能最终的实现，一个语句是使用 nested loop 的方式，还是使用  hash 的方式，实现中到底使用的是 merge sort scan , bitmap index scan 等等不同的方式来机械能数据的收集和筛选的过程。方法很多，到底那个是好的，或者是在给定时间内，我们能找到最好的，这是一个不容易逾越的技术高台。

当然这里牵扯了更多的东西，如统计信息，直方图，索引类型对于查询的影响，数据存储方式对查询的影响，所以太多需要考虑和影响optimization 的过程和结果。

在给出了执行的方案后，也就是optimization 给出了 execute plan 后，下一步就是数据库execute 执行的过程。

explain analyze SELECT * FROM flight

WHERE departure_airport ='DCA'

AND (arrival_airport='SAN' OR arrival_airport='MIA')

AND scheduled_departure BETWEEN '2020-01-27' AND '2020-12-28';

explain analyze SELECT * FROM flight

WHERE departure_airport ='DCA'

AND arrival_airport='SAN' 

AND scheduled_departure BETWEEN '2020-01-27' AND '2020-12-28'

union all

SELECT * FROM flight

WHERE departure_airport ='DCA'

AND arrival_airport='MIA' 

AND scheduled_departure BETWEEN '2020-01-27' AND '2020-12-28';

上面有两个执行的语句，意思都是一样，撰写的方法不一样，按照我们的思维方式，两个语句组合应该是单条语句执行时间的两倍，但事实上并不是，

在调整了几个POSTGRESQL的查询参数后，查询结果又变化了。

以上也说明另一个问题，执行计划有时虽然一样，但最终每次执行的时间是不一样的，有时DBA 进行SQL 的优化，只是在测试环节中测试优化后的结果还是不错的，但将他放到实际的生产环节中，发现并不和自己在测试环节中测试的结果一样，这属于正常的现象，因为生产环节中的数据是变动的，并且语句执行的依据数据统计信息也不见得一致， 并发度也不一样，最终SQL的优化不理想也实属正常。

对于表很表之间的关系，一般通过JOIN 的方式来体现，当然也有通过子查询通过不同的条件模式 EXISTS , IN  等方式，而这里尤其join 的方式给了表和表之间关系运算更大的自由度，相对于 EXISTS  IN 等方式固话，给数据库自身优化引擎发挥效能了枷锁。

如JOIN  3张表  A  B  C ， 优化引擎可以 ( A  AND B  ) AND C  ，或者  (B AND C) and A ,在或者  (A and C ) and B   这里由于数据库量的不同，执行结果虽然一致，但filiter 的数据量却不一定一样，所以执行后的时间也有快有慢， 而更多的条件组合变化就更多了，基于 CBO RBO  规则引擎计算cost 的方法，来决定哪个执行计划更优的模式。

上一期

POSTGRESQL SQL优化 重优化轻设计对不对与优化需要掌握的知识类别

https://mp.weixin.qq.com/s?__biz=Mzg4NDA0NTEwNA==&mid=2247494440&idx=1&sn=7eaf6a22b78f8229376fa8c4a3f48bc6&chksm=cfbc8f77f8cb0661a2db86558b347ee654a31284934cccd69cb3451968c3b4c47563d61802a7&token=160431904&lang=zh_CN#rd