インデックスはデータベースに選ばせよう：TiDBによるスマートなチューニング

※このブログは2025年3月27日に公開された英語ブログ ｢Let Your Database Recommend the Indexes: Smarter Tuning in TiDB｣ の拙訳です。

インデックスチューニングは、データベースクエリを高速化する最も効果的な手法の一つですが、同時に最も時間がかかり、複雑な作業でもあります。以前のブログでTiDB Index Advisor をコマンドラインツールとして初めて紹介した際、私たちの目標は、実際のワークロードにおけるインデックス選択をよりシンプルにすることでした。

そして今回、TiDB 8.5 により、このアイデアをさらに一歩進めました。
新たに導入された RECOMMEND INDEX SQLコマンドによって、インテリジェントかつコストベースのインデックス提案を、ワークフローに直接組み込むことができるようになりました。スクリプトも試行錯誤も追加ツールも不要です。

これは、単一のクエリを細かく調整する場合でも、運用中のワークロード全体のパフォーマンスを向上させる場合でも、強力なデータベース最適化を誰もが利用できるものにするというTiDBのミッションの一環です。

なぜインデックスチューニングは今もなお難しいのか

インデックスはクエリのパフォーマンスを大きく左右します。しかし、適切なインデックスを選ぶことは科学というより職人技に近く、しかも往々にして時間がかかり、ストレスの多い作業です。

開発者やDBA (データベース管理者) は、おなじみの課題に直面しています：

• バランスの取り方が難しい。 インデックスは特定のクエリ性能を向上させる一方で、更新時のオーバーヘッドも増やします。
• 高度な知識が求められる。どのカラムにインデックスを張るべきかを判断するには、クエリプランやオプティマイザの挙動を深く理解している必要があります。
• スケーラビリティに欠ける。1つのクエリをチューニングするだけでも大変なのに、それが何百・何千とあれば、もはや専任作業です。
• 時間がかかる。試行錯誤とベンチマークのサイクルは、数日から数週間に及ぶこともあります。

TiDB Index Advisorは、これらの課題に真正面から取り組みます。OLTPでもOLAPでもハイブリッドでも、実際のワークロードを分析し、定量的な効果をもたらすインデックスを自動で提案します。そしてTiDB 8.5では、そのすぐれた機能がSQLコマンドひとつで使えるようになりました。

RECOMMEND INDEX コマンドのご紹介

TiDB 8.5 では、インデックスの推奨機能がSQLに直接組み込まれました。新しく追加された RECOMMEND INDEX コマンドを使えば、個別のクエリやワークロード全体を、たった1行で分析できます。外部ツールやスクリプトは一切不要です。

基本的な構文はシンプルです：

RECOMMEND INDEX [RUN | FOR <SQL> | <OPTIONS> ];

このコマンドには主に2つの利用方法があります：

1. 単一のクエリに対してインデックスを推奨する

FORオプションを使用することで、特定のSQLステートメントに対するインデックスの提案が表示されます。

RECOMMEND INDEX FOR "SELECT a, b FROM t WHERE a = 1 AND b = 1";

2. ワークロード全体に対してインデックスを推奨する

RUNオプションを使用して、ステートメントサマリーテーブルから上位クエリを分析します。

RECOMMEND INDEX RUN;

どちらのオプションとも、推奨されるインデックス、対象のテーブル、改善スコア、そして適用すべき正確なCREATE INDEX文を含むまとまった結果を返します。これにより、どのインデックスが最もパフォーマンスに最大の影響を与えるかを繰り返し確認可能になります。

例：単一のクエリに対するインデックスの推奨

例えば、2,000行のシンプルなテーブルを扱っているとします。インデックスが存在しなければ、フルスキャンによる性能影響が発生しうる規模です。

CREATE TABLE t(a INT, b INT, c INT);

簡略化のため、ここでは insert文は省略しますが、テーブルには代表的なサンプルデータが挿入されているものとします。
これから、このクエリのパフォーマンスを改善したいと考えています：

SELECT a, b FROM t WHERE a = 1 AND b = 1;

