イノベーション エンジニアブログ


株式会社イノベーションのエンジニアたちの技術系ブログです。ITトレンド・List Finderの開発をベースに、業務外での技術研究などもブログとして発信していってます!


このエントリーをはてなブックマークに追加

改めてデータベースについて考えてみる

こんにちは。はすみんです。

今回のブログでは、SQLやチューニングについて最近勉強したことをアウトプットしてみようと思います。

SQLでデータを引っ張ってくるまでの工程

  1. アプリケーション側でSQLを実行

  2. パーサ/リライタがSQLの構文を解析し、構文木を生成

  3. プランナ/オプティマイザが、最適な実行計画を作成する。

  4. エグゼキュータが、実行計画に沿ってクエリを実行し、データを取得する

  5. 処理結果をアプリケーション側に返す

クエリの結果を取得するまでにこういった一連の処理があります。
大体処理が遅くなるのはエグゼキュータ部分で、
最適な実行計画が作られていないことが遅い原因となっていることが多いようです。

遅い=RDBMSが苦手な実行計画は、リレーショナルモデルでなかったりとか、以下のケースが多いようです。
・INDEXを利用できていない
・不要なデータの取得
・複数回クエリを実行している(N+1問題)
・テーブル設計が悪い
etc

不要なデータ取得に関しては、1回のデータ取得で扱うデータ量を小さくすることで遅くなりにくくする工夫もできます。
具体的には、
・WHERE句で絞る
・不要なJOINをなくす
などです。

INDEXに関して

INDEXの種類は思っていたよりも沢山あるようで、
B tree、B+tree、Hash、GiST、SP-GiST、GIN、BRINなどがあります。
B treeとB+treeの違いは、データをどこに持つかでして、
B+Treeはデータを分割した最下層のリーフノードに持ち、B Treeは最下層と子ノードにデータを持ちます。
そのためB+Treeは範囲検索に強く、B Treeは途中でデータが見つかればレスポンスが早いようです。
PostgreSQLやMysSQLといったメジャーなDBは、B Treeをデフォルト設定にしています。

基本的にクエリの実行においては、INDEXが貼られていないと、テーブルをフルスキャンします。(重い)
また、結合するときに、INDEXが貼ってあるカラムでないと、フルスキャンされてしまうので、結合テーブルの数とレコード数分だけ遅くなります。
検索のときはINDEXを貼ったカラムで検索し、JOINのときはINDEXを貼ったカラムを条件句に設定するようにしたいですね。

JOINに関して

種類は大きく3つで、
・Nested Loop Join
・Hash Join
・Merge Join
です。

Nested Loop Joinを早くするためには、上記のINDEXと同様、結合キーにINDEXを貼ったり、結合キーがUNIQUEであることを利用するといいそうです。
また、Nested Loop Joinは、レコード同士をかけ合わせるので、テーブルのレコード量が多くなるほど重くなります。
ただし、INDEXが貼ってある結合キーであれば、両者のレコード数を足し合わせた数だけ結合すればいいので、処理速度に差が生まれやすいそうです。

MySQLとPostgreSQLの特徴に関して

MySQL

・シンプル
・高速
→ そのため、INDEXがちゃんと効いていることが重要
・レコード更新型アーキテクチャを採用
更新処理の際は、対象レコード自身を更新します。

PostgreSQL

・高機能
JOINのアルゴリズムも複数あったりします。
・マルチインデックス
1つのテーブルに複数のINDEXを貼ることができます。
・追記型アーキテクチャ
更新処理の際は、レコードを追加し、元々のレコードから参照を消すことで更新とみなしています。

パフォーマンス改善に関して

パフォーマンス改善とは、ボトルネックを抽出し、数値的な改善を図ることです。
一口にWebサイトが遅いと言っても理由はいろいろあります。
改善を図るためには、遅い原因となっているボトルネックの抽出が大切です。

例えば、Webアプリケーションでは、応答までに関わる領域は複数あります。
クライアントのリクエスト、ネットワーク、サーバー、DB、API、などなど。
介在する領域が多いので、どこで遅くなっているのかをまず計測することが大切です。
その際に数値的な比較対象が必要なので、短期的に計測して終わりではなく、
日々計測し、比較対象となるデータを貯めておく必要があります。
要は定常データを作りましょう、っていうことです。

上記の計測の結果、Webアプリケーションにボトルネックが見つかった場合は、
パフォーマンス・チューニングツールを使ってWebアプリケーション全体を計測することから始めるといいでしょう。
プロファイルリングされたデータを用いて、改善していくループを回していくことが大切です。
データベースからクエリを実行する部分が遅いとなった場合は、実行計画をもとにチューニングしていきましょう。

実行計画に関して

実行計画とは、クエリを実行するときに、どういう方法でテーブルを検索するか、検索コストはどれくらいかを表示してくれるコマンドです。
クエリのパフォーマンスを改善するためには、まず実行計画を出力して、現在どういった処理をしているのかやコストを把握する必要があります。
Explainすることで実行計画が出力されるのですが、Explainによって出力されたプランが最適なプランかどうかを確認して改善を図っていきます。
具体的にどうやるかは、まだ勉強中なので別の機会にしますが、PostgreSQLの実行計画の見方は以下の動画がわかりやすいので見てみるといいかと思います。
https://www.youtube.com/watch?v=gxsBi-6ub3k

おわりに