쿼리 최적화

 

본 내용은 real MySQL 책을 참조하여 쓰여졌습니다!

 

1. 실행 순서

 

일반적으로 INSERT와 UPDATE는 레코드 단위로 이루어지므로 성능상 문제가 되는 경우는 별로 없습니다. 하지만 SELECT는 여러개의 테이블로부터 데이터를 조합해서 빠르게 가져와야 하기 때문에 여러 개의 테이블을 어떻게 읽을 것인가에 많은 주의를 기울여야 합니다.

 

SELECT s.emp_no, COUNT(DISTINCT e.first_name) AS cnt
FROM salaries s
INNER JOIN employees e ON e.emp_no = s.emp_no
WHERE s.emp_no IN (100001, 100002)
GROUP BY s.emp_no
HAVING AVG(s.salary) > 100
ORDER BY AVG(s.salary)
LIMIT 10;

 

SELECT 절: SELECT s.emp_no, COUNT(DISTINCT e.first_name) AS cnt
FROM 절: FROM salaries s INNER JOIN employees e ON e.emp_no = s.emp_no
WHERE 절: WHERE  s.emp_no IN (100001, 100002)
GROUP BY 절: GROUP BY s.emp_no
HAVING 절: HAVING AVG(s.salary) > 100
ORDER BY 절: ORDER BY AVG(s.salary)
LIMIT 절: LIMIT 10;

 

쿼리 실행 순서

 

왜 실행 순서를 알아야 할까요?

 

1) 이거 왜 안되나요?을 안하기 위해

SELECT CONCAT(first_name, last_name) AS full_name
FROM user
---
WHERE full_name = 'VioletBeach'; (x)
ORDER BY full_name; (o)
---

 

Where 절에서는 SELECT 절보다 먼저 실행됩니다. 즉, WHERE 절은 FROM 절의 결과를 가지고 필터링을 하는 용도이지 SELECT문에서 사용한 AS를 활용할 수 없습니다. 그래서 해당 쿼리는 에러가 발생합니다. 반대로 ORDER BY는 SELECT문보다 늦게 실행되므로 가능합니다.

 

2) 성능 최적화를 위해

 

SELECT -> FROM -> WHERE 순으로 작성하는 것이 아니라 FROM -> WHERE -> SELECT 순으로 작성하면 앞에서부터 처리할 양을 최적화하며 나아갈 수 있습니다. 비슷한 예시로 GROUP BY 연산 시에는 가급적 HAVING보단 WHERE를 사용하는 게 좋겠습니다.

 

2. INDEX

 

1. INDEX 칼럼 값 자체에 변환이 있어서는 안됩니다.

 

SELCT * FROM salaries WHERE salary*10 > 150000; (salary index가 있다고 가정) ➔ index 사용 불가

 

2. 비교 연산에서 양쪽의 두 데이터 타입은 일치해야 합니다.

 

3. WHERE 절의 경우 인덱스 컬럼의 순서와 WHERE 조건절의 순서가 다르더라고 옵티마이저가 적절히 최적화해주는 편입니다. 하지만 ORDER BY와 GROUP BY의 경우 좀 다릅니다.

 

ORDER BY, GROUP BY에서의 인덱스

 

1) 인덱스를 구성하는 순서와 GROUP BY에 명시된 컬럼의 순서가 동일해야 한다.

2) 인덱스를 구성하는 컬럼 중 뒤에 있는 컬럼이 GROUP BY 절에 명시되지 않아도 인덱스를 사용할 수 있지만 인덱스 앞쪽에 있는 컬럼이 GROUP BY 절에 명시되지 않으면 인덱스를 사용할 수 없다.

3) GROUP BY 절에 명시된 컬럼이 하나라도 인덱스에 없으면 GROUP BY 절은 인덱스를 사용할 수 없다.

 

