SQL 성능 개선을 위해 가장 많이 활용되는 방법이 인덱스 활용이다. 하지만 단순히 인덱스만 적용한다고 해서 무조건 해결되는 게 아니다. 인덱스를 적절하게 활용해야만 DB 성능이 개선된다.
인덱스란?
개념
추가적인 쓰기 작업과 저장 공간을 활용하여 DB 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조
데이터를 빨리 찾기 위해 특정 칼럼을 기준으로 미리 ‘정렬’해둔 표
인덱스를 활용하면 UPDATE/DELETE 성능도 함께 향상됨. 해당 연산 수행을 위해서는 조회가 먼저 필요하기 때문
예시
users 테이블에 1만 개의 row가 있을 때, 23살인 사용자를 조회한다고 해보자. 인덱스가 없다면 만 개의 데이터를 전부 찾아봐야 할 것이다. 이를 Full Scan이라고 한다. 그런데 만약 사전에 나이를 기준으로 데이터를 정렬해둔다면 어떨까? (1)이미 정렬되어 있기 때문에 23살 데이터의 위치를 찾기도 빠르고, (2)24살 이후의 데이터는 볼 필요가 없으므로 모든 데이터를 스캔할 필요가 없다.
인덱스 관리
인덱스는 항상 최신의 정렬된 상태를 유지해야 한다. 이에 따라 INSERT/UPDATE/DELETE 연산 수행 시 다음과 같은 연산이 추가적으로 진행되어 오버헤드가 발생할 수 있다.
INSERT: 새로운 데이터에 대한 인덱스 추가
DELETE: 삭제하는 데이터의 인덱스를 ‘사용하지 않음’ 처리
UPDATE: 기존 인덱스를 ‘사용하지 않음’ 처리 후, 갱신된 데이터에 대한 인덱스를 추가
장단점
장점: 조회 성능 향상
단점: 쓰기 작업(데이터 삽입, 수정, 삭제) 성능 저하
⇒ 느려지는 이유? 인덱스를 생성한다는 것은 결국 인덱스용 테이블을 추가적으로 생성한다는 뜻이고, 인덱스 관리를 위해서는 DB의 약 10%에 해당하는 저장 공간이 필요하다고 한다. 쓰기 작업 시 원본 테이블 뿐 아니라 인덱스 테이블에도 그 값을 반영해야 한다. 즉 두 개 이상의 테이블에서 처리가 필요하기 때문에 더 느려질 수밖에 없다. 따라서 무분별한 인덱스 사용은 지양해야 한다.
인덱스 적용 케이스
INSERT, UPDATE, DELETE가 자주 발생하지 않는 칼럼
JOIN, WHERE, ORDER BY에 자주 사용되는 칼럼
데이터의 중복도가 낮은 칼럼
인덱스의 종류
기본키(Primary Key)
PK도 인덱스의 일종이기 때문에, 테이블의 데이터가 이 PK를 기준으로 정렬되어 보관됨
인덱스란 데이터를 빨리 찾기 위해 특정 칼럼을 기준으로 미리 ‘정렬’해둔 표라고 했는데, 그 칼럼이 PK일 경우에는 클러스터링 인덱스라고 부름
클러스터링 인덱스란 “원본 데이터” 자체가 정렬되는 인덱스를 의미함
클러스터링 인덱스에는 PK밖에 없기 때문에, PK = 클러스터링 인덱스 라고 봐도 무방
유니크(UNIQUE) 조건
MySQL은 UNIQUE 제약 조건 설정 시 자동으로 인덱스가 생성됨
그 이유는 MySQL에서 유니크 조건을 구성하고 사용할 때 기본적으로 인덱스의 원리를 활용하기 때문
즉, 유니크 옵션을 사용한다 = 인덱스를 사용한다 = 조회 성능이 향상된다
유니크 특징으로 인해 생성되는 인덱스를 “고유 인덱스(Unique Index)”라고 부름
멀티 칼럼 인덱스(Multiple Column Index)
2개 이상의 칼럼을 묶어서 하나의 인덱스로 설정하는 것
즉 2개 이상의 칼럼에 대해서 미리 정렬해둔 표를 생성하는 것
여러 칼럼을 인덱스로 생성할 때에는 칼럼의 순서도 중요함. 그게 곧 정렬의 우선순위이기 때문
(부서, 나이)로 인덱스를 생성했다면, 부서를 기준으로 먼저 정렬되고 동일 부서 내 데이터들 사이에서 나이를 기준으로 다시 정렬됨
커버링 인덱스(Covering Index)
SQL문을 실행시킬 때 필요한 모든 컬럼을 갖고 있는 인덱스
SELECT id, created_at FROM users는 created_at 칼럼이 인덱스에 없기 때문에 결국 원본 테이블에 접근해야 하지만, SELECT id, name FROM users는 인덱스에 사용자가 요청하는 모든 데이터가 담겨있기 때문에 원본 테이블에 접근할 필요가 없다.
인덱스 사용 시 주의점
적절한 활용 필요
앞서 말했듯, 인덱스를 많이 정의한다고 무조건 좋은 것은 아니다. 향상되는 것은 조회 시의 성능일 뿐, 그 외의 쓰기 작업에서는 데이터의 수가 많아질수록 부하가 심해지므로, 인덱스 선언은 꼭 필요한 경우에만 사용해야 한다.
멀티 칼럼 인덱스 구성 순서
처음에 배치된 칼럼만 멀티 칼럼 인덱스에서 일반 인덱스처럼 활용 가능하다.
칼럼 순서는 소분류 > 중분류 > 대분류 순으로 구성하는 것이 좋다.
정의한 칼럼의 순서에 따라서 성능 차이가 난다. 만약 (부서, 이름) 순으로 인덱스를 정의했다면, ‘인사팀’에 해당하는 데이터를 찾은 후 ‘홍길동’을 찾을 것이다. 하지만 부서는 데이터 중복도가 높기 때문에 비효율적이며, (이름, 부서)순으로 정의하는 것이 더 높은 성능을 보일 수 있다. 즉 데이터 중복도가 낮은(≒ 카디널리티가 높은) 컬럼이 앞쪽으로 오는 게 좋은 경우가 많다. (항상 그런 것은 아니다)
이전까지의 포스팅은 모두 핵심만 정리하고자 간단한 형식으로 작성했지만, 여기서부터는 부가 설명이 필요할 것 같다. 우선 다양한 쿼리문을 테스트해보기 위해 로컬 MySQL 서버에 다음과 같이 테이블과 데이터를 생성해주었다. 귀찮아서 온라인 쿼리 사이트들도 사용해보려 했는데, 출력 결과 양식이 제대로 나오지 않아서(analyze 결과가..) 결국 MySQL Workbench를 사용했다.
스키마 구성은 제품 정보를 담는 product, 사용자 정보를 담는 customer, 그리고 주문 기록을 담는 orders 테이블로 간단히 구성했다.
-- product 테이블 생성
CREATE TABLE product (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_nm VARCHAR(100),
description VARCHAR(255)
);
INSERT INTO product (product_nm, description) VALUES
('Entity Framework Extensions', 'Use <a href="https://entityframework-extensions.net/" target="_blank">Entity Framework Extensions</a> to extend your DbContext with high-performance bulk operations.'),
('Dapper Plus', 'Use <a href="https://dapper-plus.net/" target="_blank">Dapper Plus</a> to extend your IDbConnection with high-performance bulk operations.'),
('C# Eval Expression', 'Use <a href="https://eval-expression.net/" target="_blank">C# Eval Expression</a> to compile and execute C# code at runtime.');
-- customer 테이블 생성
CREATE TABLE customer (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
customer_nm VARCHAR(10)
);
INSERT INTO customer (customer_nm) VALUES
('kate'),
('gia');
-- orders 테이블 생성
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
product_id INT,
order_cnt INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (product_id) REFERENCES product(product_id)
);
INSERT INTO orders (customer_id, product_id, order_cnt, created_at) VALUES
(1, 1, 2, '2025-01-01 09:00:00'),
(1, 2, 4, '2025-01-15 11:30:00'),
(2, 3, 1, '2025-02-01 15:45:00'),
(2, 2, 5, '2025-02-01 17:30:00'),
(2, 2, 5, '2025-02-11 14:00:00');
Workbench Visual Explain
workbench에서 쿼리를 실행하고 Result Grid 대신 Execution Plain 을 선택하면 아래와 같이 그림으로 실행 계획을 보여준다. 가장 직관적으로 확인할 수 있는 방법이다.
MySQL EXPLAIN 출력 형식
MySQL의 실행 계획을 보기 위해서는 쿼리문 앞에 EXPLAIN | DESCRIBE | DESC 명령어를 붙이면 된다. 3가지 모두 동일한 결과 출력한다. 추가로, MySQL 8부터 출력 형식을 단순 Table, JSON, Tree 형태로 지정 가능하다. json 형식은 기존의 표 형식보다 다양한 정보를 제공한다.
아래는 단순 출력 포맷을 보여주기 위한 예시로, 자세한 내용은 아직 몰라도 된다. 그리고, 정확히 무슨 기준인지는 모르겠지만 쿼리와 스키마가 모두 동일함에도 불구하고 explain/analyze 결과가 종종 달라지는 것 같다.. 그렇기 때문에 포스팅의 내용은 참고만 해주길! 이 부분에 대해서도 나중에 다룰 수 있다면 좋겠다.
-- 일반 테이블 형태
EXPLAIN
SELECT * FROM customer;
-- JSON 형태
EXPLAIN FORMAT = JSON
SELECT * FROM customer;
-- TREE 형태
EXPLAIN FORMAT = TREE
SELECT c.customer_nm, p.product_nm
FROM orders o, customer c, product p
WHERE o.customer_id = c.customer_id
AND o.product_id = p.product_id;
위에서 본 단순 EXPLAIN 결과는 MySQL 서버가 가지고 있는 통계 정보들을 활용한 예측 결과이다. 만약 실제 실행된 소요 시간과 실행 계획 정보를 출력하고 싶다면 ANALYZE 키워드를 사용해야 한다. 이 경우 Tree 형식으로 출력되며, 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행되고, 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행된다.
EXPLAIN ANALYZE
SELECT c.customer_nm, p.product_nm
FROM orders o, customer c, product p
WHERE o.customer_id = c.customer_id
AND o.product_id = p.product_id;
EXPLAIN
필드 설명
이제 EXPLAIN 실행 결과 나오는 속성들에 대해 간단히 알아보겠다. 각 필드별 상세 옵션을 정리하기 보다는 간단한 정의를 알아보고, 실제 쿼리와 대조해보며 결과를 분석하는 방식을 익히는 데 초점을 맞추려고 한다.
사진은 아래 EXPLAIN 명령을 실행 시킨 결과이다. 이 중 rows와 filtered 속성은 MySQL의 통계 정보를 기반으로 한 것이며, 정확하지는 않은 값이다. 쿼리는 일부러 다양한 결과를 포함하고자 만든 것이므로 무의미하며 이해할 필요는 없다. 단순히 로직을 결과와 대조하며 이해하는 데 참고하길 바란다.
EXPLAIN
(
SELECT * -- id = 1(row1~3)
FROM orders o
JOIN customer c ON o.customer_id = c.customer_id -- row2
JOIN product p ON o.product_id = p.product_id -- row3
WHERE c.customer_nm = 'gia' -- row1
UNION ALL
SELECT * -- id = 2(row4~6)
FROM orders o -- row 4
JOIN customer c ON o.customer_id = c.customer_id -- row 5
JOIN product p ON o.product_id = p.product_id -- row 6
WHERE o.order_cnt > (
SELECT COUNT(*) -- id = 3(row 7)
FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-02-01'
)
);
id: select를 기준으로 쿼리가 실행되는 순서를 나타낸다. MySQL에서는 조인을 하나의 단위로 실행하기 때문에 조인만 사용하는 쿼리라면 항상 1 값으로 나올 것이다. 위 예시에서는 union을 사용했고, 서브 쿼리까지 있기 때문에 id가 1~3까지 나온 것을 볼 수 있다.
select_type: select문의 유형을 나타낸다. 서브 쿼리나 union이 없으면 SIMPLE로 출력된다. union이 포함된 SQL 중 첫 번째 select의 타입은 PRIMARY, 아래 select는 UNION이다. SUBQUERY는 말 그대로 서브 쿼리에 해당한다.
table: 사용되는 테이블의 이름이나 alias를 출력한다. 서브쿼리나 임시 테이블을 만들 때에는 <subquery#> 나 <derived#>라고 출력된다. 만약 <derived2> 라면 id=2가 먼저 실행되고 그 결과인 임시 테이블을 참조하는 것이다.
리턴하는 값이 COUNT, 즉 스칼라 값이기 때문이다. MySQL이 이를 상수로 취급하여 단순 테이블 필터링으로 변환된 것이다. (by chatGPT) 이렇게 다양한 상황이 존재할 수 있어서 옵티마이저의 결정을 이해하기가 어려운 것 같다.
partitions: 해당 쿼리가 사용하고 있는 파티션을 나타낸다. 파티션이란 큰 테이블을 여러 개의 작은 논리적 단위로 나눈 것을 뜻한다. 지금은 사용하고 있지 않기 때문에, 파티션을 활용한다면 더 빨리 처리할 수 있다는 정도만 알고 넘어가자.
type: 테이블에서 데이터를 찾는 접근 방식을 나타낸다. 쿼리의 성능에 많은 영향을 미치는 매우 중요한 항목이다. ALL은 풀 스캔, 즉 테이블 전체를 스캔한 것이다. ref와 eq_ref는 비슷하지만, eq_ref는 기본 키나 유니크 키를 사용해서 항상 하나의 행만 반환한 것이고, ref는 일반 인덱스를 사용하기 때문에 여러 개의 행이 반환될 수 있다. 즉, eq_ref가 하나의 행만 찾으면 되기 때문에 더 효율적이다.
customer_id와 product_id는 둘 다 테이블의 기본 키인데 왜 row2는 ref이고 row3은 eq_ref일까? 이는 기준이 되는 table이 다르기 때문이다. row2의 table은 o로, orders 테이블에서의 customer_id(key)는 기본키가 아니기 때문에 일반 인덱스로 취급되어 ref인 것이다. row3의 table은 p이고, product_id(key)가 product 테이블의 키가 맞기 때문에 eq_ref이다. 그래서 key 필드에 PRIMARY라고 표시된 것이기도 하다.
possible_keys: 옵티마이저가 사용할 수 있는 인덱스 목록을 표시한다. 실제 사용된 것이 아니라 사용 가능한 것이다.
key: 위 목록 중에서 실제로 사용한 인덱스다. PRIMARY는 해당 테이블의 기본 키가 사용된 것이며, NULL인 경우 인덱스를 사용하지 않은 것이다. 이 경우 key, key_len, ref가 전부 NULL인 것을 확인할 수 있다.
key_len: 사용한 인덱스의 바이트 수이다. 복합 인덱스라면 인덱스를 모두 더한 값이 나온다. 인덱스가 길면 비효율적이기 때문에 참고할 수 있다.
ref: 조인 시 액세스된 테이블 정보가 표시된다. row2는 o 테이블(table)의 customer_id(key)를 c 테이블의 customer_id(ref)와 비교하여 조인했다는 의미이다.
rows: 접근 방식을 통해 접근한 행의 수를 나타낸다.
filtered: 스토리지 엔진에서 가져온 데이터를 MySQL 엔진이 필터링하여 제거된 비율을 나타낸다. row1의 경우, 사용자가 2명밖에 없기 때문에 where 조건으로 50% 필터링되었다는 의미이다. row7도 총 5개의 데이터 중 하나만 해당 범위를 벗어나기 때문에 20%가 필터링되었다.
Extra: 옵티마이저의 동작에 대한 추가 정보를 나타낸다. 여기서는 WHERE 절을 이용해서 데이터를 필터링 했음을 알 수 있다.
ANALYZE
출력 양식 설명
analyze를 사용하면 쿼리의 실행 시간과 각 단계의 실행 계획을 구체적으로 알 수 있다. EXPLAIN이 통계 수치를 기반으로 예상한 값을 알려줬다면, ANALYZE는 실제 데이터를 기반으로 한다. 출력은 트리 구조로 표시되며, 가장 안쪽에 들여쓰기된 작업이 먼저 실행된다. 각 줄은 조인이나 스캔 등의 작업 정보와 실행 시간에 대한 비용 정보로 구성된다.
