MySQL 성능 최적화: 핵심 전략과 모범 사례

MySQL 성능 최적화는 체크리스트처럼 접근하기보다 워크로드를 검토하는 방식으로 접근할 때 가장 효과적입니다. 데이터베이스는 애플리케이션이 요청하는 대로 정확히 수행합니다. 때로는 인덱스가 해결책이 되기도 하고, 더 나은 쿼리가 필요할 수도 있습니다. 때로는 더 적은 연결 수, 다른 스키마 선택, 또는 정오에 프라이머리에서 실행되어서는 안 되는 보고서가 문제일 수도 있습니다.

최고의 MySQL 성능 최적화는 먼저 불필요한 작업을 줄이는 것입니다. 하드웨어와 구성도 중요하지만, 이는 깔끔한 워크로드를 지원하기 위한 것이지, 모든 요청마다 데이터베이스의 절반을 읽는 쿼리 하나를 보상하기 위한 것이 아닙니다.

1. 최적의 인덱싱 전략

인덱스는 특히 읽기 중심 워크로드에서 데이터베이스 성능의 기본입니다. 인덱스를 사용하면 MySQL이 전체 테이블을 스캔하지 않고도 행을 빠르게 찾을 수 있어 SELECT 연산, WHERE 절 필터링, ORDER BY 및 GROUP BY 절, JOIN 연산의 속도를 획기적으로 높일 수 있습니다.

인덱스란 무엇이며 왜 중요한가?

인덱스는 데이터베이스 검색 엔진이 데이터 검색 속도를 높이기 위해 사용하는 특수 조회 테이블입니다. 책의 색인과 같다고 생각하면 됩니다. 모든 페이지를 읽어 주제를 찾는 대신 색인에서 주제를 찾아 올바른 페이지 번호로 이동합니다. MySQL에서 인덱스는 일반적으로 B-트리 구조로, 범위 쿼리와 정확한 조회에 효율적입니다.

인덱스는 읽기 속도를 높이지만, 인덱스 자체도 업데이트되어야 하므로 쓰기 연산(INSERT, UPDATE, DELETE)에는 오버헤드가 추가됩니다. 따라서 과도한 인덱싱을 피하기 위해 신중한 고려가 필요합니다.

인덱싱 모범 사례

• WHERE, JOIN, ORDER BY, GROUP BY 절에 사용되는 열 인덱싱: 이러한 열은 인덱싱의 주요 후보입니다. 테이블 간 조인 조건에 사용되는 열은 두 테이블 모두에서 인덱싱되어야 합니다.
• 복합 인덱스 선호: 쿼리가 여러 열을 자주 필터링하거나 정렬하는 경우 복합 인덱스((col1, col2, col3))가 여러 개의 단일 열 인덱스보다 더 효율적일 수 있습니다. 복합 인덱스에서 열의 순서가 중요합니다. 동등 조건은 일반적으로 범위 조건보다 먼저 와야 하며, 인덱스는 선택성에 대한 일반적인 개념보다는 실제 쿼리 형태와 일치해야 합니다.

  -- last_name 및 first_name에 복합 인덱스 생성
  CREATE INDEX idx_last_first_name ON users (last_name, first_name);
  

• 과도한 인덱싱 방지: 너무 많은 인덱스는 쓰기 연산을 느리게 하고 디스크 공간을 과도하게 소비할 수 있습니다. 실제로 이점이 있는 열에만 인덱스를 생성하세요.
• 인덱스 선택성 고려: 인덱스는 MySQL이 검토해야 하는 행 수를 크게 줄일 때 가장 효과적입니다. 카디널리티(고유 값 수)가 높은 열이 인덱싱에 적합한 후보입니다.
• 정기적으로 인덱스 사용 검토: SHOW INDEX FROM table_name;을 사용하여 정의와 카디널리티 추정치를 검사하고, 사용 가능한 경우 sys.schema_unused_indexes를 확인하세요. 사용되지 않는 인덱스 보고서를 증거가 아닌 후보로 취급하십시오. 서버가 월별 작업이나 드문 관리 워크플로를 아직 관찰하지 못했을 수 있습니다.

