Index Dive

서론

---

데이터베이스에서 쿼리 성능 최적화는 매우 중요한 작업이다. 데이터의 양이 많아질수록 효율적인 데이터 접근이 쿼리의 성능을 좌우하게 된다. 이 과정에서 핵심적인 역할을 하는 것이 바로 옵티마이저(Optimizer)이며, 그중에서도 “Index Dive”는 옵티마이저의 중요한 동작 방식 중 하나이다.

 

MySQL 옵티마이저는 쿼리 실행 계획을 수립할 때 Index Dive를 통해 인덱스를 탐색하고, 조건에 부합하는 데이터를 추정한다. 그러나 IN 조건이 많을 경우, Index Dive가 탐색해야 하는 경우의 수가 급격히 증가하여 성능 저하를 유발할 수 있다.

 

IN 조건이 많은 경우의 Index Dive

Index Dive에 대해 알아보기 전 아래와 같은 테이블이 있다 가정하자.

CREATE TABLE products (
    id INT PRIMARY KEY,
    category_id INT,
    brand_id INT,
    price DECIMAL(10, 2),
    stock INT,
    INDEX idx_category_id (category_id),
    INDEX idx_brand_id (brand_id),
    INDEX idx_price (price)
);

 

그리고 다음 쿼리를 실행한다고 가정한다.

SELECT *
FROM products
WHERE category_id IN (1, 2, 3, ..., 150)
  AND brand_id IN (1001, 1002, 1003, ..., 1150);

• WHERE 조건
  • category_id 컬럼에 150개의 IN 조건
  • brand_id 컬럼에 150개의 IN 조건
  • 각 컬럼에 대해 탐색할 인덱스는 3개(idx_category_id, idx_brand_id, idx_price)

 

Index Dive의 경우의 수

Index Dive는 쿼리를 최적화하기 위해 각 IN 조건에 대해 가능한 모든 조합을 탐색한다.

• 총 탐색 경우의 수: 150 x 150 x 3 = 67,500

즉, 옵티마이저는 67,500가지 경우의 수를 고려한 후, 비용(Cost)이 가장 낮은 실행 계획을 선택한다.

 

문제점: Index Dive의 탐색 비용 증가

1. IN 조건의 값이 많을수록 비용 증가
   • IN 조건 값이 많으면, MySQL은 모든 조건 값을 Index Dive로 탐색하므로 비용이 급격히 증가한다.
2. 인덱스 선택이 복잡해짐
   • 여러 인덱스가 존재하는 경우, 옵티마이저는 탐색 비용이 더 높아진다.
3. 성능 저하
   • 탐색 경우의 수가 많아질수록 실행 계획 수립에 더 많은 시간이 소요될 수 있다.

 

참고
일반적으로 위와 같은 문제가 발생하는 경우 옵티마이저에게 어떤 인덱스를 사용할지 명시해주면 된다.

SELECT *
FROM products
USE INDEX (idx_category_id)
WHERE category_id IN (1, 2, 3, ..., 150)
        AND brand_id IN (1001, 1002, ..., 1150);

 

---

Index Dive란?

---

Index Dive는 MySQL의 옵티마이저(Optimizer)가 쿼리 실행 계획을 세울 때, 인덱스를 탐색하여 조건에 해당하는 데이터 범위와 레코드 수를 추정하는 과정이다. 쉽게 말해, 인덱스를 살펴보고 필요한 데이터가 얼마나 있는지를 계산하여 최적의 실행 계획을 결정하는 것이다.

 

예를 들어, 다음 쿼리를 실행하는 경우

SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;

• 옵티마이저는 salary 인덱스를 Dive하여 5000 ~ 10000 범위의 데이터가 얼마나 있는지 추정한다.

 

옵티마이저는 여러 실행 경로를 분석하고, 비용(Cost)을 계산하여 가장 효율적인 방법을 선택한다.

