記事はMySQL8.0での話です。
ORDER BYの挙動について
SQLが大量のデータを画面に返す時、ボトルネックになりやすいのが「ORDER BY」です。
通常、SQL構文上のORDER BYの評価順は最後で、WHERE句が評価されてからソートを行います。
WHERE句で効果的な絞り込みをすることができず、データのサイズが一定を超えている場合、MySQLはデータをディスク上に展開してソートを行うので、遅くなります。
この時、ExplainでSQLを見ると、Extra欄に「Using Temporary, Using filesort」と表示されると思います。
この解消手段としては、ORDER BYの列にインデックスを貼ること。
そうすると、ORDER BYが構文中で最初に評価されるようになり、かつ、LIMITが効くようになります。
この時、MySQLは、LIMIT件数分、条件に合う行を見つけると、それ以上の探索をしない挙動をします。
以上で終われればいいのですが、これだけでうまくことが運ぶケースは少ないです。
WHERE句条件に注意
上記、「ORDER BY狙い」のインデックスを貼っている時、
WHERE句にカーディナリティが高い（ユニーク性の強い）条件を組み入れると途端に遅くなったりします。
例として埼玉県民テーブルで、年齢にインデックスを貼ってるとします。
SELECT * FROM 埼玉県民
ORDER BY 年齢
LIMIT 10
ここに次のWHERE句を追加してみます。
SELECT * FROM 埼玉県民
WHERE 名前 = "竈門炭治郎"
ORDER BY 年齢
LIMIT 10
名前はカーディナリティの高い列です。
すでに年齢にインデックスを貼っているこの形だと、検索が遅くなると思います。
最初に評価されたOrderByが、名前に一致する行を10件まで見つけるのには、結果的にほとんど全テーブルをなめることになるからです。
（竈門炭治郎は10人もいないと思いますが...）
一方で次のように性別が指定されたとしたら、それはカーディナリティが低い列なので、
MySQLはすぐ条件に合う10件を見つけることができます。
SELECT * FROM 埼玉県民
WHERE 性別 = "男性"
ORDER BY 年齢
LIMIT 10
埼玉県民テーブルからの抽出データに年齢並び替えの機能だけでなく、名前を検索条件に指定できるような機能を実装する際には、
ORDER BYにインデックスを貼るだけでは上手くいきません。
そして実際のアプリケーションの実装では、そんなケースがほとんどかと思われます。
複合INDEXでの対応
MySQLは、基本的には1テーブル1インデックスしか使えない仕様となっています。
（MySQLオプティマイザの気分によっては、インデックスマージしてもらえる場合もあるようです）
そのため、複合INDEXを利用します。前述のケースでは名前, 年齢で作成します。
これだと、最初に名前で絞り込んで年齢で並び替える挙動をしつつ、絞り込んだ結果の中で
10件の該当行を見つけると探索を打ち切るようになります。
現実的な対応
ここに書いた内容は、基本的な対策ですが
複合INDEXを検索条件ごとに増やしていくなどして、長い運用で機能が増えると、画面機能のクエリは
カオスになっていくことも多いのでないでしょうか。
既存のインデックスの存在や、テーブル結合行の多さ等でも
MySQLは狙い通りのインデックスを使ってくれないことがあります。
基本的にはEXPLAINを見ながらインデックスの整理や、
FORCE INDEX指定も検討しつつ、最適な落とし所を見つける作業になると思われます。
どうにもならない時は、チューニングを頑張るより
物理的にデータを減らすこと（パーティション分割、水平分割など）を検討した方が良いかもです。
今回の内容をまとめることで、
同じように大量データのクエリ構築に困っている方の課題解決について
何かヒントになれば幸いです。
Discussion