MySQLでテーブルインデックスを構築する方法

インデックスの概念を理解する最も簡単な方法は、例を挙げることです。次に例を示します。

国籍、都道府県、都市、性別、年齢、目の色など、ユーザー プロファイルに多くの列があるオンライン デート サイトを設計する必要があるとします。サイトは、複数の組み合わせでのユーザー プロファイルの検索をサポートする必要があります。同時に、ユーザーの最近のオンライン時間や他のユーザーのコメントなどに基づいて、並べ替えや限定された結果を返す機能もサポートする必要があります。このような複雑なシナリオのインデックスをどのように設計すればよいでしょうか?

少し奇妙ですが、最初に行うことは、インデックス ソートを使用する必要があるかどうか、または取得後のソートが許容されるかどうかを決定することです。インデックスの順序は、インデックスとクエリの構築方法を制約します。たとえば、WHERE age BETWEEN 18 AND 25 のようなクエリと他のユーザーレビューに基づく並べ替えに同じインデックスを使用することはできません。 MySQL が範囲クエリに 1 つのインデックスを使用する場合、ソートに別のインデックスを使用することはできません。これは最も一般的に使用される WHERE 条件であり、ほとんどのクエリで並べ替えをサポートする必要があると想定します。

複数の種類のフィルタリングをサポート

ここで、どの列の値が分散しているか、またどの列が WHERE 条件で最も頻繁に出現するかを確認する必要があります。分散した値を持つデータ列ではフィルタリングのパフォーマンスが良好です。これは通常、MySQL が無関係な行を効率的にフィルタリングできるようになるため、良いことです。

国籍の列はフィルタリングできないかもしれませんが、最も頻繁に検索される可能性があります。性別の列は通常はフィルタリングできませんが、クエリではよく使用されます。この理解に基づいて、さまざまな列の組み合わせに対して一連のインデックスを作成しました。これらのインデックスは (性別、国) から始まりました。

従来の認識では、フィルタリング特性が低い列にインデックスを構築しても無駄だと考えられていました。では、なぜ各インデックスの先頭にフィルタリングできない列を追加するのでしょうか? これを実行する理由は 2 つあります。最初の理由は、前述したように、ほとんどすべてのクエリで性別が使用されていることです。ユーザーが一度に検索できる性別は 1 つだけになるようにも設計しました。しかし、もっと重要なのは、ちょっとしたトリックを使うので、このような列を追加してもそれほどデメリットがないということです。

ここでの秘訣は、クエリを性別に制限しなくても、WHERE 句に AND sex IN('m', 'f') を追加することでインデックスが有効になることを保証できることです。これにより、必要な行がフィルター処理されないため、WHERE 句に性別を含めないのと同じ効果があります。ただし、MySQL はより多くの列を持つインデックスの先頭にこの列を追加するため、この列を含める必要があります。このトリックはこのシナリオでは機能しますが、列に多くの異なる値がある場合は機能しません。IN() に列が多すぎることになるためです。

この例は、データ テーブルの設計ですべてのオプションを開いたままにするという基本原則を示しています。インデックスを設計するときは、どのインデックスがどのクエリに最適かを考えるだけでなく、クエリの最適化についても考慮してください。インデックスが必要だが、他のクエリがインデックスによって影響を受ける可能性がある場合は、まずクエリを変更できるかどうかを自問する必要があります。解決策を見つけるには、クエリとインデックスの両方を最適化する必要があります。必ずしも完璧なインデックスを設計する必要はありません。

次に、WHERE 条件の他の可能な組み合わせについて考え、それらの組み合わせのうち、適切なインデックスがないと遅くなるものはどれかを検討する必要があります。 (性別、国、年齢) のようなインデックスが当然の選択ですが、(性別、国、地域、年齢) や (性別、国、地域、都市、年齢) のようなインデックスも必要になる場合があります。

