MySQL 성능 최적화

MySQL 성능 문제를 볼 때 가장 먼저 해야 할 일은 “느리다”를 더 작은 질문으로 쪼개는 것이다. 어떤 쿼리가 느린지, 얼마나 많은 행을 읽는지, 인덱스를 쓰고 있는지, 디스크 I/O가 병목인지부터 확인해야 한다. 감으로 인덱스를 추가하면 잠깐 빨라질 수는 있지만, 쓰기 성능이나 저장 공간에서 다른 비용을 만들 수 있다.

느린 쿼리는 실행 계획부터 본다

일반적인 순서는 슬로우 쿼리 로그로 문제 쿼리를 찾고, EXPLAIN으로 실행 계획을 확인한 뒤, 필요한 경우 인덱스나 쿼리 구조를 바꾸는 것이다. MySQL 8.0 이상이라면 EXPLAIN ANALYZE로 실제 실행 시간과 예상 행 수의 차이도 볼 수 있다.

EXPLAIN SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

실행 계획에서 우선 볼 것은 type, key, rows, Extra다. type이 ALL이면 Full Table Scan이 발생한다는 뜻이고, key가 NULL이면 사용할 인덱스를 찾지 못했다는 의미다. rows는 MySQL이 예상한 스캔 행 수이고, Extra에는 Using filesort, Using temporary, Using index 같은 추가 정보가 나온다.

type: const > eq_ref > ref > range > index > ALL
key: 실제 사용된 인덱스
rows: 예상 스캔 행 수
Extra: filesort, temporary, covering index 여부

EXPLAIN은 정답을 알려주는 도구라기보다 어디를 의심해야 하는지 알려주는 도구에 가깝다. 예상 행 수와 실제 데이터 분포가 맞지 않으면 통계 정보가 오래되었을 수도 있고, 조건절이 인덱스를 타기 어려운 형태일 수도 있다.

인덱스를 못 타는 쿼리 형태

자주 나오는 사례가 컬럼에 함수를 씌우는 경우다. 아래 쿼리는 created_at에 인덱스가 있어도 DATE(created_at)을 계산해야 하므로 인덱스를 제대로 활용하기 어렵다.

-- Before: Full Table Scan 가능성 높음
SELECT * FROM orders
WHERE DATE(created_at) = '2024-01-15';

조건을 범위 검색으로 바꾸면 인덱스를 사용할 수 있다.

-- After: Index Range Scan 가능
SELECT * FROM orders
WHERE created_at >= '2024-01-15 00:00:00'
  AND created_at < '2024-01-16 00:00:00';

LIKE '%keyword%'도 비슷하다. 앞쪽에 와일드카드가 있으면 B+Tree 인덱스를 타기 어렵기 때문에 데이터가 많아질수록 Full Table Scan 비용이 커진다.

SELECT * FROM articles WHERE content LIKE '%검색어%';

본문 검색이 중요한 기능이라면 FULLTEXT 인덱스나 검색 엔진을 고려해야 한다. MySQL FULLTEXT를 사용할 수도 있지만, 한글 검색은 ngram 파서 같은 추가 설정이 필요하다.

ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);
 
SELECT * FROM articles
WHERE MATCH(content) AGAINST('검색어' IN BOOLEAN MODE);

복합 인덱스는 순서가 중요하다

복합 인덱스에서는 컬럼 순서가 중요하다. 예를 들어 (status, created_at) 인덱스가 있다면 status = 'completed'로 먼저 좁히고, 그 안에서 created_at 범위 검색을 할 수 있다.

SELECT * FROM orders
WHERE status = 'completed'
  AND created_at BETWEEN '2024-01-01' AND '2024-01-31';

반대로 범위 조건이 먼저 등장하면 그 뒤 컬럼은 인덱스를 충분히 활용하지 못할 수 있다.

-- INDEX (a, b, c)
WHERE a = 1 AND b > 10 AND c = 100
-- b가 범위 조건이므로 c는 인덱스 활용이 제한될 수 있음

그래서 복합 인덱스를 설계할 때는 단순히 자주 쓰는 컬럼을 다 넣는 것이 아니라, 등치 조건, 범위 조건, 정렬 조건이 어떤 순서로 등장하는지 봐야 한다.

조인 방식과 데이터 양

조인 성능도 실행 계획에서 자주 문제가 된다. Nested Loop Join은 바깥 테이블의 각 행마다 안쪽 테이블을 찾는 방식이다. 결과 집합이 작고, 안쪽 테이블에 적절한 인덱스가 있으면 효율적이다. OLTP성 조회에서는 자주 맞는 선택이다.

For each row in outer_table:
  For each row in inner_table:
    If join_condition matches:
      Return combined row

Hash Join은 작은 테이블로 해시 테이블을 만든 뒤, 큰 테이블을 스캔하면서 매칭하는 방식이다. MySQL 8.0.18 이후부터 지원되며, 인덱스가 없거나 큰 결과 집합을 다룰 때 유리할 수 있다. 다만 모든 조인에 해시 조인이 좋은 것은 아니고, 데이터 크기와 메모리 사용량을 같이 봐야 한다.

InnoDB와 인덱스 구조

InnoDB는 데이터를 페이지 단위로 읽고 쓴다. 기본 페이지 크기는 16KB이고, 여러 페이지가 모여 Extent를 이룬다. 결국 쿼리 성능은 “얼마나 적은 페이지를 읽고 원하는 데이터를 찾을 수 있는가”와 연결된다.

B+Tree 인덱스는 루트에서 리프 노드까지 내려가며 값을 찾는다. 리프 노드끼리는 연결되어 있어서 범위 검색에 유리하다. Primary Key는 클러스터드 인덱스이기 때문에 리프 노드에 실제 행 데이터가 저장된다. Secondary Index의 리프 노드에는 Primary Key 값이 저장되고, 실제 행을 찾기 위해 다시 Primary Key로 조회한다. 이 과정을 더블 룩업이라고 볼 수 있다.

이 구조 때문에 Primary Key가 너무 크면 Secondary Index도 같이 커질 수 있다. 또 커버링 인덱스를 만들면 Secondary Index만 읽고도 결과를 만들 수 있어 더블 룩업을 피할 수 있다.

I/O 지표도 같이 봐야 한다

쿼리 하나만 봐서는 병목을 놓칠 수 있다. InnoDB Buffer Pool Hit Ratio가 낮으면 디스크에서 데이터를 자주 읽고 있다는 뜻일 수 있다. 슬로우 쿼리 수, QPS, 디스크 읽기 지표도 함께 봐야 한다.

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW STATUS LIKE 'Innodb_data_%';
SHOW STATUS LIKE 'Slow_queries';

MySQL 성능 최적화는 인덱스를 많이 추가하는 작업이 아니다. 느린 쿼리를 찾고, 실행 계획을 보고, 인덱스가 동작하기 좋은 형태로 쿼리를 바꾸고, 실제 I/O가 줄었는지 확인하는 과정에 가깝다. 결국 중요한 것은 “이 쿼리가 몇 건을 읽고, 몇 페이지를 건드리고, 왜 그 방식으로 실행되는가”를 설명할 수 있는지다.