Skip to main content

DB Performance

Overview​

DBのパフォーマンスについてのセクション

パフォーマンスを測る 2つの指標​

システムの世界ではパフォーマンスは2つの指標（メトリクス）によって測られる。

  1. 処理時間（プロセスタイム）や応答時間（レスポンスタイム）と呼ばれる指標
  2. スループット（Throughput）速度と同じ概念

スループットがなぜ重要なのか​

スループットが性能において重要なのは、これがシステムの「リソースキャパシティ」を決定する要因だから。
と言うのは、スループットの高いシステムであるほど、「CPUやメモリといったハードウェアリソースがたくさん必要になること」を意味しているから。
1つでも処理を実行すれば、システムはリソースを消費する。
そのため、同時に実行される処理が増えれば増えるほど、用意すべき物理リソースが増えていく。
処理の種類によっても必要なリソースの種類や量は変わってくるのですが、話を単純化して「どの処理も同じぐらいのリソースを消費する」とすれば、同時実行される処理数に比例して、必要なリソース量も増えるということになります。 平たく言うと、「同時に実行するユーザ数が増えれば、必要になる物理リソースも増える」ということ。

同時実行処理数が増えるほど用意すべきリソースも増えどれか1つのリソースでも頭打ちになった時点で、性能の劣化が始まる。
すなわち、レスポンスタイムが上昇を始め、反対にスループットが下がり始めます。
このとき最初に頭打ちになるリソースを「ボトルネックポイント（Bottle neck Point）」、略して「ボトルネック」と呼ぶ。

Image from Gyazo

これが意味することは、**「システムは、同時に実行される処理量がもっとも大きくなるタイミングを想定してリソースを用意しておかなければ、ピーク時に極端な遅延を引き起こすことになってしまう」**ということ。
このスループットとレスポンスタイムが極端な劣化を始める処理量を、「限界点（Breaking Point）」と呼ぶ。

ビークを想定したリソースを確保することを「サイジング（Sizing）」やキャパシティプランニング（Capacity Planning）と呼ぶ。
※システムの要件定義段階においてキーとなるタスク。

突発アクセス対策​

ピークが不安定かつ最大値を予測しにくいケースがある。
ピーク時に必要なリソースを用意してしまうと、逆に普段はリソースが遊んでしまってムダになる。
これらの対策としてクラウドがある。クラウドは仮想化をベースにリソース量を柔軟に変動させられる技術。スケールアップもスケールアウトも容易。

データベースはなぜボトルネックになるのか​

システムにおいてもっともボトルネックになるところ

  1. 扱うデータ量がもっとも多い
  2. リソース増加による解決が難しい
    1. クラウドだと動的にリソースを増減できるため有効

しかしDBのボトルネックはCPUやメモリではなくストレージ。
すなわち大抵の場合はハードディスク。 そして、ストレージというのはスケールアウトが困難なコンポーネント。
これは、データベースが基本的に「Active-StandBy構成」か、シェアードディスクによる「Active-Active構成」しかとれないため。
ストレージも含めてスケールアウト可能なのは、シェアードナッシングのケースだけ
しかし、シェアードナッシングを採用できる条件は限られている

そのような制限があることから、データベースの世界では伝統的にチューニングの技術が発達してきた。
「チューニング」とは、すなわちアプリケーションを効率化することで、同量のリソースにおいてもパフォーマンスを向上させる技術のこと。

データベースに限って言えば「どうやってSQLを速くするか」ということとほぼ同義。
リソース追加によるパフォーマンス問題の解決が難しいため、いかにして与えられたリソースの範囲内でやりくりするか、ということが重要になった。
例えるなら、決められた予算内で家計をやりくりする主婦みたいなもの。

DBの処理順序​

SQL文のパフォーマンスを考える場合は、DBが内部でどの順序でSQLを処理しているかを見ていく必要がある。

DBがSQL文を実行する内部プロセス順序​

Image from Gyazo

  1. 構文エラーがないか確認するparse
  2. SQL文から実行計画（Execution Plan）を立て、実行計画を決めるプログラムをオプティマイザー（Optimizer）と呼ぶ
    1. この「実行計画を立てる」というプロセスをデータベース自身が行うのは、リレーショナルデータベースの大きな特徴。C言語やJavaといったプログラミング言語を使ってプログラムを作る際には、このようなプロセスは存在しない。
    2. その理由は、プログラミング言語で作られるプログラムでは、プログラマ自身（人間）が、どうやってデータにアクセスするかまでプログラムの中に書き込んでいる（コーディングしている）ため。
    3. 実際、プログラミングの経験がある方ならばわかると思いますが、データにアクセスする処理を記述する際は、「この場所に置いてあるファイルを開く」とか「ファイルを1行ずつループして先頭行から最終行まで読み込む」といったレベルまですべてコーディングする必要がある。
    4. だが、SQLはどうやってそのデータを見つけ出すかはSQLに一切書かれていない
      1. そのためたびたび宣言型言語と呼ばれる

