HeatWave MySQL の Autopilot Indexing について

2025年4月17日

今回の記事は、HeatWave MySQL の独自機能である Autopilot Indexing について、概要と使用方法を紹介したいと思います。

HeatWave MySQL の公式ドキュメントページはこちら
MySQL :: HeatWave User Guide :: 2.8.1 Autopilot Indexing

Oracle 社のホワイトペーパーはこちら
MySQL :: HeatWave Autopilot Indexing

機能概要

Autopilot Indexing は、HeatWave MySQL のみで利用できる独自の機能です。
ホワイトペーパーの説明によると、HeatWave MySQL の機械学習を用いて動作する機能とのことです。
(そのため、オンプレ版の MySQL Community Edition, Enterprise Edition どちらもこの機能は実装されていません)

[引用：The Oracle MySQL Blog | Removing the index guesstimate with MySQL Autopilot Indexing]

具体的には InnoDB ストレージエンジンのテーブルに作成するセカンダリインデックスについて、以下の推奨提案を行うアドバイザです。

パフォーマンス向上のためのインデックス追加
使用されていない、または重複している不要なインデックス
インデックスを追加または削除する場合の
- DDL 実行時間の想定見積
- ストレージ増減

※公式リファレンスマニュアルには、Workload Optimization for OLTP という章で本機能がラインナップされています。

早速、使い方と出力内容について見ていきましょう。

使用方法

Autopilot Indexing は前述の通りバージョン 9.0.0 以上の HeatWave MySQL であればデフォルトで利用可能です。
ただし、HeatWave クラスタを有効にする必要があります。

ON/OFF にする、というものではなく、ストアドプロシージャとして機能が実装されています。

クエリ実行統計情報の蓄積

インデックスの推奨提案を導出するためには、クエリを実際に実行させて、MySQL 内のパフォーマンススキーマやインフォメーションスキーマの実行統計情報を蓄積させる必要があります。

正確には、

分析対象のスキーマに存在するテーブルに対して5つのクエリ実行履歴があり
かつ Autopilot Indexing で分析対象・分析可能なクエリであること
が条件です。

5つのクエリ実行履歴、というのは、1種類×5回実行でも、5種類×1回実行でもどちらも対象となります。

Autopilot Indexing アドバイザの実行

インターフェース実装については、

HeatWave MySQL on OCI では CLI (SQL)のみ
HeatWave MySQL on AWS では CLI と GUI が使える
という違いがあります。

CLI(SQL)

以下の権限を持つユーザで DB にログインしてインターフェースとなるプロシージャを実行します。

PROCESS
EXECUTE on sys.*
SELECT on performance_schema

 SQL > CALL sys.autopilot_index_advisor(JSON_OBJECT("output","normal","target_schema",JSON_ARRAY("sakila")));

1	SQL > CALL sys.autopilot_index_advisor(JSON_OBJECT("output","normal","target_schema",JSON_ARRAY("sakila")));

output: 3つのオプションのいずれか指定します。
- normal: デフォルト。サマリを、標準出力(stdout)と autopilot_index_advisor_report テーブルに出力します。
- autopilot_index_advisor_report テーブルは、sys.autopilot_index_advisor 実行時に作成されるメモリ一時テーブルです。
  sys.autopilot_index_advisor 実行の同一セッション内でアクセスする必要があります。
- silent: 実行結果を autopilot_index_advisor_report テーブルに出力します。
- help: コマンドラインヘルプを表示します。
target_schema: 分析対象のスキーマを1つ以上指定できます。何も指定しない場合、全てのスキーマが対象となります。
複数指定する場合は以下のようにします。

JSON_ARRAY("schema_1","schema_2","schema_3")

1

JSON_ARRAY("schema_1","schema_2","schema_3")

マニュアルには For the most accurate recommendations, specify one schema at a time. とあり、基本的には1スキーマ指定でよさそうです。
複数のスキーマにアクセスするクエリが実行される場合のみ、複数指定するとよいでしょう。

実行結果

以下は、 output: normal でアドバイザを実行した出力結果のサンプルです。
(大量の Index suggestions だったため表示は一部割愛しています)

ANALYZING DATA セクションには対象スキーマのデータサイズやテーブル・カラム数が表示されています。
- インフォメーションスキーマのテーブル・インデックス統計情報に基づくデータのため、事前に ANALYZE TABLE を実行しておくことでより正確な値を得られます。

