Presto/Trino的Mysql Connector使用和查询下推优化

1. Mysql连接器

底层是通过JDBC驱动来查询Mysql的数据源

一条SQL中有多个表，则会对应创建多个JDBC连接

1. 在Mysql中准备数据

[root@bigdata005 ~]#
[root@bigdata005 ~]# mysql -h bigdata005 -P 3306 -u root -pRoot_123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36582
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> create database trino_test;
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> use trino_test;
Database changed
mysql> 
mysql> create table tb_test(
    -> id int,
    -> name varchar(64),
    -> age int
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> insert into tb_test(id, name, age) values(3, 'zihang_san', 13), (4, 'li_si', 14), (5, 'wang_wu', 15);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from tb_test;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    3 | zhang_san |   13 |
|    4 | li_si     |   14 |
|    5 | wang_wu   |   15 |
+------+-----------+------+
3 rows in set (0.00 sec)

mysql> 

2. 在所有节点上新建catalog配置文件，然后重启Trino

[root@trino1 etc]# 
[root@trino1 etc]# pwd
/root/trino-server-367/etc
[root@trino1 etc]# 
[root@trino1 etc]# mkdir catalog
[root@trino1 etc]# 
[root@trino1 etc]# cd catalog/
[root@trino1 catalog]# 
[root@trino1 catalog]# cat test_mysql.properties 
connector.name=mysql
connection-url=jdbc:mysql://192.168.8.115:3306
connection-user=root
connection-password=Root_123

[root@trino1 catalog]# 
[root@trino1 catalog]# cd /root/trino-server-367
[root@trino1 trino-server-367]# 
[root@trino1 trino-server-367]# bin/launcher stop
Not running
[root@trino1 trino-server-367]# 
[root@trino1 trino-server-367]# bin/launcher start
Started as 46215
[root@trino1 trino-server-367]# 

• connection-url也可以配置数据库：connection-url=jdbc:mysql://192.168.8.115:3306;databaseName=trino_test

3. 使用新创建的catalog

[root@trino1 ~]# 
[root@trino1 ~]# ./trino --server trino1:8080
trino> 
trino> show catalogs;
  Catalog   
------------
 system     
 test_mysql 
(2 rows)

Query 20211227_102504_00005_tahve, FINISHED, 2 nodes
Splits: 6 total, 6 done (100.00%)
0.75 [0 rows, 0B] [0 rows/s, 0B/s]

trino> 
trino> show schemas from test_mysql;
       Schema       
--------------------
 trino_test         
(1 rows)

Query 20211227_102851_00010_tahve, FINISHED, 3 nodes
Splits: 6 total, 6 done (100.00%)
3.21 [1 rows, 153B] [1 rows/s, 48B/s]

trino>
trino> use test_mysql.trino_test;
USE
trino:trino_test>
trino:trino_test> show tables;
  Table  
---------
 tb_test 
(1 row)

Query 20211227_102939_00013_tahve, FINISHED, 3 nodes
Splits: 6 total, 6 done (100.00%)
4.78 [1 rows, 27B] [0 rows/s, 6B/s]

trino:trino_test> 
trino:trino_test> select * from tb_test;
 id |   name    | age 
----+-----------+-----
  3 | zhang_san |  13 
  4 | li_si     |  14 
  5 | wang_wu   |  15 
(3 rows)

Query 20211227_102952_00015_tahve, FINISHED, 2 nodes
Splits: 2 total, 2 done (100.00%)
2.31 [3 rows, 0B] [1 rows/s, 0B/s]

trino:trino_test> 

2. 查询下推

目前Presto/Trino只支持过滤下推和列投影下推

trino:trino_test> 
trino:trino_test> select name, count(*) num from tb_test where id in (3, 4, 5) group by name;
   name    | num 
-----------+-----
 zhang_san |   1 
 li_si     |   1 
 wang_wu   |   1 
(3 rows)

Query 20211227_111621_00017_tahve, FINISHED, 2 nodes
Splits: 6 total, 6 done (100.00%)
2.52 [3 rows, 0B] [1 rows/s, 0B/s]

trino:trino_test> 

• 过滤下推：指的是id的过滤在mysql端执行
• 列投影下推：指的是Trino只需要获取name字段，则mysql只返回name字段给Trino

所以这里的聚合操作，需要在Trino端进行。如果想把聚合操作下推到mysql端，可以利用mysql的视图，在mysql中创建查询视图

mysql> 
mysql> use trino_test;
Database changed
mysql> 
mysql> create view name_count_view as select name, count(*) num from tb_test where id in (3, 4, 5) group by name; 
Query OK, 0 rows affected (0.02 sec)

mysql> 

则在Trino执行select * from name_count_view;，则会全部下推到mysql

trino:trino_test> 
trino:trino_test> select * from name_count_view;
   name    | num 
-----------+-----
 zhang_san |   1 
 li_si     |   1 
 wang_wu   |   1 
(3 rows)

Query 20211227_113452_00018_tahve, FINISHED, 2 nodes
Splits: 2 total, 2 done (100.00%)
1.20 [3 rows, 0B] [2 rows/s, 0B/s]

trino:trino_test>