MySQL の冗長インデックスと重複インデックスの詳細な説明

MySQL では、同じ列に複数のインデックスを作成できます。意図的であるかどうかにかかわらず、MySQL は重複したインデックスを個別に維持する必要があり、クエリを最適化するときにオプティマイザもそれらを 1 つずつ考慮する必要があるため、パフォーマンスに影響します。

重複インデックスとは、同じ列に同じ順序で作成された同じタイプのインデックスを指します。このように重複インデックスを作成することは避け、見つかった場合はすぐに削除する必要があります。ただし、異なるクエリ要件を満たすために、同じ列に異なるタイプのインデックスを作成することは可能です。

CREATE TABLEテスト(
 ID INT NOT NULL 主キー、
 INT NOT NULL、
 B INT NULLではない、
 ユニーク(ID)
 インデックス(ID)、
)ENGINE=InnoDB;

この SQL は 3 つの重複インデックスを作成します。通常、これを行う理由はありません。

冗長インデックスと重複インデックスにはいくつかの違いがあります。インデックス (a, b) を作成する場合、インデックス (a) の作成は、前のインデックスのプレフィックス インデックスにすぎないため冗長インデックスになります。したがって、(a, b) は (a) としても使用できますが、(b, a) は冗長インデックスではありません。また、インデックス (b) も、b がインデックス (a, b) の左端のプレフィックス列ではないため冗長インデックスではありません。さらに、同じ列に作成された他の異なるタイプのインデックス (ハッシュ インデックスやフルテキスト インデックスなど) は、カバーされるインデックス列に関係なく、B ツリー インデックスの冗長インデックスにはなりません。

冗長なインデックスは通常、テーブルに新しいインデックスを追加するときに発生します。たとえば、既存のインデックス (A) を拡張するのではなく、新しいインデックス (A,B) を追加する場合があります。もう 1 つのケースは、インデックスを (A, ID) に拡張することです。ここで、ID は主キーです。InnoDB の場合、主キーはすでにセカンダリ インデックスに含まれているため、これも冗長です。

ほとんどの場合、冗長インデックスは不要であり、新しいインデックスを作成するのではなく、既存のインデックスを拡張する必要があります。ただし、既存のインデックスを拡張するとサイズが大きくなりすぎて、そのインデックスを使用する他のクエリのパフォーマンスに影響するため、パフォーマンス上の理由から冗長インデックスが必要になる場合があります。たとえば、整数列にインデックスがあり、インデックスを拡張するために長い varchar 列を追加する必要がある場合、特にこのインデックスをカバー インデックスとして使用するクエリがある場合、またはこれが MyISAM テーブルであり、範囲クエリが多数ある場合 (MyISAM プレフィックス圧縮のため)、パフォーマンスが大幅に低下する可能性があります。

たとえば、userinfo テーブルがあります。このテーブルには 1,000,000 件のレコードがあり、state_id 値ごとに約 20,000 件のレコードがあります。 state_idにはインデックスがあるので、次のSQLはQ1と呼ばれます。

SELECT count(*) FROM userinfo WHERE state_id=5; --Q1

クエリ実行速度は1秒あたり約115回（QPS）です。

もう1つのSQLがあります。これをQ2と呼びましょう。

SELECT state_id,city,address FROM userinfo WHERE state_id=5; --Q2

このクエリの QPS は 10 です。このインデックスのパフォーマンスを向上させる最も簡単な方法は、インデックスがクエリをカバーできるように、(state_id、city、address) のワイルド ウォー インデックスを使用することです。

アラート テーブル userinfo にキー state_id_2(state_id,city,address) を追加します。

注: state_id にはすでにインデックスがあります。前の概念によれば、これは重複インデックスではなく冗長インデックスです)

冗長インデックスと重複インデックスを見つけるにはどうすればよいでしょうか?

1. Shlomi Noach の common_schema のいくつかのビューを使用して、それを見つけることができます。common_schema は、サーバーにインストールできる共通のストレージとビューのセットです。

2. Percona Toolkit の pt_duplicate-key-checker を使用すると、テーブル構造を分析して冗長なインデックスや重複したインデックスを見つけることができます。

要約する

以上が、MySQL の冗長インデックスと重複インデックスの詳細な説明に関するこの記事の内容のすべてです。皆様のお役に立てれば幸いです。興味のある方は、次のものを参照してください: いくつかの重要な MySQL 変数、Redis と MySQL の違いの簡単な説明、MYSQL サブクエリとネストされたクエリの最適化例の分析など。不足がある場合は、メッセージを残して指摘してください。編集者が適時に返信し、修正します。このサイトをサポートしていただき、ありがとうございます!