SQL并行模式

目录

 

Oracle并行模式

MySQL并行查询

PG并行查询

 

---

Oracle并行模式

• 什么是并行？

并行是Oracle为了提高大数据量的运算效率而提供多进程协作技术，它可以让多个CPU同时处理一个计算任务，充分使用系统资源，提高计算效率。Oracle 9i之后的版本可使用该并行功能。

 

• 并行的使用场景？

1. Parallel Query（并行查询）
2. Parallel DDL （并行DDL操作，如建表，建索引等）
3. Parallel DML（并行DML操作，如insert，update，delete，merge）

 

• 并行的实现机制？

首先，Oracle会创建一个进程用于协调并行服务进程之间的信息传递，这个协调进程将需要操作的数据集分割成很多部分，成为并行处理单元，然后并行协调进程给每个并行进程分配一个数据单元。比如有四个并行服务进程，他们就会同时处理各自分配的单元，当一个并行服务进程处理完毕后，协调进程就会给他们分配另外的单元，如此反复，直到表上的数据都处理完毕，最后协调进程负责将每个小的集合合并为一个大集合作为最终的执行结果，返回给用户。

 

• 什么情况下需要启用并行？

1. 机器有充分的空闲资源（CPU、内存、I/O等）
2. 处理的数据量大。参与处理的数据量大于10GB或者SQL运行时间超过30分钟可考虑使用并行。

 

**补充说明：

1. 启动并行模式需要消耗资源与性能，只有在处理一个很大的任务，如需要几十分钟，几个小时的作业，并且要有足够的系统资源的情况下（包括CPU，内存，I/O），考虑使用并行模式。否则在系统资源负荷已经很大的情况下，启用parallel，将会导致某个会话占用了所有的的资源，其他会话不得不去等待，从而导致系统性能反而下降。
2. 建议OLAP系统中考虑使用并行模式，因为OLAP的表通常都很大。OLTP系统中不要使用，因为OLTP系统的数据访问路径基本上以索引访问为主，并且返回结果集非常小，这样的SQL处理非常快，不需要启用并行。
3. 2核以上CPU效果明显
4. 应用于返回结果大于100万时，效果明显

 

• 如何启用并行？

	Hint	ALTER SESSION	TABLE/INDEX DEGREE	System parameters
Query	select /*+ parallel(a,8) */ count(1) from table_name a;	alter session force query parallel 8; select count(1) from table_name a;	alter table table_name parallel 8; select count(1) from table_name a;	1. Parallel_min_servers=n 在初始化参数中设置了这个值，Oracle 在启动的时候就会预先启动N个并行服务进程，当SQL执行并行操作时，并行协调进程首先根据并行度的值，在当前已经启动的并行服务中条用n个并行服务进程，当并行度大于n时，Oracle将启动额外的并行服务进程以满足并行度要求的并行服务进程数量。 2. Parallel_max_servers=n  如果并行度的值大于parallel_min_servers或者当前可用的并行服务进程不能满足SQL的并行执行要求，Oracle将额外创建新的并行服务进程，当前实例总共启动的并行服务进程不能超过这个参数的设定值。 3. parallel_adaptive_multi_user=true|false  Oracle 10g R2下，并行执行默认是启用的。 这个参数的默认值为true，它让Oracle根据SQL执行时系统的负载情况，动态地调整SQL的并行度，以取得最好的SQL    执行性能。 4. parallel_min_percent  这个参数指定并行执行时，申请并行服务进程的最小值，它是一个百分比，比如我们设定这个值为50. 当一个SQL需要申请20个并行进程时，如果当前并行服务进程不足，按照这个参数的要求，这个SQL比如申请到20*50%=10个并行服务进程，如果不能够申请到这个数量的并行服务，SQL 将报出一个ORA-12827的错误。当这个值设为Null时，表示所有的SQL在做并行执行时，至少要获得两个并行服务进程。 5. parallel_degree_policy 这个参数被设置为auto时，系统会将基于执行计划中操作的成本和硬件特性来判断是否使用并行。当在语句级别使用parallel或parallel(auto)则不管parallel_degree_policy设置为何值都会使用自动并行。
DML	默认情况下，parallel dml是禁用的，需要先用alter session启用： alter session enable parallel dml; update /*+ parallel(a,8) */ table_name a set col1=1;	alter session force parallel dml parallel 8; update table_name a set col1=1;	alter session enable parallel dml; alter table table_name parallel 8; update table_name a set col1=1; 以上三步缺一不可。
DDL	无	alter session force parallel ddl parallel 8; create table table_name as select * from ……	create table table_name parallel 8 as select * from …… create index index_name …… parallel 8;

