MySQL のインデックスとデータ テーブルを管理する方法

テーブルの競合を見つけて修正する

データ テーブルに起こりうる最悪の事態は競合です。 MyISAM ストレージ エンジンを使用する場合、競合は通常、クラッシュによって発生します。ただし、ハードウェア障害、MySQL の内部バグ、またはオペレーティング システムのバグが発生した場合、すべてのストレージ エンジンでインデックスの競合が発生する可能性があります。

競合するインデックスがあると、クエリが誤った結果を返したり、重複する値がない場合でも重複インデックス エラーが増加したり、テーブル全体のスキャンやクラッシュが発生する可能性もあります。発生しないと思われるエラーなどの偶発的なイベントが発生した場合は、CHECK TABLE コマンドを実行して、データ テーブルに競合があるかどうかを検出します (一部のデータベース エンジンはこのコマンドをサポートしていませんが、他のデータベース エンジンはテーブルのチェック方法を指定する複数のオプション パラメータをサポートしています)。通常、CHECK TABLE コマンドはほとんどのテーブルおよびインデックス エラーを検出します。

REPAIR TABLE コマンドを使用してデータ テーブル エラーを修復できますが、すべてのストレージ エンジンがこのコマンドをサポートしているわけではありません。このとき、データ テーブルのエンジンを現在のエンジンと同じものに変更するなど、「操作なし」の ALTER ステートメントを実行する必要があります。たとえば、InnoDB データ テーブルに対して次のステートメントを実行できます。

ALTER TABLE innodb_tb1 ENGINE=INNODB;

あるいは、myisamchk などのストレージ エンジン固有のオフライン修復ツールを使用したり、データをエクスポートして再インポートしたりすることもできます。ただし、システム領域で競合が発生した場合や、インデックスではなくデータテーブルのデータ行領域で競合が発生した場合は、これらの方法を使用できない場合があります。この場合、バックアップからデータを復元するか、競合するファイルからデータを回復する必要がある場合があります。

InnoDB で競合が発生した場合、これは重大なエラーであり、正しい方法を使用して問題を分析する必要があります。 InnoDB には通常、競合は発生しません。競合処理に対して堅牢になるように設計されています。競合は、ハードウェア障害 (メモリ領域エラーやディスク エラーなど)、DBA の操作エラー (MySQL 環境外でデータベース ファイルを操作するなど)、または InnoDB 自体のバグ (この可能性は非常に低い) の兆候である可能性があります。一般的な理由は、rsync ユーティリティを使用してバックアップを作成するときに発生するエラーと同様です。現時点では実行できるクエリはありません。これは、回避できると思われる InnoDB データの競合を引き起こすためです。問題のあるクエリによって InnoDB データの競合が発生した場合、それはユーザーの責任ではなく、InnoDB のバグです。

実際にデータの競合が発生した場合、最も重要なことは競合の原因を突き止めることです。その前に、単にデータを修復しないでください。競合は自動的に消えるかもしれません。 innodb_force_recovery パラメータを使用して InnoDB を強制リカバリ モードに変更し、データを修復することができます (MySQL マニュアルを参照)。オープンソースの Percona InnoDB データ復旧ツール (www.percona.com/software/my…) を使用して、破損したデータ ファイルからデータを抽出することもできます。

インデックス統計の更新

MySQL クエリ オプティマイザーは、インデックスの使用方法を決定する前に、2 つの API を呼び出してインデックス値の分布を取得します。 1 つ目は records_in_range メソッドで、範囲を引数として受け取り、その範囲内の結果の数を返します。返される結果は MyISAM エンジンの場合は正確ですが、InnoDB の場合は推定値になります。

2 番目の API は info メソッドで、インデックス候補 (つまり、各インデックスに対応するレコード数の推定値) を含むさまざまな種類のデータを返します。

ストレージ エンジンがクエリ オプティマイザーに不正確なデータ行数情報を提供する場合、またはクエリ プランが複雑すぎて正確な行数を推定できない場合、オプティマイザーはインデックス統計を使用してデータ行数を推定します。 MySQL オプティマイザーはクエリ コストに基づいて決定を下しますが、最も重要なコスト基準はクエリが検索するデータの量です。インデックス統計が一度も生成されていない場合、または古くなっている場合、オプティマイザーは誤った決定を下す可能性があります。解決策は、ANALYZE TABLE コマンドを実行してインデックス統計を再構築することです。

各ストレージ エンジンはインデックス統計を異なる方法で実装するため、ANALUZE TABLE コマンドの実行頻度とコストは異なる場合があります。一般的なストレージ エンジンは、インデックス統計を次のように処理します。

