[MySQL] Explain, 카디널리티 개념과 쿼리 성능 개선하기 : 인덱스는 반드시 성능을 보장하지 않는다.

MySQL에서 쿼리 성능을 최적화하는 첫 번째 단계는 쿼리가 어떻게 실행되는지 분석하는 것입니다. 이를 위해 `EXPLAIN` 명령어를 활용하여 쿼리 계획을 확인할 수 있습니다.

이번 포스팅에서는 EXPLAIN 명령어의 사용법과 카디널리티`Cardinality`를 어떻게 해석하고 쿼리 성능 개선에 활용할 수 있는지에 대해 알아보겠습니다.

예제 테이블

create table users
(
    id bigint auto_increment primary key ,
    nickname varchar(50) not null ,
    email varchar(100) not null ,
    password varchar(100) not null ,
    created_at datetime(6) not null ,
    updated_at datetime(6) not null
);

create table subscriptions
(
    id bigint auto_increment primary key ,
    user_id bigint not null ,
    title varchar(50) null ,
    start_date date not null ,
    payment_day int not null ,
    payment_amount bigint not null ,
    payment_cycle enum ('DAILY', 'MONTHLY', 'WEEKLY', 'YEARLY') not null ,
    payment_method  enum ('BANK_TRANSFER', 'CARD') not null ,
    payment_status  enum ('COMPLETED', 'PENDING') not null ,
    memo varchar(250) null ,
    created_at datetime(6) not null ,
    updated_at datetime(6) not null ,
    foreign key (user_id) references users (id)
);

사용자 - 사용자 구독 항목 1:N

실제 쿼리 성능 개선을 위해 `10만명의 회원 데이터`와 `100만개의 구독항목`을 추가하였습니다.

EXPLAIN

`MySQL`에서는 최적의 쿼리를 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장되어 있는지를 참조하고, 해당 데이터를 기반으로 최적의 실행 계획을 수립해주는 `쿼리 옵티마이저`가 내부적으로 동작합니다.

`EXPLAIN` 명령어는 데이터베이스가 쿼리를 실행하기 위해 사용하는 실행 계획`Query Plan`을 보여줍니다. 이를 통해 쿼리가 어떤 방식으로 실행될지 확인할 수 있습니다.

사용 방법

EXPLAIN SELECT 컬럼.. FROM 테이블;

실행 계획을 세울 쿼리 앞에 `EXPLAIN` 명령어를 사용합니다.

EXPLAIN SELECT * FROM users u
INNER JOIN user_subscriptions us ON u.id = us.user_id
INNER JOIN subscriptions s ON s.id = us.subscription_id;

`id` 실행 순서를 나타냅니다. EXPLAIN 결과에서 각 쿼리의 실행 단계가 어떤 순서로 진행될지를 보여줍니다.
`select_type` 쿼리 유형을 나타냅니다. 예를 들어 SIMPLE은 단일 쿼리를 의미하며, SUBQUERY는 서브쿼리를 나타냅니다.
`table` 쿼리에서 사용된 테이블을 나타냅니다. 어떤 테이블이 쿼리에 포함되어 있는지를 보여줍니다.
`partitions` 파티셔닝된 테이블인 경우, 해당 테이블에서 사용되는 파티션을 나타냅니다.
`type` 데이터 액세스 방법을 나타냅니다. ALL, index, ref 등 여러가지 유형이 있으며, 이 값은 데이터 베이스가 쿼리 데이터를 찾는 방식에 대한 정보를 제공합니다.
`possible_keys` 쿼리 실행 시 사용할 수 있는 인덱스를 나열합니다. 선택할 수 있는 인덱스들이 어떤 것들인지를 보여줍니다.
`key` 실제 쿼리 실행 시 사용된 인덱스를 나타냅니다. 이 값은 `possible_keys`에 나열된 인덱스 중에서 실제로 선택된 것을 보여줍니다.
`key_len` 선택된 인덱스의 길이를 나타냅니다. 인덱스의 길이가 길면 비효율적일 수 있으므로, 가능한 경우 효율적인 인덱스를 사용하는 것이 좋습니다.
`rows` 예상되는 처리 행 수입니다. 쿼리가 실행될 때 처리할 것으로 예상되는 행의 수를 보여줍니다.
`filtered` 조건문이 적용된 후 남게 될 행 수의 비율을 나타냅니다. 예를 들어, WHERE 절이 적용된 후 필터링 된 결과가 몇 퍼센트인지를 표시합니다.
`extra` 쿼리 실행과 관련된 추가적인 정보를 제공합니다. 예를 들어, Using where는 WHERE 절이 적용되었음을 나타내며, Using index 는 인덱스를 사용하여 데이터가 검색되었음을 의미합니다.

