MogDB 3.1版本在AI4DB方面又有很大的增强，其中有一个非常重要的组件DBmind，引入了一个SQL改写工具即 SQL Rewriter。根据预先设定的规则，将查询语句转换为更为高效或更为规范的形式，使得查询效率得以提升。这里我们就为大家简单演示一下其具体的用法。
首先准备一下环境
MogDB 3.1环境缺少constant 文件，因此需要先安装一堆Python依赖包。
cat >> $GAUSSHOME/bin/constant <<EOF
MIN_PYTHON_VERSION="(3,6)"
MAX_PYTHON_VERSION="(3,9)"
EOF
可以通过查看$GAUSSHOME/bin/dbmind/requirements-aarch64.txt 来确认具体的环境要求。
我们先配置好PIP环境.
mkdir -p ~/.pip
cat > ~/.pip/pip.conf <<EOF
[global]
index-url = https://repo.huaweicloud.com/repository/pypi/simple
[install]
trusted-host = https://repo.huaweicloud.com
准备就绪之后，就可以开始安装python依赖包了。
python3 -m pip install --upgrade pip --user
python3 -m pip install -r $GAUSSHOME/bin/dbmind/requirements-aarch64.txt --trusted-host repo.huaweicloud.com -i http://repo.huaweicloud.com/repository/pypi/simple --user
python3 -m pip install psycopg2 --user
如果报错，换成
python3 -m pip install psycopg2-binary --user
万事俱备，只欠东风！接下来我们就来测一测DBmind的能力，这里我就为大家揭开神秘面纱。
先来了解一下这个强大的AI工具的基本用法：
gs_dbmind component sql_rewriter [PORT] [DBNAME] [SQLFILE] --db-host [default socketfile] --db-user [default init user] --schema [default public]
当然对于其支持的自动改写规则，也并非是所有的都支持，目前暂时支持如下的一些rule。
class Delete2Truncate(Rule):
class Star2Columns(Rule):
class Having2Where(Rule):
class AlwaysTrue(Rule):
class DistinctStar(Rule):
class UnionAll(Rule):
class OrderbyConst(Rule):
class Or2In(Rule):
class OrderbyConstColumns(Rule):
class ImplicitConversion(Rule):
class SelfJoin(Rule):
这里我们事先准备好一些相对简单的测试用例，内容如下：
cat > rewrite_example.sql <<EOF
delete /* 无条件的delete转换成truncate */
from test1;
select /* 星号替换成具体列名 */ *
from test1;
select /*Union替换成 Union All*/id,name
from test1
union
select id,name
from test2;
select /*order by 列号替换成具体列名 */id,name
from test1 order by 1;
select /*返回单行的去掉order by */ id
from test1 where id=1 order by id;
select /*Or 改为 in */id,name
from test1 where id = 0 or id = 1;
select /* Having 列不属于聚合函数的改成Where */ id,count(*)
from test1 group by id having id > 0 ;
select /*去掉恒为True的表达式*/id,name
from test1 where 1=1;
select /*预计算转换*/ id,name
from test1 where id + 1 < 2 ;
select /*自连接非等式连接改为等式连接*/ a.id,b.name
from test1 a , test1 b
where a.id - b.id <= 20 and a.id > b.id;
select /*distinct 带主键去掉distinct */ distinct *
from test1;
EOF
测试脚本准备完毕之后，我们就可以使用DBmind来验证一下其改写能力了。具体的执行命令如下：
gs_dbmind component sql_rewriter $PGPORT postgres rewrite_example.sql --db-user tpcc --db-host 127.0.0.1
最后我们来看看dbmind执行的结果如想象的那般神奇。
+-------------------------------------------------------+----------------------------------------------------+
| Raw SQL | Rewritten SQL |
+-------------------------------------------------------+----------------------------------------------------+
| delete | TRUNCATE TABLE test1; |
| /* 无条件的delete转换成truncate */ | |
| from test1; | |
| select | SELECT id, |
| /* 星号替换成具体列名 */ * | name |
| from test1; | FROM test1; |
| select | SELECT id, |
| /*Union替换成 Union All*/id,name | name |
| from test1 | FROM test1 |
| union | UNION ALL |
| select id,name | SELECT id, |
| from test2; | name |
| | FROM test2; |
| select | SELECT id, |
| /*order by 列号替换成具体列名 */id,name | name |
| from test1 order by 1; | FROM test1 |
| | ORDER BY id; |
| select | SELECT id |
| /*返回单行的去掉order by */ id | FROM test1 |
| from test1 where id=1 order by id; | WHERE id = 1; |
| select | SELECT id, |
| /*Or 改为 in */id,name | name |
| from test1 where id = 0 or id = 1; | FROM test1 |
| | WHERE id IN (0, |
| | 1); |
| select | SELECT id, |
| /* Having 列不属于聚合函数的改成Where */ id,count(*) | COUNT(*) |
| from test1 group by id having id > 0 ; | FROM test1 |
| | WHERE id > 0 |
| | GROUP BY id; |
| select | SELECT id, |
| /*去掉恒为True的表达式*/id,name | name |
| from test1 where 1=1; | FROM test1; |
| select | SELECT id, |
| /*预计算转换*/ id,name | name |
| from test1 where id + 1 < 2 ; | FROM test1 |
| | WHERE id < 1; |
| select | SELECT * |
| /*自连接非等式连接改为等式连接*/ a.id,b.name | FROM |
| from test1 a , test1 b | (SELECT a.id, |
| where a.id - b.id <= 20 and a.id > b.id; | b.name |
| | FROM test1 AS a, |
| | test1 AS b |
| | WHERE TRUNC((a.id) / 20) = TRUNC(b.id / 20) |
| | AND a.id > b.id |
| | UNION ALL SELECT a.id, |
| | b.name |
| | FROM test1 AS a, |
| | test1 AS b |
| | WHERE TRUNC((a.id) / 20) = TRUNC(b.id / 20 + 1) |
| | AND a.id - b.id <= 20); |
| select | SELECT id, |
| /*distinct * 带主键去掉distinct */ distinct * | name |
| from test1; | FROM test1; |
+-------------------------------------------------------+----------------------------------------------------+
我们可以看到DBmind完美的改写了11个SQL。 怎么样，是不是很Cool~~~~
本文由mdnice多平台发布