MySQL 文字列インデックスのより合理的な作成ルールに関する議論

序文

MySQL インデックスの使用に関しては、これまでインデックスの最左接頭辞ルール、インデックスカバレッジ、ユニークインデックスと共通インデックスの使用、オプティマイザによるインデックスの選択などの概念を紹介してきました。今日は、文字列のインデックスをより合理的に作成する方法について説明します。

文字列インデックスをより良く作成する方法

MySQL では、データとインデックスはすべて B+ ツリー上にあることがわかっています。インデックスを作成するときに、ツリーが占めるスペースが小さいほど、検索速度が速くなります。varchar 形式の文字列の中には非常に長いものもあります。では、効率が最優先される今日の世界では、文字列インデックスをより合理的に作成するにはどうすればよいでしょうか。
テーブルに電子メールフィールドがあり、電子メールフィールドのインデックスを作成するとします。電子メールフィールドの値の形式は、[email protected] です。

インデックスを作成するには 2 つの方法があります。

1. 電子メールフィールドのインデックスを直接作成します。alter alter table t add index index1(email);

インデックスツリー構造は次のとおりです。

2. 電子メールのプレフィックスインデックスを作成します。alter alter table t add index index2(email(6));

インデックスのデータ構造は次のとおりです。

現時点でのクエリステートメントは次のとおりです: select id,name,email from t where email='[email protected]';

index1 を使用する場合の実行手順は次のとおりです。

1. インデックス値が [email protected] である主キー値 ID1 を index1 インデックスツリーから検索します。

2. ID1 に基づいてテーブルに戻り、データ行が実際に [email protected] であることを確認します。結果を結果セットに追加します。

3. index1 インデックスツリーの次のインデックス値が [email protected] を満たすかどうかの検索を続けます。満たさない場合は、クエリを終了します。

index2 を使用する場合の実行手順は次のとおりです。

1. index2 インデックスツリーからインデックス値 zhangs を持つ主キー値 ID1 を検索します。

2. ID1 に基づいてテーブルに戻り、データ行が実際に [email protected] であることを確認します。結果を結果セットに追加します。

3. index2 インデックスツリーの次のインデックス値が zhangs を満たすかどうかの検索を続けます。満たしている場合は、テーブルに戻って行データが [email protected] であるかどうかを照会し続けます。満たしていない場合は、スキップして検索を続けます。

4. インデックス値が zhangs でなくなるまで、index2 インデックスツリーの検索を続けます。

上記の分析から、フルフィールドインデックスはプレフィックスインデックスと比較してテーブルリターンの数を減らすことがわかります。ただし、プレフィックスを6から7または8に増やすと、プレフィックスインデックステーブルリターンの数は減少します。つまり、プレフィックスの長さが定義されている限り、スペースを節約し、効率を確保できます。

そこで疑問になるのが、プレフィックスインデックスの長さをどのように測定するかということです。

1. select count(distinct email) as L from t; 。

2. 異なるプレフィックス長を順番に選択して、異なる値の数を表示します。

選択
 count(distinct left(email,4)）をL4として、
 count(distinct left(email,5)）をL5として、
 count(distinct left(email,6)）をL6としてカウントし、
 count(distinct left(email,7)）をL7としてカウントし、
t から;

次に、実際の許容損失率に応じて、最も短い適切なプレフィックス長が選択されます。

プレフィックス長の問題は解決しましたが、プレフィックスインデックスを使用すると、インデックスカバレッジ機能が使用されなくなるという問題があります。
完全なフィールドインデックスを使用する場合、 select id,email from t where email='[email protected]';をクエリすると、テーブルに戻らずに id フィールドと email フィールドを直接見つけることができます。

ただし、プレフィックスインデックスを使用する場合、MySQL はプレフィックスが電子メール値全体をカバーするかどうかを認識しません。完全に含まれているかどうかに関係なく、主キー値に基づいてテーブルをクエリすることによって決定されます。

したがって、プレフィックスインデックスを使用すると、スペースを節約して効率を確保できますが、カバーリングインデックスの特性を活用できません。使用するかどうかは、具体的な考慮事項によって異なります。

文字列インデックスを作成する他の方法

実際には、プレフィックス切り捨てを使用してすべての文字列をインデックスできるわけではありません。たとえば、ID番号やIPアドレスなどの文字列にプレフィックスインデックスを使用することは合理的ではありません。ID番号の最初の数桁は、同じ地域の人であれば一般的に同じであるため、プレフィックスインデックスを使用することは合理的ではありません。実際には、通常、IP値を数値に変換して保存します。

ID カード番号の場合、逆ストレージを使用したり、プレフィックスを取得してインデックスを作成したり、crc32() 関数を使用してハッシュチェックコード (int 値) をインデックスとして取得したりできます。

逆: select field_list from t where id_card = reverse('input_id_card_string');

crc32: select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

これら 2 つの方法は比較的効率的です。どちらも範囲検索はサポートしていませんが、等しい値の検索はサポートしています。

リバース方式ではリバース関数を使用する必要がありますが、返されるテーブルの数はハッシュ方式よりも多くなる場合があります。

ハッシュモードでは、新しいインデックスフィールドを作成し、crc32() 関数を呼び出す必要があります。 (注: crc32() 関数によって取得された結果は一意であることが保証されません。重複する可能性がありますが、その可能性は低いです)。テーブルが返される回数は少なく、ほぼ 1 回で十分です。

やっと

一般に、文字列インデックスを作成するにはいくつかの方法があります。

1. 文字列が短いため、フィールド全体が直接インデックス化されます

2. 文字列が長く、プレフィックスの識別性が高いため、プレフィックスインデックスを作成します。

3. 文字列が長く、プレフィックスが区別できない場合は、逆順またはハッシュモードでインデックスを作成します（この方法は範囲クエリでは機能しません）

4. 実際の状況に応じて、ip などの特殊な文字列は特別に扱われます。