2. 쿼리 최적화 마스터하기

완벽한 인덱싱이 있더라도 잘못 작성된 쿼리는 성능을 저하시킬 수 있습니다. 쿼리 최적화는 인덱스를 효과적으로 활용하고 리소스 소비를 최소화하는 효율적인 SQL을 작성하는 것입니다.

EXPLAIN 문: 최고의 도우미

EXPLAIN 문은 MySQL이 쿼리를 실행하는 방법을 이해하는 데 매우 중요합니다. 실행 계획, 사용된 인덱스, 테이블 조인 방식, 잠재적인 성능 병목 현상을 보여줍니다.

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

주요 EXPLAIN 출력 해석:

• type: 테이블이 조인되는 방식을 나타냅니다. const, eq_ref, ref, range를 목표로 하세요. 가능하면 ALL(전체 테이블 스캔)을 피하세요.
• rows: MySQL이 검사해야 하는 행 수의 추정치입니다. 낮을수록 좋습니다.
• key: MySQL이 실제로 사용한 인덱스입니다.
• Extra: 중요한 세부 정보를 제공합니다:
  • Using filesort: MySQL이 데이터를 정렬하기 위해 추가 패스를 수행해야 합니다(느릴 수 있음).
  • Using temporary: MySQL이 쿼리를 처리하기 위해 임시 테이블을 생성해야 합니다(느릴 수 있음).
  • Using index: '커버링 인덱스'가 사용되었습니다. 즉, 쿼리에 필요한 모든 데이터가 인덱스에서 직접 발견되어 데이터 행으로 이동할 필요가 없습니다. 매우 효율적입니다.

효율적인 WHERE 절

• 페이지네이션에 LIMIT 사용: 결과의 하위 집합을 가져올 때, 특히 페이지네이션의 경우 항상 LIMIT 절을 지정하세요.
• LIKE에서 선행 와일드카드 피하기: LIKE '%keyword'는 열에 대한 인덱스 사용을 방지하여 전체 테이블 스캔을 강제합니다. LIKE 'keyword%'를 선호하세요.
• WHERE 절에서 인덱스 열에 함수 사용 금지: WHERE YEAR(order_date) = 2023은 order_date에 대한 인덱스 사용을 방지합니다. 대신 WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'을 사용하세요.
• 명확한 범위 조건 사용: WHERE id >= 10 AND id <= 20과 WHERE id BETWEEN 10 AND 20은 포함 범위에서 동일합니다. 날짜 및 타임스탬프의 경우 반개방 범위가 더 안전한 경우가 많습니다:

  WHERE created_at >= '2025-01-01'
    AND created_at <  '2025-02-01'
  

JOIN 최적화

• 인덱스 열 조인: JOIN 조건에 사용되는 열이 두 테이블 모두에서 인덱싱되었는지 확인하세요.
• 적절한 JOIN 유형 선택: INNER JOIN, LEFT JOIN, RIGHT JOIN을 이해하고 요구 사항에 정확히 일치하는 것을 사용하세요.
• 옵티마이저가 작동하도록 한 다음 확인: MySQL은 내부 조인을 재정렬할 수 있으므로 SQL 텍스트 순서가 항상 실행 순서는 아닙니다. EXPLAIN을 사용하여 계획을 확인하세요. 잘못된 계획을 측정하고 그 이유를 이해한 경우에만 옵티마이저 힌트를 사용하세요.

일반적인 쿼리 모범 사례

• SELECT * 피하기: 필요한 열을 명시적으로 나열하세요. 이렇게 하면 네트워크 트래픽, 메모리 사용량이 줄어들고 커버링 인덱스를 사용할 수 있습니다.
• 서브쿼리가 항상 나쁘다고 가정하지 마세요: 최신 MySQL은 많은 서브쿼리를 잘 최적화할 수 있습니다. 계획과 타이밍을 확인한 후에만 다시 작성하세요. 성능이 좋고 읽기 쉬운 서브쿼리는 아무도 유지보수하고 싶어하지 않는 교묘한 조인보다 낫습니다.
• 일괄 작업: 여러 행의 INSERT 또는 UPDATE의 경우 각 행에 대해 개별 문을 사용하는 대신 단일 문을 사용하여 여러 값을 삽입/업데이트하세요. 이렇게 하면 트랜잭션 오버헤드가 줄어듭니다.

  -- 일괄 INSERT 예제
  INSERT INTO products (name, price) VALUES
  ('Product A', 10.00),
  ('Product B', 20.00),
  ('Product C', 30.00);
  