• メモリ エンジンはインデックス統計を保存しません。
• MyISAM はインデックス統計をディスクに保存し、ANALYZE TABLE は候補データ行を計算するときに完全なインデックス スキャンを使用します。このプロセス中はテーブル全体がロックされます。
• InnoDB は、MySQL 5.5 以降、インデックス統計をディスクに保存しません。代わりに、インデックスをランダムにサンプリングし、その結果をメモリに保存します。
  

インデックスの候補は、SHOW INDEX FROM コマンドを使用して調べることができます。例えば：

このコマンドはインデックスに関する多くの情報を提供します。詳細については、MySQL マニュアルを参照してください。ここで特に興味深いのは、Cardinality 列です。この列には、ストレージ エンジンがインデックスに対応すると推定する一意の値の数が表示されます。 MySQL 5.0 以降では、この情報は INFORMATION_SCHEMA.STATISTICS テーブルでも利用できるので、非常に便利です。たとえば、INFORMATION_SCHEMA をクエリして、フィルタリング可能性が低いインデックスを見つけることができます。ただし、大量のデータを持つサーバーの場合、これらの中間テーブルによってサーバーの負荷が大幅に増加する可能性があることに注意してください。

InnoDB の統計についてはさらに研究する価値があります。統計結果は、残りの未サンプリングデータも同様に分布していると仮定して、インデックス データ ページをランダムにサンプリングすることによって計算されます。以前の InnoDB バージョンでは、このサンプリングされたページ数は 8 でしたが、最近のバージョンでは、innodb_stats_sample_pages 変数を使用して調整できます。この値を 8 より大きい値に設定すると、特に大きなテーブルの場合、より代表的なインデックス統計を生成しやすくなりますが、コストは異なります。

InnoDB は、テーブルが最初に開かれたとき、ANALUZE TABLE が実行されたとき、およびテーブル ストレージ サイズが大幅に変更されたとき (変更の 1/16 または 20 億行が挿入されたとき) にインデックス統計を計算します。

InnoDB は、 INFORMATION_SCHEMA テーブルの特定のクエリ、 SHOW TABLE STATUS の実行、 SHOW INDEX クエリの実行、または自動補完設定が有効になっている MySQL コマンドライン クライアントのインデックス統計も計算します。これは、大量のデータや非常に遅い I/O 速度を持つサーバーでは実際に深刻な問題を引き起こす可能性があります。クライアント プログラムや監視ツールによって発生する再サンプリングにより、大量のロックが発生し、サーバーの負荷が増加する可能性があり、エンド ユーザーの起動時間にも影響する可能性があります。 SHOW INDEX コマンドはインデックス統計を更新するため、インデックス統計を変更しないとインデックス統計を確認できません。 innodb_stats_on_metadata オプションを無効にすると (デフォルトではオフ)、これらの問題を回避できます。次のコマンドは、InnoDB インデックス統計に関連するシステム変数を確認できます。

グローバル変数を表示、変数名が 'innodb_stats%' のような場合

InnoDB の代替となる Percona XtraDB ストレージ エンジンを備えた Percona Server を使用している場合は、さらに構成を行うことができます。 innodb_stats_auto_update オプションを使用すると、自動サンプリングを無効にし、ANALYZE TABLE を手動で実行しない限り、自動統計計算を事実上凍結することができます。これにより、不安定なクエリから解放されます。この機能は、大規模な展開を行っている顧客からのリクエストに基づいて追加されました。

クエリ プランの安定性を高め、システムの起動を高速化するために、システム レベルのデータ テーブルを使用してインデックス統計を保存できます。この方法では、システムを再起動したとき、または InnoDB を初めて起動してデータ テーブルを開いたときに、インデックス統計を再計算する必要がありません。この機能は、Percona 5.1 および標準の MySQL 5.6 リリースで利用できます。この Percona Server 機能は、innodb_use_sys_stats_table オプションによって有効になります。 MySQL バージョン 5.6 以降では、デフォルトで ON になっている innodb_stats_persistent オプションによって制御されます。同時に、単一のテーブルを制御する別の変数があります。innodb_stats_auto_recalc 変数はデフォルトで ON に設定されており、データ テーブルが 10% 以上変更されると、テーブルのインデックス統計が再計算されます (マニュアルは dev.mysql.com/doc/refman/… で参照できます)。

インデックス統計の自動更新を構成しない場合は、更新しなくてもクエリ プランの品質が低下しないことがわかっている場合を除き、定期的に ANALYZE TABLE コマンドを使用してインデックス統計を更新する必要があります。

上記は、MySQL インデックスとデータ テーブルをメンテナンスする方法の詳細です。MySQL インデックスとデータ テーブルのメンテナンスの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。