인덱스의 부작용 (과도한 생성이 불리한 이유)

1. 데이터 삽입 성능 저하

-- 6개의 인덱스를 가진 테이블에 데이터 삽입 시나리오:
INSERT INTO customer_info (full_name, birth_year, residence_city, contact_number, registration_email) 
VALUES ('이민수', 1988, '서울', '010-5555-1234', 'leemin@example.com');

-- 실제 수행되는 작업:
기본 테이블에 데이터 저장: 1회
인덱스_1 업데이트: 1회
인덱스_2 업데이트: 1회
인덱스_3 업데이트: 1회
인덱스_4 업데이트: 1회
인덱스_5 업데이트: 1회
인덱스_6 업데이트: 1회

총계: 7번의 쓰기 작업! 인덱스가 하나 추가될 때마다 쓰기 작업이 증가한다.

2. 갱신 및 삭제 속도 감소

UPDATE customer_info SET residence_city = '부산' WHERE record_id = 200;
-- residence_city 필드에 인덱스가 있는 경우: 1.기존 인덱스 제거 2.새 인덱스 삽입
-- 여러 인덱스가 관련된 경우 각각의 인덱스를 갱신해야 함

인덱스 비용 분석

저장 공간 소비

-- 인덱스 공간 점유량 확인 쿼리
SELECT 
    table_name,
    index_name,
    ROUND(index_length/1024/1024, 2) AS '인덱스_크기(MB)',
    ROUND(data_length/1024/1024, 2) AS '데이터_크기(MB)',
    ROUND(index_length/data_length, 2) AS '인덱스_대비_데이터_비율'
FROM information_schema.TABLES 
WHERE table_schema = 'target_database';

-- 일반적인 사례:
-- 실제 데이터: 150MB
-- 단일 인덱스: 30MB
-- 다중 인덱스(7개): 210MB (원본 데이터보다 커짐!)

성능 대비 분석

과도한 인덱스로 인한 문제점

1. 쿼리 최적화기 혼란

-- 12개의 인덱스를 보유한 테이블에서 쿼리 실행:
SELECT * FROM customer_info WHERE age_range > 25 AND location = '서울';

-- 최적화기가 고려해야 할 후보:
-- idx_age (age_range 기반)
-- idx_location (location 기반)  
-- idx_age_location (age_range, location 조합)
-- idx_location_age (location, age_range 조합)
-- 기타 조합들...

-- 선택 프로세스 자체가 CPU 자원을 소모하며 잘못된 인덱스 선택 가능!

2. 중복 및 불필요한 인덱스

-- 일반적인 중복 패턴:
CREATE INDEX idx_field_x ON customer_info(field_x);
CREATE INDEX idx_field_x_y ON customer_info(field_x, field_y);  -- idx_field_x는 중복!

-- 불필요한 인덱스 조합:
CREATE INDEX idx_y_x ON customer_info(field_y, field_x);  -- idx_field_x_y와 순서만 다름
CREATE INDEX idx_x_y_z ON customer_info(field_x, field_y, field_z);  -- idx_field_x_y의 기능 포함

3. 메모리 리소스 낭비

MySQL 버퍼 풀은 제한된 용량을 가짐 (예: 6GB)

최적 상황:
데이터 캐시: 4.5GB
인덱스 캐시: 1.5GB
쿼리 속도: 빠름

인덱스 과잉 상황:
데이터 캐시: 2.5GB  
인덱스 캐시: 3.5GB (사용 빈도가 낮은 인덱스들이 메모리를 차지)
쿼리 속도: 디스크 I/O 증가로 인해 느려짐!

인덱스 과잉 여부 판단 방법

상태 지표 검사

-- 1. 인덱스와 데이터 비율
-- 건강 상태: 인덱스 크기 < 데이터 크기
-- 위험 상태: 인덱스 크기 > 데이터 크기

-- 2. 인덱스 개수 기준
-- 소규모 테이블(1만 건 미만): 2-4개 인덱스
-- 중규모 테이블(1만-500만 건): 4-7개 인덱스  
-- 대규모 테이블(500만 건 초과): 7-10개 인덱스 (엄격한 평가 필요)

