主流のウェブサイトが携帯電話番号でのログインをサポートするようになった今、携帯電話番号のような文字列フィールドに適切なインデックスを構築するにはどうすればよいでしょうか？

メールボックスへのログインをサポートするシステムを管理していて、ユーザーテーブルが次のように定義されているとします：

sql
create table SUser(
	ID bigint unsigned primary key,
	email varchar(64), 
	... 
)engine=innodb; 

Eメールを使ってログインするには、以下のような記述があります：

sql
select f1, f2 from SUser where email='xxx';

emailフィールドがインデックス化されていない場合、ステートメントはテーブル全体をスキャンすることしかできません。

MySQL はプレフィックスインデックスをサポートしており、文字列の一部をインデックスとして定義することができます。インデックスを作成するステートメントがプレフィックス長を指定しない場合、デフォルトでインデックスには文字列全体が含まれます。

例えば、以下の2つのステートメントはemailフィールドにインデックスを作成します：

sql
alter table SUser add index index1(email);
alter table SUser add index index2(email(6));

• 最初のステートメントで作成されたindex1インデックスには、各レコードの文字列全体が格納されています。
• 2番目のステートメントでは、各レコードの最初の6バイトだけを取るindex2インデックスを作成します。

email(6)インデックス構造内の各メールボックスフィールドは最初の6バイトしかとらず、より少ないスペースしかとらないことがわかります。

これは同時に、レコードスキャンが追加される可能性があるという損失も伴います。

次のステートメントを見て、2つのインデックス定義のそれぞれでどのように実行されるかを見てください。

sql
select id,name,email from SUser where email='zhangssxyz@xxx.com';

index1を使用する場合、実行シーケンスは以下のようになります：

1. index1のインデックスツリーからインデックス値'zhangssxyz@xxx.com'を満たすレコードを見つけ、ID2の値を取得します。
2. 主キーに移動し、主キーの値がID2である行を調べ、Eメールの値が正しいことを確認し、変更した行を結果セットに追加します。
3. index1のインデックスツリーで次のレコードの位置をチェックし、email='zhangssxyz@xxx.com'の条件が満たされなくなったことを確認し、ループを終了します。

この処理では、データを取得するために主キーインデックスに戻る必要があるのは1回だけなので、システムはスキャンされたのは1行だけとみなします。

index2を使用する場合、実行順序は以下のようになります：

1. index2のツリーから、インデックス値'zhangs'を満たすレコードを探します。
2. 主キーに移動し、主キーの値がID1である行をチェックし、電子メールの値が'zhangssxyz@xxx.com'ではないことを確認し、行のレコードが破棄されます。
3. index2を取るだけで、次のレコードの位置をチェックし、まだ'zhangs'、ID2を取り出し、IDインデックスに判断の行全体を取るには、この時間は、右の値は、行のレコードの結果セットに追加されます
4. idxe2の値が'zhangs'でなくなるまで前のステップを繰り返し、ループを終了します。

この処理のために、データは主キーインデックスに4回戻って取得されます。

この比較から、プレフィックスインデックスを使用すると、クエリステートメントがより頻繁にデータを読み込む可能性があることがわかります。

しかし、このクエリステートメントでは、定義index2がemail（6）ではなく、email（7）である場合、すなわち、インデックスを構築するために電子メールフィールドの最初の7バイトを取るつまり、接頭辞'zhangss'レコードが1つだけであることを満たす、それはまた、ID2に直接チェックすることができ、唯一の1行のスキャン終了。

つまり、長さを定義したプレフィックスインデックスを使用することで、余分なクエリコストを増やすことなくスペースを節約することができます。

文字列のプレフィックス・インデックスを作成するには

プレフィックス長の決定

インデックスを作成する際に気になるのは、差別化の度合いです。分化の度合いが高ければ高いほど、重複するキー値は少なくなります。つまり、どれくらいの長さの接頭辞を使うべきかは、 インデックスにいくつの異なる値があるかを数えることで判断できます。

次のステートメントを使用すると、列上にいくつの異なる値があるかを計算できます。

sql
select count(distinct email) as L from SUser;

例えば、4 バイトから 7 バイトのプレフィックス・インデックスを調べます：

sql
select 
 count(distinct left(email,4)) を L4、
 count）をL5とする、
 (count）をL6として、L6として、L6として
 としてカウント) L7,
from SUser;

プレフィックス・インデックスの使用は識別性を失う可能性があるため、許容できる損失率、例えば5%をあらかじめ設定しておく必要があります。そして、返されたL4~L7の中で、L6とL7を満たしたときに、最短のプレフィックス長6を選択できるとして、L*95%を下回らない値を求めます。

プレフィックス・インデックスがカバレッジ・インデックスに与える影響

次のSQLを見てください：

sql
select id,email from SUser where email='zhangssxyz@xxx.com';

を先ほどのSQL文の例に置き換えてください：

sql
select id,name,email from SUser where email='zhangssxyz@xxx.com';

一方、このステートメントでは、idとemailを返すように要求しているだけです。

index1を使用する場合、index1からの結果を確認するためにオーバーライド・インデックスを使用して直接返すことができます。index2を使用する場合は、emailフィールドの値を判断するためにIDインデックスに戻る必要があります。

index2の定義がemail(18)に変更されたとしても、index2が既に全ての情報を含んでいるにもかかわらず、InnoDBはidインデックスに戻って再度確認する必要があります。これは、プレフィックスインデックスを使用するかどうかを選択する際に考慮すべき要因でもあります。

