MySQL에서 Alias 사용 시에 불필요한 sorting 부하를 발생 시키지 않으려면 

SELECT list에 있는 가공된 컬럼명과 구분하여 기존 테이블의 컬럼명 앞에 테이블 Alias를 사용하자

 

[튜닝 전]

order by first_name 

order by 절에 Select list의 가공된 컬럼명 사용으로 불필요한 Sort 발생

explain analyze         
select /*+INDEX(a employees_ix1) */
      a.emp_no, a.birth_date, substr(a.first_name,1,3) first_name
 from employees a 
where hire_date = '1986-01-01'
order by first_name
limit 10
;

-> Limit: 10 row(s)  (cost=28 rows=10) (actual time=0.25..0.251 rows=10 loops=1)
    -> Sort: first_name, limit input to 10 row(s) per chunk  (cost=28 rows=80) (actual time=0.249..0.25 rows=10 loops=1)
        -> Index lookup on a using employees_ix1 (hire_date = DATE'1986-01-01')  (cost=28 rows=80) (actual time=0.103..0.22 rows=80 loops=1)

 

 

[튜닝 후]

order by a.first_name 

order by 절에 컬럼명 앞에 테이블 alias 사용으로 정렬 발생하지 않음

explain analyze         
select /*+INDEX(a employees_ix1) */
      a.emp_no, a.birth_date, substr(a.first_name,1,3) first_name
 from employees a 
where hire_date = '1986-01-01'
order by a.first_name
limit 10
;

-> Limit: 10 row(s)  (cost=21 rows=10) (actual time=0.0981..0.1 rows=10 loops=1)
    -> Index lookup on a using employees_ix1 (hire_date = DATE'1986-01-01')  (cost=21 rows=60) (actual time=0.0975..0.0991 rows=10 loops=1)

'MySQL > 튜닝' 카테고리의 다른 글

MySQL ONLY_FULL_GROUP_BY  (0) 2024.12.27
MySQL 조인절에 사용한 함수 > inline view 이용  (0) 2024.12.26
MySQL IFNULL 조건절에 사용 시 주의  (0) 2024.12.24
MySQL UNNESTING 불가 > INLINE View  (0) 2024.12.24
MySQL Sub Query > Window Function  (1) 2024.12.24

IFNULL을 이용해서 바인드 변수값이 있으면 조회하고 없으면 전체 쿼리를 조회할 수 있다.

단, IFNULL에 쓰이는 조건이 인덱스 범위 검색에 쓰이지 못하는 문제가 발생한다.

 

[튜닝 전]