**优先级

 Hint -> alter session force parallel -> table/index degree -> system parameters

参考 https://www.cnblogs.com/mxh168/archive/2004/01/13/14011537.html

        https://blog.csdn.net/lihuarongaini/article/details/101401794

 

MySQL并行查询

• 并行使用场景？

MySQL 8.0.14版本引入了并行查询特性，通过多线程协作，使得check table和select count(*)类型的语句性能成倍提升。目前使用场景比较有限。

 

• 并行实现机制？

1. 分区 - 扫描的核心步骤就是分区，将扫描的数据划分成多份，让多个线程并行扫描。
2. 二次分区 – 解决首次分区后，资源利用不充分的问题，对上一步骤产生的分区进行二次分区，降低并发扫描的颗粒度，充分利用资源。
3. 并行扫描 – 将每个分区扫描任务放入队列，并行的线程从队列中获取任务，执行扫描。
4. 执行器交互 – 将存储引擎的记录格式转换成MySQL格式，通过buffer平衡读取和处理速度的差异，确保整个操作顺利进行。

 

• 如何启用并行？

通过配置参数innodb_parallel_read_threads来设置并发线程数，以开始并行扫描功能。

参考 https://www.cnblogs.com/cchust/p/12347166.html

 

PG并行查询

• 什么是并行？

并行查询即利用多CPU产生让查询更快的查询计划。由于现有实现的限制或者没有比连续查询计划更快的查询计划存在，很多查询并不能从并行查询获益。但是对于可提升的查询来说，并行查询可以将速率提升4倍甚至更多。

 

• 并行的使用场景？

• Parallel scan
• Parallel join
• Parallel aggregation
• Parallel append

 

• 并行的实现机制？

并行的实现需要加入数据分片过程、进程或线程间的通信，以及并发控制，需要在优化器中规划好并行计划。

PG的并行由多个进程的机制完成。每个进程在内部称之为1个worker，这些worker可以动态地创建、销毁。PG在SQL语句解析和生成查询计划阶段并没有并行。在执行器（Executor）模块，由多个worker并发执行被分片过的子任务。即使在查询计划被并行执行的环节，一直存在的进程也会充当一个worker来完成并行的子任务，我们可以称之为主进程。同时，根据配置参数指定的worker数，再启动n个worker进程来执行其他子计划。

PG内延续了共享内存的机制，在每个worker初始化时就为每个worker分配共享内存，用于worker各自获取计划数据和缓存中间结果。这些worker间没有复杂的通信机制，而是都由主进程做简单的通信，来启动和执行计划。

 

• 什么情况下需要启用并行？

并行查询基于代价策略的判断，譬如小数据量时默认还是普通执行，访问大量数据但只返回其中少数行给用户的查询最能从并行查询中获益。

 

• 如何启用并行？

1. max_parallel_workers_per_gather：设置单个gather或者gather merge节点能够开始的worker的最大数量，默认值为2，设置为0将会禁用并行查询执行，所以需要设置为大于0的值
2. 规划器依据代价判断产生了并行查询计划
3. 系统不能运行在单用户模式下，该模式下，整个数据库运行在单个进程中，没有后台worker进程可用
4. 其他限制，详见http://postgres.cn/docs/12/when-can-parallel-query-be-used.html

参考 https://blog.csdn.net/joy0921/article/details/80130002?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control