Federated引擎计算下推性能优化

简介

对于涉及Federated引擎的查询，社区版MySQL只有在可以利用索引RANGE/REF扫描时，才能将索引上的条件下推，其它的条件保留在本地数据库执行。而在实际场景中，一条查询的WHERE条件涉及的字段可能比较多，或者在索引字段上使用了函数导致无法直接使用索引。这时Federated引擎会向远程数据库发送全表扫描查询，将所有数据都拉回本地数据库后再执行查询。PolarDB MySQL版的条件下推功能尽可能地将查询中兼容的条件下推，可以使数据在远程数据库中就被过滤掉，从而提升了执行性能，降低了网络传输占用带宽以及本地拷贝和数据格式转换代价。

使用方法

您可以通过loose_optimizer_switch参数开启条件下推功能。具体操作请参见设置集群参数和节点参数。

示例

以下示例中包含1千万条数据的Sysbench表来模拟在不同选择率条件下，条件下推带来的性能提升。测试中使用的本地和远程Server集群规格为polar.mysql.x8.large（独享规格，4核32 GB），存储类型为PLS5。

创建远程表和Federated表

#创建远程表#
CREATE TABLE `sbtest1` (
  `id` int NOT NULL,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#创建Server#
CREATE SERVER s
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'username', PASSWORD 'password', HOST 'hostname', PORT 3306, DATABASE 'dbname');

