SQL実践入門──高速でわかりやすいクエリの書き方 要点メモ

INの行式(row expression)

P96。ORで以下のように書く場合、

SELECT key, name, date_1, flg_1, date_2, flg_2, date_3, flg_3 FROM ThreeElements
WHERE (date_1 = '2013-11-01' AND flg_1 = 'T')
  OR (date_2 = '2013-11-01' AND flg_2 = 'T')
  OR (date_3 = '2013-11-01' AND flg_3 = 'T')

INの行式に置き換えられる

WHERE ('2013-11-01', 'T')
IN ((date_1, flg_1), (date_2, flg_2), (date_3, flg_3))

さらに、CASE式にも出来る

WHERE CASE WHEN date_1 = '2013-11-01' THEN flg_1
WHEN date_2 = '2013-11-01' THEN flg_2
WHEN date_3 = '2013-11-01' THEN flg_3
ELSE NULL END = 'T'

CASE式はSQLのあらゆるところに書けるらしい。SELECT列、WHERE条件 ORDER BY句等

DBのメモリ領域

1. データキャッシュ。MySQLでいうところのinnodb_buffer_pool_size。データの一部をメモリ上に載せておくことで高速化させる。

2. ログバッファ。MySQLでいうところのinnodb_log_buffer_size。更新系SQL実行時に更新情報を貯めるバッファー。ディスクへはまとめて反映される。

3. ワーキングメモリ。MySQLでいうところのsort_buffer_size(MySQL5.7のデフォルト値は256KB)。ソートやハッシュ処理で使う。

ワーキングメモリ

P16。ソートやハッシュが必要になったときに使用される。通常はデータキャッシュやログバッファとは別の領域として管理されていることが多い。不足した場合、ストレージが使用される（いわゆるTEMP落ち）。

データがメモリに治まっている間は非常に高速なのに、メモリから溢れた瞬間に一気に遅くなる、という極端な劣化が（突然）起きてしまう。かつ、この領域は複数のSQL分で共有して使用されるため、一つのSQL分を実行しているときはメモリ内に収まっていたのだが、複数のSQL文を同時実行した際の競合によって閾値を超えてしまって溢れてしまうという難しさがある。

→ では、どれくらいに増やそうかと考えて調べたところ、こちらの記事にて、

MySQL 8.0.12以降では段階的に必要量のバッファが割り当てられるようになってるため、sort_buffer_sizeに大きい値を設定したとしても無駄なメモリの利用を抑えられるようになっている

とのことなので、大きめに設定しておいて良さそうかなと思う。

実行計画でTEMP落ちをチェックする

P337。MySQLのExplain(実行計画)で、ExtraにUsing temporary; Using filesortと表示されたら、「ワーキングメモリ内でソート処理を完結させられないため、一時領域（ストレージ）にファイルを作ってソートを行う」という意味らしい。