• 인덱스 선택: 쿼리 조건에 가장 적합한 인덱스를 결정한다.
• 조인 순서 결정: 여러 테이블이 조인될 때, 어떤 순서로 조인을 수행할지 선택한다.
• 실행 방식 결정: 풀 테이블 스캔, 인덱스 스캔, 레인지 스캔 등 다양한 방식 중 최적의 실행 방식을 선택한다.

 

Index Dive의 역할

---

옵티마이저는 쿼리를 최적화하기 위해 테이블의 인덱스를 조사하고, 그 과정에서 Index Dive를 수행한다.

Index Dive는 옵티마이저가 다음 작업을 수행할 때 중요한 도구로 사용된다.

• 데이터 분포 파악: 특정 조건에 맞는 데이터가 얼마나 존재하는지 파악한다.
• 선택도(Selectivity) 계산: 조건에 따라 얼마나 효율적으로 데이터를 검색할 수 있을지 추정한다.
• 비용 계산: 인덱스를 사용할 경우와 사용하지 않을 경우(풀 테이블 스캔)의 비용을 비교한다.

 

Index Dive의 동작 방식

---

1. 통계 정보 분석

MySQL은 ANALYZE TABLE 명령어를 통해 생성된 통계 정보를 활용한다.

• 인덱스의 카디널리티(Cardinality): 인덱스에 저장된 고유 값의 개수
  • 카디널리티가 높은 경우: 값이 대부분 고유한 경우
    ex) 주민등록번호, 상품 ID (유일한 값)
    • 효율적인 인덱스 검색 가능
  • 카디널리티가 낮은 경우: 값이 중복되는 경우
    ex) 성별, 부서 ID (중복 많음)
    • 인덱스 효율이 낮아질 수 있음
• 데이터 분포 정보: 특정 범위나 값에 해당하는 레코드의 비율 추정

 

2. 범위 탐색

쿼리에서 특정 조건이 주어졌을 때, 그 조건에 해당하는 데이터가 인덱스의 어느 범위에 위치하는지 탐색한다.

SELECT * FROM sales WHERE price BETWEEN 100 AND 500;

• price 컬럼에 대해 범위를 지정했으므로, 옵티마이저는 인덱스를 통해 범위에 해당하는 데이터를 탐색한다.

 

3. 비용 계산

옵티마이저는 Index Dive를 통해 조건에 해당하는 데이터의 양과 이를 검색하는 데 드는 비용을 계산한다.

• 풀 테이블 스캔 비용
• 인덱스 스캔 비용

 

1) 조건 분석

쿼리에서 WHERE, JOIN, GROUP BY, ORDER BY 등의 조건을 분석해 인덱스를 사용할 수 있는지 확인한다.

SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;

• 옵티마이저는 salary 컬럼에 인덱스가 있는 경우, 해당 범위를 탐색할 수 있다.

 

2) 인덱스 범위 계산

옵티마이저는 조건을 기반으로 인덱스의 탐색 범위를 결정한다.

• 범위 스캔(Range Scan): 인덱스에서 시작 지점과 끝 지점을 설정한다.
  • 시작: salary >= 5000
  • 끝: salary <= 10000

 

인덱스 트리 구조에서 탐색을 시작하며, 조건 범위에 해당하는 레코드를 확인한다.

 

3) 통계 정보 사용

MySQL은 통계 정보를 활용해 탐색 범위 내 레코드 수를 추정한다.

• 통계 정보는 SHOW INDEX 명령어로 확인 가능하다.

SHOW INDEX FROM employees;

 

• Cardinality: 인덱스에 포함된 고유 값의 개수

 

예시

• salary 범위(5000 ~ 10000) 안에 전체 레코드의 10%가 존재한다고 추정 (조건 컬럼의 인덱스 카디널리티와 전체 레코드 수의 비율로 계산 가능)
• 옵티마이저는 이를 바탕으로 선택도(selectivity)를 계산한다.
  • 선택도는 항상 0에서 1 사이의 값을 가진다.
  • 선택도가 낮음 (0에 가까움): 조건에 만족하는 데이터가 적음
    • 인덱스를 사용하는 것이 효율적
  • 선택도가 높음 (1에 가까움): 조건에 만족하는 데이터가 많음
    • 인덱스가 비효율적

 