オプティマイザーが参考にする統計情報​

統計情報はDB内部ではテーブルに保存されており、MySQL では show table status; または show index from テーブル名 で見ることが可能。

実行計画を表示する​

SQL文の実行計画を取得する方法はSQL文の前に EXPLAIN をつけるだけ

mysql> > EXPLAIN select * from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.06 sec)
  • rows
    • アクセスしたレコードの行数
  • type
    • ALL: フルスキャン
    • range: レンジスキャン
    • ref: レンジスキャン
  • possible_keys
    • インデックスを使っていることを表す
  • keys
    • インデックスを使っていることを表す

テーブルに対するアクセス方法​

テーブルへのアクセス方法は2つある。

  1. フルスキャン（ALL）
  2. レンジスキャン（range）

レンジスキャン​

レンジスキャンをするためにはインデックス（索引）が必須。 インデックスがないカラムを WHERE 句に入れてもレンジスキャンにはならない

インデックス​

B-tree という仕組みで探索を行う。
この仕組みはデータ量が増えた時ほど優れた改善方法を発揮する。
どんな値に対しても同じぐらいの時間で結果を得られる。これを均一性と呼ぶ
B-tree の階層は3~4程度になるように調整されている。

インデックスを作ったときの性能的なメリットとして、「ソートをスキップできる」というケースがあります。「ソート」というキーワードは、インデックスの構造を説明した際にも出てきましたね。B-treeではデータを順序付けた（ソートされた）形で保持していました。 これが性能的にどう有利に働くかと言うと、実はデータベースはSQL文を実行する際、裏側でソートを行うことがあるのですが、ソート済みのインデックスを使うことでそれをスキップできる場合があるのです。

インデックスの使い方としては、WHERE句で指定された条件の絞り込みを高速化するという「本来の使い方」ではないのですが、副次的な効果としてなかなか重宝します。

なお、SQLにおいて内部的にソートを発生させる処理には、以下のようなものがあります

  • GROUP BY句
  • 集約関数（COUNT/SUM/AVGなど）
  • 集合演算（UNION/INTERSECT/EXCEPT）

これらを利用する際にはソートのキーとする列が必要になるため、そのキー列にインデックスが存在すると、オプティマイザーがこれをうまく利用してソートをスキップするという効率化を行えるのです。

インデックスの作成が裏目に出るケース​

インデックスを作ると、逆に性能が劣化する代表的なケースは、次の2つ

  1. インデックス更新のオーバーヘッドにより、更新処理の性能を劣化させる
  2. 意図したものと違うインデックスが使われてしまう

インデックスは、テーブルに新しいデータが追加されたり、既存のデータに対して更新・削除が実行されると、自動的にインデックス自身も更新する機能が備わっている。
いわば、テーブルが書籍だとすれば、本文が変更されると、それに応じて索引のほうも自動的に更新されるようなもの。
これ自体は非常に優れた機能なのですが、その代償として、インデックスが存在しないときに比べれば、更新のたびにインデックスの更新も付随して発生することになるわけ。
これもオーバーヘッドの一種。
通常、1行程度のレコードの追加や更新・削除であれば、それに伴うインデックス更新にかかる時間は、ほとんど体感できないほど小さいものです。しかし「塵も積もれば」で、これが何千万行とか何億行といった更新を行うことになると、インデックス更新にかかる時間も馬鹿にできなくなる

インデックスを作る時の基準​

トレードオフがあるためバランスを考えながら作成する

  1. サイズの大きなテーブルにだけ作る
  2. 主キー制約や一意制約が付与されている列には不要 上述したように、主キー制約が付与されている列には、自動的にインデックスが作成されています。一意制約が付いている列でも、これは同様です。なぜこの2つの制約が付いている列には暗黙にインデックスが作成されるかと言うと、値の重複チェックをするためにはデータをソートする必要があるので、インデックスを作ることでソート済みにしておくのが便利だからです（B-treeインデックスは必ずデータをソートした状態で保持するのでしたね）
  3. カーディナリティ（値の分散度）の高い列に作る。 インデックスを作る列を決める指針として、もっとも重要なのがこれです。カーディナリティ（Cardinality）とは、「値の分散度」を示す言葉です。 特定の列について、多くの種類の値を持っていればカーディナリティが高く、反対に値の種類が少なければ、カーディナリティが低いということになります。 たとえば「免許証番号」というのは、ドライバーの間で重複なく発行されており、いま現在車を運転する資格のある人数だけの種類があります。カーディナリティはざっと数千万というところでしょう。これは非常にカーディナリティの高い例です。 反対に、「出身都道府県」というのはどうでしょうか。これは最大47種類しか値がないことが法的に決まっているので、免許証番号に比べれば圧倒的にカーディナリティは低くなります。さらに「性別」に至っては、男性、女性、不詳の3種類しか値を取りえないので、極めてカーディナリティが低いということになります。 つまり、インデックスを作ることによる性能改善効果が期待できる大きさを不等号で表現すると、次のようになります。
Last updated on