실행 계획(EXPLAIN) 활용

SQL이 어떻게 실행되는지 내부적으로 설명해주는 도구이다.

#실행 계획 조회하기
EXPLAIN [SQL문]

#실행 계획에 대한 자세한 정보 조회하기
EXPLAIN ANALYZE [SQL문]

# 기존 테이블 삭제
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
('박씨', 100),
('김씨', 90),
('홍씨', 88),
('이씨', 66),
('조씨', 88),
('하씨', 99),
('최씨', 77);

# 데이터 조회
SELECT * FROM users;

 

 

EXPLAIN

# 실행 계획 조회
EXPLAIN SELECT * FROM users WHERE AGE =99;

type : 테이블의 데이터를 어떤 방식으로 조회했는지

possible keys : 사용할 수 있는 인덱스 목록을 출력

key : 데이터 조회할 때 실제로 사용할 인덱스 값

rows : SQL문 수행을 위해 접근하는 데이터의 행 수 🚨이 값을 줄이는 것이 SQL 튜닝의 핵심이다.

Extra : 부가적인 정보 제공

 

✔️ type 종류

ALL은 풀 테이블 스캔이라고 테이블의 모든 테이터를 조회해서 데이터를 찾는다.

처음부터 끝까지 모두 조회하기 때문에 비효율적이다.

 

Index는 풀 인덱스 스캔이라고 인덱스 테이블을 처음부터 끝까지 조회해서 테이터를 찾는다.

인덱스는 실제 테이블보다 크기가 작고, 이미 정렬이 된 상태라 풀 테이블 스캔보다 효율적이다.

 

Const는 조회하고자 하는 1건의 데이터를 헤매지 않고 단번에 찾는다.

그래서 고유 인덱스 또는 기본키를 사용해서 1건의 데이터만 조회해야하는데 굉장히 효율적이다.

# 기존 테이블 삭제
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');

# 인덱스 조회
SHOW INDEX FROM users;

 

UNIQE 속성을 가진 컬럼은 인덱스가 자동으로 생성된다.

 

기본키에 접근해 row 하나만 조회한 것을 확인할 수 있다.

EXPLAIN SELECT * FROM users WHERE id = 3;

 

Range는 인덱스 레인지 스캔으로 인덱스를 활용해 범위 형태의 데이터를 조회한다.

범위 형태란 BETWEEN, 부등호, IN, LIKE를 활용한 데이터 조회이다.

인덱스를 활용하기 때문에 효율적이지만, 조회하는 범위가 큰 경우엔 인덱스를 사용해도 성능 저하될 수 있다.

 

Ref는 비고유 인덱스라고, UNIQE가 아닌 컬럼을 인덱스로 활용해 데이터를 조회한다.

 

 

EXPLAIN ANALYZE

# 실행 계획에 대한 자세한 정보 조회
EXPLAIN ANALYZE SELECT * FROM users WHERE AGE =99;

-> Filter: (users.age = 99)  (cost=0.95 rows=1) (actual time=0.0428..0.0469 rows=1 loops=1)
    -> Table scan on users  (cost=0.95 rows=7) (actual time=0.0388..0.044 rows=7 loops=1)

Table scan on users : users 테이블을 스캔한다.

rows : 접근한 데이터의 행의 수

actual time=앞 숫자..뒤숫자 : 마지막 데이터까지 접근한 시간

Filter : 필터링을 통해 데이터를 추출한다.

 

✔️ 정리

users 테이블의 모든 데이터 7줄에 접근했다.

그러고 그 데이터중 age = 99 조건에 만족하는 데이터만 필터링해서 조회했다.

 

---

 

JSCODE, MySQL 성능 최적화

https://inf.run/DzjSq