실행 계획 결정

• 옵티마이저는 Index Dive의 결과를 바탕으로 쿼리 실행 계획을 세운다.
• 선택도가 낮아(즉, 적은 데이터만 선택) 인덱스 사용이 효율적이라 판단되면 인덱스 스캔을 선택한다.
• 선택도가 높아(즉, 대부분의 데이터가 선택) 풀 테이블 스캔이 더 효율적이라고 판단되면 인덱스를 사용하지 않을 수 있다.

 

---

PostgreSQL vs MySQL

---

PostgreSQL과 MySQL은 쿼리 실행 계획의 캐싱 및 재활용 방식에서 차이가 존재한다.

 

PostgreSQL의 쿼리 실행 계획 재활용

계획 수립의 캐싱

PostgreSQL은 서버 전체에서 쿼리 실행 계획을 재활용할 수 있다.

• PostgreSQL에서는 PreparedStatement(SQL 문을 미리 컴파일하고 실행 계획을 생성한 뒤, 이를 재사용하는 기능)를 사용하면 쿼리 실행 계획이 캐싱되어 이후 동일한 쿼리가 실행될 때 계획 수립 과정을 생략할 수 있다. (PostgreSQL에서 말하는 Prepared Statement는 데이터베이스 서버 내부에서 관리되는 Prepared Statement)
  • Prepared Statement를 사용하면 PostgreSQL은 해당 쿼리의 실행 계획을 서버 내부에 캐시 하여 동일한 쿼리가 반복 실행될 때 실행 계획을 재사용한다.
• 계획은 주어진 쿼리에 대해 미리 수립되며, 다음 실행 시 동일한 계획이 재사용된다.
• 자동 재계획(Auto-Replanning): 캐싱된 실행 계획이 데이터 통계와 맞지 않다고 판단되면 옵티마이저가 계획을 재수립할 수도 있다.

동작 방식

   1. 준비(Prepare) 단계

PREPARE my_query (int, text) AS
SELECT * FROM my_table WHERE id = $1 AND name = $2;

 

   2. 실행 단계

EXECUTE my_query(1, 'jun');

 

 

MySQL의 쿼리 실행 계획 재활용

MySQL은 쿼리 실행 계획을 다르게 관리한다.

 

1. 기본적으로 실행 계획을 재활용하지 않음

• MySQL은 매번 쿼리가 실행될 때 옵티마이저가 실행 계획을 새로 수립한다.
• 이는 MySQL의 경량 옵티마이저 설계 철학에 기반하며, 쿼리 실행 계획이 매번 최신 데이터 통계에 맞춰지도록 한다.

 

2. 하나의 커넥션 내에서 Prepared Statement를 사용한 경우 재활용

• MySQL에서도 PostgreSQL처럼 Prepared Statement를 사용하면 쿼리 실행 계획이 재활용된다.
  • MySQL 4.1 부터 Prepared Statement을 지원하며, SQL 구문을 미리 준비해놓고 그 인자를 받아 처리하거나 인자를 바꾸어 반복적으로 처리할 수 있다.
• 하지만 해당 재활용은 동일한 커넥션 내에서만 가능하다.
• 다른 커넥션에서는 새로 실행 계획을 수립해야 한다.
• 세션 단위로만 재활용 가능하다는 것이 PostgreSQL과의 큰 차이점이다.
• 성능 이슈: MySQL의 계획 재활용은 PostgreSQL과 달리 범위가 제한적이므로, 동적인 쿼리에서는 실행 계획 수립 비용이 누적될 가능성이 높다.

동작 방식

1. 클라이언트가 쿼리를 준비(Prepare) 요청

