1. WHERE문 SQL 튜닝
예시 1
SELECT * FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);위의 SQL과 같이 3일 이내 생성된 유저 정보를 조회할 경우, created_at의 INDEX를 걸어줌에 따라 아래와 같은 속도 차이가 난다.
INDEX 적용 전(type:all) : 0.563초, index 적용 후(type:range) : 0.016초
→범위형 조건(부등호, IN, LIKE, BETWEEN)을 활용해 대량의 데이터를 가져오는 경우, 해당 칼럼에 index 사용시 성능 향상 효율 극대화
예시 2
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)위의 컬럼을 실행할 때, 인덱스 유무 및 종류에 따라 아래와 같은 결과가 출력된다.
ALL : 672ms
created_at : 16ms
department : 516ms
created_at + department : 16ms
department + created_at : 16ms
→단일 컬럼 인덱스와 멀티 컬럼 인덱스를 적용하였을 때, 성능의 차이가 거의 없다면 단일 컬럼 인덱스를 사용한다. (인덱스의 규모가 클수록 데이터 변경 처리 속도가 그만큼 낮아지기 때문)
※ explain analyze 해석
Filter: ((users.department = 'Sales') and (users.created_at >= <cache>((now() - interval 3 day)))) (cost=93877 rows=33224)
(actual time=2.67..818 rows=123 loops=1)
-> Table scan on users (cost=93877 rows=996810) (actual time=0.0874..619 rows=1e+6 loops=1)Table scan on users(풀테이블 스캔), 소요시간 : 619ms, 스캔한 행 : 1e+6(10의 6승) 100만건
조건의 필터링 시간 : 818 - 619 = 199ms, 필터링 행수 : 123
2. 작동하지 않는 INDEX
예시 1 (넓은 범위의 데이터가 대상인 SQL)
조건으로 지정이 된 칼럼에 INDEX가 존재한다 하더라도 아래와 같이 넓은 범위의 데이터를 조회하는 경우에는 풀 테이블 스캔이 효과적이다.
SELECT * FROM admins ORDER BY email ASC;email 칼럼에 INDEX가 존재하더라도 전체 범위를 대상으로 한 데이터이기에 굳이 INDEX를 거쳐서 테이블에 가는 것이 아닌 바로 테이블에 접근 해 풀스캔(ALL)하는 것이 더 효율적이다.
예시 2 (가공된 칼럼이 대상인 SQL)
조건으로 지정이 된 칼럼에 INDEX가 존재한다 하더라도 아래와 같이 함수나 연산 등을 통한 칼럼의 가공이 일어날 경우, 해당 SQL은 INDEX를 활용하지 않을 가능성이 높다.
SELECT * FROM stores WHERE SUBSTRING(address,1,5) = '서울특별시'
SELECT * FROM settlement WHERE ratio * 100 > 50 ORDER BY ratio DESC위의 쿼리는 둘 다 ALL(풀스캔)이 나오게 된다.
SELECT * FROM stores WHERE address LIKE '서울특별시%'
SELECT * FROM settlement WHERE ratio > 0.5 ORDER BY ratio DESC타겟이 되는 칼럼의 가공을 배제하기 위해 위와 같이 수정을 한 후, 실행 계획을 체크해보면 RANGE(범위인덱스 활용)으로 변경되고 변경 전보다 속도도 극적으로 개선된 것을 확인할 수 있다.
정리하자면 INDEX를 활용하기 위해서는 (1) 전체 범위가 아닌 (2) 조건에 사용되는 인덱스의 칼럼의 가공이 최대한 배제된 경우에 더욱 효과를 발휘할 것이다.
3. ORDER BY문 SQL 튜닝
ORDER BY, 즉 정렬은 시간이 오래 걸리는 부담스러운 작업이기에, 데이터가 많으면 많을수록 ORDER BY 조건의 대상이 되는 컬럼은 INDEX를 추가하는 편이 좋다.
SELECT * FROM stores ORDER BY name;단순 ORDER BY문만 걸려있는 SQL이라면 name에 INDEX가 지정이 되어 있다 하더라도 풀스캔(ALL)을 해버리기에 시간이 오래 걸린다. 그렇기에 아래와 같이 범위를 지정해줌으로써 인덱스 스캔(INDEX)으로 실행 계획이 변경되는 것을 확인할 수 있다. INDEX는 이미 해당 칼럼의 정렬처리가 되어 있기에 정렬처리를 할 필요가 없으므로, 상세 계획(EXPLAIN ANALYZE)을 확인해보면 수정 전과 비교해 정렬(SORT) 처리가 사라진 것도 확인이 가능하다.
SELECT * FROM stores ORDER BY name LIMIT 300;정리하자면 ORDER BY문은 해당 처리 자체가 범위 내의 데이터를 정렬하는 것이기에, 사용하지 않는 것이 효율적이나 사용하는 경우라면 (1)범위를 지정(LIMIT)하고 (2)정렬 대상이 되는 칼럼을 INDEX 지정하여 정렬 처리를 생략하는 것이 좋다.
4. INDEX 걸기(ORDER BY문 VS WHERE문)
INDEX를 지정할 때, WHERE문 혹은 ORDER BY문 중 어느 쪽에 지정하는 것이 더 효율적인가를 체크해보고자 한다.
SELECT * FROM admins
WHERE created_at >= 2024-08-01
AND address LIKE '%gmail.com'
ORDER BY name
LIMIT 500;예시 1 (ORDER BY문 INDEX)
위와 같은 SQL에 INDEX를 걸 경우, 우선 ORDER BY문에 지정되어 있는 name에 INDEX를 결어보면 타입 자체는 ALL(풀스캔)에서 INDEX(인덱스스캔)으로 변경되었으나 속도는 오히려 느려진 것을 확인할 수 있었다.
그 이유는 ORDER BY문에 사용되어진 컬럼에 해당하는 값을 INDEX로 지정함으로써 정렬처리가 생략되었다 하더라도, ORDER BY문을 수행하기 위해 INDEX 내부의 모든 데이터를 가져오는 계획이 세워지기에 대량의 데이터를 스토리지 엔진으로부터 받아와야 하는 비효율적인 처리가 생겨버린다.
예시 2 (WHERE문 INDEX)
그렇다면 WHERE문에 INDEX를 걸기에 앞서, 다중 조건이 걸려있는 SQL문에 어떤 컬럼에 INDEX를 걸어야 할지부터 결정할 필요가 있다.
SQL튜닝의 핵심은 최대한 스토리지 엔진으로부터 받아오는 데이터를 최소화하는 것이기에, 각각의 WHERE문을 단일 조건으로써 실행해보고 대상이 되는 레코드의 수가 적은 WHERE문에 해당하는 컬럼을 INDEX로 지정하는 것이 좋다.
그렇기에 더 적은 수의 레코드의 결과를 가진 created_at을 INDEX로 지정하여 실행해본 결과, 타입도 RANGE(범위)로 변경된 것과 극적인 속도 개션을 체크할 수 있게 되었다.
정리해보면 물론 SQL에 따라 차이는 있겠으나, ORDER BY보다는 WHERE문에 INDEX를 적용시키는 것이 효율적이며 WHERE문 내부에서도 최대한 적은 레코드를 호출하는 조건에 해당하는 컬럼을 우선적으로 INDEX에 적용시키는 것이 좋다.
5. HAVING문 SQL 튜닝
SQL문에 GROUP BY문이 있을 경우의 INDEX 작성 방법에 대해서 알아보고자 한다.
SELECT level, MIN(total_pay) FROM stores
GROUP BY level
HAVING level >= 3;해당 SQL에서 level을 INDEX로 걸 경우, 걸기 전보다 속도가 오히려 늦어지는 것을 볼 수 있다. 느려진 원인은 타입이 INDEX로 변경되었으나 어짜피 전체 레코드를 대상으로 GROUP BY문을 수행하기에 ALL로 직접 테이블에 접근하여 풀스캔하는 것보다 더 비효율적인 처리가 발생하게 된다. 그렇기에 GROUP BY를 하기 전 최대한 그 범위를 좁히고 데이터를 요청하는 것이 중요하다.
SELECT level, MIN(total_pay) FROM stores
WHERE level >= 3
GROUP BY level;위처럼 GROUP BY문 이후에 실행되는 HAVING문에서의 조건을 WHERE문으로 변경함으로써 우선적으로 범위를 좁히고 GROUP BY문을 실행시킴으로써 요청하는 데이터를 줄일 수 있다.
정리하자면 GROUP BY문이 있는 SQL의 튜닝이 필요할 경우, GROUP BY문을 수행하고 실행이 되는 HAVING문보다 WHERE문을 활용해서 우선적으로 GROUP BY의 대상이 되는 데이터를 최소화 시키는 것이 중요하다.