+----------------------------+
| INITIALIZING INDEX ADVISOR |
+----------------------------+
| Version: 1.14              |
|                            |
| Output Mode: normal        |
| Target Schemas: 3          |
|                            |
+----------------------------+
5 rows in set (0.0056 sec)

+---------------------------------------------------------+
| ANALYZING DATA                                          |
+---------------------------------------------------------+
| Total 1260 table(s) for 3 schema(s)                     |
|                                                         |
| Total Data size:  97.47 GiB                             |
| Total Index size: 38.64 GiB                             |
|                                                         |
| SCHEMA                             TABLE         COLUMN |
| NAME                               COUNT          COUNT |
| ------                             -----         ------ |
| `schema_1`                           572           8357 |
| `schema_2`                           176           2091 |
| `schema_3`                           509           7877 |
|                                                         |
| TOTAL                               1257          18325 |
|                                                         |
| WARNING: Some tables could not be fully analyzed.       |
|                                                         |
+---------------------------------------------------------+
15 rows in set (0.0056 sec)

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| INDEX SUGGESTIONS                                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Total Index suggestions: 718                                                                                                                                             |
| Statements analyzed:     4554                                                                                                                                            |
|                                                                                                                                                                          |
| SUGGEST           TABLE                                    INDEXED                                  REASON             ESTIMATED         ESTIMATED        ESTIMATED      |
| ACTION            NAME                                     COLUMNS                                  SUGGESTED          PERF IMPACT       FOOTPRINT        CREATE TIME    |
| -------           -----                                    -------                                  ---------          -----------       ---------        -----------    |
| CREATE            `schema_3`.`order`                       `status`, `shipped`, `release`           Missing Index      HIGH              +    1.69 MiB        178 ms     |
  (...)
| DROP              `schema_3`.`log_backup`                  `log_id`, `remove_date`                  Unused Index       --                -  941.00 MiB         --        |
|   ...                                                                                                                                                                    |
|                                                                                                                                                                          |
| Expected performance benefit after applying all Index suggestions: 16.8%                                                                                                 |
| Expected storage footprint after applying all Index suggestions: -39.82 GiB                                                                                              |
|        39.94 GiB freed up by dropping indexes.                                                                                                                           |
|       130.22 MiB required for creating indexes.                                                                                                                          |
|       NOTE: Indexes will be stored efficiently at time of creation.                                                                                                      |
|             To accommodate efficient future inserts, size may double.                                                                                                    |
| Expected time for applying all Index creation suggestions:         14 s                                                                                                  |
|                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
31 rows in set (0.0056 sec)

+----------------------------------------------------------------------------------------------------------------+
| SCRIPT GENERATION                                                                                              |
+----------------------------------------------------------------------------------------------------------------+
| Script generated for applying suggestions for 1 drop indexes and 0 create indexes.                             |
|                                                                                                                |
| Retrieve script containing 1 generated DDL commands using the query below:                                     |
|   SELECT log->>"$.sql" AS "SQL Script" FROM sys.autopilot_index_advisor_report WHERE type = "sql" ORDER BY id; |
|                                                                                                                |
| Caution: Executing the generated script will alter the indexes in schema                                       |
|                                                                                                                |
+----------------------------------------------------------------------------------------------------------------+
7 rows in set (0.0056 sec)

+-------------------------------------------------------------------------------------------------------------------------+
| EXPLANATIONS                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------+
| Retrieve explanations for create index recommendations using the query below:                                           |
|   SELECT JSON_PRETTY(log) AS "Explanations" FROM sys.autopilot_index_advisor_report WHERE type = "explain" ORDER BY id; |
|                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------+

+----------------------------+

| INITIALIZING INDEX ADVISOR |

+----------------------------+

| Version: 1.14 |

| |

| Output Mode: normal |

| Target Schemas: 3 |

| |

+----------------------------+

5 rows in set (0.0056 sec)

+---------------------------------------------------------+

| ANALYZING DATA |

+---------------------------------------------------------+

| Total 1260 table(s) for 3 schema(s) |

| |

| Total Data size: 97.47 GiB |

| Total Index size: 38.64 GiB |

| |

| SCHEMA TABLE COLUMN |

| NAME COUNT COUNT |

| ------ ----- ------ |

| `schema_1` 572 8357 |

| `schema_2` 176 2091 |

