Collation 에 따른 인덱스 저장 정렬 순서 차이 확인

InnoDB에서 Collation에 따라 Secondary 인덱스의 데이터가 어떤 순서로 저장되는지 확인해 보도록 하자. 

사실 Collation은 쿼리 조회시 영향을 주는 것이라 인덱스 데이터 저장 정렬 순서에 어떻게 영향을 주는지 한번도 생각해 보지 못했다. 동료와 같이 이야기 하던 중 분명 영향을 주는 것이 맞다고 생각하고 그 생각이 맞는지 테스트를 진행해 보았다. 

 

다음과 같은 테이블을 작성하여 테스트를 진행한다. 해당 작업은 MySQL Ver. 5.7.25에서 진행하였다. 

CREATE TABLE `tbs` (   `name` varchar(10) NOT NULL collate utf8mb4_bin,   `extr` varchar(10) NOT NULL collate utf8mb4_general_ci,   `id` int(11) NOT NULL,   `address` varchar(100) DEFAULT NULL,   PRIMARY KEY (`id`,`name`),   KEY (`extr`),   KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

위와 같은 테이블에 다음의 INSERT 문을 다음의 순서대로 입력하였다. 

insert into tbs values ( 'aaAs','bbB',1, 'address'); insert into tbs values ( 'AAAs','bbB',1, 'address'); insert into tbs values ( 'aAAs','bbB',1, 'address'); insert into tbs values ( 'aAAss','bBB',1, 'address'); insert into tbs values ( 'aaa','bbb',1, 'address'); insert into tbs values ( 'AAA','BBB',1, 'address'); insert into tbs values ( 'aAA','bBB',1, 'address'); insert into tbs values ( 'aaA','bBB',1, 'address');

그리고 나서 datadirectory의 스키마 디렉토리에 가서 다음과 같이 xxd를 이용하여 dump를 떠 보았다. 

mersenneui-MacBook:test mersenne$ ls -al  total 1120 drwxr-x---  13 mersenne  staff     416  5  6 14:00 . drwxr-xr-x  37 mersenne  staff    1184  5  6 09:25 .. -rw-r-----   1 mersenne  staff    8732  6 14  2019 contents.frm -rw-r-----   1 mersenne  staff   98304  6 17  2019 contents.ibd -rw-r-----   1 mersenne  staff    8640  4 20 15:44 d_t.frm -rw-r-----   1 mersenne  staff   98304  4 20 15:44 d_t.ibd -rw-r-----   1 mersenne  staff      65  2 15  2019 db.opt -rw-r-----   1 mersenne  staff    8560  2 15  2019 tb.frm -rw-r-----   1 mersenne  staff   98304  2 15  2019 tb.ibd -rw-r-----   1 mersenne  staff    8586  2 15  2019 tb1.frm -rw-r-----   1 mersenne  staff   98304  2 15  2019 tb1.ibd -rw-r-----   1 mersenne  staff    8652  5  6 14:00 tbs.frm -rw-r-----   1 mersenne  staff  114688  5  6 14:01 tbs.ibd mersenneui-MacBook:test mersenne$ xxd tbs.ibd > tbs.file mersenneui-MacBook:test mersenne$ vi tbs.file

모든 인덱스 데이터는 infimum/supremum 정보를 먼저 넣기 때문에 그 부분을 찾아서 보면 바로 인덱스 정보를 찾아서 확인할 수 있다. 

첫번째 나오는 부분은 PK 데이터 부분이다. 

두번째 나오는 부분은 첫번째 설정한 secondary 인덱스이다. 정보를 확인하면 PK정보와 추가 인덱스 키 정보가 저장되어있음을 확인할 수 있다. 

세번째 나오는 부분을 보면 name을 키로한 인덱스 정보 임을 확인할 수 있다. 

그런데 데이터 저장된 순서를 보면 모두 INSERT 구문의 순서와 동일함을 확인할 수 있다. T.T

아무래도 입력시에는 순서대로 입력하고 포인터 정보만 수정한것이 아닌가 판단이 되어 analyze table을 하여 최적화 해보고 결과를 비교해 보기로 했다. 

mysql> analyze table tbs; +----------+---------+----------+----------+ | Table    | Op      | Msg_type | Msg_text | +----------+---------+----------+----------+ | test.tbs | analyze | status   | OK       | +----------+---------+----------+----------+ 1 row in set (0.01 sec)   mysql> exit

그리고 나서 다시 xxd 명령를 실행했다. 

mersenneui-MacBook:test mersenne$ ls -al  total 2240 drwxr-x---  14 mersenne  staff     448  5  7 09:58 . drwxr-xr-x  38 mersenne  staff    1216  5  7 09:57 .. -rw-r-----   1 mersenne  staff    8732  6 14  2019 contents.frm -rw-r-----   1 mersenne  staff   98304  6 17  2019 contents.ibd -rw-r-----   1 mersenne  staff    8640  4 20 15:44 d_t.frm -rw-r-----   1 mersenne  staff   98304  4 20 15:44 d_t.ibd -rw-r-----   1 mersenne  staff      65  2 15  2019 db.opt -rw-r-----   1 mersenne  staff    8560  2 15  2019 tb.frm -rw-r-----   1 mersenne  staff   98304  2 15  2019 tb.ibd -rw-r-----   1 mersenne  staff    8586  2 15  2019 tb1.frm -rw-r-----   1 mersenne  staff   98304  2 15  2019 tb1.ibd -rw-r--r--   1 mersenne  staff  557056  5  6 14:13 tbs.file -rw-r-----   1 mersenne  staff    8652  5  7 09:58 tbs.frm -rw-r-----   1 mersenne  staff  131072  5  7 09:58 tbs.ibd mersenneui-MacBook:test mersenne$ xxd tbs.ibd > tbs.file2 mersenneui-MacBook:test mersenne$ vi tbs.file2

첫번째 Secondary 인덱스 부터 보자. 

 

analyze BEFORE 이미지

analyze AFTER 이미지

키로 사용하는 extr 컬럼은 Collation이 General CI로 설정되어있다. 순서가 변경되어있기는 하지만, 정확히는 대소문자 순서가 맞지는 않다. 

두번째 Secondary 인덱스를 확인해 보자. 

analyze BEFORE 이미지

analyze AFTER 이미지

여기서 키로 사용하는 name은 UTFMB4_BIN 으로 설정된 컬럼이다. 키순서가 정확히 대소문자 구문으로 처리되어있음을 확인할 수 있다. 

 

즉, 파일에 저장되는 인덱스 정보의 정렬 순서는 Collation 설정에 영향을 주는 것을 확인할 수 있다.