3. 성능을 위한 데이터베이스 스키마 설계

잘 설계된 스키마는 고성능 데이터베이스의 기초를 형성합니다. 스키마 설계 중 내린 결정은 쿼리 효율성과 데이터 무결성에 큰 영향을 미칩니다.

• 정규화 vs. 비정규화:
  • 정규화(예: 3NF)는 데이터 중복을 줄이고 데이터 무결성을 향상시키며, 일반적으로 더 많은 JOIN으로 이어집니다.
  • 비정규화는 JOIN을 줄이고 특정 읽기 쿼리의 속도를 높이기 위해 통제된 중복을 도입하지만, 데이터 일관성을 복잡하게 만들 수 있습니다. 일반적으로 보고 또는 특정 높은 읽기 시나리오를 위해 약간 비정규화된 균형 잡힌 접근 방식이 일반적입니다.
• 적절한 데이터 유형: 필요한 정보를 저장할 수 있는 가장 작은 데이터 유형을 선택하세요. 더 작은 범위로 충분할 때 BIGINT 대신 INT를 사용하거나, 더 짧은 문자열에 TEXT 대신 VARCHAR(255)를 사용하면 공간을 절약하고 성능을 향상시킬 수 있습니다.
  • CHAR는 고정 길이, VARCHAR는 가변 길이입니다. 고정 길이 데이터(예: 항상 같은 길이의 UUID)에는 CHAR를, 가변 길이 데이터에는 VARCHAR를 사용하세요.
• 항상 기본 키 사용: 모든 InnoDB 테이블에는 기본 키가 있어야 합니다. 자동 증가 정수는 많은 OLTP 시스템에서 간단하고 효율적이지만, 이것이 유일한 유효한 선택은 아닙니다. 보조 인덱스를 합리적으로 작게 유지하고 계획하지 않는 한 임의 쓰기 패턴을 피하는 안정적인 키를 선택하세요.
• 외래 키 인덱싱: 외래 키 관계에 관련된 열이 인덱싱되었는지 확인하세요. 이렇게 하면 JOIN 및 계단식 연산 속도가 빨라집니다.

4. 서버 구성 튜닝 (my.cnf/my.ini)

MySQL의 동작은 구성 파일(Linux에서는 my.cnf, Windows에서는 my.ini)에 의해 크게 영향을 받습니다. 이러한 설정을 하드웨어 및 워크로드에 맞게 최적화하는 것이 중요합니다.

중요한 InnoDB 설정

InnoDB 스토리지 엔진을 사용하는 대부분의 최신 MySQL 배포판에서 다음 설정이 가장 중요합니다:

• innodb_buffer_pool_size: 이것은 종종 가장 중요한 설정입니다. InnoDB가 테이블 데이터와 인덱스를 캐시하는 메모리 영역입니다. 전용 데이터베이스 서버의 일반적인 시작점은 RAM의 50-75%이며, 측정 후 더 높을 수도 있습니다. 운영 체제, 연결 메모리, 백업 및 모니터링 에이전트를 위한 공간을 남겨두세요.

  [mysqld]
  innodb_buffer_pool_size = 8G  # 16GB RAM 서버의 예
  