次に、RECOMMEND INDEXコマンドを実行します：

RECOMMEND INDEX FOR "SELECT a, b FROM t WHERE a = 1 AND b = 1";

出力結果の例を以下に示します：

+----------+-------+------------+---------------+------------+---------------------------------------------------------------+----------------------------------------------------------+-------------------------------+
| database | table | index_name | index_columns | index_size | reason                                                        | top_impacted_query                                       | create_index_statement         |
+----------+-------+------------+---------------+------------+---------------------------------------------------------------+----------------------------------------------------------+-------------------------------+
| test     | t     | idx_a_b    | a, b          | 19872      | Columns a and b appear in equality filters                    | SELECT a, b FROM t WHERE a = 1 AND b = 1 (improvement: ~99.99%) | CREATE INDEX idx_a_b ON t(a, b); |
+----------+-------+------------+---------------+------------+---------------------------------------------------------------+----------------------------------------------------------+-------------------------------+

a や b に対して個別の単一カラムインデックスを提案するのではなく、アドバイザーは (a, b) の複合インデックスが最も高いパフォーマンスを発揮すると判断しました。内部的には、複数の仮想的なインデックスパターンを評価し、最もコストが低いものを選択しています。

以下は、実行計画の違いです：

インデックスなし (フルテーブルスキャン) ：

EXPLAIN FORMAT='verbose' SELECT a, b FROM t WHERE a = 1 AND b = 1;

+---------------------+---------+------------+-----------+---------------+----------------------------------+
| id                  | estRows | estCost    | task      | access object | operator info                    |
+---------------------+---------+------------+-----------+---------------+----------------------------------+
| TableReader_7       | 0.01    | 196066.71  | root      |               | data:Selection_6                 |
| └─Selection_6       | 0.01    | 2941000.00 | cop[tikv] |               | eq(test.t.a, 1), eq(test.t.b, 1) |
|   └─TableFullScan_5 | 5000.00 | 2442000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo   |
+---------------------+---------+------------+-----------+---------------+----------------------------------+

仮想インデックス (idx_a_b) を使用した場合：

EXPLAIN FORMAT='verbose'
SELECT /*+ HYPO_INDEX(t, idx_a_b, a, b) */ a, b FROM t WHERE a = 1 AND b = 1;

+---------------------+---------+---------+-----------+-----------------------------+-------------------------------------------------+
| id                  | estRows | estCost | task      | access object               | operator info                                   |
+---------------------+---------+---------+-----------+-----------------------------+-------------------------------------------------+
| IndexReader_6       | 0.05    | 1.10    | root      |                             | index:IndexRangeScan_5                          |
| └─IndexRangeScan_5  | 0.05    | 10.18   | cop[tikv] | table:t, index:idx_a_b(a,b) | range:[1 1,1 1], keep order:false, stats:pseudo |
+---------------------+---------+---------+-----------+-----------------------------+-------------------------------------------------+

注：インデックスアドバイザーの裏側で使用されているだけでなく、仮想インデックス (Hypothetical Indexes) は単体の機能としても利用可能です。上記で示したようにHYPO_INDEX ヒントを使うことで、実際にインデックスを作成する前に、その効果をシミュレーションすることができます。

どのような利点があるのでしょうか?

インデックスがない場合、TiDBはテーブル全体をスキャンし、すべての行を評価したうえで、a と b の条件でフィルタリングしなければなりません。これは、データ量が増えるほどコストが高くなります。

一方、推奨されたインデックスを使用すれば、TiDBは高速なインデックスレンジスキャンによって、該当する行に直接アクセスできます。つまり、桁違いに低いコスト、少ないCPUやI/O負荷、そして高速な応答時間が実現されます——すべてアドバイザーによって自動的に提案されるのです。

例：ワークロード全体に対するインデックスの推奨

実際の環境では、パフォーマンスの問題は単一のクエリから発生することは稀です。TiDBのインデックスアドバイザーは、ワークロード全体を評価し、全体的に最も効果のあるインデックスを提案します。

例えば、約5,000行のテーブルが2つ (t1 と t2) あり、以下のようなクエリを実行しているとします。

