はるかに優れた戦略（ 以下、翻訳者と呼びます：通常、唯一の正しい方法 ）は、フォーム（AGE、STATE）の結合インデックスを作成することです。 なぜそうなのか見てみましょう。
通常（常にではありませんが）MySQLのインデックスはBTREEインデックスです。このタイプのインデックスは、プレフィックスに含まれる情報をすばやく検索し、ソートされた値の範囲を反復処理できます。 たとえば、AGE列にBTREEインデックスを指定してAGE = 18を要求すると、MySQLはテーブル内のクエリに一致する最初の行を検索し、最初の不適切な行が見つかるまで検索を続けます。 それ以上は何も起こらないと信じています。 BETWEEN 18 AND 20の形式のクエリなどの範囲は同様に機能します。MySQLは他の値で停止します。
AGE IN（18,20,30）のようなクエリの状況は、やや複雑です。 実際、MySQLはインデックスを数回調べる必要があります。
そこで、MySQLがインデックスで検索する方法について説明しましたが、検索後に返されるものを決定しませんでした-通常（インデックスをカバーする問題でない場合）、主キーの値である「行ポインタ」を取得します（InnoDBエンジンが使用される場合） ）、ファイル内の物理オフセット（MyISAM用）またはそのようなもの。 内部MySQLエンジンがこのポインターを使用して、指定されたインデックス値に対応するすべての必要なデータを含む完全な文字列を検索できることが重要です。
また、2つの個別のインデックスを作成した場合のMySQLのオプションは何ですか？ そのうちの1つだけを使用して適切な行を選択し（そして抽出されたデータをWHEREによってガイドされますが、 インデックスを使用せずに ）、またはすべての適切なインデックスから行へのポインターを取得し、その交差を計算してからデータを返します。
どの方法がより適切かは、インデックスの選択性と相関関係によって決まります。 最初の列でWHEREを処理した後、行の5％が選択され、2番目の列でWHEREを使用して行を合計の1％にフィルター処理する場合、もちろん交差を使用するのが理にかなっています。 ただし、2番目のWHEREが最大4.5％のみを除外する場合、通常、最初のインデックスのみを使用し、データ抽出後に不要な行を除外する方がはるかに有益です。
いくつかの例を見てみましょう。
CREATE TABLE `idxtest` (
`i1` int(10) UNSIGNED NOT NULL,
`i2` int(10) UNSIGNED NOT NULL,
`val` varchar(40) DEFAULT NULL,
KEY `i1` (`i1`),
KEY `i2` (`i2`),
KEY `combined` (`i1`,`i2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
列i1とi2を互いに独立させ、それぞれがテーブル内の行の約1％を選択します。これには合計1,000万件のレコードが含まれています。
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest WHERE i1=50 AND i2=50;
+----+-------------+---------+------+----------------+----------+---------+-------------+------+-------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+----------------+----------+---------+-------------+------+-------+
| 1 | SIMPLE | idxtest | ref | i1,i2,combined | combined | 8 | const,const | 665 |
+----+-------------+---------+------+----------------+----------+---------+-------------+------+-------+
1 row IN SET (0.00 sec)
ご覧のとおり、MySQLは結合インデックスを使用することを好み、リクエストは10ミリ秒未満で完了しました。
ここで、個々の列にのみインデックスがあると仮定します（オプティマイザに結合されたインデックスを無視するように伝えます）：
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX (combined) WHERE i1=50 AND i2=50;
+----+-------------+---------+-------------+---------------+-------+---------+------+------+-------------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+-------------+---------------+-------+---------+------+------+-------------------------------------+
| 1 | SIMPLE | idxtest | index_merge | i1,i2 | i1,i2 | 4,4 | NULL | 1032 | USING intersect(i1,i2); USING WHERE
+----+-------------+---------+-------------+---------------+-------+---------+------+------+-------------------------------------+
1 row IN SET (0.00 sec)
この例でわかるように、MySQLはインデックスの交差の検索を実行し、クエリを完了するのに70ミリ秒かかりました-7倍長くなりました！
次に、1つのインデックスのみを使用して、受信したデータをフィルター処理した場合の結果を見てみましょう。
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX (combined,i2) WHERE i1=50 AND i2=50;
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxtest | ref | i1 | i1 | 4 | const | 106222 | USING WHERE
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
1 row IN SET (0.00 sec)
今回、MySQLはさらに多くの行をバイパスする必要があり、クエリには290ミリ秒かかりました。 したがって、インデックスの共通部分を使用する方が単一のインデックスを使用するよりもはるかに優れていますが、結合インデックスを使用する方がはるかに優れていることがわかります。
ただし、インデックスの交差に関する問題はこれで終わりではありません。 現在、MySQLでこの手順を使用する可能性は非常に限られているため、MySQLは常にそれらを使用するとは限りません。
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX (combined) WHERE i1=50 AND i2 IN (49,50);
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxtest | ref | i1,i2 | i1 | 4 | const | 106222 | USING WHERE
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
1 row IN SET (0.00 sec)
列の1つでのクエリが比較ではなく列挙になるとすぐに、MySQLはインデックスの共通部分を使用できなくなります。この場合、 i2 IN（49.50）をクエリする場合、これは合理的であるためです。 。 要求は非常に選択的です。
それでは、別のテストを行いましょう。 テーブルをクリアし、i1とi2の値が強く相関するようにデータを再入力しました。 実際、それらは現在、一般的に同等です：
mysql [localhost] {msandbox} (test)> UPDATE idxtest SET i2=i1;
Query OK, 10900996 rows affected (6 min 47.87 sec)
Rows matched: 11010048 Changed: 10900996 Warnings: 0
この場合に何が起こるか見てみましょう：
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest WHERE i1=50 AND i2=50;
+----+-------------+---------+-------------+----------------+-------+---------+------+------+-------------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+-------------+----------------+-------+---------+------+------+-------------------------------------+
| 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i2,i1 | 4,4 | NULL | 959 | USING intersect(i2,i1); USING WHERE
+----+-------------+---------+-------------+----------------+-------+---------+------+------+-------------------------------------+
1 row IN SET (0.00 sec)
オプティマイザーはインデックスの共通部分を使用することにしましたが、これはおそらく最悪の解決策でした！ クエリの完了には360ミリ秒かかりました 。 また、おおよその行数を推定する際の大きな誤差にも注意してください。
これは、MySQLが列i1およびi2の値を独立して考慮し、したがってインデックスの共通部分を選択するために発生しました。 実際、彼は別のものを提案することはできません。 列の値の相関関係に関する統計情報はありません。
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX(i2) WHERE i1=50 AND i2=50;
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxtest | ref | i1,combined | i1 | 4 | const | 106222 | USING WHERE
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
1 row IN SET (0.00 sec)
そして今、MySQLがi2カラムのインデックスを使用することを禁じたとき（つまり、インデックスの共通部分を見つけることができない）、結合されたカラムではなく、1つのカラムのインデックスを使用します。 これは、MySQLが一致する行のおおよその数に関する統計情報を持っているためであり、両方のインデックスで等しいため、MySQLは小さい方を選択しました。 リクエストの実行には再び290ミリ秒かかりました-前回とまったく同じです。
MySQLで結合インデックスのみを使用するようにします。
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX(i1,i2) WHERE i1=50 AND i2=50;
+----+-------------+---------+------+---------------+----------+---------+-------------+--------+-------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+----------+---------+-------------+--------+-------+
| 1 | SIMPLE | idxtest | ref | combined | combined | 8 | const,const | 121137 |
+----+-------------+---------+------+---------------+----------+---------+-------------+--------+-------+
1 row IN SET (0.00 sec)
MySQLは、検索対象の行数の推定で約20％間違っていることがわかります。もちろん、これは間違っています。 列i1でのみインデックスを使用する場合と同じプレフィックスが使用されます。 MySQLはこれを知りません。なぜなら、 個々のインデックスの統計を見て、それらの調整を試みません。
使用される結合インデックスが1つの列のインデックスよりも大きいため、クエリの完了には300ミリ秒かかりました 。
したがって、MySQLは、これが最悪のオプションであっても、インデックスの共通部分を使用することを決定できることがわかります。ただし、技術的な観点からは、他の統計がないため、もちろんより良い計画になります。
MySQLでインデックスの共通部分を使用しないようにする簡単な方法がありますが、残念ながら、このオプションが最適ではないと見なした場合に強制的に共通部分を使用する方法はわかりません。 この機能が将来追加されることを願っています。
最後に、インデックスの共通部分を見つける手順が、複数の列にわたるインデックスを結合するよりもはるかにうまく機能する状況を見てみましょう。 これは、列間でフェッチするときにORを使用する場合です。 この場合、結合インデックスは完全に役に立たなくなり、MySQLには、1つのテーブルから受信したデータの共通部分を検索する代わりに、テーブル全体のスキャン（FULL SCAN）と値のユニオン（UNION）の実行を選択できます。
列i1とi2の割り当てを再度変更して、独立したデータが含まれるようにしました（テーブルの典型的な状況）。
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest WHERE i1=50 OR i2=50;
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+---------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+---------------------------------+
| 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i1,i2 | 4,4 | NULL | 203803 | USING union(i1,i2); USING WHERE
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+---------------------------------+
1 row IN SET (0.00 sec)
このような要求は660ミリ秒実行されます。 2列目のインデックスを無効にすると、フルスキャンが取得されます。
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX(i2) WHERE i1=50 OR i2=50;
+----+-------------+---------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | idxtest | ALL | i1,combined | NULL | NULL | NULL | 11010048 | USING WHERE
+----+-------------+---------+------+---------------+------+---------+------+----------+-------------+
1 row IN SET (0.00 sec)
MySQLはキーi1を使用可能な限り組み合わせて示しましたが、実際にはそのようなオプションはありません 。 このようなリクエストの実行には3370ミリ秒かかります。
また、FULL SCANが約50倍の行を通過したにもかかわらず、クエリの完了に5倍の時間がかかったことに注意してください。 これは、メモリ内で実行されるという事実にもかかわらず、テーブルのフルパスとキーアクセスのパフォーマンスに非常に大きな差があり、行ごとのアクセスの「コスト」の意味で10倍の時間がかかります。
UNIONの場合、オプティマイザーはより高度で、範囲を処理する能力が非常に高くなります。
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest WHERE i1=50 OR i2 IN (49,50);
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+--------------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+--------------------------------------+
| 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i1,i2 | 4,4 | NULL | 299364 | USING sort_union(i1,i2); USING WHERE
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+--------------------------------------+
1 row IN SET (0.00 sec)
まとめると
ほとんどの場合、WHEREの同様の列でANDを使用する場合、複数の列で結合インデックスを使用するのが最適なソリューションです。 インデックスの共通部分を使用すると、基本的にパフォーマンスが向上しますが、結合キーを使用する場合よりも大幅に低下します。 列間でORを使用する場合、MySQLが交差を見つけることができるように各列にインデックスが必要であり、そのようなクエリでは結合インデックスを使用できません。