[MySQL] Explain으로 성능 개선 포인트 찾기

들어가기 전 

이번 포스팅에서는 성능 개선 포인트를 찾기 전에  Explain의 컬럼인 Type에 따른 스캔방식에 대해서 알아보고 성능 개선하는 방법에 대해서 알아보겠습니다.

아래에서 설명할 내용들은 대부분 인덱스를 활용하는 내용입니다. 인덱스가 적용 안 되는 케이스에 대해서 궁금하신 분들은 아래 포스팅을 참고하시는 것을 추천드립니다.

https://hoestory.tistory.com/80

[MySQL] 인덱스 적용안되고 풀스캔이 적용되는 패턴

 

 

Explain에 대해서 알아보기 전 포스팅에서 설명할 예제들을 위한 테이블 및 데이터 삽입을 하겠습니다.

 

테이블 생성

 

CREATE TABLE users(
    id INT auto_increment primary key ,
    name varchar(100),
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

 

데이터 삽입

SET SESSION cte_data= 1000000;

INSERT INTO users (name, age, created_at)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 
)
SELECT
    CONCAT('Hoestory', LPAD(n, 6, '0')) AS name, 
    FLOOR(1 + RAND() * 100) AS age, 
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at
FROM cte;

지정한 테이블의 인덱스 정보를 확인하는 방법

SHOW INDEX FROM [테이블명]

 

위 쿼리를 실행시키면 테이블에 설정된 인덱스 정보에 대해서 볼 수 있습니다.

아래와 같이 인덱스 정보를 확인할 수 있습니다.

 

 

 

지금까지 예제에 사용할 테이블 세팅을 진행하였습니다.
이제 Explain의 결과 중 하나인 Type에 대해서 알아보겠습니다.

 

Explain Type의 종류

쿼리 성능 개선을 위해 Explain을 실행하면 아래와 같이 Type에 대한 값이 나오는 것을 확인할 수 있습니다.

 

 

타입의 종류는 아래와 같습니다.

• All
• Index
• range
• ref
• eq_ref
• const

 Explain을 실행했을 때 나오는 Type 종류에 대해서 알아봤습니다. 
이제부터 Type별 테이블을 스캔하는 방법에 대해서 알아보겠습니다.

 

All :  테이블 전체 스캔(Full Table Scan)

테이블 전체 스캔 데이터가 2건 존재하면 2건 모두 조회하고 100만 건이 존재하면 100만 건 모두 데이터를 조회하는 것을 의미합니다. 

즉 테이블에 저장되어 있는 모든 데이터를 조회를 하는 것입니다.

 

EXPLAIN SELECT * FROM users;

 

위 쿼리를 실행시키면 아래와 같이 Type이 ALL로 나오는 것을 확인할 수 있습니다.

 

 

Index : 인덱스 풀 스캔(Index Full Scan)

인덱스 풀 스캔은 인덱스 공간에 저장되어 있는 데이터를 모두 조회하는 것을 의미합니다.

테이블 전체 스캔과 유사하지만 인덱스를 통해서 데이터를 조회하기 때문에 테이블 전체 스캔에 비해 효율이지만 인덱스 데이터를 모두 읽어야 하기 때문에 완전 효율적이라고 볼 수 없습니다.

CREATE INDEX idx_name ON users(name)

EXPLAIN SELECT name FROM users ORDER BY name;

 

위 쿼리를 실행시키면 아래와 같이 Type이 ALL로 나오는 것을 확인할 수 있습니다.

 

range : 인덱스 범위 스캔(Index Range Scan)

인덱스 범위 스캔은 인덱스를 활용해 범위 형태로 데이터를 조회하는 것을 의미합니다.

LIKE, 부등호(=, <,>, <=,>=), between 등을 사용할 때 인덱스 범위스캔이 됩니다. 범위가 좁으면 효율적이지만 범위가 넓어질수록 비효율적입니다.

 

인덱스 생성

CREATE INDEX idx_age ON users(age)
CREATE INDEX idx_name ON users(name)

 

 

between

 

EXPLAIN SELECT * FROM users WHERE age between 10 AND 20;

 

 

 

부등호

EXPLAIN SELECT * FROM users WHERE age <= 10 ;

 

 

 

LIKE

 

EXPLAIN SELECT * FROM users WHERE name like 'Hoestory00000%';

 

 

 

ref 

ref 타입은 비고유 인덱스를 활용하여 특정 데이터 1건을 조회할 때 발생합니다.

비고유 인덱스는 고유하지 않는 데이터를 기준으로 인덱스를 생성한 것을 의미합니다.

 

CREATE INDEX idx_name ON users(name);
EXPLAIN * FROM users WHERE name = 'Hoestory000001';

 

 

 

const

const 타입은 ref 타입과 반대로 고유한 컬럼 또는 기본키를 통해서 특정 데이터 1건을 조회할 때 발생합니다.

 

EXPLAIN SELECT * FROM users WHERE id = 1;

 

 

 

지금까지 Type의 종류와 어떤 상황에서 어떤 Type이 나오는지 확인하였습니다.
이제부터 성능 개선을 위해 무엇을 확인해야 하는지 알아보겠습니다.

 

 

1. Explain으로 테이블을 어떤 방식으로 조회하는지와 조회하는 rows 확인

 

만약 Type이 ALL일 경우 인덱스를 생성하여 테이블 전체 스캔방식을 고쳐야 합니다.

테이블 전체 스캔방식을 인덱스 스캔방식으로 수정하기 위해 무작정 인덱스를 생성하면 오히려 성능이 느려질 수 있습니다.

그래서 인덱스를 생성 후 아래 명령어를 통해서 인덱스에 대한 정보를 확인합니다.

SHOW INDEX FROM [테이블명]

 

위 쿼리를 실행시키면 컬럼의 카디널리티, 인덱스명 등 인덱스에 대한 정보를 확인할 수 있습니다.

인덱스를 만들기 위해 중복도가 낮은 즉 카디널리티가 높은 컬럼으로 생성하면 성능이 좋아질 수 있습니다.

그리고 조회하는 rows가 적을수록 성능이 좋아지기 때문에 되도록 rows는 적은 데이터를 조회할 수 있도록 수정하여합니다.

 

 

2. Explain Analyze를 활용하여 실행시간 확인

 

 

Explain Analyze를 실행하면 이미지처럼 나오는데 읽는 방법은 아래에서 위로 읽습니다.

뎁스가 깊은 곳에서 얕은 곳으로 읽어 나가면 됩니다.

그리고 각 뎁스마다 "actual time=시작시간.. 끝시간" 형태로 실행시간을 나타냅니다.

실행시간을 통해서 어떤 동작이 얼마나 걸리는지 확인할 수 있습니다.

계산하는 방법은 아래와 같습니다.

 

-> Nested loop inner join  (cost=449599 rows=997632) (actual time=0.1..1197 rows=1e+6 loops=1)-- 2
    -> Table scan on p  (cost=100428 rows=997632) (actual time=0.0795..162 rows=1e+6 loops=1) -- 1

 

2번 동작의 실행시간을 계산하는 방법은 "2번 끝 실행시간(1197ms) - 1번 끝 실행시간(162ms)"입니다.
계산을 한 결괏값을 토대로 각 동작의 실행시간을 확인할 수 있어 성능이 느린 부분을 파악할 수 있습니다.

위에서 설명한 내용을 기반으로 성능이 좋지 않은 쿼리를 확인할 수 있습니다.