개요

인덱스 수업 들었어용

---

1. 인덱스 기본 개념

• PRIMARY KEY, FOREIGN KEY, UNIQUE 제약조건이 설정되면
  해당 컬럼에 자동으로 인덱스가 생성된다.
• 그 외에도 자주 조회되는 컬럼에 대해 별도로 인덱스를 추가할 수 있다.
• 인덱스는 테이블 전체를 순회하는 대신, B-Tree 등의 자료구조를 이용해
  원하는 데이터를 빠르게 찾을 수 있도록 돕는다.

---

2. 인덱스 조회 및 삭제

-- 테이블의 인덱스 목록 조회
SHOW INDEX FROM [테이블명];

-- 인덱스 삭제 (기본 PK 인덱스는 삭제 불가/권장하지 않음)
ALTER TABLE [테이블명]
DROP INDEX [인덱스명];

---

3. 인덱스 생성

-- 단일 컬럼 인덱스 생성
CREATE INDEX [인덱스명]
ON [테이블명] ([컬럼명]);

-- 예) author 테이블의 name 컬럼에 인덱스 생성
CREATE INDEX name_index ON author(name);


-- 복합 인덱스 생성 (여러 컬럼 동시 지정)
CREATE INDEX [인덱스명]
ON [테이블명] ([컬럼1], [컬럼2]);

-- 예) name, age 컬럼 복합 인덱스 생성
CREATE INDEX name_age_index ON author(name, age);

• 복합 인덱스는 여러 컬럼을 한 번에 인덱싱하므로,
  쿼리 조건에 두 컬럼 모두 포함되어야 효과적이다.

---

4. 인덱스 성능 테스트 예시

-- 인덱스 없는 테이블 생성
CREATE TABLE author_no_index (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255),
    name VARCHAR(255)
);

-- 인덱스 있는 테이블 생성 (email에 UNIQUE 인덱스)
CREATE TABLE author_with_index (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    name VARCHAR(255)
);

709ms → 340ms 시간 단축 확인

• 수십만 건의 데이터를 넣고 동일 쿼리 실행 시,
  인덱스 있는 경우 실행 시간이 대폭 줄어든다(기하급수적 단축! ↔ 산술급수적)

DELIMITER //

CREATE PROCEDURE insert_authors()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE email VARCHAR(100);
    DECLARE batch_size INT DEFAULT 10000; -- 한번에 삽입할 행 수
    DECLARE max_iterations INT DEFAULT 100;  -- 전체 반복횟수 (ex. 100만 건)
    DECLARE iteration INT DEFAULT 1;

    WHILE iteration <= max_iterations DO
        START TRANSACTION;

        WHILE i <= iteration * batch_size DO
            SET email = CONCAT('bradkim', i, '@naver.com');
            INSERT INTO author (email) VALUES (email);
            SET i = i + 1;
        END WHILE;

        COMMIT;

        SET iteration = iteration + 1;

        -- 각 트랜잭션 후 0.1초 지연 (부하 완화)
        DO SLEEP(0.1);
    END WHILE;
END //

DELIMITER ;

• 위 코드로 대용량 데이터 삽입 후 인덱스를 통한 조회의 성능을 확인하였다 (SQL에서는 반복문 사용이 안되기 때문에 프로지서를 활용하여 반복문을 수행한 것)

---

인덱스 요약

• 인덱스는 검색 속도를 획기적으로 개선하므로 필수적이다.
• 기본 키(PK), 외래 키(FK), 유니크 제약조건 생성 시 자동으로 인덱스가 만들어진다.
• 자주 조회하는 컬럼에는 별도 인덱스 생성 권장.
• 복합 인덱스는 여러 관련 컬럼에 대해 생성 가능하지만,
  쿼리 내 조건에 모두 포함돼야 효과적이다.
• 대용량 데이터 삽입 후 인덱스 생성 및 성능 테스트가 매우 중요하다.

---

마치며

프로시저를 사용하여 큰 데이터를 사용하여 진행해보니 체감되는 듯
index 활용을 잘 해봐야겠다!