[스터디3] MySQL 스터디 - 클러스터드 vs 논클러스터드 인덱스 2차 실험

클러스터드 인덱스와 논클러스터드 인덱스를 비교하는 테스트 쿼리 수행

기존 테스트: https://ssuperjun.tistory.com/59

피드백: 가설 설정과 테스트 방향성은 좋지만, 논클러스터드 인덱스 테이블을 설계할 때 pk를 id로 설정하는 바람에 결과가 모두 유효하지 않게 됨

 

재실험 진행

결론: MySQL InnoDB 엔진에선 완전한 의미의 논클러스터드 인덱스 구현은 불가능

=> 논클러스터드 인덱스는 실제 row를 찾을 때 이중 탐색 구조(1. secondary index 탐색 -> 2. PK 얻음 -> 3. PK로 clustered index 재탐색) 때문

 

따라서 클러스터드 인덱스 vs 논클러스터드 인덱스 성능을 비교하는 대신

id를 pk로 하는 클러스터드 인덱스 vs uuid(랜덤값)를 pk로 하고 조회 시 세컨더리 인덱스가 커버링 인덱스

로 테이블을 설계해 실험을 진행

 

실험 방향성이

"PK, 세컨더리 인덱스 설계에 따른 성능 차이"로 바뀌게 됨

순차 PK vs. 랜덤 PK + 세컨더리 인덱스

---

사전 준비

-- ca801의 mysql 접속
USE index_test;
-- 작업 시간 확인용
SET profiling = 1;

 

테이블 생성

-- 1. 클러스터드 인덱스 테이블: 조회 기준 컬럼(age)이 PK → 클러스터드 인덱스 직접 활용
CREATE TABLE clustered_table (
    age        INT          NOT NULL,
    id         INT          NOT NULL AUTO_INCREMENT,
    name       VARCHAR(100),
    department VARCHAR(50),
    salary     INT,
    PRIMARY KEY (age, id),   -- age가 클러스터드 인덱스 선두 키
    KEY (id)        -- AUTO_INCREMENT 제약 해소용
);

-- 2. 논클러스터드 인덱스 테이블: PK는 UUID(랜덤) → 클러스터드 인덱스가 조회에 무관
--    age 조회는 반드시 세컨더리 인덱스 → PK → 클러스터드 재탐색 경로를 탐
CREATE TABLE non_clustered_table (
    pk_uuid    CHAR(36)     NOT NULL,           -- 랜덤 UUID → 클러스터드 키가 조회와 무관
    id         INT          NOT NULL AUTO_INCREMENT,
    name       VARCHAR(100),
    age        INT,
    department VARCHAR(50),
    salary     INT,
    PRIMARY KEY (pk_uuid),                      -- 랜덤값이 클러스터드 인덱스
    UNIQUE KEY uq_id (id),
    INDEX idx_age (age),                        -- 단일 세컨더리 인덱스
    INDEX idx_dept_salary (department, salary)  -- 복합 세컨더리 인덱스
);

 

데이터 삽입

-- 기존 프로시저 삭제 후 재생성
DROP PROCEDURE IF EXISTS insert_dummy_data;

-- 더미 데이터 삽입 (UUID 포함)
DELIMITER $$
CREATE PROCEDURE insert_dummy_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE v_name    VARCHAR(100);
    DECLARE v_age     INT;
    DECLARE v_dept    VARCHAR(50);
    DECLARE v_salary  INT;

    WHILE i < 1000000 DO
        SET v_name   = CONCAT('user_', i);
        SET v_age    = FLOOR(20 + RAND() * 40);
        SET v_dept   = ELT(FLOOR(1 + RAND() * 5), 'HR', 'Dev', 'Sales', 'Finance', 'Marketing');
        SET v_salary = FLOOR(30000 + RAND() * 70000);

        -- clustered_table: age가 PK 선두 → 범위 조회 시 클러스터드 인덱스 직접 사용
        INSERT INTO clustered_table (age, name, department, salary)
        VALUES (v_age, v_name, v_dept, v_salary);

        -- non_clustered_table: UUID PK → age 조회 시 반드시 이중 탐색 발생
        INSERT INTO non_clustered_table (pk_uuid, name, age, department, salary)
        VALUES (UUID(), v_name, v_age, v_dept, v_salary);

        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL insert_dummy_data();

 

