MySQL 인덱싱 마스터하기: 더 빠른 쿼리 성능을 위한 가이드

MySQL 인덱싱은 데이터베이스가 느릴 때 제가 가장 먼저 확인하는 부분이지만, 동시에 실수하기 쉬운 부분이기도 합니다. 인덱스는 테이블 스캔을 빠른 조회로 바꿔줄 수 있습니다. 하지만 쓰기 작업을 느리게 하고, 메모리를 낭비하며, 쿼리 플래너가 사용하지 않는다면 잘못된 성과 착각을 줄 수도 있습니다.

실용적인 질문은 "이 컬럼에 인덱스를 추가해야 할까?"가 아닙니다. 더 나은 질문은 "어떤 쿼리를 더 저렴하게 만들고 싶은지, 그리고 인덱스가 도움이 되었다는 것을 어떻게 증명할 것인가?"입니다. 이 질문을 염두에 두고 읽어보세요. 좋은 인덱싱은 실제 쿼리에서 시작되며, 중요해 보이는 컬럼 목록에서 시작되지 않습니다.

MySQL 인덱스란 무엇인가?

MySQL 인덱스는 데이터베이스 테이블에서 데이터 검색 속도를 향상시키는 데이터 구조입니다. 책의 색인과 비슷하다고 생각하면 됩니다: 특정 주제를 찾기 위해 책 전체를 읽는 대신, 색인에서 주제를 찾아 정확한 페이지 번호를 알 수 있습니다. 마찬가지로 데이터베이스 인덱스는 MySQL이 전체 테이블을 스캔하지 않고 특정 쿼리 조건과 일치하는 행을 빠르게 찾을 수 있도록 합니다.

테이블을 쿼리할 때 MySQL은 인덱스를 사용하여 모든 행을 검사하는 것보다 훨씬 빠르게 관련 행을 찾을 수 있습니다. 이는 특히 행 수가 많은 테이블이나 필터링(WHERE 절), 테이블 조인(JOIN 절), 정렬(ORDER BY 절)이 포함된 쿼리에 유용합니다.

인덱스 작동 방식

MySQL은 일반 InnoDB 인덱스에 대해 B-트리 인덱스를 주로 사용합니다. B-트리는 키를 정렬된 순서로 유지하므로 동등 조회, 범위 검색, 정렬된 스캔 및 많은 조인에 효과적입니다. 하나 이상의 컬럼에 인덱스를 생성하면 MySQL은 다음과 같은 구조를 만듭니다:

• 리프 노드는 실제 데이터 포인터 또는 클러스터형 인덱스(InnoDB의 기본 키)의 경우 데이터 행 자체를 포함합니다.
• 내부 노드는 트리를 탐색하여 올바른 리프 노드를 찾는 데 도움이 되는 키를 포함합니다.

쿼리가 인덱스의 왼쪽 부분을 사용할 수 있을 때 MySQL은 전체 테이블을 읽는 대신 트리의 좁은 부분으로 이동할 수 있습니다. 이것이 진정한 이점입니다. 인덱스가 모든 쿼리를 빠르게 만드는 것은 아닙니다. 특정 액세스 패턴을 저렴하게 만듭니다.

MySQL 인덱스 유형

MySQL은 각각 고유한 장점과 사용 사례를 가진 다양한 유형의 인덱스를 지원합니다.

1. PRIMARY KEY

• PRIMARY KEY 제약 조건은 컬럼의 각 값이 고유하고 NULL이 아님을 보장합니다. 암시적으로 인덱싱됩니다.
• 테이블은 하나의 PRIMARY KEY만 가질 수 있습니다.
• InnoDB 테이블은 기본 키(클러스터형 인덱스)에 의해 물리적으로 정렬됩니다.

예시:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

2. UNIQUE 인덱스

• UNIQUE 인덱스는 인덱싱된 컬럼의 모든 값이 고유함을 강제합니다. NULL 값을 허용하지만, 여러 NULL이 허용됩니다(컬럼이 PRIMARY KEY 또는 이를 방지하는 다른 UNIQUE 제약 조건의 일부가 아닌 경우).
• 컬럼이 고유해야 하지만 기본 식별자가 아닌 경우 데이터 무결성을 보장하는 데 유용합니다.

예시:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE
);

3. INDEX (또는 KEY)

• 비고유 인덱스라고도 하는 표준 인덱스입니다.
• 데이터 검색 속도를 높이는 데 사용됩니다. 고유성을 강제하지 않습니다.

예시:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    INDEX idx_customer_id (customer_id)
);

4. FULLTEXT 인덱스

• CHAR, VARCHAR 및 TEXT 컬럼의 전체 텍스트 검색에 사용됩니다.
• 대용량 텍스트 필드 내에서 키워드 검색을 허용합니다.
• 최신 MySQL 버전의 InnoDB에서 지원됩니다. 이전 MySQL 설치에서는 제한 사항이 다를 수 있으므로 설계 전에 정확한 버전을 확인하세요.

예시:

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT (title, body)
);

5. SPATIAL 인덱스

