Let's have a cup of coffee.

개요 : 인덱스 x01 : col1, col2 , PK(YMD(파티션키), col3) 가 있을때 아래와 같은 상황이 발생한다.

전제 : 인덱스 확장 (use_index_extensions) 옵션이 on 

        -  InnoDB를 사용하는 테이블에서 Secondary Index에 자동으로 추가된 PK를 사용할지 말지 결정하는 것이다.

        -  조회 방법 : SELECT @@optimizer_switch;

설명 : InnoDB 스토리지 엔진의 세컨더리 인덱스는 PK를 값으로 가진다. 

        -  인덱스 x01 : col1, col2 는 col1, col2, YMD, col3 를 가진 것과 비슷하다.

[use_index_extensions 일어 날때]

• IX01 인덱스를 사용하면서 PK를 사용해 해당 파티션만을 조회 한다.
• 파티션 프루닝 정보 : explain partitions 사용

-- IX01 : STR + MD
-- PK : SYMD(파티션키) + COL1....파티션 키가 맨앞에 없더라도 파티션 프루닝이 일어난다. 하지만, index 선택을 잘 안하려는 현상 발생 FORCE INDEX() 힌트 사용

EXPLAIN ANALYZE
SELECT *
  FROM A
 WHERE STR = '9680'
   AND MD = '01' 
   AND SYMD BETWEEN '20240101' AND '20240131';
   
-> Index lookup on A using IX01 (STR='9680', MD='01'), with index condition:('20240101' <= SYMD <= '20240131') (cost=10.96 rows=44) (actual time=0.113..0.246 rows=48 loops=11281)

[use_index_extensions 안 일어 날 때]

• 인덱스 4개를 생성해도 PK를 사용해서 파티션 프러닝이 일어나지 않는다.
• 위아래 모두 YMD는 varchar(8) 이다.

IX01 : STCD, MCD
IX02 : STCD, MCD, SYMD
IX03 : STCD
IX04 : STCD, SYMD

PK : SYMD(파티션키), STCD, DVCD

SELECT *
 FROM A
WHERE A.SYMD BETWEEN '20240301' AND '20240331'
  AND A.STCD IN (SELECT MCD FROM M_CD_TAB)
  AND A.MCD IN ('1','2','3')
  
  
 -> Filter: ('20240301' <= A.SYMD <='20240331') and (A.MCD IN (<cache>('1'),<cache>('2'),<cache>('3')) (cost=9.46 rows=9) (actual time=70.077..176.655 rows=8 loops=6)
   -> Covering index lookup on A using IX03 (STCD='<subquery2>'.STCD) (cost=9.46 rows=419) (actual time=0.264..156.989 rows=57260 loops=6)

튜닝 중에 이유를 찾으면 수정.. 찾을 수 있을까?

똑같은 상황인데 파티션키를 포함해서 인덱스 탐색 조건에 파티션 키가 포함 안되는 이유는 무엇일까?

• 범위조건, 상수값이 아니라서?, IN절에 여러개의 값 포함(실제는 몇십개..) 

아래와 같이 상수 조건으로 입력시 IX01 인덱스와 PK컬럼을 이용하여 인덱스를 잘 타게 된다.

-- IX01 : STCD, MCD, SHCD

EXPLAIN ANALYZE
SELECT *
  FROM A 
 WHERE SYMD BETWEEN '20240101' AND '20240131'
   AND STCD = '01'
   AND MCD = '011'
;

-> Index lookup on A using IX01 (STCD='01', MCD='011') with index condition:(A.SYMD between '20240101' and '20240131') (cost=14.10 rows=47) (actual time=0.410..0.442 rows=47 loops=1)

개요 : MySQL SEMIJOIN 힌트 사용법

세미조인 최적화 4가지

*MySQL 5.5 이하 SubQuery 최적화 문제로 INNER JOIN 사용, 5.6이상 SEMIJOIN 사용 가능 

[튜닝 예제]

사용 힌트 권장(둘 중에 사용) :

/*+ SEMIJOIN(@subq1 FIRSTMATCH, DUPWEEDOUT) */

/*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPWEEDOUT) */

[튜닝 전 쿼리 & 실행계획]   3.679 (s)

A 테이블 PK : PKCOL, A_IX02 : YMD, DV_CD, DV_CD2, CD

X 테이블 PK : PKCOL1, PKNUM2, X_IX03 : TEL_DV_CD 

EXPLAIN ANALYZE
SELECT A.YMD, COUNT(*) CNT
  FROM A
 WHERE A.CD NOT IN ('22')
   AND A.DV_CD NOT IN ('01','02')
   AND A.YMD BETWEEN DATE_FORMAT(ADDDATE('20250101',-5),'%Y%m%d')
                 AND DATE_FORMAT(ADDDATE('20250101',5),'%Y%m%d')
   AND EXISTS(SELECT 1
                FROM X
               WHERE X.TEL_DV_CD = '00'
                 AND A.PKCOL = X.PKCOL1)
 GROUP BY A.XXX_YMD
 
 -> Table scan on <temporary> (actual time=0.002..0.007 rows=17 loops=1)
   -> Aggregate using temporary table (actual time=3679.117..3679.123 rows=17 loops=1)
     -> Nested loop inner join (cost=530627.28 rows=271591) (actual time=2213.917..3523.712 rows=216879 loops=1)
       -> Remove duplicates from input sorted on X_IX03 (cost=106973.58 rows=1039670) (actual time=0.050..670.777 rows=850280 loops=1)
         -> Covering index lookup on X on X_IX03 (TEL_DV_CD='00') (cost=106973.58 rows=1039670) (actual time=0.48..476.261 rows=862612 loops=1)
       -> Filter: (A.CD <> '22') and (A.DV_CD not in ('01','02')) and (A.YMD between<cache>(date_format(('20250101' + interval -(5) day),'%Y%m%d')) and <cache>(date_format(('20250101' + interval 5 day),'%Y%m%d')))) (cost=319686.73 rows=0) (actual time=0.003..0.003 rows=0 loops=850280)
         -> Single-row index lookup on A using PRIMARY (PKCOL=X.PKCOL1) (cost=319686.73 rows=1) (actual time=0.003..0.003 rows=1 loops=850280)

