本記事は東京学芸大学 櫨山研究室 Advent Calendar 2020の十二日目の記事になります．
初めて記事を書きます。暖かい目でご覧ください。
はじめに
開発中、処理スピードや効率について考えずに「動いたらヨシ！」とすることが多かったのですが
mapper（SQL文）の修正を大幅にする機会があり、SQL文の良し悪しについて先輩に教えていただきました。
その際の教わったことや感じたことをメモに残します。
ただただSQL文を書いて、動いた〜となっている人に向けた記事です。
概要
前述した通り、SQL文はただ動けばいいと思ってたのですが、
・扱うデータ量が増える
・アクセス数が増える
などでSQL文の良し悪しは運用に大きく影響するとわかりました。
逆に言えば、SQL文を改善することで処理スピードが上がったり効率がよくなったりします。
この記事では、
・SQL文の実行計画を見る方法
・そのSQL文は改善余地があるか
・改善余地がある場合どう改善すべきか
を述べていきます。
SQL文の実行計画
MySQLには実行計画というものがあります。
この実行計画は、自身が今作成しているSQL文の先頭に "EXPLAIN" とつけて実行するだけで見れます。
例えば
SELECT * FROM user_table JOIN book_table ON user_table.id = book_table.user_id WHERE book_table.status = 1;
というSQL文を調査するときは
EXPLAIN SELECT * FROM user_table JOIN purchase_table ON user_table.id = purchase_table.user_id WHERE user_table.purchase_count > 0;
これをMySQL上で実行するとMySQLが以下のような表を出してくれます。
これが実行計画です。
それぞれの項目には意味があるのですが、詳しくは以下の記事を参考にしてください。
https://qiita.com/kzbandai/items/ea02727f4bb539fcedb5
改善可能か？
SQL文はやりたいことが複雑になればなるほど長くなり改善ポイントも多くなります。
ただ、長くてもそのSQL文が最善である可能性もあります。
そこで、自分のSQL文が改善する余地があるのか？を知るためのポイントを紹介します。
項目ごとにチェックする
type - ALLと表示されてるとよくない（全文検索になっている）
key - NULLじゃないほうがいい（クエリが絞れていない）
Extra - using file sortがでているとよくない（diskに書き込んだ後データをソートしている）
単純すぎるSQLだと、クエリの指定をする必要がなかったり
複雑すぎるSQLだと、using file sortがどうしても消せなかったりなどがありますが
基本的には、この項目に当てはまるものがあれば改善は可能です。
改善の仕方
indexの作成
既存の２つのテーブルの中間にもう１個テーブルを作るような感覚です。
例えば、全文検索をするにしても、indexの中で値が降順に並べられていると検索効率が良くなります。
ですが、indexはSQL文を実行するたびに見るので、作りすぎは注意です。
(indexの作成の仕方は検索してください)
クエリの指定
単純に検索対象が減ります。
SQLの実行順序のなるべく先の方で指定すると、その後の処理も楽になります。
これらを試し、SQL文を修正するたびにEXPLAINで実行計画を見てMySQLの負荷を減らしていきます。
終わりに
改善ポイントについても私の知らない部分があるかもしれませんが、
手始めにここをチェックすればいいのかぐらいの気持ちで捉えていただけるとありがたいです。
SQL文は書きっぱなしにせず、実行計画を見てよりいいSQL文に修正していきましょう。（私も気をつけます）
拙い記事ですが、読んでいただきありがとうございました。