[database] Index 정리

인덱스에 대해 알아보자!

 

일단 인덱스가 무엇인가?

- 인덱스(index)는 데이터베이스에서 튜플의 검색 성능을 높이기 위해 속성 값과 튜플이 저장된 주소를 저장하는 것을 말한다. 키-값 형태로 '속성 값-튜플 주소'를 인덱스 포인터에 저장한다. 

- 방대한 정보의 테이블이 있을때 그 많은 데이터를 하나하나 풀스캔할 수 없으니깐 인덱스를 걸어주면 인덱스라는 포인터가 생겨서 각 칼럼의 주소값이 저장이 되어 지정된 칼럼의 인덱스가  주소값을 통해 전체 테이블로 가서  원하는 데이터 연결을 더 빨리 찾을 수 있다.

 

[인덱스(index)의 장점과 단점 ]

• 장점
  • 테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
  • 전반적인 시스템의 부하를 줄일 수 있다.
• 단점
  • 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
  • 인덱스를 정렬된 상태로 유지하기 위해 데이터 추가, 수정,삭제하는 경우에는 속도가 느리고 그 만큼의 저장공간도 할당된다. 
  • 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다

원하는 칼럼의 인덱스를 지정하였을 때

 

인덱스는 언제 생성하는가?

인덱스를 생성하는 방법은 2가지가 있는데

첫번째는 테이블을 생성할 때 

두번째는 이미 만들어진 테이블이 있고 그 안에 데이터들이 다 들어가 있을 때 인덱스를 만들 수 있다.  

 

 

 

두 개의 컬럼을 사용하여 인덱스로 만들 수 있는지? 

- 멀티컬럼인덱스(Muti-column Index, 다중컬럼인덱스)를 통해 만들 수 있다. 

멀티 컬럼 인덱스는 데이터를 조회할 때 단일 인덱스를 여러개를 사용해야 하는 경우가 많다면 사용할 수 있는데 

예를 들어 A, B 컬럼을 바탕으로 데이터 탐색을 자주는 하는 경우 A,B 단일 인덱스에 걸려 있는 상태에서 조회하는 것 보다

다중 컬럼 인덱스로 걸려 있는 경우 데이터 엑세스가 줄어들어 성능이 더 좋다. 

 

 

 

멀티 컬럼 인덱스를 사용할 때 주의할 점은?

다중 컬럼 인덱스에서 중요한 것은 인덱스의 두번째 칼럼이 첫번째 컬럼에 의존해서 정렬되어 있다는 것이다. 

즉 두번째 컬럼은 첫번째 컬럼이 똑같은 레코드에서만 의미가 있다. 그래서 컬럼의 순서를 신중하게 결정해야한다.

예를 들어 조건과 같이 개수가 적은 데이터를 조회하는 컬럼을 다중 컬럼 인덱스 앞쪽에 설정하고,

범검색과 같이 개수가 많은 데이터를 조회하는 컬럼을 다중 컬럼 인덱스 뒤쪽에 설정하는 것이 좋다. 

 

또한 단일 컬럼 인덱스보다 더 비효율적으로 insert/update/delete를 수행하므로 가급적 업데이트가 안되는 값을 선정하여 사용하는 것이 좋다.

이 경우에는 team만 찾고 싶다면 멀티인덱스가 아닌 단일 인덱스로 찾는게 더 나음

 

 

그런데 왜 index를 많이 걸어주면 안되는걸까? 

-앞서 적혀진 장단점에 보면 인덱스는 별도의 데이터가 생성되며 용량 차지하게 된다. 그래서 별도의 데이터가 생성되며 인덱스를 많이 만들면 그만큼 내가 만든 인덱스도 여러개의 데이터가 저장되는데 수정을 할 경우 과부하가 올 수 있으므로 index를 많이 걸어준다고 성능이 좋아지는 것은 아니다. 

 

추가 내용++

CREATE, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있다. 그러한 이유 중 하나는 DELETE와 UPDATE 연산 때문이다. 앞에서 설명한대로, UPDATE와 DELETE는 기존의 인덱스를 삭제하지 않고 '사용하지 않음' 처리를 해준다고 하였다. 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생된다면 실제 데이터는 10만건이지만 인덱스는 훨씬 많이 존재하게 되어, SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 될 것이다. 

 

출처: https://mangkyu.tistory.com/96 [MangKyu's Diary:티스토리]

 

 

 

use index () 

-use index() 일 경우 여러 인덱스들 가운데 내가 원하는 인덱스가 걸린 컬럼들의 정보를 가져오게 하고 싶을 때 부탁하는듯함 입력어로 지정해서 하는 것이다.

하지만 내가 입력한 인덱스 대신 옵티마이저가 멀티인덱스 중에 보고 가장 효율적인 인덱스를 자동으로 가져올 수 있다

force index () 

-force index( ) 경우 내가 지정한 인덱스만 사용해달라고 명령적으로 지정해서 하는 것이다. 

 

*여기서 옵티마이저 (optimizer) 란..? 

옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다.

 

 

 

바이너리서치도 설명하기 

[ B+Tree ]

B+Tree는 DB의 인덱스를 위해 자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조이다. B+Tree는 모든 노드에 데이터(Value)를 저장했던 BTree와 다른 특성을 가지고 있다.

• 리프노드(데이터노드)만 인덱스와 함께 데이터(Value)를 가지고 있고, 나머지 노드(인덱스노드)들은 데이터를 위한 인덱스(Key)만을 갖는다.
• 리프노드들은 LinkedList로 연결되어 있다.
• 데이터 노드 크기는 인덱스 노드의 크기와 같지 않아도 된다.

데이터베이스의 인덱스 컬럼은 부등호를 이용한 순차 검색 연산이 자주 발생될 수 있다. 이러한 이유로 BTree의 리프노드들을 LinkedList로 연결하여 순차검색을 용이하게 하는 등 BTree를 인덱스에 맞게 최적화하였다. (물론 Best Case에 대해 리프노드까지 가지 않아도 탐색할 수 있는 BTree에 비해 무조건 리프노드까지 가야한다는 단점도 있다.)

이러한 이유로 비록 B+Tree는 O(log2nlog2n) 의 시간복잡도를 갖지만 해시테이블보다 인덱싱에 더욱 적합한 자료구조가 되었다.

 

아래의 그림은 InnoDB에서 사용된 B+Tree의 구조이다.

 

 

 

 

InnoDB에서의 B+Tree는 일반적인 구조보다 더욱 복잡하게 구현이 되었다. InnoDB에서는 같은 레벨의 노드들끼리는 Linked List가 아닌 Double Linked List로 연결되었으며, 자식 노드들은 Single Linked List로 연결되어 있다.

 

출처: https://mangkyu.tistory.com/96 [MangKyu's Diary:티스토리]

 

 

 

 

어떤 인덱스가 지정되어 있는지만을 확인할 때 확인하는 방법 

: SHOW INDEXES FROM table_name;