[튜닝 후 쿼리 & 실행계획1]   1.448(s)

EXPLAIN ANALYZE
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ A.YMD, COUNT(*) CNT
  FROM A
 WHERE A.CD NOT IN ('22')
   AND A.DV_CD NOT IN ('01','02')
   AND A.YMD BETWEEN DATE_FORMAT(ADDDATE('20250101',-5),'%Y%m%d')
                 AND DATE_FORMAT(ADDDATE('20250101',5),'%Y%m%d')
   AND EXISTS(SELECT 1
                FROM X
               WHERE X.TEL_DV_CD = '00'
                 AND A.PKCOL = X.PKCOL1)
 GROUP BY A.XXX_YMD
 
 -> Group aggregate: count(*) (cost=70011670865.49 rows=350057705667) (actual time=946.385..1467.407 rows=17 loops=1)
   -> Nested loop inner join (cost=35005900298.81 rows=350057705667) (actual time=939.509..1448.989 rows=216870 loops=1)
     -> Filter: ((A.CD <> '22') and (A.DV_CD not in ('01','02')) and (A.YMD between <cache>(date_format(('20250101' + interval -(5) day),'%Y%m%d')) and <cache>(date_format(('20250101' + interval 5 day),'%Y%m%d')))) (cost=96062.06 rows=336701) (actual time=0.026..232.122 rows=223093 loops=1)
       -> Covering index range scan on A using A_IX02 over ('20241227' <= YMD <= '20250106' AND NULL < DV_CD) (cost=96062.96 rows=467640) (actual time=0.021..159.775 rows=235732 loops=1)
     -> Single-row index lookup on <subquery2> using <auto_distinct_key) (PKCOL1=A.PKCOL) (actual time=0.001..0.001 rows=1 loops-223093)
       -> Materialize with deduplication (cost=210871.52..210871.52 rows=1039670) (actual time=1155.121..1177.010 rows=850280 loops=1)
         -> Covering index loopup on X using X_IX03 (TEL_DV_CD='00') (cost=106904.52 rows=1039670) (actual time=0.036..327.789 rows=862612 loops=1)

[튜닝 후 쿼리 & 실행계획2]    0.786 (s)

• 오라클의 세미조인 방식과 작동원리가 같음

EXPLAIN ANALYZE
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ A.YMD, COUNT(*) CNT
  FROM A
 WHERE A.CD NOT IN ('22')
   AND A.DV_CD NOT IN ('01','02')
   AND A.YMD BETWEEN DATE_FORMAT(ADDDATE('20250101',-5),'%Y%m%d')
                 AND DATE_FORMAT(ADDDATE('20250101',5),'%Y%m%d')
   AND EXISTS(SELECT /*+ QB_NAME(subq1) */1
                FROM X
               WHERE X.TEL_DV_CD = '00'
                 AND A.PKCOL = X.PKCOL1)
 GROUP BY A.XXX_YMD
 