この結果、多くのインデックスを作成する必要が生じます。インデックスを再利用できる場合は、組み合わせはそれほど多くありません。 IN() トリックを使用して、(性別、国、年齢) および (性別、国、地域、年齢) インデックスを削除できます。これらの列が検索フォームで指定されていない場合は、国リストと地域リストを使用して、インデックスの先頭の制約が満たされていることを確認できます (すべての国、すべての地域、すべての性別の組み合わせが多数存在する可能性があります)。

これらのインデックスは、ほとんどの検索クエリを満たしますが、アップロードされた写真 (has_pictures)、目の色 (eye_color)、髪の色 (hair_color)、教育レベル (education) などのあまり一般的ではないフィルターについてはどのように設計すればよいでしょうか。これらの列がそれほど選択的ではなく、あまり頻繁に使用されない場合は、それらをスキップして、MySQL にいくつかの追加行をスキャンさせることができます。したがって、これらを age 列の前に追加し、IN() トリックを使用して説明を先頭に追加することで、これらの列が指定されていない場合を処理できます。

インデックスの最後に年齢が記載されていることに気づいたかもしれません。なぜこのコラムが特別扱いされているのでしょうか?私たちは、MySQL がインデックス列を最大限に活用できるように努めています。 MySQL は最初の範囲クエリ条件に遭遇するまで、最も左の一致ルールを使用するためです。これまでに説明したすべての列は、等価条件の WHERE 句で使用できますが、age は範囲クエリになる可能性が最も高くなります。

また、age BETWEEN 18 AND 25 の代わりに age IN(18, 19, 20, 21, 22, 23, 24, 25) などの IN クエリを使用して範囲クエリをリストに変更することもできますが、これは常に可能であるとは限りません。一般的な原則としては、範囲決定条件をインデックスの最後に置くようにして、オプティマイザがインデックスをできるだけ多く使用するようにします。

WHERE 句で指定されていないインデックス条件をカバーするために、必要な数の列を持つ IN クエリを使用できることを説明しました。しかし、やり過ぎると新たな問題を引き起こす可能性があります。このような IN クエリ リストをさらに使用すると、オプティマイザーはより多くの組み合わせを評価することになり、クエリの速度が低下する可能性があります。次のクエリを考えてみましょう。

WHERE eye_color IN('brown', 'blue', 'hazel')
	AND hair_color IN('black', 'red', 'blonde', 'brown')
  	AND 性別 IN('M', 'F')

オプティマイザーはこれを 432 = 24 の組み合わせに変換し、WHERE 条件で各ケースをチェックします。 24 はまだそれほど大きな組み合わせの数ではありませんが、その数が数千に達すると大きな組み合わせの数になります。 MySQL の古いバージョンでは、IN クエリ内の大きな数値で問題が発生する可能性が高くなります。クエリ オプティマイザーの実行速度が遅くなり、メモリ消費量も増加します。新しいバージョンの MySQL では、組み合わせが多すぎると評価が停止しますが、これは MySQL のインデックス使用能力に影響します。

複数の範囲のクエリを避ける

last_online 列があり、過去 1 週間にオンラインだったユーザーを表示する必要があると仮定します。

WHERE eye_color IN('brown', 'blue', 'hazel')
	AND hair_color IN('black', 'red', 'blonde', 'brown')
  	AND 性別 IN('M', 'F')
 	AND last_online > DATE_SUB(NOW(), 間隔 7 日)
 	18歳から25歳まで 

このクエリの問題は、範囲クエリが 2 つあることです。 MySQL では last_online 条件または age 条件のいずれかを使用できますが、両方は使用できません。 last_online 制約が age 制約なしで出現する場合、または last_online が age よりも選択的である場合は、last_online を最後に置く別のインデックス セットを追加する必要があるかもしれません。しかし、年齢を IN クエリに変換できず、last_oinline クエリと年齢範囲クエリの両方がある場合にクエリ速度を改善できるようにしたい場合はどうすればよいでしょうか。現時点では直接的な方法はありません。しかし、範囲を等価比較に変換することができます。これを実行するには、定期的に維持する事前計算済みのアクティブ列を追加します。ユーザーがログインした場合は 1 とマークし、7 日以内に連続してログインしなかった場合は 0 に戻します。

