SQLレビュー機能の使用 - Data Management - Alibaba Cloud ドキュメントセンター

Data Management DMSのSQLレビュー機能は、一度に複数のSQL文をレビューし、最適化の提案を提供します。この機能を使用して、インデックスを使用していない、またはデータベース開発標準に準拠していないSQL文が実行されないようにし、データベースのパフォーマンスを向上させたり、SQLインジェクションのリスクを軽減したりできます。このトピックでは、SQLレビュールールを設定する方法、およびSQLステートメントをレビュー用に送信する方法について説明します。このトピックで使用される例では、XMLファイル内の複数のSQL文がレビューされます。

背景情報

通常、プロジェクトが公開される前に、使用されるすべてのSQL文をレビューする必要があります。これにより、データベース開発標準に準拠していないSQL文がオンライン環境に公開されなくなります。しかしながら、このプロセスは、手動で実行される場合、長い時間を要する。

DMSは、SQLレビュールールに基づいて自動化された方法でSQLステートメントをレビューし、最適化の提案を提供するSQLレビュー機能を提供します。コンソールでSQLレビュールールをカスタマイズできます。このトピックでは、次のSQLレビュールールを使用します。

WHERE句はUPDATEおよびDELETEステートメントで推奨されます
SQLインジェクションのリスク検出
実行計画のインデックスチェック
変更時間列の更新は、UPDATEステートメントで推奨されます
フォースインデックスのリスク検出

準備

ターゲットデータベースインスタンスにtest_sql_review_tableという名前のテーブルを作成し、テストデータ構築機能を使用してテーブルにデータを挿入します。次のステートメントを使用して、テーブルを作成します。

