MogDB查询优化之执行计划专篇

概述

本篇文章对MogDB执行计划的知识和测试做相关整理，主要针对以下问题展开：
1、详细记录第一次物理读、逻辑读的执行计划差别；
2、对比索引、全表扫描的执行计划差别；
3、对比只通过索引访问、和通过索引访问之后在回表的执行计划；
4、对比多表关联有多少种执行计划，差异是什么；

知识原理

1.SQL执行流程

SQL引擎从接受SQL语句到执行SQL语句需要经历的步骤如下图所示：

其中，sql首先是转化为格式化的结构对语法进行分析，如果没有语法上的问题，进一步对于语义进行分析，如果语义解析检查通过，进一步对sql进行重写，该步主要依据底层设定的规则对一个很糟糕的语句转化为高效执行的语句，如条件化简、移除不必要的括号、移除没用多余的条件、常量表检测优先执行等策略。
进一步对重写后的sql，结合数据库内部的统计信息和GUC参数来决定执行计划达到优化效果。最后根据优化后的规划路径执行sql查询。
从整个步骤来看，最重要的部分就是查询优化部分，该部分的优化效果由几大因素决定：统计信息、GUC参数、底层存储。
统计信息可以在不同的执行方式下对比执行代价（如元组数、字段宽度、NULL记录比率、distinct值、MCV值、HB值等表的特征值），进而选出整体执行代价最小/首元组返回代价最小的执行方式进行执行。统计信息是查询优化的核心输入，所以周期性地运行ANALYZE，或者在对表的大部分内容做了更改之后马上运行它是个好习惯。
GUC参数适当的调整可以优化SQL执行效果，比如表之间的连接分为Nested Loop、Merge Join和Hash Join三种，优化器会根据统计信息来决定代价最小的连接方式执行，但是当因为某一些原因其cost不能真实反映时就需要通过guc调整参数让执行计划倾向更优。
数据的底层存储分为行存储和列存储，底层存储方式的选择依赖客户的具体场景。一般来说计算型业务查询场景（以关联、聚合操作为主）建议使用列存表；点查询、大批量UPDATE/DELETE业务场景适合行存表。

2. 执行计划命令

执行计划（explain，也叫查询计划或者解释计划）是数据库执行 SQL 语句的具体步骤，例如通过索引还是全表扫描访问表中的数据，连接查询的实现方式和连接的顺序等。如果 SQL 语句性能不够理想，我们首先应该查看它的执行计划。本文主要介绍如何在MogDB数据库中获取和理解执行计划，并给出进一步深入分析的参考文档。
执行计划最关键的部分是sql语句的预计时间开销，这是执行生成器预估的时间，如果指定了analyze选项，返回结果是实际的运行统计结果，包括每个计划节点内时间总开销（ms）和实际返回的总行数。这对于判断计划生成器的估计是否接近现实非常有用。
需要注意的是，指定了analyze是sql语句实际被执行的结果，可能设计到数据的变化，不利于我们进行测试，所以想要实际执行但是又不去提交更改的数据，需要使用到事务的特性（回滚和提交自己决定）：

START TRANSACTION;
EXPLAIN ANALYZE ...;
ROLLBACK;


执行计划命令有三类：explain、explain analyse/analyze和explain performance，简单来说，explain执行计划是sql的预估计划，并不是实际的结果，而explain analyse和explain performance都是实际执行结果，前者自定义指定输出信息，后者输出全部信息。且在统计节点开销的过程中，explain analyse和explain performance会增加性能分析的开销，所以首次运行执行计划会比普通查询需要更多的时间开销。

Tip:遇到sql执行太慢的问题，可以通过执行计划explain命令查看预估的原因，进一步使用explain analyse和explain performance分析，以便更加精准定位。

测试验证

1. 物理读与逻辑读

物理读：一般为第一次读取且内存无相关数据时，从磁盘中读取数据。
逻辑读：一般物理读之后内存中留有缓存，从内存（缓存）中读取数据，。

要想在执行计划中明确是逻辑读还是物理读可以使用explain (analyze,buffers true) sql或者explain performance输出详细的信息，其中：
“Buffers：shared read=xxx” 表示物理读；
“Buffers：shared hit=xxx” 表示逻辑读

在数据库操作中，优先在缓存中寻找对应的表，如果缓存不存在相应的数据表则进入到底层磁盘中寻找，且物理读的数据会在读取后进入内存，便于下次更加快速的读取操作。
类似的，在主备模式下主节点的wal日志发送到备节点，备节点接受到wal日志也是优先放在内存中，进一步持久化到磁盘，在数据同步的过程中，也是优先产生缓存副本而不是直接读写到磁盘中去。

测试两者的执行计划差别：

explain analyze select * from bmsql_oorder;


从结果上不难看出，首次读取表花费的时间远大于后面再次读表所花时间(如果数据量比较小就差距没那么明显)，也就是说物理读的时间开销非常大，读取之后数据缓存在内存中，该测试用例逻辑读的所需时间开销不及物理读的10%。而且可以发现每次逻辑读的时间不会固定或者趋近于某一个值，输出时间开销没有规律且上下浮动，但是差距不大。

