[스터디] 13. B-Tree 인덱스

목차

 

8.3 B-Tree 인덱스 개요

• B-Tree 인덱스는 가장 기본적인 인덱스 구조로, 데이터가 균형적으로 유지되어 효율적인 검색이 가능함.
• B-Tree의 B는 Binary(이진)이 아닌 Balanced(균형)을 의미하며, DBMS에서는 주로 B+-Tree와 B*-Tree 변형을 사용함.
  • B-Tree 인덱스는 주로 이진 트리 구조를 기반으로 하지만, 데이터의 처리와 저장 방식에 따라 다양한 구조를 사용함.
• 데이터는 항상 정렬된 상태로 저장되어 검색 효율을 극대화함.

8.3.1 B-Tree 인덱스의 구조 및 특성

• B-Tree의 기본 구조는 루트 노드, 브랜치 노드, 리프 노드로 구성되며, 루트에서 리프 노드로 내려가며 데이터를 찾음.
• 인덱스의 리프 노드는 항상 데이터 파일에 저장된 레코드의 주소를 가지고 있음.
• MySQL의 MyISAM과 InnoDB 엔진은 B-Tree 인덱스를 활용하는 방식이 다름.

▲ B-Tree 인덱스의 구조

8.3.2 B-Tree 인덱스 키 추가 및 삭제

• 키 추가: 새로운 키가 추가될 위치를 검색하여 저장하며, 필요한 경우 노드 분리(Split) 작업을 수행해 트리의 균형을 유지함.
• 키 삭제: 리프 노드에서 키를 제거하고, 삭제된 공간은 필요 시 재활용함.

8.3.3 B-Tree 인덱스 사용에 영향을 미치는 요소

• 인덱스의 키 크기, 인덱스 깊이, 선택도와 같은 요소들이 인덱스의 효율성에 영향을 줌.
• 키 크기가 클수록 인덱스의 저장 공간이 늘어나며, 선택도가 높을수록 검색 성능이 향상됨.

8.3.4 B-Tree 인덱스를 통한 데이터 읽기

• 인덱스 레인지 스캔, 인덱스 풀 스캔, 루스 인덱스 스캔과 같은 방식으로 데이터를 검색함.
• 인덱스는 쿼리 조건에 따라 최적화된 검색 방식을 선택하여 데이터 읽기 효율을 극대화함.

-- 인덱스 레인지 스캔
EXPLAIN SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';

-- 인덱스 풀 스캔
EXPLAIN SELECT * FROM employees WHERE birth_date >= '1990-01-01';

-- 루스 인덱스 스캔 (GROUP BY와 함께 사용)
EXPLAIN SELECT dept_no, MIN(emp_no) FROM dept_emp WHERE dept_no BETWEEN 'd002' AND 'd004' GROUP BY dept_no;

8.3.5 다중 칼럼(Multi-column) 인덱스

• 다중 칼럼 인덱스는 여러 개의 칼럼을 하나의 인덱스로 결합하여 쿼리 효율성을 높임.
• MySQL에서는 Concatenated Index라고도 하며, 각 칼럼의 순서가 중요한 역할을 함.

-- 다중 칼럼 인덱스 생성
CREATE INDEX idx_dept_emp ON employees (dept_no, emp_no);

-- 다중 칼럼 인덱스 활용 쿼리
EXPLAIN SELECT * FROM employees WHERE dept_no = 'd002' AND emp_no >= 10102;

8.3.6 B-Tree 인덱스의 정렬 및 스캔 방향

• B-Tree 인덱스는 오름차순 또는 내림차순으로 정렬되며, 인덱스 스캔 방향에 따라 효율적인 검색이 가능함.
• 인덱스는 정렬 순서에 따라 오름차순 정렬 인덱스나 내림차순 정렬 인덱스로 생성할 수 있음.

-- 오름차순 및 내림차순 인덱스 생성
CREATE INDEX idx_firstname_asc ON employees (first_name ASC);
CREATE INDEX idx_firstname_desc ON employees (first_name DESC);

