MySQL ‐ SQL문 튜닝 실습 - dnwls16071/Backend_Study_TIL GitHub Wiki

📚 한 번에 많은 데이터를 조회하는 SQL문 튜닝

SELECT * FROM users LIMIT 10000;
  • 한 번에 모든 데이터를 조회하는 경우는 없다
  • 조회하는 데이터의 개수가 성능에 많은 영향을 끼치기 때문이다.
  • LIMIT, WHERE문 등을 활용하면 한 번에 조회하는 데이터의 수를 줄일 수 있다.

📚 WHERE문 SQL 튜닝

  • 최근 3일 이내 가입한 유저 조회하기 - 가입일자를 기준으로 하는 인덱스를 생성해서 조회한다.

스크린샷 2025-01-27 오후 8 37 04

  • Sales 부서이면서 최근 3일 이내 가입한 유저 조회하기 - 여러 가지 방법을 생각할 수 있다.
    • 가입일자를 기준으로 하는 인덱스 생성하기
    • 부서명을 기준으로 하는 인덱스 생성하기
    • 가입일자, 부서명을 모두 가지는 멀티 컬럼 인덱스 생성하기

❗모든 경우에 대해서 체크를 하고 실질적으로 성능 향상에 큰 효과가 있는 것을 택하면 된다. ❗데이터 액세스를 줄이려면 중복 정도가 낮은 컬럼에 인덱스를 생성해야 한다는 것을 잊지 말자. ❗첫 번째, 두 번째 방법은 단일 컬럼 인덱스에 해당하고 세 번째 방법은 멀티 컬럼 인덱스에 해당한다. 이 단일 컬럼 인덱스와 멀티 컬럼 인덱스의 성능 차이가 크게 없다면 멀티 컬럼 인덱스보다는 단일 컬럼 인덱스를 사용하도록 한다.

📚 인덱스를 걸어도 인덱스가 작동하지 않는 경우

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE,  # name UNIQUE 속성 부여
    age INT
);

EXPLAIN SELECT * FROM users 
ORDER BY name DESC;

실행 계획 조회

스크린샷 2025-01-27 오후 8 57 40

❓이름을 기준으로 조회하는 실행 계획을 조회했는데 왜 index가 아니고 ALL(풀 테이블 스캔)이 나오는 걸까?

  • 그 이유는 옵티마이저가 넓은 범위의 데이터를 조회할 때는 인덱스를 활용하는 것이 비효율적이라고 판단하기 때문이다.
  • 인덱스를 활용하지 않고 풀 테이블 스캔으로 데이터를 찾을 때가 훨씬 효율적이라고 판단한 것이다.
  • 즉, 굳이 인덱스를 거쳐서 각 원래 테이블 데이터를 일일이 하나씩 찾아내는 것보다 바로 원래 테이블에 다이렉트로 접근해서 모든 데이터를 통째로 가져와서 정렬하는 것이 효율적이라고 판단한 것이다.
# Bad
SELECT * FROM users
WHERE salary * 2 < 1000 # 직접적으로 인덱스 컬럼에 대한 가공 연산을 하게 되면 인덱스가 걸리지 않는다.
ORDER BY salary;

# Good
SELECT * FROM users
WHERE salary < 1000 / 2
ORDER BY salary;

❗넓은 범위의 데이터를 조회할 때는 인덱스를 사용해서 조회하는 것보다 풀 테이블 스캔이 효과적이다. ❗또한, 인덱스 컬럼을 가공할 경우 MySQL은 해당 인덱스를 사용하지 못하는 경우가 많다. 따라서 인덱스를 활용하려면 인덱스 컬럼 자체는 최대한 가공하지 말아야 한다.

📚 ORDER BY문 SQL 튜닝

  • ORDER BY는 시간이 오래 걸리는 작업이므로 최대한 지양하자. 인덱스를 사용하면 미리 정렬을 해 둔 상태가 되기 때문에 ORDER BY를 사용해서 정렬해야 하는 번거로운 작업을 피할 수 있게 된다.
  • 또한 LIMIT 없이 큰 범위의 데이터를 조회해오는 경우 옵티마이저가 인덱스를 활용하지 않고 풀 테이블 스캔을 해버릴 수 있다. 따라서 성능 효율을 위해서 LIMIT을 통해 작은 데이터 범위를 조회해오도록 하자.

📚 WHERE문 인덱스와 ORDER BY문 인덱스 비교

  • ORDER BY의 경우 기본적으로 모든 데이터를 바탕으로 정렬을 해야 하기 때문에 테이블 풀 스캔(ALL) 혹은 인덱스 풀 스캔(index)을 활용할 수 밖에 없다.
  • 그렇기 때문에 ORDER BY보다 WHERE문에 있는 컬럼에 인덱스를 걸었을 때 성능이 향상되는 경우가 많다.

📚 HAVING문 SQL 튜닝

  • HAVING절은 WHERE절과 동일하다. 단 조건 내용에 그룹 함수를 포함하는 것만을 포함한다.
  • HAVING절을 사용하려면 반드시 GROUP BY절이 선결되어야 한다.
# Bad
SELECT age, MAX(salary) FROM users
GROUP BY age # 나이 컬럼을 그룹화
HAVING age >= 20 AND age < 30; # 나이 컬럼 즉, 그룹 함수 포함 조건

# Good
SELECT age, MAX(salary) FROM users
WHERE age >= 20 AND age < 30
GROUP BY age;

참고 - GROUP BY, HAVING ❗HAVING 대신에 WHERE문을 쓸 수 있는지도 체크해본다.