인덱스란 무엇인가?
인덱스란 아래 그림처럼 책의 목차라고 보면된다.
책에서 어떤 부분을 보고 싶을 때 목차가 없으면 어떠한가?
책의 모든 부분을 펼쳐보면서 내가 원하는 부분을 찾아야한다.
그런데 만약 목차가 있다면 목차만 보고 내가 보기 원하는 부분을 빠르게 찾을 수 있을 것이다.
데이터베이스 돌아와서 이야기하면 테이블은 책이고, 인덱스는 책의 목차인 셈이다.
그럼 인덱스를 무조건 생성하는 것이 좋은가?
결론 부터 말하자면 '아니오'이다.
인덱스는 물리적 디스크에 저장이 되기 때문에 컴퓨터의 용량을 차지한다.
또한 '삽입/수정/삭제'시에 인덱스를 생성해야 하기에 생성하지 않는 것보다 느려진다.
결국 인덱스는 삽입 / 수정 / 삭제에서 성능감수를 하고 조회에서 높은속도로 데이터를 조회하기 위한
일종의 '트레이드오프' 관계인 셈이다.
그럼 어떤 경우에 인덱스를 생성해야 하는가?
일단 결론부터 말하면 대량 검색 / 정렬이 많은 컬럼 그리고 분포도가 '좋고/넓은' 컬럼에 인덱스를 걸어야 한다.
분포도가 '좋은/넓은' 컬럼이 대상이 되어야 한다. 분포도가 '나쁘고/좁은' 컬럼을 선택하게 되면 오히려 독이 되기도 한다.
예를 들어보자
총 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')티스토리 뷰
1. 인덱스 컬럼의 변형
select * from table where LOWER(name) ='word';
select * from table where idx - 1 = 5;
이 처럼 인덱스에 변형을 가하게 되면, DBMS가 인덱스를 이용하지 않는다.
2. NOT 또는 IN 연산자 사용
NOT일 경우 무조건 인덱스를 안타는 것이 아니다.
NOT일 경우에도 인덱스를 타긴 타지만, 일반적으로, NOT에 사용된 값이 아닌 데이터의 비율이 높은 경우가 많기 때문에 인덱스를 타지 않는 경우가 많다.
마찬가지로 IN일 경우에도, IN에 포함된
데이터들의 비율이 매우 높다면 FULL SCAN을 하는 것이 낫다고 DBMS가 판단하면 인덱스를 타지 않는다.
3. 와일드 카드 LIKE문장에서 범위를 전체를 지정시
select * from table where name like '%word';
문자열로 이루어진 값을 인덱스로 잡았을 때, %가 앞쪽에 사용되면 정렬 순서를 사용할 수 없으므로 테이블 FULL SCAN이 이루어진다.
select * from table where name like 'word%';
당연한 얘기지만 쿼리가 이런 경우 인덱스를 탄다. 문자열 정렬 순서를 그대로 이용할 수 있기 때문이다.
4. 복합 컬럼 index에서 조건이 잘못되여 index 가 적용 되지
못하는경우
select * from table where name = 'word' or idx = 5
name과 idx가 둘다 인덱스가 걸려있는 경우라해도, DBMS가 최적의 OR 조건을 뽑기 힘들어, FULL SCAN 하는 경우가 많다.
5. Optimizer 의 선택select * from table where name ='word' and id ='elky';
인덱스가 name 과 id로 2개가 있을 경우 id나 name 인덱스 중 하나가 선택될수도 있고, 둘다 선택 될 수도있다.
어떤 방식으로 선택하는냐가 속도에 중요할수도있다. 즉 실행 계획을 추적해서 원하는 결과가 나오도록 관리가 필요하다