MySQL Index

 

 

서두

미루고 미루어왔던 MySQL Index를 정리했다

 

초기에는 우테코 테코톡 영상을 많이 참고했으며, 추후 지식이 쌓이면서 뇌근육이 강해져갔는데,

어느 순간부터는 SQL 레벨업, Real MySQL 책 참고를 많이 했다

 

잘 알려지지 않은 책은 아닌 것 같은데, SQL 레벨업 책이 정말 괜찮다.

어떤 부분에 있어서는 Real MySQL보다도 좋은 것 같다.

Real MySQL이 다루는 난이도나 내용은 더 많을 지라도, SQL 레벨업은 꽤 깊이 있는 내용을  직관적이면서 쉽게 설명했다

(외국 서적인데 번역도 매끄럽다 !!, 쪽수도 적다)

 

서두가 길었다

 

우선 MySQL은 다른 DB와 다른게 몇가지 있다

 

- Repeatable Read 격리 수준에서도 Phantom Read가 없다

  (MVCC를 이용했기 때문인 걸로 안다)

- 세컨더리 인덱스의 리프노드에는 데이터의 주소가 아닌 PK가 있다

  - 이로 인해서 인덱스를 생성할 때 PK를 생략하고 만들 수 있다

 

인덱스 이건 왜 있죠? 먹는건가요?

인덱스를 왜 태울까? 답은 간단하다 

안 태우면 느리니까...

1억개 레코드 중에서 특정 레코드를 찾으려면 최악의 경우 1억번을 다 탐색한다

평균적으로도 5천만번은 탐색하겠지...

 

인덱스를 태우면? log(1억)으로 시간복잡도가 확 줄어든다

이때 이 log의 밑(base)는 2가 아니다

인덱스는 B+ Tree구조를 사용하는데 이때 이 B는 Binary(2)가 아니다...!

Balanced의 약자인데, 자손 노드가 2개보다 많이 올 수 있다

만일 저 밑이 100이라면 4란 숫자가 나온다...

 

즉 1억번 탐색이 4번 만으로 끝난다는 것...

일반적으로 MySQL은 트리의 깊이가 4 정도가 되게끔 유지를 하게 한다

 

 

아니아니 고러면 이거 좋기만 한거야?

은탄환은 없다고... 당연히 그렇지 않다

R의 성능을 올리고 나머지 CUD의 성능은 감소한다

이런 이유는 데이터가 삽입되면서 인덱스에도 레코드가 쌓이고, 또 재정렬해야 하기 때문이다

나는 Update의 경우에는 성능상 문제가 없을꺼라 생각했지만 이 U의 연산을 하는 과정에 Delete를 하고 Create하는 과정이 있다고 한다

 

그럼 인덱스를 걸면 손해일 수도 있단거네??

그렇다. 무분별한 인덱스는 좋지 않다

(정확하지 않다) 어떤 곳은 테이블당 1~3개 정도의 인덱스를 권장하는 것 같다

 

인덱스는 보통의 경우는 성능상의 이득을 본다

왜냐하면 웹서비스의 특성상 변경 연산에 비해 읽는 연산이 더욱 많기 때문이다

읽기:변경의 비율은 대략 9:1 ~ 8:2 정도 된다고 한다

 

따라서 쓰기 속도를 다소 희생해서 읽기 속도를 비약적으로 늘리는 것은 매우 의미 있다

그눔의 레플리케이션도 인덱스를 먼저 걸고, 한대의 DB에서 할 수 있는 튜닝은 모두 하고 난 이후에 하는 것이라 난 생각한다

 

 

잘 걸어 놓은 인덱스, 적중하지 않을 수도 있어...

인덱스를 걸어두었다고 무조건 인덱스를 이용한 검색이 이루어지는 건 아니다

 

예를 들어 총 테이블의 10% 이상의 검색이 이루어지면 타지 않는다

옵티마이저가 풀테이블 스캔하는게 더 이로울 것이라 판단한 것

저 현상이 이해안될 수도 있다. 일반적으로 인덱스 스캔으로 레인지 스캔을 하더라도 결국 데이터에 접근할 때는 랜덤 IO가 발생할 수 있다.. 여기서 병목이 일어나는데... 그럴바에는 풀테이블 스캔을 한다 (부정확한 정보일 가능성 존재)

이 10%라는 수치는 보조 메모리의 성능이 상승하면서 점차 줄어들 것으로 본다 (SQL Level up)

 

또 몇가지 사항이 있다

 

- column Like '%' + 값

- 함수(column) = 값

- column1 = 값1 and column 2 = 값2 (순서)

  - 8.0에서는 순서가 틀려도 되는 걸로 안다

 

저런 예외 사항 몇가지는 인덱스의 자료구조에 대해 곰곰히 생각해보면 이해되는 것들도 있다

 