-> Group aggregate: count(*) (cost=313607.94 rows=401948) (actual time=10.184..785.654 rows=17 loops=1)
  -> Nested loop semijoin (cost=273413.12 rows=401948) (actual time=0.038..763.252 rows=216870 loops=1)
    -> Filter: (A.CD <> '22') and (A.DV_CD not in ('01','02')) and (A.YMD between <cache>(date_format(('20250101' + interval -(5) day),'%Y%m%d')) and <cache>(date_format(('20250101' + interval 5 day),'%Y%m%d')))) (cost=96062.06 rows=336701) (actual time=0.024..240.037 rows=223093 loops=1)
       -> Covering index range scan on A using IX02 over ('20241227' <= YMD <= '20250106' AND NULL < DV_CD) (cost=96062.06 rows=467640) (actual time=0.020..161.669 rows=235732 loops=1)
    -> Covering index lookup on X using X_IX03 (TEL_DV_CD='00', PKCOL1=A.PKCOL) (cost=0.49 rows=1) (actual time=0.002..0.002 rows=1 loops=223093)

개요 : MySQL에서 NULL은 정렬할 때 어디에 오는가?

결론 : Oracle과 PostgreSQL은 NULL을 마지막에 정렬, MySQL은 NULL을 첫번째로 정렬한다.

[MySQL에서 테스트]

• MAX : 문자열 > 공백 > NULL
• MIN  :  NULL < 공백 < 문자열 

-- 테스트 테이블 생성
CREATE TABLE TEST_YNNULL
(COL1 VARCHAR(10));

-- 데이터 삽입
INSERT INTO TEST_YNNULL VALUES(NULL);
INSERT INTO TEST_YNNULL VALUES('Y');
INSERT INTO TEST_YNNULL VALUES('N');
INSERT INTO TEST_YNNULL VALUES('');

-- 정렬
SELECT * FROM TEST_YNNULL ORDER BY COL1;

COL1
--------
[NULL]

N
Y

-- NULL 데이터 비교
SELECT * FROM TEST_YNNULL WHERE COL1 IS NULL;

COL1
-------
[NULL]


-- 공백 데이터 비교
SELECT * FROM TEST_YNNULL WHERE COL1 = ''; -- "" 도 같은 결과

COL1
-------
         <--- 공백

-- MAX, MIN 비교(NULL, 공백 포함)
SELECT MAX(COL1), MIN(COL1) FROM TEST_YNNULL;

MAX(COL1) | MIN(COL1)
------------------------
Y         |              <--- 공백

DELETE FROM TEST_YNNULL WHERE COL1 = '';

-- MAX, MIN 비교(NULL만 포함)
SELECT MAX(COL1), MIN(COL1) FROM TEST_YNNULL;

MAX(COL1) | MIN(COL1)
------------------------
Y         | N

문제 : 오라클 클라이언트 사용하여 Unload 시 10자리 초과의 숫자에 대해 지수형으로 저장하여 csv 파일을 다시 aurora로 이행시 지수형부분이 아래와 같이 뒷부분이 "000000"로 저장되는 문제 발생, 또한 99999999999 숫자의 경우 100000000000로 저장되는 문제 발생

Case 1)

오라클 ASIS 데이터 : 12345678912
Unload CSV 파일 : 1.23456E+10
Aurora 에서 데이터 : 1234000000

Case 2)

오라클 ASIS 데이터 : 99999999999
Unload CSV 파일 : 1.0000E+11
Aurora 에서 데이터 : 100000000000

해결 방안1)

SET NUM[WIDTH] {10|n}

SPOOL 파일에 SET NUMWIDTH 38 옵션을 추가, Default 값은 10, 11자리 이상은 지수로 표현함

n은 38까지 설정가능( 2~50 까지 가능하다고 하는데 테스트 해봐야함)

38자리로 설정하면 숫자는 공백을 포함하여 저장되는데 TOBE의 데이터 타입이 숫자형이라서 공백은 제외하고 데이터는 잘 들어간다.    

ex) 123 -> '                             123' -> 123

spool on

SET PAGES 0
SET FEEDBACK OFF
SET HEADING ON
SET ECHO OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET LONG 1000000
SET LONGCHUNKSIZE 100000
SET NUMWIDTH 38

ALTER SESSION SET NLS_DATE_FORMAT='YYYYYMMDDHH24MISS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYYMMDDHH24MISSXFF';

spool [PATH]

[SQL]
;

spool off

ex) numwidth 설정 예제 

