인덱스란 무엇인가?
인덱스란 아래 그림처럼 책의 목차라고 보면된다.
책에서 어떤 부분을 보고 싶을 때 목차가 없으면 어떠한가?
책의 모든 부분을 펼쳐보면서 내가 원하는 부분을 찾아야한다.
그런데 만약 목차가 있다면 목차만 보고 내가 보기 원하는 부분을 빠르게 찾을 수 있을 것이다.
데이터베이스 돌아와서 이야기하면 테이블은 책이고, 인덱스는 책의 목차인 셈이다.
그럼 인덱스를 무조건 생성하는 것이 좋은가?
결론 부터 말하자면 '아니오'이다.
인덱스는 물리적 디스크에 저장이 되기 때문에 컴퓨터의 용량을 차지한다.
또한 '삽입/수정/삭제'시에 인덱스를 생성해야 하기에 생성하지 않는 것보다 느려진다.
결국 인덱스는 삽입 / 수정 / 삭제에서 성능감수를 하고 조회에서 높은속도로 데이터를 조회하기 위한
일종의 '트레이드오프' 관계인 셈이다.
그럼 어떤 경우에 인덱스를 생성해야 하는가?
일단 결론부터 말하면 대량 검색 / 정렬이 많은 컬럼 그리고 분포도가 '좋고/넓은' 컬럼에 인덱스를 걸어야 한다.
분포도가 '좋은/넓은' 컬럼이 대상이 되어야 한다. 분포도가 '나쁘고/좁은' 컬럼을 선택하게 되면 오히려 독이 되기도 한다.
예를 들어보자
총 100명의 사람 중에서 만나야 할 한 사람을 찾아야한다.
"그는 남자 입니다" => 분포도가 나쁘기 때문에 필터링된 데이터가 많다. 성별은 좋은 컬럼이 아니다.
"그는 백인입니다" => 마찬가지로 인종도 좋은 컬럼이 아니다.
"그의 주민등록번호는 XXXXXXXXXXX 입니다" => 아주 좋은 분포도를 가지는 컬럼이다.
요약하자면 분포도가 좋다는 것은 다수의 데이터중에서 유일한 값이 많다라는 것이고
분포도가 나쁘다는 것은 중복값이 많다는 것이다.
인덱스를 타지 않는 경우
인덱스 컬럼절을 변형한 경우
/* 인덱스를 타지 않는 예 */
SELECT column_name FROM table_name WHERE TO_CHAR(column_name, 'YYYYMMDD') = '20130909';
/* 인덱스를 타는 예 */
SELECT column_name FROM table_name WHERE column_name = TO_DATE('20130909', 'YYYYMMDD');내부적으로 데이터 형 변환이 일어난 경우
/* 인덱스를 타지 않는 예 DATE 타입의 column */
SELECT column_name FROM table_name WHERE column_name = '20130909';
/* 인덱스를 타는 예 */
SELECT column_name FROM table_name WHERE column_name = TO_DATE('20130909', 'YYYYMMDD');조건절에 NULL 또는 NOT NULL을 사용하는 경우
/* 인덱스를 타지 않는 예 */
SELECT column_name FROM table_name WHERE column_name IS NULL;
SELECT column_name FROM table_name WHERE column_name IS NOT NULL;
/* 인덱스를 타는 예 */
SELECT column_name FROM table_name WHERE column_name > '';
SELECT column_name FROM table_name WHERE column_name >= 0;부정형으로 조건을 사용한 경우
/* 인덱스를 타지 않는 예 */
SELECT column_name FROM table_name WHERE column_name != 30;
/* 인덱스를 타는 예 */
SELECT column_name FROM table_name WHERE column_name < 30 AND column_name > 30;
SELECT column_name FROM table_name WHERE NOT EXISTS (SELECT column_name FROM table_name WHERE column_name = 30);LIKE 연산자를 사용하는 경우
/* 인덱스를 타지 않는 예 */
SELECT column_name FROM table_name WHERE column_name LIKE '%S%';
SELECT column_name FROM table_name WHERE column_name LIKE '%S';
/* 인덱스를 타는 예 */
SELECT column_name FROM table_name WHERE column_name LIKE 'S%';OR 조건을 사용하는 경우
/* 인덱스를 타지 않는 예 */
SELECT * FROM table_name WHERE column_name = 'yunseop' or name = 'song';
/* 인덱스를 타는 예 */
SELECT * FROM table_name WHERE column_name = 'yunseop'
UNION ALL
SELECT * FROM table_name WHERE column_name = 'song';복합인덱스의 순서를 정확하게 사용하지 않은 경우
/*
복합인덱스 에서는 인덱스의 순서가 중요하다.
ALTER TABLE users ADD INDEX idx_test ( age, name );
*/
/* idx_test 인덱스를 타지 않는 예 */
SELECT * FROM users WHERE name = 'hong' AND age = 30
/* idx_test 인덱스를 타는 예 */
SELECT * FROM users WHERE age = 30 AND name = 'hong'과도하게 In 구문에 조건이 많은 경우 ( 조건부)
/*
Mysql에서는 range_optimizer_max_mem_size 설정의 용량을
In절의 파라미터가 넘어서면 FullScan을 한다.
다른 DB의 경우도 파라미터가 많아지면 옵티마이저가 FullScan을 할 수 있다.
*/
/* 인덱스를 타지 않는 예 */
SELECT * FROM users WHERE name in ('a', 'b' ...... N개)
/* 인덱스를 타는 예 */
SELECT * FROM users WHERE in ('a', 'b')