• innodb_log_file_size: InnoDB 리두 로그의 크기입니다. 로그가 클수록 쓰기 중심 워크로드의 체크포인트 압력을 줄일 수 있지만, 충돌 복구 시간이 늘어날 수 있습니다. 올바른 값은 쓰기 볼륨과 복구 기대치에 따라 달라집니다. 오래된 튜닝 가이드에서 고정된 크기를 복사하지 마세요.
• innodb_flush_log_at_trx_commit: 트랜잭션 내구성과 관련하여 InnoDB가 ACID 준수를 얼마나 엄격하게 따르는지 제어합니다.
  • 1 (기본값): 완전히 ACID를 준수합니다. 각 트랜잭션 커밋 시 로그가 디스크에 플러시됩니다. 가장 안전하지만 가장 느립니다.
  • 0: 로그가 약 1초에 한 번씩 로그 파일에 기록됩니다. 가장 빠르지만, 충돌 시 최대 1초의 트랜잭션이 손실될 수 있습니다.
  • 2: 각 커밋 시 로그가 OS 캐시에 기록되고 약 1초에 한 번씩 디스크에 플러시됩니다. 절충안이지만, OS 충돌 시 트랜잭션이 손실될 수 있습니다.
  • 애플리케이션의 데이터 무결성 요구 사항과 성능 요구 사항에 따라 선택하세요.

기타 중요한 설정

• max_connections: 최대 동시 클라이언트 연결 수입니다. 너무 높게 설정하면 더 많은 RAM을 소비하고, 너무 낮게 설정하면 'Too many connections' 오류가 발생할 수 있습니다. 애플리케이션의 연결 풀링 및 최대 부하에 따라 조정하세요.
• tmp_table_size 및 max_heap_table_size: 인메모리 임시 테이블의 최대 크기를 정의합니다. 임시 테이블이 이 크기를 초과하면 MySQL은 이를 디스크에 기록하여 상당한 속도 저하를 유발합니다. EXPLAIN에서 Using temporary가 자주 표시되는 경우, 특히 대규모 데이터 세트에 대한 GROUP BY 또는 ORDER BY 연산에서 이러한 값을 늘리세요.
• sort_buffer_size: 정렬 연산(ORDER BY, GROUP BY)에 사용되는 버퍼입니다. 쿼리에 대규모 정렬이 자주 포함되고 EXPLAIN에 Using filesort가 나타나는 경우 이 값을 늘리는 것을 고려하세요(연결당).
• join_buffer_size: 인덱스 없이 테이블을 조인할 때 전체 테이블 스캔에 사용됩니다. EXPLAIN에 이것이 표시되면 일반적으로 누락된 인덱스를 나타내지만, 더 큰 버퍼는 인덱스되지 않은 조인에 도움이 될 수 있습니다.
• query_cache_size: MySQL 5.7.20에서 더 이상 사용되지 않으며 MySQL 8.0에서 제거되었습니다. 쿼리 결과를 캐싱하는 것이 매력적으로 보일 수 있지만, 특히 사용량이 많은 서버에서 높은 잠금 경합으로 인해 성능 병목 현상이 되는 경우가 많습니다. 일반적으로 비활성화(query_cache_size = 0)하고 애플리케이션 수준 캐싱 또는 더 빠른 스토리지 엔진에 의존하는 것이 좋습니다.

팁: 구성 변경 후 변경 사항을 적용하려면 MySQL 서버를 다시 시작하세요. 프로덕션에 적용하기 전에 항상 스테이징 환경에서 변경 사항을 테스트하세요.

5. 하드웨어 및 운영 체제 고려 사항

아무리 최적화된 MySQL 인스턴스라도 하드웨어가 부족하거나 운영 체제 설정이 잘못 구성되면 병목 현상이 발생할 수 있습니다.

• RAM: innodb_buffer_pool_size에 중요합니다. 버퍼 풀에 더 많은 RAM을 사용할수록 MySQL이 디스크에 접근해야 하는 빈도가 줄어듭니다.
• CPU: 멀티 코어 CPU는 특히 동시 쿼리 실행 및 복잡한 연산에 유용합니다.
• 디스크 I/O: 이것은 종종 주요 병목 현상입니다. SSD 기반 스토리지는 임의 I/O가 중요하기 때문에 사용량이 많은 프로덕션 MySQL의 일반적인 기준입니다. 자체 관리 서버의 경우 중복성과 쓰기 동작을 신중하게 고려하세요. 클라우드 데이터베이스의 경우 프로비저닝된 IOPS, 버스트 제한, 지연 시간 및 백업 기간에 주의하세요.
• 네트워크 지연 시간: 원격 데이터베이스 액세스의 경우 애플리케이션 서버와 데이터베이스 서버 간의 네트워크 지연 시간을 최소화하세요.
• 운영 체제 튜닝: 데이터베이스 워크로드에 맞게 OS 설정이 최적화되었는지 확인하세요. Linux의 경우 vm.swappiness(불필요한 스와핑 방지), file-max(열린 파일 제한) 및 ulimit 설정 조정을 고려하세요.

