[SQL 튜닝을 위해 무엇을 해야 하는가?(What)]
SQL 튜닝이라는 것은 결국 AS-IS와 TO-BE를 비교해서 더 나은 것은 선택하는 과정이고
이러한 선택에는 기준 즉 비교할 Data가 필요하다.
이러한 비교 Data는 아래 2가지라고 할 수 있다.
- 실행 시간
- 실행 계획
[실행 시간]
실행 계획을 분석하는 것도 중요하지만 결국은 실제 동작을 개선하는 것이 의미 있기 때문에
실행 시간을 먼저 확인하고 문제가 있으면 실행 계획을 보는 것이 더 효율적이라고 생각한다.
(Slow Query가 확인되면 이후 실행 계획을 통해 해당 Query를 분석하며 개선!)
실제 실행 시간을 확인하기 위한 방법은 간단하다.
Dbeaver 기준 Query Manager로 확인하면 실제 실행 시간을 통한 차이를 명확하게 확인 가능
INDEX 생성 이후에 소요시간이 0.261초에서 0.042초까지 6배 이상 빨라진 것을 직관적으로 확인 가능
[실행 계획 ]
실행 계획은 옵티마이저가 SQL을 어떻게 처리할지 미리 계획한 내용이다.(실제 실행 결과와 다를 수 있음)
실행 계획을 통해 Query의 동작을 분석하기 위해서는 2가지를 확인 할 필요가 있다.
- EXPLAIN
- EXPLAIN ANALYZE
<EXPLAIN>
분석하고자 하는 쿼리 앞에 EXPLAIN이라는 키워드만 붙혀주고 실행하면 위와 같은 결과가 나오는데
중요한 정보들은 아래와 같다.
[type]
ALL(Full Table Scan - 튜닝 필요할 수 있음)
index(Full Index Scan - index 활용(정렬))
range(Index Range Scan - index 활용(범위 조건절))
const(unique한 단건 조회에 index 활용(조건절) - 가장 효율적)
ref(unique하지 않은 컬럼으로 index 활용(조건절))
possible_keys : 사용 가능한 index 목록
key : 실제 사용한 index
ref : join시 사용된 column
rows : access한 table의 data수(줄이는 것이 좋음)
filtered : filtering이 이루어진 후 사용하는 data의 비율(낮을수록 조건절이 유효하다는 의미)
*** rows/filtered는 실제 값이 아닌 추정치이므로 실제 실행을 확인
여기에서 가장 먼저 확인 해봐야 할 것은 type으로 Index의 적용 여부/방식을 알려준다.
ALL이 나오는 경우 확인 해볼 필요는 있으나 index를 활용한다고 무조건 빠른 것은 아니므로
Query와 Data에 따라 분석해봐야 한다.
(Index Table이 오히려 병목이 되는 경우가 있음 -> index(Full Index Scan) + GROUP BY의 경우)
<EXPLAIN ANALYZE>
EXPLAIN에 비해 실행 결과에 대한 구체적인 정보들(동작/actual time/rows)을 알 수 있다.
동작 : 1. Table scan(==full scan) -> 2. Filter 로 SQL 연산이 동작함
actual time : ms1..ms2 (ms1은 연산 시작 시간 / ms2가 해당 연산이 끝날 때까지의 시간 -> ms2가 중요)
이후 연산의 ms2 - 이전 연산의 ms2 = 이후 연산의 실행 시간
* ex) Filter의 실행 시간은 186 - 160 = 26ms
rows : 해당 연산을 진행하며 access한 row 숫자(줄일 수록 좋음)
[자주 쓰는 명령어]
SHOW INDEX FROM users;
CREATE INDEX idx_name ON users(name);
ALTER TABLE users DROP INDEX idx_name;