with index condition: (a.first_name = ifnull('Shao',a.first_name)

explain analyze    
select *
 from employees a 
where hire_date = '1986-01-01'
  and first_name = ifnull(:first_name, a.first_name)
 ;

emp_no|birth_date|first_name|last_name|gender|hire_date |
------+----------+----------+---------+------+----------+
421721|1953-09-26|Shao      |Reinhart |F     |1986-01-01|


-> Index lookup on a using employees_ix1 (hire_date = DATE'1986-01-01'), with index condition: (a.first_name = ifnull('Shao',a.first_name))  (cost=20.8 rows=80) (actual time=0.0378..0.0391 rows=1 loops=1)

 

인덱스 범위 조건에 사용될 수 없어도 인덱스 조건을 같이 넘겨서 인덱스를 MySQL엔진에서 사용한다면 using index condition이 발생된다.

 

즉, ifnull을 사용해서 first_name이 인덱스 범위 검색 조건에 사용되지 못하여 INNO DB 스토리지 엔진에서 쓸데 없는 데이터를 읽고 MySQL 스토리지 엔진에서 필터링을 거치게 된다.

INNO DB 스토리지 엔진 MySQL 스토리지 엔진           사용자
hire_date = DATE'1986-01-01'  a.first_name = ifnull('Shao',a.first_name)

 

 

[튜닝 후]

:first_name이 무조건 들어오는 조건이라면 UNION ALL로 구현 할 필요 없이 아래 SQL문의 UNION ALL의 윗 구문만 사용한다.

explain analyze 
select *
 from employees a 
where hire_date = '1986-01-01'
  and :first_name is not null
  and first_name = :first_name
union all 
select *
 from employees a 
where hire_date = '1986-01-01'
  and :first_name is null
 ;

emp_no|birth_date|first_name|last_name|gender|hire_date |
------+----------+----------+---------+------+----------+
421721|1953-09-26|Shao      |Reinhart |F     |1986-01-01|

-> Append  (cost=0.35..0.35 rows=1) (actual time=0.0467..0.0508 rows=1 loops=1)
    -> Stream results  (cost=0.35 rows=1) (actual time=0.0453..0.0476 rows=1 loops=1)
        -> Index lookup on a using employees_ix1 (hire_date = DATE'1986-01-01', first_name = 'Shao')  (cost=0.35 rows=1) (actual time=0.0382..0.0404 rows=1 loops=1)
    -> Stream results  (cost=0..0 rows=0) (actual time=737e-6..737e-6 rows=0 loops=1)
        -> Zero rows (Impossible WHERE)  (cost=0..0 rows=0) (actual time=85e-6..85e-6 rows=0 loops=1)

'MySQL > 튜닝' 카테고리의 다른 글

MySQL 조인절에 사용한 함수 > inline view 이용  (0) 2024.12.26
MySQL Alias 사용 시 주의사항  (0) 2024.12.24
MySQL UNNESTING 불가 > INLINE View  (0) 2024.12.24
MySQL Sub Query > Window Function  (1) 2024.12.24
MySQL DELETE & JOIN  (0) 2024.12.24

 

CONCAT

문자열 합치기

 select gender, concat(last_name , ' ', first_name), emp_no
   from employees a 
  where hire_date BETWEEN '1986-01-01' and '1986-01-01'
  limit 10
  ;

gender|concat(last_name , ' ', first_name)|emp_no|
------+-----------------------------------+------+
M     |Lores Hausi                        |434949|
F     |Vingron Boriana                    | 89812|
M     |Harbusch Hercules                  |225277|
F     |Shobatake Zita                     |208807|
M     |Famili Ziyad                       |221613|
F     |Soicher Jaihie                     |465446|
M     |McConalogue Moie                   | 72864|
F     |Shumilov Subir                     |259440|
F     |Reinhart Shao                      |421721|
F     |Prenel Holgard                     |214099|

 

GROUP_CONCAT

그룹별로 문자열 합치기

 select gender, GROUP_CONCAT(emp_no, ',') 
   from employees a 
  where hire_date BETWEEN '1986-01-01' and '1986-01-01'
  group by gender
  ;
  
  gender|GROUP_CONCAT(emp_no, ',')                                                                                                                                                                                                                                      |
------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
M     |100711,,103944,,487879,,476952,,232578,,219853,,42013,,37711,,492784,,482916,,231980,,417922,,424450,,498761,,434949,,202218,,52692,,445833,,262950,,458582,,248293,,104764,,29708,,31961,,26365,,430556,,108318,,225277,,72864,,16483,,221613,,448755,,415376,|
F     |279357,,13094,,262840,,441840,,104083,,232210,,48489,,14845,,255744,,100812,,224714,,89812,,208807,,465446,,259440,,421721,,214099,,224288,,13415,,469053,,265749,,47413,,22576,,65089,,245335,,411185,,219203,,29119,,417105,,200640,,433424,,77007,,496085,  |

 

 

||

first_name = 'Sha' 로 검색하여 쿼리의 결과가 없다.

select gender, concat(last_name , ' ', first_name), emp_no, hire_date, first_name, last_name
  from employees a 
 where hire_date BETWEEN '1986-01-01' and '1986-01-01'
   and first_name = 'Sha'||'o'
 limit 10
;

--결과 없음

-> Limit: 10 row(s)  (cost=0.35 rows=1) (actual time=0.0115..0.0115 rows=0 loops=1)
    -> Index lookup on a using employees_ix1 (hire_date = DATE'1986-01-01', first_name = 'Sha')  (cost=0.35 rows=1) (actual time=0.0108..0.0108 rows=0 loops=1)

 

 

 

CONCAT

first_name = concat('Sha','o') 로 검색하여 결과값이 1건 나온다.

주의) 문자열 합치는 것은 CONCAT 사용해야 결과가 달라지지 않는다!!