EXPLAIN ANALYZE

`EXPLAIN`은 실제 실행 결과를 다루지 않고, 실행 전 예측에 기반합니다.

그렇기에 항상 최적의 실행계획을 보장하지는 않고, 때로는 실제 실행 전략이 크게 다를 수도 있습니다.

특히 DB 옵티마이저에게 제공되는 통계 정보(데이터의 분포도와 인덱스 정보 등)가 부정확한 경우 잘못된 실행계획을 세울 가능성이 높습니다.

`EXPLAIN ANALYZE`는 쿼리를 실제로 실행한 후, 실제 실행 결과를 기반으로 실행 계획을 제공합니다.

MySQL이 제공하는 쿼리 프로파일링 도구이며 (MySQL 8.0.18 부터 지원) 쿼리 실행 중 MySQL이 어디서 시간을 많이 썼는지, 그 이유가 무엇인지 분석이 가능합니다.

결과적으로 실행 전 계획과, 실제 측정치를 함께 출력합니다.

EXPLAIN → 쿼리를 실행하지 않고, 실행 과정을 예측
EXPLAIN ANALYZE → 쿼리를 실행하고, 실행 과정을 분석

그렇다고 해서 `EXPLAIN`을 사용하지 않고 `EXPLAIN ANALYZE`만을 사용하지는 않습니다.

`EXPLAIN ANALYZE`는 실행 결과가 나와야 하기 때문에, `높은 슬로우 쿼리`, `상용 환경에서 부하가 큰 쿼리`를 분석 시 부담이 될 수 있습니다.

이럴 경우 실행하지 않아도 전략을 예측할 수 있는 `EXPLAIN`이 더 유용할 수 있습니다.

사용방법

EXPLAIN ANALYZE SELECT 컬럼.. FROM 테이블;

`EXPLAIN` 사용 시와 크게 달라진 점은 없으며 분석할 쿼리 앞에 `EXPLAIN ANALYZE` 명령어를 붙이고 실행합니다.

EXPLAIN ANALYZE SELECT * FROM users u
INNER JOIN user_subscriptions us ON u.id = us.user_id
INNER JOIN subscriptions s ON s.id = us.subscription_id;

`actal` 실행 결과
`time` 실행 시간(ms)
`time=a...b 중 a` 첫번째 행을 읽어오는데 들었던 시간의 평균(ms)
`time=a...b 중 b` 모든 행을 읽어오는데 들었던 시간의 평균(ms)
`rows` 반환된 행수의 평균
`loops` 작업을 반복한 횟수
`cost` CPU 사이클, I/O 접근 빈도 등 리소스 소모에 대한 추정치

카디널리티(Cardinality)

카디널리티`Cardinality`는 컬럼에 포함된 고유 값의 개수를 의미합니다.

현재 예제 테이블의 회원 구독항목을 예로 들면

`payment_status` 컬럼의 값이 `COMPLETED`, `PENDING`라면 카디널리티는 `2` 입니다. `카디널리티 낮음`
`title` 컬럼의 값이 `고유한 1000개의 값`이라면, 카디널리티는 `1000`입니다. `카디널리티 높음`

카디널리티와 인덱스의 관계

`카디널리티가 높은 컬럼` 인덱스를 사용하는 것이 효과적입니다.
`카디널리티가 낮은 컬럼` 인덱스 사용이 비효율적일 수 있습니다.

카디널리티(Cardinality)는 해당 컬럼의 중복도를 나타냅니다.
보통 성별, 학년 등은 카디널리티가 낮다. 라고 말하며,
주민등록번호, 계좌번호 등은 카디널리티가 높다고 말합니다.

사용 방법

SELECT COUNT(DISTINCT 컬럼명) from 테이블명;

카디널리티를 확인하려면 `COUNT(DISTINCT)`를 사용하며, 결과로는 해당 컬럼의 고유 값 개수를 반환합니다.

SELECT
    COUNT(DISTINCT payment_status),
    COUNT(DISTINCT title)
from user_subscriptions;

원하는 테이블에서 컬럼의 카디널리티를 확인할 수 있습니다.

SHOW INDEX FROM 테이블명;

테이블이 가진 인덱스의 카디널리티를 확인 하려면 해당 명령어를 사용할 수 있습니다.

SHOW INDEX FROM subscriptions;

트러블 슈팅 접근

-- 사용자가 가장 많이 결제한 구독항목 조회
EXPLAIN ANALYZE
SELECT u.nickname, s.title, MAX(s.payment_amount) AS max_payment
FROM subscriptions s
JOIN users u ON s.user_id = u.id
GROUP BY s.user_id, s.title
ORDER BY max_payment DESC
LIMIT 1;

