MySQLでの高速化の基礎①

2019/07/05 2019/07/05

SQLの高速化の基礎

こんにちは。Nonです。今回はありそうでなかったSQLの高速化の基礎について記載します。

実は今まで触れる機会が少なかった

個人的にSQLが遅くて困ったことが無い（規模の小さい商品や、個人開発などのみかかわってきたもので・・・）ので、知らないこと多かったんですよね。

僕の経験で言えば、多くても５万くらいのデータしか扱ってきませんでした。
実はもっとデータの量もあったかもしれないけど、それは先人たちがすでに高速化していたものを使いまわしていただけだったと思います。

最近、データの量が多い画面を担当することになって、この知識があるかないかで、ユーザーへの便利度が決まるということに気づきました。
なので、備忘録ついでに書いておくことにしました。

EXPLAIN

高速化といえばまずこれ。インデックスが適切に使用されているかなど、MySQLがどのように実行するかの情報を見ることができます。

copied.EXPLAIN
    SELECT
        *
    FROM users
    WHERE user_id = 1

typeを確認

type	内容
const	PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。最速。
eq_ref	JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。constと似ているがJOINで用いられるところが違う。
ref	ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセスタイプ。
range	インデックスを用いた範囲検索。
index	フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。
ALL	フルテーブルスキャン。インデックスがまったく利用されていないことを示す。OLTP系の処理では改善必須。

インデックス

高速化といえばまずこれ。
簡単に「インデックス」といいますが、結構扱いに困るイメージです。
知っているようで知らない感じに陥りそうな困ったちゃんです。

単純なインデックス

copied.SELECT
  *
FROM articles
WHERE id = 1

idは当然PRIMARY_KEYでAUTO_INCREMENTなので、検索が早いです。
イメージとしては辞書の「あ/か/さ/た/な/は/ま/や/ら/わ」の索引のイメージ。

範囲を扱うインデックス

copied.SELECT
  *
FROM articles
WHERE post_datetime BETWEEN '2019-04-01' AND '2019-05-01'
AND user_id = 1

このときはpost_datetimeとuser_idに複合インデックスを作成しておくと早くなる。
でもこの例では一つだめなところがある。

インデックスの順番

post_datetimeで検索するよりも先にuser_idで検索される箇所から検索したほうが早い。

copied.SELECT
  *
FROM articles
WHERE user_id = 1
AND post_datetime BETWEEN '2019-04-01' AND '2019-05-01'

正しくはこう。
post_datetimeはrange検索なので、あとから検索したほうが早くなる。
インデックスを貼る順番にも注意。

user_id
post_datetime

だ。

インデックスを使わない選択肢

copied.SELECT
  *
FROM memo
WHERE delete_flag = false

メモの削除件数０件。
このとき、インデックスがあろうがなかろうがフルスキャンである。
こういったインデックスの恩恵を受けられないカラムにはインデックスを貼らない方がいい。
インデックスを貼ると、INSERTの負荷が重くなるからだ。

JOIN

JOINするときはできるだけ絞れ。
JOINはループ
下記は例。（いい例が思いつかずすみません。）

copied.SELECT
  *
FROM articles
  LEFT JOIN comments
  ON comments.articles_id = articles.id
  AND comments.articles_id = 1
WHERE articles.id = 1

JOINする対象テーブルは予め小さくしておく。
AND comments.articles_id = 1の箇所がそうです。
JOINをするときMySQLは内部的にループを回して条件を絞り込む。
例えば、ON comments.articles_id = articles.idの箇所、あっているものをループで検索しだす。ということは予めループされるものを小さくしておけば、検索までにかかる時間は短くなる。

phpで例を挙げると

copied.// join処理①
for ($i = 0; $i < 1000; $i++) {
    for ($j = 0; $j < 1000; $j++) {
    }
}

// join処理②
for ($i = 0; $i < 100; $i++) {
    for ($j = 0; $j < 100; $j++) {
    }
}

①より②の方が早いに決まっている。

EXISTSという選択肢

copied.SELECT
  *
FROM articles
WHERE EXSITS (
    SELECT
        1
    FROM comments
    WHERE comments.articles_id = articles.id
)

EXISTSは存在を確認するのみなので、早い。デメリットとして、EXSITSで書いたテーブル情報を参照することはできない。例ではコメントの有無はわかるが、コメントの内容を参照できない。

その他

数件程度ならパフォーマンス的にも問題が無いが件数が増えていくほどに効果が増していく系。

ちゃんとバッククォート「`」を記載する。

copied.SELECT
    *
FROM `articles`
WHERE `articles`.`id` = 1

指定された型を意識する

copied.SELECT
    *
FROM `users`
WHERE `name` = '007' /* 007という名前でも検索は文字型で */

最後に

疲れた・・・
いざ書き出したらキリがないですね。これ。
記事をあとから更新するか、分割するかします。お疲れ様でした。
また更新しますのでタグ検索など利用して見てください。