실험 주제: PK 설계 방식과 세컨더리 인덱스 구성에 따른 읽기 성능 차이

• A 테이블: PK = (age, id) — 조회 기준 컬럼이 클러스터드 인덱스 선두 키
• B 테이블: PK = UUID — 클러스터드 키가 조회와 무관, 세컨더리 인덱스로만 접근

가설 1. 조회 기준 컬럼이 PK 선두 키(age)인 경우, 범위 조회는 A 테이블이 빠를 것이다.

가설 2. 복합 세컨더리 인덱스가 커버링 인덱스로 작동하면, SELECT *이 아닌 인덱스 포함 컬럼만 조회 시 B 테이블이 빠를 것이다.

가설 3. GROUP BY 기준이 PK 선두 키(age)이면 A 테이블이 빠를 것이다.

가설 4. GROUP BY 기준이 세컨더리 인덱스 컬럼(department, salary)이면, 커버링 인덱스가 작동하는 B 테이블이 빠를 것이다.

 

가설 1: 범위 조회

-- 캐시 제거
FLUSH TABLES;

-- 실행 계획 확인
EXPLAIN ANALYZE
SELECT * FROM clustered_table
WHERE age BETWEEN 30 AND 35;

-- 반복 실행 (시간 측정용)
SELECT * FROM clustered_table WHERE age BETWEEN 30 AND 35;
SELECT * FROM clustered_table WHERE age BETWEEN 30 AND 35;
SELECT * FROM clustered_table WHERE age BETWEEN 30 AND 35;

-- 캐시 제거
FLUSH TABLES;

-- 실행 계획 확인
EXPLAIN ANALYZE
SELECT * FROM non_clustered_table
WHERE age BETWEEN 30 AND 35;

-- 반복 실행 (시간 측정용)
SELECT * FROM non_clustered_table WHERE age BETWEEN 30 AND 35;
SELECT * FROM non_clustered_table WHERE age BETWEEN 30 AND 35;
SELECT * FROM non_clustered_table WHERE age BETWEEN 30 AND 35;

-- 시간 비교
SHOW PROFILES;

 

가설 2: 다중 컬럼 where

FLUSH TABLES;

EXPLAIN ANALYZE
SELECT department, salary FROM clustered_table
WHERE department = 'Dev' AND salary BETWEEN 50000 AND 80000;

SELECT department, salary FROM clustered_table WHERE department = 'Dev' AND salary BETWEEN 50000 AND 80000;
SELECT department, salary FROM clustered_table WHERE department = 'Dev' AND salary BETWEEN 50000 AND 80000;
SELECT department, salary FROM clustered_table WHERE department = 'Dev' AND salary BETWEEN 50000 AND 80000;

FLUSH TABLES;

EXPLAIN ANALYZE
SELECT department, salary FROM non_clustered_table
WHERE department = 'Dev' AND salary BETWEEN 50000 AND 80000;

SELECT department, salary FROM non_clustered_table WHERE department = 'Dev' AND salary BETWEEN 50000 AND 80000;
SELECT department, salary FROM non_clustered_table WHERE department = 'Dev' AND salary BETWEEN 50000 AND 80000;
SELECT department, salary FROM non_clustered_table WHERE department = 'Dev' AND salary BETWEEN 50000 AND 80000;

SHOW PROFILES;

 

가설 3: group by가 pk 선두 키(age) 기준

ALTER TABLE non_clustered_table DROP INDEX idx_age;

FLUSH TABLES;

EXPLAIN ANALYZE
SELECT age, COUNT(*) FROM clustered_table
GROUP BY age;

SELECT age, COUNT(*) FROM clustered_table GROUP BY age;
SELECT age, COUNT(*) FROM clustered_table GROUP BY age;
SELECT age, COUNT(*) FROM clustered_table GROUP BY age;

FLUSH TABLES;

EXPLAIN ANALYZE
SELECT age, COUNT(*) FROM non_clustered_table
GROUP BY age;

SELECT age, COUNT(*) FROM non_clustered_table GROUP BY age;
SELECT age, COUNT(*) FROM non_clustered_table GROUP BY age;
SELECT age, COUNT(*) FROM non_clustered_table GROUP BY age;

SHOW PROFILES;

 

가설 4. group by가 세컨더리 인덱스 컬럼(department, salary) 기준

