22. InnoDB의 PK와 일반 인덱스 Key 컬럼의 중복시 저장은 어떻게 될까?

아마 한번쯤은 고민해 봤을 문제이다. 

Secondary index 생성 시 PK의 컬럼과 중복된다면....내부적으로 어떻게 저장될까? 

먼저 제레미의 글을 찾아보면 다음과 같은 글을 확인할 수가 있다. 

http://blog.jcole.us/2013/01/10/the-physical-structure-of-records-in-innodb/

Secondary indexes

Secondary indexes in InnoDB have an identical overall structure to the clustered key (PRIMARY KEY) but instead of containing non-key fields, they contain the clustered key fields, also known as a Primary Key Value or most commonly, PKV. If any fields overlap between the secondary key and the clustered key, the overlapping fields are removed from the clustered key stored in the secondary index records. For example, if a table has a PRIMARY KEY (a, b, c) and a secondary index KEY (a, d), the secondary key in the index will be as expected, (a, d) but the PKVs will contain only(b, c).

즉, PKV와 Secondary index의 컬럼이 중복이 되면 PKV에서 중복된 컬럼의 값은 제외하고 저장한다는 이야기 이다. 

그럼 정말로 그러한지 한번 테스트를 해보기로 했다. 

이해하기 쉽게 하기 위해 다음과 같이 문자열로 된 테이블을 만들었다. 

[root@oraclelinux6.localdomain][test]> show create table test2;

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                                                                                                                             |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| test2 | CREATE TABLE `test2` (

  `str` varchar(10) COLLATE utf8_bin NOT NULL,

  `str2` varchar(10) COLLATE utf8_bin DEFAULT NULL,

  `str3` varchar(10) COLLATE utf8_bin DEFAULT NULL,

  PRIMARY KEY (`str`),

  KEY `aaa` (`str2`,`str`),

  KEY `bbb` (`str2`),

  KEY `ccc` (`str2`,`str3`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

데이터는 다음과 같이 입력했다. 

[root@oraclelinux6.localdomain][test]> select * from test2;

+-------+------+-------+

| str   | str2 | str3  |

+-------+------+-------+

| data1 | etc1 | NULL  |

| data2 | etc2 | NULL  |

| data3 | etc3 | misc3 |

+-------+------+-------+

3 rows in set (0.00 sec)

이제 xxd를 이용하여 ibd 파일을 읽어 보도록 하자. 

[mysql@oraclelinux6 test]$ xxd test2.ibd > test2.log

[mysql@oraclelinux6 test]$ vi test2.log

xxd로 변환한것을 읽어보면 다음과 같이 확인해 볼 수가 있다. 

PK를 포함하여 인덱스가 총 4개 이기 때문에 ibd 파일을 변환한 파일을 열어보면 다음과 같이 키가 저장된 영역이 4개가 보임을 확인할 수 있다. 물론, 첫번째에 보이는 부분은 데이터가까지 저장되어 그 내용을 확인할 수 있다. 

이제 우리가 알려고 하는 중요한 내용을 알아보자. 

2번째 이미지와 3번째 이미지는 각각 aaa 와 bbb의 인덱스 정보를 저장한 영역에 대한 xxd 결과 이미지이다. aaa는 str2, str1으로 키가 구성되어있고, bbb는 str2로만 키가 구성되어있다. 즉, aaa의 경우 키를 구성하는 컬럼중의 하나가 PK의 구성 컬럼과 같은 것임을 알 수 있다. 그런데 보면 저장된 내용은 aaa나 bbb나 동일함을 알 수 있다. aaa는 자신의 키정보와 PKV 정보를 저장하는데, 중복되는 str에 대한 정보가 하나 빠진 상태인 것이고, bbb의 경우 자신의 키 정보인 str2와 PKV인 str을 같이 저장하고 있기 때문에 두개의 실제 저장 내역은 동일하게 보이는 것이다. 

테스트 해본 내용은 여기까지 정리~! 

더 심도깊게 할 수도 있으나, 일이 많으므로 여기까지만, ㅋㅋㅋ