select gender, concat(last_name , ' ', first_name), emp_no, hire_date, first_name, last_name
  from employees a 
 where hire_date BETWEEN '1986-01-01' and '1986-01-01'
   and first_name = concat('Sha','o')
 limit 10
;

gender|concat(last_name , ' ', first_name)|emp_no|hire_date |first_name|last_name|
------+-----------------------------------+------+----------+----------+---------+
F     |Reinhart Shao                      |421721|1986-01-01|Shao      |Reinhart |


-> Limit: 10 row(s)  (cost=0.35 rows=1) (actual time=0.031..0.0332 rows=1 loops=1)
    -> Index lookup on a using employees_ix1 (hire_date = DATE'1986-01-01', first_name = concat('Sha','o'))  (cost=0.35 rows=1) (actual time=0.0303..0.0323 rows=1 loops=1)

'MySQL > MySQL' 카테고리의 다른 글

함수 생성 시 주의사항  (0) 2024.12.26
DATE_FORMAT  (0) 2024.12.24
AUTO_INCREMENT  (0) 2024.12.20
숫자(정수,소수점,DECIMAL)  (0) 2024.12.20
문자열 이스케이프(ESCAPE)  (0) 2024.12.20

MySQL에서 서브쿼리가 자동적으로 Unnesting이 적용되지 않아서 인덱스 활용이 제대로 되지 않을 때

쿼리를  Inline View로 변경하여 작성한다.

 

[인덱스 생성]

create index employees_ix1 on employees(gender, birth_date);
create index employees_ix2 on employees(hire_date, birth_date);

 

[튜닝 전]

서브쿼리가 Unnesting 되지 않아서 a 테이블을 먼저 읽고 동작한다. (index에서 gender 값만 읽음)

Unnesting 된다면 서브쿼리가 먼저 실행되고 birth_date값을 넘겨줘 a 테이블에서 gender+birth_date 인덱스를 활용한다.

explain
select a.*
  from employees a 
 where a.gender = 'M'
   and a.birth_date in (
			select birth_date
			from employees
			where hire_date BETWEEN '1986-01-01' AND '1986-01-01'
			union all
			select birth_date
			from employees
			where hire_date BETWEEN '1986-01-02' AND '1986-01-02'
            )
;


id|select_type       |table    |partitions|type|possible_keys|key          |key_len|ref       |rows  |filtered|Extra                             |
--+------------------+---------+----------+----+-------------+-------------+-------+----------+------+--------+----------------------------------+
 1|PRIMARY           |a        |          |ref |employees_ix1|employees_ix1|1      |const     |149601|   100.0|Using index condition; Using where|
 2|DEPENDENT SUBQUERY|employees|          |ref |employees_ix2|employees_ix2|6      |const,func|     1|   100.0|Using index                       |
 3|DEPENDENT UNION   |employees|          |ref |employees_ix2|employees_ix2|6      |const,func|     1|   100.0|Using index                       |
 

explain analyze
select a.*
  from employees a 
 where a.gender = 'M'
   and a.birth_date in ( /* 비 연관 서브쿼리 중복 제거 -> distinct 된 결과 집합(필터 역할) */
			select birth_date
			from employees
			where hire_date BETWEEN '1986-01-01' AND '1986-01-01'
			union all
			select birth_date
			from employees
			where hire_date BETWEEN '1986-01-02' AND '1986-01-02'
            )
