MySQL, 효과적인 Index 설정방법

[1] MySQL이 인덱스를 사용하는 방법

[1.1] 기본 개념

[1.2] 주요 인덱스 종류

• B-Tree 인덱스
• 트리의 모든 리프 노드가 같은 레벨에 있음
• 데이터가 정렬된 상태로 유지
• 각 노드는 여러 키를 가질 수 있음
• 검색: O(log n)
• 삽입/삭제: O(log n)
B-Tree 인덱스와 관련된 설명은 잘 작성된 게시물이 있어, 항목에 추가했습니다.
[자료구조] 그림으로 알아보는 B-Tree
B트리는 이진트리에서 발전되어 모든 리프노드들이 같은 레벨을 가질 수 있도록 자동으로 벨런스를 맞추는 트리입니다.

https://velog.io/@emplam27/%EC%9E%90%EB%A3%8C%EA%B5%AC%EC%A1%B0-%EA%B7%B8%EB%A6%BC%EC%9C%BC%EB%A1%9C-%EC%95%8C%EC%95%84%EB%B3%B4%EB%8A%94-B-Tree

• R-Tree (Spatial) 인덱스
• 공간을 사각형 영역으로 분할
• 각 노드는 MBR(Minimum Bounding Rectangle)을 포함
• 계층적 구조로 공간 데이터 관리

• Hash 인덱스
• 해시 함수를 통한 직접 주소 계산
• O(1) 시간 복잡도로 검색
• 충돌 해결을 위한 체이닝 사용

[1.3] MySQL이 인덱스를 사용하는 상황

• WHERE 절 검색 최적화
• 인덱스가 없다면: 전체 테이블 스캔
• 인덱스 있을 때: B-트리에서 직접 검색 가능

• 선택적 인덱스 사용

CREATE TABLE employees (
    id INT PRIMARY KEY,
    department VARCHAR(50),
    gender CHAR(1),
    INDEX dept_idx (department),
    INDEX gender_idx (gender)
);

// MySQL은 department 인덱스 선택 (더 선택적)
SELECT * FROM employees 
WHERE department = 'IT' AND gender = 'M';

• department: 여러 부서 존재 (높은 선택성)
• gender: M/F 만 존재 (낮은 선택성)
 

• 조인(JOIN) 작업 수행 시, 동일한 유형과 크기로 선언된 경우 열에 대한 인덱스를 더 효율적으로 사용할 수 있습니다.
•  VARCHAR및 는 CHAR동일한 크기로 선언된 경우 동일한 것으로 간주됩니다.
• VARCHAR(10)및 는 CHAR(10)동일한 크기이지만 VARCHAR(10)및 는 CHAR(15)그렇지 않습니다.

• 복합 키 인덱스
 

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    status VARCHAR(20),
    INDEX idx_user_date_status (user_id, order_date, status)
);

// 인덱스 활용 가능한 쿼리들
SELECT * FROM orders WHERE user_id = 1;  
// index 걸림
SELECT * FROM orders WHERE user_id = 1 AND order_date = '2024-01-01';  
// index 걸림
SELECT * FROM orders WHERE order_date = '2024-01-01';  
// index 안걸림, 2번 째 열인 order_date를 조건으로함

 
 

• ORDER BY, GROUP BY 정렬 작업
 

CREATE TABLE products (
    category_id INT,
    price DECIMAL(10,2),
    INDEX idx_cat_price (category_id, price)
);

// 인덱스를 활용한 효율적인 MIN/MAX 검색
SELECT MIN(price), MAX(price)
FROM products 
WHERE category_id = 1;

 

• MIN/MAX 값 최적화
 

CREATE TABLE products (
    category_id INT,
    price DECIMAL(10,2),
    INDEX idx_cat_price (category_id, price)
);

// 인덱스를 활용한 효율적인 MIN/MAX 검색
SELECT MIN(price), MAX(price)
FROM products 
WHERE category_id = 1;

 

• 커버링 인덱스 (인덱스만으로 쿼리 결과 제공)
 
커버링 인덱스란 무엇일까요? 커버링 인덱스란, 쿼리가 필요로 하는 모든 데이터가 인덱스에 포함된 경우를 말합니다.
 

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_name_email_city (name, email, city)
);

// 커버링 인덱스 활용 (테이블 접근 없음)
SELECT name, email, department  -- 인덱스에 포함된 컬럼만 조회
FROM employees
WHERE name = 'John';

// 커버링 인덱스 미활용 (테이블 접근 필요)
SELECT id, name, email  -- id는 인덱스에 없음
FROM employees
WHERE name = 'John';

 
위와 같은 users 테이블이 있다고 할 때, 쿼리가 필요로 하는 모든 데이터가 인덱스에 포함되지 않는 경우

// 일반 인덱스 사용 (테이블 접근 필요)
SELECT id, name, age  // age는 인덱스에 없음
FROM users
WHERE name = 'John';

