ゲームを題材に学ぶ 内部構造から理解するMySQL

第5回 DB側でやること、アプリ側でやることを見極めよう

この記事を読むのに必要な時間:およそ 2 分

 本記事は,『Software Design 2019年8月号』の第2特集「ゲームを題材に学ぶ 内部構造から理解するMySQL」をWeb掲載用に再編集したものです。
 本記事のテーマを,より基本的なところから丁寧に解説した『SQLの苦手を克服する本 データの操作がイメージできれば誰でもできる』が2019年8月26日に発売されました。本記事と併せてご活用ください。

「JOINはDBサーバの負荷が高くなる」は本当か?

 ⁠JOINは複雑なので,単純なSQLに分割してぐるぐる系で取得すれば,処理が遅くなったとしても,DBサーバの負荷は減る」と考えているエンジニアが実際に存在します。前回解説したとおり,SQLのオーバーヘッドの大きさをイメージできれば,⁠そんなことはない」と理解できたかもしれませんが,さらに深く理解するために,本稿ではJOINを分割したときと,JOINしたときの違いを見てみましょう。

 JOINした(一発系の)リスト1を手続き型言語に直してみると,リスト2になります。

リスト1 JOINを使った一発系SQL

SELECT *
FROM country
INNER JOIN city
ON country.Code = city.CountryCode;

リスト2 ⁠JOINを使った一発系SQL」をJavaのようなコードで表現

for(Row rowCountry:country){
    rowKeys = city.Indices.CountryCode.getRangeKeys(rowCountry.code);
    for(RowID rowID:rowKeys){
        // SELECT句の処理があれば行う
        retRows.add(rowCountry, city.getRow(rowID));
    }
}
// ORDER BY句があればソート
return retRows;

 ぐるぐる系のリスト3を手続き型言語に直してみると,リスト4になります。

リスト3 ぐるぐる系のSQL

SELECT * FROM country;
-- 以下をcountryの件数回問合せる
SELECT * FROM city WHERE CountryCode = ?;

リスト4 ⁠ぐるぐる系のSQL」をJavaのようなコードで表現

for(Row rowCountry:country){
    retRows.add(rowCountry);
}
return retRows;

// APサーバからcountryの件数回呼び出される
rowKeys = city.Indices.CountryCode.getRangeKeys(?);
for(RowID rowID:rowKeys){
    retRows.add(rowCountry, city.getRow(rowID));
}
return retRows;

 見比べてみると,⁠ぐるぐる系」でも,DBサーバの処理は何ひとつ減っていないことがわかります。

 第4回の図2と第3回の「DBエンジンがSQL実行前に行う内部処理」をもう一度見てください。ぐるぐる系は,この膨大なSQLのオーバーヘッドとネットワークの処理が無駄に繰り返されるため,DBサーバの負荷を下げるどころか,負荷を大幅に上げることになるのです。

APサーバで肩代わりできる処理

 APサーバで処理することによってDBサーバの負荷が下がるということは,APサーバがDBサーバの処理を肩代わりしているということになります。APサーバで肩代わりできる処理は次のものしかありません。

  • マスタ類のキャッシュ → APサーバの同期が取れれば有効
  • SELECT句の処理 → SELECT句でソートするWindow関数以外は誤差
  • ループのブレイク処理 → 誤差
  • ソート処理 → DBサーバの負荷を下げるには有効

 DBサーバの負荷をどうしても下げたいのであれば,SQLでのORDER BYを禁止にすべきですが,ほとんどの共通仕様やコーディングルールで,⁠ORDER BYを必ず付けること」となっています。ORDER BY(ソート)「1ページにつき10件ずつ表示する」といった表示件数を制御するページングが必要なWebシステムでは意味がありますが,そうでなければAPサーバでソートしても問題はありません。ソートは非常に重い処理ですので,シングルポイントになるDBサーバで処理するより,分散できるAPサーバで処理するほうが良いでしょう。

SQLでどこまで処理すべきか

 C言語やアセンブリ言語の経験がないエンジニアが増えています。そのため,CPUとメモリの関係をあまり意識することがなくなっています。ここでは,CPUやメモリを意識してSQLについて考えてみましょう。

 SQLはRDBMSのインプロセスで動作します。インプロセスとはRDBMSのメモリ空間内で処理されるという意味で,ポインタ(メモリのどこにデータがあるかを示すもの)で処理できます。C言語(C++)の経験がなければ「参照渡しできる」と読み替えても良いでしょう。APサーバは別のメモリ空間で動作しています。APサーバから見れば,RDBMSのメモリ空間のデータを直接読み書きすることはできませんから,お互いに値渡しするしかありません。

 ここで第1回の図1で解説したページ単位でのI/Oを思い出してください。インプロセスであればポインタ(参照渡し)で処理できる内容でも,APサーバで処理させるためにはDBサーバはデータをバッファからワークメモリにコピーして,レコード,フィールドに加工しなければなりません。つまり,⁠できる限りSQLで処理するほうがDBサーバの負荷が下がる」というのは,⁠DBサーバから大量のデータをAPサーバにすべて値渡しして処理するより,DBサーバ内でできる処理は参照渡しで処理するほうが効率が良い」と考えれば理解しやすいのではないでしょうか。SQLのオーバーヘッドの繰り返しを避けるだけでなく,内部処理もSQLのほうが効率的になるわけです。

 設計時に「SQLでどこまで処理するか」を決めるには,処理全体を自分が得意な手続き型言語で設計し,⁠どのメモリ空間で処理すべきか,ネットワークを越えて値渡しで処理することで効率的になる処理は何か?」と切り分けて考えてみましょう。そうすると,APサーバが肩代わりできる処理は前述の4つしかないことが理解できるはずです。ですから,ほとんどの場合,⁠SQLでどこまで処理すべきか」というのは,⁠ソート以外のデータに関連する処理はすべてSQLで処理すべき」となります。

 ⁠そんな極端な」という声が聞こえそうですが,DBサーバで利用される言語がSQLではなくAPサーバと同じ言語であったなら,⁠APサーバで処理すべき」と考えるエンジニアは少ないはずです。SQLになったときに逆の判断になる理由は,合理的に判断した結果でしょうか? ⁠SQLが嫌い(わかりにくい⁠⁠」という主観が影響してはいませんか?

著者プロフィール

生島勘富(いくしまさだよし)

株式会社ジーワンシステム 代表取締役。
フリーランスのエンジニアを経て,2003年に株式会社ジーワンシステムを創業する。その後,プレイングマネージャーとして多くのシステム開発に従事し,現在ではデータベースを中心としたコンサルティングを行っている。
メール:info@g1sys.co.jp
Webサイト:http://www.g1sys.co.jp/
ブログ:https://sikushima.hatenablog.com/ Twitter:@Sikushima