SELECT a, b FROM t1 WHERE a = 1 AND b <= 5;
SELECT d FROM t1 ORDER BY d LIMIT 10;
SELECT * FROM t1 JOIN t2 ON t1.d = t2.d WHERE t1.a = 1;

この簡単なコマンドを実行すると：

RECOMMEND INDEX RUN;

以下のような出力が得られます：

+----------+-------+------------+---------------+------------+-------------------------------------------------------------+--------------------------------------------------------------+----------------------------------+
| database | table | index_name | index_columns | index_size | reason                                                      | top_impacted_query                                           | create_index_statement           |
+----------+-------+------------+---------------+------------+-------------------------------------------------------------+--------------------------------------------------------------+----------------------------------+
| test     | t1    | idx_a_b    | a, b          | 19872      | Columns a and b appear in equality or range filters         | SELECT a, b FROM t1 WHERE a = 1 AND b <= 5                   | CREATE INDEX idx_a_b ON t1(a,b); |
| test     | t1    | idx_d      | d             | 9936       | Column d used in ORDER BY and join predicate                | SELECT d FROM t1 ORDER BY d LIMIT 10                         | CREATE INDEX idx_d ON t1(d);     |
| test     | t2    | idx_d      | d             | 9936       | Column d used in join condition with t1.d                   | SELECT * FROM t1 JOIN t2 ON t1.d = t2.d WHERE t1.a = 1       | CREATE INDEX idx_d ON t2(d);     |
+----------+-------+------------+---------------+------------+-------------------------------------------------------------+--------------------------------------------------------------+----------------------------------+

裏側では何が起きているのでしょうか？

• TiDBはinformation_schema.statements_summaryから実際のSQLステートメントを取得します。
• 実行頻度順にランキングを付けます—なぜなら、頻繁に実行されるクエリが最も重要だからです。
• デフォルトでは、上位1,000件のクエリを分析します (max_num_queryオプションで設定可能)。
• コストベースの分析を行い、単に明白なものだけでなく、高い効果が見込めるインデックスを提案します。

このワークロードに基づくアプローチにより、推奨されるインデックスは推測や行き当たりばったりなものではなく、実際のクエリの挙動に基づいています。ワークロードが OLTP、OLAP、またはハイブリッドであっても、アドバイザーは最小限の労力で全体のパフォーマンス向上を支援します。

推奨結果の確認と保存

RECOMMEND INDEXを実行するたびに、TiDB は結果をmysql.index_advisor_resultsというシステムテーブルに記録します。これにより、後で推奨結果を確認したり、内容をチェックしたり、作業を自動で行ったりできます。

次のクエリを実行してください：

SELECT * FROM mysql.index_advisor_results;

出力の例：

+----+---------------------+---------------------+-------------+------------+------------+---------------+---------------------------------------------------------+------------------------------------------------------------+-----------------------------+-------+
| id | created_at          | updated_at          | schema_name | table_name | index_name | index_columns | index_details                                           | top_impacted_queries                                        | workload_impact             | extra |
+----+---------------------+---------------------+-------------+------------+------------+---------------+---------------------------------------------------------+------------------------------------------------------------+-----------------------------+-------+
|  1 | 2024-12-10 11:44:45 | 2024-12-10 11:44:45 | test        | t1         | idx_a_b    | a, b          | Columns a, b used in WHERE clause                       | SELECT a, b FROM t1 WHERE a = 1 AND b <= 5                 | ~39% overall improvement     | NULL  |
|  2 | 2024-12-10 11:44:45 | 2024-12-10 11:44:45 | test        | t1         | idx_d      | d             | Column d used in ORDER BY and join                      | SELECT d FROM t1 ORDER BY d LIMIT 10                       | ~22% overall improvement     | NULL  |
|  3 | 2024-12-10 11:44:45 | 2024-12-10 11:44:45 | test        | t2         | idx_d      | d             | Column d used in join with t1                           | SELECT * FROM t1 JOIN t2 ON t1.d = t2.d WHERE t1.a = 1     | ~36% overall improvement     | NULL  |
+----+---------------------+---------------------+-------------+------------+------------+---------------+---------------------------------------------------------+------------------------------------------------------------+-----------------------------+-------+