6. 사전 예방적 모니터링 및 분석

최적화는 지속적인 프로세스입니다. 지속적인 모니터링은 성능 추세를 식별하고, 병목 현상을 조기에 감지하며, 튜닝 노력의 영향을 검증하는 데 도움이 됩니다.

• 느린 쿼리 로그: 지정된 시간(long_query_time)보다 오래 걸리는 쿼리를 기록하도록 MySQL을 구성하세요. 이것은 문제가 있는 쿼리를 식별하는 기본 도구입니다.

  [mysqld]
  slow_query_log = 1
  slow_query_log_file = /var/log/mysql/mysql-slow.log
  long_query_time = 1
  log_queries_not_using_indexes = 1
  

• 느린 쿼리 로그 분석: pt-query-digest(Percona Toolkit)와 같은 도구는 큰 느린 쿼리 로그를 구문 분석하고 집계된 보고서를 제공하여 가장 빈번하고 느린 쿼리를 강조 표시할 수 있습니다.
• MySQL 상태 변수 (SHOW STATUS): 서버 활동, 메모리 사용량, 연결 등에 대한 실시간 정보를 제공합니다. 실시간으로 문제를 찾는 데 유용합니다.

  SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
  SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
  

  • Innodb_buffer_pool_reads 대 Innodb_buffer_pool_read_requests의 비율이 높으면 버퍼 풀 적중률이 낮다는 것을 의미하며, innodb_buffer_pool_size가 너무 작을 수 있음을 시사합니다.
• 모니터링 도구: Percona Monitoring and Management (PMM), Prometheus with Grafana, 또는 MySQL Enterprise Monitor와 같은 전용 모니터링 솔루션을 활용하세요. 이러한 도구는 포괄적인 메트릭, 대시보드 및 알림을 제공합니다.
• 정기적인 감사: 애플리케이션이 발전함에 따라 데이터베이스 스키마, 쿼리 패턴 및 인덱스 사용량을 정기적으로 검토하여 최적화된 상태를 유지하세요.

실용적인 최적화 워크플로

느린 MySQL 시스템을 인계받았다면, 첫 시간에 열 가지 설정을 변경하고 싶은 충동을 참으세요. 반복 가능한 흐름을 사용하세요.

느린 쿼리 로그와 애플리케이션 추적으로 시작하세요. 최악의 단일 실행 시간뿐만 아니라 총 시간을 기준으로 중요한 쿼리를 찾으세요. 200ms가 걸리고 시간당 50,000번 실행되는 쿼리는 밤에 한 번 20초가 걸리는 보고서보다 더 큰 피해를 줄 수 있습니다.

그런 다음 실제 매개변수 값을 포함하여 정확한 쿼리 형태에 EXPLAIN을 사용하세요:

EXPLAIN
SELECT id, customer_id, total, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

이와 같은 쿼리의 경우 (customer_id, status, created_at) 인덱스가 유용할 수 있습니다. 화면이 일반적으로 모든 고객에 대해 먼저 status로 필터링하는 경우 (status, created_at)이 더 나을 수 있습니다. 올바른 인덱스는 열 이름이 아닌 액세스 패턴에서 비롯됩니다.

쿼리 및 인덱스 검토 후 메모리를 살펴보세요. 활성 데이터 세트가 버퍼 풀보다 훨씬 크면 MySQL이 스토리지에서 더 자주 읽게 됩니다. 버퍼 풀이 이미 크고 서버가 여전히 느린 경우 문제는 테이블 스캔, 낮은 지역성, 임시 테이블 또는 쓰기 압력일 수 있습니다. 더 많은 메모리는 워크로드가 이를 재사용할 수 있을 때만 도움이 됩니다.

