ハッシュ化による複合インデックスの最適化

複合インデックスをハッシュ化する理由

複合インデックスをハッシュ化することによって、大きな複合キーをより小さな固定サイズの値に変換することができます。こうすることでシンプルにパフォーマンスを向上させることができ、大規模なデータセットの検索にかかる時間を大幅に短縮できます。複合キーをハッシュ化すると、基本的にレコードに一意の識別子が作成され、データベースエンジンが瞬時にデータを取得できるようになります。

ハッシュを生成する方法

ハッシュの生成において、入力されたデータは一意の固定サイズのデータに変換されます。また、同じ入力から同じ出力が得られますが入力がわずかでも異なれば大きく異なったデータを得ることができます。

ハッシュの種類

MD5

ハッシュを迅速に生成しますが、高いセキュリティ要件が求められるアプリケーションには推奨されません。
MD5は、32文字の16進数を生成する広く使用されている暗号化ハッシュ関数です。非常に速いですが、脆弱性があり衝突攻撃を受けやすいです。したがって、セキュリティよりも速度が優先される重要ではないアプリケーションによく使われます。

SHA-1

ほとんどの暗号化用途では非推奨ですが、チェックサムでは使われることがあります。
SHA-1は40文字の文字列を作成し、かつてはMD5よりも強力であると考えられていました。一部のチェックサムアプリケーションでは依然として使用されています。

SHA-2

SHA-1やMD5よりも強力で、セキュリティを重視するほとんどのアプリケーションに適しています。
SHA-2は、SHA-224、SHA-256、SHA-384、SHA-512、SHA-512/224、SHA-512/256 などのハッシュ関数で構成されます。以前のバージョンよりも優れたセキュリティを提供し、セキュリティが重要である場面でよく使用されます。

ハッシュ化した文字列にCONCATを使用する

カラムを連結することは、複合キーを作成するための一般的な方法です。CONCAT関数は複数の列の値を1つの文字列に結合し、この結合された文字列をhash関数の入力として利用します。これは、それぞれのカラムの値が一意でない場合でも一意のハッシュを生成してくれるので、単純かつ強力なアプローチとなります。

UPDATE table_name 
SET hash_column = MD5(CONCAT(column1, column2, column3));

GENERATED ALWAYS AS句を使用する

GENERATED ALWAYS AS句は、新しいレコードが挿入または更新されるたびにハッシュを計算してくれます。これにより、hash列が依存するカラムの値が変更されたとしても、常に同期してカラムの値に基づいたhashを生成してくれます。

ALTER TABLE table_name
ADD hash_column CHAR(32) GENERATED ALWAYS AS (MD5(CONCAT(column1, column2))) STORED;

型の選択

MD5の場合には生成されるハッシュのサイズが固定されているため、MySQLがスペース割り当てを最適化するためにVARCHARよりもCHARの方が良いでしょう。

UNHEXおよびバイナリ型を使用した最適化

さらにMySQLのスペースの割り当てを最適化したい場合にはUNHEX関数を用いてバイナリを生成するのが良いでしょう。UNHEXでハッシュをバイナリ形式に変換すると、ストレージをさらに最適化できます。この変換により、ストレージサイズが半分になり、データベースが軽量かつ高速になります。

ALTER TABLE table_name
ADD hash_column BINARY(16) GENERATED ALWAYS AS (UNHEX(MD5(CONCAT(column1, column2)))) STORED;

インデックス用いてクエリする

クエリを実行する場合、UNHEXを使用するとバイナリ化したハッシュ値を直接比較できるためクエリが高速になります。

SELECT * FROM table_name WHERE hash_column = UNHEX(MD5('value1value2'));

まとめ

以上のような過程で複合インデックスを最適化することができます。大規模なデータセットにはかなり有効な手段となてくるのでぜひ試してみてください。