7月スタッフ会合を行いました。

こんにちは、１ヶ月ぶりです。
今月も、スタッフ会合が開かれました。

今月のワークショップは「実践的SQLパフォーマンスチューニング」です。
SQLとは、データベースから目的のデータを取得する目的で使う言語のことです。
しかし、誤った書き方をしてしまうと、動作が極端に遅くなってしまいます。

そこで、SQLのパフォーマンスチューニングです！

SQLの書き方を見直すことで、元の数倍の速度で処理ができるようになることもあります。
以前、会社のとある案件で、SQLを見直したら動作が数十倍速くなったこともあるそうです。

では今回のワークショップです！

ワークショップ担当の社員から、とあるSQL文が渡されました。
このSQLはパフォーマンスがとても悪いそうです。
これを、複数のチームに別れてパフォーマンスチューニングしていきます。

渡されたSQLに、

• インデックスを張る
• 1対多の関係をできるだけ排除する ⇒ INNER JOIN文をEXISTSに置き換える（場合による）
• 不要な重複を作らない

といった処理をしていきます。

時間が限られているため、どれを優先して行うかも勝負の鍵を握ります！

インデックスとは、索引みたいなもので、

• データを絞り込むために使用する列
• 表の結合条件の列
• データの並び替えやグループ分けで使用する列

に張ると処理が高速になります。

これらを含めて作業開始です・・・！
まずどの作業をするかチーム内で話し合いました。
まず始めに、渡されたSQLの実行に掛かる時間を調べました。

EXPLAIN ANALYZE 
SELECT DISTINCT
  u.name 
FROM
  users AS u 
  LEFT JOIN orders AS o 
    ON u.id = o.user_id 
  LEFT JOIN order_details AS od 
    ON od.order_id = o.id 
  LEFT JOIN order_addresses AS oa 
    ON oa.order_id = o.id 
WHERE
  oa.address LIKE '会津若松%' 
  AND o.order_date > '2012-01-01' 
  AND od.price > 50000 
ORDER BY
  u.name

実行するSQL文の前にEXPLAIN ANALYZEを付けると実行時間や、コストを表示することができます。
初期のSQL文の実行速度は、約20000ms(20秒)でした。

さて、ここからチューニング開始です！
ある人は先にインデックスを貼ろう！と言ったり、また別の人は先にINNER JOINをEXISTSに置き換えようと言ったり・・・。

私たちのチームはdistinctで重複してるレコードを排除することから始めました。
SELECT句にdistinctを付けて・・・あれ、あまり早くならない。
少ししか効果がないと言っていたのでこんなものなのかな？

次にインデックスを貼る事にしました。
検索条件に使うname, address, order_dateに貼って・・・
CREATE INDEX インデックス名 ON テーブル名(カラム名)
おお、中々早くなった。

最後にEXISTSで副問い合わせ。
私達のチームはここで苦戦。あれ、そもそもEXISTSって何だ・・・という状態に＾＾；
インターネットで調べつつ何とか完成はできたものの、WHERE句で各IDを結びつけるのを忘れていて全く別の処理に・・・。
それぞれのチームの結果はこうなりました！

• team A ("Total runtime: 207.238 ms")

CREATE INDEX index_order_details ON order_details(order_id); 
CREATE INDEX index_order_addresses ON order_addresses(order_id); 
CREATE INDEX index_orders ON orders(user_id);

SELECT
  u.* 
FROM
  users AS u 
WHERE
  EXISTS ( 
    SELECT
      o 
    FROM
      orders AS o 
    WHERE
      u.id = o.user_id 
      AND o.order_date > '2012-01-01' 
      AND EXISTS ( 
        SELECT
          od 
        FROM
          order_details AS od 
        WHERE
          od.order_id = o.id 
          AND od.price > 50000
      ) 
      AND EXISTS ( 
        SELECT
          oa 
        FROM
          order_addresses AS oa 
        WHERE
          oa.order_id = o.id 
          AND oa.address LIKE '会津若松%'
      )
  ) 
