『SQLパフォーマンス詳解』を読んだ

気づけば今年になってから一度もブログを書いていないことに気づいて、あ、やばい、となった。
ゴールデンウィークを持て余しているわけではない。決して。

どんな本か

SQLパフォーマンス詳解

著者はMarkus Winand氏(@MarkusWinand)。言わずと知れた"USE THE INDEX, LUKE"の人*1。訳者は松浦隼人氏(@dblmkt)。GitHub Japanの人。

b.l0g.jp

なぜ読んだか

以下のブログをみた。

pgsqldeepdive.blogspot.jp

データベースのパフォーマンスやインデックスの設計方法について考える時、個々のRDBMS製品に特化した書籍は多少はあるかもしれませんが、ここまで網羅的かつ横断的に解説されている本を、私は他に見たことがありません。

日々のお仕事の中でスロークエリのチューニングをして、多少の知識はつくものの局所的で、それらを体系的に学ぶ機会はなかったので、ちょうどよいと思った。
PDFが5ユーロと激安お買い得だったし。激安。

全体の感想

最初の章が「インデックスの内部構造」の時点で他のSQL関連本や各種DBMS製品の技術書とは一線を画すかんじ。

全体を通して「インデックスをいかに活用するか」が軸になっていて、当初の購入目的だった「SQLパフォーマンスに関する体系的な知識」という意味では、自分のイメージとはやや違うかも？と最初は思った。しかしよくよく考えると、SQLのパフォーマンス問題の本質は「SQLの実行=論理と、(リレーショナルモデルに基づく)データ構造=物理の間の境界」によって生じるものと捉えられる。その境界に存在する不整合の一つとして、「集合論に基づくリレーショナルモデルには要素間の順序性はないのに対し、データ探索には順序性がある」があり、これに対する解決策として、インデックスというものがある、と解釈できる。その意味で、インデックスの活用はSQL及びリレーショナルモデルにおけるパフォーマンス問題の本質的課題といえる。

目次をみて、興味があるキーワードが2,3あるか、あるいはMySQLやPostgreSQLやOracleやSQL ServerなどのRDBMS製品の比較が整理されているので、使ったことがない製品ではどうなっているかといった、横串に知識を補強する目的にも有益かも。一方で、目次や以下のメモをみて何を言っているかわからない、知らない単語がいっぱいある、という状態の方は、本書の前により基礎的かつ広くSQLに関する体系的な本を読むのがいいかも。また、パフォーマンス問題は実際にSQLを実行したりサービスを開発する中で実際に直面しないとなかなか実感しづらく、そういった経験がない人が読んでもあまり腑に落ちないかもしれない。というわけで、「SQLはだいたい知ってて使ってるけど、なんで遅いのか、どうすれば速くなるのかわからない」という方にはぜひオススメしたい。

用語の定義にWikipediaを引用されたりするのはご愛嬌。よくみたらLUKEのウェッブサイトそのままっぽいというのは内緒。

各章のメモと感想

全8章+付録という構成。気になったところを引用して、感想を書く。

第1章インデックスの内部構造

Bツリーは、二分木(binary tree)ではなく、バランス木(balanced tree)です。(p.5)

バランス木は、ブランチノードが、各子ノードの最大値を保持している。ルートノードからリーフノードまでの深さがどこでも同じなため、値に偏りがよらず安定した検索性能を維持できる。ツリーの深さが対数的に増える(=対数的スケーラビリティ)ため、リーフノードの増加に対してツリーの深さの増大は非常に遅い。

複合インデックスを定義する際に考えるべき最も重要なのは、そのインデックスを使えるSQL文ができるだけ多くなるように、列の順番を決めることです。(p.16)

インデックスというものはその仕組みを理解しなくても作れるし使える。複合インデックスのカラムの定義順に意味があるということが直感的に理解されづらいのは、普段主に触れているSQLが「物理構造に依存しない」設計がされているからだと思う。

インデックスは、「SQLの論理」と「データの物理」のギャップが生み出すパフォーマンスの問題を埋める存在ともとれる。

第2章 where句

疑問符(?)は、SQL標準で唯一定義されている、プレースホルダの文字です。(p.38)

