Join-fu: The Art of SQL Tuning for MySQLの簡易まとめ

MySQL Pro の著者の ZendCon08 での発表スライド．

Join-fu: The Art of SQL Tuning for MySQL

ケチれ

ひたすらケチる．
- VARCHAR よりも CHAR 使え．VARCHAR 使うときは慎重に考えろ．
- TEXT は節約して使え．
- BLOG はもっと節約して使え．
BIGINT とか必要か？
- セッションとかで IP アドレス記録するときも，INET_ATON とかで INT UNSIGNED で扱う方が容量少なくてすむ．
  - さらに範囲も INT の範囲になるな．
- 以下の定義でも，各レコードは 4 byte = 32bit int だけ消費する
  - INT(1) の「1」はストレージ上の消費桁数ではなく，「1桁目以外を 0 で埋める」ということ．

CREATE TABLE ti (
  a INT(1) UNSIGNED NOT NULL
);

- - 桁数で制限しても消費メモリは変わらない．

垂直分割と水平分割

垂直分割

1つのテーブルに，更新頻度の高いカラムと，低いカラムを混在させてないか？
分割することで，更新だけじゃなくて，バッファの節約にもなることを忘れないように．
カラムに FULLTEXT は必要か？

MySQL query cache を知るために

アプリの read/write のパターンをよく理解すべき．
キャッシュは，CPU使用率と読み込みパフォーマンスが妥協点を探るようなもの．
Query Cache はSQL文のHash値をキーとして保存されている．
- SQL文が完全一致しないと取得できない．
あらゆるテーブルへのあらゆる変更は，キャッシュされた結果との非整合を生じさせる
書き込むアプリは，Query Cache に気をつける
- SELECT SQL _NO_CACHE とか．

垂直分割の続き

1つのテーブルで，更新されないカラムと，更新頻度の高いカラムを混在させてないか？
- 更新がある度に，query cache が破壊されていく．
InnoDBテーブルで，WHERE 句のない COUNT(*) を実行してないか？
- 時としてテーブルのフルスキャンが実行されてしまう．

coding like join-fu master

首尾一貫せよ
Iterator/loop などのことは考えるな．
ANSI スタイルで SQL 文を書け
- 集合で考えるんだ．
大きな長大な SQL 文じゃなく，小さな管理しやすい SQL 文に分割するんだ．

join-fu guidelines

色んな方法を試せ
結合ヒントに気をつけろ(Beware of join hints)
テストとベンチマークを忘れるな

SQL 文の書き方とか

タブ・スペースを使ってわかりやすく．
同僚のことを考えて，プログラムコードのように読みやすく書け．

ANSI vs. Theta Style

ANSI Style は JOIN を明示的に書く
Theta Style は明示的には書かない．
なぜ ANSI Style か？
- MySQL だと，明示的に書かなければ暗黙的にINNER でかつ CROSS結合だと見なされてしまう．
  - MySQL 自体は，INNER/CROSS/LEFT/RIGHT/NATURAL 結合をサポート
  - 読みにくくなる．
- Theta Style だと結合条件のミスを招きやすい．
  - 特にテーブルが多いとき
  - WHERE 句からちょっと条件が抜けるだけで，終号の直積になってしまう．

EXLAPIN

MySQL の実行計画がわかる．SELECT の前につけるだけ．

select_type: データ集合の種類
table: テーブルの alias，テーブルの実名，もしくは導出された中間テーブルなど
type: 集合内のデータを収集する戦略
possible_keys: optimizer が Query に使えると思ったキー
key: 実際に optimzer が使ったキー
rows: 集合中のレコード数の見積り
Extra: optimzer が教えてくれる情報
ref: 結合に使ったカラム

- type: const, ref: const
  - これは使われるカラムが unique で not null である場合
  - 1行しか出てこないとき
  - 結合カラムでも大丈夫
    - 要するに「primary key などのすぐ見つかるカラムに値が指定されている SELECT」と言うことかな？
- type: range
  - 範囲演算子がかかってるカラムにインデックスが貼ってあるとき
  - 非常に多くのレコードが返ると想定される場合は，範囲絞りじゃなくてテーブルスキャンになる．
  - インデックス張ってあるカラムは関数呼び出しによって処理されてはいけない
    - 関数の引数として，インデックス張ってあるカラムを使うな．
- scan vs. seek
seek とは一般的，データを探すためにメモリやディスク内の別の場所へジャンプするようなもの．
一方 scan はデータ断片の先頭へジャンプして，断片の最後まで連続的にデータを読み込む
非常に大きなデータ量の場合，scan は複数回の seek よりも効果的になる．
- type: ALL
  - WHERE 句がない場合
  - WHERE 句のどのカラムにもインデックスが張ってない
  - インデックス張ってあっても，選択性が良くない
  - WHERE 句が多くのレコードが引っかかるような条件
  - MySQL 5.0 以前か，WHERE 句で OR を使ってる．

- Extra: Using index
  - type: index とは違う
- インデックス貼ってあるカラムに関数とか使ったダメだ
  - LIKE 'XXX%' ならインデックスからの範囲選択が有効に働いて，window が小さくなる．
  - LEFT(title, 2) = 'Ti' とかだと，フルテーブルスキャンになってしまう．
  - 最適化手法例

SELECT * FROM Orders WHERE TO_DAYS(CURRENT_DATE()) - TO_DAYS(order_created) <= 7;

- - - インデックス張ってある order_created が関数に使われてるので，これを改善

SELECT * FROM Orders WHERE order_created >= CURRENT_DATE() - INTERVAL 7 DAYS;

- - - クエリキャッシュに入るように，実行時まで不確定な関数とかは削除する．

SELECT * FROM Orders WHERE order_created >= "2008-09-30" - INTERVAL 7 DAYS;

- - - 必要なカラムだけとってくるようにする．
      - 使わないカラムあっても仕方ない．
      - 結果が大きくなると query cache にもフィットしないし，テンポラリテーブルも大きくなるから．

SELECT order_id, customer_id, order_total, order_created
FROM Orders WHERE order_created >= "2008-09-30" - INTERVAL 7 DAYS;

foreach ループについて考える

支払いテーブルから各顧客毎に，支払日が一番新しいレコードを全て取得したい．

SELECT
p.*
FROM payment p
WHERE p.payment_date =
( SELECT MAX(payment_date)
  FROM payment
  WHERE customer_id = p.customer_id
);

- これは全件検索になっているので，インデックス(customer_id, payment_date)を張ってみる．
  - でもそれだけじゃダメで，
- 副問い合わせを使う
  - 副問い合わせは集合 -> 顧客の最新支払い日の集合

SELECT
p.*
FROM (
  SELECT customer_id, MAX(payment_date) as last_order
  FROM payment
  GROUP BY customer_id
) AS last_orders
INNER JOIN payment p
ON p.customer_id = last_orders.customer_id
AND p.payment_date = last_orders.last_order;

- - 1段階テーブル導出を経由することで，インデックスが有効に働くようになる．
  - 最初の 30 倍ほど高速

query of N:M Replations

WHERE の中で OR を扱うとき
- optimizer がテーブルを結合する順序は，記載順と逆．
WHERE の中で AND を扱うとき
- Filesort させないために，CROSS JOINを使う
  - CROSS JOIN して，それに INNER JOIN する感じ
- それよりも，2つのクエリーに分けた方がいい場合もある．

あと省略．