[MariaDB][ADMIN] INNODB 클러스터 인덱스(클러스터 테이블) 개념 및 성능 분석

1.클러스터링 인덱스 지원 엔진

  - InnoDB, TokuDB 

2. 클러스터링 인덱스 적용 대상

  - 프라이머리 키(PK)

3. 클러스터링 인덱스 구성 방법

  - PK 기준으로 정렬되어 레코드가 저장되며, PK가 변경된다면 레코드의 물리적인 저장 위치가 변경 된다.(Non 클러스터 테이블은 INSERT 될때 파일의 끝 또는 임의의 빈공간에 저장되고, 위치 변경은 없다. 레코드가 저장 된 주소(ROWID)를 식별 아이디로 인식함)

  - 클러스터키를 구성하는 우선순위는 아래와 같다.

  - PK가 있으면 클러스터 키로 선택 > NOT NULL 제약조건의 유니크 인덱스 중에서 첫번째 인덱스를 클러스터 키로 선택 > 임의의 유니크 값을 생성하여 클러스터 키로 선택 

4. 클러스터링 인덱스와 보조 인덱스(Secondary index)와의 관계 

  - InnoDB 테이블의 모든 보조 인덱스는 해당 레코드가 저장된 주소가 아니라 PK값을 저장하도록 구현 됨(인덱스 검색 > 레코드의 PK확인 > PK로 테이블 검색 후 최종 데이터 추출)    

5. 클러스터링 인덱스 장단점

  - 장점 : PK로 검색 할때 처리 성능이 매우 빠름, 모든 보조 인덱스가 PK를 갖고 있기 때문에 인덱스만으로 처리 될 수 있는 경우가 많음(커버링 인덱스)

  - 단점 : 모든 보조 인덱스가 PK를 갖고 있기 때문에 인덱스 크기가 커짐, INSERT 할 때 PK에 의해 레코드의 저장 위치가 결졍되어 처리 성능이 느림, PK를 변경 할때 DELETE, INSERT가 발생하여 처리 성능이 느림 

6. Non-Clusterd Index(B-Tree) Vs Clusterd Index   

  - 공통점: 키 값으로 정렬되어 저장 

  - 차이점: B-Tree의 리프 노드와 달리 클러스터링 인덱스의 리프 노드에는 레코드의 모든 컬럼이 같이 저장되어 있음

  6.1 테스트 

  #테스트 환경

    - CentOS 7.4

    - MariaDB 10.3.8 

  # 테스트 내용 

    - Clusterd Index(InnoDB) 와 Non-Clusterd Index(MyISAM) 크기 및 성능 비교 

1. 테스트 테이블 및 데이터 생성 

/*클러스터링 테이블 생성*/
CREATE TABLE test.INNODB_CLUSTER_TABLE(
  SEQ_NO  int(9) NOT NULL AUTO_INCREMENT,
  CONN_DATE datetime NOT NULL ,
  ID varchar(100) NOT NULL ,  
  NAME varchar(100) NOT NULL ,
  PRIMARY KEY (SEQ_NO,CONN_DATE)  
) ENGINE=InnoDB ;

/*Non클러스터링 테이블 생성*/
CREATE TABLE test.MyISAM_NON_CLUSTER_TABLE(
  SEQ_NO  int(9) NOT NULL AUTO_INCREMENT,
  CONN_DATE datetime NOT NULL ,
  ID varchar(100) NOT NULL ,  
  NAME varchar(100) NOT NULL ,
  PRIMARY KEY (SEQ_NO,CONN_DATE)  
) ENGINE=MyISAM ;

/*200만건  데이터 적재*/
INSERT INTO test.INNODB_CLUSTER_TABLE(CONN_DATE,ID,NAME) 
SELECT CONCAT('2020-03-', LPAD(FLOOR(1+RAND()*30),2,'0'),' 09:51:30.000'),SUBSTRING (MD5( RAND ()) from 1 for 6),SUBSTRING (MD5( RAND ()) from 1 for 6)
FROM   seq_1_to_2000000;

INSERT INTO test.MyISAM_NON_CLUSTER_TABLE(CONN_DATE,ID,NAME) 
SELECT CONCAT('2020-03-', LPAD(FLOOR(1+RAND()*30),2,'0'),' 09:51:30.000'),SUBSTRING (MD5( RAND ()) from 1 for 6),SUBSTRING (MD5( RAND ()) from 1 for 6)
FROM   seq_1_to_2000000;

2. 클러스터링 테이블과 Non클러스터링 테이블 용량 비교 
  - INNODB_CLUSTER_TABLE(ibd ) : 96MB
  - MyISAM_NON_CLUSTER_TABLE(MYD) + MyISAM_NON_CLUSTER_TABLE(MYI) = 54M + 30M = 84MB

3. 클러스터링 인덱스과 Non클러스터링 인덱스(B-Tree) 생성
ALTER TABLE test.INNODB_CLUSTER_TABLE ADD INDEX INNODB_CLUSTER_TABLE_IX01(ID,NAME);
ALTER TABLE test.MyISAM_NON_CLUSTER_TABLE ADD INDEX INNODB_CLUSTER_TABLE_IX01(ID,NAME);

