옵티마이저가 넓은 범위의 데이터를 조회할 때는 인덱스를 활용하는 것이 비효율적이라고 판단한다. 왜냐면, 굳이 인덱스를 거쳤다가 각 원래 테이블의 데이터를 일일이 하나씩 찾아내는 것보다, 바로 원래 테이블에 접근해서 모든 데이터를 통째로 가져와서 정렬하는게 효율적이라고 판단하기 때문이다.
(예시)
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 만 인덱스를 설정했을 때의 성능과 크게 차이가 나지 않는다.
’단일 컬럼에 설정하는 일반 인덱스’를 설정했을 때와‘멀티 컬럼 인덱스를 설정했을 때’의 성능 차이가 별로 나지 않는다면 ?
이 실행 계획을 보고 비효율적으로 처리하는 방식이 있는 지 점검하고, 비효율적인 부분이 있다면 더 효율적인 방법으로 SQL문을 실행하게끔 튜닝을 하는 게 목표
사용하는 방법
# 실행 계획 조회하기
EXPLAIN [SQL문]
# 실행 계획에 대한 자세한 정보 조회하기
EXPLAIN ANALYZE [SQL문]
사용예시
가정 : 콜롬이 name, age 로 구성된 users 테이블에 7명의 데이터가 저장되어 있다.
EXPLAIN [SQL문]
EXPLAIN SELECT * FROM users
WHERE age = 23;
결과
id : 실행 순서
select_type : (처음에는 몰라도 됨)
table : 조회한 테이블 명
partitions : (처음에는 몰라도 됨)
type : 테이블의 데이터를 어떤 방식으로 조회하는 지
possible keys : 사용할 수 있는 인덱스 목록을 출력
key : 데이터 조회할 때 실제로 사용한 인덱스 값
key_len : (처음에는 몰라도 됨)
ref : 테이블 조인 상황에서 어떤 값을 기준으로 데이터를 조회했는 지
rows : SQL문 수행을 위해 접근하는 데이터의 모든 행의 수 (= 데이터 액세스 수) → 이 값을 줄이는 게 SQL 튜닝의 핵심
filtered : 필터 조건에 따라 어느 정도의 비율로 데이터를 제거했는 지 의미 → filtered 비율이 낮을 수록 쓸데없는 데이터를 많이 불러온 것. → filtered의 값이 30이라면 100개의 데이터를 불러온 뒤 30개의 데이터만 실제로 응답하는데 사용했음을 의미한다.
Extra : 부가적인 정보를 제공 → ex. Using where, Using index
(주의점)rows,filtered의 값은 정확한 수치가 아닌 추정값이기 때문에 오차가 있을 수 있다.
실행 계획에서 type 의미 분석하기
All : 풀 테이블 스캔
인덱스를 활용하지 않고 테이블을 처음부터 끝까지 전부다 뒤져서 데이터를 찾는 방식 → 비효율적이므로 최적화 필요
DROP TABLE IF EXISTS users; # 기존 테이블 삭제
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT
);
INSERT INTO users (name, age) VALUES
('Alice', 30),
('Bob', 23),
('Charlie', 35);
# 실행 계획 조회하기
EXPLAIN SELECT * FROM users WHERE age = 23;
type 에 ALL 이 나온 이유
users 테이블의 데이터는 age를 기준으로 정렬되어 있지 않고 id를 기준으로 정렬되어 있다.
age = 23의 값을 가진 데이터를 찾으려면 테이블의 처음부터 끝까지 다 뒤져봐야 한다.
그래서 실행 계획의 type이 ALL로 나온 것
index :풀 인덱스 스캔
인덱스 테이블을 처음부터 끝까지 다 뒤져서 데이터를 찾는 방식 →인덱스의 테이블은 실제 테이블보다 크기가 작기 때문에,풀 테이블 스캔(Full Table Scan)보다 효율적
# 높은 재귀(반복) 횟수를 허용하도록 설정
# 생성할 더미 데이터의 개수와 맞춰서 작성
SET SESSION cte_max_recursion_depth = 1000000;
# 더미 데이터 1000000 삽입 쿼리
INSERT INTO users (name, age)
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 1000000
)
SELECT
CONCAT('User', LPAD(n, 7, '0')), -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
FLOOR(1 + RAND() * 1000) AS age -- 1부터 1000 사이의 난수로 나이 생성
FROM cte;
# 인덱스 생성
CREATE INDEX idx_name ON users (name);
# 실행 계획 조회
EXPLAIN SELECT * FROM users
ORDER BY name
LIMIT 10;
name을 기준으로 정렬해서 데이터를 가져와야 하기 때문에,name을 기준으로 정렬되어 있는 인덱스를 조회
덩치가 큰users테이블의 데이터를 하나씩 찾아보면서 정리를 하는 것보다, 이미name을 기준으로 정렬되어 있는 인덱스를 참고하는 게 효율적이라고 판단
모든 인덱스의 값을 다 불러온 뒤에 최상단 10개의 인덱스만 추출
10개의 인덱스에 해당하는 데이터를users테이블에서 조회
const : 1건의 데이터를 바로 찾을 수 있는 경우
고유 인덱스 또는 기본 키를 사용해서 1건의 데이터만 조회한 경우에 type 에 const가 출력된다.
예시
DROP TABLE IF EXISTS users; # 기존 테이블 삭제
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
account VARCHAR(100) UNIQUE
);
INSERT INTO users (account) VALUES
('user1@example.com'),
('user2@example.com'),
('user3@example.com');
EXPLAIN SELECT * FROM users WHERE id = 3;
EXPLAIN SELECT * FROM users WHERE account = 'user3@example.com';
UNIQUE 로 설정한 콜롬은 인덱스가 자동으로 생성이 되고 고유한 값이므로 한번의 검색으로 해결이 된다.
range :인덱스 레인지 스캔(Index Range Scan)
인덱스를 활용해 범위 형태의 데이터를 조회한 경우를 의미
범위 형태란 BETWEEN, 부등호(<, >, ≤, ≥), IN, LIKE를 활용한 데이터 조회
데이터를 조회하는 범위를 신경써야 한다.
예시
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
age INT
);
-- 높은 반복 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성)
SET SESSION cte_max_recursion_depth = 1000000;
-- 더미 데이터 삽입 쿼리
INSERT INTO users (age)
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT
FLOOR(1 + RAND() * 1000) AS age -- 1부터 1000 사이의 난수로 나이 생성
FROM cte;
-- 인덱스 생성
CREATE INDEX idx_age ON users(age);
실행계획 조회
EXPLAIN SELECT * FROM users
WHERE age BETWEEN 10 and 20;
EXPLAIN SELECT * FROM users
WHERE age IN (10, 20, 30);
EXPLAIN SELECT * FROM users
WHERE age < 20;
ref :풀 인덱스 스캔
UNIQUE가 아닌 컬럼의 인덱스를 사용한 경우 type에 ref가 출력
예시
DROP TABLE IF EXISTS users; # 기존 테이블 삭제
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO users (name) VALUES
('박재성'),
('홍길동'),
('임정우');
CREATE INDEX idx_name ON users(name);
