MySQLでの高速化の基礎①

2019/07/05 2019/07/05 #MySQL #高速化

SQLの高速化の基礎

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

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

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

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

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

EXPLAIN

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

EXPLAIN
    SELECT
        *
    FROM users
    WHERE user_id = 1

typeを確認

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

インデックス

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

単純なインデックス

SELECT
  *
FROM articles
WHERE id = 1

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

範囲を扱うインデックス

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

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

インデックスの順番

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

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

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

  1. user_id
  2. post_datetime

だ。

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

SELECT
  *
FROM memo
WHERE delete_flag = false

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

JOIN

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

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で例を挙げると

// 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という選択肢

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

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

その他

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

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

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

指定された型を意識する

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

最後に

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

のん

名刺 : About me.

YouTube : のんラボ

Twitter : @nonz250

Github : nonz250

Qiita : @nonz250

My Qiita posts My Qiita contributions My Qiita followers

主にPHPを使用し、サーバーサイドを担当。最近はフロントにも興味津々。

なにかを作ったりいじったりするのが好きで、個人開発なども行っている。

趣味はバイクアイコン画像は大抵愛車の「Z250」である。友達にアイコン描いてもらえて嬉しい。

PHP / Laravel / CakePHP2 / CakePHP3 / Vue / Nuxt / C# / etc...

Tags

#のんラボ #Laravel #Vue #個人開発 #ブログ #プログラミング #javascript #Html5 #WEBサービス #Twitter #今年の抱負メーカー #勉強方法 #PWA #モバイルアプリ #Android #ツーリング #バイクに乗るエンジニア #Z250 #秋吉台 #能登半島 #バイク #冒険 #東尋坊 #Squid #リバースプロキシ #hosts #axios #cropper #AdSense #Bootstrap #MySQL #高速化 #トドTask #Telescope #デバッグ #composer #テスト #セキュリティ #POSレジ #スマレジ #本部機能 #バリデーション #入力チェック #Mac #Chrome #テスト駆動開発 #開発手法 #UI #デザイン #WEBサイト #機能美 #PHP #Laravel 6 #コメント #バージョンアップ #vue-cli #localhost #BIツール #売上分析 #TANAX #MFK250 #ツアーシェルケース2 #RESTful #API #REST API #実務的 #PHP Tech Tutor #Smaregi Tech Talk #勉強会 # ブログ #CakePHP3 #CSRF #VSCode #開発環境 #CakePHP3.0 #さくらのレンタルサーバー #モジュールモード #シェル #メール #Gmail #relay #OGP #エラーページ #抱負 #家庭教師 #ドメイン駆動設計 #DDD #読書会 #那智の滝 #伊勢志摩 #伊勢志摩スカイライン #フロント #三方五湖 #レインボーライン #ボーイスカウト・ルール #プログラマが知るべき97のこと #リファクタリング #ユビキタス言語 #車輪の再発明 #マイクロサービス #デプロイ #QA #laravel-mix #Tips #storybook #@storybook/addon-actions #昇降デスク #コードレス #書斎 #オフィス #リモートワーク #働き方 #エラーハンドリング #スマレジ4 #pixel 5 #レビュー #スマレコ #TDD #RSS #404 #高山ダム #ラーツー #React #Nuxt #node_modules #エラー #インポート #設定方法 #環境構築 #Docker #フォレストパーク神野山 #学生向け #PR #採用 #Node.js #npm #しまなみ海道 #youtube #CSS #IE #SLA #Rust #千里浜なぎさドライブウェイ #千里浜 #インサイドセールス #曽爾高原 #無線LAN #ポートフォリオ #バルカンS #納車 #Next.js #チームビルディング #リーダー #悩み #Github Actions #Marp #ネタ #サーバー移行 #S3 #ストレージサーバー #RFC 7807 #Digest #認証 #Xdebug #CLI #西日本 #例外