PREPARE stmt1 FROM 'SELECT * FROM employees WHERE id = ?';

 

   2. 서버는 쿼리 실행 계획을 수립하고, 이를 캐싱

   3. 클라이언트가 실행 요청(Execute) 시, 준비된 계획을 재사용

EXECUTE stmt1 USING @id;

 

   4. 필요 시 Statement 해제

DEALLOCATE PREPARE stmt1;

 

참고: Query Cache와의 혼동
MySQL에는 Query Cache라는 기능이 있었지만, 이는 쿼리 결과를 캐싱하는 것이지 실행 계획을 캐싱하는 것이 아니다.
(MySQL 8.0 이후로 Query Cache는 성능 문제로 인해 제거되었다.)

 

참고: EXPLAIN 명령어로 Index Dive 확인하기
MySQL에서 EXPLAIN 명령어를 사용하여 실행 계획을 확인하면, Index Dive의 결과를 간접적으로 확인할 수 있다.

 

만약 MySQL에서 실행 계획 재활용이 필요하다면 Prepared Statement를 활용하거나, 복잡한 쿼리의 경우 저장 프로시저를 고려하는 것이 좋다.

 

 

---

Index Dive가 효율적인 경우

---

범위 조건이 명확한 경우

조건

• WHERE 절에서 범위 조건이 명확하게 정의되어 있고, 해당 컬럼에 인덱스가 존재하는 경우

예제

SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;

 

효율적인 이유

• Index Dive는 B-Tree 인덱스를 활용하여 시작과 끝 값을 빠르게 찾고, 해당 범위 내 데이터를 탐색하므로 효율적이다.
• 범위가 명확하고 데이터가 고르게 분포된 경우, 최소한의 탐색으로 필요한 데이터를 추출할 수 있다.

 

선택도가 높은 조건

조건

• WHERE 조건에서 선택도가 높은 값(데이터 중 극히 일부만 일치)이 포함된 경우

예제

 

SELECT * FROM orders WHERE order_status = 'SHIPPED';

• order_status 컬럼에 인덱스가 있고, 'SHIPPED' 상태의 데이터가 전체 데이터 중 5%라면 Index Dive가 매우 효율적으로 동작

 

효율적인 이유

• 선택도가 높은 조건은 인덱스를 사용하여 데이터의 일부만 가져오기 때문에, 풀 테이블 스캔보다 훨씬 바르다.
• 옵티마이저는 Index Dive로 조건에 맞는 데이터를 정확히 추정하고 최소한의 리소스로 검색을 수행한다.

 

복합 인덱스가 조건에 적합한 경우

조건

• WHERE 조건에서 사용된 컬럼이 복합 인덱스의 선두 컬럼에 포함된 경우

예제

SELECT * FROM employees WHERE department_id = 3 AND salary > 5000;

• 복합 인덱스 INDEX(department_id, salary)가 있다면, 옵티마이저는 department_id = 3 조건으로 인덱스 탐색 후, salary > 5000 조건으로 필터링

 

효율적인 이유

• 복합 인덱스는 인덱스의 선두 컬럼부터 조건을 처리하며, Index Dive를 통해 탐색 범위를 좁혀 나갈 수 있다.

 

IN 조건에서 조건 값이 적을 경우

조건

• WHERE 절에서 IN 조건이 사용되고, 조건 값의 개수가 적을 때

예제

SELECT * FROM employees WHERE department_id IN (1, 2, 3);

 

효율적인 이유

• IN 조건 값이 적을 경우, 각각의 Index Dive 작업이 빠르게 수행되어 전체 쿼리의 성능을 높인다.
• 적은 수의 조건 값은 옵티마이저가 병렬적으로 처리하기에도 적합하다.

 

이외에도 정렬 또는 그룹 조건이 인덱스와 일치하는 경우, 조인 조건이 인덱스를 활용할 수 있는 경우 등 Index Dive를 활용하면 효율적인 경우가 존재한다.

 

 