この方法により、MySQL は (active、sex、country、age) などのインデックスを使用できるようになります。この列はそれほど正確ではないかもしれませんが、このタイプのクエリではそれほど高い精度は必要ではないかもしれません。正確な検索が必要な場合は、WHERE 条件に last_online を保持し、インデックスを追加しないでください。この手法は URL 検索の場合と似ています。この条件では、インデックスによってヒットする行を除外する可能性が低いため、インデックスは使用されません。インデックスを追加しても、必ずしもクエリにメリットがあるとは限りません。

ここで、パターンを確認できます。ユーザーがアクティブな結果と非アクティブな結果の両方を検索したい場合は、IN クエリを使用できます。このようなリスト クエリを多数追加しましたが、回避策として、クエリの組み合わせごとに個別のインデックスを作成します。たとえば、(active、sex、country、age)、(active、country、age)、(sex、country、age)、(country、age) のインデックスを作成できます。このようなインデックスは特定のクエリには適した選択肢かもしれませんが、これらの組み合わせを維持することによる悪影響と、組み合わせに必要な追加のストレージ スペースにより、この戦略は弱くなる可能性があります。

これは、オプティマイザーの変更がインデックスの最適化に実際に影響を与える可能性があるケースです。 MySQL の将来のバージョンでインデックス スキャンが本当に削除されれば、インデックスで複数の範囲条件を使用できるようになるかもしれません。その場合、IN クエリでこの問題を解決する必要はなくなります。

並べ替えを最適化する

最後のトピックはソートです。少量のデータの結果は、filesort を使用してすばやくソートできますが、データが数百万行ある場合はどうなるでしょうか?たとえば、WHERE 条件で性別のみが指定されている場合。

このようなフィルタリングの少ないシナリオでは、並べ替え用の特定のインデックスを追加できます。たとえば、(sex, ratings) のインデックスは次のクエリに使用できます。

SELECT <cols> FROM profiles WHERE sex='M' ORDER BY ratings LIMIT 10;

このクエリにはソート句と LIMIT 句の両方が含まれており、インデックスがないと遅くなる可能性があります。インデックスがあっても、UI にページ分割されたクエリがあり、ページ番号が先頭近くにない場合は、このクエリが遅くなる可能性があります。次の例では、ORDER BY と LIMIT の組み合わせが不適切です。

SELECT <cols> FROM profiles WHERE sex='M' ORDER BY ratings LIMIT 100000, 10;

インデックスがあっても、このようなクエリは深刻な問題を引き起こす可能性があります。これは、スキューが大きいと大量のデータが破棄され、スキャンに時間がかかるようになるためです。非正規化、事前計算、キャッシュによって、このようなクエリの問題を解決できる可能性があります。より良い戦略は、ユーザーがクエリできるページを制限することです。実際に検索結果の 10,000 ページを気にする人はいないので、これによってユーザー エクスペリエンスが低下する可能性は低いでしょう。

もう 1 つの優れた戦略は、推論された結合クエリを使用することです。これは、カバー インデックスを使用して主キー列を取得し、データ行を取得する方法です。取得する必要のあるすべての列を組み合わせることができるため、破棄する必要があるデータを収集する MySQL の作業が軽減されます。次に例を示します。

SELECT <cols> FROM profiles INNER JOIN (
  SELECT <主キー列> FROM プロファイル
  x.sex='M' の場合、評価による順序、制限 100000、10
AS x USING(<主キーの列>);

上記は、MySQL がデータ テーブル インデックスを構築する方法の詳細です。MySQL がデータ テーブル インデックスを構築する方法の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。