INDEX

INDEX란?

• 데이터를 빠르게 조회하기 위한 Key 개념
• WHERE 절에서 자주 조회되는 컬럼을 index로 등록하면 조회가 빨라진다.
• 특정 컬럼 또는 여러 컬럼을 묶어 INDEX로 등록할 수 있다.
  • 여러 컬럼을 묶은 INDEX: 복합 INDEX 또는 복합키
• 데이터가 insert 될 때, 인덱스 데이터도 따로 저장 된다.(인덱스가 너무 많으면 입력 속도 느려지고 용량 차지)

---

InnoDB의 인덱스 구조
MySQL의 스토리지 엔진인 InnoDB의 경우 B+Tree 자료구조로 구성되어 있다.(B-Tree의 확장 개념)

• 균형 트리
• 복잡도: O(LogN)

---

INDEX의 종류

---

인덱스는 언제 사용하는가?

• WHERE절에서 조회 조건이 빈번히 일어날 때
• NULL 값이 많은 데이터들 중 NULL이 아닌 데이터를 찾을 때 유용
• 특정 컬럼을 기준으로 데이터를 정렬할 때 index를 사용하는 것이 좋다.

---

INDEX 따로 추가하기

-- UNIQUE KEY 추가: studentId 컬럼에는 중복값이 들어갈 수 없다.
ALTER TABLE `student` ADD UNIQUE INDEX `index_studentId` (`studentId`) USING BTREE;

-- 일반 인덱스 추가
ALTER TABLE `student` ADD INDEX `idx_department` (`department`);

-- 일반 복합 인덱스 추가
ALTER TABLE `student` ADD INDEX `idx_department_address` (`department`, `address`);

---

INDEX 삭제하기

ALTER TABLE 테이블명 DROP INDEX 인덱스명

---

실행 계획(성능) 보기
MySQL의 옵티마이저가 어떻게 판단해서 쿼리를 실행하는지 정보를 본다.

• 인덱스가 여러개인 경우 하나만 선택된다.

explain 쿼리문;

-- index가 없는 컬럼 like: 인덱스가 없으므로 full scan
explain select * from customer where first_name like 'A%';
explain select * from customer where first_name like '%A';
explain select * from customer where first_name like '%A%';

-- index가 있는 컬럼 like
explain select * from customer where last_name like 'A%'; -- range scan
explain select * from customer where last_name like '%A'; -- full scan
explain select * from customer where last_name like '%A%'; -- full scan

-- pk로 값 조회: const
explain select * from customer where customer_id = 3;

-- index 없는 컬럼 값 조회: full scan
explain select * from film where length = 100;

-- index로 값 조회: ref
explain select * from customer where store_id = 2;


-- pk로 범위 조회
explain select * from film where film_id < 10; -- range
explain select * from film where film_id < 1000; -- range

-- index가 없는 컬럼 범위 조회
explain select * from film where length < 50; -- full scan 

-- length 컬럼 index 추가하기
alter table film add index `idx_length` (`length`);

-- index로 범위 조회
explain select * from film where length < 50; -- range
explain select * from film where length < 100; -- full scan --> 데이터 수가 적기 때문에 옵티마이저가 풀스캔이 낫다고 판단

-- index 삭제하기
alter table film drop index `idx_length`;

---

인덱스 사용시 주의할 점

• 인덱스가 여러개일 경우, 의도한 인덱스를 타지 않을 수 있으므로 실행 계획을 확인할 것
• 카디널리티(Cardinality)가 낮으면 사용하지 않는다.(중복도가 높은 경우)
  • 예) 성별 컬럼 - 남성이 80% 여성이 20%인 경우 INDEX 효과 없음
  • 예) 과목 컬럼 - 국어, 영어, 수학. 세 과목만 존재하는데 데이터가 많은 경우
• 컬럼의 값의 갱신이 자주 일어나는 경우 사용하지 않는다.
• WHERE 구문과 ORDER BY에서의 컬럼이 다른 경우 한쪽 index가 선택되므로 실행 계획 확인할 것