FLUSH TABLES;

EXPLAIN ANALYZE
SELECT department, AVG(salary) FROM clustered_table
GROUP BY department;

SELECT department, AVG(salary) FROM clustered_table GROUP BY department;
SELECT department, AVG(salary) FROM clustered_table GROUP BY department;
SELECT department, AVG(salary) FROM clustered_table GROUP BY department;

FLUSH TABLES;

EXPLAIN ANALYZE
SELECT department, AVG(salary) FROM non_clustered_table
GROUP BY department;

SELECT department, AVG(salary) FROM non_clustered_table GROUP BY department;
SELECT department, AVG(salary) FROM non_clustered_table GROUP BY department;
SELECT department, AVG(salary) FROM non_clustered_table GROUP BY department;

SHOW PROFILES;

---

결과 분석

 

가설 1. 조회 기준 컬럼이 PK 선두 키(age)인 경우, 범위 조회는 A 테이블이 빠를 것이다.

항목	A 테이블	B 테이블
접근 방식	Index range scan using PRIMARY	Index range scan using idx_age
옵티마이저의 예상 cost	15,945	17,080
실제 반환 행	12,777	14,920
actual time	0.1 ~ 4.92 ms	1.56 ~ 69.3 ms
이중 탐색	❌ 없음	✅ idx_age → pk_uuid 재탐색 발생
한 row당 처리 시간	0.00085 ms/행	0.00307 ms/행

=> A 테이블이 3.6배 빠름

*참고: actual time의 앞 숫자는 첫 행을 찾는 시간, 뒷 숫자는 전체를 다 처리하는 시간

 

A table은 PK 자체가 age 기준으로 정렬되어 있어서, 범위 스캔 시 리프 노드에 실제 데이터가 바로 있음

B able은 idx_age로 age 범위를 찾은 뒤, 각 행마다 UUID pk로 클러스터드 인덱스를 재탐색 필요

---

가설 2. 복합 세컨더리 인덱스가 커버링 인덱스로 작동하면, SELECT *이 아닌 인덱스 포함 컬럼만 조회 시 B 테이블이 빠를 것이다.

항목	A 테이블	B 테이블
접근 방식	Table scan (풀스캔)	Covering index range scan
옵티마이저의 예상 cost	26,726	9,360
실제 스캔 행	276,513행 (전체 풀스캔)	24,437행 (인덱스만 스캔)
실제 반환 행	23,713	24,437
actual time	0.173 ~ 89 ms	0.094 ~ 11.7 ms
평균 실행 시간	78ms	17ms
이중 탐색	없음 (풀스캔)	❌ 없음 (커버링 인덱스 작동)

=> 가설과 일치

---

가설 3. GROUP BY 기준이 PK 선두 키(age)이면 A 테이블이 빠를 것이다.

항목	A 테이블	B 테이블
접근 방식	Covering index scan using PRIMARY	Table scan + Aggregate using temporary table
옵티마이저의 예상 cost	84,170	36,776
실제 스캔 행	408,414	417,715
actual time	3.3 ~ 122 ms	0.274 ~ 253 ms
정렬 방식	filesort 없음 (PK 순서 그대로)	임시 테이블 생성 후 집계
평균 실행 시간	82ms	190ms

 

=> 가설과 일치

*특이사항: B 테이블 반복 실행 시 속도가 점점 빨라짐 -> 버퍼 풀에 페이지가 캐싱되면서 풀스캔 속도가 빨라진 것으로 추정

---

가설 4. GROUP BY 기준이 세컨더리 인덱스 컬럼(department, salary)이면, 커버링 인덱스가 작동하는 B 테이블이 빠를 것이다.

항목	A 테이블	B 테이블
접근 방식	Table scan + Aggregate using temporary table	Covering index scan using idx_dept_salary
옵티마이저의 예상 cost	42,982	85,093
실제 스캔 행	475,607	484,497
actual time	0.275 ~ 470 ms	0.087 ~ 245 ms
임시 테이블	생성됨	없음
커버링 인덱스	❌	idx_dept_salary
평균 실행 시간	390ms	219ms

=> 가설과 일치

 

*특이사항: 옵티마이저의 예상 cost와 실제 결과가 반대 -> 임시 테이블 생성 비용이 과소평가됨

=> 옵티마이저의 예상 cost가 항상 일치하는 건 아님