## モジュール 4: インデックス使用統計の監視 適切なインデックス設計は、データベースパフォーマンスを維持するための重要な前提条件です。TiDB v8.0.0は、`information_schema.tidb_index_usage`テーブルと`sys.schema_unused_indexes`ビューを導入し、インデックスの使用統計を提供します。この機能は、データベース内のインデックスの効率を評価し、インデックス設計を最適化するのに役立ちます。 このモジュールで作業するための前提知識として、[TiDB SQL Tuning](https://www.pingcap.com/education/course-tidb-sql-tuning/)トレーニングからの以下の補足情報を示します。便利のためにここに表示します。 ![70%](https://lab-static.pingcap.com/quick-demo/SelCarCost.png) ### 手順 1. tidb_index_usage をクエリしてインデックスの効率を確認する + `tidb_index_usage`テーブルをクエリして`book_title_uk`インデックスの使用ベースラインを取得します。前のモジュールでの`EXPLAIN`コマンドは実際にクエリを実行しなかったため、統計はすべてゼロです。 ```sql SELECT table_schema, table_name, index_name, query_total, rows_access_total, last_access_time, percentage_access_0, percentage_access_0_1, percentage_access_1_10, percentage_access_10_20, percentage_access_20_50, percentage_access_50_100, percentage_access_100 FROM information_schema.tidb_index_usage WHERE index_name='book_title_uk'\G ``` ```output *************************** 1. row *************************** table_schema: bookshop table_name: books index_name: book_title_uk query_total: 0 rows_access_total: 0 last_access_time: NULL percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 0 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 1 row in set (0.01 sec) ``` + CBO統計を収集します。 ```sql ANALYZE TABLE bookshop.books; ``` ```output Query OK, 0 rows affected, 8 warnings (0.69 sec) ``` + `book_title_uk`インデックスを使用する可能性のあるクエリを実行します。 ```sql SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology' AND title = 'Play That Funky Music'; ``` ```output +-------+--------+ | stock | price | +-------+--------+ | 147 | 185.76 | +-------+--------+ 1 row in set (0.00 sec) ``` + 使用統計を再度確認します。`query_total`、`rows_access_total`、および`percentage_access_1_10`が増加することに注意してください。 ```sql SELECT table_schema, table_name, index_name, query_total, rows_access_total, last_access_time, percentage_access_0, percentage_access_0_1, percentage_access_1_10, percentage_access_10_20, percentage_access_20_50, percentage_access_50_100, percentage_access_100 FROM information_schema.tidb_index_usage WHERE index_name='book_title_uk'\G ``` ```output *************************** 1. row *************************** table_schema: bookshop table_name: books index_name: book_title_uk query_total: 1 rows_access_total: 1 last_access_time: 2025-03-09 05:33:47 percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 1 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 1 row in set (0.01 sec) ``` + `book_title_uk`インデックスを使用しない可能性のある別のクエリを実行します。この場合、`type`が述語であり、`title`は省略されています。`book_title_uk`インデックスの先頭列は`title`であるため、CBOは`book_title_uk`インデックスアクセスパスを選ばないでしょう。 ```sql SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology'; ``` ```output +------------+--------+ | stock | price | +------------+--------+ | 2147483647 | 101.02 | | 30 | 195.32 | | 141 | 13.20 | | 138 | 94.34 | | 38 | 138.97 | | 66 | 13.00 | | 147 | 185.76 | | 6 | 109.95 | | 20 | 75.24 | | 990 | 197.15 | | 4981 | 133.59 | | 9888998 | 106.51 | | 288 | 197.48 | | 30000 | 56.56 | | 5555555 | 104.12 | | 6 | 187.77 | +------------+--------+ 16 rows in set (0.02 sec) ``` + ただし、前のクエリの結果セットに基づいて、クエリがインデックスアクセスパスを介して実行されたのか、フルテーブルスキャンアクセスパスを介して実行されたのかはわかりません。`tidb_index_usage`テーブルから答えを得ましょう。カウンタが増加していないことがわかります。これは、前のクエリでインデックスが使用されなかったことを意味します。 ```sql SELECT table_schema, table_name, index_name, query_total, rows_access_total, last_access_time, percentage_access_0, percentage_access_0_1, percentage_access_1_10, percentage_access_10_20, percentage_access_20_50, percentage_access_50_100, percentage_access_100 FROM information_schema.tidb_index_usage WHERE index_name='book_title_uk'\G ``` ```output *************************** 1. row *************************** table_schema: bookshop table_name: books index_name: book_title_uk query_total: 1 rows_access_total: 1 last_access_time: 2025-03-09 05:33:47 percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 1 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 1 row in set (0.01 sec) ``` + `type = 'Science & Technology'`という述語ではインデックスを使用できないことを確認します。 ```sql EXPLAIN ANALYZE SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology'; ``` ```output +---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+ | TableReader_11 | 16.00 | 16 | root | partition:all | time:1.5ms, loops:2, RU:4.149196, cop_task: {num: 8, max: 1.33ms, min: 955.8µs, avg: 1.17ms, p95: 1.33ms, max_proc_keys: 13, p95_proc_keys: 13, tot_proc: 662.4µs, tot_wait: 4.26ms, copr_cache_hit_ratio: 0.00, build_task_duration: 23.7µs, max_distsql_concurrency: 8}, rpc_info:{Cop:{num_rpc:8, total_time:9.31ms}} | data:Projection_5 | 994 Bytes | N/A | | └─Projection_5 | 16.00 | 16 | cop[tikv] | | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:8, tasks:8}, scan_detail: {total_process_keys: 100, total_process_keys_size: 8414, total_keys: 108, get_snapshot_time: 4.02ms, rocksdb: {key_skipped_count: 100, block: {}}}, time_detail: {total_process_time: 662.4µs, total_wait_time: 4.26ms, tikv_wall_time: 6.22ms} | bookshop.books.stock, bookshop.books.price | N/A | N/A | | └─Selection_10 | 16.00 | 16 | cop[tikv] | | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:8, tasks:8} | eq(bookshop.books.type, "Science & Technology") | N/A | N/A | | └─TableFullScan_9 | 100.00 | 100 | cop[tikv] | table:books | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:8, tasks:8} | keep order:false | N/A | N/A | +---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+ 4 rows in set (0.00 sec) ``` + この時点で、CBOが`TableFullScan`を選択したもう一つの理由は、データセットが小さすぎる（テーブルには100行しかない）ためだと考えるかもしれません。その通りです。データセットが十分に大きくない場合、`IndexLookUp`と`TableFullScan`の間で選択する際に明確なパフォーマンスの違いはありません。しかし、実行計画はCBOの決定を反映しています。CBOの決定をテストするために別の実験を行いましょう。 + 今回は列の組み合わせ`(type, title)`に新しいグローバル一意インデックスを作成します。`type`が先頭の列です。 ```sql CREATE UNIQUE INDEX book_type_uk ON bookshop.books (type, title) GLOBAL; ANALYZE TABLE bookshop.books; ``` ```output Query OK, 0 rows affected (6.02 sec) Query OK, 0 rows affected, 8 warnings (0.82 sec) ``` + 再度`type = 'Science & Technology'`述語クエリを実行します。今回は`book_type_uk`と`book_title_uk`の2つのインデックスがありますが、CBOはどのアクセス方法を選択するでしょうか？`book_title_uk`は可能性が低く、選択は`book_type_uk`と`TableFullScan`の間で行われます。すでに知っているように、結果セットの行は全体の16％を占めています。クエリの選択性は、CBOがインデックスアクセスパスを好むのに最適ではないかもしれません。 ```sql SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology'; ``` ```output +------------+--------+ | stock | price | +------------+--------+ | 30000 | 56.56 | | 5555555 | 104.12 | | 6 | 187.77 | | 2147483647 | 101.02 | | 30 | 195.32 | | 141 | 13.20 | | 138 | 94.34 | | 38 | 138.97 | | 990 | 197.15 | | 4981 | 133.59 | | 9888998 | 106.51 | | 66 | 13.00 | | 147 | 185.76 | | 6 | 109.95 | | 20 | 75.24 | | 288 | 197.48 | +------------+--------+ 16 rows in set (0.00 sec) ``` + 再度`tidb_index_usage`テーブルを参照します。`book_type_uk`のカウンタが増加していないことがわかります。特に`percentage_access_10_20`カウンタが0であることがわかります。これは、前のクエリでインデックスが使用されなかったことを意味します。しかし、`books`テーブルに多くの行があり、選択性が16％のままであれば、CBOは同じクエリに対してインデックスアクセスパスを選択するかもしれません。 ```sql SELECT table_schema, table_name, index_name, query_total, rows_access_total, last_access_time, percentage_access_0, percentage_access_0_1, percentage_access_1_10, percentage_access_10_20, percentage_access_20_50, percentage_access_50_100, percentage_access_100 FROM information_schema.tidb_index_usage WHERE index_name in ('book_title_uk','book_type_uk')\G ``` ```output *************************** 1. row *************************** table_schema: bookshop table_name: books index_name: book_title_uk query_total: 1 rows_access_total: 1 last_access_time: 2025-03-09 05:33:47 percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 1 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 *************************** 2. row *************************** table_schema: bookshop table_name: books index_name: book_type_uk query_total: 0 rows_access_total: 0 last_access_time: NULL percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 0 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 2 rows in set (0.01 sec) ``` + 一般的なルールとして、TiDBでは選択性が10％未満であることがインデックスアクセスパスに適した条件と見なされますが、これはすべてのケースに対する絶対的なルールではありません。 ### 手順 2. インデックス使用統計に基づいて未使用のインデックスを削除する + 無駄なインデックスは、DML操作（INSERT、DELETE、UPDATEなど）および一部のDDL操作を遅くするだけです。それを削除することが正しい行動です。 + `tidb_index_usage`テーブルはどのインデックスが使用されているか、されていないかを教えてくれるので、TiDBはDBAやDevOpsエンジニアに無駄なインデックスを教えるための`sys.schema_unused_indexes`という興味深いインメモリテーブルを提供します。クラスタの再起動時にクリアされます。`sys.schema_unused_indexes`に依存する前に、少なくとも1つのTiDBノードが十分な時間稼働していることを確認する必要があります。 + `books`テーブルの未使用インデックスを見つけます。 ```sql SELECT * FROM sys.schema_unused_indexes WHERE object_schema='bookshop' AND object_name='books'; ``` ```output +---------------+-------------+--------------+ | object_schema | object_name | index_name | +---------------+-------------+--------------+ | bookshop | books | book_type_uk | +---------------+-------------+--------------+ 1 row in set (0.01 sec) ``` + 未使用のインデックスをCBOに対して不可視にします。CBOはそのようなインデックスを実行計画で使用しません。実際のシナリオでは、システムのパフォーマンス低下を監視する必要があります。 ```sql ALTER TABLE bookshop.books ALTER INDEX book_type_uk INVISIBLE; ``` ```output Query OK, 0 rows affected (0.51 sec) ``` + 一定のビジネスサイクルが完了し、不可視インデックスに関連するパフォーマンス低下が観察されない場合は、それを削除できます。 ```sql ALTER TABLE bookshop.books DROP INDEX book_type_uk; ``` ```output Query OK, 0 rows affected (0.51 sec) ``` + インデックス使用統計から支援を受けて運用決定を行う方法を理解したので、次のモジュールに進んでください。