• 점, 선, 다각형과 같은 공간 데이터 유형을 인덱싱하는 데 사용됩니다.
• 동작 및 요구 사항은 MySQL 버전 및 스토리지 엔진에 따라 다릅니다. 실행하려는 정확한 공간 쿼리를 테스트하세요. 공간 인덱스가 모든 GIS 스타일 조건에 도움이 될 것이라고 가정하지 마십시오.

6. HASH 인덱스 (제한적 사용)

• MySQL의 MEMORY 스토리지 엔진은 HASH 인덱스를 지원합니다. 동등 조회용으로 설계되었으며 범위 스캔이나 정렬에는 사용되지 않습니다.
• 대부분의 일반적인 시나리오에 적합한 범용 인덱스 유형이 아닙니다.

인덱스 생성 및 관리

인덱스 생성 방법

테이블 생성 시 또는 기존 테이블을 변경하여 인덱스를 생성할 수 있습니다.

1. 테이블 생성 시:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    INDEX idx_department (department_id),
    INDEX idx_hire_date (hire_date)
);

2. 기존 테이블 변경:

-- 단일 컬럼 인덱스 추가
ALTER TABLE customers
ADD INDEX idx_email (email);

-- 고유 인덱스 추가
ALTER TABLE users
ADD UNIQUE INDEX uidx_username (username);

-- 다중 컬럼(복합) 인덱스 추가
ALTER TABLE orders
ADD INDEX idx_customer_date (customer_id, order_date);

인덱스 삭제 방법

인덱스가 더 이상 필요하지 않거나 성능에 부정적인 영향을 미치는 경우(예: 쓰기 작업 중) 삭제할 수 있습니다.

-- 표준 인덱스 삭제
ALTER TABLE customers
DROP INDEX idx_email;

-- 고유 인덱스 삭제
ALTER TABLE users
DROP INDEX uidx_username;

다중 컬럼(복합) 인덱스

복합 인덱스는 두 개 이상의 컬럼에 생성됩니다. 복합 인덱스에서 컬럼의 순서는 매우 중요합니다.

• (col1, col2)에 대한 복합 인덱스는 col1만 필터링하거나 col1과 col2를 모두 필터링하는 쿼리에 사용될 수 있습니다.
• 일반적으로 col2만 필터링하는 쿼리에는 사용되지 않습니다.

예시:

(customer_id, order_date)에 대한 인덱스를 고려해보세요. 이 인덱스는 다음과 같은 쿼리에 가장 효과적입니다:

SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-10-27';
SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date;

SELECT * FROM orders WHERE order_date = '2023-10-27';에는 그다지 유용하지 않을 수 있습니다.

MySQL 인덱싱 모범 사례

1. WHERE, JOIN 및 ORDER BY 절에 사용되는 컬럼 인덱싱

인덱스가 상당한 성능 이점을 제공하는 가장 일반적인 위치입니다.

• WHERE 절: 필터 조건이 주요 사용 사례입니다.
• JOIN 조건: JOIN 문의 ON 절에 사용되는 컬럼을 인덱싱하면 테이블 조인 속도가 크게 향상됩니다.
• ORDER BY 및 GROUP BY 절: 인덱스는 MySQL이 정렬 작업을 피하도록 도울 수 있습니다.

2. 복합 인덱스 현명하게 사용

• 순서가 중요합니다: 쿼리 모양과 일치하는 컬럼을 먼저 배치하세요. 동등 필터는 일반적으로 범위 필터보다 먼저 옵니다. 정렬에 사용되는 컬럼은 필터링 컬럼 이후에 도움이 될 수 있습니다.
• 실제 쿼리에서 필터링하지 않는다면 가장 선택적인 컬럼을 맹목적으로 먼저 배치하지 마세요. (status, created_at) 인덱스는 status의 카디널리티가 낮더라도 액세스 패턴과 일치하기 때문에 WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50에 탁월할 수 있습니다.

3. 인덱스 전후에 EXPLAIN 사용

인덱스를 희망으로 판단하지 마세요. EXPLAIN을 실행하고, 스테이징 또는 유지 관리가 안전한 환경에서 인덱스를 추가한 후 다시 EXPLAIN을 실행하세요.

EXPLAIN
SELECT order_id, total, created_at
FROM orders
WHERE customer_id = 123
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 20;

type, key, rows 및 Extra를 확인하세요. key가 NULL이면 MySQL이 인덱스를 선택하지 않은 것입니다. rows가 여전히 테이블 크기에 가깝다면 인덱스가 이 쿼리에 충분히 선택적이지 않을 수 있습니다. Extra에 Using filesort가 표시되면 자동으로 나쁜 것은 아니지만, MySQL이 선택한 인덱스에서 요청된 순서로 행을 반환할 수 없음을 알려줍니다.

MySQL 8.0.18 이상에서는 EXPLAIN ANALYZE가 더 유용할 수 있습니다. 쿼리를 실행하고 실제 타이밍과 행 수를 보고하기 때문입니다:

EXPLAIN ANALYZE
SELECT order_id, total
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

프로덕션 시스템에서는 문을 실행하므로 주의해서 사용하세요.

4. 테이블이 아닌 워크플로우를 중심으로 인덱스 구축