-- 인덱스를 이용한 정렬 방향 비교
EXPLAIN SELECT * FROM employees ORDER BY first_name ASC LIMIT 5;
EXPLAIN SELECT * FROM employees ORDER BY first_name DESC LIMIT 5;

8.3.6.1 인덱스 스캔 방향 테스트

• 정순 스캔과 역순 스캔을 통해 B-Tree의 정렬 및 스캔 방향의 효율성을 확인함.

-- 정순 스캔 예시
EXPLAIN SELECT * FROM employees WHERE first_name >= 'Anna' ORDER BY first_name ASC LIMIT 3;

-- 역순 스캔 예시
EXPLAIN SELECT * FROM employees WHERE first_name <= 'Mike' ORDER BY first_name DESC LIMIT 3;

8.3.7 B-Tree 인덱스의 가용성과 효율성

• 인덱스의 가용성은 쿼리 조건에 따라 다르며, 조건이 인덱스를 최적으로 활용할 수 있는지 판단하는 것이 중요함.
• 특정 조건이 있을 때 B-Tree 인덱스의 작업 범위 결정 조건과 체크 조건이 쿼리의 성능에 영향을 미침.

8.3.7.1 인덱스 사용 불가 조건

• 다음과 같은 조건에서는 인덱스를 효과적으로 사용할 수 없음.
  • `<>`, `NOT IN`, `IS NOT NULL`과 같은 NOT-EQUAL 조건
  • LIKE 조건이 `%`로 시작할 때
  • 연산자 또는 함수로 인덱스 칼럼이 변형된 경우

-- NOT-EQUAL 조건으로 인덱스 사용 불가
EXPLAIN SELECT * FROM employees WHERE dept_no <> 'd001';

-- LIKE 조건의 전방 일치
EXPLAIN SELECT * FROM employees WHERE first_name LIKE '%e';

SQL 실습

1. MySQL Workbench에서 새 데이터베이스와 테이블 생성하기

 

-- 새 데이터베이스 생성 및 사용
CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;

-- 테이블 생성
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    dept_no VARCHAR(10),
    birth_date DATE
);

 

2. 데이터 삽입하기

 

INSERT INTO employees (first_name, dept_no, birth_date)
VALUES 
    ('Alice', 'D001', '1985-05-01'),
    ('Bob', 'D002', '1980-08-22'),
    ('Charlie', 'D003', '1975-12-15'),
    ('Diana', 'D001', '1990-02-17'),
    ('Eve', 'D002', '1983-07-29'),
    ('Frank', 'D003', '1978-09-12');

 

3. 단일 칼럼 인덱스 생성하기

 

-- 단일 칼럼 인덱스 생성
CREATE INDEX idx_firstname ON employees (first_name);

 

(인덱스 레인지 스캔)
`first_name`을 기준으로 범위를 지정하여 쿼리 결과를 확인함.

EXPLAIN SELECT * FROM employees WHERE first_name BETWEEN 'Alice' AND 'Eve';

 

4. 다중 칼럼 인덱스 생성하기

 

-- 다중 칼럼 인덱스 생성
CREATE INDEX idx_dept_emp ON employees (dept_no, emp_id);

 

`dept_no`와 `emp_id`를 조건으로 설정해 인덱스의 성능을 확인함.

EXPLAIN SELECT * FROM employees WHERE dept_no = 'D001' AND emp_id > 1;

 

5. 정렬 및 스캔 방향 실습

 

정렬된 인덱스의 효율성을 실습함. 내림차순과 오름차순으로 쿼리를 작성해 인덱스가 어떻게 작동하는지 비교함.

-- 인덱스를 이용한 오름차순과 내림차순 정렬 비교
EXPLAIN SELECT * FROM employees ORDER BY first_name ASC LIMIT 3;
EXPLAIN SELECT * FROM employees ORDER BY first_name DESC LIMIT 3;

 

6. 인덱스 가용성과 효율성 확인하기

인덱스가 사용되지 않는 조건을 테스트함.

-- LIKE 조건에서 앞에 %를 사용할 때 인덱스가 사용되지 않는 경우 확인
EXPLAIN SELECT * FROM employees WHERE first_name LIKE '%e';

 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.