[MYSQL] SQL 쿼리문 최적화 - 효율적인 쿼리를 위한 팁

SQL 쿼리문 최적화 목표

1. 성능 향상(개선)

2. 응답 시간 단축

3. 기술 자원 보존

4. 비용 절약

 

SQL 효율적인 쿼리를 위한 팁

1.  Select only the columns you need. (필요한 컬럼만 선택하라)

SELECT * FROM customers;

데이터베이스에서 쿼리를 실행할 때 필요한 컬럼만 선택한다.

SELECT name,age,city FROM customers;

장점

1. 성능 향상 : 필요한 컬럼만 선택하면 데이터베이스 서버가 불필요한 작업을 수행하지 않아도된다. 컬럼의 개수가 적을수록 데이터 검색 및 처리 시간이 단축되고, 네트워크 부하가 감소한다. 쿼리의 실행 속도가 향상되고, 전체적인 시스템 성능이 향상될 수 있다.

2. 리소스 절약 : 필요한 컬럼만 선택함으로써 데이터 전송에 필요한 리소스를 절약할 수 있다. 컬럼의 개수가 적을수록 데이터의 용량이 줄어 들어 네트워크 대역폭을 덜 사용하게 된다. 네트워크 트래픽을 줄이고, 데이터 전송 비용을 절감하는 데 도움이 된다. (메모리 사용량 감소)

3. 가독성 개선 : 필요한 컬럼만 선택하면 결과 집합이 간결해지고 가독성이 향상된다. 필요한 데이터에 집중하여 결과를 파악할 수 있으며, 불필요한 컬럼을 제거함으로써 결과의 이해도를 높일 수 있다. (코드 유지 보수성 향상)

---

 2. Use LIMIT to preview query results. (쿼리 결과를 미리보기 위해 Limit을 사용하라)

LIMIT을 활용한다.

SELECT name FROM customers LIMIT 50;

장점

1. 성능 향상 : LIMIT를 사용하여 반환되는 행의 수를 제한함으로써 네트워크를 통한 데이터 전송량을 줄이고 전체 쿼리 성능을 향상시킬 수 있다. (실행 시간 단축)

 

2. 자원 절약 : LIMIT을 사용하여 일부 결과만 가져오면 데이터베이스 서버가 불필요한 작업을 수행하지 않아도 된다. 결과 집합이 작아지므로 네트워크 대역폭과 메모리 사용량을 줄일 수 있다. (효율적인 메모리 사용)

3. 데이터 검색 제어 : LIMIT 절은 반환되는 행의 수를 제어하여 특정 수의 행을 가져올 수 있도록 해준다. 특정 수의 상위 또는 하위 행을 선택하여 결과를 조정할 수 있다. 

---

3. Use wildcards only at the end of a phrase. (와일드카드는 문장의 끝에서만 사용하라)

WHERE 절에 와일드카드가 있는 경우, 구문 맨 마지막에만 사용한다.

SELECT name FROM customers WHERE state = 'A%';

장점

1. 인덱스 활용 : 데이터베이스에서 인덱스는 주로 값의 시작 부분에 대해 최적화되어 있다. 와일드카드를 구문의 끝에만 사용하면 인덱스를 효과적으로 활용할 수 있다. 데이터베이스는 와일드카드가 문장의 끝에 있는 경우 더 효율적인 검색 방법을 선택할 수 있으며, 인덱스를 효과적으로 활용하여 쿼리의 실행 계획을 최적화할 수 있다. (쿼리 최적화 가능성)

 

2. 검색 정확성 향상 : 와일드카드를 구문의 끝에만 사용하면 검색 범위를 제한할 수 있다. 'A%'로 검색하는 경우 'A'로 시작하는 값만 검색하게 된다. 이는 검색 결과를 더 정확하게 제한하여 검색 성능을 향상시킬 수 있다. (불필요한 결과 제외)

 

 * WHERE state = '%A%'와 WHERE state = 'A%' 의 차이점