다음으로 동시성을 살펴보세요. 데이터베이스는 많은 소규모 쿼리를 처리할 수 있지만, 무제한 병렬 작업을 처리하지는 않습니다. 앱이 너무 많은 연결을 열면 MySQL은 유용한 작업을 완료하는 것보다 세션을 저글링하는 데 더 많은 시간을 소비할 수 있습니다. 적절한 최대값을 가진 연결 풀은 max_connections를 높이는 것보다 성능을 더 향상시키는 경우가 많습니다.

마지막으로 변경 사항을 검증하세요. 좋은 최적화는 어딘가에서 나타나야 합니다: 검사된 행 수 감소, 쿼리 지연 시간 감소, 디스크 읽기 압력 감소, 잠금 대기 시간 단축, 복제 지연 시간 감소, 또는 시간 초과 감소. 메트릭이 움직이지 않으면 변경 사항이 병목 현상을 해결하지 못했거나 측정이 너무 모호했기 때문입니다.

MySQL을 느리게 만드는 일반적인 실수

한 가지 일반적인 실수는 모든 외래 키와 모든 필터 열을 개별적으로 인덱싱한 다음 쓰기가 느린 이유를 궁금해하는 것입니다. 외래 키 열은 종종 인덱싱되어야 하고 필터 열은 종종 인덱스의 이점을 얻지만, 단일 열 인덱스 더미는 잘 설계된 하나의 복합 인덱스를 대체하지 못합니다.

또 다른 실수는 큰 오프셋으로 페이지네이션을 사용하는 것입니다:

SELECT *
FROM events
ORDER BY created_at DESC
LIMIT 50 OFFSET 500000;

MySQL은 여전히 많은 수의 행을 지나쳐야 합니다. 깊은 페이지의 경우 키셋 페이지네이션이 일반적으로 더 좋습니다:

SELECT *
FROM events
WHERE created_at < '2025-05-01 12:00:00'
ORDER BY created_at DESC
LIMIT 50;

긴 트랜잭션은 또 다른 조용한 고통의 원천입니다. 사용자 입력을 기다리거나, 외부 API를 호출하거나, 잠금을 보유한 상태에서 대규모 배치를 처리하는 트랜잭션은 관련 없는 작업을 차단할 수 있습니다. 트랜잭션을 짧게 유지하세요. 데이터베이스 작업을 수행하고, 커밋한 다음, 느린 외부 작업을 수행하세요.

전역 버퍼 변경도 역효과를 낼 수 있습니다. sort_buffer_size 및 join_buffer_size와 같은 설정은 연결당 설정입니다. 하나의 보고서가 느리다고 해서 전역적으로 높이면 여러 세션에서 메모리 사용량이 배가될 수 있습니다. 먼저 쿼리를 수정하세요. 필요한 경우 특수 작업에 세션 수준 변경을 사용하세요.

"좋은" 상태의 모습

건강한 MySQL 환경은 모든 쿼리가 즉시 빠른 환경이 아닙니다. 팀이 비용이 많이 드는 쿼리를 설명하고, 무거운 작업을 예측하며, 사용자가 보고하기 전에 병목 현상을 볼 수 있는 환경입니다. 느린 쿼리 로그가 활성화되어 있습니다. 대시보드는 쿼리 지연 시간, 검사된 행 수, 버퍼 풀 읽기, 잠금 대기, 디스크 지연 시간, 연결 수 및 복제 지연 시간을 보여줍니다. 스키마 변경은 실제 데이터로 테스트됩니다. 인덱스에는 소유자와 이유가 있습니다.

이것은 거대한 튜닝 체크리스트보다 덜 화려하지만, 애플리케이션이 변경됨에 따라 MySQL이 빠르게 유지되는 방법입니다. 워크로드를 측정하고, 불필요한 작업을 줄이고, 한 번에 하나씩 변경하고, 증거를 유지하세요.