옵티마이저가 넓은 범위의 데이터를 조회할 때는 인덱스를 활용하는 것이 비효율적이라고 판단한다. 왜냐면, 굳이 인덱스를 거쳤다가 각 원래 테이블의 데이터를 일일이 하나씩 찾아내는 것보다, 바로 원래 테이블에 접근해서 모든 데이터를 통째로 가져와서 정렬하는게 효율적이라고 판단하기 때문이다.
(예시)
SELECT * FROM users
ORDER BY name DESC;
▶ 이런 경우 인덱스를 활용하지 않고 풀 테이블 스캔으로 데이터를 찾을 때 훨씬 효율적이라고 판단
넓은 범위의 데이터를 조회하는 경우, 인덱스를 사용해서 조회하는 것보다 풀 테이블 스캔을 이용하는 것이 효과적.
* 최적화를 위하여 넓은 범위의 데이터를 조회하는지 잘 파악이 안된다면, 인덱스를 적용한 후에 실행 계획 조회해 보자.
그림에 보이듯이 인덱스를 했음에도 불구하고 trype이 ALL 인 것을 볼 수 있다.
→ 풀 테이블 스캔으로 데이터를 찾을 때 훨씬 효율적
CREATE INDEX idx_name ON users (name);
EXPLAIN SELECT * FROM users
ORDER BY name DESC;
경우 2 :인덱스 컬럼을 가공(함수 적용, 산술 연산, 문자열 조작 등 )을 한 경우
id, name, salary, created_at 콜롬들을 가진 users 테이블에 100만 건의 랜덤 데이터 삽입
인덱스 생성
CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_salary ON users (salary);
실행 계획 조회해보기
# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE SUBSTRING(name, 1, 10) = 'User000000';
# 2달치 급여(salary)가 1000 이하인 유저 조회
EXPLAIN SELECT * FROM users
WHERE salary * 2 < 1000
ORDER BY salary;
첨부된 결과 사진에 있는 타입에 보이듯이 ALL 이다. 즉, 풀테이블 스캔을 한 것을 볼 수 있다.
인덱스 컬럼을 가공해서 사용하지 않게 SQL문 수정하기
# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE name LIKE 'User000000%'; # % 와일드 카드: 0개 이상의 어떤 문자든지 매칭될 수 있음
# 2달치 급여(salary)가 1000 이하인 유저 조회
EXPLAIN SELECT * FROM users
WHERE salary < 1000 / 2
ORDER BY salary;
SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
인덱스를 추가하기전
약 0.35초tyep = ALL
인덱스 추가 후
CREATE INDEX idx_created_at ON users (created_at);
약 0.056초type = range
created_at 에 인덱스를 추가함으로써 검색 타입이 ALL 에서 range 로 바뀌고 소요시간이 약 0.35 초 에서 0.056 초로 감소
Sales 부서이면서 최근 3일 이내에 가입한 유저 조회하기
데이터 생성
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
100만건 데이터 생성 후 데이터 조회해서 성능 측정
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
0.24초
실행계획 확인
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
#세부내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
CREATE INDEX idx_created_at ON users (created_at);
#성능평가
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
0.24초 에서 0.033 초로 소요시간 단축
type = range , 모든 테이블을 검색하지 않고 idx_created_at 인덱스 레인지 스캔을 함 데이터 접근 개수(rows)도 줄어들었다.
< department 컬럼을 기준으로 인덱스 추가 >
ALTER TABLE users DROP INDEX idx_created_at; # 기존 created_at 인덱스 삭제
CREATE INDEX idx_department ON users (department);
# 성능 측정
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
약 0.13초 소요 created_at 컬럼을 기준으로 인덱스 추가 한 경우가 소요시간이 훨씬 짧음
ref 타입, = 비고유 인덱스로 조회를 했다 rows = 191,314 는 인덱스를 하기전 보다 좋아 졌지만 created_at 컬럼을 기준으로 인덱스 추가 한 경우보다 많은 데이터에 접근
<department 와created_at 두컬럼에인덱스 추가>
# CREATE INDEX idx_department ON users (department); # 위에서 이미 추가함
CREATE INDEX idx_created_at ON users (created_at); # created_at 인덱스 추가
# 성능 측정
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
소요 시간은 idx_created_at 과 비슷
idx_created_at 과 똑같이 range 타입으로 일정 범위만 검색
-> Filter: (users.department = 'Sales') (cost=498 rows=212) (actual time=0.0929..11.6 rows=114 loops=1)
-> Index range scan on users using idx_created_at over ('2024-12-27 16:39:14' <= created_at), with index condition: (users.created_at >= <cache>((now() - interval 3 day))) (cost=498 rows=1106) (actual time=0.062..11.3 rows=1106 loops=1)
디테일을 확인해 보면 idx_created_at 만 사용
결론적으로 idx_department 을 사용하지 않았다는 것은 department 컬럼에 한 인덱스가 필요가 없고 판단하여 idx_department는 의미 성능평가에서도 비슷하므로 created_at 컬럼에만 인덱스를 하는 것이 가장 효율적인 최적화 방법으로 볼수 있다.
[이것만은 꼭 기억해두자!]
데이터 액세스(rows)를 크게 줄일 수 있는 컬럼은 중복 정도가 낮은 컬럼이다.
따라서 중복 정도가 낮은 컬럼을 골라서 인덱스를 생성해라.
< 멀티 컬럼 인덱스 >
공통 : 성능테스트
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
순서 (created_at, department)
ALTER TABLE users DROP INDEX idx_created_at;
ALTER TABLE users DROP INDEX idx_department;
CREATE INDEX idx_created_at_department ON users (created_at, department);
성능 테스트 결과 : 소요시간 약 0.0035 초
순서 (department,created_at)
ALTER TABLE users DROP INDEX idx_created_at_department;
CREATE INDEX idx_department_created_at ON users (department, created_at);
성능 테스트 결과 : 소요시간 약 0.0033초
멀티 컬럼 인덱스를 사용했지만 created_at 만 인덱스를 설정했을 때의 성능과 크게 차이가 나지 않는다.
’단일 컬럼에 설정하는 일반 인덱스’를 설정했을 때와‘멀티 컬럼 인덱스를 설정했을 때’의 성능 차이가 별로 나지 않는다면 ?
<실습> 모든 컨테이너 삭제후 무작위 숫자 API를 v3 버전의 컨테이너로 실행한다. 이번에는 헬스 체크 간격을 조금 줄인다. 컨테이너의 상태가 정상인지 확인하고 API를 몇번 호출해 상태가 이상으로 바뀌는지 확인하라.
# 기존 컨테이너를 모두 삭제 docker container rm -f $(docker container ls -aq) -a = 모든 컨테이너(중지된 컨테이너 포함)를 조회합니다. -q = 컨테이너 ID만 출력 -aq = 모든 컨테이너의 ID를 출력 $(...) = 명령어 치환으로 ( )에서 얻은 결과를 가져온다.
# API를 v3 버전의 이미지로 실행한다. docker container run -d -p 8080:80 --health-interval 5s diamol/ch08-numbers-api:v3
# 5초 정도 기다린 후 컨테이너 목록을 확인한다. docker container ls
# API 를 네번 호출 - 세번은 성공, 마지막은 실패 curl http://localhost:8080/rng
# 앱에 버그가 발생했다. 15초 기다린 후 상태가 이상으로 바뀌는지 확인한다. docker container ls
커맨드 실행 출력 내용
4번 째 요청 부터 버그가 발생 → HTTP 테스트 유틸리리티가 세 번 연속 실패를 반환 → unhealthy 상태로 변환
<실습> 웹 앱 버전 v3를 실행하라. API 가 없으므로 컨테이너가 바로 종료된다.
docker container run -d -p 8081:80 diamol/ch08-numbers-web:v3 docker container ls --all
diamol/ch08-numbers-web:v3 : 웹 앱 컨테이너의 디펜던스 체크가 실패해 컨테이너가 종료
diamol/ch08-numbers-api:v3 : api 컨테이너가 실행 중이지만 컨테이너 이름이 "number-api" 로 지정되지 않아서 웹 앱이 API 컨테이너를 발견하지 못한다.
도커 컴포즈에 헬스 체크와 디펜던시 체크 정의하기
디펜던시 체크에 실패했을 때 실행하던 컨테이너를 종료해야 하는 이유
▶ 단일 서버에서 앱을 실행 중이라면 이상이 생긴 컨테이너를 새 컨테이너로 교체하면 더 심각한 장애를 일으킬 수 있기 때문
▶하지만 종료된 컨테이너를 재시작하거나 이미지에 정의 되지 않은 헬스 체크를 추가할 수는 있다.