4. 인덱스 추가 후 용량 비교(보조 인덱스에 클러스터 키가 추가되어 생성 되기 때문에 동일한 인덱스를 생성해도 2배이상 용량 차이가 발생)
  - INNODB_CLUSTER_TABLE(ibd ) : 156(+60)MB
  - MyISAM_NON_CLUSTER_TABLE(MYD) + MyISAM_NON_CLUSTER_TABLE(MYI) = 54M + 30M(+24) = 108MB

5. 클러스터링 인덱스와 Non클러스터인덱스 성능 비교 
  5.1  PK 인덱스 컬럼 조회(PK인덱스 조건)
  SELECT  A.SEQ_NO, A.CONN_DATE        
  FROM    test.INNODB_CLUSTER_TABLE A 
  WHERE   1=1
  AND   A.SEQ_NO BETWEEN 1 AND 1500000
  AND   A.CONN_DATE BETWEEN STR_TO_DATE(202003010100,'%Y%m%d%H%i%s') AND STR_TO_DATE(20200310235959,'%Y%m%d%H%i%s');
  483815 rows in set (0.572 sec)

  SELECT  A.SEQ_NO ,A.CONN_DATE       
  FROM    test.MyISAM_NON_CLUSTER_TABLE A 
  WHERE   1=1
  AND   A.SEQ_NO BETWEEN 1 AND 1500000
  AND   A.CONN_DATE BETWEEN STR_TO_DATE(202003010100,'%Y%m%d%H%i%s') AND STR_TO_DATE(20200310235959,'%Y%m%d%H%i%s');
  499694 rows in set (0.671 sec)

  비교 결과 : PK컬럼만 사용 할때는 성능의 차이는 거의 없다.


  5.2 테이블 전체 컬럼 조회(PK인덱스 조건) 
  SELECT  A.SEQ_NO ,A.CONN_DATE ,A.ID ,A.NAME 
  FROM    test.INNODB_CLUSTER_TABLE A 
  WHERE   1=1
  AND   A.SEQ_NO BETWEEN 1 AND 1500000
  AND   A.CONN_DATE BETWEEN STR_TO_DATE(202003010100,'%Y%m%d%H%i%s') AND STR_TO_DATE(20200310235959,'%Y%m%d%H%i%s');
  483815 rows in set (0.695 sec)

  SELECT  A.SEQ_NO ,A.CONN_DATE ,A.ID ,A.NAME 
  FROM    test.MyISAM_NON_CLUSTER_TABLE A 
  WHERE   1=1
  AND   A.SEQ_NO BETWEEN 1 AND 1500000
  AND   A.CONN_DATE BETWEEN STR_TO_DATE(202003010100,'%Y%m%d%H%i%s') AND STR_TO_DATE(20200310235959,'%Y%m%d%H%i%s');
  499694 rows in set (3.087 sec)

  비교 결과 : 클러스터 인덱스는 모든 컬럼을 갖고 있기 때문에 테이블 엑세스가 없다. 
                      Non클러스터 인덱스는 테이블 엑세스 하여 컬럼의 값을 불러 온다.

  5.3  보조 인덱스 컬럼 조회(보조인덱스 조건)
  SELECT  A.ID ,A.NAME 
  FROM    test.INNODB_CLUSTER_TABLE A 
  WHERE   1=1
  AND     A.ID LIKE '9%'
  AND     A.NAME LIKE 'b%';
  15700 rows in set (0.135 sec)

  SELECT  A.ID ,A.NAME 
  FROM    test.MyISAM_NON_CLUSTER_TABLE A 
  WHERE   1=1
  AND     A.ID LIKE '9%'
  AND     A.NAME LIKE 'b%';
  15644 rows in set (0.201 sec)

  5.4  테이블 전체 컬럼 조회(보조인덱스 조건)
  SELECT  A.SEQ_NO ,A.CONN_DATE ,A.ID ,A.NAME 
  FROM    test.INNODB_CLUSTER_TABLE A 
  WHERE   1=1
  AND     A.ID LIKE '9%'
  AND     A.NAME LIKE 'b%';
  15700 rows in set (0.106 sec)

  SELECT  A.SEQ_NO ,A.CONN_DATE,A.ID ,A.NAME 
  FROM    test.MyISAM_NON_CLUSTER_TABLE A 
  WHERE   1=1
  AND     A.ID LIKE '9%'
  AND     A.NAME LIKE 'a%';
  15718 rows in set (1 min 0.115 sec)

  비교 결과 : 클러스터 보조 인덱스는 PK값을 갖고 있기 때문에, 테이블 엑세스 없이 테이블의 전체 컬럼을 조회 할 수 있다.
                 Non클러스터 보조 인덱스는 테이블 엑세스 하여 컬럼의 값을 불러 온다. 

7. 결론

  - MariaDB(MySQL)의 InnoDB 클러스터링 인덱스(테이블)는 정렬 구조 테이블이기 때문에 DML 성능 저하 및 저장 공간 과사용이라는 단점을 갖고 있으나, 데이터 조회 할 때 매우 뛰어난 성능을 보장하는 특징을 갖고 있다.  

  - 클러스터링 인덱스를 정확히 이해하고 SQL을 작성해야 최대 성능을 끌어 낼 수 있다.

참고 URL : https://12bme.tistory.com/149