[SQL] SQL Booster - 인덱스
2024, Apr 08
목차
- 6.1.1 인덱스란?
- 6.1.2 인덱스 종류
- 6.1.3 B*트리 구조와 탐색 방법
- 6.1.4 데이터를 찾는 방법
- 6.1.5 데이터를 찾는 방법 - 테이블 전체 읽기 (풀스캔)
- 6.1.6 데이터를 찾는 방법 - 인덱스를 이용한 찾기
- 6.1.7 INDEX RANGE SCAN VS. TABLE ACCESS FULL
- 6.1 요약
- 6.2.1 단일 인덱스의 컬럼 정하기
- 6.2.2 단일 인덱스 VS 복합 인덱스
- 6.3.1 복합 인덱스 - 컬럼 선정과 순서#1
- 6.3.4 복합 인덱스 - 컬럼 선정과 순서#4
- 6.4.1 인덱스로 커버된 SQL
- 6.4.2 Predicate Information - ACCESS
- 6.4.3 너무 많은 인덱스의 위험성
- 6.4.4 인덱스 설계 과정
SQL Booster 책으로 공부하며 정리해보는 내용
6.1.1 인덱스란?
테이블 내의 데이터를 찾을 수 있게 일부 데이터를 모아서 구성한 데이터 구조
인덱스가 없는 BIG 테이블 조회 - 실행계획
"SQL_ID 64zytgkun017h, child number 0"
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*) FROM
T_ORD_BIG T1 WHERE T1.ORD_SEQ = 343
Plan hash value: 914281838
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:18.51 | 258K| 258K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:18.51 | 258K| 258K|
|* 2 | TABLE ACCESS FULL| T_ORD_BIG | 1 | 12326 | 10000 |00:00:23.83 | 258K| 258K|
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 2 - filter(""T1"".""ORD_SEQ""=343)"
Note
-----
- dynamic sampling used for this statement (level=2)
인덱스 생성 후 실행계획
"SQL_ID 64zytgkun017h, child number 0"
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*) FROM
T_ORD_BIG T1 WHERE T1.ORD_SEQ = 343
Plan hash value: 858429001
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 24 | 16 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 24 | 16 |
|* 2 | INDEX RANGE SCAN| X_T_ORD_BIG_TEST | 1 | 12326 | 10000 |00:00:00.01 | 24 | 16 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 2 - access(""T1"".""ORD_SEQ""=343)"
Note
-----
- dynamic sampling used for this statement (level=2)
6.1.2 인덱스 종류
인덱스를 구성하는 컬럼 수에 따른 구분
- 단일 인덱스 : 인덱스에 하나의 컬럼만 사용
- PK 속성이 단일 컬럼일 때 주로 사용
- 복합 인덱스 : 인덱스에 두 개 이상의 컬럼을 사용
- 컬럼 인덱스, 결합 인덱스
- 복합 인덱스로 여러 SQL을 커버하는 것이 좋음 → SQL 별로 필요한 인덱스를 모두 만들다 보면 인덱스가 너무 많아짐
인덱스를 구성하는 컬럼 값들의 중복 허용 여부
- 유니크 인덱스 : 중복 허용 X
- 비유니크 인덱스 : 중복 허용 O
인덱스의 물리적인 구조에 따른 구분
- B*트리 인덱스
- 트리 형태의 자료 구조를 사용
- 비트맵 인덱스
파티션 된 인덱스 구분
- 글로벌 인덱스
- 로컬 인덱스
그 외
- IOT(Index Organized Table) : 테이블 자체를 특정 컬럼 기준으로 인덱스화 (클러스터드 인덱스)
- MYSQL은 PK를 무조건 클러스터드 인덱스로 구성함.
- IOT도 B*트리 인덱스
6.1.3 B*트리 구조와 탐색 방법
- 인덱스 생성 시 옵션을 따로 정의하지 않으면, B*트리 구조의 인덱스가 만들어짐
- 구분
- 루트블록
- 브랜치 블록
- 리프 블록 → 인덱스 키 값 순으로 정렬되어 있음
6.1.4 데이터를 찾는 방법
- 테이블 전체 읽기 (TABLE ACCESS FULL)
- 인덱스를 이용한 찾기 (INDEX RANGE SCAN & TABLE ACCESS BY INDEX ROWID)
- rowid를 이용한 직접 찾기 (TABLE ACCESS BY INDEX ROWID)
6.1.5 데이터를 찾는 방법 - 테이블 전체 읽기 (풀스캔)
- 데이터가 정렬되어 있지 않기 때문에, 모든 블록을 읽어야함
"SQL_ID 0pghnam4br94r, child number 0"
-------------------------------------
"SELECT /*+ GATHER_PLAN_STATISTICS */ T1.CUS_ID ,COUNT(*) "
ORD_CNT FROM T_ORD_BIG T1 WHERE T1.ORD_YMD = '20170316' GROUP
BY T1.CUS_ID ORDER BY T1.CUS_ID
Plan hash value: 381085929
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:18.44 | 258K| 258K| | | |
| 1 | SORT GROUP BY | | 1 | 90 | 5 |00:00:18.44 | 258K| 258K| 2048 | 2048 | 2048 (0)|
|* 2 | TABLE ACCESS FULL| T_ORD_BIG | 1 | 86427 | 50000 |00:00:16.73 | 258K| 258K| | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 2 - filter(""T1"".""ORD_YMD""='20170316')"
- 읽어야 하는 데이터가 많을 때는 오히려 인덱스보다 효율적일 수 있다.
6.1.6 데이터를 찾는 방법 - 인덱스를 이용한 찾기
과정
- 루프에서 리프로 : 검색 조건에 해당하는 첫번째 리프 블록
- 부하가 거의 없음
- 리프블록 스캔 : 리프블록 차례로 읽음
- ORD_YMD ‘20170103’인 데이터를 검색하려면, ‘20170103’보다 큰 데이터를 만날때까지 리프블록들을 읽음
- 테이블 접근
- 인덱스 리프 블록의 ROWID값을 참조해 테이블의 데이터를 찾아감
"SQL_ID br1774wn1f4yc, child number 0"
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_1) */
"T1.CUS_ID ,COUNT(*) ORD_CNT FROM T_ORD_BIG T1 WHERE T1.ORD_YMD = "
'20170316' GROUP BY T1.CUS_ID ORDER BY T1.CUS_ID
Plan hash value: 4053389377
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.31 | 10200 | 50 | | | |
| 1 | SORT GROUP BY | | 1 | 90 | 5 |00:00:00.31 | 10200 | 50 | 2048 | 2048 | 2048 (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| T_ORD_BIG | 1 | 86427 | 50000 |00:00:00.13 | 10200 | 50 | | | |
|* 3 | INDEX RANGE SCAN | X_T_ORD_BIG_1 | 1 | 86427 | 50000 |00:00:00.01 | 142 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 3 - access(""T1"".""ORD_YMD""='20170316')"
6.1.7 INDEX RANGE SCAN VS. TABLE ACCESS FULL
- 랜덤 액세스 : IO 작업 한 번에 하나의 블록을 가져오는 접근 방법
"SQL_ID 936uuj6k8q5v6, child number 0"
-------------------------------------
"SELECT /*+ GATHER_PLAN_STATISTICS 6.1.7*/ T1.CUS_ID ,COUNT(*) "
ORD_CNT FROM T_ORD_BIG T1 WHERE T1.ORD_YMD = '20170316' GROUP
BY T1.CUS_ID ORDER BY T1.CUS_ID
Plan hash value: 4053389377
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.04 | 10200 | | | |
| 1 | SORT GROUP BY | | 1 | 90 | 5 |00:00:00.04 | 10200 | 2048 | 2048 | 2048 (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| T_ORD_BIG | 1 | 86427 | 50000 |00:00:00.04 | 10200 | | | |
|* 3 | INDEX RANGE SCAN | X_T_ORD_BIG_1 | 1 | 86427 | 50000 |00:00:00.01 | 142 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 3 - access(""T1"".""ORD_YMD""='20170316')"
- INDEX RANGE SCAN 했음
- 5만 건 정도 할땐 인덱스가 좋음
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_1) */
T1.ORD_ST ,SUM(T1.ORD_AMT)
FROM T_ORD_BIG T1
WHERE T1.ORD_YMD BETWEEN '20170401' AND '20170630'
GROUP BY T1.ORD_ST;
/*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_1) */
힌트 부분에 INDEX(T1_X_T_ORD_BIG_1) / FULL(T1) 뭐로 쓰냐에 따라서 나눠짐
- INDEX로 했을 때의 실행계획
"SQL_ID 9s6kqtqd5nzrb, child number 0"
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_1) */
"T1.ORD_ST ,SUM(T1.ORD_AMT) FROM T_ORD_BIG T1 WHERE T1.ORD_YMD "
BETWEEN '20170401' AND '20170630' GROUP BY T1.ORD_ST
Plan hash value: 2353140372
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 0 | 0 | | | |
| 1 | HASH GROUP BY | | 1 | 2 | 0 |00:00:00.01 | 0 | 0 | 355M| 20M| |
| 2 | TABLE ACCESS BY INDEX ROWID| T_ORD_BIG | 1 | 7317K| 7528K|00:01:32.87 | 992K| 35529 | | | |
|* 3 | INDEX RANGE SCAN | X_T_ORD_BIG_1 | 1 | 7317K| 7528K|00:00:02.21 | 20973 | 6163 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 3 - access(""T1"".""ORD_YMD"">='20170401' AND ""T1"".""ORD_YMD""<='20170630')"
- FULL로 했을 때의 실행계획
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS FULL(T1) */ T1.ORD_ST
",SUM(T1.ORD_AMT) FROM T_ORD_BIG T1 WHERE T1.ORD_YMD BETWEEN "
'20170401' AND '20170630' GROUP BY T1.ORD_ST
Plan hash value: 1691582511
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:17.93 | 258K| 258K| | | |
| 1 | HASH GROUP BY | | 1 | 2 | 2 |00:00:17.93 | 258K| 258K| 934K| 934K| 2119K (0)|
|* 2 | TABLE ACCESS FULL| T_ORD_BIG | 1 | 7317K| 7650K|00:00:21.72 | 258K| 258K| | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 2 - filter((""T1"".""ORD_YMD""<='20170630' AND ""T1"".""ORD_YMD"">='20170401'))"
→ INDEX는 1분이 넘어갔지만, FULL은 20초정도 걸림
결론 : 데이터가 특정 수준 이상으로 많으면 인덱스를 이요한 ‘랜덤 액세스’보다 ‘FULL SCAN’ 방식이 훨씬 효율적이다.
6.1 요약
- 적은 양의 데이터를 읽으면 INDEX RANGE SCAN이 유리
- 많은 양의 데이터를 읽어야 한다면 FULL SCAN이 유리할 수 있다.
- FULL SCAN은 데이터가 쌓일수록 성능이 점차 나빠진다. 테이블 관리 전략이 필요하다.
6.2.1 단일 인덱스의 컬럼 정하기
SELECT /*+ GATHER_PLAN_STATISTICS */
TO_CHAR(T1.ORD_DT,'YYYYMM') ,COUNT(*)
FROM T_ORD_BIG T1
WHERE T1.CUS_ID = 'CUS_0064'
AND T1.PAY_TP = 'BANK'
AND T1.RNO = 2
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM');
SELECT 'CUS_ID' COL ,COUNT(*) CNT FROM T_ORD_BIG T1 WHERE T1.CUS_ID = 'CUS_0064'
UNION ALL
SELECT 'PAY_TP' COL ,COUNT(*) CNT FROM T_ORD_BIG T1 WHERE T1.PAY_TP = 'BANK'
UNION ALL
SELECT 'RNO' COL ,COUNT(*) CNT FROM T_ORD_BIG T1 WHERE T1.RNO = 2;
- 인덱스가 필요한 쿼리의 where조건 중 가장 건수가 적은 것에 인덱스를 걸면 좋음 (RNO)
CUS_ID에 인덱스를 건 경우
"SQL_ID b42g731m3m5jv, child number 0"
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_3) */
"TO_CHAR(T1.ORD_DT,'YYYYMM') ,COUNT(*) FROM T_ORD_BIG T1 WHERE "
T1.CUS_ID = 'CUS_0064' AND T1.PAY_TP = 'BANK' AND T1.RNO = 2
"GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM')"
Plan hash value: 950523884
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:31.15 | 245K| 180K| | | |
| 1 | HASH GROUP BY | | 1 | 15 | 2 |00:00:31.15 | 245K| 180K| 1064K| 1064K| 565K (0)|
|* 2 | TABLE ACCESS BY INDEX ROWID| T_ORD_BIG | 1 | 15 | 2 |00:00:00.04 | 245K| 180K| | | |
|* 3 | INDEX RANGE SCAN | X_T_ORD_BIG_3 | 1 | 338K| 340K|00:00:00.27 | 950 | 949 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 2 - filter((""T1"".""RNO""=2 AND ""T1"".""PAY_TP""='BANK'))"
" 3 - access(""T1"".""CUS_ID""='CUS_0064')"
- 이전보다 오래 걸림
- 원인
- INDEX RANGE SCAN에 A-Rows가 340K이다. 그러니 TABLE ACCESS BY INDEX ROWID가 340K번 수행됨. (위에는 3047번만 실행됨)
6.2.2 단일 인덱스 VS 복합 인덱스
CREATE INDEX X_T_ORD_BIG_3 ON T_ORD_BIG(ORD_YMD, CUS_ID);
이렇게 인덱스 생성
쿼리
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_3) */
T1.ORD_ST ,COUNT(*)
FROM T_ORD_BIG T1
WHERE T1.ORD_YMD LIKE '201703%'
AND T1.CUS_ID = 'CUS_0075'
GROUP BY T1.ORD_ST;
실행계획
"SQL_ID 4f3zn95aaqn87, child number 0"
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_3) */
"T1.ORD_ST ,COUNT(*) FROM T_ORD_BIG T1 WHERE T1.ORD_YMD LIKE "
'201703%' AND T1.CUS_ID = 'CUS_0075' GROUP BY T1.ORD_ST
Plan hash value: 950523884
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.55 | 37494 | 7518 | | | |
| 1 | HASH GROUP BY | | 1 | 2 | 1 |00:00:01.55 | 37494 | 7518 | 1096K| 1096K| 468K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| T_ORD_BIG | 1 | 19925 | 30000 |00:00:00.29 | 37494 | 7518 | | | |
|* 3 | INDEX RANGE SCAN | X_T_ORD_BIG_3 | 1 | 19925 | 30000 |00:00:00.27 | 7494 | 7493 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 3 - access(""T1"".""ORD_YMD"" LIKE '201703%' AND ""T1"".""CUS_ID""='CUS_0075')"
" filter((""T1"".""CUS_ID""='CUS_0075' AND ""T1"".""ORD_YMD"" LIKE '201703%'))"
- WHERE 절에 사용되는 컬럼들을 복합 인덱스로 구성했음 → 테이블에 접근하지 않아도 조건에 맞는 데이터를 찾아낼 수 있다.
정리
- 인덱스를 설계할 때 중요하게 고려할 부분은 ‘테이블 접근’(TABLE ACCESS BY INDEX ROWID)을 줄이는 것이다.
- 복합 인덱스에 너무 많은 컬럼을 사용하면 데이터의 입력, 수정, 삭제에서 성능 저하가 나타난다.
6.3.1 복합 인덱스 - 컬럼 선정과 순서#1
"SQL_ID 4vgb4qg5wma2c, child number 0"
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_4) */
"T1.ORD_ST ,COUNT(*) FROM T_ORD_BIG T1 WHERE T1.ORD_YMD LIKE "
'201703%' AND T1.CUS_ID = 'CUS_0075' GROUP BY T1.ORD_ST
Plan hash value: 2396198709
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.08 | 30125 | | | |
| 1 | HASH GROUP BY | | 1 | 2 | 1 |00:00:00.08 | 30125 | 1096K| 1096K| 453K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| T_ORD_BIG | 1 | 19925 | 30000 |00:00:00.13 | 30125 | | | |
|* 3 | INDEX RANGE SCAN | X_T_ORD_BIG_4 | 1 | 19925 | 30000 |00:00:00.03 | 125 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 3 - access(""T1"".""CUS_ID""='CUS_0075' AND ""T1"".""ORD_YMD"" LIKE '201703%')"
" filter(""T1"".""ORD_YMD"" LIKE '201703%')"
- 위에서는 Buffers가 7494인데, 여기서는 125임
- 7000블록의 IO를 개선했다.
컬럼 순서에 따라 ‘INDEX RANGE SCAN’에서 Bufffers 차이가 나는 원리
- 리프블록에서 ORD_YMD, CUS_ID 순으로 정렬 되어있음
- WHERE 조건에서 ORD_YMD는 LIKE로 걸어서 조건에 해당하는 리프블록이 CUS_ID로 했을때보다 많음
- 결론 : WHERE 조건에 = 조건을 사용한 컬럼부터 놓으면 IO를 줄일 수 있다.
6.3.4 복합 인덱스 - 컬럼 선정과 순서#4
많은 조건이 걸리는 SQL
SELECT COUNT(*)
FROM T_ORD_BIG T1
WHERE T1.ORD_AMT = 2400
AND T1.PAY_TP = 'CARD'
AND T1.ORD_YMD = '20170406'
AND T1.ORD_ST = 'COMP'
AND T1.CUS_ID = 'CUS_0036';
- 이걸 복합 인덱스로 만들려면 어떻게 해야 할까 (인덱스를 구성하는 컬럼이 너무 많아지면 좋지 않다.)
조건별로 카운트
SELECT 'ORD_AMT' COL ,COUNT(*) FROM T_ORD_BIG T1 WHERE T1.ORD_AMT = 2400
UNION ALL
SELECT 'PAY_TP' COL ,COUNT(*) FROM T_ORD_BIG T1 WHERE T1.PAY_TP = 'CARD'
UNION ALL
SELECT 'ORD_YMD' COL ,COUNT(*) FROM T_ORD_BIG T1 WHERE T1.ORD_YMD = '20170406'
UNION ALL
SELECT 'ORD_ST' COL ,COUNT(*) FROM T_ORD_BIG T1 WHERE T1.ORD_ST = 'COMP'
UNION ALL
SELECT 'CUS_ID' COL ,COUNT(*) FROM T_ORD_BIG T1 WHERE T1.CUS_ID = 'CUS_0036';
- 성능 향상에 도움이 되는 조건 컬럼만 선별
SQL 결과
ORD_AMT,630000
PAY_TP,18270000
ORD_YMD,90000
ORD_ST,27420000
CUS_ID,330000
- 건수가 적게나오는 ORD_YMD, CUS_ID 순으로 인덱스 선별하면 될 것이다. (INDEX_3)
INDEX 3을 이용한 실행계획
"SQL_ID 03u9urh9t9k76, child number 0"
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_3) */
COUNT(*) FROM T_ORD_BIG T1 WHERE T1.ORD_AMT = 2400 AND
T1.PAY_TP = 'CARD' AND T1.ORD_YMD = '20170406' AND T1.ORD_ST
= 'COMP' AND T1.CUS_ID = 'CUS_0036'
Plan hash value: 1332747187
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.06 | 10045 | 42 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.06 | 10045 | 42 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T_ORD_BIG | 1 | 2 | 10000 |00:00:00.05 | 10045 | 42 |
|* 3 | INDEX RANGE SCAN | X_T_ORD_BIG_3 | 1 | 960 | 10000 |00:00:00.02 | 45 | 42 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 2 - filter((""T1"".""ORD_AMT""=2400 AND ""T1"".""PAY_TP""='CARD' AND ""T1"".""ORD_ST""='COMP'))"
" 3 - access(""T1"".""ORD_YMD""='20170406' AND ""T1"".""CUS_ID""='CUS_0036')"
정리
- = 조건을 사용한 컬럼이 복합 인덱스의 앞부분으로
- 성능에 도움이 되는 조건 컬럼만 선별하여 복합 인덱스를 구현
6.4.1 인덱스로 커버된 SQL
-- CUS_ID, ORD_YMD인덱스를 사용하는 SQL
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_4) */
T1.ORD_ST ,COUNT(*)
FROM T_ORD_BIG T1
WHERE T1.ORD_YMD LIKE '201703%'
AND T1.CUS_ID = 'CUS_0075'
GROUP BY T1.ORD_ST;
-- X_T_ORD_BIG_4인덱스의 재생성
DROP INDEX X_T_ORD_BIG_4;
CREATE INDEX X_T_ORD_BIG_4 ON T_ORD_BIG(CUS_ID, ORD_YMD, ORD_ST);
- → 사용하는걸 다 INDEX 걸어버림 → 테이블 접근 없이 인덱스만으로 SQL 처리 가능
-
인덱스로 커버된 SQL
- 사용을 지양하도록…
6.4.2 Predicate Information - ACCESS
SELECT /*+ GATHER_PLAN_STATISTICS */
T1.ORD_ST ,COUNT(*)
FROM T_ORD_BIG T1
WHERE SUBSTR(T1.ORD_YMD,1,6) = '201703'
AND T1.CUS_ID = 'CUS_0075'
GROUP BY T1.ORD_ST;
"SQL_ID 72hgcu944yrp8, child number 0"
-------------------------------------
"SELECT /*+ GATHER_PLAN_STATISTICS */ T1.ORD_ST ,COUNT(*) "
"FROM T_ORD_BIG T1 WHERE SUBSTR(T1.ORD_YMD,1,6) = '201703' AND "
T1.CUS_ID = 'CUS_0075' GROUP BY T1.ORD_ST
Plan hash value: 2396198709
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.59 | 31381 | 1248 | | | |
| 1 | HASH GROUP BY | | 1 | 2 | 1 |00:00:00.59 | 31381 | 1248 | 1096K| 1096K| 435K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| T_ORD_BIG | 1 | 3386 | 30000 |00:00:00.41 | 31381 | 1248 | | | |
|* 3 | INDEX RANGE SCAN | X_T_ORD_BIG_4 | 1 | 3386 | 30000 |00:00:00.09 | 1381 | 1248 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 3 - access(""T1"".""CUS_ID""='CUS_0075')"
" filter(SUBSTR(""T1"".""ORD_YMD"",1,6)='201703')"
- Predicate Information
- 인덱스에는 SUBSTR 적용 이전 데이터가 들어가있어서, 인덱스를 제대로 사용할 수 없음
SELECT /*+ GATHER_PLAN_STATISTICS */
T1.ORD_ST ,COUNT(*)
FROM T_ORD_BIG T1
WHERE T1.ORD_YMD LIKE '201703%'
AND T1.CUS_ID = 'CUS_0075'
GROUP BY T1.ORD_ST;
→ 이렇게 LIKE를 사용해야 제대로 탔음을 알 수 있음
6.4.3 너무 많은 인덱스의 위험성
SELECT T1.SEGMENT_NAME ,T1.SEGMENT_TYPE
,T1.BYTES / 1024 / 1024 as SIZE_MB
,T1.BYTES / T2.CNT BYTE_PER_ROW
FROM DBA_SEGMENTS T1
,(SELECT COUNT(*) CNT FROM ORA_SQL_TEST.T_ORD_BIG) T2
WHERE T1.SEGMENT_NAME LIKE '%ORD_BIG%'
ORDER BY T1.SEGMENT_NAME;
6.4.4 인덱스 설계 과정
- SQL별 접근 경로 조사
- 테이블별 접근 경로 정리
- 종합적인 인덱스 설계
- 핵심 테이블 및 핵심 SQL 집중 설계
- 생성 및 모니터링