1. '%A%' : 'A'가 포함되어 있는 경우를 검색 - 'ABC', 'DEFAB', 'GHA'와 같은 값이 검색 결과로 반환
2. 'A%' : :  'A'로 시작하는 경우를 검색 - 'ABC', 'ABCD', 'ADE'와 같은 값이 검색 결과로 반환

---

4. Avoid SELECT DISTINCT if possible. (가능하면 SELECT DISTINCT 문을 피하라)

중복된 값을 제거하기 위해 추가적인 처리 부담(많은 처리 능력을 요구)을 주어 성능 저하를 초래할 수 있다. 

SELECT DISTINCT name, age FROM customers;

원하는 고유한 결과를 제공하기 위해 충분한 컬럼 값을 선택한다.

SELECT name,age,gender,city,state,zip FROM customers;

또는 GROUP BY 절을 사용하여 중복된 값을 그룹화한다. 이를 통해 중복된 값을 그룹 단위로 처리할 수 있고, SELECT 문에서 필요한 컬럼만 선택할 수 있다. (결과를 그룹화)

SELECT name,age FROM customers GROUP BY name,age;

장점

1. 실행 시간 단축 : SELECT DISTINCT는 중복된 결과를 제거하기 위해 추가 작업을 수행해야 한다.(메모리 및 처리 능력을 필요) 결과 집합이 크거나 복잡한 경우에는 처리 시간이 증가할 수 있고, 쿼리의 복잡성이 높을수록 자원 사용량이 증가할 수 있다. 따라서 중복을 제거할 필요가 없는 경우에는 SELECT DISTINCT를 피함으로써 쿼리의 실행 시간을 단축시킬 수 있고, 불필요한 자원 소비를 줄일 수 있으며, 데이터베이스 서버의 성능을 향상시킬 수 있다.(자원 절약)

 

2. 성능 최적화 가능성 : SELECT DISTINCT는 쿼리 실행 계획을 최적화하는 데 영향을 줄 수 있다. 중복을 제거하는 작업은 추가적인 정렬이나 해시 연산을 수행해야 하므로 실행 계획이 변경될 수 있습니다. 쿼리 실행 계획을 더 효율적으로 최적화할 수 있다.

---

5. Run large queries during off-peak hours. (사용량이 적은 시간에 대규모 쿼리를 실행하라)

사용량이 적은 시간에 대규모 쿼리를 실행한다. (예시 : 2:00 ~ 5:00 AM)

 

장점

1. 리소스 활용 개선 : 주간에는 일반적으로 데이터베이스 서버에 대량의 트래픽이 발생할 수 있다. 주간 이외의 시간에 큰 쿼리를 실행하면 다른 사용자와의 리소스 경합을 피하고, 데이터베이스 서버의 리소스를 효율적으로 활용할 수 있다.

2. 사용자 영향 최소화 : 주간에는 일반적으로 많은 사용자가 시스템을 사용하고 있으므로, 큰 쿼리를 실행하는 경우에는 다른 사용자의 성능에 영향을 줄 수 있다. 주간 이외의 시간에 큰 쿼리를 실행하면 다른 사용자에게 불편을 주지 않고, 시스템 전체의 성능을 유지할 수 있다.

* 간단한 회고

SQL 쿼리문을 최적화하는 방법을 알아보다가 위 내용을 알게 되었다. 보통 SQL 성능을 향상(속도 개선)시키기 위해서 SQL문을 많이 수정하거나 서브쿼리문을 많이 활용 했었다. 단순히 1. 원하는 결과 값을 얻기 위해 2. 쿼리문의 응답 시간을 단축하기 위해 기본적인 개념을 뒤로한 채 앞만 보고 달려갔던 내 모습이 부끄러웠다. 요즘 쿼리 튜닝에 대해서 많이 관심을 갖고 있다. 이번기회에 실행계획을 통해 인덱스 튜닝하는 방법에 대해서 숙지하고, 실무에 적용 해보는 시간을 가져보려고 한다. 그전에 옵티마이저, 실행계획 등 기본적으로 알아야할 개념들이 많이 있다. 하나하나씩 파헤쳐보자.

 

 

* 참고

- SQL Query Optimization - Tips for More Efficient Queries