正しいMySQLキーペアインデックスの選択

最近、私は、1つの属性によって排他的にレコードを検索する主な目的を持っていたMySQLテーブルを作成する作業をしました.かなりシンプルなテーブルではあるかもしれませんが、パフォーマンスを優先するためにインデックスを導入する最善の方法に疑問を持っていました.私はこれがおそらくNOSQLエンジンの上で走るのであるかもしれないのを知っています、しかし、これはこの記事が何であるかでありません.
私は2つの属性を関連付ける必要があることを知っていましたが、より良いものの疑問にありました:SQLのキーペアインデックスかハッシュアルゴリズム(MD 5)を使用して?MySQLは似たようなハッシュアルゴリズムを使用していたのでしょうか、それともMD 5よりも速いのでしょうか?私はまわりでgoogledしました、しかし、私が明らかな答えを見つけなかったことを本当に驚いていました(あるいは、多分、私はgooglingで吸います).
私は、私はこのようにこの記事の目的は、あまりにもいくつかのパフォーマンス結果を表示し、いくつかの結論を得るようにスクリプトを私に答えを与えるベンチマークが必要です.比較目的のために、各属性に個別に割り当てられたインデックスを持つテーブルのパフォーマンスをチェックしたかった.合計でベンチマークに3つのテーブルがあります.
テーブル個別インデックス

CREATE TABLE `no_indexes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `attribute_a` varchar(255) NOT NULL,
  `attribute_b` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `attribute_a` (`attribute_a`),
  KEY `attribute_b` (`attribute_b`)
) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

表key pairインデックス

CREATE TABLE `keypair_index` (
  `attribute_a` varchar(255) NOT NULL,
  `attribute_b` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  KEY `keypair_index_index` (`attribute_a`,`attribute_b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

表ハッシュ指数

CREATE TABLE `hash_index` (
  `hash` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `attribute_a` varchar(255) NOT NULL,
  `attribute_b` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL
  KEY `hash_index_hash_index` (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

ベンチマーク用のスクリプトはPHP 7.2で書かれました.最初のテストは、各テーブルのレコードの同じ数を作成し、ランダムに生成された値を持つフィールドを占有し、それであることattribute_a 4つの可能な異なる値attribute_b すべての明確な値を持っていた.
注意してくださいhash_index 作成されたレコードごとに、ハッシュ(MD 5アルゴリズムで)を計算する必要がありますattribute_a and attribute_b . このハッシュをPHPまたはMySQLで生成できます.
テストは、結果が一貫していることを確認するために複数回実行されました.
複数のインサートを実行する時間(ベンチマークの時間)のベンチマーク結果

非常に速い最初の観察で、それはそれのようですhash_index テーブルにレコードを挿入する際に最悪のパフォーマンスがあります.しかし、実際には、最高のパフォーマンスを提供する場合は、テーブルの上に50 K以上のレコードを持って計画している!これは、それが他のテーブルよりも良いスケールを意味する最小のスケーリング因子(下位より良い)を持っているためですindividual_indexes 表.The keypair_index テーブルは10 Kレコードを挿入するときにOKに見え始めたが、50 K以上のレコードが存在する場合、それは他のテーブルよりもずっと遅くなることが明らかになります.あなたがスケーリングの多くの記録と計画を計画するならば、ここの勝者は確かにhash_index 表.
次に、私は検索の読み取り速度をテストしたいと思いました.スクリプトは、レコードの作成と同じ方法で検索を試みますattribute_a and attribute_b , MySQLキャッシュを無効にする(これは常に更新されている生産テーブルを模倣することです).注意してくださいhash_index テーブルのレコードを識別するものであるため、テーブルはすべての読み取り(選択)操作のMD 5ハッシュを計算する必要があります.
各テーブルでの10 k探索を行う時間のベンチマーク結果(milesecondsの時間):

今ではあらゆるレベルでhash_index テーブルは、最高のパフォーマンスとスケーリングを提供しています.再び、1.05(!)の最も低いスケーリング率(より低いほうがよい)これはB - tree構造の性質によってレコードの量がわずかに増加することに注意してください.私は、この議論に他のテーブルを持ってくる必要さえないと思います.
結論:ハッシュインデックスは、多くのルックアップを実行するテーブル、特に2つ以上の属性を組み合わせたテーブルが欲しいならば、キーペアのMySQLインデックスよりもずっと速く動作します.
あなたが非常に少量の検索操作を実行するログテーブルのような何かを計画するならば、キー対インデックスを使用する価値があるかもしれません、そして、記録の少ない量を持つように計画されていますが、長い目で見れば、私はいつもハッシュインデックスで行きます.