5. SQL문 튜닝 연습-(2)

1. 인덱스를 걸었는데도 인덱스가 작동하지 않는 경우 - 1

(id,name,age 컬럼 테이블에 name에 인덱스 걸고, 100만개의 더미 데이터 있는경우)

EXPLAIN SELECT * FROM users 
ORDER BY name DESC;

빨간부분 밑에는 인덱스 생성 안하고 조회, 위에부분은 name에 인덱스 생성후 조회한건데 차이 안나고 같다.

 

📌 왜 인덱스를 무시하고 풀 테이블 스캔할까?

이유:
→ 옵티마이저가 인덱스를 사용하는 것이 오히려 비효율적이라고 판단했기 때문

즉, 넓은 범위의 데이터를 조회할 때는 인덱스를 활용하는 것이 비효율적이라고 판단한다. 인덱스를 활용하지 않고 풀 테이블 스캔으로 데이터를 찾을 때 훨씬 효율적이라고 판단한다.

굳이 인덱스를 거쳤다가 각 원래 테이블의 데이터를 일일이 하나씩 찾아내는 것보다, 바로 원래 테이블에 접근해서 모든 데이터를 통째로 가져와서 정렬하는 게 효율적이라고 판단한 것이다. 실제 성능상으로도 풀 테이블 스캔을 통해 데이터를 가져오는 게 효율적이다.

⭐ [이것만은 꼭 기억해두자!]

• 넓은 범위의 데이터를 조회하는 경우, 인덱스를 사용해서 조회하는 것보다 풀 테이블 스캔이 효과적이라고 판단한다. (실제로도 그렇다.)

 

2. 인덱스를 걸었는데도 인덱스가 작동하지 않는 경우 - 2

(id,name,salary [1부터 1000000 사이의 랜덤값],create_at 컬럼 테이블에 단일 name,salary에 각각 인덱스 걸고, 100만개의 더미 데이터 있는경우)

# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE SUBSTRING(name, 1, 10) = 'User000000';

# 2달치 급여(salary)가 1000 이하인 유저 조회
SELECT * FROM users
WHERE salary * 2 < 1000
ORDER BY salary;

이 두가지의 경우 조회해보니 인덱스 걸어도 둘다 풀 테이블 스캔으로  type가 ALL로 나온다.

인덱스를 활용하지 않기 때문에 비효율적으로 데이터를 조회한다.

📌 왜 인덱스를 무시하고 풀 테이블 스캔할까?

SQL문을 작성할 인덱스 컬럼을 가공(함수 적용, 산술 연산, 문자역 조작 등)하면, MySQL은 해당 인덱스를 활용하지 못하는 경우가 많다. 따라서 인덱스를 적극 활용하기 위해서는 인덱스 컬럼 자체를 최대한 가공하지 않아야 한다. 

(쉽게 말하면 컬럼 자체에 함수 적용,연산,조작등 할시 모든 데이터에 가공후 비교하면서 조회해야하니 풀 스캔)

 

인덱스 컬럼을 가공해서 사용하지 않게 SQL문 수정하기

# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE name LIKE 'User000000%';

# 2달치 급여(salary)가 1000 이하인 유저 조회
EXPLAIN SELECT * FROM users
WHERE salary < 1000 / 2
ORDER BY salary;

이렇게 like 를 사용하거나 salary 컬럼 자체에 2배 말고 1000을 반으로 나눈 경우가 2달치 급여 1000이하랑 같으니 이렇게 한다.

⭐ [이것만은 꼭 기억해두자!]

• 인덱스 컬럼을 가공(함수 적용, 산술 연산, 문자역 조작 등)하면, MySQL은 해당 인덱스를 사용하지 못하는 경우가 많다. 따라서 인덱스를 적극 활용하기 위해서는 인덱스 컬럼 자체를 최대한 가공하지 않아야 한다.

3. ORDER BY문이 사용된 SQL문 튜닝하기

(id,name[이름뒤 7자리 랜덤값], department[이름의 7자리 랜덤값 10으로 나눈 나머지에 따라 정해진 정적값],

salary[1부터 1000000 사이의 랜덤값] ,create_at 컬럼 테이블, 100만개의 더미 데이터 있는경우)

SELECT * FROM users
ORDER BY salary
LIMIT 100;

 

 

대략 200ms 정도의 시간이 걸리고,type가 ALL이라 풀 테이블 스캔이니 비효율이다.

ORDER BY는 시간이 오래걸리는 작업이므로 최대한 피해주는 것이 좋다. 왜냐하면 정렬이라는 작업 자체가 다른 작업에 비해서 부담스러운 작업이며 성능에 안 좋은 영향을 끼치는 요소 중 하나이기 때문이다.

 

세부 계획을 보면

1. 풀 테이블 스캔으로 100만개(rows=1e+6)의 데이터에 액세스했다.
2. users.salary를 기준으로 정렬을 진행했다.
3. Limit으로 인해 100개의 데이터만 제한해서 조회했다.

📌 개선을 위한 인덱스 적용

CREATE INDEX idx_salary ON users (salary);

대략 30ms 정도 걸리고, 풀 테이블 스캔(type: ALL)이 아닌 인덱스 풀 스캔(type: index)을 활용해서 빠르게 데이터를 정렬해서 조회해왔다. 

세부 계획을 보면

 

