MySQL 인덱싱 마스터하기: 더 빠른 쿼리 성능을 위하여

MySQL 인덱싱은 데이터베이스 성능을 최적화하는 데 매우 중요한 기법입니다. 인덱스를 전략적으로 생성하면 데이터를 검색하는 데 걸리는 시간을 극적으로 줄여 애플리케이션 응답 시간을 단축하고 전반적인 시스템 효율성을 높일 수 있습니다. 이 가이드는 MySQL 인덱싱의 기본 사항을 살펴보고, 다양한 인덱스 유형을 탐색하며, 필수적인 성능 튜닝 기술을 마스터하는 데 도움이 되는 생성 및 분석 모범 사례를 제공합니다.

테이블을 올바르게 인덱싱하는 방법을 이해하는 것은 느린 데이터베이스와 번개처럼 빠른 데이터베이스의 차이를 만들 수 있습니다. 인덱스가 없으면 MySQL은 많은 쿼리에 대해 전체 테이블 스캔을 수행해야 하며, 이는 데이터가 늘어남에 따라 비효율적이 됩니다. 이 글은 인덱싱 기회를 식별하고, 효과적인 인덱스를 생성하며, 그 영향을 검증하는 데 필요한 지식을 갖추도록 돕는 것을 목표로 합니다.

MySQL 인덱스란 무엇인가?

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

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

인덱스는 어떻게 작동하는가

MySQL은 일반적으로 B-트리 데이터 구조를 인덱스로 사용합니다. B-트리는 데이터를 정렬된 상태로 유지하고 효율적인 검색, 삽입 및 삭제를 허용하는 균형 잡힌 트리 구조입니다. 하나 이상의 열에 인덱스를 생성하면 MySQL은 다음과 같이 B-트리 구조를 구축합니다.

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

쿼리가 인덱싱된 열을 사용하면 MySQL은 B-트리를 탐색하여 원하는 행에 대한 포인터를 빠르게 찾습니다. 이 로그 시간 복잡도(O(log n))는 전체 테이블을 선형 스캔(O(n))하는 것보다 훨씬 빠릅니다.

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 Index

• 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 Index

• CHAR, VARCHAR, TEXT 열에 대한 전문 검색에 사용됩니다.
• 대규모 텍스트 필드 내에서 복잡한 키워드 검색을 허용합니다.
• MyISAM 및 InnoDB 스토리지 엔진만 지원합니다.

예시:

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

5. SPATIAL Index

• 공간 데이터 유형(예: 점, 선, 다각형) 인덱싱에 사용됩니다.
• 열이 NOT NULL로 정의되어야 합니다.
• MyISAM 및 InnoDB(특정 데이터 유형 포함)만 지원합니다.

6. HASH Index (제한적 사용)

• MySQL의 MEMORY 스토리지 엔진은 HASH 인덱스를 지원합니다. 매우 빠른 동등 조회(O(1))를 제공하지만 범위 쿼리나 정렬에는 유용하지 않습니다.
• 대부분의 일반적인 시나리오에 대한 범용 인덱스 유형은 아닙니다.

인덱스 생성 및 관리

인덱스 생성 방법

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

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. 복합 인덱스 현명하게 사용하기

• 순서가 중요합니다: 쿼리에서 함께 자주 사용되는 경우 가장 선택성이 높은 열(가장 고유한 값이 많은 열)을 인덱스 정의에서 먼저 배치합니다.
• "