[무언가에 대한 리뷰][테크영상리뷰][쉬운코드]DB 인덱스(DB index) !!

https://www.youtube.com/watch?v=IMDH4iAQ6zM 

목차

• Index가 중요한 이유
• Index 거는 방법
• Index 동작방식
• Index 사용시 참고사항

 

Index가 중요한 이유

SELECT * FROM MEMBER WHERE first_name = 'hello'

• 100만건 기준일때 first_name이 Index가 되어있지 않다면?
  • full Scan(= table Scan)으로 찾아야한다.
  • O(N)의 시간이 걸린다.
• first_name에 index가 걸려있다면?
  • full Scan보다 더 빨리 찾을 수 있다.
  • O(log N) (B-tree based Index) 
• Index를 쓰는 이유는, 조건을 만족하는 튜플들을 빠르게 조회하기 위해.
• 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위해이다.
• 아래의 4개 코드는 빠르게 조건을 찾기 위한것이 인덱스를 사용하는것의 예시다. 특정조건에 만족하는 데이터들을 빠르게 찾기 위해 인덱스를 사용한다.
  • SELECT문의 first_name 조건
  • DELETE문의 log_datetime 조건
  • UPDATE문의 dept_id 조건 
  • SELECT문에 JOIN ( JOIN하기 위한 조건을 빠르게 찾기위한것이 Index다. )

SELECT * From Member where first_name = "HELLO";
DELETE FROM logs WHERE log_datetime < '2022-01-01';
UPDATE employee SET salary = salary * 1.5 WHERE dept_id = 1001;
SELECT * from employee E JOIN department D ON E.dept_id =D.id;

 

Index 생성방법

• Player라는 테이블이 있다.  
  • id
  • name
  • team_id
  • backnumber

CREATE TABLE player(
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    team_id INT,
    backnumber INT
);

 

• 아래 2개의 코드가 있다. 

SELECT * FROM PLAYER where name='hello'
SELECT * FROM player where team_id = 105 and backnumber = 7;

• 이제 인덱스를 만들어보겠다.

 

아래의 코드에 대한 Index를 걸기 위해 name 에 Index를 걸겟다. (추가로 JPA에서 인덱스거는방법도있다. )

SELECT * FROM PLAYER where name='hello'

• name Attribute에 Index를 건다. name은 동명조건이 있을수있으니 중복을 허용할 수 있는 Index를 걸어준다.
  • play_name_idx : 인덱스의 이름
  • Player 테이블에 대해 name Attribute에 Index를 건다.
  • 이제 name Attribute에는 Index가 걸린다. 

CREATE INDEX player_name_idx ON player (name);

 

 

이번에는 team_id와 backnumber에 대한 index를 건다.

 

SELECT * FROM player where team_id = 105 and backnumber = 7;

• 서로 다른 팀에서는 같은 등번호를 가질 수 있지만, 서로 같은팀에서는 unique한 backnumber를 가진다.
• 이런 조회를할떄 성능향상을 할때는 team_id와 backnumber를 합쳐서 인덱스를 만들어줘야한다.

create unique index team_id_backnumber_idx ON player ( team_id, backnumber );

• team_id와 back_number 의 각 선수들의 정보를 unique하게 식별할 수 있으니 unique indx를 생성한다.
• 아까 이전의 name 같은 경우는 중복값(동명이인)이 존재하기에 unqiue Index가 아닌 것이다.

 

 

• 이 방법 말고도 처음에 테이블을 생성할떄 바로 Index를 생성하는 방법에 대해 알아본다.

CREATE TABLE player(
    id INT PRIMARY KEY, //대부분의 RDBMS 에서는 Primary Key는 자동으로 Index가 생성된다.
    name VARCHAR(20) NOT NULL,
    team_id INT,
    backnumber INT,
    INDEX player_name_idx (name),  //테이블에서 생성할경우 이름 생략해도 자동으로 생성된다.
    UNIQUE INDEX team_id_backnumber_idx (team_id, backnumber)
);

 

• team_id_backnumber_idx와 같이 두개 이상의 컬럼으로 구성된 인덱스를 multicolumn index, composite index라고 한다.

만약 해당 테이블의 Index정보를 보고싶다면,

SHOW INDEX FROM player;

• TAble의 이름
• Non-Unique
  • Unique_index 라면 1
  • Unique_index라면 0
• Key_name = Index name
  • 여기서 똑같은 Index가 2개일경우 MultiColumn Index로 이루어져있따는것이다.
  • 이 경우 Seq_in_index가 1, 2,... 이렇게 증가하고 있는 모습이 있다.
