SQL审核优化建议最佳实践

功能介绍

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

在开发人员提交SQL语句时，SQL审核优化建议会检测SQL语句是否符合研发规范（表属性、列属性、索引属性、数据查询规范、数据变更规范等），辅助DBA审核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='测试';

• 若实例的管控模式为安全协同，您可以额外创建安全规则集，并将数据库实例关联目标安全规则集。具体操作，请参见创建安全规则和修改数据库实例的安全规则。

步骤一：配置安全规则

1. 登录新版DMS控制台。
2. 在页面顶部，选择全部功能 > 系统管理 > 安全规则，进入安全规则页面。
3. 单击目标安全规则右侧操作列下的编辑。
   
4. 在详情页左侧的导航栏中，单击SQL审核优化建议。
5. 单击表要有主键规则右侧的编辑。
   

   说明 您可以单击标记（规则生效范围，目前包含DDL语句、DML语句（增删改查）、行为动作、状态（开启或关闭规则）右侧的图标，进行快速筛选。
6. 在规则内容配置弹窗中，配置如下信息。
   

   参数名	说明
   行为动作	将行为动作设置为必须改进。 说明 系统初始化规则中不包含必须改进，关于行为动作的更多说明，请参见系统行为动作。
   备注	输入此规则的备注信息，如业务背景信息。

7. 单击确定。
   SQL审核优化建议将在开发过程中，根据配置的安全规则检验SQL语句。

步骤二：执行数据变更

在配置好安全规则后，执行数据变更工单。

1. 登录新版DMS控制台。
2. 在页面顶部，选择全部功能 > 数据方案 > 普通数据变更。
3. 在数据变更工单申请页面，配置相关参数，并单击提交申请。
   说明 关于参数的说明，请参见表 1。

   • 选择已关联目标安全规则集的数据库实例。
   • 数据变更的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. 单击执行变更，参考下表进行任务设置，单击确定执行。
   说明 在创建工单时，若执行方式选择为审批通过后，自动执行，系统会自动跳过此步骤。

   配置项	说明
   是否立即执行	选择是否立即执行，支持： 立即执行：提交即刻执行。 定时执行：按需指定在业务特定时间执行。 默认为立即执行。
   是否事务控制	选择是否开启事务控制，支持： 开启：遇到失败则全部回滚（仅限DML，DDL不在范围内）。 关闭：逐条提交，遇到失败则终止但不回滚。 默认为关闭。
   是否备份数据	选择是否备份数据，支持： 开启：针对UPDATA、DELETE语句将要影响数据，系统生成对应的INSERT脚本附件，对原数据进行备份。 关闭：不生成备份附件。 默认为开启。