클러스트링 인덱스, 논 클러스트링 인덱스, 복합 인덱스, 커버링 인덱스....

클러스트링 인덱스

클러스트링 인덱스는 쉽게 말해 테이블 생성시 존재하는 PK를 생각하면 된다. 개발자가 수동으로 걸지 않아도 이미 존재하는 인덱스라고 생각하면 얼추 맞다

 

논 클러스트링 인덱스

논 클러스트링 인덱스는 세컨더리 인덱스라고도 하는데, 개발자가 수동으로 생성하는 인덱스다

 

복합인덱스

복합인덱스는 여러개의 컬럼을 가지는 인덱스이다.

 

복합 인덱스는 이음동의어가 여러개다

- Composite(복합) 인덱스

- Multi Column (다중 컬럼) 인덱스

- 결합 인덱스

 

인덱스를 거는 순서는 카디널리티(*)가 높은 순서대로 하는게 좋다

 

커버링 인덱스

커버링 인덱스는 복합인덱스 등으로 걸어서 Select를 했더니, 이 Select 절에 명시해 놓은 컬럼들이 복합인덱스의 컬럼들 안에 속한거다(Cover된것)

즉 이게 무슨 뜻이냐면, 인덱스 정보에 찾고자 하는 정보가 모두 포함돼 있어, 원본 정보를 찾으러 Data Record를 조사할 필요 없다는 것이다

 

카디널리티(*): 값이 다양한 정도. (ex. ID는 높고, 반, 성별은 낮다)

 

인덱스는 어디에 있어?

인덱스는 메모리가 허용하는 한 메모리에 상주하게 된다

buffer_size를 통해서 조정도 할 수 있다 (총 메모리의 50% 정도)

빈번하게 사용되는 데이터의 경우 커버링 인덱스를 통해서 비약적인 성능 향상을 도모할 수 있을 것으로 보인다

 

보조메모리:: 하드와 SSD

하드의 경우 순차 IO는 준수한데에 반해 랜덤 IO는 아주 느리다

(디스크의 원판을 생각해보면 왠지 알 수 있을 것이다)

 

SSD의 경우 순차IO는 당연히 준수하고 랜덤 IO가 하드에 비해 아주 빠르다

그렇다고 랜덤 IO가 순차 IO만큼 빠르지는 않고 의외로 차이가 난다고는 한다

 

순차 IO의 경우 또한 SSD가 하드보다 빠른 성능을 보여준다

하지만 랜덤 IO에서 아주 큰 성능차를 보인다

 

DB용 서버는 반드시 SSD가 탑재되는걸 권고한다

 

 

구동 테이블

조인할 때 앞에 오는 대상 테이블. (구동, drivenm)

뒤에 오는 테이블을 피구동이라 한다

보통 구동 테이블은 레코드 갯수가 보다 더 작은 걸 둔다고 한다

그러나 보다 더 정확한 접근은, 구동 테이블에서 접근하는 피구동 컬럼에 인덱스가 걸려있는게 중요하다고 한다

(nested  loop를 돌떄 탐색하는 총 row수가 중요하다 !!)

 

 

인덱스의 자료구조

인덱스의 자료구조는 B tree가 아닌 B+ tree를 사용한다

이는 레인지 스캔할 때 훨씬 더 유리하기 때문,

B+ tree 는 리프 노드끼리 더블 링크드 리스트로 이루어졌기 때문에 범위 검색에 아주 유리하다

 

Hash Index는 하나의 레코드 검색시에 O(1) 이지만, 자료구조 태생상 범위 검색이 불가하다

웹 서비스에서 구간 검색은 아주 중요하다!

B+ tree는 O(logN)이지만 범위 검색이 되며, 로그의 밑이 크기 떄문에 실제로 O(1)과 차이가 매우 크지는 않다

 

 

#정리 예정

- No Offset 방식?

(왜 이렇게 비합리적으로 돌아가는지 알아보자... 동작방식이 .. 좀 음.. 그렇다. 왜이런 구조인거야~)

 

 

참고한 자료

[유튜브]

토르&매트의 SQL 튜닝

라라의 SQL

 

[서적]

Real MySQL 1권

SQL Level Up

 

[B+ Tree]

[MySQL] B-tree, B+tree란? (인덱스와 연관지어서)

 

Difference between B tree and B+ tree - GeeksforGeeks

 

B+Tree index structures in InnoDB

 

[공식문서]

MySQL

 

 

[읽으면 좋은 것]

https://d2.naver.com/helloworld/7005

SSD vs HDD 의 차이에 따른 성능을 알려주었다

읽기 쉽고, 인사이트를 제공한다 (버퍼를 잘 사용하면 HDD로 충분히 커버 가능하다란 것을 알려준다)

다만 8GB 등의 지표는 특정 컴퓨팅에 해당하는 값이기 때문에, 다른 환경에서도 적용하기에는 힘들 것 같다