明けましておめでとうございます。今年もよろしくお願いいたします。
11月の事ですが、SQLパフォーマンス詳解を読みました。
感想などを書いていきたいと思います。
読んでみようと思ったきっかけ
かなり多くの回数呼ばれるプログラムのデバッグや後輩のコードレビューをする中でパフォーマンスを意識したSQLの知識が皆無に等しくこのままでは良くないと思ったため。また先輩から勧められたため。
感想
インデックスをどう活用するかを書いてくれててわかりやすかった。どうすればパフォーマンスが良くなるのかということでググると、「適切なインデックス」を張るということをよく目にするがいつも「どうすれば適切になるんだ」と思っていた。張り過ぎもよくないと聞くので「過ぎ」ってどの程度のことなんだろうと思ったりしていた。インデックスは作成されているけど、sqlの書き方のせいで使えていないとかもある。そういうものがわかってきたので読んで良かったと思います。MySQLのExtra列については今後しっかり勉強したい。
以下まとめ(MySQLのみ) 第４章、５章は省略
第１章 インデックスの内部構造
インデックスの最も重要な目的は、インデックスを張ったデータに対して順序をつけてアクセスできるようにすることです。
writeの処理(insert, update, delete)が走る際に、大量のデータを動かすわけにはいかない。そういう場合の解決策として双方向連結リストでインデックスリーフノードと対応するテーブルのデータを対応させている。
検索ツリー(Bツリー)
データベースがリーフノードを高速に見つけるのに役に立つ。MySQLの場合は、BTREEと表記されている。Bはバランスの略でその名の通り木構造。1~1000というidが入ったノードと1001~2000、2001~3000といったノードがあって20xxというidを検索するとき、1から順に検索しなくて良いので速くなる。1001以上かどうか、2001以上かどうかなどを調べて該当のノードの中を確認する。 ツリーが深さは対数的に増え、リーフノードの増加に対してツリーの深さの増大は非常に遅い。
インデックスによる検索は、以下の３ステップで行われる。
- ツリーを走査
- リーフノードチェーンをたどる
- テーブルからデータを読み出す
UNIQUEなSCANは１しか行わないため高速。
第２章 WHERE句
前提としてSQLを発行する際には実行計画(EXPLAIN)は必ず行う。 MySQLにおける実行計画で最低限注意して見るべき点は下の記事を参考にしています。
https://nippondanji.blogspot.com/2009/03/mysqlexplain.html
複合インデックス定義する際に考えるべき最も重要なのは、そのインデックスを使えるSQL文ができるだけ多くなるように、列の順番を決めることです。
a, b, c, dという4つの列の複合インデックスがあった場合は、a,bを条件に検索するときも使えるので先に定義しておく。cを条件にすることが多い場合は、c, a, b, dなどの順で定義するといい。こうすることで新たなインデックスの作成を減らせる。インデックスの数が多いと更新処理のパフォーマンスに影響する。
WHERE句はインデックスの列の順に合わせて使う。 OR検索やWHERE INなどは場合によっては実行計画が変わるので出来るだけUNIONやJOINをうまく使って出来るだけ使わないようにしたほうがいいかもしれない。データ数が少ないテーブルの場合はそんなに気にしなくていいとは思うが。
パラメータ化クエリはちゃんと使う。SQLインジェクションを防ぐために。残念ながらMySQLはされていないが、SQLによってはパラメータ化クエリを使うことでキャッシュされたSQLが使える。
・範囲検索(大なり、小なり、BETWEEN) なるべく先に狭い範囲になるように検索してから残りを検索する。
・LIKE ワイルドカード(%)を先頭に出来るだけ使わない。 使ったことなかったけどmatchやagainstを使えるなら使う。
カラムはしっかり指定して＊で取得するのはやめる。
インデックスは2つ使うより、1つだけ使う方が高速
第３章 パフォーマンスとスケーラビリティ
データ量が多くなるにつれパフォーマンスは悪くなる。
注意深い実行計画の調査結果は、うわべだけのベンチマークよりも信用のおけるものです。完全な負荷テストは意味のあることですが、そのための手間はかかります。
本番のデータとテスト時のデータ、開発時のデータは全く違うものだと思うし、似たようなテストをするにしても大変な手間がかかる。大量データの場合は特に、適切なインデックスが張られているかを注意する。ただそれだけデータがあるということはそれだけ更新処理があると思うので張りすぎにも注意する。
リレーショナルSQLデータベースかリレーショナルでないシステムに関わらず、正しいインデックスを作ることは、クエリの応答時間を短くする唯一の方法です。
第６章 ソートとグルーピング
order by句がインデックスによる順序付けと一致している場合、 データベースは明示的なソート処理を省略できます。
order by句でASK, DESCが混在している場合はインデックスが効かない。ASC,ASCやDESC,DESCだったら効く。混在させる場合はインデックスを定義する必要がある。
第７章 部分的結果
・LIMIT
データベースは、部分結果のみを取得することを事前に知っている場合のみ、部分結果のみ、部分結果向けにクエリを最適化できます。
実際に事前に知れているかどうかは実行計画を確認する。ただMySQLの場合は、Extra列に「using filesort」がなければ部分結果の取得が行われている。
・ページングについて
ページングの際は、並べ替えの順序は確定的である必要があります。
ページングの際はoffsetを使うのが一般的だがそのテーブルにリアルタイムで行が挿入される場合は、挿入されたデータが最初に来る場合があるので、ずれてしまう。
第８章 挿入、削除、更新
インデックスが作られるとデータベースはインデックスを自動的にメンテナンスする。 簡単にいうとinsertするとインデックスのノードも更新するため処理が重くなる。 インデックスを張りすぎると良くないのはこのため。insertが圧倒的に多いテーブルにはインデックスは出来るだけ少なくする必要がある。
インデックスは注意深くかつ慎重に使い、かつ、可能な限り冗長なインデックスは使わないようにしましょう。これは、delete文やupdate文を使う際にも同じ事が言えます。
deleteやupdateにも実行計画はあります。