SQL> show numwidth 
numwidth 10

SQL> SELECT 1234567891 num1, 12345678912 num2 from dual;

num1        | num2       |
--------------------------
1234567891  | 1.2346E+10 |

SQL> set numwidth 20

num1        | num2        |
---------------------------
1234567891  | 12345678912 |

해결방안2)

SET NUMF[ORMAT] format

NUMFORMAT을 설정한다. NUMFORMAT은 TO_NUMBER 형식 모델과 같은 방식으로 작동한다. 기본값은 ""(설정되지 않음) 이다.

SQL> select 12345 from dual;

12345
------
12345

SQL> set numformat 0000000000
SQL> select 12345 from dual;

     12345
----------
0000012345

SQL> set numformat 99999.00
SQL> select 12345 from dual;

    12345
---------
 12345.00

해결방은 3)

Unload 하기 전에 SQL에 숫자형 데이터에 to_char() 로 문자형으로 형변환한다. 문자형은 지수변환하지 않고 데이터 그대로 저장하고 TOBE에 입력할 때 숫자형으로 변환된다.

하지만, 숫자 컬럼이 많아서 해당 방법은 적용하지 않았다.

결론은 1번 방안으로 선택하였다.

해당 쿼리는 집계하는 쿼리 인데 실행계획을 보면 LEFT JOIN 후에 2천만건이 넘는다. 

이것을 다시 집계하여 천건으로 줄인다. LEFT JOIN에서 과도한 부하가 발생한다.

튜닝 전 쿼리(간략화)

SELECT A.pk1, A.pk2, A.pk3, A.pk4, MIN(B.col3), MAX(B.col3)
  FROM A 
  LEFT JOIN B
    ON A.pk1 = b.col1 AND A.pk2 = b.col2 
  GROUP BY A.pk1, A.pk2, A.pk3, A.pk4
 ;

튜닝 전 실행계획(부하 부분)

-> Aggregate using temporary table (actual time=45112.001..45112.202 rows=1043 loops=1)
  -> Left hash join (cost=14662616114.85 rows=0) (actual time=263.626..2267.308 rows=200000000 loops=1)

튜닝 후 쿼리

# 인덱스 생성 
create index B_IX01 on B(col1, col2, col3);

SELECT A.pk1, A.pk2, A.pk3, A.pk4, min(B_min_col3) B_min_col, max(B_max_col3) B_max_col
  FROM A
  LEFT JOIN 
  LATERAL (SELECT B.col1, B.col2, MIN(B.col3) B_min_col3, MAX(B.col3) B_max_col3
            FROM B
            WHERE A.pk1 = B.col1
              AND A.pk2 = B.col2) B
      ON A.pk1 = B .col1 AND A.pk2 = B.col2
 GROUP BY A.pk1, A.pk2, A.pk3, A.pk4

튜닝 후 실행계획(튜닝된 부분)

-> Group aggregate : min(B.col3), max(B.col3) (cost=3.63 rows=12) (actual time=0.033..0.033 rows=0 loops=1043)
  -> Filter : ... 
    -> Covering index lookup on B using B_IX01 (col1=A.pk1, col2=A.pk2) (cost=2.45 rows=12) (actual time=0.004..0.023 rows=13 loops=1043)

실제 쿼리는 상당히 길고 부하부분도 여러군데 이지만 부하 부분은 정리된 바와 같이 조인 결과 rows 가 천만건이 넘는다. 조인과정과 이를 다시 집계하는 과정 둘다 부하가 발생한다.  튜닝 된 것처럼 집계처리를 미리 할 수 있다면 집계 후 조인하여 부하를 줄일 수 있다.  

MySQL에서 인덱스가 사용되지 않는 조건

기본사항 

• NOT EQUAL 조건
• LIKE '%ABC' : %로 시작하는 LIKE 검색
• 인덱스 컬럼이 변형된 경우
• NOT DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
• 데이터 타입이 서로 다른 비교(타입을 변환해야 비교가 가능한 경우)
• 문자열 데이터의 collation이 다른 경우

특이사항

• 인덱스의 선두 컬럼의 등치 조건이고 두번째 이상 컬럼이 범위 검색일 경우 두번째 이상 컬럼의 값을 무시하는 경우가 발생한다.
  "COL2 BETWEEN :1 AND :2"  조건일 때 무시되는 상황이 발생하여 강제로 WITH문으로 바인드 변수 값의 범위를 컬럼 값으로 전달해주면 인덱스를 검색한다.  "COL2 IN ( WITH temp_w AS (....) SELECT :COL2 FROM temp_w )