인덱스

[metaapple]

[metaapple]

---

---

---

---

✅ 실습 목적

• 인덱스를 사용할 경우와 사용하지 않을 경우의 성능 차이를 실제 실행 시간과 쿼리 플랜 (EXPLAIN) 을 통해 비교
• MySQL 8의 InnoDB 엔진의 클러스터드 인덱스 특성을 활용한 체감 실습

-- ✅ 데이터베이스 생성: test_index_db라는 이름의 데이터베이스를 생성합니다.
-- 이미 존재한다면 생성하지 않습니다.
CREATE DATABASE IF NOT EXISTS test_index_db;

-- 해당 데이터베이스 사용
USE test_index_db;


-- ✅ 인덱스가 없는 테이블 생성
-- 직원 정보 저장용 테이블이며, 'department' 컬럼에 인덱스를 만들지 않습니다.
CREATE TABLE employees_no_index (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- 기본키, 자동 증가 ID
    name VARCHAR(100),                           -- 직원 이름
    department VARCHAR(100),                     -- 부서 이름 (검색 대상)
    age INT,                                     -- 나이
    salary INT                                   -- 급여
);


-- ✅ 인덱스가 있는 테이블 생성
-- 위 테이블과 동일하지만, 'department' 컬럼에 인덱스를 추가합니다.
CREATE TABLE employees_with_index (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- 기본키, 자동 증가 ID
    name VARCHAR(100),                           -- 직원 이름
    department VARCHAR(100),                     -- 부서 이름
    age INT,                                     -- 나이
    salary INT,                                  -- 급여
    INDEX idx_department (department)            -- ✅ department 컬럼에 인덱스 추가
);


-- ✅ 저장 프로시저 정의 시작 (100,000건 더미 데이터를 삽입하기 위한 루프 구조)
DELIMITER $$

CREATE PROCEDURE insert_dummy_data()
BEGIN
    DECLARE i INT DEFAULT 0;  -- 반복용 변수 선언
    WHILE i < 100000 DO
        -- employees_no_index 테이블에 더미 데이터 한 건 삽입
        INSERT INTO employees_no_index (name, department, age, salary)
        VALUES (
            CONCAT('Employee_', i),                                       -- 이름: Employee_0 ~ Employee_99999
            ELT(FLOOR(1 + RAND() * 5), 'HR', 'Sales', 'Engineering',      -- 부서: 무작위 선택
                'Marketing', 'Finance'),
            FLOOR(25 + RAND() * 30),                                      -- 나이: 25 ~ 54
            FLOOR(30000 + RAND() * 20000)                                 -- 급여: 30000 ~ 49999
        );
        SET i = i + 1;  -- 다음 반복을 위한 증가
    END WHILE;
END$$

-- 저장 프로시저 정의 종료
DELIMITER ;


-- ✅ 저장 프로시저 실행 (실제 더미 데이터를 10만 건 삽입)
CALL insert_dummy_data();


-- ✅ 인덱스 테이블에 동일한 데이터를 복사
-- 인덱스 없는 테이블의 데이터를 인덱스 있는 테이블로 복사
INSERT INTO employees_with_index (name, department, age, salary)
SELECT name, department, age, salary FROM employees_no_index;


-- ✅ 인덱스 없는 테이블에서 부서명이 'Engineering'인 데이터를 검색
-- 전체 테이블을 탐색 (Full Table Scan)할 가능성이 높음
EXPLAIN SELECT * FROM employees_no_index WHERE department = 'Engineering';


-- ✅ 인덱스 있는 테이블에서 동일한 검색 실행
-- 인덱스를 통해 빠르게 검색됨 (Index Range Scan 또는 Index Ref Scan)
EXPLAIN SELECT * FROM employees_with_index WHERE department = 'Engineering';

-- ✅ 실제 인덱스 검색 쿼리 실행 (결과 확인용)
SELECT * FROM employees_with_index WHERE department = 'Engineering';

[metaapple]

✅ InnoDB

• InnoDB는 MySQL에서 사용하는 스토리지 엔진(데이터 저장 방식)
• 다르게 말하면, 테이블 데이터를 어떻게 저장하고, 읽고, 쓰고, 트랜잭션 처리할지를 결정하는 모듈
• InnoDB는 MySQL 8에 기본 내장된 스토리지 엔진 프로그램 --> 5.5부터 default가 됨.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB;

[metaapple]

⸻

1. Clustered Index Tree (클러스터 인덱스 트리)
   • Root 노드는 PK(id) 기반의 B+ Tree
   • 리프 노드(Leaf Node)에 실제 전체 데이터 row가 함께 저장되어 있음.
   • 예: 10 → 1 (Alice, 25, Seoul)

특징:
• PK 조회 시 인덱스 → 데이터 접근이 한 번에 이루어짐.
• 빠르고 효율적

⸻

2. Secondary Index Tree (비클러스터 인덱스 트리)
   • Root 노드는 name을 기준으로 정렬된 인덱스
   • 리프 노드는 name과 함께 **참조하는 PK(id)**만 저장됨.
   • 이후 PK를 이용해 다시 클러스터 인덱스를 탐색해 실제 데이터를 찾음.

예: Charlie → PK=3 → clustered index lookup

특징:
• Secondary Index는 항상 간접 접근을 요구함.
• 전체 row를 찾으려면 두 번의 접근이 필요.
⸻

이런 구조적 차이는 SELECT 성능, 특히 WHERE 조건, JOIN, ORDER BY, LIMIT과 함께 사용될 때 큰 영향을 미치며, 인덱스 튜닝의 핵심이됨.