SQL 詳細とは：概要と例 - PolarDB - Alibaba Cloud ドキュメントセンター

背景情報

データベースを使用する際、データベースやテーブルに対するスキーマ変更 (列やインデックスの作成、追加、削除など) やロック操作は、通常の業務運用に影響を与える可能性があります。これらの操作に関する監査ログは、データベースの運用担当者にとって重要です。運用担当者は、各操作のユーザーアカウント、クライアント IP アドレス、開始時刻、完了時刻などの詳細を把握する必要があります。

従来、監査ログでは、すべての SQL ステートメントを監査するグローバルスイッチが使用されます。監査レコードは網羅的ですが、この方法はコストが比較的高く、情報を保存するために追加コンポーネントが必要になる場合があります。

PolarDB for MySQL は、データベースとテーブルのスキーマ変更やロック操作を詳細に監査するための SQL 詳細機能を提供します。この機能は、関連するステートメントの実行が開始されるとすぐに監査レコードをキャプチャし、レコードをデータベースのシステムテーブルに格納します。ビジネスニーズに基づいて監査レコードの保持期間を設定できます。保持期間を超えた監査レコードは自動的に削除されます。この機能の監査コストは非常に低く抑えられています。たとえば、各監査レコードが 1 KB のストレージを消費し、1 日あたり 1,024 回のスキーマ変更が発生し、保持期間が 30 日である場合、必要なストレージはわずか 30 MB です。

前提条件

PolarDB クラスターは、以下のいずれかのバージョン要件を満たす必要があります：

PolarDB for MySQL 8.0.1、リビジョンバージョン 8.0.1.1.31 以降。
PolarDB for MySQL 8.0.2、リビジョンバージョン 8.0.2.2.12 以降。

クラスターのバージョンを確認するには、「エンジンバージョンを照会」をご参照ください。

パラメーター

SQL 詳細を有効にし、監査レコードの保持期間を設定するには、コンソールで次のパラメーターを設定してください。パラメーターの設定手順については、「クラスターおよびノードパラメーターの設定」をご参照ください。

パラメーター	レベル	説明
loose_awr_sqldetail_enabled	グローバル	SQL 詳細を有効または無効にします。有効な値： [OFF] (デフォルト)：SQL 詳細を無効にします。 [ON]：SQL 詳細を有効にします。
loose_awr_sqldetail_switch	グローバル	SQL 詳細で記録する操作タイプ。サブスイッチ： [ddl]：DDL 操作を記録します。有効な値： [ON] (デフォルト)：DDL 操作の詳細を記録します。 [OFF]：DDL 操作の詳細を記録しません。 [lock_db_table]：LOCK DB および LOCK TABLE 操作を記録します。有効な値： [ON] (デフォルト)：LOCK DB および LOCK TABLE 操作の詳細を記録します。 [OFF]：LOCK DB および LOCK TABLE 操作の詳細を記録しません。
loose_awr_sqldetail_retention	グローバル	監査レコードの保持期間。この期間を超えたレコードは自動的に削除されます。有効な値：0 ～ 18446744073709551615。デフォルト値：2592000。単位：秒。

テーブルスキーマ

PolarDB for MySQL は、監査レコードを格納するための組み込みシステムテーブル sys.hist_sqldetail を提供します。このテーブルはシステムの起動時に自動的に作成されるため、手動で作成する必要はありません。テーブルスキーマは次のとおりです。

