Zenn
🐡

TiDB Cloud Starter : Index Advisor を試してみる

に公開
TiDB
tech

Index Advisor とは

TiDBのv.8.5以降で導入された機能で、クエリのパフォーマンス向上のためのインデックスを自動で提案してくれます。RECOMMEND INDEXを使って、クエリ実行前の推奨インデックスを出力してくれます。

TiDB Cloud Starter ではサポートしていない機能として、仮想インデックスという機能があります。これはインデックスを物理的に作成しない状態でも、あると仮定してインデックスが存在している場合のSQL実行計画を出力してくれるものです。これにより効率的なインデックス作成後のシミュレーションが行えます。

EXPLAIN FORMAT='VERBOSE' SELECT /*+ HYPO_INDEX(t, idx_ab, 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_ab(a, b) | range:[1 1,1 1], keep order:false, stats:pseudo |
+------------------------+---------+---------+-----------+-----------------------------+-------------------------------------------------+

この機能はCloud Starterでは利用できないとドキュメントには記載がされていますが、やってみるとい動作しますので以下の、さっそくやってみる、パートで試していきます。

さっそくやってみる

1. テスト用テーブルとテストデータ書き込み

まずはテスト用テーブルを作成し5000件のデータを挿入します。
以下を実行しテーブルを作成します。

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

次に5000件のテストデータを作成し投入します。


SET SESSION cte_max_recursion_depth = 10000;

WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 5000
)
SELECT n, n * 10 AS b, n * 100 AS c
FROM seq;

INSERT INTO t (a, b, c)
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 5000
)
SELECT n, n * 10, n * 100
FROM seq;

2. SQLをもとにしたIndexの推奨

では以下のSQLを実行する際にインデックスが有益かどうかを判別します。

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


実行計画を見るとフルスキャンが発生しています。

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


次に以下のSQLでインデックスが有効かどうかを判断し、必要なら推奨してもらいます。

RECOMMEND INDEX RUN for "SELECT a, b FROM t WHERE a = 1 AND b = 10";

SELECT CONCAT(
  'CREATE INDEX ', index_name,
  ' ON `', schema_name, '`.`', table_name, '`(',
  index_columns, ');'
) AS create_index_statement,
reason_json.reason AS reason
FROM (
  SELECT *,
         JSON_EXTRACT(index_details, '$.Reason') AS reason
  FROM mysql.index_advisor_results
  WHERE schema_name = DATABASE() AND table_name = 't'
  ORDER BY created_at DESC
  LIMIT 1
) AS reason_json;


CREATE INDEX idx_a_b ON test.t(a,b);が推奨項目として表示されています。

3. 仮想インデックスによる検証

Index作成前に仮想インデックスを用いてインデックスの有効性を検証します。

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


インデックスが存在し利用されたと仮定した場合の推測コストが算定されています。インデックス未使用時のコストと比べると圧倒的に処理コストが減っていることがわかります。

4. インデックスの作成と再検証

では以下のSQLでインデックスを作成します。

CREATE INDEX idx_a_b ON `test`.`t`(a,b);

実行計画を再度取得するとインデックスが効いていることがわかります。

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

インデックス作成後も以下のSQLで明示的にインデックスを無視した実行計画を出力可能です。

EXPLAIN SELECT /*+ IGNORE_INDEX(t, idx_a_b) */ a,b FROM t WHERE a=1 AND b=1;

Discussion