MySQL outer join or조건 튜닝

OUTER JOIN OR 조건 튜닝

쿼리 검수를 하는데 처음 접하는 쿼리를 보게 되어 당황스러웠던 경험이 있습니다.
바로 LEFT OUTER JOIN 의 JOIN 절에 OR 조건이 달려있는 쿼리였는데요.
항상 JOIN on a.id = b.id 같은 단일조건, 조건이 더 달려 봤자 AND 조건이었는데 OR 조건은 처음 접하는 경험이었는데요
저는 LEFT OUTER JOIN 절에 OR 조건이 포함되면 인덱스를 타지못해 성능이 좋지 않아 아래와 같이 쿼리를 튜닝했습니다.

쿼리 확인

select
  tb1.`ymdt` as col_0_0_,
  tb1.`amount` as col_1_0_,
.
.
.
  tb4.`c_id` as col_10_0_,
  tb4.`issue_id` as col_11_0_
from
  tb_test1 tb1
  inner join tb_test2 tb2 on tb1.`order_no` = tb2.`order_no`
  inner join `tb_test3` tb3 on tb1.`order_no` = tb3.`order_no`
  left outer join tb_test4 tb4 on (
    tb4.`no` = tb1.`no`
    or tb4.`no` = tb1.`origin_no` )
where
  tb1.`ymdt` >= ?
  and tb1.`ymdt` < ?
  and exists (
      select '1' from tb_test5 tb5 where tb5.`order_no` = tb1.`order_no`
        and ( tb5.`coupon_id` in ( ? )) )
order by 
.
.

실행계획

+----+-------------+-----------+------------+------+----------------------------------+---------------+---------+------------------------------+---------+----------+--------------------------------------------------+
| id | select_type | table     | partitions | type | possible_keys                    | key           | key_len | ref                          | rows    | filtered | Extra                                            |
+----+-------------+-----------+------------+------+----------------------------------+---------------+---------+------------------------------+---------+----------+--------------------------------------------------+
|  1 | SIMPLE      | tb5       | p202002,....| ref  | ink1,ink4                        | ink4          | 82      | const                        |       1 |   100.00 | Using temporary; Using filesort; Start temporary |
|  1 | SIMPLE      | tb1       | p202010     | ref  | ink01, ink03,idx2                | ink03         | 82      | tb5.no                       |       1 |    23.93 | Using index condition; End temporary             |
|  1 | SIMPLE      | tb2       | p202002,....| ref  | PRIMARY,idx1                     | PRIMARY       | 82      | tb5.no                       |       1 |   100.00 | NULL                                             |
|  1 | SIMPLE      | tb3       | p202002,....| ref  | PRIMARY                          | PRIMARY       | 82      | tb5.order_no                 |       1 |   100.00 | NULL                                             |
|  1 | SIMPLE      | tb4       | p202002,....| ALL  | PRIMARY                          | NULL          | NULL    | NULL                         | 4605120 |   100.00 | Range checked for each record (index map: 0x1)   |
+----+-------------+-----------+------------+------+----------------------------------+---------------+---------+------------------------------+---------+----------+--------------------------------------------------+

=> 실행 계획이 잘나가다가 tb4 부분을 보면 tb4 를 조인할 때 풀스캔을 하면서 수많은 row를 읽게 됩니다.
LEFT OUTER JOIN 절에 OR 로 인해서 인덱스를 타지 못해서 엄청난 비효율이 발생하고 있습니다.

쿼리 튜닝

select
  tb1.`ymdt` as col_0_0_,
  tb1.`amount` as col_1_0_,
.
.
.
  COALESCE(tb4.`c_id`, tb6.`c_id`) as col_10_0_, 
  COALESCE(tb4.`issue_id`, tb6.`issue_id`) as col_11_0_
from
  tb_test1 tb1
  inner join tb_test2 tb2 on tb1.`order_no` = tb2.`order_no`
  inner join `tb_test3` tb3 on tb1.`order_no` = tb3.`order_no`
  left outer join tb_test4 tb4 on ( tb4.`no` = tb1.`no` )
  left outer join tb_test4 tb6 on ( tb6.`no` = tb1.`origin_no`)
where
  tb1.`ymdt` >= ?
  and tb1.`ymdt` < ?
  and exists (
      select '1' from tb_test5 tb5 where tb5.`order_no` = tb1.`order_no`
        and ( tb5.`coupon_id` in ( ? )) )
order by 
.
.

• LEFT OUTER JOIN OR 부분을 컬럼 별로 JOIN 할 수 있도록 수정
• SELECT 절에서 기존 tb4 에서 가져오던 컬럼이 tb4, tb6 으로 나뉘어졌기 때문에 COALESCE 함수를 사용하도록 수정함

튜닝한 쿼리 실행계획

+----+-------------+------------+----------------+-------+-----------------------------+---------------+---------+-------+----------+------------------------------+
| id | select_type | table      | partitions     | type  | possible_keys               | key           | key_len | rows  | filtered | Extra                        |
+----+-------------+------------+----------------+-------+-----------------------------+---------------+---------+-------+----------+------------------------------+
|  1 | SIMPLE      | tb1        | p202010        | range | ink01,ink03                 | ink01         | 5       | 26702 |   100.00 | Using index condition        |
|  1 | SIMPLE      | tb4        | p202002,,....  | ref   | PRIMARY                     | PRIMARY       | 82      |     1 |   100.00 | NULL                         |
|  1 | SIMPLE      | tb6        | p202002,,....  | ref   | PRIMARY                     | PRIMARY       | 82      |     1 |   100.00 | Using index                  |
|  1 | SIMPLE      | tb5        | p202002,,....  | ref   | ink1,ink4                   | ink1          | 82      |     1 |    30.00 | Using where; Start temporary |
|  1 | SIMPLE      | tb2        | p202002,,....  | ref   | PRIMARY                     | PRIMARY       | 82      |     1 |   100.00 | NULL                         |
|  1 | SIMPLE      | tb3        | p202002,,....  | ref   | PRIMARY                     | PRIMARY       | 82      |     1 |   100.00 | End temporary                |
+----+-------------+------------+----------------+-------+-----------------------------+---------------+---------+-------+----------+------------------------------+