-- 3. 인덱스 활용률 분석
SELECT 
    schema_name,
    table_name,
    index_name,
    read_count,
    insert_count,
    update_count,
    delete_count
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY read_count DESC;

-- read_count 값이 낮다면 인덱스 사용 빈도가 낮음을 의미

미사용 인덱스 식별

-- MySQL 8.0 이상에서 미사용 인덱스 조회
SELECT * FROM sys.schema_unused_indexes;

-- 모든 버전에서 사용 가능한 수동 분석
SELECT 
    index_info.index_name,
    index_info.table_name,
    index_info.read_operations,
    index_info.insert_operations,
    index_info.update_operations,
    index_info.delete_operations,
    CASE 
        WHEN index_info.read_operations < 800 THEN '삭제 고려'
        WHEN index_info.read_operations < 8000 THEN '관찰 필요'
        ELSE '유지'
    END AS action_recommendation
FROM (
    SELECT 
        OBJECT_NAME AS table_name,
        INDEX_NAME AS index_name,
        COUNT_READ AS read_operations,
        COUNT_INSERT AS insert_operations,
        COUNT_UPDATE AS update_operations,
        COUNT_DELETE AS delete_operations
    FROM performance_schema.table_io_waits_summary_by_index_usage
    WHERE OBJECT_SCHEMA = DATABASE()
) index_info
WHERE read_operations = 0 
   OR (read_operations < 80 AND update_operations > 800);

인덱스 최적 수 전략

업무 유형별 전략

OLTP 시스템(온라인 거래, 빈번한 쓰기):
  ✅ 인덱스는 적고 효율적으로 구성(2-7개)
  ✅ 고빈도 쿼리에만 인덱스 생성
  ✅ 미사용 인덱스 주기적 정리

OLAP 시스템(분석 리포트, 빈번한 읽기):
  ✅ 인덱스 수를 약간 더 허용(7-12개)
  ✅ 다양한 쿼리 패턴 지원
  ✅ 유지 관리 비용 고려 필요

인덱스 우선순위 설정

-- 중요도 기준 인덱스 생성 순서:
1. 기본키 인덱스 (필수)            -- ⭐⭐⭐⭐⭐
2. 고유 제약 인덱스                -- ⭐⭐⭐⭐⭐
3. 고빈도 WHERE 조건 필드          -- ⭐⭐⭐⭐
4. 고빈도 JOIN 조건 필드           -- ⭐⭐⭐⭐  
5. 고빈도 ORDER BY/GROUP BY 필드   -- ⭐⭐⭐
6. 커버링 인덱스 (테이블 재방문 방지) -- ⭐⭐⭐
7. 전문 검색 인덱스 (텍스트 검색)   -- ⭐⭐
8. 낮은 선택성 필드 인덱스 (성별 등) -- ⭐ (대체로 불필요)

실전 사례: 쇼핑몰 시스템 인덱스 최적화

상품 테이블 최적화 전

-- 18개의 인덱스 보유!
CREATE TABLE product_catalog (
    item_id INT PRIMARY KEY,
    product_title VARCHAR(250),
    category_code INT,
    unit_cost DECIMAL(12,2),
    inventory_count INT,
    item_state TINYINT,
    creation_timestamp DATETIME,
    modification_timestamp DATETIME,
    -- 단일 필드 인덱스들
    INDEX idx_title (product_title),
    INDEX idx_category (category_code),
    INDEX idx_cost (unit_cost),
    INDEX idx_inventory (inventory_count),
    INDEX idx_state (item_state),
    INDEX idx_creation (creation_timestamp),
    INDEX idx_modification (modification_timestamp),
    -- 복합 인덱스들
    INDEX idx_cat_cost (category_code, unit_cost),
    INDEX idx_cat_state (category_code, item_state),
    INDEX idx_cost_state (unit_cost, item_state),
    -- 중복 인덱스들...
    INDEX idx_title_partial (product_title(25)),  -- idx_title과 중복
    INDEX idx_cat_cost_inventory (category_code, unit_cost, inventory_count),
    INDEX idx_cost_cat_state (unit_cost, category_code, item_state)
);

문제점: 쓰기 속도 저하, 유지보수 비용 증가, 메모리 과다 사용.

최적화 후

