MySQL 성능향상

1. Index

2. B-Tree

3. FastOpimizer & Execution Plan

4. Full Text Index

5. Partition

 

Index

Index Scan vs Table Scan(Full Table Search)

id(PK)	data
1	A
2	B
3	C

만약 조건절에 data로 검색을 했을때 전체 테이블을 스캔해야한다 ( WHERE data = 'A' )

id로 검색했을 때 pk는 인덱스가 있기때문에 인덱스 영역만 읽기는다

Clustered Index VS None-Clustered Index

Clustered Index : 물리적인 데이터파일과 직접적으로 연관 1:1 매칭

PK , Unique Index , Physical Data File

읽기 속도만 향상된다

쓰기는 느리다

 

MySQL 8.0부터는 옵티마이저의 성능이 개선되어 빠르다 -> 인덱스를 남용할 필요 없다 옵티마이저와 SSD만으로도 성능을 낼 수 있다

 

카디널리티 : 인덱스의 분류기준 예를 들어 성별로 남 , 여 로 구분하여 인덱싱을 할 경우 카디널리티는 2가 되어 성능이 좋지않다

카디널리티가 높아야 성능이 좋다.

B(Balanced)-Tree

Roots , Branches , Leaves Node

트리의 노드들에 데이터를 쌓는다

노드를 페이지라고 부른다 (비트리에서)

execution plan

id

select_type

type 

  - const : PK같은 클러스터드 인덱스

  - ALL : 테이블 전체조회

  -  ref : 유니크가 아닌 조회

  - range : 인덱스의 특정범위조회

  - fulltext : 풀텍스트인덱스를 사용했을때

  - etc 

possible_keys : 사용가능한 키들

key : 옵티마이저가 선택한 키

key_len : 인덱스의 키사이즈

ref : 인덱스에서 데이터를 찾기위해 어떤 컬럼을 기준으로 찾는지를 나타낸다

rows :  row access count  찾아낸 로우들의 개수

filtered : 남아있는 로우들의 추정 수

extra : 

  - Using where : 인덱스를 사용하지 않을경우

  - Using Index : 인덱스를 사용할 경우

  - Using Filesort : 클러스트 인덱스가 아닌경우

  - etc : 그 외

 

인덱스를 재구성했을경우에는

optimize table; = 테이블 최적화

anlyze table; = 테이블 재분석

 

을 해주는것이 좋다

 

---

정리

 

1. 클러스터 인덱스는 데이터와 직접 연관

2. 데이터크기가 크면 페이지 분할이 빈번하게 발생하기 때문에 쓰기 성능이 저하된다 (B-Tree참조) 

3. 카디널리티가 높을수록 유리

4.클러스터 인덱스는 조회 성능은 보조 인덱스보다 빠르지만 쓰기 성능은 저하된다. -> auto increment 를 사용하여 방지 :: 인덱스 중간에 데이터가 끼어들경우 페이지(노드) 분할이 일어나기 때문에 , AI를 사용하면 끼어드는 경우가 적기때문에 사용

5. 다중컬럼인덱스는 순서를 고려해서 잡으면 효과적

6.인덱스는 꼭 필요한 컬럼만 사용 : 인덱스가 전체테이블의 10~15% 이상 읽을경우 사용하지 않음