TiDBインデックス最適化：パフォーマンス向上のための未使用インデックスの検出と削除

「インデックスは、大量のデータをスキャンする必要性を減らし、データベースのクエリパフォーマンスを最適化するために不可欠です。しかし、アプリケーションの進化、ビジネスロジックの変更、およびデータ量の増加に伴い、インデックスの非効率性が生じ、次のような問題が発生します：

• 未使用のインデックス：これらのインデックスは、かつては必要性があったものの、クエリオプティマイザによって選択されなくなり、ストレージを消費し、書き込み操作に不要なオーバーヘッドを追加します。
• 非効率なインデックス：一部のインデックスはオプティマイザによって使用されますが、予想よりも多くのデータをスキャンするため、ディスクI/Oを増やし、クエリパフォーマンスを低下させます。

このようなインデックスの問題を放置しておくと、ストレージコストの増加、パフォーマンスの低下、および運用上の非効率性を引き起こす可能性があります。そのため、定期的なインデックス監査は、最適化されたデータベースを維持するために非常に重要です。

TiDBのような分散SQLデータベースでは、分散クエリの規模とマルチノード連携の複雑さにより、インデックスの非効率性はさらに大きな影響を及ぼします。インデックスを事前に特定して最適化することは、このように役立ちます：

• ストレージオーバーヘッドの削減：未使用のインデックスを削除することで、ディスク領域が解放され、長期的なストレージコストを削減します。
• 書き込みパフォーマンスの向上：不要なインデックスメンテナンスが削減されると、書き込み負荷の高いワークロード (INSERT、UPDATE、DELETE) のパフォーマンスが向上します。 
• クエリ実行の最適化：効率的なインデックスにより、スキャンする行数を減らし、クエリ速度と応答時間を向上させます。
• データベース管理の効率化：最適化された少数のインデックスは、バックアップ、リカバリ、およびスキーマ変更を簡素化します。

TiDB 8.0では、DBAおよび開発者がインデックスの使用パターンを追跡し、データに基づいたの意思決定を行うのに役立つ専用のシステムビューが導入されています。このブログでは、TiDBのパフォーマンスと安定性を向上させるために、未使用または非効率なインデックスを検出し、削減するために必要なツールについて説明します。

インデックスの最適化が重要な理由

未使用および非効率なインデックスは、単なる無駄な領域以上のものです。それらは、データベースのパフォーマンスとスケーラビリティに直接影響します。TiDBのインデックスを積極的に管理することは、システム効率を大幅に向上させることができます。

TiDBインデックス最適化の主なメリット

• ストレージコストの削減：すべてのインデックスはディスク領域を消費します。データベースが成長するにつれて、未使用のインデックスを維持することは、不要なストレージの拡張につながります。それらを削除することで、ディスク領域が解放され、運用コストが削減されます。
• 書き込みパフォーマンスの向上：すべてのINSERT、UPDATE、およびDELETE操作は、関連するインデックスを更新する必要があります。冗長なインデックスは、特に並行性の高いワークロードにおいてこれらの操作を遅くします。インデックスの使用状況を最適化することで、書き込みレイテンシが短縮され、システム全体の応答性が向上します。
• クエリパフォーマンスの向上：非効率なインデックスは、過剰なデータスキャンを引き起こし、ディスクI/Oとクエリレイテンシを増加させる可能性があります。インデックスの選択性を高め、最適化することで、クエリの実行がより高速かつ効率的になります。
• データベース管理の効率化：インデックスが多すぎるデータベースは、バックアップ、リカバリ、およびスキーマ変更を複雑にします。不要なインデックスを減らすことで、データベース管理が簡素化され、メンテナンスがスムーズになります。

インデックスの最適化を習慣化する

インデックスはビジネスロジックの変更とともに進化するため、定期的なインデックス監査はデータベースメンテナンスの標準的な作業である必要があります。TiDBは、ユーザーがリスクを負うことなくインデックスを検出し、評価、最適化できるように監視ツールを提供します。

次のセクションでは、TiDB 8.0のTIDB_INDEX_USAGEおよびschema_unused_indexesビューが、DBAがインデックスを効率的に追跡および最適化するのにどのように役立つかを説明します。

TiDBインデックスの最適化：データ駆動型のアプローチ

