아래 글을 참고 및 번역하여 작성한 글입니다. 의역이 들어갔으므로, 원문을 읽어보시는 것을 추천드립니다.
SQL Performace Tuning -GeeksforGeeks
입사 후 처음으로 SQL 튜닝이라는 말을 들었습니다. 여러 기관이 있고 각 기관 사이에 수많은 데이터가 오가는 업무 환경 특성 상 SQL이 진짜 중요했고, 이 SQL의 성능을 높이는게 중요하다 보니 중요 업무 중 하나가 바로 이 SQL 튜닝이라고 했습니다. 이에 조금 더 알아보고자 이번 글을 준비해보았습니다.
SQL 튜닝이란
SQL 튜닝Tuning이란 SQL 쿼리를 강화하여 서버의 처리 속도를 올리는 것을 말한다. 주 목적은 유저가 쿼리를 보낸 뒤 응답을 받기까지 걸리는 속도를 단축하는 것과 최소한의 자원을 사용해서 처리하도록 만드는 것이다.
즉, SQL 튜닝이란 관계형 데이터베이스에서 쿼리의 처리 속도를 올리는 것이다.
SQL의 속도를 최적화 하기 위한 방법이나 도구가 딱 하나만 있는 것은 아니다. 그보다는 다양한 메서드나 프로시저의 활용한 프로시저들의 집합이라고 볼 수 있다. 우선, 필수적으로 실행되어야 하는 계산의 수와 쿼리 실행 시간에 영향을 끼치는 요소들에 대해서 알아보자.
- 테이블 사이즈Table size : 수십 만개의 열을 가진 테이블이 하나 또는 그보다 더 많다면, 성능에 영향을 끼칠 것이다.
- 조인Joins : 두 개의 테이블을 조인하면서 반환해야 하는 열의 수가 눈에 띄게 늘어난다면, 쿼리 실행 속도 역시 느려질 것이다.
- 병합Aggregations : 하나의 단일 결과를 만들기 위해서 여러 개의 열을 하나로 합치게 된다면, 각각의 값을 검색할 때보다 더 많은 처리 과정이 필요하다.
- 쿼리를 실행하는 다른 사용자Other users executing queries : 데이터베이스가 더 많은 쿼리를 실행하고, 한 번에 더 많은 처리를 해야한다면, 당연히 더 느려질 것이다. 만약 다른 사용자들이 앞서 언급한 요소들을 충족시키기 위해서 쿼리를 실행했고, 이에 많은 리소스를 사용하고 있다면, 특히 문제가 될 수도 있다.
SQL 서버 성능 측정을 위해 느린 SQL 서버 찾아내는 방법
1. 실행 계획 생성하기
SQL Server Management Studio를 활용하면 실행 계획을 작성하여 늘어지는 쿼리가 무엇인지 알아낼 수 있다. 쿼리를 실행하고 나면 실제 실행 계획이 생성된다.
2. 자원resource 사용률 모니터하기
SQL 데이터베이스의 성능은 자원 사용에 크게 좌우 된다. 당신이 측정할 수 없는 부분은 개선할 수 없기 때문에, 자원 사용을 모니터하는 것이 매우 중요하다. 윈도우의 System Monitor tool을 사용하여 SQL 서버의 성능을 측정하면, SQL 서버 객체와 성능 카운터, 다른 객체들의 상태를 확인할 수 있다. System Monitor를 사용하여 윈도우와 SQL 서버 카운터를 통시에 체크하면서 두 서비스의 성능에 상관관계가 있는지 확인 해봐라.
3. SQL DMV를 사용해서 느린 쿼리 찾기
SQL 서버가 갖고 있는 최고의 기능 중 하나가 바로 풍부한 동적 관리 보기(Dynamic Management Views, DMVs)이다. 매우 다양한 정보를 제공하는데, 예를 들면 쿼리 상태, 실행 계획, 최근 쿼리 등이 있다.
Dynamic Management Views, DMVs
동적 관리 뷰 (Transact-SQL) 영문 / 국문 / 추가 정보
동적 관리 뷰 및 함수는 서버 인스턴스의 상태를 모니터링하고, 문제를 진단하고, 성능을 조정하는 데 사용할 수 있는 서버 상태 정보를 반환한다.
- 성능 관련 정보를 수집하기 위한 도구
- SQL Server 모니터링 역할
- 성능 최적화 및 문제점을 진단하는 용도로 사용됨
자주 쓰이는 DMV 정보
SQL 쿼리 최적화하기
서버/데이터베이스는 매우 중요하다. 만약 쿼리가 비효율적이거나 에러를 포함하고 있다면, 데이터베이스의 자원을 엄청나게 잡아 먹고, 속도를 느리게 하거나 다른 유저와의 연결을 끊을 것이다. 당신의 데이터베이스 성능에 부정적인 영향은 최소화하면서 쿼리를 최적화 해야한다. 다음과 같은 기술을 사용해서 쿼리를 최적화 할 수 있다.
1. SELECT * 대신 정확하게 필요한 필드를 선택하라
많은 SQL 개발자들이SELECT *을 사용해서 테이블의 모든 데이터를 가져온다. 그러나, 테이블이 많은 열과 행을 가지고 있을 때에는 데이터베이스에 너무 많은 부하가 걸리게 된다. SELECT문을 사용할 때는 정말 필요한 데이터를 콕 집어서 사용하자.
2. SELECT DISTINCT를 피해라
중복된 데이터를 제거할 때에 SELECT DISTINCT를 사용하는 것이 유용하다. 별도의 결과를 얻기 위해서는 SELECT DISTINCT GROUP을 써서 쿼리의 모든 필드를 각각 그룹화할 수 있다. 하지만, 이를 위해서는 너무 많은 계산 능력이 필요하다. 게다가, 데이터들이 부정확하게 구분될 수 있다. 그냥 더 많은 열을 선택해서 데이터의 중복 여부를 구분하는 쪽이 낫다.
3. WHERE 이나 cross JOIN이 아닌 INNER JOIN을 사용해라
Catesian Join 또는 CROSS JOIN이라고도 불리는 Cartesian 연결은 변수의 모든 가능한 결합을 만들어 낸다. 1000명의 고객과 1000명의 총 판매 데이터가 있다면 필터링을 하기 전에 총 1,000,000개의 결과가 나올 것이다. 이는 데이터베이스가 너무 많은 일을 하게 만든다.
4. 필터를 사용할 때, HAVING 대신 WHERE을 사용해라
HAVING문은 WHERE문이 실행 된 뒤에 계산 된다. 만약 쿼리를 필터하는게 조건에 기반해 있다면, HAVING 보다 WHERE이 더 효율적이다.
5. 절의 끝에서만 와일드 카드를 사용해라
와일드카드는 문자화 된 값들을 찾을 때 아주 광범위하게 찾게 해준다. 그러나, 대부분의 검색은 효율성이 매우 떨어진다. 와일드카드를 사용하게 되면, 데이터베이스는 조건을 만족하는 값을 찾기 위해 모든 레코드를 뒤져야 한다.
Noida과 Noopur 같은 결과가 필요하다고 할 때에,
SELECT City FROM GeekTable WHERE City LIKE ‘%No%’보다는
SELECT City FROM GeekTable WHERE City LIKE ‘No%’ 이 훨씬 효율적이다.
6. LIMIT를 사용해서 쿼리 결과를 샘플링하라
쿼리 전체를 실행하기 전에, LIMIT를 사용해서 미리 표본을 보는 데에 사용해라. LIMIT을 사용하면 지정한 숫자만큼의 레코드만 반환하니까, 무거운 쿼리문을 모두 확인하고 수정하거나 개선할 고민을 할 필요가 없다.
7. 피크 타임이 아닐 때 쿼리를 실행하라
데이터베이스에 가해질 영향을 줄이기 위해 지금 데이터베이스를 사용하는 사람이 제일 적을 때, 예를 들면 늦은 밤 시간 같을 때에 쿼리를 실행하라.
