全部產品
Search
文件中心

Data Management:SQL審核最佳化建議在資料變更工單中的應用

更新時間:Jun 30, 2024

DMS安全規則中的SQL審核最佳化建議,可以對SQL Console或資料變更工單中執行的SQL進行規範性檢查(例如表要有備忘、表需要包含某些列),並提出相關最佳化建議(例如無鎖資料變更推薦),輔助DBA審核SQL語句,提高研發品質。本文將示範設定部分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. 登入Data Management 5.0
  2. 在頂部功能表列中,選擇安全與規範 > 安全規則

    說明

    若您使用的是極簡模式的控制台,請單擊控制台左上方的2023-01-28_15-57-17.png表徵圖,選擇全部功能 > 安全與規範 > 安全規則

  3. 單擊目標安全規則右側操作列下的編輯

    image

  4. 詳情頁左側的導覽列中,單擊SQL審核優化建議

  5. 單擊表要有主鍵規則右側的編輯

    image

    說明

    您可以單擊標記(選擇規則生效的範圍,包含DDL語句、DML語句)、行為動作狀態(開啟或關閉)右側的image表徵圖,進行快速篩選。

  6. 規則內容配置彈窗中,配置如下資訊。本樣本將行為動作設定為必須改進

    規則內容配置

    說明

    系統初始化規則中不包含必須改進。關於行為動作的更多說明,請參見系統行為動作

  7. 單擊確定

    在後續的資料開發、資料變更及SQL審核等功能中,SQL審核最佳化建議將根據配置的安全規則檢驗SQL語句。

步驟二:執行資料變更

  1. 登入Data Management 5.0
  2. 在頂部功能表列中,選擇資料庫開發 > 資料變更 > 普通資料變更

    說明

    若您使用的是極簡模式的控制台,請單擊控制台左上方的2023-01-28_15-57-17.png表徵圖,選擇全部功能 > 資料庫開發 > 資料變更 > 普通資料變更

  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審核報錯項上,查看具體的意見。

    報錯項1報錯項2

  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. 系統將再次進行預檢查。

    檢查通過。

    image

  8. 單擊提交審批,並等待管理員審批通過。

    審批通過後,會產生執行任務。

  9. 審批通過後,單擊執行變更,在任務設定對話方塊中設定任務執行參數,並單擊確定執行

    說明
    • 建立工單時,如果執行方式選擇審批通過後,自動執行,系統會自動跳過此步驟。

    • 已暫停任務重啟後,將從暫停位置繼續執行指令碼。

    配置項

    說明

    執行策略

    選擇執行策略:

    • 立即執行:單擊確定執行後,立即執行工單任務。

    • 定時執行:指定任務開始執行的時間。例如,在2024年05月22日0時執行任務。

    開啟整體事務

    選擇是否開啟整體事務,預設關閉。

    • 開啟:執行失敗則全部復原(僅限DML,DDL不在範圍內)。

    • 關閉:逐條提交SQL任務,執行失敗則終止任務,但不復原。

    開啟備份

    選擇是否開啟備份,預設開啟。

    說明
    • 僅執行UPDATE和DELETE語句時,支援備份資料。

    • MongoDB和Redis不支援備份資料。

    • 開啟:執行UPDATEDELETE語句前,系統自動產生對應的備份指令碼附件。

      • 如果資料庫類型為MySQL和MariaDB,產生REPLACE INTO備份語句。

        說明

        MySQL包括:RDS MySQLPolarDB MySQL版PolarDB分布式版、其他來源MySQL。

      • 如果資料庫類型為除MySQL、MariaDB外的其他引擎,產生INSERT備份語句。

    • 關閉:不產生備份附件。

    說明

    SQL任務的執行會受到安全規則SQL執行控制模組的監控,例如SQL執行前資料庫鎖逾時機制、資料庫負載檢查、SQL執行後Sleep策略等。如需修改預設的檢測點設定,請參考配置SQL執行控制

    • 工單執行成功後,在操作列單擊詳情,可查看工單執行狀態、執行次數、影響行數、執行指令碼、日誌等資訊。

    • 工單執行成功後,您可以進入目標資料庫的SQL視窗,查看資料變更是否符合預期。

其他動作

工單執行成功後,您可單擊詳情按鈕,查看執行日誌,包括執行語句、執行所用時間、調度詳情等資訊。