インデックスはクエリパフォーマンスに不可欠ですが、適切な分析なしにそれらを削除すると、予期しないリグレッションやシステムの不安定性につながる可能性があります。安全で効果的なインデックス管理を実現するために、TiDBではユーザーが以下を可能にする組み込み監視ツールを提供します：

• リアルタイムでのインデックス使用状況の追跡：インデックスのアクセス頻度を特定し、パフォーマンス向上に貢献しているかどうかを確認します。
• 未使用のインデックスの検出：データベースが最後に再起動されてから使用されていないインデックスを特定します。
• インデックスの効率性の評価：インデックスがデータを効果的にフィルタリングしているか、過剰なI/Oオーバーヘッドを引き起こしているかを評価します。
• インデックス削除の安全なテスト：インデックスに依存するクエリがないことを確認するために、インデックスを削除する前に一時的にインデックスを不可視にします。

TiDBは、3つの強力なツールを導入することでインデックス最適化を簡素化します：

• TIDB_INDEX_USAGE：インデックスの使用パターンとクエリ頻度を監視します。
• schema_unused_indexes：最後のTiDB再起動以降に使用されていないインデックスをリストします。
• Invisible Indexes：DBAがインデックスを完全に削除する前に、削除の影響をテストできるようにします。

これらの監視ツールを使用することで、TiDBユーザーはパフォーマンスの低下のリスクを負うことなく、冗長なインデックスを自信を持ってクリーンアップできます。

TiDBインデックスの最適化：TIDB_INDEX_USAGEによるインデックス使用状況の追跡

TiDB 8.0で導入されたシステムテーブルTIDB_INDEX_USAGEは、インデックスがどのように使用されているかをリアルタイムで把握することができ、DBAがクエリパフォーマンスを最適化し、不要なインデックスを削除するのに役立ちます。

TIDB_INDEX_USAGEを使用する理由

このシステムテーブルにより、ユーザーは以下のことが可能になります：

• 未使用のインデックスの検出：クエリによってアクセスされていないインデックスを特定し、安全に削除できるものを判断するのに役立ちます。
• インデックス効率の分析：インデックスがどの程度の頻度で使用され、効率的なクエリ実行に貢献しているかどうかを追跡します。
• クエリパターンの評価：インデックスが読み取り操作、データスキャン、およびキーバリュー (KV) リクエストにどのように影響するかを理解します。

TiDB 8.4以降では、クラスタ化されたテーブルのプライマリキーも対象となり、インデックスパフォーマンスをより詳細に把握できるようになりました。

TIDB_INDEX_USAGEの主要なメトリクス

カラム名	説明
TABLE_SCHEMA	インデックスを含むデータベース名
TABLE_NAME	インデックスを含むテーブル名
INDEX_NAME	インデックス名
QUERY_TOTAL	インデックスにアクセスしたクエリの合計数で、0の場合インデックスは未使用
KV_REQ_TOTAL	インデックスによって生成されたKVリクエストの合計数
ROWS_ACCESS_TOTAL	インデックスを使用してスキャンされた行の合計数
PERCENTAGE_ACCESS_0	行がアクセスされなかった回数
PERCENTAGE_ACCESS_0_1	Number of times 0%-1% of table roテーブル行の0%-1%がアクセスされた回数
PERCENTAGE_ACCESS_1_10	テーブル行の1%-10%がアクセスされた回数
PERCENTAGE_ACCESS_10_20	テーブル行の10%-20%がアクセスされた回数
PERCENTAGE_ACCESS_20_50	テーブル行の20%-50%がアクセスされた回数
PERCENTAGE_ACCESS_50_100	テーブル行の50%-100%がアクセスされた回数
PERCENTAGE_ACCESS_100	テーブル全体がスキャンされた回数
LAST_ACCESS_TIME	インデックスを使用した最新のクエリのタイムスタンプ

TIDB_INDEX_USAGEを使用して未使用および非効率なインデックスを特定する方法

• 未使用のインデックス：
  • QUERY_TOTAL = 0の場合、インデックスはどのクエリにも使用されていません。
  • LAST_ACCESS_TIMEが非常に古い場合、インデックスはもはや必要性がない可能性があります。
• 非効率なインデックス：
  • PERCENTAGE_ACCESS_100の値が高い場合は、フルインデックススキャンを示唆しており、これは非効率なインデックスを示している可能性があります。
  • ROWS_ACCESS_TOTAL / QUERY_TOTALを比較することで、インデックスがその使用状況に対して過剰な行をスキャンしているかどうかを判断することができます。

