SQL Query (쿼리) 최적화는 데이터베이스 관리나 개발에 필수입니다. 최적화가 안되어 있는 쿼리를 구동하면 성능에 문제가 있기 마련입니다. 대다수의 케이스의 앱이나 웹 사이트의 유저들은 정보를 최대한 빨리 얻으려고 하죠. 이제는 1초도 긴 시간이 되었습니다. 이 포스팅에서는 13가지의 최적화 방법을 알아 보겠습니다.
I. 모든 데이터가 필요하지 않은 경우는 TOP을 사용
SELECT 문구에 TOP을 사용하면 리턴되는 데이터 수를 줄일 수 있습니다. 모든 데이터가 필요하지 않을경우에는 권장합니다. TOP은 MSSQL 용으로 SELECT와 사용되고 LIMIT은 MYSQL용으로 쿼리의 맨 마지막에 사용이 됩니다.
쿼리 예제:
최적화 예제:
II. * 기호 대신에 컬럼 이름을 사용
SELECT 문구에 기호를 사용하면 쿼리가 간결해지고 편하죠. 하지만 이 방법은 아주 안좋습니다. 꼭 필요한 컬럼만 리턴이 되도록 대신에 컬럼 이름을 넣어 주어야 합니다. 필요한 컬럼만 리턴이 되면 리턴되는 테이블의 사이즈가 최소화가 되어, 네트워크를 더 빨리 거쳐서 오므로 데이터가 유저에 도달하는 시간이 줄어듭니다.
쿼리 예제:
최적화 예제:
III. HAVING 대신에 WHERE를 사용하여 데이터를 필터링
SQL의 실행 순서는 WHERE를 먼저 거치고 나서 HAVING으로 전달 됩니다. WHERE에서 데이터를 최소화하는 것이 쿼리의 성능이 향상 됩니다. 꼭 HAVING을 사용해야 한다면 먼저 WHERE에서 데이터를 필터링하여 HAVING에서 사용이 되면 그만큼 적은 데이터를 쿼리가 사용하기 때문이죠.
쿼리 예제:
최적화 예제:
IV. 필요없는 DISTINCT는 사용하지 말것
DISTINCT를 사용하면 간편하게 중복 데이터가 처리되어 데이터가 리턴이 됩니다. 이것은 GROUP BY 쿼리를 사용하는 것과 동일합니다. 하지만, 이 경우에는 많은 컴퓨터 리소스를 사용하게 되고 정확하지 않은 데이터가 리턴이 될 수도 있습니다. 이럴 경우에는 컬럼을 더 삽입하여 중복 데이터가 리턴되지 않게 합니다.
쿼리 예제:
최적화 예제:
V. SUBQUERY (서브쿼리) 대신에 JOIN이나 CTE를 사용
JOIN의 이점은 서브쿼리 (subquery) 보다 빠르다는 점입니다. 모든 쿼리를 로드하고 데이터를 불러온 후 구동되는 서브쿼리에 비해, JOIN은 쿼리 적합한 실행 계획 (execution plan)이 만들어지므로 프로세스의 최적화가 되어 더 빨라지는 것입니다.
쿼리 예제:
최적화 예제:
여러개의 서브쿼리를 사용해야 한다면 CTE (common table expression)를 권장합니다. 쿼리 디버깅도 편해지기 때문이죠.
쿼리 예제:
최적화 예제:
VI. JOIN의 최적화
JOIN은 여러 종류가 있습니다. 자칫 잘못 사용하면 중복 데이터가 리턴되거나 쿼리가 상당히 느려질 수 있습니다.
OUTER JOIN
OUTER JOIN은 꼭 필요할 때만 사용하는 것이 좋습니다. OUTER JOIN은 JOIN되는 두 테이블의 매치가 되던 안되던 모든 데이터가 리턴이 됩니다. 중복데이터가 나올 수 있는 확률이 아주 높습니다.
INNER JOIN
INNER JOIN은 두 테이블의 데이터 중 매치된 데이터만 리턴이 됩니다. 보통 이 JOIN을 사용합니다.
쿼리 예제:
INNER JOIN
INNER JOIN은 두 테이블의 데이터 중 매치된 데이터만 리턴이 됩니다. 보통 이 JOIN을 사용합니다.
쿼리 예제:
RIGHT 보다는 LEFT JOIN을 사용하는 것을 권장합니다. JOIN하는 테이블의 위치만 바꾸면 되고 쿼리를 읽고 이해하는데 더 쉽습니다.
마지막으로, JOIN을 할 시에는 JOIN되는 컬럼이 인덱싱이 되어 있어야 쿼리의 성능이 최적화됩니다.
VII. 바쁜 시간에는 WITH (NOLOCK)을 사용
수많은 유저들이 접속하고 서버가 바쁜시에 쿼리를 구동하면 어떤 테이블은 LOCK이 되어 있을 수 있습니다. CRUD에 사용이 되고있으면 그렇죠. 그럴 시에는 CRUD 프로세스가 종료가 되고 트랜잭션이 성공적으로 커밋이 된 후에 그 테이블을 쿼리할 수 있습니다. 그러는 도중에는 LOCK이 풀리기 전까지는 쿼리가 기다리게 됩니다.
아주 중요하지 않은 쿼리 같은 경우는 바쁜시간에 구동하는 것을 권장하지 않지만, 바쁜시간에 구동을 해야 한다면 WITH (NOLOCK)을 사용하면 LOCK이 풀릴 때까지 기다리지 않고 바로 데이터를 리턴 할 수 있습니다. 주의점은 WITH (NOLOCK)을 사용하면 트랜잭션이 커밋이 안된 DIRTY 데이터가 리턴이 될 수 있습니다.
쿼리 예제:
최적화 예제:
VIII. 인덱스 칼럼을 쿼리시에는 IN을 사용
인덱스가 되어있는 칼럼을 쿼리에 사용할 시에는 IN을 사용하는 것이 최적화된 방법입니다 왜냐하면 인덱싱 정렬 순으로 쿼리 실행시에 IN 내부의 리스트가 정렬이 되기 때문이죠. 참고로 IN에 들어가는 값은 상수만 가능합니다.
쿼리 예제:
최적화 예제:
IX. 일대다 관계 (One-To-Many Relationship)의 테이블을 JOIN시에는 DISTINCT 대신에 EXISTS를 사용
DISTINCT는 GROUP을 만들기 때문에 리소스를 많이 사용합니다. EXISTS를 사용하는 서브쿼리를 이용하면 테이블 전체를 리턴하지 않아도 됩니다.
쿼리 예제:
최적화 예제:
X. 데이터 필터링 시에 OR를 사용하지 말것
OR를 사용하여 데이터를 필터링 하면 쿼리는 하나지만 프로세스는 여러개가 됩니다. OR에 있는 문구 하나하나를 계산하게 되는 것이죠. 이럴 시에는 UNION을 사용합니다.
쿼리 예제:
최적화 예제:
XI. UNION 대신에 UNION ALL을 사용
UNION은 중복 데이터를 걸러내는 프로세스를 거치므로 모든 데이터가 리턴이 되는 UNION ALL 보다 느립니다.
쿼리 예제:
최적화 예제:
XII. JOIN 시에 OR를 사용하지 말것
OR를 사용하여 JOIN을 하면 쿼리의 속도가 두배나 느려집니다.
쿼리 예제:
최적화 예제:
XIII. 오퍼레이터의 오른편에 집계함수 (Aggregate Functions)를 사용하지 말것
오퍼레이터의 오른편에 집계함수 (aggregate functions) 사용을 최소화 하면 쿼리의 성능을 큰폭 높일 수 있습니다
쿼리 예제:
최적화 예제:
Comments