このシステムテーブルでは、以下の情報を確認できます：

• 推奨結果の保存されたな履歴
• 個別クエリおよび全体のワークロードに対する推定効果
• すぐに適用可能なCREATE INDEX文

特に、ステージング環境やCIパイプライン、または本番導入前に推奨内容を管理したいチームに役立ちます。

アドバイザーの詳細設定 (SETとSHOW)

TiDB では、RECOMMEND INDEXの動作を細かく制御できます。
ワークロードや環境に合わせて、制限値、タイムアウト、インデックス制約などを調整可能です。

現在の設定を確認するには：

RECOMMEND INDEX SHOW;

出力の例：

+-------------------+--------+----------------------------------------------------------+
| option            | value  | description                                              |
+-------------------+--------+----------------------------------------------------------+
| max_num_index     | 5      | Maximum number of indexes to recommend                   |
| max_index_columns | 3      | Maximum number of columns in a multi-column index        |
| max_num_query     | 1000   | Maximum number of queries to analyze from the workload   |
| timeout           | 30s    | Max time to run the advisor                              |
+-------------------+--------+----------------------------------------------------------+

設定を更新するには、SET構文を使用してください：

RECOMMEND INDEX SET timeout = '20s';

これらのオプションを使うと便利です：

• 運用上の制約に合わせて提案数を制限する
• 過度に複雑なインデックスを防ぐ
• より最近の、または過去のクエリ履歴に注目する
• 本番環境でアドバイザーの実行時間が長くなりすぎないように制御する

これらのパラメーターを微調整することで、特定の環境におけるパフォーマンス、安全性、使いやすさのバランスをとるようにアドバイザの動作を形成することができます。

TiDBと他のインデックスチューニングアドバイザーとの比較

インデックスの推奨は新しいものではありませんが、｢どのように行うか｣ で大きな違いがあります。

ほとんどの商用データベースにはインデックスチューニングツールが備わっています。中にはルールベースのものもあれば、外部スクリプトやGUIが必要なもの、あるいは1クエリずつしか分析できないものもあります。また、そのほとんどがプロプライエタリライセンスに縛られています。

TiDBは異なるアプローチを取っています：

• オープンソースで完全に統合されています
• 単なるルールベースではなく、コストベースで判断します
• ワークロード認識型 ー 1度に1クエリずつではなく、最も頻繁で最も高価なクエリ全体を分析します
• SQLネイティブなので、自動化や開発ワークフローに自然にフィットします

TiDBと他の一般的なデータベースとの比較は以下の通りです：

	Open Source (オープンソース)	SQLベースのインデックス提案	複数クエリの同時分析	自動インデックス提案	インデックス選定方法
TiDB	はい	はい	はい	はい	シミュレーションを実行して最もコストの低いインデックスを選定 (コストベース戦略)
PostgreSQL (hypopg)	はい	一部対応 (拡張機能が必要)	いいえ	いいえ	ユーザーがシミュレーション結果を手動で比較 (仮想インデックス)
MySQL	はい	いいえ	いいえ	限定的	固定されたクエリパターンを使用 (ルールベース戦略)
SQL Server	いいえ	いいえ (GUIのみ)	はい	はい	各インデックス候補の実行計画コストを推定 (コストベース戦略)
Oracle	いいえ	いいえ (GUIのみ)	はい	はい	オプティマイザのコストモデルを使用 (コストベース戦略)
CockroachDB	いいえ	いいえ	いいえ	いいえ	ユーザーが手動で判断・テスト (手動インデックスのみ)

用語集：これらのインデックスチューニング戦略の仕組み

