Showing Posts From

설계

01 Dec, 2025
- 개발

MySQL 인덱스 설계: 경험치로는 모자란 부분

MySQL 인덱스 설계: 경험치로는 모자란 부분 느려진다는 신호, 그 다음부터가 지옥문 "김개발님, 대시보드 조회가 자꾸 타임아웃이 나네요. 인덱스를 좀 봐주실 수 있을까요?" 팀원 신입이 슬랙에 메시지를 보냈고, 나는 "아 그거요, 금방 봐드리죠"라고 답했다. 자신감 가득한 대답이었다. 그런데 이게 내 인생의 3시간을 빼앗을 줄은 몰랐다. 일반적으로 백엔드 개발자에게 "쿼리가 느리다"는 보고는 고혈압 약을 먹고도 혈압을 올릴 수 있는 마법의 말이다. 왜냐하면 가능한 원인이 너무 많기 때문이다. N+1 쿼리? 인덱스 미스? 테이블 잠금? MySQL 캐시? 네트워크 레이턴시? 아니면 서버 리소스 부족? 경력 7년차라는 타이틀이 나를 자신감 넘치게 만든다. 7년이면 충분하지 않을까? 인덱스 정도야 내가 다 알지. 나는 빠르게 쿼리 플랜을 뽑아내기로 했다. EXPLAIN EXTENDED SELECT * FROM user_logs WHERE user_id = 123 AND created_at BETWEEN '2024-01-01' AND '2024-12-31' AND status IN ('active', 'pending') ORDER BY created_at DESC LIMIT 100;플랜을 봤다. rows: 2,847,392. 풀 테이블 스캔이다. 뭐가 이렇게 많이 나오는 거야? 나는 즉각 인덱스를 설계했다. "네, 여기요. (user_id, created_at, status) 복합 인덱스를 추가하면 될 것 같습니다. 아 그거요, status도 같이 포함해야 필터링이 먹으니까요." 신입이 감탄했다. "오, 역시 시니어는 다르네요!" 그 칭찬이 나를 망쳤다. 나는 인덱스를 추가했고, 자리로 돌아가 커피를 마셨다. 그리고 30분 뒤... "어... 김개발님? 아직 느린데요?"'EXPLAIN' 다시 읽기, 내가 놓친 것들 다시 쿼리 플랜을 봤다. 이번엔 좀 더 찬찬히. 아니, 이전엔 정말 제대로 본 건가? mysql> EXPLAIN EXTENDED SELECT * FROM user_logs WHERE user_id = 123 AND created_at BETWEEN '2024-01-01' AND '2024-12-31' AND status IN ('active', 'pending') ORDER BY created_at DESC LIMIT 100\G결과를 보니:Type: ALL - 풀 테이블 스캔 Key: NULL - 인덱스를 사용하지 않음 Key_len: NULL - 인덱스 길이도 없음 Rows: 2,847,392 - 약 284만 건을 스캔 Using where; Using filesort - 디스크 정렬 사용어? 근데 내가 인덱스 추가했는데? 나는 실시간으로 테이블을 다시 확인했다. SHOW INDEXES FROM user_logs;인덱스가 추가되어 있었다. 그럼 왜 안 쓰는 건데? 그 순간이었다. 뇌가 깨어났다. 아, 쿼리 자체가 이상한데? 다시 원래 쿼리를 봤다. SELECT * FROM user_logs WHERE user_id = 123 AND created_at BETWEEN '2024-01-01' AND '2024-12-31' AND status IN ('active', 'pending') ORDER BY created_at DESC LIMIT 100;뭔가 이상하다. WHERE절에는 user_id가 먼저 오고, 그 다음에 created_at과 status가 있다. 그런데 내가 만든 인덱스는... (user_id, created_at, status)? 아 그거요. 잠깐. 인덱스가 있어도 MySQL 옵티마이저가 판단해서 안 쓸 수도 있다. 특히 카디널리티(Cardinality) 문제가 있으면 말이다. ANALYZE TABLE user_logs; SHOW INDEXES FROM user_logs\G카디널리티를 확인했다. user_id는 5개. status는 3개. 근데 전체 행은 284만 건. 오... 아. 그 순간 깨달음이 왔다. Status 컬럼의 카디널리티가 너무 낮다는 것이다. 'active'와 'pending'만 있으면, 결국 거의 모든 행이 조건을 만족한다는 뜻이다. 그럼 MySQL은 "인덱스를 타서 284만 건을 다 읽은 다음 정렬할까? 아니면 차라리 풀 스캔 한 번 하고 나중에 정렬할까?" 이렇게 판단하고, 풀 스캔이 더 빠르다고 생각한 것이다. 그런데 내가 뭘 했냐고? 인덱스를 추가하고 "야, 이제 빠르겠지?" 했다. 기초를 무시하고 경험만 믿었다. EXPLAIN EXTENDED SELECT * FROM user_logs WHERE user_id = 123 AND created_at BETWEEN '2024-01-01' AND '2024-12-31' ORDER BY created_at DESC LIMIT 100;status 조건을 빼서 실행해봤다. Type: range Key: idx_user_created_status Key_len: 12 Rows: 1,247 Using index condition어? 이제 인덱스를 쓴다? 그렇다. 내가 놓친 문제는 인덱스가 없는 게 아니었다. 쿼리 자체가 문제였고, 옵티마이저의 선택이 합리적이었고, 내가 상황을 제대로 분석하지 않았다는 것이다.쿼리 다시 쓰기, 그리고 배운 것 결국 문제는 쿼리였다. 신입이 준 쿼리를 그대로 실행시키고 있었다. SELECT * FROM user_logs WHERE user_id = 123 AND created_at BETWEEN '2024-01-01' AND '2024-12-31' AND status IN ('active', 'pending') ORDER BY created_at DESC LIMIT 100;나는 신입을 불렀다. "어, 이 쿼리를 왜 이렇게 짰어요? status를 꼭 WHERE절에 포함해야 하나요?" 신입 왈: "어... 기획팀에서 'active나 pending 상태인 로그만 보고 싶다'고 했는데..." "근데 실제로 active나 pending이 아닌 상태의 로그가 많나요?" 신입이 생각해봤다. "아, 거의 없네요. 아마 전체의 98% 정도가 active나 pending일 것 같은데요?" 그 순간이었다. 카디널리티가 높지 않은 컬럼은 WHERE절의 앞에 오면 안 된다. 특히 복합 인덱스에서는 더욱 그렇다. 인덱스 컬럼의 순서가 중요하다는 건 알았지만, 이렇게까지 직접 경험할 줄은... 나는 쿼리를 다시 설계했다. SELECT * FROM user_logs WHERE user_id = 123 AND created_at BETWEEN '2024-01-01' AND '2024-12-31' LIMIT 100;그리고 애플리케이션 단에서 status를 필터링했다. 실행 시간: 150ms → 8ms. 18배 빨라졌다. 인덱스를 하나 추가해서가 아니라, 쿼리를 바꿔서. 그 다음부터 나는 매번 "아 그거요"라고 하기 전에 EXPLAIN을 3번은 본다. 아 그거요. 근데 일단 쿼리 플랜부터 봐야 할 것 같은데요.이렇게 말한다. 조금 더 신중해진 나. 인덱스는 은탄환이 아니다 사실 이 경험이 나한테 준 가장 큰 교훈은 간단하다. 인덱스를 추가하는 것보다, 쿼리를 제대로 짜는 게 100배 중요하다. 7년차니까 알겠지 하면서 기초를 무시했다. 느린 쿼리가 나오면 인덱스를 먼저 생각한다. 하지만:EXPLAIN을 제일 먼저 봐야 한다. Type, Key, Key_len, Rows, Extra 모두. 카디널리티를 확인해야 한다. 특히 복합 인덱스를 만들 때. 인덱스 컬럼의 순서가 결정적이다. Equality, Range, Sort 순서로. LIMIT절이 있으면 MySQL은 풀 스캔도 고려한다. 특히 작은 LIMIT일 때. 데이터 분포를 알아야 한다. 같은 인덱스도 데이터가 어떻게 분포하는지에 따라 쓰일 수도, 안 쓰일 수도 있다.그날 이후로 나는 슬랙 메시지에 "쿼리가 느려요"라는 보고가 오면, 먼저 쿼리를 본다. 그 다음에 EXPLAIN을 본다. 그리고 나서야 인덱스를 생각한다. 신입이 또 다른 느린 쿼리 건을 줬을 때, 나는 EXPLAIN을 봤다. N+1이었다. 관계 데이터베이스인데 Loop Join으로 100만 번의 쿼리를 날리고 있었다. 인덱스와는 아무 상관이 없었다. "아 그거요. LEFT JOIN으로 한 번에 가져가시면 될 것 같습니다." 이번엔 자신감이 없었다. EXPLAIN을 5번 봤고, 쿼리를 다시 썼고, 인덱스 필요성을 판단했다. 그 결과 실행 시간이 47초에서 2초로 줄었다. 신입이 감탄했고, 나도 뿌듯했다. 하지만 그 뿌듯함은 다르다. 이번엔 "내가 똑똑해서"가 아니라 "내가 제대로 분석해서"다.당신이 다음부터 할 수 있는 것 이 글을 읽는 개발자에게 당부하고 싶다. 특히 "경력이 좀 쌓인" 개발자에게. EXPLAIN을 무시하지 말자. 쿼리가 느려졌다고 해서 인덱스를 먼저 생각하지 말자. 그리고 자신감으로 기초를 뛰어넘지 말자. 진짜로 해야 할 체크리스트:쿼리를 읽는다. JOIN 수, WHERE 조건, GROUP BY, ORDER BY. 이상한 부분이 있나? EXPLAIN을 본다. Type이 ref가 아닌가? Rows가 너무 많진 않은가? Using filesort가 있진 않은가? 실제 실행 시간을 본다. EXPLAIN은 추정값이다. 100% 정확하지 않다. 쿼리를 수정한다. WHERE절 순서, JOIN 방식, 서브쿼리 활용. 그래도 느리면 인덱스를 생각한다.내가 처음에 인덱스를 추가했을 때, 아무 효과가 없었던 이유는 인덱스가 문제가 아니었기 때문이다. 쿼리 자체가 비효율적이었다. 지금도 팀에서 느린 쿼리가 나오면 내가 본다. 그런데 거의 절반 이상이 인덱스 문제가 아니다. 대부분은:**SELECT *** 하는데 실제로 10개 컬럼만 필요한 경우 Left Join에서 WHERE절에 Right 테이블 조건이 있어서 LEFT가 INNER가 되는 경우 GROUP BY 후 HAVING으로 필터링 하는데, WHERE에서 미리 필터링할 수 있는 경우 Subquery로 일일이 조회하는 경우이런 것들은 인덱스로 안 된다. 쿼리를 다시 써야 한다. 나는 이제 느린 쿼리를 보면 한숨이 아니라 '어, 이게 뭔가 재미있는 문제네?' 이런 생각이 든다. 왜냐하면 대부분 쿼리 최적화로 해결할 수 있기 때문이다. 그리고 그게 훨씬 더 근본적인 솔루션이기 때문이다. 경력이 늘수록, 기초가 더 중요하다는 걸 깨닫는다. 그리고 "아 그거요"라는 대답이 가장 위험한 말이라는 것도.인덱스는 옷깃이 아니라, 기초 설계가 먼저인 법이다.