• Seq_in_index
• Column_name
• NULL
  • YES
  • NO

 

B-Tree 기반의 Index가 동작하는 방식

• Member라는 테이블에 3개의 Attribute가 존재한다.
  • A
  • B
  • C
• A Attribute에 대해서 Index를 만들어주자.
  • B-Tree based Index(A)
    • 우선 A에 대한 값들이 정렬이 된 상태로 저장이된다.
    • ptr (pointer)라는 데이터가 존재한다. 실제 Members 테이블의 데이터가 존재하는 Pointer를 가리킨다. (데이터테이블을 가리키는것이다.)
    • WHERE a = 9 ; (A가 9 인 조건을 찾아보자, SELECT, UPDATE ... WHERE , JOIN)
      • 이미 정렬이 된 상태이니 처음부터 차례대로 찾는것이 아닌 Binary Search를 사용하여 진행한다. 이진탐색은 O(log N)의 시간복잡도다.
      • 만약 9 를 찾을경우 가운데 값 5 보다 크다면 위로, 작다면 아래로 이런식으로 진행한다.
    • WHERE a = 7 AND b = 95 인 조건을 사용하자. Index는 A만 걸린상태다. 어떤점이 위와 다른지 확인하자.
      • 현재 Index에는 A라는 값만 가지고 있다.
      • 먼저 A값을 Binary Search하는것은 동일하다.
      • A가 7인 값이 여러개일경우에는 B까지 비교하며 진행해야한다. 또한 A=7과 B=95 인 조건이 여러개일수도있다.
      • 즉, 이진탐색을하며 모든 구간을 확인해주어야한다.
      • 이제 A값의 Index의 Pointer 값은 데이터테이블을 가리키니 해당 데이터테이블의 튜플에서의 B값을 다시 반복하여 찾는다. 
      • 만약 A Index만 만들어져있고 B 조건이 Index가 없다면, A Index는 빠르게 찾을 수 있지만, B 조건에는 Index가 없으므로 해당 값을 찾지 못하므로 B조건은 full scan 이다.
      • 지금으로써는 값이 적지만, 100만개라고 가정한다면, 성능적으로 비효율적이다.
      • Index를 더 효율적으로 하기 위해 Create INDEX(a, b)로 한다.
      • 이제는 Index(a, b)가 생성되었고, Members 테이블에서 어떤 값을 가리키는지 pointer값이 있다.
        • 여기서 Index의 우선순위는 A를 기준으로 먼저 정렬되고, B가 정렬된다. Index 생성할시 파라미터의 순서에 따라 우선순위가 결정된다. Index의 Attribute의 순서가 중요하다.
      • 최종적으로는 이 Pointer가 Members를 가리킨다.
      • 이번에도 Where A = 7 And B = 95 인 조건을 사용해보자. 
    • WHERE b = 95 인경우에는 정렬이 되어있지 않기에 성능이 느리다.
      • 또 이떄는 B를 위한 Index를 생성해야한다.
    • 지금까지 B-TREE 기반의 base에 대해 알아보았다.
• 인덱스를 잘이해햇는지 테스트해보자.
• 현재 총 3개의 인덱스가 존재한다.
  • Index(id)
  • Index(name)
  • Index(team_id, backnumber)

SELECT * FROM player WHERE team_id = 110;

• 이 쿼리는 team_id가 110인 튜플을 찾으니 INDEX(team_id, backnumber) Index를 사용해서 빠르게 튜플을 찾을 수 있다.

 

SELECT * FROM player WHERE team_id = 110 AND backnumber = 7;

• 이 쿼리는 team_id와 backnumber로 튜플을 찾으니 INDEX(team_id, backnumber)로 사용하면된다.

 

SELECT * FROM player WHERE backnumber = 7;

• 이 쿼리는 FULL SCAN을 한다.

 

SELECT * FROM player WHERE team_id = 110 OR backnumber = 7;

• team_id는 빠르게 검색할 수 있지만 backnumber는 full scan을 해야한다.

 

• 즉, backnumber만 조회할시에는 추가적으로 INDEX(backnumber)를 걸어줘야 빠르게 사용가능하다.
• 사용되는 query에 맞춰서 적절하게 INDEX를 걸어줘야 query 가 빠르게 처리될 수 있다.

 

어떤 쿼리가 어떤 INDEX를 사용하는지 아는방법은, EXPLAIN 키워드를 활용한다.

EXPLAIN
SELECT * FROM player WHERE backnumber = 7;