• 인덱스에서 'John' 검색
• 인덱스에서 해당 레코드의 주소 확인
• 테이블 데이터에 접근하여 age 값 조회
• 결과 반환
순으로 동작하게 되서, 디스크 I/O와 쿼리 응답 시간이 더 커지게 됩니다.
 
반면, 커버링 인덱스 검색을 하게되면

// 복합 인덱스 INDEX idx_name_email_city (name, email, city)
SELECT name, email, city
FROM users
WHERE name = 'John';

• 인덱스에서 'John' 검색
• 인덱스에서 필요한 모든 데이터 확인
• 바로 결과 반환
 
이런 커버링 인덱스를 테이블에 구축함으로 써 얻을 수 있는 장점은 아래와 같습니다.
• 디스크 I/O 감소
• 메모리 사용 최적화
• 쿼리 응답 시간 단축
 
만약 위 users 테이블에 대한 페이지네이션을 진행하고 싶다면

// 커버링 인덱스가 없어 Using Index 상태가 아닌 경우
SELECT id, name, email
FROM users
WHERE city = 'Seoul'
ORDER BY name
LIMIT 100 OFFSET 5000;

// INDEX idx_city_name_email_id (city, name, email, id) index에 id 추가

// 이제 이 쿼리는 커버링 인덱스를 제대로 활용 가능
SELECT id, name, email
FROM users
WHERE city = 'Seoul'
ORDER BY name
LIMIT 100 OFFSET 5000;

// 혹은 id를 인덱스에 추가하지 않고
// 서브쿼리에서는 인덱스에 포함된 컬럼만 조회
// 메인쿼리에서 필요한 id 조회
SELECT u.id, u.name, u.email
FROM (
    SELECT name, email
    FROM users
    WHERE city = 'Seoul'
    ORDER BY name
    LIMIT 100 OFFSET 5000
) sub
JOIN users u 
    ON u.name = sub.name 
    AND u.email = sub.email;

 
복합 인덱스에 id를 추가하여, 페이지 네이션에서 사용될 데이터를 커버링 인덱스로 만들고 페이지네이션을 진행할 수 있게 됩니다.
 
커버링 인덱스에 관한 좋은 게시물이 있어 추가합니다.
1. 커버링 인덱스 (기본 지식 / WHERE / GROUP BY)
일반적으로 인덱스를 설계한다고하면 WHERE절에 대한 인덱스 설계를 이야기하지만 사실 WHERE뿐만 아니라 쿼리 전체에 대해 인덱스 설계가 필요합니다. 인덱스의 전반적인 내용은 이전 포스팅을 참고하시면 좋습니다. 인덱스는 데이터를 효율적으로 찾는 방법이지만, MySQL의 경우 인덱스안에 포함된 데이터를 사용할 수 있으므로 이를 잘 활용한다면 실제 데이터까지 접근할 필요가 전혀 없습니다. 이처럼 쿼리를 충족시키는 데 필요한 모든 데이터를 갖고 있는 인덱스를 커버링 인덱스 (Covering Index 혹은 Covered Index) 라고합니다. 좀 더 쉽게 말씀드리면 SELECT, WHERE, ORDER BY, GROUP BY 등에 사용되는 모든 컬럼이 인덱스의 구성요소인 경우를 얘기합니다. 1-1. 커버링..

https://jojoldu.tistory.com/476

[2] Index 설정 핵심 개념

[2-1] 선택성(Selectivity)과 카디널리티(Cardinality)

• 카디널리티(Cardinality)
특정 컬럼에서 유니크한 값의 개수를 의미합니다.
• 카디널리티가 높으면 인덱스 설정에 좋은 컬럼입니다.
• 하나의 row에서 카디널리티가 높다는 것은 모든 값이 unique하다는 의미입니다.

• 선택성(Selectivity)
선택성은 카디널리티라는 개념을 전체 행 수로 나눈 비율입니다. 더 정확히는 일정 데이터 집합에서 특정한 값을 얼마나 잘 선택할 수 있는가에 대한 지표입니다. 선택성이 1이라는 것은 모든 값이 유니크하다는 의미입니다.
• 선택도가 낮으면 인덱스 설정에 좋은 컬럼입니다.
• 수식으로는 (카디널리티 / 전체 행 수)
• 0과 1 사이의 값을 가짐
• 1에 가까울수록 높은 선택성
• 선택성이 높다는 것은, 전체 1000명의 직원이 있는 테이블에서
 

• 예시
(선택성과 카디널리티 비교) 1000개 행이 있는 테이블에서
• 카디널리티
• 성별 컬럼의 카디널리티는 2 (남/여)
• 주민등록번호 컬럼의 카디널리티는 전체 행 수와 거의 동일
• 선택성
• 예: 성별 컬럼의 카디널리티는 0.002 (2/1000)
• 예: 주민등록번호의 선택성은 1.0
 