1. 쿼리 실행 시간 체크

비교하려는 대상의 실행 `[A]time(...끝시간) - [B]time(...끝시간)`로 `A쿼리`의 실행시간을 알 수 있습니다.
rows를 통해 예상되는 처리 행 수도 확인할 수 있습니다. (행 마다 개별적)

JOIN users u ON s.user_id = u.id
GROUP BY s.user_id, s.title
ORDER BY max_payment DESC

EXPLAIN ANALYZE를 확인해 보았을 때 `E`, `D`부분에서 실행 시간이 지연되는 것으로 보아 쿼리의 해당 부분이 슬로우쿼리의 원인으로 추측해 볼 수 있습니다.

2. 카디널리티 확인

SELECT COUNT(DISTINCT user_id),
       COUNT(DISTINCT title),
       COUNT(DISTINCT payment_amount)
FROM subscriptions;

카디널리티 확인 결과 높은 것을 확인할 수 있습니다.

3. 복합 인덱스키 추가

CREATE INDEX idx_user_title_payment_amount ON subscriptions(user_id, title, payment_amount);

문제되는 부분에 대해 복합 인덱스키를 추가하였습니다.

복합 인덱스키 추가 시 쿼리의 진행 흐름 순서와 동일한 순서로 추가해야 원활한 역할수행이 가능해집니다.

단일 인덱스키가 아닌 복합 인덱스키를 추가한 이유는

단일 인덱스는 각 컬럼에 대해 독립적으로 인덱스를 만들고, 쿼리가 실행될 때 각각의 인덱스를 별도로 사용합니다
복합 인덱스는 여러 컬럼을 하나의 인덱스로 묶습니다. 즉, 복합 인덱스를 사용하면 하나의 인덱스로 모든 조건을 처리하여 성능상 유리할 수 있기 때문입니다.

쉽게 말해, `단일 인덱스`는 각 컬럼마다 `각각 다른 책`을 만들어 놓은 것이고, `복합 인덱스`는 여러 컬럼을 `하나의 책에 모두` 담아 놓은 것과 같습니다.

복합 인덱스를 사용하면 `원하는 정보를 하나의 책에서 바로` 찾아볼 수 있어 더욱 빠르고 효율적임을 의미합니다.

4. 결과

복합 인덱스를 설정하여 해당 쿼리의 수행 시간이 `6.5초 → 2.2초`로 개선되었음을 확인할 수 있습니다.

마무리

MySQL 쿼리 성능을 최적화하기 위해서는 `EXPLAIN`과 `EXPLAIN ANALYZE`를 활용하여 쿼리 실행 계획과 실제 실행 결과를 자세히 분석하는 것이 중요합니다. 이를 통해 슬로우쿼리를 찾아내고, 효율적인 인덱스 추가나 쿼리 구조 개선을 통해 문제를 해결할 수 있습니다.

또한, 카디널리티`Cardinality` 개념을 활용해 인덱스가 효과적으로 작동할 수 있는 컬럼을 파악하고, 낮은 카디널리티를 가진 컬럼은 불필요한 인덱스를 줄이는 등 최적화 전략을 세우는 것도 필요합니다.

데이터가 변함에 따라 실행 계획 역시 변할 수 있으므로, 주기적으로 쿼리 성능을 점검하고 옵티마이저 통계 정보를 갱신하는 것이 장기적으로 데이터베이스 성능 유지에 도움을 줍니다. 데이터베이스 최적화는 한 번의 작업으로 끝나지 않으며, 꾸준한 점검과 개선으로, 안정적이고 빠른 서비스를 제공할 수 있습니다.

[MySQL] Explain, 카디널리티 개념과 쿼리 성능 개선하기 : 인덱스는 반드시 성능을 보장하지 않는다.

예제 테이블

EXPLAIN

사용 방법

EXPLAIN ANALYZE

사용방법

카디널리티(Cardinality)

카디널리티와 인덱스의 관계

사용 방법

트러블 슈팅 접근

1. 쿼리 실행 시간 체크

2. 카디널리티 확인

3. 복합 인덱스키 추가

4. 결과

마무리

댓글

이 글 공유하기

티스토리툴바

예제 테이블

EXPLAIN

사용 방법

EXPLAIN ANALYZE

사용방법

카디널리티(Cardinality)

카디널리티와 인덱스의 관계

사용 방법

트러블 슈팅 접근

1. 쿼리 실행 시간 체크

2. 카디널리티 확인

3. 복합 인덱스키 추가

4. 결과

마무리

댓글

이 글 공유하기

다른 글

티스토리툴바