최근 실패한 결제를 나열하는 관리자 화면을 상상해보세요:

SELECT id, customer_id, failure_code, created_at
FROM payments
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 100;

status만 인덱싱하면 MySQL이 많은 실패한 행을 정렬해야 할 수 있습니다. (status, created_at) 인덱스는 일반적으로 더 나은 선택입니다. MySQL이 실패한 행을 찾고 시간 순서대로 읽을 수 있기 때문입니다. 쿼리가 인덱스의 컬럼만 반환하는 경우 커버링 인덱스를 고려할 수 있습니다:

CREATE INDEX idx_payments_status_created_cover
ON payments (status, created_at, id, customer_id, failure_code);

빠를 수 있지만 무료는 아닙니다. 인덱스가 더 넓어지고, 더 많은 스토리지를 차지하며, 모든 쓰기 작업에 더 많은 비용이 듭니다. 저는 추가 유지 관리 비용을 정당화할 만큼 자주 실행되는 핫 쿼리에만 커버링 인덱스를 사용합니다.

5. 올바르게 보이지만 아무것도 하지 않는 인덱스 주의

일반적인 함정은 다음과 같습니다:

• 함수가 인덱스 값을 숨깁니다: WHERE DATE(created_at) = '2025-01-01'.
• 선행 와일드카드가 일반 B-트리 사용을 방해합니다: WHERE email LIKE '%@example.com'.
• 유형 불일치로 인해 변환이 강제됩니다: 정수 컬럼을 따옴표로 묶은 문자열과 비교하는 것은 여전히 작동할 수 있지만 실제 스키마에서 계획을 혼동할 수 있습니다.
• 인덱스가 쿼리에 대해 잘못된 컬럼으로 시작합니다: (created_at, customer_id)는 (customer_id, created_at)과 같지 않습니다.

가능하면 조건을 다시 작성하세요:

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

이 형식을 사용하면 MySQL이 created_at에 대한 범위 스캔을 사용할 수 있습니다.

6. 중복 및 사용되지 않는 인덱스 신중하게 제거

과도한 인덱싱은 조용한 성능 문제입니다. 각 추가 보조 인덱스는 INSERT, UPDATE 및 DELETE 중에 유지 관리되어야 합니다. 쓰기 작업이 많은 테이블에서 사용되지 않는 인덱스 5개는 하나의 느린 SELECT보다 더 중요할 수 있습니다.

MySQL 5.7 및 8.0에서 sys 스키마는 후보를 찾는 데 도움이 될 수 있습니다:

SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema = 'app';

해당 출력을 명령이 아닌 단서로 취급하세요. 서버가 최근에 다시 시작되었거나, 월간 보고서가 아직 실행되지 않았거나, 스테이징 트래픽이 프로덕션과 일치하지 않기 때문에 인덱스가 사용되지 않는 것처럼 보일 수 있습니다. 인덱스를 삭제하기 전에 배포 기록, 예약된 작업 및 외래 키 요구 사항을 확인하세요.

7. 대규모 테이블에 안전하게 인덱스 추가

작은 테이블에서는 ALTER TABLE ... ADD INDEX가 일반적으로 문제가 없습니다. 대규모 프로덕션 테이블에서는 실제 작업이 될 수 있습니다. MySQL 버전, 스토리지 엔진, 테이블 정의 및 정확한 DDL에 따라 인덱스 추가는 온라인 DDL을 사용하거나 메타데이터 잠금, 임시 공간, 리두 생성 및 복제 지연을 통해 압력을 생성할 수 있습니다.

대규모 인덱스를 추가하기 전에 다음을 확인하세요:

• 테이블 및 기존 인덱스의 크기.
• 복제본이 따라잡을 수 있는지 여부.
• MySQL 버전이 예상하는 온라인 알고리즘을 지원하는지 여부.
• 긴 트랜잭션이 DDL을 차단하는 경우 애플리케이션이 메타데이터 잠금을 견딜 수 있는지 여부.

민감한 시스템의 경우 pt-online-schema-change 또는 gh-ost와 같은 마이그레이션 도구를 사용하거나 트래픽이 적은 시간에 DDL을 예약하세요.

실용적인 인덱스 검토 루틴

느린 MySQL 쿼리를 검토할 때 다음 순서를 사용합니다:

1. 실제 바인드 값이 포함된 정확한 SQL을 캡처합니다.
2. EXPLAIN을 실행하고, 안전한 경우 EXPLAIN ANALYZE를 실행합니다.
3. 기존 인덱스가 WHERE, JOIN 및 ORDER BY 패턴과 일치하는지 확인합니다.
4. 스테이징에 가장 작은 유용한 복합 인덱스를 추가합니다.
5. 검사된 행 수, 쿼리 시간 및 쓰기 영향을 비교합니다.
6. 신중하게 롤아웃하고 느린 쿼리 로그 및 복제 지연을 모니터링합니다.

이 루틴은 인덱싱을 정직하게 유지합니다. 인덱스를 수집하려는 것이 아닙니다. 애플리케이션이 실제로 실행하는 쿼리에 대해 MySQL이 수행해야 하는 작업량을 줄이려는 것입니다.