MySql 조회 성능 최적화를 위한 Index 의 이해(1)

MySql 과 SpringBoot 에서 데이터 조회 성능을 최적화하기 위한 방법들은 다양하다.

 

다음 두가지의 방식을 생각해 볼 수 있다.

 

1. 메모리의 캐쉬 hit 을 올린다

CPU 내부의 L1~L3 캐시와 같은 하드웨어 캐시의 히트율을 높이는 방식으로 프로그래밍을 한다.

- SPring 에서 @Cacheable ,@CacheEvict 등을 사용한 캐싱 로직을 구현 하는 것 도 있다.

- 추가로 Mysql 의 쿼리 캐시의 경우 5.6 은 deprecate 이며, 8.0 에서는 제거 되었다. 

 

2. 인덱싱

- 적절한 인덱스를 DB 테이블에 설정하여 조회에 자주 사용되는 컬럼 또는 JOIN 연산에 사용되는 칼럼에 인덱스를 설정한다.

 

데이터의 조회 성능 최적화 중 데이터 베이스와 관련된  하나의 방법에는  디스크 의 랜덤 I/O 를 줄이는 것이며 ,

InnoDB 테이블의 클러스터링 키를 조회에 이용한다면 순차 I/O 를 효과 적으로 활용 할 수 있다.

 

참고) 인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O 를 사용한다. 

큰 테이블의 레코드 대부분을 읽어 올때 , 인덱스를 사용하지 않고 순차 I/O 를 유도 하기 위하여 Full table scan 을 사용 할 수 도 있으며, 통계 처리 및 데이터 웨어 하우스에서 자주 사용된다라고 한다. : RealMySql 1 편  pg 217

 

일반적인 dbms의 옵티 마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5 배 정도 비용이 더 많이 드는 작업인 것으로 예측한다. (mysql 서버에서는 코스트 모델 설정에 따라 변경이 될 수 있다.)

 

인덱스를 통해 읽어야할 레코드의 수가 전체 테이블 레코드의 20~25 % 를 넘어서면 인덱스를 이용하지 않고 테이블을 full scan 하여 필터링 하는것이 효율 적이다.

 

ex)

1000 만건의 데이터의 통계를 위해 4000 만 건을 읽는 작업에서는(전체 테이블 레코드의 20 %) 인덱스를 이용하지 않는게 올바른 판단일 수가 있다.

 

 

 

Mysql 의 index 의 기본 특징을 짚고 넘어가자.

 

데이터베이스에서의 인덱스의 도입은 탐색 범위의 최소화를 위하여 도입이 되었고 ,여기서 index 란 정렬된 데이터 테이블을 조회 하는 키라고 이해하자.

 

인덱스가 설정되지 않은 칼럼의 경우 FullScan 이 수행이 되기 때문에 조회시 속도 저하를 가져온다.

 

MySQL DB Index 의 경우 Clusterd Index 와  Secondary index(virtual index) 가 있다.

 

클러스터형 인덱스 (Clustered Index)

MySql 의 경우 Pk , Unique,index 와 같은 인덱스들은  B - trees 의 자료 구조의 형태를 가지며 저장이 된다.

예외 : 8.0 의 Spatial data type 의 경우 R - trees 를 사용한다.

 

B - Tree 의 특징은 다음과 같다.

 

1. 삽입 /삭제 시 균형을 이룬다.  : 인덱스를 가진 테이블에 DML 작업을 할 경우 많은 시간과 비용이 필요하다.

2. 하나의 노드가 여러 개의 자식노드를 가진다.

3 .리프노드에만 데이터가 존재 한다.

 

2, 3 의 특징으로 인하여 연속적인 데이터 접근시 유리하며 범위 탐색에 특화 되어 있다.

 

다음과 같은 예시 처럼 , 정확한 키 값을 찾는 전방일치 탐색이 불가한  HashMap 의 단점을 보완 할 수 있고.

-- 전방일치 탐색 예  :  like'AB%'  

SELECT * FROM table_name WHERE column_name LIKE 'AB%';

-- result  ABCD ,ABDock ,,,
-- AB 로 시작하는 모든 레코드를 찾아야하는 상황

 

 

-> B- Tree의 각 노드의 키는 정렬이 되어 있어 현재 노드와 시작부분이 일치하는지 빠르게 확인한다.

 

삽입 삭제 비용이 상당히 높은 list 의 단점을 보완 하는 자료 구조이다.

 

자료구조에서 불리는 노드는 앞으로 데이터 파일라고 부른다. 

 

인덱스의 키는 B- Tree 구조로 저장이 되지만 하나의 데이터 파일은 정렬된 리스트로 보관이 되어 있다.

 

사전에서 단어 를 찾는 것처럼 ,  첫 단어를 찾아 사전에서 펼칠 페이지를 찾고 , 사전의 페이지 내부에서 단어들이 정렬이 되어 있는 것과 같이 구성이 되어있다라고 생각을 하면 편하다.

 

 

여담으로

1.각종 블로그에서는 MySql innoDB 는 B+ Tree 를 사용하지  B-Tree 가 아니라고 하지만 , 공식문서에서는 B-tree 라 표기가 되어 있다.

 

2. 데이터 ,인덱스 페이지가 아닌 파일이다.  파일은  레코드의 집합체(Disk-based Data Structure)이다.

 

tuple : record

attirbute : field

table : file  로 매핑이 된다.  (오라클문서 기반 전공 강의 pdf 자료 참고)

 

3. DataStructure 보다는 DataOraganization 이라는 용어를 쓰자.

 

4.  primary index , multi level index 등 다양한 인덱스가 있어 Non clustered index 는 적절한 단어가 아니다.

 

MySql 의 Pk 는 클러스터 인덱스 이다.

 

InnoDB  테이블에서 레코드는 클러스터되어 디스크에 저장되므로 , 기본적으로 프라이머리 키 순서로 정렬되어 저장이 된다.

 

클러스터 인덱스의 또 다른 특징은 데이터 페이지와 함깨 인덱스 키가 존재 한다.

 

 

 

 

클러스터의 키가 삽입시 , 기존의 위치에 있던 클러스터키는 새로운 데이터 주소를 받는다. 

클러스터 키 순서에 따라 데이터 저장 위치가 변경이 된다.

 

 

mysql 의 pk 는 클러스터 인덱스 이다. 이말은 즉 pk 의 삽입 삭제로 인하여 , 성능 이슈가 발생한다.

 

pk 로 auto increment. vs uuid 생성기 ? 어느 방식이 어떤 상황에서 성능 우선을 가져올까 ?

 

 

mysql 에서 pk를 제외한 모든 인덱스는 pk 를 가지고 있고 Secondary Index 로 분류 하며, 세컨더리 인덱스만으로는 데이터를 찾아갈 수 없어, pk 인덱스를 항상 검색 해야한다.

 

범위 검색에서는 pk 를 활용한 검색이 빠르며 , 세컨 더리 인덱스들이 pk 를 가지고 있어 커버링에 유리하다.

공간적 캐쉬에 이점이 있다.