2. 索引与全表扫描区别

在查询优化的阶段，不同的查询方式的统计信息所产生的代价是非常不同的，例如在大表中有全表扫描或索引两种不同的访问方式，一般情况下索引的查询速度更快，代价更小，故底层优先走索引，但是在小表中，走索引有时候却不如全表快。
现在对两种扫描进行实际测试（Seq Scan是顺序扫描、也称全表扫描；Index Scan为索引扫描）。

2.1. 小表测试

create table no_index_table(id int , col1 varchar(8));
insert into no_index_table select generate_series(1,10),left(md5(random()::text),8);
create table index_table as table no_index_table;
create index index1 on index_table(col1);


explain performance select * from no_index_table;
explain performance select * from index_table;


explain performance select id from no_index_table where col1='476fa2a5';
explain performance select id from index_table where col1='476fa2a5';


总结来说，对于小表，创建索引不但不能发挥作用，反而占用存储资源。

2.2. 大表测试

大表no_index_table、index_table测试，填充至1000000条记录：

insert into no_index_table select generate_series(11,1000000),left(md5(random()::text),8);
insert into index_table select generate_series(11,1000000),left(md5(random()::text),8);
explain performance select * from no_index_table;
explain performance select * from index_table;


explain performance select id from no_index_table where col1='476fa2a5';
explain performance select id from index_table where col1='476fa2a5';


总结的来说

• 没有加索引的表，只能走顺序扫描；
• 加了索引的表，特别是小表，即使仅针对索引字段进行查询也是走顺序扫描，而大表一般是走索引扫描，但是也会存在不走索引（索引失效）的情况；
• 不管是走索引还是顺序扫描，他们的耗时是由sql语句决定的，耗时更短的方式会成为最终的执行方式

3. 对比纯索引访问和索引后回表的执行计划区别

什么时候需要回表：通俗的讲就是，如果索引的列在 select 所需获得的列中或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列（select *），索引就需要到表中找到相应的列的信息，这就叫回表。
在innodb存储引擎中，命中索引的节点存放了当前索引字段的数据和主键（如果没有设定主键那么就是第一个非空唯一索引）的数据，其他的数据必须经过回表查询，所以一般而言回表的时间开销是不可忽略的，尤其是索引命中后如果还需要回表的话无疑会增大查询时间，接下来是具体的测试对比：

explain performance select * from index_table where col1='476fa2a5';
explain performance select col1 from index_table where col1='476fa2a5';


从图中不难看出回表扫描的时间比纯索引输出的时间长，且回表扫描的索引输出为“Index Scan”，纯索引输出不回表的为“Index Only Scan”

4. 多表关联的执行计划

多表连接的执行计划有三种，分别是Nested Loop、Hash Join、Merge Join。

Nested Loop：嵌套循环，适用于被连接的数据子集较小的查询。在嵌套循环中，外表驱动内表，外表返回的每一行都要在内表中检索找到它匹配的行，因此整个查询返回的结果集不能太大（不能大于10000），要把返回子集较小的表作为外表，而且在内表的连接字段上建议要有索引。
Hash join：哈希连接，适用于数据量大的表的连接方式。优化器使用两个表中较小的表，利用连接键在内存中建立hash表，然后扫描较大的表并探测散列，找到与散列匹配的行。Sonic和非Sonic的Hash Join的区别在于所使用hash表结构不同，不影响执行的结果集。
Merge Join：归并连接，通常情况下执行性能差于哈希连接。如果源数据已经被排序过，在执行融合连接时，并不需要再排序，此时融合连接的性能优于哈希连接。

外表返回的数据子集较小的情况（Nested loop）：

explain performance select no_index_table from no_index_table,index_table where no_index_table.col1=index_table.col1 and index_table.col1='476fa2a5';


外表返回的数据子集较大的情况（Hash join）：

explain performance select no_index_table from no_index_table,index_table where no_index_table.col1=index_table.col1;


外表返回的数据子集较大但经过order by排序（Merge Join）：

explain performance select no_index_table from no_index_table,index_table where no_index_table.col1=index_table.col1 order by index_table.col1;


5. 索引失效

索引失效：sql执行采用索引扫描方式的总开销比顺序扫描更大，根据优化器的基于最小开销的原则选定顺序扫描执行sql，让原有的索引无法发挥作用，类似于失效（不要理解为物理上的失效，这是概念上的失效，索引还是存在的！）。
索引失效的可能场景：

• 查询条件不涉及到索引字段或者带有or；
• 小表查询；
• 大表行命中率较高的SQL语句（或者理解为顺序扫描更快），如模糊查询、范围查询；
• 不满足最左前缀匹配原则导致联合索引失效；
• 其他情况，如对索引列进行计算、对索引列增删改、char类型字段不加引号、表没有analyze、not in、is null等；

参考文档

opengasuss:
https://docs.opengauss.org/zh/docs/5.0.0/docs/PerformanceTuningGuide/%E8%AF%A6%E8%A7%A3.html