-- 핵심 인덱스 5개로 축소
CREATE TABLE product_catalog (
    item_id INT PRIMARY KEY,
    product_title VARCHAR(250),
    category_code INT,
    unit_cost DECIMAL(12,2),
    inventory_count INT,
    item_state TINYINT,
    creation_timestamp DATETIME,
    modification_timestamp DATETIME,
    -- 1. 고빈도 쿼리: 카테고리+가격 정렬
    INDEX idx_cat_cost_state (category_code, unit_cost, item_state),
    -- 2. 고빈도 쿼리: 상태+생성시간
    INDEX idx_state_creation (item_state, creation_timestamp),
    -- 3. 제목 검색 (접두사 인덱스)
    INDEX idx_title (product_title(60)),
    -- 4. 재고 알림
    INDEX idx_inventory_state (inventory_count, item_state),
    -- 5. 시간 범위 검색
    INDEX idx_creation_time (creation_timestamp)
);

결과: 쓰기 속도 35% 향상, 메모리 사용량 55% 감소, 조회 성능 유지.

인덱스 관리 모범 사례

1. 정기 감사 실시

-- 월 1회 실행
-- 인덱스 사용 현황 확인
SELECT * FROM sys.schema_unused_indexes;

-- 중복 인덱스 탐색
SELECT 
    first_stat.table_name,
    first_stat.index_name AS primary_idx,
    second_stat.index_name AS duplicate_idx,
    first_stat.column_name
FROM information_schema.statistics first_stat
JOIN information_schema.statistics second_stat 
    ON first_stat.table_schema = second_stat.table_schema
    AND first_stat.table_name = second_stat.table_name
    AND first_stat.column_name = second_stat.column_name
    AND first_stat.seq_in_index = second_stat.seq_in_index
    AND first_stat.index_name != second_stat.index_name
WHERE first_stat.table_schema = DATABASE();

2. 비가시 인덱스를 통한 테스트

-- MySQL 8.0+ 기능
-- 먼저 인덱스를 비가시 상태로 변경하여 영향 관찰
ALTER TABLE customer_info ALTER INDEX idx_evaluation INVISIBLE;

-- 일정 기간 운영 후
-- 영향이 없다면 최종 삭제
DROP INDEX idx_evaluation ON customer_info;

3. 쓰기 성능 모니터링

-- 인덱스가 쓰기 성능에 미치는 영향 확인
SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted';
SHOW GLOBAL STATUS LIKE 'Innodb_rows_updated';
SHOW GLOBAL STATUS LIKE 'Innodb_rows_deleted';

-- 인덱스 유지 관리 비용 계산
-- 쓰기 성능 저하가 발생하면 인덱스 수 축소 고려

핵심 원칙

인덱스 생성 체크리스트

• 이 쿼리는 하루에 몇 번 실행되는가? (100회 이상 시 고려)
• 이 인덱스로 여러 쿼리를 처리할 수 있는가?
• 테이블의 주요 작업은 읽기인가 쓰기인가?
• 인덱스 대상 필드의 선택성이 높은가? (10% 이상)
• 유사한 인덱스가 이미 존재하는가? (중복 방지)
• 인덱스가 얼마나 많은 공간을 차지하는가?
• 유지 관리 비용이 수용 가능한가?

간단한 의사결정 흐름

신규 쿼리에 인덱스 필요?
    ↓
하루 100회 이상 실행? → 아니오 → 인덱스 불필요
    ↓예
유사 인덱스 존재? → 예 → 기존 인덱스 재사용
    ↓아니오  
선택성 > 10%? → 아니오 → 인덱스 필요 없음 가능성
    ↓예
테이블 쓰기 빈도 높음? → 예 → 신중한 평가 필요
    ↓아니오
인덱스 생성 후 성능 모니터링

인덱스 수의 균형점 요약

기억할 점:

• 🔹 모든 인덱스는 부채 (유지 관리 비용 발생)
• 🔹 고빈도 쿼리에만 인덱스 생성 가치 있음
• 🔹 정기 정리는 청소와 같음
• 🔹 질이 양보다 중요 (설계가 잘 된 복합 인덱스는 단일 인덱스 3개보다 우수)

최종 권장사항: 핵심 쿼리부터 시작하여 필요에 따라 생성하고, 주기적으로 평가하여 간소화를 유지하라!