はじめに
Ateam cyma Adevent Calendar 2019 の 5日目です。
本日は、株式会社エイチームEC事業本部cymaのエンジニア@ihsiekが担当します。
普段、私はcymaのバックヤード業務で使うシステムを見ていますが、もともとインフラエンジニアという役割をもらっていたこともあり、インフラ周りのメンテナンスや問い合わせがあると対応に入ったりもしています。
前提
- 対象読者
- ORMを使わずにRDBMSに触る機会のあるSQL初心者エンジニアの方
- データ分析のためにSQLを書くことが増えたエンジニア以外の方
- おことわり
- DB製品ごとに設計思想が違い、網羅的に詳細を説明することはしません。抽象化した一般論がメインになるため、本稿を索引としてお使いのDB製品での方法を模索していただくことを想定しています。
- ビッグデータを扱うことを前提とした製品では、一部当てはまらない情報がありますのでご留意ください。
はじめに
昨今、高機能なORMが一般的になり、プログラム中にSQLを埋め込むことは減ってきました。
一方で、Redashなどの便利なダッシュボードツールでエンジニア以外でSQLを書く人も増えているのではないでしょうか。
BigQueryやAmazon Redshiftのように高性能なDB製品が増えたことで、SQL自体をチューニングしたことがないという方もおられると思います。
それ自体は悪いことではありませんが、パフォーマンスの良いSQLを意識することで、より快適なSQLライフを手に入れてみませんか？
SQLのパフォーマンスが上がると何がいいのか？
-
SQLの実行が速くなる
欲しい結果が返ってくるだけのSQLは比較的簡単に書くことができますが、チューニングすることで実行時間が10分の1になるといった事例もあります。
実際に、10年くらい前に関わっていた仕事の事例で、10分以上掛かっていたSQLを1分弱まで改善したことがあります。
情報は生モノなので、即時性の高い情報が得られるに越したことはありませんよね？ -
サーバの負荷が軽くなる
多くの人がSQLを実行するようになると、その分、サーバに負荷がかかるようになります。
サーバに負荷がかかるとリソースを奪い合って処理が重くなったり、サーバ自体の維持にかかるコストが増えることがあります。
要件にあったサーバの選定・調達はインフラエンジニアの仕事なので、普段は気にしない人も多いと思いますが、パフォーマンスの高いSQLを書くことで余計なお金を使わずに済む、と考えて意識してもらえるとインフラエンジニアとしては非常に助かります
チューニング手法
実行計画を確認する
実行計画とは、オプティマイザ(DBに対する問い合わせを最適化する機能のことで、DB製品によって動きが異なります)がSQLを解析した結果として出力されるどの順番でテーブルにアクセスし、どの条件でデータを抽出し、どのような結合方法で結果を結合するかのを示すものです。
実行したいSQLの直前にEXPLAINというキーワードを書いてSQLを実行することで確認できます。
具体的には以下のように書きます。
EXPLAIN SELECT * FROM users WHERE id = 1;
実行計画の読み方はDB製品によって異なるのでここでは説明しませんが、「実行計画の読み方」などで検索するといろいろ情報に行き当たると思いますので、詳細は割愛します。
クエリキャッシュを利用する
多くのDB製品ではクエリキャッシュという機構を備えています。(MySQL 8.0 : クエリーキャッシュのサポート終了という話もあるので、すべての製品が対象ではありません)
クエリキャッシュとは、過去に実行されたSQLの実行計画または実行結果をキャッシュしておいて、同一のSQLが実行されたときにキャッシュを使ってレスポンスを高速化させる機構です。
キャッシュされるのが、実行計画なのか実行結果なのかは、DB製品によって異なるので気になる方はお使いのDB製品のクエリキャッシュについて調べてみてください。
一般的にクエリキャッシュを有効にするためにはバイト単位で文字を一致させる必要があります。
一桁ミリ秒での応答を期待されるDB製品において、キャッシュ利用のために空白や改行を取り除く処理を挟むのが非効率だからと推測されます。
SQLのコード規約を設ける
ということで、クエリキャッシュが使われるSQLを作成するために、以下のようなコード規約を設けることをオススメします。
-
大文字、小文字を統一する
- 以下のようなルールを設定すると人によって迷わないと思います。
- 大文字: 予約語(SELECT,WHERE,BETWEENなど)
- 小文字: ユーザー定義語(テーブル名, カラム名など)
- 以下のようなルールを設定すると人によって迷わないと思います。
-
インデントを揃える
具体的に配下のような書き方です。
SELECT users.id
, users.name
FROM users
WHERE users.id = 1
;
INNER JOIN句のような複数の語句から成り立つものはどこで改行するのがいいか、人によって好き嫌いが分かれそうですね
バインド変数を使う
実行計画をキャッシュするDB製品でのみ有効な手法ですが、変数部分をプレースホルダーにするバインド変数を使うと変数だけが変わっても同一のSQLとみなしてキャッシュされた実行計画を再利用してくれます。
Oracleであれば以下のようになると思います。(ここ数年Oracleを触っていないのでうろ覚えですが。。。)
VARIABLE user_id NUMBER;
SELECT users.id
, users.name
FROM users
WHERE users.id = :user_id
;
インデックスを利用する
インデックスは名前の通り、書籍の索引のようなもので、欲しい情報を探す手助けをしてくれます。
例えば、usersテーブルから20歳以上40歳未満のユーザーを抽出したい場合、インデックスなしだとusers.age >= 20 AND users.age < 40の条件に合致するレコードを1件ずつチェックすることになります。
100件～1,000件程度のレコード数であれば、そこまで気にしなくても良いのですが、数万件を超えるレコード数に対して1件ずつチェックすることの非効率さはイメージいただけるのではないでしょうか。
インデックスありの場合は、先ほどの条件に合致するレコードにアクセスするための情報がインデックスとして記録されているため、対象のデータに直接アクセスすることができます。書籍の索引を引いて目的のページを直接開くようなイメージです。
ここで述べたとおり、インデックスを使うと目的のデータに効率よくアクセスできるのですが、インデックスが正しく使われていなかったり、正しく貼られていなかったりすると、インデックスを貼ったのにSQLが重いというケースもあり得るので使い方を説明していきます。
インデックスが使われているかを確認する
記事の冒頭で紹介した実行計画を確認したときに、インデックスが使われていない行があれば、インデックスを使用できる可能性があります。
後述で触れますが、すべてのケースでインデックスが使われていることが最適な状態ではないので、注意してください。
インデックスが使われるクエリを書く
参照するテーブルに貼られたインデックスを確認して、インデックスに含まれる項目でデータを絞り込めないか検討します。
インデックスが貼られたカラムをWHERE句で指定する
インデックスが貼られたカラムをWHERE句で指定するだけで、基本的にはインデックスが使用されます。
例えば、usersテーブルのage(年齢)にインデックスが貼られていた場合、以下のようなSQLを実行することでインデックスが使用されます。
SELECT * FROM users WHERE users.age = 20;
オプティマイザの判断によってはインデックスが使われないケースもありますが、どのようなケースで使われないのかはDB製品によって異なるので、ここでは取り扱わないこととします。
ヒント句を使用する
実行計画はオプティマイザが統計情報などのデータの状態を見て生成するため、最近のオプティマイザがおかしな条件を指定するケースはそこまで多くなく、ヒント句を使ったチューニングの優先度は低めです。
それでも、人が見てなぜその順で参照してしまうのか・・・となることはあるので、ヒント句という構文の存在を知っておくことは大切です。
ヒント句を使用すると、使用してほしいインデックスをオプティマイザに指示することができます。
注意事項
-
ヒント句の乱用
前述の通りオプティマイザの判断が間違っている場合にのみ使うようにしてください。 -
複合インデックス(複数のカラムを指定したインデックス)について
複合インデックスは、指定されたカラムの評価順を意識する必要があります。
例えば、usersテーブルにzipcode, address1, address2の順番で貼られた複合インデックスがあったとして、address1, address2の条件しか指定しなかった場合、一般的に複合インデックスは使用されません。
これは、インデックスに使用されているB-Treeというアルゴリズムの仕様に基づくものです。
うまく行かないとき
インデックスが貼られているカラムが適切なのかを確認する
インデックスが有効に機能するのはCardinalityが高い場合です。
Cardinalityはデータの種類を表す指標で、性別はCardinalityが低く(Facebookの例を見ても58種類程度)、日時のような情報はCardinalityが高いと言えます。
また、Cardinalityが十分に高くてもSelectivityが高い(データの偏りが大きい)と機能しない場合があります。
おわりに
Ateam cyma Adevent Calendar 2019 の 5日目、いかがでしたか。
色々と説明不足な点もありそうなので、公開後にでもアップデートを重ねていけたらと考えています。
株式会社エイチームでは、一緒に働けるチャレンジ精神旺盛な仲間を募集しています。
エンジニアとしての働き方に興味を持たれた方はcymaのQiita Jobsをご覧ください。
そのほかの職種は、エイチームグループ採用サイトをご覧ください。