;
-> Filter: <in_optimizer>(a.birth_date,<exists>(select #2))  (cost=15657 rows=149601) (actual time=5.31..2154 rows=6436 loops=1)
    -> Index lookup on a using employees_ix1 (gender = 'M'), with index condition: (a.gender = 'M')  (cost=15657 rows=149601) (actual time=3.6..731 rows=179973 loops=1)
    -> Select #2 (subquery in condition; dependent)
        -> Limit: 1 row(s)  (cost=2.2 rows=1) (actual time=0.00685..0.00685 rows=0.0358 loops=179973)
            -> Append  (cost=2.2 rows=2) (actual time=0.00669..0.00669 rows=0.0358 loops=179973)
                -> Stream results  (cost=1.1 rows=1) (actual time=0.00323..0.00323 rows=0.0165 loops=179973)
                    -> Limit: 1 row(s)  (cost=1.1 rows=1) (actual time=0.00307..0.00307 rows=0.0165 loops=179973)
                        -> Covering index lookup on employees using employees_ix2 (hire_date = DATE'1986-01-01', birth_date = <cache>(a.birth_date))  (cost=1.1 rows=1) (actual time=0.00293..0.00293 rows=0.0165 loops=179973)
                -> Stream results  (cost=1.1 rows=1) (actual time=0.00307..0.00307 rows=0.0196 loops=177009)
                    -> Limit: 1 row(s)  (cost=1.1 rows=1) (actual time=0.00288..0.00288 rows=0.0196 loops=177009)
                        -> Covering index lookup on employees using employees_ix2 (hire_date = DATE'1986-01-02', birth_date = <cache>(a.birth_date))  (cost=1.1 rows=1) (actual time=0.00274..0.00274 rows=0.0196 loops=177009)

 

 

[튜닝 후]

서브쿼리를 Inline view로 변경하여 중복 제거(Union)하고 a 테이블과 조인한다. 이때 birth_date 값이 넘어가면서 a 테이블의 gender+birth_date 인덱스를 활용한다. 

explain
select a.*
  from employees a 
     ,(select birth_date
	   from employees
	   where hire_date BETWEEN '1986-01-01' AND '1986-01-01'
	    union /* 중복 제거 */
	   select birth_date
	    from employees
	    where hire_date BETWEEN '1986-01-02' AND '1986-01-02'
      ) b 
 where a.gender = 'M'
   and a.birth_date = b.birth_date   
;
id|select_type |table     |partitions|type|possible_keys|key          |key_len|ref               |rows|filtered|Extra                |
--+------------+----------+----------+----+-------------+-------------+-------+------------------+----+--------+---------------------+
 1|PRIMARY     |<derived2>|          |ALL |             |             |       |                  | 173|   100.0|                     |
 1|PRIMARY     |a         |          |ref |employees_ix1|employees_ix1|4      |const,b.birth_date|  31|   100.0|Using index condition|
 2|DERIVED     |employees |          |ref |employees_ix2|employees_ix2|3      |const             |  80|   100.0|Using index          |
 3|UNION       |employees |          |ref |employees_ix2|employees_ix2|3      |const             |  93|   100.0|Using index          |
 4|UNION RESULT|<union2,3>|          |ALL |             |             |       |                  |    |        |Using temporary      |
 
explain analyze
select a.*
  from employees a 
     ,(select birth_date
	   from employees
	   where hire_date BETWEEN '1986-01-01' AND '1986-01-01'
	    union /* 중복 제거 */
	   select birth_date
	    from employees
	    where hire_date BETWEEN '1986-01-02' AND '1986-01-02'
      ) b 
 where a.gender = 'M'
   and a.birth_date = b.birth_date   
;

-> Nested loop inner join  (cost=1969 rows=5442) (actual time=0.7..35.5 rows=6436 loops=1)
    -> Table scan on b  (cost=59.4..64 rows=173) (actual time=0.279..0.392 rows=171 loops=1)
        -> Union materialize with deduplication  (cost=59.4..59.4 rows=173) (actual time=0.277..0.277 rows=171 loops=1)
            -> Covering index lookup on employees using employees_ix2 (hire_date = DATE'1986-01-01')  (cost=9.1 rows=80) (actual time=0.0244..0.0421 rows=80 loops=1)
            -> Covering index lookup on employees using employees_ix2 (hire_date = DATE'1986-01-02')  (cost=10.4 rows=93) (actual time=0.00877..0.0328 rows=93 loops=1)
    -> Index lookup on a using employees_ix1 (gender = 'M', birth_date = b.birth_date), with index condition: (a.gender = 'M')  (cost=7.88 rows=31.5) (actual time=0.156..0.202 rows=37.6 loops=171)

'MySQL > 튜닝' 카테고리의 다른 글

MySQL Alias 사용 시 주의사항  (0) 2024.12.24
MySQL IFNULL 조건절에 사용 시 주의  (0) 2024.12.24
MySQL Sub Query > Window Function  (1) 2024.12.24
MySQL DELETE & JOIN  (0) 2024.12.24
MySQL LATERAL  (0) 2024.12.24

 

테이블 3번 액세스 쿼리 => 한 번만 읽도록 변경

 

상황에 따라 테이블을 한번 액세스할지 2,3번 액세스 할지 결정 해야 한다.

아래의 예제는 3번 테이블을 엑세스 하는 것이 더 빠르고 비용도 적다. 

하지만 빅테이블의 경우에는 달라질지도 모르니 상황에 따라 선택해서 SQL을 사용하자.

 

[테이블 3번 엑세스]

EXPLAIN
SELECT a.*, b.*
  FROM salaries a
 INNER JOIN employees b 
   ON a.emp_no = b.emp_no AND b.gender ='M'
 WHERE a.emp_no between 10000 and 10099
   AND a.salary = (SELECT max(x.salary) FROM salaries x WHERE x.emp_no = a.emp_no)
   AND a.to_date = (SELECT max(x.to_date) FROM salaries x WHERE x.emp_no = a.emp_no)
;

id|select_type       |table|partitions|type |possible_keys|key    |key_len|ref               |rows|filtered|Extra      |
--+------------------+-----+----------+-----+-------------+-------+-------+------------------+----+--------+-----------+
 1|PRIMARY           |b    |          |range|PRIMARY      |PRIMARY|4      |                  |  99|    50.0|Using where|
 1|PRIMARY           |a    |          |ref  |PRIMARY      |PRIMARY|4      |employees.b.emp_no|   9|   100.0|Using where|
 3|DEPENDENT SUBQUERY|x    |          |ref  |PRIMARY      |PRIMARY|4      |employees.a.emp_no|   9|   100.0|           |
 2|DEPENDENT SUBQUERY|x    |          |ref  |PRIMARY      |PRIMARY|4      |employees.a.emp_no|   9|   100.0|           |


EXPLAIN ANALYZE
SELECT a.*, b.*
  FROM salaries a
 INNER JOIN employees b 
   ON a.emp_no = b.emp_no AND b.gender ='M'
 WHERE a.emp_no between 10000 and 10099
   AND a.salary = (SELECT max(x.salary) FROM salaries x WHERE x.emp_no = a.emp_no)
   AND a.to_date = (SELECT max(x.to_date) FROM salaries x WHERE x.emp_no = a.emp_no)
;
-> Nested loop inner join  (cost=116 rows=481) (actual time=0.365..4.58 rows=55 loops=1)
    -> Filter: ((b.gender = 'M') and (b.emp_no between 10000 and 10099))  (cost=20.1 rows=49.5) (actual time=0.0826..0.154 rows=63 loops=1)
        -> Index range scan on b using PRIMARY over (10000 <= emp_no <= 10099)  (cost=20.1 rows=99) (actual time=0.0313..0.075 rows=99 loops=1)
    -> Filter: ((a.salary = (select #2)) and (a.to_date = (select #3)))  (cost=0.985 rows=9.71) (actual time=0.0686..0.0691 rows=0.873 loops=63)
        -> Index lookup on a using PRIMARY (emp_no = b.emp_no)  (cost=0.985 rows=9.71) (actual time=0.00319..0.00507 rows=9.73 loops=63)
        -> Select #2 (subquery in condition; dependent)
            -> Aggregate: max(x.salary)  (cost=4.17 rows=1) (actual time=0.00534..0.00536 rows=1 loops=613)
                -> Index lookup on x using PRIMARY (emp_no = a.emp_no)  (cost=1.94 rows=9.71) (actual time=0.00283..0.00443 rows=11.8 loops=613)
        -> Select #3 (subquery in condition; dependent)
            -> Aggregate: max(x.to_date)  (cost=4.17 rows=1) (actual time=0.00515..0.00517 rows=1 loops=63)
                -> Index lookup on x using PRIMARY (emp_no = a.emp_no)  (cost=1.94 rows=9.71) (actual time=0.00275..0.00405 rows=9.73 loops=63)

 

 

 

[테이블 1번 엑세스] 

NO ROW_NUMBER DENSE_RANK RANK
1 1 1 1
2 2 1 1
3 3 2 3

 

EXPLAIN
SELECT *
FROM (
	SELECT a.emp_no, a.salary, a.from_date, a.to_date, b.birth_date, b.first_name, b.last_name, b.gender, b.hire_date
	      ,rank() over(partition by a.emp_no order by a.salary desc) rn1
	      ,rank() over(partition by a.emp_no order by a.to_date desc) rn2
	  FROM salaries a
	 INNER JOIN employees b 
	    ON a.emp_no = b.emp_no AND b.gender ='M'
	 WHERE a.emp_no between 10000 and 10099	   
) a 
WHERE a.rn1 = 1 
  AND a.rn2 = 1
;

id|select_type|table     |partitions|type  |possible_keys|key        |key_len|ref               |rows|filtered|Extra                                       |
--+-----------+----------+----------+------+-------------+-----------+-------+------------------+----+--------+--------------------------------------------+
 1|PRIMARY    |<derived2>|          |ref   |<auto_key0>  |<auto_key0>|16     |const,const       |  10|   100.0|                                            |
 2|DERIVED    |a         |          |range |PRIMARY      |PRIMARY    |4      |                  | 984|   100.0|Using where; Using temporary; Using filesort|
 2|DERIVED    |b         |          |eq_ref|PRIMARY      |PRIMARY    |4      |employees.a.emp_no|   1|    50.0|Using where                                 |
 
 
EXPLAIN ANALYZE
SELECT *
FROM (
	SELECT a.emp_no, a.salary, a.from_date, a.to_date, b.birth_date, b.first_name, b.last_name, b.gender, b.hire_date
	      ,rank() over(partition by a.emp_no order by a.salary desc) rn1
	      ,rank() over(partition by a.emp_no order by a.to_date desc) rn2
	  FROM salaries a
	 INNER JOIN employees b 
	    ON a.emp_no = b.emp_no AND b.gender ='M'
	 WHERE a.emp_no between 10000 and 10099	   
) a 
WHERE a.rn1 = 1 
  AND a.rn2 = 1
;

-> Index lookup on a using <auto_key0> (rn1 = 1, rn2 = 1)  (cost=0.35..3.51 rows=10) (actual time=5.65..5.67 rows=55 loops=1)
    -> Materialize  (cost=0..0 rows=0) (actual time=5.62..5.62 rows=613 loops=1)
        -> Window aggregate: rank() OVER (PARTITION BY a.emp_no ORDER BY a.to_date desc )   (actual time=3.63..4.33 rows=613 loops=1)
            -> Sort: a.emp_no, a.to_date DESC  (actual time=3.62..3.72 rows=613 loops=1)
                -> Table scan on <temporary>  (cost=2.5..2.5 rows=0) (actual time=3.15..3.27 rows=613 loops=1)
                    -> Temporary table  (cost=0..0 rows=0) (actual time=3.14..3.14 rows=613 loops=1)
                        -> Window aggregate: rank() OVER (PARTITION BY a.emp_no ORDER BY a.salary desc )   (actual time=1.66..2.55 rows=613 loops=1)
                            -> Sort: a.emp_no, a.salary DESC  (actual time=1.65..1.76 rows=613 loops=1)
                                -> Stream results  (cost=543 rows=492) (actual time=0.114..1.39 rows=613 loops=1)
                                    -> Nested loop inner join  (cost=543 rows=492) (actual time=0.105..1.01 rows=613 loops=1)
                                        -> Filter: (a.emp_no between 10000 and 10099)  (cost=199 rows=984) (actual time=0.09..0.416 rows=984 loops=1)
                                            -> Index range scan on a using PRIMARY over (10000 <= emp_no <= 10099)  (cost=199 rows=984) (actual time=0.088..0.345 rows=984 loops=1)
                                        -> Filter: (b.gender = 'M')  (cost=0.25 rows=0.5) (actual time=471e-6..507e-6 rows=0.623 loops=984)
                                            -> Single-row index lookup on b using PRIMARY (emp_no = a.emp_no)  (cost=0.25 rows=1) (actual time=326e-6..345e-6 rows=1 loops=984)

'MySQL > 튜닝' 카테고리의 다른 글

MySQL IFNULL 조건절에 사용 시 주의  (0) 2024.12.24
MySQL UNNESTING 불가 > INLINE View  (0) 2024.12.24
MySQL DELETE & JOIN  (0) 2024.12.24
MySQL LATERAL  (0) 2024.12.24
MySQL WITH문에 상수, 바인드 변수, 사용자 정의 변수 "@" 사용 테스트  (0) 2024.12.23

[튜닝 전]

원본테이블의 크기가 너무커서 아래와 같이 실행하면 불필요한 시간이 소요된다. 

DELETE A FROM 원본테이블 A
 WHERE EXISTS (SELECT 'X'
                 FROM 삭제집합생성 X 
                WHERE A.PK_COLUMN1=X.PK_COLUMN1
                  AND A.PK_COLUMN2=X.PK_COLUMN2);

-----------------------------------------------------------------------------------
ID  select_type         table   type    key      ref                        Extra
-----------------------------------------------------------------------------------
1   PRIMARY             A       ALL                                         Using where
2   DEPENDENT SUBQUERY  X       eq_ref  PRIMARY  A.PK_COLUMN1,A.PK_COLUMN2  Using index
-----------------------------------------------------------------------------------

 

[튜닝 후]

순서조정을 못하여서 JOIN으로 변경 후 실행 한다.

실행계획이 바뀌면서 작은집합이 드라이빙테이블이 되어서 소요시간이 줄어든다.

튜닝 후 :
DELETE A FROM 원본테이블 A
   INNER JOIN 삭제집합 X
           ON A.PK_COLUMN1=X.PK_COLUMN1
          AND A.PK_COLUMN2=X.PK_COLUMN2;

-----------------------------------------------------------------------------------
ID  select_type   table   type    key      ref                        Extra
-----------------------------------------------------------------------------------
1   SIMPLE        X       index   PRIMARY                             Using index
2   SIMPLE        A       eq_ref  PRIMARY  X.PK_COLUMN1,X.PK_COLUMN2  
-----------------------------------------------------------------------------------
 

'MySQL > 튜닝' 카테고리의 다른 글

MySQL UNNESTING 불가 > INLINE View  (0) 2024.12.24
MySQL Sub Query > Window Function  (1) 2024.12.24
MySQL LATERAL  (0) 2024.12.24
MySQL WITH문에 상수, 바인드 변수, 사용자 정의 변수 "@" 사용 테스트  (0) 2024.12.23
MySQL : events_statements_summary_by_digest  (0) 2024.12.23

+ Recent posts

티스토리툴바