その他のプログラム

メールボックスのようなフィールドでは、接頭辞のインデックス付けは問題ないかもしれません。しかし、十分に区別されていないプレフィックスに遭遇した場合はどうすればよいでしょうか？

たとえば、IDカード番号18ビット、最初の6ビットは、アドレスコードですので、同じ県の人々のIDカード番号の最初の6ビットは、一般的に同じです。メンテナンスデータベースは、市町村の市民情報システムであると仮定すると、IDカード番号の長さが6プレフィックスインデックスを行うには、差別化の程度は非常に低いです。差別化の要件を満たすために長さ12以上のプレフィックスインデックスを作成する必要があります。

しかし、インデックスが長く選択されればされるほど、より多くのディスクスペースを占有し、同じデータページに対して置けるインデックス値は少なくなり、クエリ効率は低下します。

• ビジネス要件がIDカードによる同等のクエリの必要性だけであると判断できる場合、より少ないスペースで同じクエリ効率を達成する別の処理方法はありますか？あります！

最初の使い方は

逆順保管

ID番号を逆に保存する場合は、クエリごとにこのように記述します：

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

ID番号の下6桁はアドレスコードのような重複ロジックを持たないので、下6桁で十分な差別化ができるかもしれません。実際には、count(distinct)を使って区別の程度を確認することを忘れないでください！

つ目の方法は

ハッシュ・フィールド

IDカードのチェックデジットを保持するために、テーブルに別の整数フィールドを作成し、そのフィールドにインデックスを作成することもできます。

sql
alter table t add id_card_crc int unsigned, add index(id_card_crc);

新しいレコードが挿入されるたびに、crc32() 関数が新しいフィールドを埋めるためのチェックデジットを取得するために使用されます。チェックデジットが競合している可能性があるため、つまり、2つの異なるIDカード番号のcrc32（）結果が同じである可能性がありますので、クエリどこステートメントのid_card値が正確に同じであるかどうかを判断するための部分。

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

このインデックスの長さは4バイトになり、オリジナルよりずっと小さくなります。

反転ストレージとハッシュ・フィールドの類似点と相違点

接点

どちらもレンジクエリをサポートしていません。

• 逆順で保存されたフィールドに作成されたインデックス 逆順の文字列でソートすると、[ID_X, ID_Y]にID番号を持つすべての市民を見つけるためにインデックスを使用することはできなくなります。
• また、ハッシュフィールドは等値検索のみをサポートしています。

異にする

追加スペース

• もちろん、4バイトのプレフィックス長を使用する反転ストレージでは十分ではないはずですが、ハッシュ・フィールドとほぼ同じ長さを消費します。
• ハッシュ・フィールドには追加のフィールドが必要です。

CPU消費量

• リバース・メソッドでは、読み書きするたびにリバース関数を呼び出す必要があります。
• ハッシュ・フィールドでは、crc32()関数を追加で呼び出す必要があります。

この2つの関数の計算複雑さだけを見れば、逆関数の方が余計なCPUリソースを消費しません。

クエリー効率

• ハッシュフィールドクエリのパフォーマンスはより安定しています。 なぜなら、crc32値は衝突するものの、その確率は小さく、クエリごとにスキャンされる行の平均数は1に近いと考えることができるからです。
• インバーテッド・ストレージ 依然としてプレフィックス・インデックス、つまりスキャンされる行数が増加

概要

文字列フィールドにインデックスを作成するシナリオは、以下のように使用できます：

1. 完全なインデックスを直接作成します。
2. プレフィックス・インデックスを作成すると、スペースを節約できますが、クエリのスキャン回数が増えます。
3. 逆順に格納し、プレフィックス・インデックスを作成することで、文字列自体のプレフィックスにある差別化の欠点を回避することができます。
4. ハッシュフィールドインデックスを作成し、安定したクエリのパフォーマンス、追加のストレージと計算の消費量は、3番目の方法と同様に、範囲のスキャンをサポートしていません。

実際には、どの方法を使用するかは、ビジネス分野の特性に基づいて選択されます。

議題

学生情報のデータベースを管理し、学生ログイン名の統一フォーマットは「学号@gmail.com」。学生番号は15桁の番号で、最初の3桁は市町村番号、4～6桁は学校番号、7～10桁は入学年度、最後の5桁は連番。

システムはログイン名とパスワードを入力することでログインし、システムの使用を継続する前に正しく検証されます。ログイン検証のみを考慮したこのログイン名のインデックスをどのように設計しますか？前回お預かりした質問は、学校番号フィールドのインデックスを作成する方法にはどのようなものがあるかというものでした。

学校番号ルールのため、前方および後方接頭辞インデックスに関係なく、重複は高くなります。1校しか管理されないため、最初の6ビットは固定で、邮箱后缀都是@gamil.com、入学年と連番のみ、9ビット長で保存できます。

これをベースに、デジタル・タイプを使ってこの9桁を格納することができます。例えば、201100001の場合、わずか4バイトです。実際には、これは一種のハッシュであり、文字列からデジタルへの最も単純な変換ルールで、変換結果が一意になるように背景を設定するだけです。

もちろん、学校の人々の総数は、このデータ量は、50年わずか100万人の学生は、このテーブルは確かに小さなテーブルです。ビジネスの簡素化のために、元の文字列が直接格納されています。コストと利益の最適化 "のアイデア。