CREATE TABLE `hist_sqldetail` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `State` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
  `Thread_id` bigint(20) unsigned DEFAULT NULL,
  `Host` varchar(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `User` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `Client_ip` varchar(60) COLLATE utf8mb4_bin DEFAULT NULL,
  `Db` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `Sql_text` mediumtext COLLATE utf8mb4_bin NOT NULL,
  `Server_command` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `Sql_command` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `Start_time` timestamp(6) NULL DEFAULT NULL,
  `Exec_time` bigint(20) DEFAULT NULL,
  `Wait_time` bigint(20) DEFAULT NULL,
  `Error_code` int(11) DEFAULT NULL,
  `Rows_sent` bigint(20) DEFAULT NULL,
  `Rows_examined` bigint(20) DEFAULT NULL,
  `Rows_affected` bigint(20) DEFAULT NULL,
  `Logical_read` bigint(20) DEFAULT NULL,
  `Phy_sync_read` bigint(20) DEFAULT NULL,
  `Phy_async_read` bigint(20) DEFAULT NULL,
  `Process_info` text COLLATE utf8mb4_bin,
  `Extra` text COLLATE utf8mb4_bin,
  `Create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `Update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`Id`),
  KEY `i_start_time` (`Start_time`),
  KEY `i_update_time` (`Update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

次の表で、システムテーブルの各列を説明します。

列	説明
Id	自動インクリメント ID。
State	レコードが書き込まれた時点の操作状態。
Thread_id	SQL ステートメントを実行したスレッドの ID。
Host	SQL ステートメントが実行されたホスト。
User	SQL ステートメントを実行したユーザー名。
Client_ip	SQL ステートメントが実行されたクライアント IP アドレス。
Db	SQL ステートメントが実行されたデータベース名。
Sql_text	実行された SQL ステートメント。
Server_command	SQL ステートメントの実行に使用されたサーバーコマンド。
Sql_command	SQL ステートメントのコマンドタイプ。
Start_time	SQL ステートメントの実行開始時刻。
Exec_time	実行時間。単位：マイクロ秒。
Wait_time	待機時間。単位：マイクロ秒。
Error_code	エラーコード。
Rows_sent	返された行数。
Rows_examined	スキャンされた行数。
Rows_affected	影響を受けた行数。
Logical_read	論理読み取り数。
Phy_sync_read	物理同期読み取り数。
Phy_async_read	物理非同期読み取り数。
Process_info	拡張フィールド。処理情報。
Extra	拡張フィールド。その他の情報。
Create_time	レコードが書き込まれた時刻。
Update_time	レコードが最後に更新された時刻。

例

コンソールで loose_awr_sqldetail_enabled パラメーターを [ON] に設定し、データベースで次のステートメントを実行します。

create table t(c1 int);
Query OK, 0 rows affected (0.02 sec)

create table t(c1 int);
ERROR 1050 (42S01): Table 't' already exists

alter table t add column c2 int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

lock tables t read;
Query OK, 0 rows affected (0.00 sec)

unlock tables;
Query OK, 0 rows affected (0.00 sec)

insert into t values(1, 2);
Query OK, 1 row affected (0.00 sec)

次のステートメントを実行して、sys.hist_sqldetail テーブルの監査レコードを表示します。

select * from sys.hist_sqldetail\G

出力：

*************************** 1. row ***************************
            Id: 1
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: create table t(c1 int)
Server_command: Query
   Sql_command: create_table
    Start_time: 2023-01-13 16:18:21.840435
     Exec_time: 17390
     Wait_time: 318
    Error_code: 0
     Rows_sent: 0
 Rows_examined: 0
 Rows_affected: 0
  Logical_read: 420
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:18:22.391407
   Update_time: 2023-01-13 16:18:22.391407
*************************** 2. row ***************************
            Id: 2
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: create table t(c1 int)
Server_command: Query
   Sql_command: create_table
    Start_time: 2023-01-13 16:18:22.416321
     Exec_time: 822
     Wait_time: 229
    Error_code: 1050
     Rows_sent: 0
 Rows_examined: 0
 Rows_affected: 0
  Logical_read: 55
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:18:23.393071
   Update_time: 2023-01-13 16:18:23.393071
*************************** 3. row ***************************
            Id: 3
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: alter table t add column c2 int
Server_command: Query
   Sql_command: alter_table
    Start_time: 2023-01-13 16:18:34.123947
     Exec_time: 16420
     Wait_time: 245
    Error_code: 0
     Rows_sent: 0
 Rows_examined: 0
 Rows_affected: 0
  Logical_read: 778
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:18:34.394067
   Update_time: 2023-01-13 16:18:34.394067
*************************** 4. row ***************************
            Id: 4
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: lock tables t read
Server_command: Query
   Sql_command: lock_tables
    Start_time: 2023-01-13 16:19:49.891559
     Exec_time: 145
     Wait_time: 129
    Error_code: 0
     Rows_sent: 0
 Rows_examined: 0
 Rows_affected: 0
  Logical_read: 0
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:19:50.399585
   Update_time: 2023-01-13 16:19:50.399585
*************************** 5. row ***************************
            Id: 5
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: unlock tables
Server_command: Query
   Sql_command: unlock_tables
    Start_time: 2023-01-13 16:19:56.924648
     Exec_time: 98
     Wait_time: 0
    Error_code: 0
     Rows_sent: 0
 Rows_examined: 0
 Rows_affected: 0
  Logical_read: 0
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:19:57.400294
   Update_time: 2023-01-13 16:19:57.400294

出力から、SQL 詳細は DDL、LOCK DB、LOCK TABLE ステートメントの監査情報のみを記録し、DML ステートメントの監査情報は記録しないことが分かります。また、SQL 詳細は SQL ステートメントの実行開始と同時にシステムテーブルにレコードを書き込み、ステートメントの完了後にレコード内の状態などのフィールドを自動的に更新します。