1. 인덱스 스캔으로 정렬된 상태에서 100개 데이터 바로 조회
2. 별도의 정렬 작업 없음 (인덱스 덕분에 이미 정렬되어 있음)
3. LIMIT 100 → 필요한 100개 데이터만 반환

 

 

빨라진 이유는?

-> 개선전 세부계획이랑 비교해보면 정렬 부분이 빠져있다. 즉 정렬 작업이 없어서 빠르고, 데이터 엑세스가 100개뿐이라 그렇다.

 

근데 인덱스 풀스캔인데 왜 데이터 엑세스 100개 밖에 조회 안했나?

이유는 Limit 때문에 그렇다

 

⭐ [이것만은 꼭 기억해두자!]

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

 

4. WHERE문에 인덱스를 걸기 vs ORDER BY문에 인덱스를 걸기

(id,name[이름뒤 7자리 랜덤값], department[이름의 7자리 랜덤값 10으로 나눈 나머지에 따라 정해진 정적값],

salary[1부터 1000000 사이의 랜덤값] ,create_at 컬럼 테이블, 100만개의 더미 데이터 있는경우)

SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

 

 

• 응답 속도: 약 280ms
• type: ALL (풀 테이블 스캔)

📌 개선을 위해 인덱스 적용 고민

department 인덱스?  데이터 중 'Sales' 부서가 많음 → 효과 미미할 것 예상
그러면 created_at vs salary 중 어디에 인덱스를 걸까? 직접 해보고 비교해보자

 

1️⃣ salary 인덱스 적용

응답 속도	약 1100ms (더 느려짐!)
type	index (인덱스 스캔)
rows	약 800,000개 데이터 액세스

문제점

• salary 인덱스에는 created_at, department 정보가 없음
• 그래서:
  1. salary 정렬된 인덱스 → 데이터 하나씩 테이블 접근
  2. 테이블에서 created_at, department 조건 일일이 확인
• 사실상 풀 테이블 스캔과 다름없음 → 오히려 더 느림

2️⃣ created_at 인덱스 적용 (기존 salary 인덱스 삭제후 create_at 적용)

응답 속도	약 30ms (6~7배 개선!)
type	range (인덱스 레인지 스캔)
rows	1084개 데이터 액세스 → 134개 필터링 → 정렬 → 100개 반환

세부계획

1. created_at 인덱스 활용 → 최근 3일 이내 데이터 1084개 엑세스 데이터 조회 (index range scan)
2. department = 'Sales' 조건 필터링 → 엑세스 데이터 134개
3. salary 정렬 후 LIMIT 100

→ 인덱스 덕분에 불필요한 데이터 접근 대폭 감소

⭐ [이것만은 꼭 기억해두자!]

• ORDER BY의 특징 상 모든 데이터를 바탕으로 정렬을 해야 하기 때문에, 인덱스 풀 스캔 또는 테이블 풀 스캔을 활용할 수 밖에 없다. 이 때문에 ORDER BY문보다 WHERE문에 있는 컬럼에 인덱스를 걸었을 때 성능이 향상되는 경우가 많다.  (항상 그런건 아니니 성능 측정과 실행 계획을 살펴보는 습관을 들이자.)

 

5. HAVING문이 사용된 SQL문 튜닝하기

(테이블 컬럼 구성이 4번이랑 동일하고 거기에 age 컬럼만 추가 되어 있고,age에 인덱스를 적용한 상태)

쿼리문을 해석해보면 20~29살 각 나이별 사람들중 급여가 제일 높은 사람 출력 

CREATE INDEX idx_age ON users (age);

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

• 응답 속도: 약 800ms
• type: INDEX (인덱스 풀 스캔)
  
  

 

세부 실행 계획  

1. idx_age 인덱스를 통해 테이블 스캔 (100만 개 전부 액세스)
   → 이유: idx_age 인덱스에는 salary 정보가 없음
2. 모든 데이터 읽은 뒤, age 별로 GROUP BY ,각 나이 그룹에서 salary 최대값 계산 (MAX(salary))
3. 마지막으로 HAVING age >= 20 AND age < 30 조건으로 20~29세 그룹만 필터링

 왜 비효율적일까?

• 인덱스에는 age 값만 있으므로, salary 값을 확인하려면 테이블 전체 접근 필요
• 결국, 인덱스 풀스캔과 테이블 전체 스캔이 일어남 → 100만 개 데이터 전부 읽음
• 그 후에야 그루핑 + MAX 계산 → 조건 필터링

개선하기->  WHERE로 조건 이동 (HAVING 대신)

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

• 응답 속도: 약 150ms
• type: Range

세부 실행 계획

1. idx_age 인덱스를 통해 20~29에 해당하는 99993개의 데이터 엑세스(위에 방법 보다 엑세스 90만개 줄였으므로 향상)
2. 99993개의 데이터로 GROUP BY 하고 거기서 salary 최대값 계산

 

HAVING 대신에 WHERE문을 사용함으로써 GROUP BY를 처리하기 전에 데이터를 필터링했다. 그런 뒤에 필터링 된 데이터를 기반으로 GROUP BY를 진행했다. 

⭐ [이것만은 꼭 기억해두자!]

• HAVING문 대신에 WHERE문을 쓸 수 있는 지 체크해보자.
  (어쩔 수 없이 HAVING을 쓸 수 밖에 없는 경우도 존재한다.)