GaussDB(for MySQL)剪枝功能，让查询性能提升70倍！

作者，祝青平，华为云数据库内核高级工程师。 擅长数据库优化器内核研发，9年数据库内核研发经验，参与多个TP以及AP数据库的研发工作。

近日，华为云数据库社区下面有这样一条用户提问留言：请问，如何通过MySQL提升DISTINCT，尤其是多表连接下DISTINCT的查询效率？

在回答这个问题之前，我们先了解一下DISTINCT。

在SQL语句中，DISTINCT关键词用于返回唯一不同的值，使用场景多，应用频繁。它可以用于做单列数据去重，例如，对公司雇员按照”first_name”去重后，得到1275条记录。

也可以做多列去重，即只有所有指定列的信息都相同时，才会被认为是重复的信息，例如，对公司雇员按照”first_name”和”gender”两列去重后得到2550条记录。

对于“多表连接+DISTINCT”场景，MySQL 8.0需要扫描表连接后的结果。当表连接数量多或基表数据量大时，扫描的数据量也会很大，会导致执行效率很低。如下示例，对7个表连接后的结果做DISTINCT，使用MySQL 8.0.30社区版本，执行耗时186秒，通过查看慢日志信息，发现扫描了约4400万行数据。

为了提升DISTINCT，尤其多表连接下DISTINCT的查询效率，GaussDB(for MySQL)在执行优化器中加入了剪枝功能，可以去除不必要的扫描分支，节省查询耗时。

GaussDB(for MySQL)剪枝方案

以下面的SQL执行为例，表t1有4行数据1,2,5,6。执行如下多表连接+DISTINCT：

表连接执行逻辑如下：

上述例子中，在MySQL 8.0.30社区版本执行器需要扫描60行数据才能获得结果集。找到满足条件的唯一结果{i=1,j=2,k=5}后，不会停止本轮扫描，而是继续扫描{i=1,j=5,k=1}及后续无用的数据，导致执行时间长。详细的执行流程参见下图：

针对如上的多表连接+DISTINCT执行效率慢的问题，GaussDB(for MySQL)在火山模型的执行器上实现了提前减枝优化，当找到满足的条件的DISTINCT值之后，通过全局变量判断是否可以提前结束本轮迭代，并层层退出，大幅减少了扫描工作量。

以上述SQL为例，在扫描{1,1,1},{1,1,2},{1,1,5},{1,1,8},{1,2,1},{1,2,2},{1,2,5} 7组数据后,找到满足DISTINCT 条件值 tt1.a "1"，立即结束本轮迭代，并停止上一层迭代。该例子中只需要扫描28行数据就可获得最终结果集，相比MySQL 8.0社区版本扫描60行，GaussDB(for MySQL)性能显著提升。

GaussDB(for MySQL)剪枝特性使用方法

打开特性开关：SET rds_nlj_distinct_optimize=ON;

通过”EXPLAIN FORMAT=TREE”查看特性是否生效，执行计划中出现” join with distinct optimization”关键字说明特性生效，查询过程中可进行减枝优化，提升多表JOIN+DISTINCT执行效率。

GaussDB(for MySQL)剪枝典型场景测试对比

前面提到的测试样例中，GaussDB(for MySQL)执行耗时2.7秒完成，只需要扫描数据量约61万行；相比MySQL 8.0 社区版本执行耗时约186秒，扫描数据量4400万，执行耗时和扫描数据量减少近70倍，实现了执行效率飞跃式提升。如下图所示：

因此，针对“多表连接+DISTINCT”的场景，GaussDB(for MySQL)在执行过程中动态剪枝，裁剪掉大量无用数据，减少执行过程中扫描数据量，是提升查询效率的秘密武器。

总结：

以上通过对GaussDB(for MySQL)剪枝方案、剪枝特性使用方法、典型场景测试对比结果的详细呈现，剖析了“多表连接+DISTINCT”场景中，GaussDB(for MySQL)大幅提升查询效率的原因。如果对华为云GaussDB(for MySQL)更多功能感兴趣的话，可以查看官方产品文档，了解更多：https://support.huaweicloud.com/gaussdbformysql/index.html

 

点击关注，第一时间了解华为云新鲜技术~