CREATE TABLE `test_sql_review_table` (
  `id` BIGINT(20) UNSIGNED  NOT NULL AUTO_INCREMENT,
  `gmt_create` DATETIME NOT NULL,
  `gmt_modified` DATETIME NOT NULL,
  `detail_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `name` VARCHAR(256) DEFAULT NULL,
  `db_id` BIGINT(20) DEFAULT NULL,
  `is_delete` VARCHAR(1) DEFAULT NULL,
  `file_content_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

レビューするファイルを準備します。この例では、次のサンプルコードを含むMyBatisフレームワークに基づくXMLファイルを使用します。

[MyBatisサンプルコード] をクリックして、サンプルコードをダウンロードできます。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xxx.namespace">

    <sql id="SELECT_ALL_FROM">
        SELECT
        id,
        gmt_create,
        gmt_modified,
        detail_id,
        name,
        db_id,
        is_delete,
        file_content_id
        FROM test_sql_review_table sf
    </sql>

    <select id="getByPK" resultType="com.xxx.TestSQLReviewTableDO">
        <include refid="SELECT_ALL_FROM"/>
        WHERE id=${pk}
    </select>

    <select id="getXxxList" resultType="com.xxx.TestSQLReviewTableDO">
        <include refid="SELECT_ALL_FROM"/>
        WHERE
        <foreach collection="pks" open="sf.id in (" item="item" separator="," close=")">
            #{item}
        </foreach>
        <if test="searchKey != null and searchKey!=''">
            AND sd.name like concat('%',#{searchKey}, '%')
        </if>
        AND sf.is_delete='N'
    </select>

    <update id="updateAaaa">
        UPDATE test_sql_review_table
        SET
        db_id=#{dbId}
        WHERE detail_id=#{detailId}
        AND is_delete='N'
    </update>

    <delete id="deleteXxxx">
        DELETE FROM test_sql_review_table
    </delete>

</mapper>

手順1: SQLレビュールールの設定

この例では、WHERE句はUPDATEおよびDELETEステートメントで推奨されますセキュリティルールをカスタマイズする必要があります。他のセキュリティルールのデフォルト設定を使用します。次の例は、セキュリティコラボレーションモードでセキュリティルールを設定する方法を示しています。詳細については、「SQLレビューの最適化」をご参照ください。

DMS管理者としてのDMSコンソールV5.0にログインします。
上部のナビゲーションバーで、セキュリティと仕様 > セキュリティルール を選択します。
説明
DMSコンソールをシンプルモードで使用する場合は、左上隅のアイコンの上にポインターを移動し、[すべての機能] > [セキュリティと仕様] > [セキュリティルール] を選択します。
左側のナビゲーションウィンドウで、[SQL監査最適化の推奨事項] をクリックします。
変更するセキュリティルールセットを見つけて、[操作] 列の [編集] をクリックします。
を検索するFind theUpdate/delete文はwhere条件を指定することを推奨しますセキュリティルールとクリック編集[アクション] 列に表示されます。
表示される ルールコンテンツ構成 ダイアログボックスで、[動作] アクションを [改善する必要がある] に設定し、[OK] をクリックします。

ステップ2: レビューのためにSQL文を提出する

次の例では、MyBatisフレームワークに準拠したXMLファイル内のSQL文を確認し、チェック結果に基づいて修正します。詳細については、「SQLレビュー」をご参照ください。

DMSコンソールV5.0 にログインします。
上部のナビゲーションバーで、データベース开発 > SQLレビュー > SQL監査チケット を選択します。
説明
DMSコンソールをシンプルモードで使用する場合は、コンソールの左上隅にあるアイコンの上にポインターを移動し、[すべての機能] > [データベース開発] > [SQLレビュー] > [SQL監査チケット] を選択します。
Sql監査作業指示アプリケーション ページで、[SQLレビュー] をクリックし、[アプリケーション] セクションでパラメーターを設定します。 申請書の送信 をクリックします。
説明
- 設定したセキュリティルールセットに関連付けられているデータベースインスタンスを選択します。
- この例では、このトピックのPreparationsセクションから取得したサンプルコードをアップロードします。

SQLレビュー結果を表示し、ステートメントを変更します。

次の検査結果が返されます。 SQL审核结果

SQL	SQLレビュー結果	SQL最適化の提案
`<delete id="deleteXxxx"> DELETE FROM test_sql_review_table </delete>`	改善する必要があります: DELETEステートメントでWHERE句を推奨します。説明デフォルトでは、このセキュリティルールのBehavioral actionパラメーターは推奨改善に設定されています。この例では、Behavioral actionパラメーターは、「手順1: データベースのSQLレビュールールの設定」セクションの改善する必要がありますに設定されています。	必要に応じてWHERE句を追加します。テーブル全体を削除する必要がある場合は、次のWHERE句を使用します。WHERE 1 = 1。例： `DELETE FROM test_sql_review_table WHERE id = #{pk}`
`<select id="getByPK" resultType="com.xxx.TestSQLReviewTableDO"> <include refid="SELECT_ALL_FROM"/> WHERE id=${pk} </select>`	潜在的な問題: ドル記号 ($) を使用してSQL文を連結すると、SQLインジェクションのリスクが発生します。	SQL文の ${pk} を#{pk} に置き換えて、SQLインジェクションのリスクを防ぎます。例： `<include refid="SELECT_ALL_FROM"/> WHERE id=#{pk}`
`<update id="updateAaaa"> UPDATE test_sql_review_table SET db_id=#{dbId} WHERE detail_id=#{detailId} AND is_delete='N' </update>`	推奨改善: UPDATEステートメントで変更時間列の更新を推奨: gmt_modified 潜在的な問題: SQL実行プランはインデックスを使用しません。インデックスの推奨: DMSでは、インデックスを1つ追加することを推奨します。	UPDATEステートメントのSET句に`gmt_modified = NOW()` を追加します。 [詳細] パネルのインデックスの推奨事項タブで、インデックスを使用するSQL文を取得します。次に、DDLベースのロックフリー変更のチケットを起票します。詳細については、「ロックフリーDDL操作の実行」をご参照ください。例： `UPDATE test_sql_review_table SET db_id=#{dbId}, gmt_modified = NOW() WHERE detail_id=#{detailId} and is_delete='N'`

説明

XMLファイルに動的SQL文が含まれている場合、DMSは変数の置換を試み、SQL文の実行計画を照会します。 SQL文の実行計画の詳細を表示するには、[操作] 列の [詳細] をクリックします。
歴史的な理由により、SQL文には現在解決できない、または解決する必要のない問題が含まれている場合があります。この場合、これらのSQL文を手動で確認し、承認済みまたは不承認としてタグ付けできます。 [操作] 列で、[マニュアルレビュー] > [承認] または [マニュアルレビュー]> [非承認] を選択します。

変更後、DMSコンソールでページを更新し、SQLレビュー結果を再度確認します。

承認ステップで、[承認の申請] をクリックします。 DMS管理者またはDBAは、SQL文を再度チェックします。
チケットが承認された後、チケットは閉じられます。
説明
変更されたSQL文が依然としてチェックに失敗するか、DMSで解析できない場合は、[承認] ステップで [承認の送信] をクリックするとエラーメッセージが返されます。