SQL の詳細とは何か、およびその使用例 - PolarDB - Alibaba Cloud ドキュメントセンター

SQL の詳細は、PolarDB for MySQL のデータベースおよびテーブルに対する DDL 操作およびロック操作を監査します。各 SQL ステートメントの実行コンテキストを記録し、設定された保持期間を超えたレコードは自動的に削除されます。フル監査ログ機能（すべての SQL ステートメントを監査するが、高いオーバーヘッドを伴う）とは異なり、SQL の詳細はスキーマ変更およびロック関連のイベントに特化しており、軽量かつターゲットを絞った監査証跡を必要とする運用・保守（O&M）チームに適しています。

仕組み

DDL またはロックステートメントの実行が開始されると、SQL の詳細は sys.hist_sqldetail システムテーブルにレコードを書き込みます。ステートメントの実行が完了すると、PolarDB はそのレコードを最終状態およびパフォーマンスメトリクスで更新します。設定された保持期間を超えたレコードは自動的に削除されます。

SQL の詳細が記録する内容：

DDL 操作：テーブルの作成、カラムの追加、インデックスの変更などのスキーマ変更
ロック操作：LOCK TABLE および LOCK DB ステートメント

SQL の詳細が記録しない内容：

DML ステートメント（INSERT、UPDATE、DELETE、SELECT）は意図的に除外されています。SQL の詳細は、低オーバーヘッドでのスキーマ変更監査を目的として設計されており、汎用的なクエリログには対応していません。DML ステートメントを監査する場合は、フル監査ログ機能を有効化してください。

ストレージ

各監査レコードは 1 KB のストレージを消費します。たとえば、1 日あたり 1,024 件の DDL またはロック操作を 30 日間保持する場合、約 30 MB を消費します。

前提条件

開始する前に、クラスターが以下のいずれかのバージョン要件を満たしていることを確認してください。

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`	グローバル	OFF	ON、OFF	SQL の詳細の有効化／無効化
`loose_awr_sqldetail_switch`	グローバル	`ddl`: ON；`lock_db_table`: ON	`ddl`: ON/OFF；`lock_db_table`: ON/OFF	監査対象となる操作の種類を制御します。`ddl` は DDL 文をカバーし、`lock_db_table` は LOCK TABLE 文および LOCK DB 文をカバーします。
`loose_awr_sqldetail_retention`	グローバル	2592000	0～18446744073709551615	監査レコードの保持期間（秒単位）。この値を超えるレコードは自動的に削除されます

sys.hist_sqldetail テーブル

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`	ステートメントを実行したスレッドの ID
`Host`	実行ユーザーに関連付けられたホスト
`User`	ステートメントを実行するために使用されたユーザー名
`Client_ip`	クライアントの IP アドレス
`Db`	ステートメントが実行されたデータベース
`Sql_text`	SQL ステートメントの全文
`Server_command`	サーバーのコマンドタイプ（例：`Query`）
`Sql_command`	ステートメントの種類（例：`create_table`、`alter_table`、`lock_tables`）
`Start_time`	実行開始時刻（マイクロ秒精度）
`Exec_time`	実行時間（マイクロ秒単位）。異常に遅い DDL 操作を特定するために使用します
`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`	レコードが最後に更新されたタイムスタンプ

使用例

この例では、SQL の詳細が DDL およびロック操作を記録する一方で、DML 操作は無視することを示します。

ステップ 1： コンソールで 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)

ステップ 2： 監査レコードを照会します。

select * from sys.hist_sqldetail\G

期待される出力：

*************************** 1. 行 ***************************
            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. 行 ***************************
            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. 行 ***************************
            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. 行 ***************************
            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. 行 ***************************
            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

出力には、各 DDL およびロックステートメントに対応する 5 件のレコードが含まれています。insert into t values(1, 2) ステートメントは記録されていません。これは、SQL の詳細が DML 操作を記録しないためです。

2 行目には、重複した CREATE TABLE 実行時の Error_code: 1050 が表示されています。SQL の詳細は、成功および失敗の両方のステートメントを記録するため、O&M 担当者はスキーマ変更の試行履歴（失敗も含む）を完全に把握できます。