09. INDEX

 

💡 인덱스(Index)는 데이터 검색 속도를 향상시키는 데이터 구조로 데이터를 빠르게 조회할 수 있는 포인터를 제공한다. 데이터베이스에서 데이터를 찾을 때 전체 테이블을 검색하는 대신 인덱스를 통해 검색을 하므로 속도가 더 빨라진다. 인덱스는 주로 WHERE절의 조건이나 JOIN 연산에 사용되는 컬럼에 생성한다. 다만 인덱스도 데이터 저장 공간을 차지하고 데이터가 변경될 때마다 인덱스 역시 갱신해야 하기 때문에 어떤 컬럼에 인덱스를 생성할지는 신중히 결정해야 한다.

 

1. 인덱스 생성

[인덱스 생성]

# PRIMARY KEY 제약 조건을 지닌 기본 테이블 생성 후 기본 데이터 INSERT 후 조회
CREATE TABLE phone (
    phone_code INT PRIMARY KEY,
    phone_name VARCHAR(100),
    phone_price DECIMAL(10, 2)
);

INSERT INTO phone (phone_code , phone_name , phone_price )
VALUES (1, 'galaxyS23', 1200000),
       (2, 'iPhone14pro', 1433000),
       (3, 'galaxyZfold3', 1730000);

SELECT * FROM phone;

# 인덱스가 없는 컬럼을 WHERE절의 조건으로 활용한 조회 진행 시 EXPLAIN으로 쿼리 실행 계획 확인
EXPLAIN SELECT * FROM phone WHERE phone_name = 'galaxyS23';

# phone_name 컬럼에 idx_name이라는 이름의 인덱스 생성 후 확인
CREATE INDEX idx_name 
ON phone (phone_name);

 

[복합 인덱스 생성] : 2개 이상의  컬럼을 한 번에 하나의 인덱스로 설정해서 생성

CREATE INDEX idx_name_price
ON phone (phone_name, phone_price);

SHOW INDEX FROM phone;

 

2. 인덱스 활용

[인덱스 조회]

# 테이블에 인덱스가 설정 된 컬럼을 활용해 조회를 진행
SELECT * FROM phone WHERE phone_name = 'iPhone14pro';

# EXPLAIN 명령어로 쿼리 실행 계획 확인(인덱스 사용 여부 및 사용 컬럼 확인)
EXPLAIN SELECT * FROM phone WHERE phone_name = 'iPhone14pro';

 

[인덱스 최적화(재구성)]

• 인덱스 최적화(재구성)은 인덱스가 파편화 되었거나, 데이터의 대부분이 변경된 경우에 유용하다.
• 이는 인덱스의 성능을 개선하고, 디스크 공간을 더 효율적으로 사용하게 해준다.
• 단, 인덱스를 재구성하는 동안 해당 테이블은 잠길 수 있으므로, 이 작업은 주의해서 수행해야 한다.
• 'ALTER TABLE' 명령어를 사용해서 재구성한다.

# 인덱스가 파편화 되었거나, 데이터의 대부분이 변경된 경우
ALTER TABLE phone DROP INDEX idx_name;
ALTER TABLE phone ADD INDEX idx_name(phone_name);

# MySQL의 경우 OPTIMIZE TABLE 명령을 통해 테이블과 인덱스 최적화가 가능하다
OPTIMIZE TABLE phone;

 

3. 인덱스 삭제

# 인덱스 삭제 후 테이블에 존재하는 인덱스 확인
DROP INDEX idx_name ON phone;
SHOW INDEX FROM phone;