TIDB_INDEX_USAGEを活用することで、TiDBユーザーはインデックスパフォーマンスに関する詳細な洞察を得ることができ、不要なインデックスの削除とクエリ実行の最適化が容易になります。

インデックス使用状況データの効率的な処理

遅延するデータの更新

• パフォーマンスへの影響を最小限に抑えるため、TIDB_INDEX_USAGE は即時更新されません。インデックス使用状況のメトリクスは最大5分遅延する可能性があるため、クエリ分析の際にはこの遅延を考慮する必要があります。

永続化されないインデックス使用状況データ

• TIDB_INDEX_USAGE はデータをメモリに保存するため、ノード再起動をまたいで永続化されません。
• TiDB ノードが再起動されると、そのノードの全てのインデックス使用状況統計はクリアされます。

履歴追跡のための機能強化の計画

• TiDB は、インデックス使用状況データを定期的にスナップショットするワークロードリポジトリを開発中です。これにより、ユーザーはリアルタイムのメトリクスのみにだけでなく、時間の経過に伴う傾向をレビューできるようになります。
• この機能が利用可能になるまで、DBAは以下を使用してインデックス使用状況のスナップショットを定期的にエクスポートできます。

SELECT * FROM INFORMATION_SCHEMA.TIDB_INDEX_USAGE INTO OUTFILE '/backup/index_usage_snapshot.csv';

これにより、時間の経過に伴うスナップショットを比較することで履歴追跡が可能になり、インデックスの使用傾向を検出し、より多くの情報に基づいた削除の決定を行うことができます。

TiDBインデックス最適化：TiDBノード全体でのインデックス使用状況データの統合

TiDBは分散SQLデータベースであるため、クエリワークロードは複数のノードに分散されます。各TiDBノードは自身のローカルなインデックス使用状況を追跡しますが、インデックスパフォーマンスのグローバルなビューを得るために、TiDBはCLUSTER_TIDB_INDEX_USAGEシステムテーブルを提供します。

このビューは、すべてのTiDBノードからのインデックス使用状況データを統合し、インデックス戦略を最適化する際に分散クエリワークロードが完全に考慮されるようにします。

CLUSTER_TIDB_INDEX_USAGEがどのように役立つか

ノードレベルでのインサイトを提供するTIDB_INDEX_USAGEとは異なり、このクラスタ全体のビューにより、ユーザーは以下を行うことができます：

• インデックス使用状況の不整合を検出する：例えば、一つのインデックスが、あるノードでは頻繁に使用されるが、他のノードでは使用されないことがあります。
• 分散クエリのグローバルなインデックスパターンを分析し、インデックス作成の決定が実際のワークロードの分散を反映するようにします。
• すべてのノードでインデックス作成戦略を最適化し、複数ノード展開時のクエリ効率を改善させます。

TiDBノードによってクエリの作業負荷が異なるため、あるノードでは使用されていないように見えるインデックスが、他のノードでは依然として重要である可能性があります。ワークロードごとにインデックスの使用状況を分析するには、以下を実行します：

SELECT INSTANCE, TABLE_NAME, INDEX_NAME, SUM(QUERY_TOTAL) AS total_queries
FROM INFORMATION_SCHEMA.CLUSTER_TIDB_INDEX_USAGE
GROUP BY INSTANCE, TABLE_NAME, INDEX_NAME
ORDER BY total_queries DESC;

これにより、インデックスが全ノードで本当に使用されていないのか、特定のインスタンスでのみ使用されているのかを判断することができ、DBAはインデックス削除について十分な情報に基づいた決定を下すことができます。

TIDB_INDEX_USAGEとの主な相違点

機能	TIDB_INDEX_USAGE	CLUSTER_TIDB_INDEX_USAGE
スコープ	単一データベース内のインデックス使用状況を追跡	TiDBクラスタ全体のインデックス使用状況を集約
インデックス トラッキング	データは各データベースに対してローカル	クラスタ全体の集中管理されたビュー
主なユースケース	データベースインスタンスレベルでのインデックス使用状況のデバッグ	グローバルなインデックスパターンとマルチノードの動作の分析

CLUSTER_TIDB_INDEX_USAGEの効果的な使用

このシステムテーブルは複数のノードのデータを統合するため、以下を考慮してください：

データ更新の遅延

