[MySQL] IOT 테이블과 Primary Key와의 관계 그리고 세컨더리 인덱스

■ InnoDB 스토리지 엔진의 테이블 데이터 저장 방식

MySQL 테이블의 데이터는 IOT(Index Oraganized Table)라고 하여 프리머리 키값을 이용하여 데이터를 정렬 후 테이블에 저장하게 되어 있습니다.

이 프리머리 키를 다른말로 클러스터링 인덱스라고도 합니다.  그리고 MySQL에서는 이 클러스터링 인덱스가 좀 특별하게 다루어집니다.

 

그림 1. MySQL B-Tree 인덱스 구조

 

위의 그림은 MySQL의 클러스터링 인덱스의 구조인데 일반적인 B- Tree 인덱스와 비슷합니다.. 그러나 MySQL에서 B-Tree 인덱스의 리프노드에는 모든 칼럼이 같이 저장되어 있습니다.

즉 클러스터링 테이블은 데이터와 프리머리 인덱스를 모두 포함하고 있는 구조가 되는 것입니다.

 

■ 세컨더리 인덱스(Secondary Index) 및 동작 방식

프리머리 키 외에 추가적으로 생성하는 인덱스를 세컨더리 인덱스라고 하며 유니크 키(Unique Key) 또한 세컨더리 인덱스에 포함됩니다.

일반적인 DBMS에서 세컨더리 인덱스를 생성하면 리프 노드(Leaf node)에는 그 데이터가 있는 실제 물리 주소를 가지고 있다가 해당 인덱스가 사용되면 바로 데이터를 가지고 오게 됩니다.

하지만 MySQL에서의 세컨더리 인덱스는 프리머리 키 값을 저장하게 되어 있습니다. 그래서 쿼리 실행시 세컨더리 인덱스를 사용하게 되면 해당 인덱스에 저장되어 있는 프리머리 키값을 찾아 가게 되어 있습니다.

그래서 그 키 값을 이용해서 해당 데이터를 검색하게 되어 있습니다.

즉, 세컨더리 인덱스 -> 프리머리 키  -> 찾고자 하는 데이터

방식으로 데이터를 찾게 되어 있습니다.

이 방법이 느릴것으로 생각이 들수도 있지만 클러스터링 인덱스 구조로 되어 있기 때문에 검색 속도가 월등히 빠를 수 있습니다.

그리고 모든 세컨더리 인덱스가 프리머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많이 있습니다.

 

■ InnoDB에서 테이블 생성시 참고 사항

InnoDB에서 테이블 생성시 Primary Key를 지정하지 않으면 내부적으로  대체할 컬럼을 선택하게 됩니다.
그 컬럼을 선정하는 방법은 NOT NULL 옵션의 유니크 인덱스 (unique index)중에서 첫 번째 인덱스를 클러스터 키로 선택합니다.

아니면 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후 클러스터 키로 선택을 하게 됩니다.

즉, 어떻게 해서든 내부적으로 키를 만들어 추가시키도록 되어 있습니다. 물론 이 키에 대한 정보는 어디서도 조회가 되지 않습니다.

 

■ 테이블 데이터와 인덱스 크기 확인 및 검증

위에서 말했듯이 프리머리 키 즉, 클러스터링 인덱스는 프리머리 키와 데이터를 리프 노드(Leaf node)에서 모두 가지고 있다고 했습니다.

메타 테이블중 information_schema.table에는 데이터 크기와 인덱스 크기가 제공 됩니다. 

 

# 테이블 크기 및 인덱스 크기 확인.

mysql> select TABLE_SCHEMA, TABLE_NAME, DATA_LENGTH, INDEX_LENGTH
    -> from information_schema.TABLES
    -> where 1=1
    -> and table_name='sbtest'
    -> and TABLE_SCHEMA='testdb';
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | DATA_LENGTH | INDEX_LENGTH |
+--------------+------------+-------------+--------------+
| testdb       | sbtest     |   234618880 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (0.00 sec)

 

# 테스트 테이블 DDL

+--------+-------------------------------------------------+
| Table  | Create Table                                    |
+--------+-------------------------------------------------+
| sbtest | CREATE TABLE `sbtest` (
  `id` int(11) NOT NULL DEFAULT '0',
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------+
1 row in set (0.00 sec)

 

MySQL에서는 테이블에 프리머리 키가 주어지지 않을 경우 내부적으로 가장 프리머리 키에 가장 가까운 컬럼을 이용해서 IOT테이블로 만듭니다. 그래서 위의 테이블도 프리머리 키를 지정하진 않았지만 자동적으로 프리머리 키가 만들어졌으며 위의 테이블 정보에 나와있는 DATA_LENGTH는 데이터 크기와 프리머리 키 크기를 더한 값이라 할 수 있습니다. 그래서 검증하는 차원에서 위의 테이블에 프리머리 키를 한번 추가해 보겠습니다. 위의 테이블에서 가장 프리머리 키에 가까운 컬럼은 id로 이 컬럼을 이용해서 프리머리 키를 한번 추가해보겠습니다.

 

# 프리머리키 추가

mysql> alter table testdb.sbtest add primary key(id);
Query OK, 0 rows affected (13.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

# 테이블 데이터 조회

mysql> select TABLE_SCHEMA, TABLE_NAME, DATA_LENGTH, INDEX_LENGTH
    -> from information_schema.TABLES
    -> where 1=1
    -> and table_name='sbtest'
    -> and TABLE_SCHEMA='testdb';
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | DATA_LENGTH | INDEX_LENGTH |
+--------------+------------+-------------+--------------+
| testdb       | sbtest     |   257654784 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (0.00 sec)

 

보시는 바와 같이 데이터의 크기가 좀더 늘어난것을 확인해 볼 수 있습니다. 즉 뒤의 IDEX_LENGTH-인덱스 크기는 세컨더리 인덱스의 크기이며 프리머리 키의 크기는 전혀 반영되지 않음을 알수 있습니다. 즉 데이터 크기는 데이터와 프리머리 키의 크기인것을 알 수 있습니다.