TIL 정리_55(인덱스)

 

데이터베이스에서의 인덱스

 

인덱스는 데이터베이스 객체의 하나로 DDL을 사용해서 작성하거나 삭제한다

표준 SQL에는 CREATE INDEX 명령이 없지만 인덱스 자체가 데이터베이스 제품에 의존하는

선택적인 항목으로 취급된다 

인덱스 = 정렬
지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것이다
-> 검색이 빨라진다 

 

인덱스 작성 : CREATE INDEX

예) CREATE INDEX 인덱스명 ON 테이블명(열의 이름 1, 이름 2 ....) 

인덱스 삭제 : DROP INDEX 

예) DROP INDEX 인덱스명 (스키마 객체의 경우) 

DROP INDEX 인덱스명 ON 테이블명(테이블 내 객체의 경우)

 

인덱스 작성을 통해 쿼리의 성능 향상을 기대할 수 있다

실제로 인덱스를 사용해 검색하여 확인하려면 EXPLAIN 명령을 사용하면 된다

EXPLAIN SQL 명령 

-> 실제로는 실행되지 않고, 어떤 상태로 실행되는지를 데이터베이스가 설명해준다 

 

 

인덱스의 특징 
- insert, update, delete의 성능을 희생하고 대신 select의 성능을 향상시킨다
- update, delete 행위가 느릴뿐, update delete를 하기 위해 해당 데이터를 조회하는 것은

인덱스가 있으면 빠르게 조회 가능하다 ( 편집, 등록만 더뎌진다)

->인덱스가 없는 컬럼을 조건으로 update delete를 하게 되면 굉장히 느려 많은 양의 데이터를
삭제해야하는 상황에서는 인덱스로 지정된 컬럼을 기준으로 진행하는 걸 추천한다

 

 

이진트리 인덱스 구조

 

인덱스 탐색은 root -> branch -> leaf -> 디스크 저장소 순으로 진행된다

• 인덱스의 두번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있다
• 디스크에서 읽는 것은 메모리에서 읽는 것보다 성능이 훨씬 떨어진다
  ->인덱스 성능을 향상시킨다는 것은 디스크 저장소에 얼마나 덜 접근하게 만드느냐,
  인덱스 root에서 leaf까지 오고 가는 횟수를 얼마나 줄이느냐에 달려있다
• 인덱스의 갯수는 3~4개 정도가 적당하다
• 다수의 인덱스는 새로운 행을 등록할 때마다 인덱스를 추가해야하고, 수정/삭제시마다
• 인덱스 수정이 필요하여 성능상 이슈가 존재한다 
• 인덱스 역시 공간을 차지하기에 많은 인덱스들은 그만큼의 공간을 차지한다
  -> 많은 인덱스로 인해 옵티마이저가 잘못된 인덱스를 선택할 확률이 높다

 

 

인덱스 키 값의 크기

• innoDB(mysql)은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 라고 하며,
  인덱스 역시 페이지 단위로 관리된다
• 크기는 16kb로 고정되어 있다
• 인덱스 키는 길면 길수록 성능상 이슈가 있다(성능이 떨어진다)

 

 

인덱스 컬럼 기준
1개의 컬러만 인덱스를 걸어야 한다면 해당 컬럼은 카디널리티(cardinality)가 가장 높은 것을
잡아야 한다

카디널리티 : 해당 컬럼의 중복된 수치를 말한다
예) 성별, 학년 등은 카디널리티가 낮다
주민등록번호, 계좌번호는 카디널리티가 높다

 

인덱스로 최대한의 효율을 뽑아내려면 해당 인덱스로 많은 부분을 걸러내야한다.
만약 성별을 인덱스로 잡는다면 남/녀 중 하나를 선택하게 되어 인덱스를 통해 50%밖에 걸러내지 못하지만, 

주민번호 계좌번호 등은 인덱스를 통해 데이터의 대부분을 걸러내기 때문에

빠르게 검색이 가능하다

 

여러 컬럼으로 인덱스 구성시 기준
여러 컬럼의 경우 어떤 순서로 인덱스를 구성해야 할까?

-> 카디널리티가 높은곳에서 낮은순으로 구성하는게 더 성능이 뛰어나다

 

 

꼭 인덱스의 컬럼을 모두 사용해야만 인덱스가 사용되는 것은 아니다
어떤것이 누락되어도 괜찮고 어떤것이 안될까?
-> 최소한 첫 번째 인덱스 조건은 조회 조건에 포함되어야 한다
첫 번째 인덱스 컬럼이 조회 쿼리에 없으면 인덱스를 타지 않는다

 

인덱스 조회시 주의사항

1. between like <, >등 범위 조건은 해당 컬럼은 인덱스의 영향을 받지만, 
   그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않는다
2. 반대로 =, in은 다음 컬럼도 인덱스를 사용한다
   -in은 결국 = 를 여러 번 실행시킨것이기 때문이다
   - in의 인자로 서브쿼리가 들어가면 서브쿼리의 외부가 먼저 실행되고 in은 체크 조건으로 실행되기 떄문에
   인자값으로 상수는 괜찮지만 서브 쿼리를 넣게되면 성능상 이슈가 발생한다
3. AND 연산자는 각 조건들이 읽어와야할 row수를 줄이는 역할을 하지만
   or연산자는 비교해야할 row가 늘어나기 떄문에 풀 테이블 스캔이 발생할 확률이 높다
4. 인덱스로 사용된 컬럼값 그대로 사용해야 인덱스가 사용된다
   - 인덱스는 가공된 데이터를 저장하고 있지 않다
   - 컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않는다 
   ->정확한 타입을 사용해야 한다

 

인덱스 컬럼 순서와 조회 컬럼 순서
- 꼭 순서를 지킬 필요는 없다
- 조회 조건이 포함되어 있는지가 더 중요하다
(이왕이면 순서를 맞추는 편이 좋다)

참고 : 

SQL 첫 걸음 
https://jojoldu.tistory.com/243