• パフォーマンスへの影響を最小限に抑えるため、データは定期的に更新されます。クエリ実行直後にインデックス使用状況を分析する場合は、メトリクスが更新されるまで時間を置いてください。

メモリベースのストレージ

• TIDB_INDEX_USAGEと同様に、このシステムテーブルはノードの再起動をまたいでデータを永続化しません。ノードがダウンすると、記録されたインデックス使用データは失われます。

履歴追跡のための機能強化の計画

• TiDBは、インデックス使用メトリクスを定期的にスナップショットするワークロードリポジトリを開発しており、DBAはリアルタイムデータのみに依存するのではなく、時間の経過に伴う傾向を分析できます。

CLUSTER_TIDB_INDEX_USAGEを活用することで、TiDBユーザーはインデックスの動作に関するグローバルな視点を得ることができ、インデックス設計が分散クエリワークロードと一致していることを確認できます。

TiDBインデックス最適化：schema_unused_indexesによる未使用インデックスの簡単な特定

インデックス使用状況のデータを手動で分析することは、時間がかかる場合があります。このプロセスを簡素化するために、TiDBはschema_unused_indexesを提供します。これは、データベースが最後に再起動されてから使用されていないインデックスを一覧表示するシステムビューです。

これにより、DBAは以下のことを迅速に行うことができます。

• 使用されていないインデックスを特定し、不要なストレージコストを削減します。
• INSERT、UPDATE、およびDELETEクエリにオーバーヘッドを追加するインデックスを排除することで、DML操作を高速化します。
• クエリパターンを手動で分析する必要がなく、インデックス監査を効率化します。

schema_unused_indexesの仕組み

schema_unused_indexesビューはTIDB_INDEX_USAGEから派生しており、最後のTiDB再起動以降、一度もクエリで利用された記録がないのインデックスを自動的に抽出します。

未使用のインデックスのリストを取得するには、以下のコマンドを実行するだけです：

SELECT * FROM sys.schema_unused_indexes;

This will return a result set similar to:

object_schema	object_name	index_name
bookshop	users	nickname
bookshop	ratings	uniq_book_user_idx

schema_unused_indexes を使用する際の重要な注意点

インデックスが ｢未使用｣ とみなされるのは、最後の再起動以降です

• TiDBノードが再起動すると、使用状況追跡データはリセットされます。
• このデータを利用する前に、代表的なワークロードを捕捉するのに十分な期間システムが稼働していることを確認してください。

すべての未使用インデックスを即座に削除するべきではありません

• インデックスの中には、ほとんど使用されていないにもかかわらず、特定のクエリ、バッチジョブ、またはレポートタスクに不可欠なものがあります。インデックスを削除する前に、そのインデックスが以下をサポートしているかどうかを検討してください：
  • 稀だが不可欠なクエリ (例：月次レポート、分析)
  • 毎日実行されないバッチ処理ジョブ
  • DBAが使用するアドホックなトラブルシューティングクエリ
• 重要だが使用頻度の低いクエリにインデックスが表示される場合は、そのインデックスを維持するか、まず不可視にすることを検討します。
• パフォーマンスに影響を与えずにインデックスを削除できるかどうかを安全にテストするには、不可視インデックス (次のセクションで説明) を使用してください。

schema_unused_indexesを活用することで、TiDBユーザーは不要なインデックスを迅速に特定し、最小限の労力でデータベースのオーバーヘッドを削減できます。

TiDBインデックスの最適化：不可視インデックスによるインデックス削除の安全なテスト

適切な検証を行わずにインデックスを削除すると、予期せぬ問題が発生する可能性があります。特に、インデックスの使用頻度は低いものの、特定のクエリにとっては重要なインデックスである場合です。このリスクを軽減するために、TiDBは不可視インデックスを提供し、DBAはインデックスを削除せずに一時的に無効にすることができます。

不可視インデックスとは

不可視インデックスはデータベースに存在しますが、TiDBオプティマイザによって無視されます。これにより、ユーザーはインデックスを完全に削除せずに、インデックスが本当に不要かどうかをテストできます。

主な利点は次のとおりです：

• 安全なインデックステスト – クエリはインデックスを使用しなくなりますが、必要に応じて迅速に復元できます。
• インデックスストレージを削除しない – インデックスはそのまま残るため、コストのかかる再作成は不要です。
• パフォーマンスのモニタリング – DBAは最終的な決定を下す前に、インデックスなしのクエリの動作を観察することができます。