| `schema_3` 509 7877 |

| |

| TOTAL 1257 18325 |

| |

| WARNING: Some tables could not be fully analyzed. |

| |

+---------------------------------------------------------+

15 rows in set (0.0056 sec)

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| INDEX SUGGESTIONS |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Total Index suggestions: 718 |

| Statements analyzed: 4554 |

| |

| SUGGEST TABLE INDEXED REASON ESTIMATED ESTIMATED ESTIMATED |

| ACTION NAME COLUMNS SUGGESTED PERF IMPACT FOOTPRINT CREATE TIME |

| ------- ----- ------- --------- ----------- --------- ----------- |

| CREATE `schema_3`.`order` `status`, `shipped`, `release` Missing Index HIGH + 1.69 MiB 178 ms |

(...)

| DROP `schema_3`.`log_backup` `log_id`, `remove_date` Unused Index -- - 941.00 MiB -- |

| ... |

| |

| Expected performance benefit after applying all Index suggestions: 16.8% |

| Expected storage footprint after applying all Index suggestions: -39.82 GiB |

| 39.94 GiB freed up by dropping indexes. |

| 130.22 MiB required for creating indexes. |

| NOTE: Indexes will be stored efficiently at time of creation. |

| To accommodate efficient future inserts, size may double. |

| Expected time for applying all Index creation suggestions: 14 s |

| |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

31 rows in set (0.0056 sec)

+----------------------------------------------------------------------------------------------------------------+

| SCRIPT GENERATION |

+----------------------------------------------------------------------------------------------------------------+

| Script generated for applying suggestions for 1 drop indexes and 0 create indexes. |

| |

| Retrieve script containing 1 generated DDL commands using the query below: |

| SELECT log->>"$.sql" AS "SQL Script" FROM sys.autopilot_index_advisor_report WHERE type = "sql" ORDER BY id; |

| |

| Caution: Executing the generated script will alter the indexes in schema |

| |

+----------------------------------------------------------------------------------------------------------------+

7 rows in set (0.0056 sec)

+-------------------------------------------------------------------------------------------------------------------------+

| EXPLANATIONS |

+-------------------------------------------------------------------------------------------------------------------------+

| Retrieve explanations for create index recommendations using the query below: |

| SELECT JSON_PRETTY(log) AS "Explanations" FROM sys.autopilot_index_advisor_report WHERE type = "explain" ORDER BY id; |

| |

+-------------------------------------------------------------------------------------------------------------------------+

INDEX SUGGESTIONS セクションは、アドバイザのメインとなる分析に基づくレコメンデーションが表示されます。
- 今回の例では、インデックス追加と削除が併せて提案されています。
- 表示量が14件を超えるとサマリ表示上からは省略されるようです。
- REASON SUGGESTED, ESTIMATED PERF IMPACT, ESTIMATED FOOTPRINT, ESTIMATED CREATE TIME の情報は非常に参考になるかと思います。
  複数インデックス追加を促されているけど、どれを優先したらよいのか、
  メンテナンス(DDL実行)にかかる時間の見積り、追加・削除時のディスク変化量の把握、
  といった運用上の判断要素に用いることができます。

output: normal で標準出力されるのはここまでです。

SCRIPT GENERATION セクションに記されているのは、具体的なアクションの実行 DDL ステートメントを sys.autopilot_index_advisor_report テーブルから抽出するクエリです。

 SQL > SELECT
  log ->> "$.sql" AS "SQL Script"
FROM
  sys.autopilot_index_advisor_report
WHERE
  type = "sql"
ORDER BY
  id;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| SQL Script                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| DROP INDEX `idx_2` ON `schema_3`.`log_backup`;                                                                                |
  (...)

SQL > SELECT

log ->> "$.sql" AS "SQL Script"

FROM

sys.autopilot_index_advisor_report

WHERE

type = "sql"

ORDER BY

id;

+-----------------------------------------------------------------------------------------------------------------------------------------+

| SQL Script |

+-----------------------------------------------------------------------------------------------------------------------------------------+

| DROP INDEX `idx_2` ON `schema_3`.`log_backup`; |

(...)

EXPLANATIONS セクションに記されているように、さらに「インデックス作成の推奨事項の説明」(SUGGEST ACTION が CREATE の場合)を表示するクエリを実行すると以下の結果を得られます。

