今回はデータベースのお話しです。
ただ、ここでは、データベースが何かということはという説明はしません。
そもそもデータベースが何かを知らないという人は別のサイトなりで勉強してください。
データベースを使ったことがある、もしくは、データベースがどういったものかについては知っていることが前提で話を進めていきます。
ここで説明するのは、データベースエンジンがどういった処理をしているのか?という内容です。
もちろん、そんなことを知らなくてもプログラムやSQL文を書くことはできます。
しかし、知らないと無駄なリソースを消費したり、レスポンスが悪いSQL文を書いてしまうことがあります。
そうならないように、しっかり理解するようにしましょう。
データベースの仕組み
分かりやすいように昔のデータベースを例に説明します。
このころのデータベースは構造が単純なため、現在のRDBよりかなり高速に処理ができました。
現在のNoSQLDBは、当時のDBと同じような構造となっていると思われます。
昔のデータベースはアイサムファイルをデータファイルとして使用していました。
アイサムファイルとは、要するにテキストファイルです。
その頃のデータベースは、データベースエンジンとテーブル定義、データファイル、キー定義、キーデータでできていました。
データファイルには、データが全部つながった状態で保存されています。
テーブル定義にはカラムの順番と桁数が設定されており、レコード長はそれらの合計となります。
プログラムからデータを取得するとき、データベースエンジンはアイサムファイルからデータをレコード長でデータを切って取得するという仕組みです。
当初のデータベースはRDBではなく、ただのDBですのでSQL文は使えません。
レコードの取得も1回の呼び出しで1件しか取得できないので、プログラムで件数分ループして取得することになります。
検索するときはキー定義で設定された項目を使用します。
キー定義は現代の検索を早くするためのキー(インデックス)ではなく、検索条件として指定できる項目の設定です。
つまり、キー定義で設定されていない項目は検索条件にすることができません。
プログラムで検索条件を指定してデータの取得を実行するとデータベースエンジンは以下のように処理を行います。
テーブルの結合
このころのシステムでもマスターテーブルは存在していましたので、現在のテーブル結合のような処理は必要でした。
しかし、SQL文は使用できませんので、ロジックで結合することになります。
テーブル結合のロジックは以下のようにまります。
現代のRDB
現代のRDBにはストアドプロシージャ、チューニング機能、バックアップ機能、スケジューリング機能などがあり、いろいろ便利になっています。
データ検索もSQL文が使用でき、検索条件を自由に設定できますし、高速に処理するためのデータキャッシュ、SQLキャッシュ、オプティマイザ、リーフによるインデックス検索などの機能も充実しています。
しかし、データの検索ロジックに関しては昔のデータベースと大きくは変わっていません。
在庫テーブル、倉庫マスタ、商品マスタを使用して名古屋倉庫の在庫の一覧を表示する処理を考えてみましょう。
SELECT
在庫テーブル.商品コード,
商品マスタ.商品名,
在庫テーブル.倉庫コード,
倉庫マスタ.倉庫名,
在庫テーブル.在庫数
FROM 在庫テーブル
INNER JOIN 倉庫マスタ ON 在庫テーブル.倉庫コード = 倉庫マスタ.倉庫コード
INNER JOIN 商品マスタ ON 在庫テーブル.商品コード = 商品マスタ.商品コード
WHERE 倉庫マスタ.倉庫名 = '名古屋倉庫'
このようなSQLを実行したときの処理は以下のようになります。
説明からは省略していますが、エンジン処理は最初にSQL文のコンパイルや実行計画の作成を行います。
また、主キーやインデックスがあればそれを利用した検索となります。
例えば、このSQLのレスポンスが悪く、改善が必要となった場合、どうすればいいでしょうか?
通常、SQLのレスポンス改善はインデックスを作成するなどして改善する方法が用いられます。
上記の例を見ると、在庫テーブルの件数分、倉庫マスタを全件検索(フルスキャン)しています。
では、倉庫マスタの倉庫コードにインデックスを付けることで早くなるでしょうか?
レスポンスが遅い原因は、「在庫テーブルの件数×倉庫マスタの件数」のデータ読込を行っていることが考えられます。
もし、倉庫マスタにインデックスを追加したとしてもデータ読込回数は減ることはありません。(少しは減るかもしれませんがレスポンスタイム改善といえるレベルにはならないと思います)
結果として、フルスキャンをインデックススキャンにしたとしてもレスポンスタイムに大きな変化は期待できません。
テーブルデータを直接読み込むことが原因で遅い場合などはインデックススキャンにすることで劇的に早くなる場合もありますが、
今回のように、インデックスやメモリ管理を駆使してレスポンスなどの問題を解決することができない場合があります。
データベースエンジンがどういった処理をしているか、その結果何に時間がかかっているかをしっかりと理解して対策を行わなければ、問題の解決は難しいということになります。
データベースを使用する技術者としては、データベースエンジンが何をしているかが想像し、データベースエンジンに負荷をかけない使い方をすることが重要です。
では、先ほどのSQLのレスポンス改善はどのようにすればいいでしょうか?
今回の要件では倉庫名が指定されるため、倉庫マスタの対象レコードを1件に絞ることができます。
SELECT
在庫テーブル.商品コード,
商品マスタ.商品名,
在庫テーブル.倉庫コード,
倉庫ビュー.倉庫名,
在庫テーブル.在庫数
FROM 在庫テーブル
INNER JOIN
(SELECT 倉庫コード, 倉庫名 FROM 倉庫マスタ WHERE 倉庫名 = '名古屋倉庫') AS 倉庫ビュー
ON 在庫テーブル.倉庫コード = 倉庫ビュー.倉庫コード
INNER JOIN 商品マスタ ON 在庫テーブル.商品コード = 商品マスタ.商品コード
このように、倉庫マスタの対象行だけのビューを作り、在庫テーブルと結合することでデータ読込を約1/3に減らすことができます。
倉庫マスタの絞り込みが事前に実行されますが、それまで在庫テーブルの件数分、倉庫マスタのフルスキャンを実行していたものが最初の1回だけになりますので、無視してもいい時間です。
単純に処理内容が1/3になっているため、レスポンスタイムも1/3になることが期待できます。
それまで10秒かかっていたとすると3秒になるということになりますので、十分な改善ができているといえます。
ただし、このようなビューを利用するとデータベースによってはテンポラリ領域を使用することがありますので、テンポラリ領域の枯渇には注意が必要です。
データベースはエンジンの処理が理解できるように、各々のメーカーより情報が提供されています。
そういった情報を勉強して処理効率やリソースの消費を意識したSQL文を書くようにしましょう。