【SQL】 自己相関サブクエリ でパフォーマンスが悪かった話とその解決策

DBパフォーマンス

■ はじめに

自己相関サブクエリ でパフォーマンスが悪かったので
その際の解決策などを記録する

目次

【１】現象
【２】動作環境
【３】パフォーマンスが悪かったSQL文
【４】原因
【５】解決案
　１）インデックス追加
　２）SQL文変更

【１】現象

* 50万レコードほどのテーブルで相関サブクエリを使った結果、
　数十分掛かってしまった

【２】動作環境

 * OS : CentOS7
 * DB : MySQL

【３】パフォーマンスが悪かったSQL文

イメージ 

SELECT
 (中略)
 main_ol.remarks AS remarks
FROM
 user AS u
 (中略)
INNER JOIN
  operation AS o
ON
  (中略)
LEFT OUTER JOIN
 operation_log AS main_ol
ON
 u.id = main_ol.user_id
 AND o.operation_type = main_ol.operation_type
 AND main_ol.operation_level IN ('1', '2') -- '1':ERROR, '2':WARN
 AND main_ol.operation_datetime =
  -- ★最新のoperation_datetimeを取得
  (SELECT
     MAX(sub_ol.operation_datetime)
   FROM
     operation_log AS sub_ol
   WHERE
      sub_ol.user_id = main_ol.user_id
      AND sub_ol.operation_type = main_ol.operation_type
      AND sub_ol.operation_level IN ('1', '2') -- '1':ERROR, '2':WARN
      AND sub_ol.operation_datetime < CURRENT_TIMESTAMP
   GROUP BY
     sub_ol.user_id,
     sub_ol.operation_type

【４】原因

 * 最新のoperation_datetimeを取得するための自己相関サブクエリ（★）が
   毎行動作してしまい、使いまわしができていない

【５】解決案

 * 「１）インデックス追加」および「２）SQL文変更」で解決できた

１）インデックス追加

 * インデックス追加については、条件に関わる項目に対して行う
 * 複数の場合、複合インデックスで追加する
  => 上記のSQL文だと「user_id」「operation_type」「operation_level」「operation_datetime」
 * どの位、どの順番で行うかは、実際のデータを使い、検証して決める

２）SQL文変更

* 処理が使い回しできるようなSQL文に工夫

イメージ 

SELECT
 (中略)
 main_ol.remarks AS remarks
FROM
 user AS u
 (中略)
INNER JOIN
  operation AS o
ON
  (中略)
LEFT OUTER JOIN
 -- (iii) operation IDに紐づく remarks を取得
 (SELECT
   sub_ol.user_id AS user_id,
   sub_ol.operation_type AS operation_type,
   sub_ol.remarks AS remarks
  FROM
   operation_log AS sub_ol
  INNER JOIN
    -- (ii) user_id / operation_type / operation_datetime 単位（※１）で、最大operation IDを取得
    (SELECT
       latest_ol.user_id AS user_id,
       latest_ol.operation_type AS operation_type,
       MAX(latest_ol.id) AS latest_id
     FROM
       operation_log AS latest_ol
     INNER JOIN
       -- (i) user_id / operation_type 単位（※１）で、最新operation_datetimeを取得
       (SELECT
          latest_each_usr_ol.user_id AS user_id,
          latest_each_usr_ol.operation_type AS operation_type,
          MAX(latest_each_usr_ol.operation_datetime) AS latest_operation_datetime
        FROM
          operation_log AS latest_each_usr_ol
        WHERE
          latest_each_usr_ol.operation_datetime < CURRENT_TIMESTAMP
          AND latest_each_usr_ol.operation_level IN ('1', '2') -- '1':ERROR, '2':WARN
        GROUP BY
          latest_each_usr_ol.user_id,
          latest_each_usr_ol.operation_type
        ) AS leu_ol
     ON
       latest_ol.user_id = leu_ol.user_id,
       latest_ol.operation_type = leu_ol.operation_type,
       latest_ol.operation_datetime = leu_ol.operation_datetime
     WHERE
       latest_ol.operation_level IN ('1', '2') -- '1':ERROR, '2':WARN
     GROUP BY
       latest_ol.user_id,
       latest_ol.operation_type
       latest_ol.operation_datetime
    ) AS l_ol
    ON
      sub_ol.id = l_ol.id
 ) AS main_ol
 ON
   u.id = main_ol.user_id
   AND o.operation_type = main_ol.operation_type

補足 ※１：XXXX単位について 

 * GROUP BY句により、実現

補足 ※２：修正したSQL文について 

* 以下のようなイメージで間引いていく

【間引きのイメージ】 

+--(i) user_id / operation_type 単位で、最新operation_datetimeを取得 ---------------------------+
|                                                                                               |
|  +--(ii) user_id / operation_type / operation_datetime 単位で、最大operation IDを取得 --+     |
|  |                                                                                      |     |
|  | +--(iii) operation IDに紐づく remarks を取得 --------------------------------+       |     |
|  | |                                                                            |       |     |
|  | |                                                                            |       |     |
|  | |                                                                            |       |     |
|  | +----------------------------------------------------------------------------+       |     |
|  |                                                                                      |     |
|  +--------------------------------------------------------------------------------------+     |
|                                                                                               |
+-----------------------------------------------------------------------------------------------+