GreatSQL turbo引擎尝鲜
原创
官方文档 https://greatsql.cn/docs/8.0.32-27/5-enhance/5-1-highperf-turbo-engine.html
GreatSQL是国产MySQL数据库（基于PerconaServer），在最新的8.0.32-27版本中引入了基于duckdb的turbo引擎。
实际测试了下，查询的性能是InnoDB版本的数倍，如下例子：
-- 先安装turbo 引擎
INSTALL PLUGIN turbo SONAME 'turbo.so';
-- 会话级别开启
SET turbo_enable=ON;
SET turbo_cost_threshold=0;
-- 也可以在SQL使用如下的注解启用turbo引擎
[sbtest] > select /*+ SET_VAR(turbo_enable=ON) SET_VAR(turbo_cost_threshold=0) */ count(*),pad from sbtest21 group by pad order by count(*) desc limit 10;
+----------+-------------------------------------------------------------+
| count(*) | pad |
+----------+-------------------------------------------------------------+
| 83 | 66202187557-84435839944-55667215409-90570358414-31637104658 |
| 83 | 76850358328-37456088098-63227150241-80408017321-55708658131 |
| 83 | 15707544335-17078548588-82990222641-96684198494-32704412526 |
| 83 | 42148153820-63912435911-60711471440-30138158702-72811841654 |
| 83 | 69542395702-08901993005-59193004039-06326397684-22442381922 |
| 83 | 21929031373-52156291688-55023478560-30112241256-72211162737 |
| 83 | 81671051211-70956666686-54385232205-87790856148-92928150375 |
| 83 | 59263197956-34457427299-64894204178-13211169732-45101629008 |
| 83 | 97731413648-35551438673-64192810799-86237123761-77807200301 |
| 83 | 91068880342-27161739786-24992014538-41179841042-13195435331 |
+----------+-------------------------------------------------------------+
10 rows in set (0.32 sec)
-- 再插入一些测试数据
[sbtest] > insert into sbtest21(k,c,pad) select k,c,pad from sbtest21 limit 900;
Query OK, 900 rows affected (0.10 sec)
Records: 900 Duplicates: 0 Warnings: 0
[sbtest] > insert into sbtest21(k,c,pad) select k,c,pad from sbtest21 limit 900;
Query OK, 900 rows affected (0.09 sec)
Records: 900 Duplicates: 0 Warnings: 0
[sbtest] > insert into sbtest21(k,c,pad) select k,c,pad from sbtest21 limit 900;
Query OK, 900 rows affected (0.07 sec)
Records: 900 Duplicates: 0 Warnings: 0
-- 可以看到新插入数据后，是没有问题的
[sbtest] > select count(*) from sbtest21;
+----------+
| count(*) |
+----------+
| 4108206 |
+----------+
1 row in set (0.15 sec)
-- 查询也是秒出
[sbtest] > select /*+ SET_VAR(turbo_enable=ON) SET_VAR(turbo_cost_threshold=0) */ count(*),pad from sbtest21 group by pad order by count(*) desc limit 10;
+----------+-------------------------------------------------------------+
| count(*) | pad |
+----------+-------------------------------------------------------------+
| 174 | 36461252421-61685722109-29308901920-64946508497-61380124049 |
| 174 | 48285699294-91409726503-62143793983-32056797352-49834383441 |
| 174 | 74050453667-72829333445-97031782856-23538680494-16010162407 |
| 174 | 29182419251-15628869716-09812798039-08930172270-08957112562 |
| 174 | 21874646890-22044056556-23516691783-58365979744-67610025809 |
| 174 | 49929902094-28050743503-00397462464-81141937432-41746769682 |
| 174 | 83039633702-78867865170-17535838289-70531953919-80420010993 |
| 174 | 44379021374-67566098182-32471755788-70718241680-84761487238 |
| 174 | 87842320461-85306265600-18140200737-46546135853-84635271072 |
| 174 | 73593763774-94745379539-36773876534-85457279752-86949985152 |
+----------+-------------------------------------------------------------+
10 rows in set (0.61 sec)
如果另开一个窗口（没有启用turbo加速），可以看到查询速度还是比较慢的
[sbtest] > select count(*),pad from sbtest21 group by pad order by count(*) desc limit 10;
+----------+-------------------------------------------------------------+
| count(*) | pad |
+----------+-------------------------------------------------------------+
| 174 | 36461252421-61685722109-29308901920-64946508497-61380124049 |
| 174 | 84775460752-15073585199-78954694683-28622417687-45891940567 |
| 174 | 81671051211-70956666686-54385232205-87790856148-92928150375 |
| 174 | 59263197956-34457427299-64894204178-13211169732-45101629008 |
| 174 | 97731413648-35551438673-64192810799-86237123761-77807200301 |
| 174 | 45390620154-21838174026-16550111445-82748738185-67906448220 |
| 174 | 91068880342-27161739786-24992014538-41179841042-13195435331 |
| 174 | 14275276037-35477884076-18040802680-47366705146-95937312945 |
| 174 | 91467487600-64313987041-30392523208-64318284976-54067225522 |
| 174 | 66202187557-84435839944-55667215409-90570358414-31637104658 |
+----------+-------------------------------------------------------------+
10 rows in set (4.66 sec)在生产数据测试场景下，性能也是InnoDB的3倍以上，如下例子：
set global turbo_memory_limit= 1073741824 ; -- 改为1GB
select
/*+ SET_VAR(turbo_enable=ON) SET_VAR(turbo_cost_threshold=0) */
DATE_FORMAT(a.request_time, '%Y%m') AS '时间',
count(distinct concat(DATE_FORMAT(a.request_time,'%Y%m%d'), a.fasl )) as '发票数量'
from sbtest111 a
where a.channel = 'aaaaaaaaa' and a.result_status = 1
and a.fasl like '%invoice_result%'
group by DATE_FORMAT(a.request_time, '%Y%m');
使用turbo引擎，耗时25秒
select
DATE_FORMAT(a.request_time, '%Y%m') AS '时间',
count(distinct concat(DATE_FORMAT(a.request_time,'%Y%m%d'), a.fasl )) as '发票数量'
from sbtest111 a
where a.channel = 'aaaaaaaaa' and a.result_status = 1
and a.fasl like '%invoice_result%'
group by DATE_FORMAT(a.request_time, '%Y%m');
使用innodb引擎，耗时1分48秒
select
/*+ SET_VAR(turbo_enable=ON) SET_VAR(turbo_cost_threshold=0) */
DATE_FORMAT(a.request_time, '%Y%m') AS '时间',
count(distinct concat(DATE_FORMAT(a.request_time,'%Y%m%d'), a.fasl )) as '发票数量'
from sbtest111 a
where a.channel = 'aaaaaaaaa' and a.result_status = 1
and a.fasl like '%tax_tick_info%'
group by DATE_FORMAT(a.request_time, '%Y%m');
使用turbo引擎，耗时29秒
select
DATE_FORMAT(a.request_time, '%Y%m') AS '时间',
count(distinct concat(DATE_FORMAT(a.request_time,'%Y%m%d'), a.fasl )) as '发票数量'
from sbtest111 a
where a.channel = 'aaaaaaaaa' and a.result_status = 1
and a.fasl like '%tax_tick_info%'
group by DATE_FORMAT(a.request_time, '%Y%m');
使用innodb引擎，耗时1分43秒
但是，目前看turbo并不能适用于全部的SQL（例如sql中where条件有json列的turbo查询加速），更多的请参考官方文档。
注意： 不要全局开启turbo_enable参数，会导致部分sql有warning，实际测试发现django的ORM查询遇到sql有warning情况下，直接就报错了。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
腾讯云开发者