(COL_1, COL_2, COL_3, COL_4)가 인덱스라면 다음은 모두 인덱싱을 사용할 수 없다.
GROUP BY COL_2, COL_1
GROUP BY COL_1, COL_3, COL_2
GROUP BY COL_1, COL_3
GROUP BY COL_1, COL_2, COL_3, COL_4, COL_5

사실 사용 가능한 패턴은 다음 밖에 없습니다.
GROUP BY COL_1
GROUP BY COL_1, COL_2
GROUP BY COL_1, COL_2, COL_3
GROUP BY COL_1, COL_2, COL_3, COL_4

 

4. WHERE 절에서 Is NULL 인덱스 연산이 있다면 해당 계산의 결과값을 써서는 안 됩니다.

 

5. Short-Circuit Evaluation은 쿼리에도 적용됩니다. 해당 조건으로 쿼리해오는 양을 줄일 수 있습니다.

 

6. INDEX의 경우 카디널리티가 높은 column을 선택하는 것이 성능이 제일 잘 나옵니다.

 

7. INDEX 조회는 직접 레코드 조회보다 4~5배 비용이 드므로 테이블 스캔 규모가 크면 효율이 떨어집니다.

 

 

 

3. JOIN

 

 

드라이빙 테이블: 먼저 액세스 되는 쪽

드라이븐 테이블: 나중에 엑세스 되는 쪽

 

기본적으로 FROM 절의 순서의 영향을 받지만 옵티마이저의 기준에 따라 달라집니다.

 

5000만건의 A 테이블과 1000건의 B 테이블이 있으면 5000만건을 먼저 드라이빙하면 5000만번 반복하여 B 테이블을 탐색하며 1000건인 B가 먼저 드라이빙 되면 최대 1000번 A 테이블 탐색이 반복된다. 즉 작업 대상이 되는 행의 수가 적은 테이블부터 액세스되어야 한다.

 

SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no = de.emp_no

 

1) 모두 인덱스가 있을 때: 어느 쪽이든 검색 작업이 빠르게 이루어질 수 있으므로 레코드 크기에 따라 옵티마이저가 알아서 고릅니다.

 

2) employees.emp_no에만 인덱스가 있을 경우: dept_emp 테이블을 드라이빙으로 선택하고 employees 테이블을 드리븐 테이블로 선택합니다. 

 

3) 두 칼럼 모두 인덱스가 없는 경우: 어느 테이블을 드라이빙으로 선택하더라도 풀 스캔이 발생하므로 레코드 건수가 적은 테이블을 드라이빙 테이블로 선택합니다. 다 만 SQL 8.0부턴 블록 네스티드 루프 조인 대신 해시 조인 알고리즘을 사용하는데 해시 조인에서 주의할 점은 네스티드-루프 조인은 읽 은 레코드의 순서가 다른 테이블이 모두 조인돼도 그대로 유지되지만 해시 조인은 순서가 달라질 수 있습니다.

 

4. 기타팁

1. ORM을 쓴다면 해당 ORM이 어떤 쿼리를 만드는지 반드시 확인해야 합니다.

 

2. CHAR과 VARCHAR의 차이는 고정된 크기냐 아니냐의 차이가 있습니다. VARCHAR의 경우 맨 앞에 해당 CHAR의 크기 를 저장하고 있습니다. 고정된 크기인 것이 확실하다면 (주민번호, 핸드폰 번호 등) CHAR를 사용해야 하는 편이 좋은

좋은데 VARCHAR이 더 큰 값으로 update 된다면 레코드 자체를 다른 공간으로 옮겨서 저장하는 비용이 더 들기 때문입니다.

 

3. SELECT 시 꼭 필요한 컬럼만 불러와야 합니다.

 

4. LIKE 사용 시 “%”의 경우 String 뒷부분에만 쓰는 것이 좋습니다. 인덱스 사용 여부에 영향을 줍니다.

 

5. ORDER BY는 가장 바깥쪽 쿼리에 배치해야 합니다.

 

6. 복잡한 쿼리 하나를 쓴다면 여러 쿼리로 분할하는 것을 고민해봅시다.