---

Index Dive가 비효율적인 경우

---

Index Dive는 대게 효율적인 데이터 검색을 위해 설계되었지만, 특정 상황에서는 성능이 저하될 수 있다.

 

1. outdated Statistics (오래된 통계 정보)

Index Dive는 옵티마이저가 인덱스 통계를 기반으로 선택도를 추정할 때 정확도가 중요하다. 하지만 테이블의 데이터 통계가 오래되었다면, 잘못된 실행 계획을 수립할 수 있다.

 

문제점

• 데이터가 변경되었지만, MySQL의 통계 정보가 업데이트되지 않음
• 옵티마이저가 잘못된 레코드 수를 추정하여 비효율적인 계획(ex. 풀 테이블 스캔)을 선택

해결 방법

• 주기적으로 통계 정보를 갱신

ANALYZE TABLE employees;

 

 

2. 복잡한 조건 처리 (Composite Conditions)

WHERE 절에 복합 조건이 포함되면, Index Dive가 잘못된 실행 계획을 수립할 수 있다.

 

예제

SELECT * FROM employees
WHERE salary BETWEEN 5000 AND 10000
  AND department_id = 3;

• 옵티마이저는 salary 인덱스를 기준으로 계획을 수립할 수도 있지만, department_id와의 조합에서 효율성이 떨어질 수 있다.

문제점

• 복합 조건이 포함된 경우, 단일 인덱스만 사용되거나 잘못된 인덱스가 선택될 가능성이 있음

해결 방법

• 복합 인덱스 설계

CREATE INDEX idx_salary_department ON employees (department_id, salary);

 

 

3. 선택도가 낮은 조건 (Low Selectivity Conditions)

선택도가 낮은 조건(즉, 많은 데이터가 조건에 부합할 경우)에서는 Index Dive가 인덱스를 사용하는 계획을 선택하더라도 비효율적일 수 있다.

 

예제

SELECT * FROM employees WHERE department_id = 1;

• department_id 값이 전체 데이터의 50%를 차지하는 경우, 인덱스를 사용하는 것보다 풀 테이블 스캔이 빠를 수 있다.

문제점

• 선택도가 낮아 인덱스가 실질적으로 도움되지 않음

해결 방법

• 옵티마이저가 풀 테이블 스캔을 선택하도록 유도

SELECT /*+ FULL(employees) */ * FROM employees WHERE department_id = 1;

• MySQL 옵티마이저에게 /*+ FULL(employees) */ 는 employees 테이블을 풀 테이블 스캔(Full Table Scan) 하도록 지시하는 힌트이다.

 

4. IN 조건 값이 많은 경우 (Large IN Clauses)

IN 절에 많은 값을 나열하면, Index Dive가 각각의 값을 반복적으로 처리하므로 비효율적일 수 있다.

 

예제

SELECT * FROM employees WHERE department_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

• 옵티마이저는 각 department_id 값을 Index Dive로 처리하며, 값이 많을수록 성능이 저하된다.

문제점

• IN 조건 값이 많으면 Index Dive의 비용 증가

해결 방법

• 쿼리를 UNION으로 분리

SELECT * FROM employees WHERE department_id = 1;
UNION ALL
SELECT * FROM employees WHERE department_id = 2;

 

 

5. LIKE 조건 사용 시 비효율성

Index Dive는 LIKE 조건에서 인덱스를 제대로 활용하지 못할 수 있다.

 

예제

SELECT * FROM employees WHERE name LIKE '%Jun';

• name 컬럼에 인덱스가 있더라도, 앞부분이 와일드카드(%)로 시작하면 인덱스가 사용되지 않는다.

문제점

• 와일드카드로 시작하는 LIKE 조건은 인덱스를 활용하지 못함

해결 방법

• Full-Text Index 사용

ALTER TABLE employees ADD FULLTEXT(name);

 

 