TiDBで不可視インデックスを使用する

インデックスを (削除せずに) 不可視にするには、次のコマンドを使用します：

ALTER TABLE bookshop.users ALTER INDEX nickname INVISIBLE;

クエリパフォーマンスの監視

インデックスを不可視にした後、システムのクエリパフォーマンスを観察します：

• パフォーマンスが変化しない場合、インデックスは不要である可能性が高く、安全に削除できます。
• クエリレイテンシが増加する場合、インデックスはまだ必要である可能性があり、削除を再検討する必要があります。

不可視インデックスを使用するためのベストプラクティス

• オフピーク時にテストを実施 – 管理された環境でパフォーマンスへの影響を監視します。
• クエリ監視ツールの使用 – インデックスを不可視にする前後にクエリ実行計画を分析します。
• 複数のワークロードでの確認 – インデックスが特定のレポートやスケジュールされたクエリに必要ないことを確認します。

不可視インデックスを活用することで、TiDBユーザーはリスクなしでインデックス削除の決定を検証し、より管理された予測可能なデータベースの最適化プロセスが実現できます。

インデックスを不可視にする期間は？

• OLTPワークロード：日々の変動を考慮して、少なくとも1週間監視します。
• バッチ処理/ETLワークロード：1つの完全なレポートサイクル (例：月次財務レポートの実行) を待ちます。
• アドホックな分析クエリ：クエリログを使用して、インデックスを削除する前に不要であることを確認します。

安全のために、インデックスを最終的に削除する前に、少なくとも1つの完全なビジネスサイクルを通してインデックスを不可視にし、すべてのワークロードがテストされたことを確認してください。

システムテーブル schema_unused_indexes

ユーザーが結果を簡単に取得できるように、TiDBは、すべてのTiDBインスタンスが最後に開始されてから使用されていないインデックスをリストするMySQL互換のビューsys.schema_unused_indexesも提供します。このビューのデータはTIDB_INDEX_USAGEから取得されます。TIDB_INDEX_USAGEは、TiDBノードが再起動されるとクリアされるため、インデックスの使用状況を確認する前にノードが十分な時間実行されていることを確認する必要があります。

古いバージョンからTiDB 8.0以降にアップグレードされたクラスタの場合、sysスキーマとそこに含まれるビューを手動で作成する必要があります。手順については、公式ドキュメントを参照してください。

まとめと重要なポイント

効果的なインデックス管理は、TiDBにおけるデータベースパフォーマンスの維持に非常に重要です。TiDBの監視ツールを活用することで、DBAはシステム安定性を損なうことなく、インデックスを簡単に特定、評価、最適化できます。

以下のベストプラクティスに従うことで、TiDBユーザーはデータベースを最適化し、不要なリソース消費を削減し、最適なクエリパフォーマンスを維持できます。

1. インデックス使用状況を定期的に監視
   1. TIDB_INDEX_USAGEを使用してインデックスのクエリアクティビティを追跡します。
   2. CLUSTER_TIDB_INDEX_USAGEを使用してクラスタ全体のインデックスの動作を把握します。
2. 未使用のインデックスを確実に特定
   1. schema_unused_indexesを使用して、最後の再起動以降に使用されていないインデックスをリストアップします。
   2. 注意してください。一部のインデックスは使用頻度が低いものの、特定のクエリにとって依然として重要である可能性があります。
3. 不可視インデックスを使用したインデックス削除の安全なテスト
   1. インデックスを削除する前にINVISIBLEとしてマークし、その必要性を検証します。
   2. クエリパフォーマンスが悪影響を受ける場合は、不可視を復元します。
4. インデックスの最適化でオーバーヘッドを削減
   1. ストレージを消費し、書き込み操作を遅くする冗長または低選択性のインデックスを回避します。
   2. クエリフィルタリング効率を向上させるために、インデックス構造を最適化します。
5. 継続的なインデックスメンテナンスを優先
   1. スキーマ変更、アプリケーション更新、またはワークロードの変化後にインデックスを定期的に監査します。
   2. TiDBの実行計画分析ツールを使用して、インデックスが効果的に使用されていることを確認します。

TiDBインデックス最適化についてご不明な点がございましたら、X (旧Twitter)、LinkedIn、またはSlackチャネルでお気軽にお問い合わせください。