[영상후기]DB 인덱스(DB index) !! 핵심만 모아서 설명합니다 !! (31분이 아깝지 않을 겁니다)

index가 없다면? full scan으로 검색해야 함 O(N)

index가 있다면 O(log N) - B tree 기반 인덱스

index 사용 이유? 조건을 만족하는 튜플을 빠르게 조회

• 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위함
• 특정 조건을 만족하는 데이터를 빠르게 찾기 위해 index 사용

MySQL에서 인덱스 생성

• 중복 O : CREATE INDEX 인덱스이름 ON 테이블 (속성)

  CREATE INDEX player_name_idx ON player (name);

• 두개 이상의 속성을 묶어서 중복 없는 인덱스로 생성할 경우 : CREATE UNIQUE INDEX ~ ON 테이블 (속성1, 속성2)

  CREATE UNIQUE INDEX team_id_backnumber_idx ON player (team_id, backnumber);

테이블을 생성하며 인덱스 생성

• create table 하면서 만들 땐 인덱스 이름 생략 가능
  • INDEX 인덱스명(생략 가능) (속성) - 중복값 가능한 인덱스
  • UNIQUE INDEX 인덱스명(생략 가능) (속성) - 중복값 불가능한 인덱스

대부분 RDBMS에서 Primary key 생성 시 index 자동 생성

2개 이상 컬럼을 가지고 있는 인덱스를 multicolumn(composite) index

인덱스 조회 명령어 : SHOW INDEX FROM 테이블명;

인덱스를 찾을때 Binary Search 활용

• 중간값과 비교하여 작으면 위쪽, 크면 아래쪽
• 중복값이 있을 수 있으니 조건 만족하는것 찾고 다음값도 확인

(인덱스 조건 AND 인덱스x 조건)의 경우

• 인덱스를 통해 앞에 조건을 빠르게 찾고, 인덱스에 해당하는 값에 가서 인덱스가 없는 조건도 만족하는지 확인해야 함
• 비효율적 -> 인덱스를 묶어서 만들어주면 효율적으로 변함
• INDEX(a,b) -> a로 인덱스하고 b를 a에 맞춰 정렬
  • b만 따로 검색할때 인덱스를 활용할 경우 효율이 없음
    -> b 인덱스를 따로 구현하는 것이 좋음
  • ex) Index(a, b)인 상황에서 => b=95; 인 조건, b를 찾기 위해 풀스캔과 비슷 => b로만 인덱스 걸어주는 것이 효율적

즉, 사용되는 쿼리에 맞춰 적절하게 index를 걸어줘야 query가 빠르게 처리될 수 있다.

SQL 명령어 앞에 EXPLAIN 키워드를 적어주면 해당 쿼리가 어떤 index를 사용한 지 확인할 수 있음

optimizer가 알아서 적절한 index를 선택함

• 가끔 성능이 좋지 않은걸 사용할 수 있음
• 직접 인덱스 고르고 싶다면?
  • USE INDEX(권장 사항 느낌) / FORCE INDEX(강제 느낌) 키워드 사용
  • Ignore INDEX : 추천 인덱스 제외 할 수도 있음

SELECT * FROM player USE INDEX (backnumber_idx) where backnumber = 7;
-> 권장 사항 느낌으로 알려줌
SELECT * FROM player FORCE INDEX (backnumber_idx) where backnumber = 7;
-> 결과 얻을 수 없으면 그냥 풀스캔(옵티마이저 판단, force 불가 시)
-> IGNORE INDEX(인덱스 사용x)

Index 생성 시 인덱스를 위한 부가적 데이터 생성됨

• table에 write할 때마다 index 변경 발생
  • 데이터 추가 시 인덱스에도 추가 해야 함
  • 오버헤드 증가
• 추가적 저장 공간 차지
• 불필요한 인덱스 만들지 말자

Covering Index : 조회하려는 attribute를 인덱스가 모두 커버 가능(실제 DB에 접근x)

team_id	backnumber	ptr

ex) select team_id, backnumber FROM player where team_id=5 // index : team_id, backnumber

• 인덱스의 attribute로 정보를 가져오지 않아도 모두 커버가능 (실제 테이블까지 가지 않아도 되니깐)
• 조회 성능이 더 빠름

Hash index : Hash table 사용하여 index 구현

• 조회 : O(1)의 성능
• rehashing(테이블 사이즈 증가)에 대한 부담
• equlity 비교만 가능, range 비교 불가능
• multicolumn index의 경우 전체 attributes에 대한 조회만 가능
  • INDEX(a, b) 의 경우 (a, b) 만 가능
  • B Tree의 경우 a만 이용하여 조회할 수도 있음

Full scan이 더 좋은 경우

• table에 데이터가 조금 있을 때 (몇십, 몇 백건 정도)
• 조회하려는 데이터가 테이블의 상당 부분을 차지할 때

  id	name	mobile_carrier	phone_number

  • ex) 고객들 중 SK 통신사 사용자 조회
  • ex) 대부분 SK 통신사 사용 시 full scan이 더 빠름
  • SELECT * FROM customer WHERE mobile_carrier = 'SK';

• full scan 판단은 옵티마이저가 함

order by나 group by 에도 index 사용될 수 있다.

foreign key에는 index가 자동으로 생성되지 않을 수 있다.

이미 데이터가 몇 백만 건 이상 있는 테이블에 인덱스를 생성하는 경우 시간이 몇 분 이상 소요될 수 있고 DB 성능에 안좋은 영향을 줄 수 있다.