---

Index Dive와 IN 쿼리

---

IN 쿼리는 여러 값을 조건으로 지정할 때 자주 사용되지만 Index Dive 동작 방식에 의해 IN 쿼리는 효율적일 수도, 비효율적일 수도 있다. 

 

IN 쿼리와 Index Dive의 관계

IN 조건 사용 시 MySQL 옵티마이저는 각 조건 값을 기반으로 인덱스를 개별적으로 탐색한다. 이 과정에서 Index Dive가 발생하며, 다음과 같은 작업이 이루어진다.

1. IN 조건의 각 값을 인덱스에서 검색
2. 조건에 해당하는 데이터의 범위를 계산
3. 모든 조건 값을 처리한 후 결과 병합

 

Index Dive는 IN 조건 값이 많을수록 더 많은 탐색을 수행하므로, 데이터의 양과 분포에 따라 성능에 큰 영향을 줄 수 있다.

 

 

IN 쿼리에서 Index Dive 동작 사례

예제 1: IN 조건이 효율적으로 작동하는 경우

CREATE TABLE employees (
    id INT PRIMARY KEY,
    department_id INT,
    name VARCHAR(50),
    salary INT,
    INDEX (department_id)
);

 

SELECT * FROM employees WHERE department_id IN (1, 2, 3);

 

Index Dive의 동작

• 옵티마이저는 department_id = 1, department_id = 2, department_id = 3 각각에 대해 Index Dive를 수행한다.
• 조건 값별로 인덱스 범위를 계산하고, 필요한 데이터만 검색한다.

 

EXPLAIN 분석

EXPLAIN SELECT * FROM employees WHERE department_id IN (1, 2, 3);

• type: INDEX RANGE SCAN
• rows: 각 조건 값에 대해 검색된 레코드 수

 

효율적인 이유

• IN 조건 값이 적고, 선택도가 높아 department_id 인덱스가 사용됨

 

예제 2: IN 조건이 비효율적으로 작동하는 경우

 

예제 1과 동일한 쿼리를 사용하여 결과를 비교 분석한다. (department_id 값을 1, 2, 3으로 80개 데이터 삽입)

EXPLAIN SELECT * FROM employees WHERE department_id IN (1, 2, 3);

• type: ALL
• rows: 80

 

비효율적인 이유

EXPLAIN 결과에서 type이 range에서 ALL로 변한 이유는 MySQL 옵티마이저가 실행 계획을 수립할 때, 인덱스 사용 여부를 결정하는 기준이 바뀌었기 때문이다. 이 변화는 데이터 분포, 통계 정보, 쿼리 조건, 그리고 테이블 상태에 따라 발생할 수 있다.

 

데이터 분포의 변화

MySQL 옵티마이저는 테이블 통계 정보를 기반으로 특정 조건이 얼마나 많은 데이터를 반환할지 추정한다.

• 데이터 분포가 고르게 되어 선택도가 낮아진 경우
  • department_id IN (1, 2, 3) 조건에서 department_id 값이 테이블 전체 데이터의 대부분을 차지하게 되면, 옵티마이저는 인덱스를 사용하는 것이 비효율적이라 판단할 수 있다.
  • 이 경우, 전테 테이블 스캔(ALL)이 더 나은 실행 계획으로 간주된다.

 

데이터 양의 증가

테이블에 삽입된 데이터가 많아지면 옵티마이저가 전체 데이터를 탐색하는 비용과 인덱스를 사용하는 비용을 비교하여 결정한다.

• 작은 테이블: 데이터가 적을 경우, 옵티마이저는 풀 테이블 스캔이 더 빠르다고 판단할 수 있다.
• 큰 테이블: 데이터가 많을수록 인덱스를 사용하는 것이 효율적이다.
• ex) 100개의 데이터 중 department_id에 해당하는 데이터가 90개라면, 옵티마이저는 풀 테이블 스캔을 선택할 가능성이 높다.