MySQL 面接の質問: ハッシュ インデックスの設定方法

B-Tree インデックスに加えて、MySQL は次のインデックスも提供します。

• ハッシュインデックス

メモリエンジンのみでサポートされ、シンプルなシナリオ

• Rツリーインデックス

MyISAMの特殊なインデックスタイプ。主に地理空間データ型に使用される。

• 全文

MyISAM の特殊なインデックス。主に全文インデックス作成に使用されます。MySQL 5.6 以降では、InnoDB は全文インデックス作成をサポートしています。

インデックス/ストレージ エンジンMyISAMInnoDBメモリB ツリー インデックスサポートサポートサポートHASH インデックスサポートサポートサポートR ツリー インデックスサポートサポート全文インデックスサポートサポートサポート

最も一般的に使用されるインデックスは B ツリー インデックスとハッシュ インデックスであり、ハッシュ インデックスをサポートするのはメモリ エンジンと NDB エンジンのみです。ハッシュ インデックスはキー値クエリに適しており、ハッシュ インデックスを介したクエリは B ツリー インデックスよりも高速です。ただし、ハッシュ インデックスは、<><==、>== などの範囲検索をサポートしていません。メモリは「=」の条件でのみハッシュインデックスを使用します

MySQL 8.0 は関数インデックスをサポートしています。それ以前は、列の先頭部分のみにインデックスを付けることができ、たとえば、タイトル フィールドの場合、タイトルの最初の 10 文字のみにインデックスを付けることができます。この機能により、インデックス ファイルのサイズが大幅に削減されますが、プレフィックス インデックスにも欠点があり、order by および group by 操作では無効になります。

film(title(10))にインデックスidx_titleを作成します。

1 機能

配列のみが存在し、ハッシュ関数を使用してキーが特定のメモリ位置に変換され、値が配列内のその位置に配置されます。ハッシュを使用するとハッシュの競合が発生する可能性が当然ありますが、MySQL ではジッパー方式を使用してこれを解決します。

ハッシュ インデックスはハッシュ テーブルに基づいて実装されます。ハッシュ インデックスは、クエリ条件がハッシュ インデックス内の列と完全に一致する場合にのみ使用できます。ハッシュ インデックス内のすべての列について、ストレージ エンジンは各行のハッシュコードを計算し、ハッシュコードはハッシュ インデックスに格納されます。

• たとえば、ID 番号と名前を保持し、ID 番号に基づいて対応する名前を検索するテーブルには、次のハッシュ インデックスがあります。

たとえば、ID_card_n4 に対応するユーザー名を確認します。

• ID_card_n4をハッシュ関数で計算してAを得る
• 順番にトラバースしてUser4を見つける

4つのID_card_nの値は必ずしも順番に増えるわけではないので、新しいUserが追加されても速度が速く、最後に追加するだけになります。 もちろん、欠点も明らかです。順序付けされていないため、ハッシュ インデックスは間隔クエリでは非常に遅くなります。たとえば、ID 番号が [ID_card_X、ID_card_Y] の範囲内にあるすべてのユーザーを検索する場合は、テーブル全体をスキャンする必要があります。

2 ハッシュインデックスの欠陥

• 2回検索する必要があります
• 部分インデックス検索や範囲検索はサポートされていません
• ハッシュ コードにはハッシュ衝突が発生する可能性があります。ハッシュ アルゴリズムが適切に設計されていない場合、衝突が多すぎてパフォーマンスが低下します。
• インデックスにはハッシュ値が格納されるため、< = > と IN のみがサポートされます。
• インデックスを操作することでソートすることはできません。ハッシュ値は格納時に計算されますが、計算されたハッシュ値は格納された値と必ずしも等しいとは限らないため、ソートすることはできません。
• 完全なテーブルスキャンは回避できませんが、メモリ テーブルは一意でないハッシュ インデックスをサポートします。つまり、異なるインデックス キーが同じハッシュ値を持つ可能性があります。
• ハッシュ テーブルはキーワードに基づいてメモリの保存場所に直接アクセスするデータ構造であるため、その主要なハッシュ インデックスを使用するには、すべてのデータ ファイルをメモリに追加する必要があり、大量のメモリを消費します。
• すべてのクエリが等値クエリであればハッシュは確かに高速だが、実際には範囲検索データの方が
• キー値の完全な値マッチングのインテリジェントな処理
• クエリハッシュ関数はインデックスキーのサイズを決定します

InnoDB または MyISAM でハッシュ インデックスをサポートするには、適応型ハッシュ インデックスと呼ばれる疑似ハッシュ インデックスを通じて実装できます。

ハッシュ値を保存するフィールドを追加し、ハッシュ値にインデックスを付け、挿入および更新時に計算されたハッシュをテーブルに自動的に追加するトリガーを作成できます。

ハッシュ テーブル構造は、Memcached など、等しい値のクエリのみが必要なシナリオに適しています。

3 事例応用

非常に大きなテーブルがあるとします。たとえば、ユーザーがログインしたときに、メールでユーザーを取得する必要があります。メール列に直接インデックスを作成すると、インデックス範囲のマッチングに加えて、文字列のマッチングも実行する必要があります。メールが短い場合は問題ありませんが、長い場合は、クエリコストが比較的高くなります。 このとき、電子メールにハッシュ インデックスを作成し、int を使用してクエリを実行すると、文字列比較クエリよりもパフォーマンスが大幅に向上します。

ハッシュアルゴリズム

ハッシュ インデックスを作成するには、まず「High Performance MySQL」で説明されている CRC32 アルゴリズムなどのハッシュ アルゴリズムを選択する必要があります。

INSERT UPDATE SELECT 操作

テーブルにハッシュ値フィールドを追加します。

ALTER TABLE `User` ADD COLUMN email_hash int unsigned NOT NULL DEFAULT 0;

次のステップは、UPDATE および INSERT 中に email_hash フィールドを自動的に更新することです。これはトリガーによって実現されます。

区切り文字 |
CREATE TRIGGER user_hash_insert BEFORE INSERT ON `User` FOR EACH ROW BEGIN
NEW.email_hash=crc32(NEW.email) を設定します。
終わり;
|
CREATE TRIGGER user_hash_update BEFORE UPDATE ON `User` FOR EACH ROW BEGIN
NEW.email_hash=crc32(NEW.email) を設定します。
終わり;
|
区切り文字 ;

SELECT リクエストは次のようになります。

`email`、`email_hash` を `User` から選択します。 
	email_hash = CRC32(“[email protected]”) 
			かつ、`email` = "[email protected]";

+----------------------------+------------+
| 電子メール | email_hash |
+----------------------------+------------+
| [email protected] | 2765311122 |
+----------------------------+------------+

AND email = "[email protected]" は、ハッシュ衝突が発生した場合にデータの不正確さを防ぐためです。

これで、MySQL の面接の質問でハッシュ インデックスを設定する方法に関するこの記事は終了です。MySQL でのハッシュ インデックスの設定の詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後も 123WORDPRESS.COM を応援していただければ幸いです。