SQL审核优化建议在数据变更工单中的应用

背景信息

DMS的安全规则中已生成默认的SQL审核规范检查规则，例如：表要有备注、插入语句不能为not null列插入null值、插入语句中insert字段名不能重复。

SQL规范检查及SQL优化建议概览，请参见配置SQL审核优化建议。

准备工作

本文在演示操作前提前创建了migration_job表，建表语句如下：

CREATE TABLE `migration_job` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `gmt_create` datetime NOT NULL COMMENT '创建时间',
  `ref_id` bigint(20) unsigned NOT NULL COMMENT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试';

步骤一：配置安全规则

如下为您介绍配置安全协同模式的SQL审核优化建议规则。若需要调整自由操作和稳定变更模式的SQL审核优化建议规则，请单击对应管控模式右侧的SQL审核优化建议即可调整。

1. 登录数据管理DMS 5.0。

2. 单击控制台左上角的图标，选择全部功能 > 安全与规范（DBS） > 安全规则。

   说明

   若您使用的是非极简模式的控制台，在顶部菜单栏中，选择安全与规范（DBS） > 安全规则。

3. 单击目标安全规则右侧操作列下的编辑。

   

4. 在详情页左侧的导航栏中，单击SQL审核优化建议。

5. 单击表要有主键规则右侧的编辑。

   

   说明

   您可以单击标记（选择规则生效的范围，包含DDL语句、DML语句）、行为动作、状态（开启或关闭）右侧的图标，进行快速筛选。

6. 在规则内容配置弹窗中，配置如下信息。本示例将行为动作设置为必须改进。

   

   说明

   系统初始化规则中不包含必须改进。关于行为动作的更多说明，请参见系统行为动作。

7. 单击确定。

   在后续的数据开发、数据变更及SQL审核等功能中，SQL审核优化建议将根据配置的安全规则检验SQL语句。

步骤二：执行数据变更

1. 登录数据管理DMS 5.0。

2. 单击控制台左上角的图标，选择全部功能 > 数据库开发 > 数据变更 > 普通数据变更。

   说明

   若您使用的是非极简模式的控制台，在顶部菜单栏中，选择数据库开发 > 数据变更 > 普通数据变更。

3. 在数据变更工单申请页面，配置相关参数，并单击提交申请。

   说明

   关于参数的说明，请参见普通数据变更。

   • 选择已关联目标安全规则集的数据库实例。

   • 数据变更的SQL代码如下：

     CREATE TABLE test1 ( 
         id bigint COMMENT 'id', 
         name varchar(60) COMMENT 'name'
     ) DEFAULT CHARSET = utf8mb4 COLLATE utf8mb4_bin ENGINE = INNODB;
     
     INSERT INTO migration_job(id, ref_id, gmt_create, gmt_create) 
     VALUES(1, null, now(), now());

   

4. 工单提交后，系统将根据步骤一中配置的安全规则进行规范检查。

   SQL审核优化建议对会提交的语句进行规范审核，本示例产生：1项必须改进意见、 2项潜在问题、 1项建议改进意见。

5. 单击查看详情，将鼠标光标移动至SQL审核报错项上，查看具体的意见。

   

6. 根据提示信息，单击修改SQL，优化SQL代码，并单击确认修改。

   修改内容为：

   • 在CREATE语句中增加test1表的主键与备注。

   • 在INSERT语句中删除重复字段gmt_create，并对ref_id字段插入值。

   修改后的SQL代码如下：

   CREATE TABLE test1 (
       id bigint PRIMARY KEY COMMENT 'id',
       name varchar(60) COMMENT  'name'
   ) DEFAULT  CHARSET = utf8mb4 COLLATE  utf8mb4_bin ENGINE = INNODB  COMMENT  = '备注';
   INSERT INTO migration_job(id, ref_id, gmt_create) VALUES(1, 2, now());

7. 系统将再次进行预检查。

   检查通过。

   

8. 单击提交审批，并等待管理员审批通过。

   审批通过后，会生成执行任务。

9. 审批通过后，单击执行变更，在任务设置对话框中设置任务执行参数，并单击确定执行。

   说明

   • 创建工单时，如果执行方式选择审批通过后，自动执行，系统会自动跳过此步骤。

   • 已暂停的任务重启后，将从暂停位置继续执行脚本。

   配置项	说明
   执行策略	选择执行策略： 立即执行：单击确定执行后，立即执行工单任务。 定时执行：指定任务开始执行的时间。例如，在2024年05月22日0时执行任务。
   开启整体事务	选择是否开启整体事务，默认关闭。 开启：执行失败则全部回滚（仅限DML，DDL不在范围内）。 关闭：逐条提交SQL任务，执行失败则终止任务，但不回滚。
   开启备份	选择是否开启备份，默认开启。开启后，后续您才可以使用备份文件快速恢复数据。 说明 仅执行UPDATE和DELETE语句时，支持备份数据。 MongoDB和Redis不支持备份数据。 开启：执行UPDATE、DELETE语句前，系统自动生成对应的备份脚本附件。 如果数据库类型为MySQL和MariaDB，生成REPLACE INTO备份语句。 说明 MySQL包括：RDS MySQL、PolarDB MySQL版、PolarDB分布式版、其他来源MySQL。 如果数据库类型为除MySQL、MariaDB外的其他引擎，生成INSERT备份语句。 关闭：不生成备份附件。

   说明

   SQL任务的执行会受到安全规则中SQL执行控制模块的监控，例如SQL执行前数据库锁超时机制、数据库负载检查、SQL执行后Sleep策略等。如需修改默认的检测点设置，请参考配置SQL执行控制。

   • 工单执行成功后，在操作列单击详情，可查看工单执行状态、执行次数、影响行数、执行脚本、日志等信息。

   • 工单执行成功后，您可以进入目标数据库的SQL窗口，查看数据变更是否符合预期。