(선택성) 선택성이 높다는 것은, 전체 1000명의 직원이 있는 테이블에서
• 이메일 주소 (높은 선택성)
• 1000명 모두 다른 이메일 주소
• 선택성 = 1000/1000 = 1.0 (높음)
• 한 이메일로 검색하면 딱 1명만 찾아짐
• 성별 (낮은 선택성)
• 남/여 두 가지 값만 존재
• 선택성 = 2/1000 = 0.002 (낮음)
• 'M'으로 검색하면 약 500명이 찾아짐

[2-2] 조회 활용도 (↑ 높을수록 좋음)

// 조회 활용도가 높은 예시
SELECT * FROM employees 
WHERE employee_id = 1234;    // 사번으로 자주 조회
WHERE email = '@test.com';   // 이메일로 자주 검색

• WHERE 절에서 자주 사용되는 컬럼

• JOIN 조건으로 자주 사용되는 컬럼

• ORDER BY에서 자주 사용되는 컬럼

• GROUP BY에서 자주 사용되는 컬럼

[2-3] 수정 빈도 (↓ 낮을수록 좋음)

// 수정 빈도가 높은 예시 (인덱스로 부적합)
UPDATE employees 
SET last_login_time = CURRENT_TIMESTAMP  // 로그인할 때마다 갱신
SET status = 'ACTIVE'                    // 상태가 자주 변경됨

• INSERT/UPDATE/DELETE 발생 시 인덱스도 함께 갱신 필요

• 인덱스 갱신 작업은 추가적인 I/O 발생

• 수정이 잦은 컬럼에 인덱스를 걸면 성능 저하 가능성

[2-4] 복합 키 및 인덱스 순서(Composite Index Order)

• 왼쪽 기준 규칙(Leftmost Principle)

// 인덱스: (a, b, c) 순서로 생성된 경우

// 효율적인 쿼리 (인덱스 사용)
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

// 비효율적인 쿼리 (인덱스 미사용)
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

• 선택성을 고려

// 좋은 예
CREATE INDEX idx_employee ON employees (
    department_id,  // 선택성 중간 (20개 부서)
    position,       // 선택성 중간 (10개 직급)
    name           // 선택성 높음 (거의 고유)
);

// 자주 사용되는 쿼리 패턴
WHERE department_id = 10 AND position = 'manager'

• 정렬(ORDER BY) 고려

// 인덱스: (department_id, hire_date)

// 효율적인 쿼리 (인덱스 사용)
SELECT * FROM employees
WHERE department_id = 10
ORDER BY hire_date;

// 비효율적인 쿼리 (인덱스 미사용)
SELECT * FROM employees
WHERE department_id = 10
ORDER BY name;  // 인덱스에 없는 컬럼으로 정렬

• 가장 자주 사용되는 조건을 왼쪽에 배치

• 선택성이 높은 컬럼을 왼쪽에 배치

• 정렬이 자주 필요한 컬럼도 고려

• = 조건이 많은 컬럼을 BETWEEN, LIKE 보다 왼쪽에 배치

[3] 인덱스 오버헤드

[3.1] 저장공간 오버헤드

CREATE TABLE users (
    id INT,
    name VARCHAR(100),
    email VARCHAR(100),
    address TEXT
);

// 각각의 인덱스가 추가 공간 필요
CREATE INDEX idx_name ON users(name);    // 이름 인덱스
CREATE INDEX idx_email ON users(email);  // 이메일 인덱스

• 각 인덱스는 원본 데이터와 별도로 저장공간 필요

• 보통 원본 테이블의 10-20% 추가 공간 필요

• 복합 인덱스는 더 많은 공간 필요

[3.2] 성능 오버헤드(DML 작업시 발생)

// 인덱스가 걸린 컬럼 수정 시
UPDATE users SET email = 'new@email.com';  // 인덱스도 업데이트
INSERT INTO users VALUES (...);            // 모든 인덱스에 데이터 추가
DELETE FROM users WHERE id = 1;            // 모든 인덱스에서 데이터 삭제

• 인덱스 데이터 재정렬

• 인덱스 페이지 분할

• 인덱스 깊이 조정

[3.3] 성능 저하 및 인덱스 생성 시 고려사항

// 불필요한 인덱스 예시
CREATE INDEX idx_rarely_used ON orders(created_at);  // 거의 사용안함
CREATE INDEX idx_duplicate ON users(first_name, name);  // 중복적인 효과

SHOW INDEX FROM table_name; // 인덱스 사용 통계

• 주기적인 인덱스 사용 현황 모니터링

• 사용되지 않는 인덱스 제거

• 중복되는 인덱스 통합

• 비용대비 효과 분석

• 참고
 
MySQL :: MySQL 9.1 Reference Manual :: 10.3.1 How MySQL Uses Indexes
Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.

https://dev.mysql.com/doc/refman/9.1/en/mysql-indexes.html