ORDER BY
  u.name; 

• team B (exists内でuserとひもづいていないため、SQL間違い)

CREATE INDEX username ON users(name); 
CREATE INDEX order_address ON order_addresses(address); 
CREATE INDEX order_date ON orders(order_date); 
CREATE INDEX order_details_price ON order_details(price);

SELECT DISTINCT
  u.id
  , u.name 
FROM
  users AS u 
  LEFT JOIN orders as o 
    ON u.id = o.user_id 
WHERE
  o.order_date > '2012-01-01' 
  AND EXISTS ( 
    SELECT
      * 
    FROM
      order_details 
    WHERE
      price > 50000 
      AND EXISTS ( 
        SELECT
          * 
        FROM
          order_addresses 
        WHERE
          address LIKE '会津若松%'
      )
  ) 
ORDER BY
  u.name; 

• team C ("Total runtime: 498.736 ms")

select
  u.id
  , u.name 
from
  users as u 
where
  exists ( 
    select
      null 
    from
      ( 
        select
          o.user_id 
        from
          orders as o 
        where
          exists ( 
            select
              null 
            from
              order_details as od 
            where
              od.price > 50000 
              and od.order_id = o.id
          ) 
          and exists ( 
            select
              null 
            from
              order_addresses as oa 
            where
              oa.address like '会津若松%' 
              and oa.order_id = o.id
          ) 
          and o.order_date > '2012-01-01'
      ) as k 
    where
      k.user_id = u.id
  ) 
order by
  u.name; 

• team D ("Total runtime: 209.447 ms")

select
  u.* 
from
  users as u 
  left join orders as o 
    ON u.id = o.user_id 
where
  exists ( 
    select
      * 
    from
      order_addresses as oa 
    where
      oa.order_id = u.id 
      and oa.address like '会津若松%'
  ) 
  and exists ( 
    select
      * 
    from
      order_details as od 
    where
      od.order_id = u.id 
      and od.price > 50000
  ) 
  and exists ( 
    select
      * 
    from
      orders as o 
    where
      u.id = o.user_id 
      and o.order_date > '2012-01-01'
  ) 
group by
  u.id 
having
  count(u.id) > 1 
order by
  u.name; 

• team E (exists内でuserとひもづいていないため、SQL間違い)

select
  u.id
  , u.name 
from
  users as u 
where
  exists ( 
    select
      null 
    from
      ( 
        select
          o.user_id 
        from
          orders as o 
        where
          exists ( 
            select
              null 
            from
              order_details as od 
            where
              od.price > 50000 
              and od.order_id = o.id
          ) 
          and exists ( 
            select
              null 
            from
              order_addresses as oa 
            where
              oa.address like '会津若松%' 
              and oa.order_id = o.id
          ) 
          and o.order_date > '2012-01-01'
      ) as k 
    where
      k.user_id = u.id
  ) 
order by
  u.name; 

回答（"Total runtime: 199.284 ms"）

create index order_user_id_index ON orders(user_id); 
create index oder_order_date_index ON orders(order_date); 
create index od_order_id_index ON order_details(order_id);
create index od_price_index on order_details(price); 
create index oa_order_index on order_addresses(order_id); 
create index oa_address_index on order_addresses(address); 
create index user_name_index on users(name);

select
  u.id, u.name 
from
  users as u 
where
  EXISTS ( 
    select
      * 
    from
      orders o 
    where
      u.id = o.user_id 
      and o.order_date > '2012-01-01' 
      and EXISTS ( 
        select
          * 
        from
          order_details od 
        where
          o.id = od.order_id 
          and od.price > 50000
      ) 
      and EXISTS ( 
        select
          * 
        from
          order_addresses oa 
        where
          o.id = oa.order_id 
          and oa.address like '会津若松%'
      )
  ) 
order by
  u.name

20秒が0.2秒になりました。
ハードウェアのコストを掛けずにパフォーマンスが改善される・・・SQLパフォーマンスチューニング素晴らしい！
業務にも活かしたいと思います！ありがとうございました！