• 이를 통해
  • table의 이름
  • possible_keys (backnumber_idx) 사용가능한 Index가 ~~어떤것이 있었고,
  • key : 실제로 사용한 Index입니다.
• DB Index의 Optimizer가 알아서 적절하게 INDEX를 선택한다.
• 간혹, Optimizer가 다른 Index를 선택해서 사용하는 경우가 있다.
• 특정 Index를 사용하도록 명시하고 싶은경우에는

• EXPLAIN
  SELECT * FROM player USE INDEX (backnumber_idx) 
  WHERE backnumber = 7;

  이 경우에는 권장사항을 고려한다.

• EXPLAIN
  SELECT * FROM player FORCE INDEX (backnumber_idx) 
  WHERE backnumber = 7;

• 이 경우는 무조건 사용하도록 한다. 아니라면 full scan.
• 혹은 ignore index로 어떤 index를 피하도록 설정가능하다.

Index는 막만들어도 괜찮을까?

아래처럼 4가지의 Index가 존재한다고 가정한다.

• Index(id)
• Index(name)
• Index(team_id, backnumber)
• Index(backnumber)
• 각각의 인덱스에 대해서 별도의 데이터가 존재하는것이다. Pointer정보를 포함하는 부가적인 Index가 생성된다.
• table에 Write할떄마다 Index도 변경이 발생한다.
• B-Tree로 구현되어있으면, Insert되거나 Update될때마다 오버헤드가 발생한다.
• 추가적인 저장공간을 차지한다.
• 불필요한 Index를 만들지말자.
  • 예로들면, team_id를 조회한다고 가정하면,
    • Index(team_id, backnumber)가 존재하기에 굳이 Index(team_id)를 다시 안만들어도된다.

 

Covering Index란

• Player테이블이 존재하고, INDEX(team_id, backnumber)가 존재하는 상황이다.

• SELECT team_id, backnumber FROM player WHERE team_id = 5;

  • team_id가 5인 선수들의 team_id와 backnumber를 가져오는 쿼리다.
  • 모든 정보를 가져우는것이 아닌 team_id와 backnumber만 가져오려는 것이다.
  • 이떄 이미 Index(team_id, backnumber)에 team_id와 backnumber가 존재한다.
  • 그래서 이런 경우에는 INDEX(team_id, backnumber)만으로도 조회가 가능하다.
  • 조회하려는 attribute(s)를 Index가 모두 cover할떄 커버링 인덱스라한다.
  • 의도적으로 커버링 인덱스로 만들어 조회성능이 빠르도록 합니다.

Hash Index란

• hash table을 사용하여 Index를 구현한다.
• 시간복잡도 O(1)의 성능이다. 조회가 매우 빠르다.
• 단점들이 존재한다.
  • rehashing에 대한 부담
    • Hash로 이루어져있으니 Array로 연결되어있다.
    • 어느 순간에 더 큰 사이즈로 rehashing ( assign more memory )이 상태에서 hash table의 사이즈를 늘려주는것에 대한 부담이 존재한다.
  • equality 비교만 가능, range 비교 불가능
    
    • Mysql 같은경우 B-Tree만 아니라 hash index도 존재하는데, 범위로 검색이 불가능하다.
  • multicolumn index의 경우 전체 attributes에 대한 조회만 가능
    • Index (a, b)가 있을떄 B-Tree 라면, a 조회사 (a, b) 여도 사용이 가능한데 Hash Index는 불가하다.

Full SCAN이 더 좋은경우

• table에 데이터가 조금 있을떄 (몇십, 몇백건 정도일떄)
• 데이터가 몇십만 몇백만건일떄 더 좋을떄는
  • 조회하려는 데이터가 테이블의 상당 부분을 차지할떄는 좋다.
  • CUSTOMER table이 존재한다.
    • id, name, mobile_carrier, phone_number 라고한다.

SELECT * FROM customer WHERE mobile_carrier = '가장 많이 쓰는 통신사';

• 이 처럼 가장 많이 쓰는 통신사가 거의 90%라면, full scan으로 하는것이 더 빠르다.
• full scan을쓸지 Index를 쓸지는 Optimizer가 판단한다.

그 외

• order by나 group by 에도 index가 사용될 수 있다.
• foreign key에는 index가 자동으로 생성되지 않을 수 있다. (join 관련)
• 이미 데이터가 몇백만건 이상있는 테이블에 인덱스를 생성하는경우 시간이 몇분이상 소요될 수 있고, DB성능에 안좋은 영향을 줄 수 있다.

Explain 키워드를 활용하여 분석하는것을 추천한다.