• コストベース (Cost-based)：複数のクエリプランをシミュレーションし、最も効率的なインデックスを選択する方法
• 仮説ベース (Hypothetical)：ユーザーが仮想的なインデックスを試すことはできるが、最終的な判断はユーザーに委ねられる方法
• ルールベース (Rule-based)：単純なヒューリスティック (例：WHERE句に含まれるカラム) に基づいてインデックスを提案する方法
• 手動 (Manual)：組み込みツールが存在せず、ユーザー自身がインデックスの定義・テスト・調整をすべて行う方法
• ワークロード認識型 (Workload-aware)：多数のクエリを分析し、その中でも特に頻繁に使われるものやリソース消費が大きいクエリを優先して処理し、全体のパフォーマンス向上を図る方法

インデックスのチューニングベンチマークと実際の結果

TiDBのインデックスアドバイザーは、理論上優れているだけではありません。人工的なベンチマークから実際の顧客環境での導入事例に至るまで、さまざまなワークロードにおいて、実証可能なパフォーマンス向上を実現しています。

ベンチマークのハイライト

• TPC-H (OLAPベンチマーク)：TiDBのアドバイザーは、インデックスの効果が限定的であることが多い分析ワークロードにおいても、選択フィルタを最適化し、スキャンのオーバーヘッドを削減することで、16%のパフォーマンス向上を実現しました。
• Join Order Benchmark (JOB)：複雑な結合、ネストされたサブクエリ、さまざまなデータ分布が混在するワークロードです。アドバイザは、OLTPとOLAPの両方のパターンで強みを発揮し、クエリ全体のパフォーマンスを46%向上させました。
• Web3bench (HTAPベンチマーク)： TiDBが開発し、TPC-TCカンファレンスで発表されたトランザクションと分析のハイブリッドベンチマークにおいて、アドバイザは75%の性能向上を達成し、リアルタイムデータプラットフォームにおける価値を証明しました。

顧客シミュレーション

• グローバルホームシェアリングマーケットプレイス：8つの実運用クエリを対象とした社内シミュレーションにおいて、Index Advisorはパフォーマンスを95%改善し、負荷の高い結合およびフィルタ操作の待ち時間を劇的に短縮しました。
• Web3系のカスタマー：インデックス・アドバイザーを導入する前は、最も重要なクエリの50%でメモリ不足 (OOM) エラーが発生していました。手作業によるインデックス作成には数週間を要し、冗長性 (20のインデックス) が生じていました。アドバイザーは、わずか10個のインデックスで同じ問題を解決し、成功率は5%向上しました。

これらの結果から、TiDBのインデックスアドバイザーは精度が高いだけでなく、実用的でスケーラブル、そして多くの場合、手動チューニングよりも優れていることが示されています。

RECOMMEND INDEXを始めましょう

TiDBのインデックスアドバイザーは、わずか数ステップで利用を開始できます。セットアップ不要、プラグイン不要、コードの変更も不要です。

特定のクエリをチューニングする場合：

RECOMMEND INDEX FOR "SELECT * FROM my_table WHERE user_id = 42";

最も頻繁に使用されるクエリを分析したい場合：

RECOMMEND INDEX RUN;

デフォルトでは、TiDBはstatements_summary内の上位1,000件のクエリを対象に分析を行い、ワークロード全体に対して最も効果的なインデックスを推奨します。 timeout、max_num_query、max_index_columnsなどのオプションでこの動作を制御できます。

RECOMMEND INDEX SET timeout = '20s';
RECOMMEND INDEX SHOW;

すべての結果はmysql.index_advisor_resultsに保存されるため、後から確認したり、インデックス作成をスクリプト化することが可能です。

数時間から数秒へ：より良いインデックスチューニングへの道

TiDBのインデックスアドバイザーは、インテリジェントかつコストベースのインデックス最適化を、ワークフローに直接組み込むことができます。単一のクエリを最適化する場合でも、数千件のクエリ全体のパフォーマンスを改善する場合でも、RECOMMEND INDEXを使えばより早く、より良い結果が得られます。

ネイティブなSQL統合、完全なワークロード認識、そして実運用環境で実証された結果により、TiDBにおけるパフォーマンスを引き出す最もシンプルな方法のひとつです。試行錯誤も、手動でのチューニングループも不要です。

さらに詳しく知りたい方は、ドキュメントをご覧いただくか、テスト環境でぜひお試しください。