MySQL :: HeatWave User Guide :: 2.8.1.5 Autopilot Index Advisor Report Table

 SQL > SELECT
    JSON_PRETTY(log) AS "Explanations"
FROM
    sys.autopilot_index_advisor_report
WHERE
    type = "explain"
ORDER BY
    id \G
*************************** 1. row ***************************
Explanations: {
  "SQL": "CREATE INDEX `autoidx_tab2063_col8_col25_col67` ON `schema_3`.`order` ( `status`, `shipped`, `release` );",
  "explanation": [
    {
      "reason": "Secondary Index",
      "query_text": "SELECT * FROM `order` WHERE release = ? AND `status` IN (...) AND `shipped` = CURDATE ( ) AND `number_01` != ?",
      "estimated_gain": "1000.0x"
    }
  ],
  "est_create_time": "177.00 ms"
}
  (...)

SQL > SELECT

JSON_PRETTY(log) AS "Explanations"

FROM

sys.autopilot_index_advisor_report

WHERE

type = "explain"

ORDER BY

id \G

*************************** 1. row ***************************

Explanations: {

"SQL": "CREATE INDEX `autoidx_tab2063_col8_col25_col67` ON `schema_3`.`order` ( `status`, `shipped`, `release` );",

"explanation": [

{

"reason": "Secondary Index",

"query_text": "SELECT * FROM `order` WHERE release = ? AND `status` IN (...) AND `shipped` = CURDATE ( ) AND `number_01` != ?",

"estimated_gain": "1000.0x"

}

"est_create_time": "177.00 ms"

}

(...)

on AWS の GUI

HeatWave on AWS 専用コンソールから GUI で簡単に操作することができます。

※参考として The Oracle MySQL Blog で公開されている画面をご覧ください。

[引用：The Oracle MySQL Blog | Removing the index guesstimate with MySQL Autopilot Indexing]

制限事項

今までの MySQL には無い画期的で便利な機能ですが、利用上の注意や制限もあるのでその点も紹介しておきます。

MySQL :: HeatWave User Guide :: 2.8.1.2 Running Autopilot Indexing

以下については Autopilot Indexing の評価分析対象外となります。

MySQL システムスキーマ
小さなテーブル (innodb_page_size が 16KB 未満、もしくは 1000行未満)
ストアドルーチン(プロシージャ,ファンクション)から発行されるステートメント
プリペアードステートメント
以下のデータ型のカラム

BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB JSON TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT

1
2
3

BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
JSON
TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT

また、Autopilot Indexing では以下についてはレコメンデーションを生成しません。

主キーの作成
外部キー制約で必要なインデックス
関数インデックス
パーティションテーブルのインデックス
LIKE 検索を性能向上させるインデックス

活用方法

Autopilot Indexing のユースケースについて考察してみます。(あくまで一例です)

定期的なチェックの実施

記事冒頭でもお伝えした通り、本機能はユーザが任意実行するアドバイザです。

運用上は、ジョブスケジューラなどで定期的に実行して提案内容をチェックし、適宜メンテナンス実施するのが効率的かと思います。

クエリチューニングの補助ツールとして

クエリチューニングは、やはり専門的なスキルが必要になってきます。
専任 DBA が常日頃から対応している環境では不要かもしれませんが、そうではないところも多いのではと思います。

セカンダリインデックスだけでも、どこをどう変更すると改善できるのかを導き出すにも労力が必要ですが、公式のツールで自動化できるのは大きなメリットと言えます。

また、ひと手間かかりますが、別環境(オンプレ)の MySQL のスキーマ・テーブル、データを HeatWave MySQL に複製しておいて、HeatWave MySQL 側でスロークエリログを流し込み、別環境の検査として用いるのも有益でしょう。
※あくまで参考として、バージョン差異の有無や HeatWave MySQL 自体の性能が起因して、必ずしも同一の結果とならない場合がある点はご注意ください。

開発時のインデックス検討に

システム開発の要件・状況によっては、ひとまずテーブル・カラム構成は定めるものの、一定期間アプリケーションを動作させてテストしつつ、スロークエリが検出したらインデックス追加を行っていくような進め方のときに、本機能が使えます。

スロークエリログ(経過秒が閾値)だけではなく、スキーマ全体のパフォーマンス向上に寄与するインデックスを提案してくれる(はず)でしょう。

また、テーブルを自動生成するがインデックスは付けないようなツールを使う場合(Apache Superset の CSV インポート機能など)も、しばらくクエリ実行が行われたのちに本機能を使って必要なセカンダリインデックスを割り出だせるのでとても便利です。

メンテナンス作業の時間見積もりに

INDEX SUGGESTIONS セクションの説明のところでも述べましたが、各 DDL がどれくらいの所要時間かを見積もってくれるのは便利です。

ただし、実際に試してみた結果では、アドバイザの提示する算出時間より若干長い処理時間が掛かりました。(1.5～1.7 倍程度)
あくまで目安(ベース)として、そこから余裕を持たせたメンテナンス時間を計画するとよいと思います。

TIPS

パフォーマンススキーマ統計情報をリセットするには

sys.ps_truncate_all_tables プロシージャを実行することですべてのパフォーマンススキーマ・サマリーテーブルが TRUNCATE されます。

 SQL > CALL sys.ps_truncate_all_tables(FALSE);

1	SQL > CALL sys.ps_truncate_all_tables(FALSE);

アドバイザ実行時の WARNING 内容を確認する方法

クエリ実行されているはずなのに、アドバイザの分析対象になっていないのか、結果が得られない… といったときに、 sys.autopilot_index_advisor_report テーブルの中身を除くと、その理由や対象が分かる場合があります。

 SQL > SELECT
  JSON_PRETTY(log) AS 'Warnings'
FROM
  sys.autopilot_index_advisor_report
WHERE
  type = 'warn'
ORDER BY
  id \G

SQL > SELECT

JSON_PRETTY(log) AS 'Warnings'

FROM

sys.autopilot_index_advisor_report

WHERE

type = 'warn'

ORDER BY

id \G

以下の出力を得ることができます。
Cause や Issue を確認し、仕様や前述の制限事項に抵触していないかをチェックします。
対象のクエリは digest で performance_schema.events_statements_summary_by_digest から特定できます。
※ query_id というのは Autopilot Indexing 実行時の内部管理ID なので上記テーブルと関連性はありません。

*************************** 1. row ***************************
Warnings: {
  "Cause": "No tables used for query",
  "Issue": "Query not used in Advisor",
  "digest": "c20fcea11911be36651b7ca7bd3712d4ed9ac1134cee9c6620039e1fb13b5eff",
  "query_id": 8
}

(...)

*************************** 7. row ***************************
Warnings: {
  "Cause": "Low statement count and time",
  "Issue": "Query not used in Advisor",
  "digest": "fabf4319e5a3d07071317913191333673faf249a0b68fdd1637bf4b60eb1bc2f",
  "query_id": 1
}

*************************** 1. row ***************************

Warnings: {

"Cause": "No tables used for query",

"Issue": "Query not used in Advisor",

"digest": "c20fcea11911be36651b7ca7bd3712d4ed9ac1134cee9c6620039e1fb13b5eff",

"query_id": 8

}

(...)

*************************** 7. row ***************************

Warnings: {

"Cause": "Low statement count and time",

"Issue": "Query not used in Advisor",

"digest": "fabf4319e5a3d07071317913191333673faf249a0b68fdd1637bf4b60eb1bc2f",

"query_id": 1

}

まとめ

MySQL(InnoDB) には従来なかった、HeatWave MySQL オリジナルの便利な機能が利用可能となりました。

多くのユーザのデータベース運用に適用できる(そして高いメリットを享けられる)注目の機能です。

以下の点が若干惜しいところではありますが、今後の改善に期待したいと思います。

MySQL バージョンが 9.0 以上で HeatWave クラスタ有効でないと使えない
プリペアードステートメントが対象外 (OLTPアプリケーションではよく使われるため)

HeatWave MySQL の利用を検討されている場合は、是非 Autopilot Indexing をお試しいただけたら幸いです。

よかったらシェアしてね！

URLをコピーしました！

HeatWave MySQL の Autopilot Indexing について

機能概要

使用方法

クエリ実行統計情報の蓄積

Autopilot Indexing アドバイザの実行

CLI(SQL)

実行結果

on AWS の GUI

制限事項

活用方法

定期的なチェックの実施

クエリチューニングの補助ツールとして

開発時のインデックス検討に

メンテナンス作業の時間見積もりに

TIPS

パフォーマンススキーマ統計情報をリセットするには

アドバイザ実行時の WARNING 内容を確認する方法

まとめ

スマートスタイルTECHブログについて

関連記事