#创建Federated表#
CREATE TABLE `sbtest1` (
  `id` int NOT NULL,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_1` (`k`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='s';

开启和关闭条件下推功能的SQL查询

#关闭条件下推的SQL查询#
set optimizer_switch='engine_condition_pushdown=off';
Query OK, 0 rows affected (0.03 sec)

EXPLAIN SELECT COUNT(*) FROM sbtest1 WHERE id + 1 < 100;
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9850101 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.04 sec)

#开启条件下推的SQL查询#
set optimizer_switch='engine_condition_pushdown=on';
Query OK, 0 rows affected (0.10 sec)

EXPLAIN SELECT COUNT(*) FROM sbtest1 WHERE id + 1 < 100;
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------------------------------------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                                                               |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------------------------------------------------------------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9850101 |   100.00 | Using where with pushed condition ((`federated`.`sbtest1`.`id` + 1) < 100) |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.05 sec)

上述SQL查询中，由于WHERE条件中使用了包含主键的函数与常量进行比较，最优的执行计划是使用全表扫描的方式，主要的差别在于是否将WHERE条件下推到远程Server执行。通过改变常量值为selectivity * table_size，构造不同的选择率条件，测试性能如下：

通过上图可以看到，当条件选择率selectivity较低时（小于0.1），开启条件下推相对于关闭条件下推有约一倍的性能提升。

在SQL查询过程中，交替开启和关闭条件下推功能。通过PolarDB控制台的性能监控功能，可以看到SQL查询期间对于网络带宽的占用情况：

• 当条件不下推时，SQL查询对于网络带宽的占用很高，且在不同选择率条件下网络带宽的占用率都是相同的；

• 当条件下推且选择率较低时（小于0.1），网络的流量非常小且随着选择率的增大而逐渐增高。与条件不下推情况下的网络IO相比有明显区别。

简介

Federated查询在获取远程表的数据时，返回的是所有列的值。但在实际情况中，一条查询可能只需要部分列的值，其他列的值并没有发挥作用，反而给远程Server增加了选取、格式转换数据的代价，占用了更多的网络传输带宽。因此PolarDB MySQL版在社区版MySQL的基础上做了进一步的优化，使得Federated查询只会向远程Server选取需要的列，大幅减少了远程Server选取数据的代价和网络传输带宽，提升了查询性能，表的列数越多，提升效果越明显。

示例

以下示例使用包含1百万条数据、不同列数的表来模拟真实场景。包含100列的表定义如下，重复定义Sysbench表中k、c、pad三列来拓宽表；通过减少字符类型的长度来支持定义更多列，而不至于占用更多的空间。

定义表

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(15) NOT NULL DEFAULT '',
  `pad` char(8) NOT NULL DEFAULT '',
  `k1` int(11) NOT NULL DEFAULT '0',
  `c1` char(15) NOT NULL DEFAULT '',
  `pad1` char(8) NOT NULL DEFAULT '',
  ...
  `k32` int(11) NOT NULL DEFAULT '0',
  `c32` char(15) NOT NULL DEFAULT '',
  `pad32` char(8) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

开启和关闭按需返回列功能的SQL查询

分别在开启和关闭按需返回列功能的场景下，使用SELECT pad FROM sbtest1进行测试。由于远程表在pad字段上没有索引，所以执行计划使用的是主键全表扫描，执行时间（秒）如下：

从上述加速比曲线可以看出，随着表列数的增加，按需返回列功能带来的加速比接近于线性提升。

此外，按需返回列功能也会增加远程Server的计划选择空间。如果查询所访问的列可以使用索引，那么执行计划会在远程表上使用索引扫描，而不是全表扫描，这将会进一步带来性能的提升：

#关闭按需返回列功能进行查询#
SET federated_fetch_select_field_enabled=OFF;
Query OK, 0 rows affected (0.19 sec)

SELECT SUM(k) FROM federated_col_64.sbtest1;
+--------------+
| SUM(k)       |
+--------------+
| 499868973740 |
+--------------+
1 row in set (5.20 sec)

#开启按需返回列功能进行查询#
SET federated_fetch_select_field_enabled=ON;
Query OK, 0 rows affected (0.11 sec)

SELECT SUM(k) FROM federated_col_64.sbtest1;
+--------------+
| SUM(k)       |
+--------------+
| 499868973740 |
+--------------+
1 row in set (0.45 sec)

从上述示例可以看出，在k列上使用索引后，开启按需返回列功能后查询性能提升了10倍以上。

简介

社区版MySQL在Federated查询上的分页查询由于无法将条件全部下推，所以必须将远程Server的全部数据返回本地Server，使用WHERE条件过滤后再进行分页。当PolarDB MySQL版支持条件下推后，如果SQL语句只涉及单个Federated表，且不包含聚合、窗口、UNION、DISTINCT、HAVING等影响结果正确性的语法时，就可以将[ORDER BY <cols>] LIMIT OFFSET语法下推到远程Server进行处理，仅给本地Server返回需要的数据，本地Server就可以直接输出结果到客户端。

使用方法

您可以通过loose_optimizer_switch参数开启[ORDER BY <cols>] LIMIT OFFSET下推优化功能。具体操作请参见设置集群参数和节点参数。

示例

创建表

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(15) NOT NULL DEFAULT '',
  `pad` char(8) NOT NULL DEFAULT '',
  `k1` int(11) NOT NULL DEFAULT '0',
  `c1` char(15) NOT NULL DEFAULT '',
  `pad1` char(8) NOT NULL DEFAULT '',
  ...
  `k32` int(11) NOT NULL DEFAULT '0',
  `c32` char(15) NOT NULL DEFAULT '',
  `pad32` char(8) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

开启和关闭[ORDER BY <cols>] LIMIT OFFSET下推优化功能的SQL查询

#开启[ORDER BY <cols>] LIMIT OFFSET下推优化功能进行查询#
set optimizer_switch='limit_offset_pushdown=on';
Query OK, 0 rows affected (0.05 sec)

EXPLAIN SELECT * FROM federated.sbtest1 LIMIT 100 OFFSET 1000;
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9850101 |   100.00 | Using limit-offset pushdown |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.06 sec)

#关闭[ORDER BY <cols>] LIMIT OFFSET下推优化功能进行查询#
set optimizer_switch='limit_offset_pushdown=off';
Query OK, 0 rows affected (0.05 sec)

EXPLAIN SELECT * FROM federated.sbtest1 LIMIT 100 OFFSET 1000;
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9850101 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.05 sec)

#关闭按需ORDER下推#
SET federated_pushdown_order_enabled=OFF;
Query OK, 0 rows affected (0.19 sec)

#开启按需ORDER下推#
SET federated_pushdown_order_enabled=ON;
Query OK, 0 rows affected (0.11 sec)

mysql> explain select * from federated.sbtest1 order by id limit 100 offset 1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 9850101 | 100.00 | Using limit-offset pushdown; Using order pushdown `id` |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------+

分别在开启和关闭[ORDER BY <cols>] LIMIT OFFSET下推优化功能的场景下，在1千万条数据的Sysbench表上使用SELECT * FROM federated.sbtest1 LIMIT 100 OFFSET number进行测试。不同OFFSET值的测试结果如下：

从上表可以看出，OFFSET值越小，加速越明显，最高约60倍以上的加速比。