名前付きパラメータって標準なんだと思っていた。

ちなみに、SQL標準に乗っ取ると、以下のようなイメージ。パラメータにしたいところに?をいれて、出てきた順に連番がつく。

var commandText = @"
SELECT user_name
FROM users
WHERE user_id = ? AND user_status = ?;
";

using (var connection = new SqlConnection(connectionString))
{
    var command = new SqlCommand(commandText, connection);
    command.Parameters.Add("1", SqlDbType.Int);
    command.Parameters.Add("2", SqlDbType.String);

    command.Parameters["1"].Value = 123;
    command.Parameters["2"].Value = "active";
}

これだと、順番が変わったり間に新たなパラメータを入れたら、パラメータの設定も書き直さなくてはならない。これだと不便すぎるので、通常のRDBMS製品は、:とか@とかではじまる名前付きのパラメータ変数を定義できるようになっている。C#のSqlCommandのドキュメントにも

The Microsoft .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to a SQL Statement or a stored procedure called by a command of CommandType.Text. In this case, named parameters must be used.

とある*2。

LIKE式を直接最適化してしまう方法として、WildSpeedを使うこともできます。(p.48)

PostgreSQLに関する記述だが、知らない子だった。

Zen: wildspeed

一時はcontribに含まれていたらしいが、作成されるインデックスサイズがクソデカく、pg_trgmに取って代わられたらしい*3。やってることは同じで、各文字に対してインデックスを作成する。

PostgreSQLのクエリ計画のキャッシュは、カーソルが開かれた文にのみ適用されます。つまり、PreparedStatementを開く必要があります。...なお、PostgreSQLのJDBCドライバは、5回目の実行以降でのみキャッシュを有効にすることに注意しましょう。(p.75)

PL/pgSQLのfunctionで実行されるクエリはキャッシュされる*4。それ以外のSQLはされない。キャッシュされたプランはメモリヒープに保持され、コネクションをハンドルするサーバプロセスによって使われる。pg_prepared_statements というカタログビューをみれば、そのコネクションで保持されているプランをみれる。

そもそも実行計画の作成がボトルネックになるほどカリカリにチューニングすることがそんなにない。

第3章パフォーマンスとスケーラビリティ

注意深い実行計画の調査結果は、うわべだけのベンチマークよりも信用のおけるものです。(p.86)

サービスの利用を完全にシミュレートすることは不可能なので、本番環境と開発・テスト環境を厳密に一致させることは本質的にできない。ベンチマークによって「特定の」「想定可能な」ケースで問題がないことを示すことはできるため、その点で有益ではある。実行計画の調査は、想定の精度を向上させる。原理を理解した上でどんなベンチマークを取るべきかを設計することが重要。

第4章結合処理

結合述語にインデックスを作成しても、ハッシュ結合のパフォーマンスは良くなりません。(p.102)

よくよく考えれば当たり前のことだが、日々意識しているわけではないので記憶に定着しておらず、毎回どういう仕組だったっけ、と頭を使わないといけない。

入れ子ループ結合だと、内側のクエリを実行するたびにBツリーの操作が走る(結合条件のカラムに対するインデックスがある場合)。ハッシュ結合は、内側のテーブルを全走査して、結合条件のカラムを(ハッシュ化された)キー、結合の候補となるレコードを値としたハッシュテーブルを作成する。内側のクエリの結果が大きい場合にハッシュ結合が選択されやすい。ハッシュ結合を高速化したければ、メモリ上に作成されるハッシュテーブルがなるべく小さくなるようにすればいい。クエリの条件をなるべくつけたり、必要なカラムだけをSELECTしたり。当たり前だけど。

第5章データのクラスタリング

インデックスの並びとテーブルの並びの相関関係は、パフォーマンスを測る上での基準になります。いわゆる、クラスタ化係数です。(p.113)

「インデックスの効果」として説明されているが、インデックスとクラスタリングは原理的には独立しているという理解。クラスタリングは、テーブル内のレコードの物理配置を特定のカラムの値に基づいてソートする。そのカラムについてのインデックスがあればそれは整列済みの順序を保持しているから、それを利用して配置しよう、という関係。PosgreSQLにおけるクラスタリングについては以下で書いた。