TIL: SQL — Index

4 min readJul 7, 2021

Index는 RDBMS에서 SELECT 속도를 높이기 위해 쓰이는 기술이다.

SELECT *
FROM `sample_staff`.`salary`
WHERE 1=1
  AND `salary`.`salary_amount` > 190000
  AND `salary`.`from_date` >= '1998-01-01'
LIMIT 100
;

간단한 SELECT 문이다. WHERE 조건문으로 ‘salary’ 테이블에서 ‘salary_amount’가 190,000보다 크고 ‘from_date’가 ‘1998 01 01’ 이후인 row들을 조회한다. Row count가 총 2,703,223개 있는 ‘salary’ 테이블에서 쿼리를 돌리면 2.7초가 걸린다.

ALTER TABLE `salary` ADD INDEX `idx_salary_amount`(`salary_amount`);

위에 쿼리를 실행하면 ‘salary’ 테이블에서 ‘salary_amount’로 정렬된 ‘idx_salary_amount’ 인덱스가 추가된다. 그리고 제일 처음 SELECT 쿼리를 실행하면 2ms가 걸린다. Index가 없을 때와의 엄청난 차이다.

EXPLAIN SELECT *
FROM `sample_staff`.`salary`
WHERE 1=1
  AND `salary`.`salary_amount` > 190000
  AND `salary`.`from_date` >= '1998-01-01'
LIMIT 100
;

SELECT 문 앞에 EXPLAIN을 붙여서 실행하면 해당 SELECT 문이 어떻게 실행되는지 확인할 수 있다.

살펴보면 좀 전에 생성한 ‘idx_salary_amount’를 사용하여 SELECT 문을 실행하는 것을 확인할 수 있다.

Index 단점
Index를 생성하면 추가적인 저장공간이 필요하고 DELETE, INSERT, UPDATE 작업이 있을경우 성능이 떨어진다.

Unique Index

Index 중에 중복된 값을 허용하지 않도록 설정한 것을 Unique Index라 한다. Unique Index 생성 방법은 다음과 같다.

ALTER TABLE `department` ADD UNIQUE INDEX `ak_department` (`name`);

위 쿼리는 ‘department’ 테이블에 ‘name’ 컬럼 기준으로 중복된 값을 허용하지 못하도록 ‘ak_department’ (ak는 alternative key의 약자이다.) 이름을 가진 unique index를 생성한다.

Partial Index

Partial Index는 하나의 속성의 부분만 index 하는 것을 뜻한다.

ALTER TABLE `phone_book` ADD INDEX (last_name(4))

위에 쿼리는 ‘phone_book’ 테이블에서 ‘last_name’ 컬럼의 처음 네 글자를 묶어서 Index를 생성한다.

SELECT * FROM `phone_book` WHERE `last_name` = 'Smith'

위 쿼리를 실행하면 ‘Smit’으로 묶인 index를 조회하여 조건에 맞는 row를 찾는다.

Index Hints (Use, Force, Ignore)

SELECT를 실행할 때 특정 index를 use(사용), force(강제 사용), 또는 ignore(무시) 할 수 있다.

SELECT *
FROM `sample_staff`.`salary` IGNORE INDEX (`ak_salary`) IGNORE INDEX (`idx_employee_id`)
WHERE 1=1
  AND `salary`.`employee_id` = 499997
  AND `salary`.`from_date` = '1993-12-27'
  AND `salary`.`to_date` = '1994-12-27'
;

위 쿼리는 ‘ak_salary’와 ‘idx_employee_id’를 IGNORE INDEX를 통해 무시되고 SELECT를 실행한다.

SELECT *
FROM `sample_staff`.`salary` USE INDEX (`ak_salary`)
WHERE 1=1
  AND `salary`.`employee_id` = 499998
  AND `salary`.`from_date` = '1993-12-27'
  AND `salary`.`to_date` = '1994-12-27'
;

위 쿼리는 USE INDEX로 ‘ak_salary’라는 특정한 index를 정의하여 SELECT를 실행한다.

TIL: SQL — Index

Unique Index

Partial Index

Index Hints (Use, Force, Ignore)

Written by Gibson Lee