MySQL - UUID 활용 - PK 로 사용

Last Updated on 6월 21, 2023 by Jade(정현호)

안녕하세요 
이번 포스팅에서는 MySQL 의 PK 컬럼에서 Auto Increment 와 같은 자동 숫자 증가 기능 대신에 UUID 를 사용하는 내용에 대해서 전반적으로 확인 해보려고 합니다.  

UUID 란

UUID 는 Universally Unique IDentifier 의 약어이고 범용 고유 식별자 라고 할 수 있습니다.

소프트웨어 구축이 구현에 사용 되는 식별자 표준으로 아폴로 네트워크 컴퓨팅 시스템(NCS)에서 사용 되었다가 나중에 개방 소프트웨어 재단(OSF)의 분산 컴퓨팅 환경(DCE)에서 사용 되었습니다.

UUID 는 국제 인터넷 표준화 기구(IETF; Internet Engineering Task Force)에서 정식 표준으로 채택되어 관리 되며, RFC 4122(Request for Comments) 로 지정되어 있습니다.

네트워크 상에서 서로 모르는 개체들을 식별하고 구별하기 위해서는 각각의 고유한 이름이 필요하며, 이 이름은 고유성(유일성)이 매우 중요합니다.

같은 이름을 갖는 개체가 존재한다면 구별이 불가능해 지기 때문입니다. 따라서 고유성을 완벽하게 보장 하려면 중앙관리시스템이 있어서 일련번호를 부여해 주면 간단 하지만, 동시 다발 적이고 독립적으로 개발되고 있는 시스템 들의 경우 중앙관리시스템은 불가능하게 됩니다.

그래서 개발 주체가 스스로 이름을 짓도록 하되 고유성을 충족할 수 있는 방법이 필요 하였으며, 이를 위하여 탄생한 것이 범용고유식별자(UUID)이며 국제기구에서 표준으로 정하고 있습니다.


UUID 생성 알고리즘은 필요한 경우 시스템당 초당 최대 1000만 정도의 매우 높은 할당 속도를 지원 하므로 트랜잭션 ID로 사용할 수도 있습니다.


UUID는 다른 대안에 비해 상당히 작은 편에 속하는 고정 크기(128비트)입니다.

이것은 모든 종류의 정렬, 순서 지정(ordering) 및 해싱, 데이터베이스에 저장, 간단한 할당(Simple allocation) 및 일반적인 프로그래밍 용이성에 적합합니다.

UUID 의 버전에 따라서 고유성을 완벽하게 보장할 수 없을 수도 있지만, 실제 사용 상에서 중복될 가능성이 거의 없다고 인정되기 때문에 많이 사용 되고 있습니다.


UUID 는 16 옥텟(128비트) 로 구성되어 있으며, 표준 형식으로 32개의 16진수로 표현되어 총 36개 문자(32개 문자 와4개의 하이픈)으로 된 8-4-4-4-12 라는 5개의 그룹을 하이픈으로 구분하게 되며 이를 다음 다음과 같이 표기 됩니다.

0ece7810-4d12-11ed-b88e-080027608c76

아래 표는 UUID 레이아웃 구성 정보 입니다. 

UUID 레이아웃 "xxxxxxxx-xxxx-Mxxx-Nxxx-xxxxxxxxxxxx" 에서 3번째 항목은 time_hi_and_version 으로 필드의 최상의 4비트는 생성 중인 버전을 표시하며 M 자리에는 버전 숫자 [1-5] 가 표시되게 됩니다. 또한 위의 N 자리에는 [8, 9, a, b]  4개 중 하나가 표기 되게 됩니다.

5개의 버전이 존재하며 버전 별로 UUID 를 만들어 내는 방식이 차이를 보이게 됩니다.

• 버전 1 (MAC 주소) : MAC Address and time-based version
• 버전 2 (DCE 보안) : MAC Address and time-based and DCE Security version
• 버전 3 (MD5 해시) : The name-based version, that uses MD5 hashing
• 버전 4 (랜덤) : The randomly or pseudo-randomly generated version
• 버전 5 (SHA-1 해시) : The name-based version, SHA-1 hashing

MySQL 에서 UUID() 함수에서 구현하는 것은 버전 1 이며 타임스탬프 및 MAC 주소로 구성됩니다.
               

UUID 를 사용하는 이유

MySQL 에서 보편적으로 PK 컬럼 타입에 대해서 선정시 실제 업무에 의해서 생성된 본질식별자 와 업무에 의해서 생성된 것은 아닌 인위적으로 식별자 역할을 위해 만들어진 인조식별자 로 나눠볼 수 있습니다.

인조식별자를 사용하는 형태로는 대표적으로 AUTO_INCREMENT 를 사용하는 방법이 보편적인 방법 입니다. 

이러한 인조식별자를 사용하는 방법에는 AUTO_INCREMENT 이외 UUID 를 사용할 수 도 있습니다.

UUID 는 사용하기에 따라서 본질식별자로도 사용할 수도 있습니다.


MySQL 에서 또는 다른 RDBMS 에서 순차적 증가 숫자 시스템(Auto_Increment)이 아닌 UUID 를 사용하는 이유 또는 경우로는 아래와 같이 정리 해 볼 수 있습니다.


1) 데이터베이스가 여러 개로 분리 되어 사용 되다가 다시 합쳐서 사용 되는 경우

대규모 데이터의 세그먼트(샤드)를 포함하는 데이터베이스가 여러 개 있는 경우 UUID를 사용한다는 것은 하나의 ID가 현재 있는 데이터베이스뿐만 아니라 모든 데이터베이스내에서 고유 하다는 것을 의미 하게 됩니다. 이렇게 하면 데이터베이스 간에 데이터를 이동시 키의 중복없이 안전하게 이동할 수 있습니다. 또는 샤딩 된 모든 데이터베이스를 Hadoop 클러스터와 같은 곳에 하나로 병합하는 경우에는 키 충돌에 대해서 대비할 수 있게 됩니다. 


2) 키를 회득하는데 있어서 종속적이지 않습니다.

UUID 는 함수를 호출하여 키를 생성하게 됩니다. 그에 반면에 AUTO_INCREMENT PK 는 다음 값을 알기 위해서는 조회를 해야지 그 다음 유니크 한 값을 알 수 있습니다. 그래서 구성상 PK 값을 사전에 회득해야하는 로직의 경우 UUID 는 INSERT 전에 PK 를 알수 있음으로 PK 값을 알기 위한 반복적인 DB Hit(Access) 를 방지 할 수 있다는 성능적 이점이 있습니다.


3) 보안적인 면이 상대적으로 안전합니다.

AUTO_INCREMENT 로 PK 를 사용시 단순 증가 숫자값이기 때문에 해당 값을 통해서 크롤링 등으로 다른 데이터도 추가로 수집하기 쉬우며 또한 의도치 않은 다른 PK ID 을 유추하거나 조회를 시도할 수도 있습니다. 해당 테이블이 외부에서 URL 등에서 직접적으로 표현되는 경우 라면, 애플리케이션 구현에서 추가적인 보안사항을 통해 먼저 제한을 두어야 햐지만, DB 상에서도 유추가 불가능한 값인 UUID 를 사용하는 경우 이러한 케이스에서 보안적으로 안전하게 사용할 수 있게 됩니다.


4) 업무적인 고유한 값 또는 본질식별자 값으로 사용할 수 있습니다.

Auto_Increment 와 같은 단순 증가 방식을 이용하는 목적 보다는 UUID 의 원론적인 목적과 같이 고유한(유일성) 값을 생성하기 위해서 사용할 수 있습니다. 

예를 들어 주문번호나 체결번호, 상품등록번호나 상품고유번호 등과 같은 업무적인(비지니스와 연관된) 고유한 식별 값이 필요하며, 여러 테이블에서 그리고 연계된 다른 DB의 테이블에서도 연관 되어 공통으로 사용 되고 고유한 값이 필요한 경우에 해당 값을 생성(채번) 하기 위해서 별도의 함수나 기존의 max(번호) + 1 등과 같은 형태 대신하여 UUID 를 사용할 수 있습니다.

UUID 를 통한 고유한 값을 생성 하여 업무적인 식별자로 데이터를 입력하는 방식으로 사용할 수도 있습니다.
            

MySQL 에서 UUID 사용

MySQL 에서 UUID 를 사용하면 위에서 언급한 내용과 같이 여러가지 장점이 있습니다. 다만 반대로 UUID 사용시 단점 또는 고려해야할 부분도 있습니다.
         

consider

MySQL 에서 UUID 를 사용할 때 고려해야할 사항은 다음과 같습니다.

• UUID 의 36자리에 따른 상대적 큰 크기, 그에 따른 Secondary Index 의 크기 증가
• 성능적인 이슈(AUTO_INCREMENT 에 비해) , 일반적으로 무작위로 생성되며, 클러스터형 인덱스가 재조정 되도록 합니다.
  
• 혹시라도 발생할 수 있는 중복 발생 소지

테스트를 위한 테이블을 만들고 데이터를 입력 하도록 하겠습니다.

CREATE TABLE tb_test ( 
uuid VARCHAR(36) DEFAULT (UUID()) PRIMARY KEY, 
first_name VARCHAR(15), emp_no int unsigned)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

...

mysql> select * from tb_test;
+--------------------------------------+------------+--------+
| uuid                                 | first_name | emp_no |
+--------------------------------------+------------+--------+
| 739c5f83-4ee6-11ed-a207-08002722a50f | Georgi     |  10001 |
| 7993163f-4ee6-11ed-a207-08002722a50f | Mary       |  10002 |
| 7f89caa9-4ee6-11ed-a207-08002722a50f | Saniya     |  10003 |
| 85805573-4ee6-11ed-a207-08002722a50f | Sumant     |  10004 |
| 8b7704d4-4ee6-11ed-a207-08002722a50f | Duangkaew  |  10005 |
| c896700e-4ee6-11ed-a207-08002722a50f | Patricio   |  10006 |
+--------------------------------------+------------+--------+

시간 간격을 두고 데이터를 몇 건 더 입력 해보도록 하겠습니다.

mysql> insert into tb_test(first_name,emp_no) values ('Lillian',10007);

mysql> select * from tb_test;
+--------------------------------------+------------+--------+
| uuid                                 | first_name | emp_no |
+--------------------------------------+------------+--------+
| 0e3298e1-4ee7-11ed-a207-08002722a50f | Lillian    |  10007 |
| 739c5f83-4ee6-11ed-a207-08002722a50f | Georgi     |  10001 |
| 7993163f-4ee6-11ed-a207-08002722a50f | Mary       |  10002 |
| 7f89caa9-4ee6-11ed-a207-08002722a50f | Saniya     |  10003 |
| 85805573-4ee6-11ed-a207-08002722a50f | Sumant     |  10004 |
| 8b7704d4-4ee6-11ed-a207-08002722a50f | Duangkaew  |  10005 |
| c896700e-4ee6-11ed-a207-08002722a50f | Patricio   |  10006 |
+--------------------------------------+------------+--------+


mysql> insert into tb_test(first_name,emp_no) values ('Mayuko',10008);

mysql> select * from tb_test;
+--------------------------------------+------------+--------+
| uuid                                 | first_name | emp_no |
+--------------------------------------+------------+--------+
| 0e3298e1-4ee7-11ed-a207-08002722a50f | Lillian    |  10007 |
| 28ce2269-4ee7-11ed-a207-08002722a50f | Mayuko     |  10008 |
| 739c5f83-4ee6-11ed-a207-08002722a50f | Georgi     |  10001 |
| 7993163f-4ee6-11ed-a207-08002722a50f | Mary       |  10002 |
| 7f89caa9-4ee6-11ed-a207-08002722a50f | Saniya     |  10003 |
| 85805573-4ee6-11ed-a207-08002722a50f | Sumant     |  10004 |
| 8b7704d4-4ee6-11ed-a207-08002722a50f | Duangkaew  |  10005 |
| c896700e-4ee6-11ed-a207-08002722a50f | Patricio   |  10006 |
+--------------------------------------+------------+--------+



mysql> insert into tb_test(first_name,emp_no) values ('Ramzi',10009);

mysql> select * from tb_test;
+--------------------------------------+------------+--------+
| uuid                                 | first_name | emp_no |
+--------------------------------------+------------+--------+
| 0e3298e1-4ee7-11ed-a207-08002722a50f | Lillian    |  10007 |
| 28ce2269-4ee7-11ed-a207-08002722a50f | Mayuko     |  10008 |
| 739c5f83-4ee6-11ed-a207-08002722a50f | Georgi     |  10001 |
| 7993163f-4ee6-11ed-a207-08002722a50f | Mary       |  10002 |
| 7e050e3a-4ee7-11ed-a207-08002722a50f | Ramzi      |  10009 |
| 7f89caa9-4ee6-11ed-a207-08002722a50f | Saniya     |  10003 |
| 85805573-4ee6-11ed-a207-08002722a50f | Sumant     |  10004 |
| 8b7704d4-4ee6-11ed-a207-08002722a50f | Duangkaew  |  10005 |
| c896700e-4ee6-11ed-a207-08002722a50f | Patricio   |  10006 |
+--------------------------------------+------------+--------+



mysql> insert into tb_test(first_name,emp_no) values ('Bojan',10010);

mysql> select * from tb_test;
+--------------------------------------+------------+--------+
| uuid                                 | first_name | emp_no |
+--------------------------------------+------------+--------+
| 0e3298e1-4ee7-11ed-a207-08002722a50f | Lillian    |  10007 |
| 28ce2269-4ee7-11ed-a207-08002722a50f | Mayuko     |  10008 |
| 739c5f83-4ee6-11ed-a207-08002722a50f | Georgi     |  10001 |
| 7993163f-4ee6-11ed-a207-08002722a50f | Mary       |  10002 |
| 7e050e3a-4ee7-11ed-a207-08002722a50f | Ramzi      |  10009 |
| 7f89caa9-4ee6-11ed-a207-08002722a50f | Saniya     |  10003 |
| 85805573-4ee6-11ed-a207-08002722a50f | Sumant     |  10004 |
| 8b7704d4-4ee6-11ed-a207-08002722a50f | Duangkaew  |  10005 |
| 952aa053-4ee7-11ed-a207-08002722a50f | Bojan      |  10010 |
| c896700e-4ee6-11ed-a207-08002722a50f | Patricio   |  10006 |
+--------------------------------------+------------+--------+

예상과 달리 입력된 순서가 보장이 되지 않으며, 중간에 입력이 되었습니다.


Clustered Index(PK) 방식을 사용하는 InnoDB 의 테이블에서 순서를 보장하기 위해 기존의 레코드를 이동 시켜서 정렬을 하는 과정이 수행이 된 것이며 테이블이 작은 경우 영향이 없거나 작을 수 있지만 매우 큰(수백 GB 이상 ~ 수TB) 테이블이었을 경우 PK 컬럼 데이터의 레코드의 이동은 매우 큰 성능적인 영향을 끼칠수도 있을 것 입니다.

처음 8자리는 time_low 로 타임스탬프의 낮은 필드값에 대해서 16진수로 표현된 값입니다.
정렬 기준에 따라 생성된 UUID 값이 기존의 PK 값 중간값으로 입력 될 수도 있습니다.


또한 UUID에 대한 VARCHCAR 데이터 유형을 유지하는 경우 기본 키는 레코드당 146바이트가 사용 되게 됩니다.

mysql> explain
    -> select * from tb_test
    -> where uuid='c896700e-4ee6-11ed-a207-08002722a50f'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 146 <!!--
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

혹시 만일에 발생 할 수도 있는 UUID 값의 중복에 대해서는 보통 많이 사용하는 UUID 버전1 과 버전4를 비교 해서 설명하자면, 

버전 1은 타임스탬프는 60비트이며, 협정 세계시(UTC)로 1582년 10월 15일 00:00:00.00(the date of Gregorian reform to the Christian calendar) 이후 100 nanosecond 간격의 현재 시간을 표현합니다.

아주 우연히도 중복될 소지에 대비하여, 버전 1에서는 타임스템프 외 추가적으로 Clock Sequence 값을 포함하고 있으며, Clock이 시간적으로 거꾸로 설정되거나 노드 ID가 변경될 때 발생할 수 있는 중복을 방지하는 데 사용됩니다. 즉 중복 발생 가능성이 매우 적다고 할 수 있습니다. 


버전 4는 랜덤한 128비트를 생성 합니다. 생성 원리상 중복은 가능할 수 있습니다. 다만 중복 될 수 있는 가능성이 매우 희박합니다.

UUID 는 총 340,282,366,920,938,463,463,374,607,431,768,211,456 개 생성 가능 하며, 매 초 마다 10억개의 UUID를 85년에 걸쳐서 생성 하였을 경우, 단 하나 이상의 UUID가 중복될 확률은 50% 라고 하니 사실은 버전 4도 중복이 될 실질적 확율은 매우 희박 하다고 할 수 있습니다.

v1 과 v4 를 비교 하자면 중복 발생 가능성이 상대적으로 적은 것은 v1 이라고 할 수 있습니다. 다만 버전 1은 시간 정보와 Node의 MAC주소(48비트) 주소 정보를 포함하고 있기 때문에 관련된 정보가 노출은 될 수 있으나 사실 2개의 정보가 큰 문제가 된다기 보다는 시간정보와 MAC주소의 조합으로 다른 UUID 를 유추할수 있는 소지는 있긴 합니다만 그래도 AUTO_INCREMENT 에 비해서는 상대적으로 PK 키 값을 예측이나 유추 하기는 어렵습니다.


그렇다면 시간 기반인 버전1 과 완전한 무작위(랜덤) 인 버전4 중에 어떤 것을 사용을 해야 할지에 대해서는 성능적인 부분도 고려가 필요 합니다.

일단 먼저 위에서 언급한 내용처럼 MySQL 에서 제공되는 내장 uuid 함수는 버전 1 입니다. 버전4를 사용하기 위해서는 애플리케이션에서 구현하거나 별도의 프로시저를 작성해서 사용해야 합니다. 관련된 정보(소스)는 stackoverflow.com 등에서 쉽게 찾을 수 있습니다. 

AUTO_INCREMENT 와 버전 1 과 버전 4 에 대해서 대량의 데이터를 INSERT 또는 로드 하는 경우와 기존의 많은 데이터가 있을 경우 등을 고려 해야 합니다.

버전 4가 중복이 발생할 소지가 매우 적고 유니크한 값을 생성 할 수 있으며 유추하기 어려운 값을 사용한다는 점 등의 장점이 있으나, 버전4는 완전 랜덤한 값을 생성하기 때문에 INSERT 가 많아지면 저장된 데이터에 대한 PK 정렬(sort) 로 인해서 성능 저하가 발생 할 수 있습니다.

[performance when using uuid for primary key]

위의 차트는 100,000 개 데이터를 입력시 AI 와 UUID 별로의 소요 시간을 측정한 내용입니다.

기본적인 버전 4는 완전한 임의의 값(랜덤) 이기 때문에 버전1에 비해서 정렬에 대한 작업 오버헤드가 크게 발생하게 됩니다.

기본 버전 4가 아닌 변형된 형태로 응용된 형태로 작성한 코드를 이용해서 랜덤한 UUID는 생성 하면서 Sequential 하게 생성 하였을 경우에는 기본적인 버전 4와 다르게 정렬에 대한 오버헤드가 줄어들어서 AUTO_INCREMENT 나 버전 1과의 차이가 없는 것을 확인 할 수 있습니다.


위와 같은 배치 INSERT 는 짧은 시간안에 데이터를 입력하는 경우임으로 보통의 경우 uuid 버전1 이라도 순차적으로 생성되어 테이블에 입력 할 수 있습니다.

하지만 실제 사용하는 경우, 즉 데이터가 불규칙한 간격으로 또는 텀을 가지고(간격을 가지고) 입력하게 된다면 위에서 테스트로 데이터를 입력한 내용처럼 중간에 삽입될 수 있어서 정렬에 대한 오버헤드가 발생할 수 있습니다.
(loop 프로시저 등으로 테스트 하면 짧은 시간 계속 데이터를 입력하기 때문에 보통의 경우 순차적인 UUID 를 획득하여 입력하게 됩니다.)

그래서 UUID 사용시 입력에 대한 부분으로 Sequential 또는 정렬된 결과로 입력을 할 수 있는 부분이 중요한 고려 사항이라고 할 수 있겠습니다.
                 

Action Plan(Solutions)

UUID 를 사용 하였을 때 장점이 있는 것 만큼, MySQL 에서 사용하기 위해서는 고려 할 점과 해결해야 할 점이 있는 부분을 위에서 설명하였고, 몇 가지 방안으로 UUID 사용시 효율적으로 할 수 있는 부분에 대해서 확인 해 보도록 하겠습니다.


먼저 길이와 관련된 사이즈 문제 입니다. MySQL 의 Secondary Index 는 PK 컬럼 값을 포함하기 때문에 PK 컬럼의 길이가 길어지는 만큼 Secondary Index 의 크기도 증가하게 되는 구조라서 적절한 PK컬럼 사이즈를 사용하는데 고려가 필요 합니다.

mysql> explain
    -> select * from tb_test
    -> where uuid='c896700e-4ee6-11ed-a207-08002722a50f'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 146
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

위에서 테스트를 위해서 생성한 테이블에서는 UUID 값을 담기 위해서 varchar(36) 으로 생성 하였고, 키의 길이가 레코드 별 146바이트를 사용한다는 것을 확인 할 수 있었습니다.

그래서 익히 많이 알려진 내용과 같이 UUID 를 사용할 경우 컬럼 사이즈를 줄이기 위해서 BINARY 컬럼 타입을 사용해야 합니다.

MySQL 5.7 에서는 보통 사용하는 방법과 같이 hex, unhex 를 사용하여 데이터를 입력하고 조회를 하면 됩니다.

create table tb_test2 ( 
uuid binary(16) default (uuid()) primary key, 
first_name varchar(15), emp_no int unsigned)
engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci;

insert into tb_test2(uuid,first_name,emp_no) 
values (unhex(replace(uuid(),'-','')),'Georgi',10001);


---- 또는 default 에 함수를 설정  ----


create table tb_test2 ( 
uuid binary(16) default (unhex(replace(uuid(),'-',''))) primary key, 
first_name varchar(15), emp_no int unsigned)
engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci;

insert into tb_test2(first_name,emp_no) values ('Mary',10002);



-- 데이터 조회
select hex(uuid),first_name,emp_no from tb_test2;
+----------------------------------+------------+--------+
| hex(uuid)                        | first_name | emp_no |
+----------------------------------+------------+--------+
| C1435B5A4F2311EDA20708002722A50F | Georgi     |  10001 |
| C59F061A4F2311EDA20708002722A50F | Mary       |  10002 |
+----------------------------------+------------+--------+

컬럼타입을 binary(16) 으로 변경 하게 되면 varchar 에 비해서 많은 용량을 줄일 수 있게 됩니다.

mysql> explain
select hex(uuid),first_name,emp_no 
from tb_test2 where uuid=unhex('9B2512EF4F2411EDA20708002722A50F')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test2
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 16
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

하지만 여전히 항상 순서를 보장하며 입력 되지는 않기 때문에, 기존에 입력 되었던 데이터 중간에 입력 될 수 있고 그에 따라서 기존 레코드의 이동이 발생할 수 도 있습니다.

insert into tb_test2(first_name,emp_no) values ('Saniya',10003);

select hex(uuid),first_name,emp_no from tb_test2;
+----------------------------------+------------+--------+
| hex(uuid)                        | first_name | emp_no |
+----------------------------------+------------+--------+
| 9B2512EF4F2411EDA20708002722A50F | Saniya     |  10003 | <--!!
| C1435B5A4F2311EDA20708002722A50F | Georgi     |  10001 |
| C59F061A4F2311EDA20708002722A50F | Mary       |  10002 |
+----------------------------------+------------+--------+

표준 UUID 는 순차적이지 않음으로 로직과 방법이 포함된 개발 언어 영역에서 별도로 구현해서 UUID 를 순차적으로 만들어서 사용하기도 합니다.

MySQL 버전 5.7까지는 MySQL 자체적으로 순차적으로 UUID 를 생성할 수 없었기 때문에 위와 같이 별도로 구현을 해서 사용해야 했으나 MySQL 버전 8.0 에서는 UUID 와 관련된 3가지 Native Function 이 지원됨에 따라서 이러한 고민도 해결 할 수 있게 되었습니다.

• UUID_TO_BIN
• BIN_TO_UUID
• IS_UUID

UUID_TO_BIN() 함수는 UUID 를 binary 로 convert 해주는 함수 입니다. 반환된 바이너리 UUID는 VARBINARY(16)값이 되게 됩니다.

UUID_TO_BIN 함수에는 두번째 인자값을 선택할 수 있으며 0 또는 1 을 선택할 수 있습니다.

• swap_flag 가 0 이면 생성된 binary 결과는 생성된 uuid 문자열 과 같은 순서로 생성됩니다.
• swap_flag 가 1 이면 반환 값의 형식이 달라지게 됩니다. 시간이 낮은 부분과 시간이 높은 부분(각각 16진수의 첫 번째 및 세 번째 그룹)을 교환하게 됩니다. 이렇게 하면 더 빠르게 변하는 부분이 오른쪽으로 이동하고 결과가 인덱싱된 열에 저장되는 경우 인덱싱 효율성이 향상될 수 있습니다.

테스트 테이블 생성 및 데이터를 입력 후 조회를 해보도록 하겠습니다.

-- 테이블 생성 : UUID_TO_BIN(UUID(),1)
create table tb_test3 ( 
uuid binary(16) default (UUID_TO_BIN(UUID(),1)) primary key, 
first_name varchar(15), emp_no int unsigned)
engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci;


-- 데이터 입력
insert into tb_test3(first_name,emp_no) values ('Georgi',10001);
insert into tb_test3(first_name,emp_no) values ('Mary',10002);
insert into tb_test3(first_name,emp_no) values ('Saniya',10003);
insert into tb_test3(first_name,emp_no) values ('Sumant',10004);
insert into tb_test3(first_name,emp_no) values ('Duangkaew',10005);
insert into tb_test3(first_name,emp_no) values ('Patricio',10006);


-- 데이터 조회
mysql> select hex(uuid),first_name,emp_no
from tb_test3;
+----------------------------------+------------+--------+
| hex(uuid)                        | first_name | emp_no |
+----------------------------------+------------+--------+
| 11ED4F5A366CD3C3A20708002722A50F | Georgi     |  10001 |
| 11ED4F5A89BD4D38A20708002722A50F | Mary       |  10002 |
| 11ED4F5A8ED8C0C8A20708002722A50F | Saniya     |  10003 |
| 11ED4F5C16AAD10DA20708002722A50F | Sumant     |  10004 |
| 11ED4F5D5B56C1E8A20708002722A50F | Duangkaew  |  10005 |
| 11ED4F5E2CDE8B13A20708002722A50F | Patricio   |  10006 |
+----------------------------------+------------+--------+

UUID_TO_BIN 함수를 사용하여 테이블을 만들었으며 두번째 인자값으로 1을 사용 하였습니다. 그래서 입력된 UUID 값을 보면 정렬을 위한 변환된 UUID 가 입력되어 있는 것을 확인할 수 있습니다.


BIN_TO_UUID() 함수는 Binary 형으로 입력되어 있는 바이너리 UUID 값을 문자열 UUID 로 반환합니다.

함수에서 두 번째 인자로 0 과 1 을 입력 할 수 있습니다.

• swap_flag 가 0 이면 입력된 UUID 인수의 형식과 반환되는 UUID 형식이 같게 출력 됩니다.(아래 출력 결과 참조)
• swap_flag 가 1 이면 UUID 값은 낮은 시간 부분과 높은 시간 부분이 교환된 것으로 가정합니다. 이 출력값은 원래 위치로 위치가 교환된 값이 출력 됩니다(입력시 원래 값)

mysql> select bin_to_uuid(uuid,0),bin_to_uuid(uuid,1),first_name
from tb_test3;
+--------------------------------------+--------------------------------------+------------+
| bin_to_uuid(uuid,0)                  | bin_to_uuid(uuid,1)                  | first_name |
+--------------------------------------+--------------------------------------+------------+
| 11ed4f5a-366c-d3c3-a207-08002722a50f | 366cd3c3-4f5a-11ed-a207-08002722a50f | Georgi     |
| 11ed4f5a-89bd-4d38-a207-08002722a50f | 89bd4d38-4f5a-11ed-a207-08002722a50f | Mary       |
| 11ed4f5a-8ed8-c0c8-a207-08002722a50f | 8ed8c0c8-4f5a-11ed-a207-08002722a50f | Saniya     |
| 11ed4f5c-16aa-d10d-a207-08002722a50f | 16aad10d-4f5c-11ed-a207-08002722a50f | Sumant     |
| 11ed4f5d-5b56-c1e8-a207-08002722a50f | 5b56c1e8-4f5d-11ed-a207-08002722a50f | Duangkaew  |
| 11ed4f5e-2cde-8b13-a207-08002722a50f | 2cde8b13-4f5e-11ed-a207-08002722a50f | Patricio   |
+--------------------------------------+--------------------------------------+------------+

조회 결과는 위에서 테이블을 생성시 UUID_TO_BIN() 함수의 두번째 인자값을 1로 설정하고 입력한 테이블 데이터 입니다.

위의 조회 결과에서 확인 할 수 있는 것처럼 두번자 인수(인자)에 대해서 0을 사용하게 되면 Sequential 하게 입력하기 위해서 순서가 변경된 UUID 값 그대로가 표현 되고, 1 을 사용하게 되면 자리를 다시 교환(swap) 함으로써 원래의 값으로 출력 되게 됩니다.

그래서 두번째 컬럼 인 bin_to_uuid(uuid,1) , 즉 변환되기 이전 값으로 order by 를 사용하여 정렬하여 출력하면 입력 순서가 랜덤하다는 것을 알 수 있습니다.

mysql> select bin_to_uuid(uuid,0),bin_to_uuid(uuid,1),first_name,emp_no
from tb_test3
order by 2;
+--------------------------------------+--------------------------------------+------------+--------+
| bin_to_uuid(uuid,0)                  | bin_to_uuid(uuid,1)                  | first_name | emp_no |
+--------------------------------------+--------------------------------------+------------+--------+
| 11ed4f5c-16aa-d10d-a207-08002722a50f | 16aad10d-4f5c-11ed-a207-08002722a50f | Sumant     |  10004 |
| 11ed4f5e-2cde-8b13-a207-08002722a50f | 2cde8b13-4f5e-11ed-a207-08002722a50f | Patricio   |  10006 |
| 11ed4f5a-366c-d3c3-a207-08002722a50f | 366cd3c3-4f5a-11ed-a207-08002722a50f | Georgi     |  10001 |
| 11ed4f5d-5b56-c1e8-a207-08002722a50f | 5b56c1e8-4f5d-11ed-a207-08002722a50f | Duangkaew  |  10005 |
| 11ed4f5a-89bd-4d38-a207-08002722a50f | 89bd4d38-4f5a-11ed-a207-08002722a50f | Mary       |  10002 |
| 11ed4f5a-8ed8-c0c8-a207-08002722a50f | 8ed8c0c8-4f5a-11ed-a207-08002722a50f | Saniya     |  10003 |
+--------------------------------------+--------------------------------------+------------+--------+

마지막 컬럼 emp_no 나 first_name 을 보시면 이전에 PK 컬럼 기준으로 정렬 된 조회한 결과와 다르다는 것을 확인 할 수 있습니다.


IS_UUID() 함수는 UUID 문자열에 대해서 UUID 가 맞는지(유효한지)를 확인 하는 함수 입니다.

인수가 유효한 문자열 형식 UUID 이면 1을 반환하고, 인수가 유효한 UUID가 아니면 0을 반환하고, 인수가 NULL이면 NULL을 반환합니다.

• 정상적인 UUID 가 입력 되었을 경우

mysql> SELECT IS_UUID('6ccd780c-baba-1026-9564-5b8c656024db');
+-------------------------------------------------+
| IS_UUID('6ccd780c-baba-1026-9564-5b8c656024db') |
+-------------------------------------------------+
|                                               1 |
+-------------------------------------------------+

• UUID 형식에 맞지 않는 잘못된 UUID 가 입력 되었을 경우

mysql> SELECT IS_UUID('6ccd780c-baba-1026-9564-5b8c6560');
+---------------------------------------------+
| IS_UUID('6ccd780c-baba-1026-9564-5b8c6560') |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+

위와 같이 UUID 에 대해서 validate check 기능을 하는 함수 입니다.


MySQL 에서 UUID 를 사용할 경우 고민 또는 고려해야할 부분으로 "컬럼 사이즈", "성능", "중복성" 3가지에 대해서는 다시 정리를 해보자면,

중복성의 경우 사실상 중복이 될 확율은 희박하며, UUID Version 1 을 사용하게 되면 Version 4 에 비해 더욱 더 중복될 가능성이 낮아지게 됩니다. 

UUID 의 긴 자리수에 의한 큰 크기는 binary 형태로 저장함으로써 varchar 형태로 저장하는 것에 비해서 용량을 감소시켜서 사용할 수 있습니다.

성능에 대한 부분은 MySQL 5.7 버전까지는 별도로 구현한 순서를 보장하는, 즉 Sequential 한 UUID 를 생성하여 입력하는 방식이나 다른 방법에 대해서 고려가 필요 하였으나 MySQL 8.0 에서 부터 지원되는 Native 함수인 UUID_TO_BIN() 를 이용하면 Sequential 한 UUID 값을 사용할 수 있어서 성능상으로 고려해야할 점에 대해서도 대응이 가능하며, 사용편의성(별도로 구현이 필요하지 않기 때문에) 측면에서 더욱 좋아졌다라고 할 수 있겠습니다.

MySQL 8.0버전에서 UUID 를 사용을 고려 할 경우 추가된 UUID 함수를 사용 한다면, 적용을 위해 고려 해야하는 많은 부분들을 해소 할 수 있을 것이라고 생각 합니다.
 

이번 포스팅은 여기서 마무리 하겠습니다.
               

Reference

Reference URL
• rfc-editor.org/rfc/rfc4122
• wikipedia.org/범용 고유 식별자
• wikipedia.org/Universally_unique_identifier
• blog.programster.org/performance-when-using-uuid-for-primary-key
• mysql.com/storing-uuid-values-in-mysql-tables
• lefred.be/mysql-uuids
• mysql.com/miscellaneous-functions


관련된 다른 글

MySQL - InnoDB Cluster(클러스터) - (1) - 아키텍처 - 그룹 복제(Group Replication)

안녕하세요 이번 포스팅에서는 InnoDB Cluster(클러스터) 에 대해서 InnoDB 의 아키텍처 및 구성 및 설정에 관해서 확인 해보도록 하겠습니다.

MySQL - 확장 검색 - 전문검색 FullText Search - Real MySQL 8.0

안녕하세요 이번 포스팅은 MySQL 의 전문검색(Fulltext Search) 기능에 대해서 확인 해보려고 합니다. 전체적인 내용은 Real MySQL 8.0 책 과 MySQL Document 를 정리 한 내용 입니다.

MySQL 8.0 - User 생성 구문 Comment 와 Attribute

안녕하세요 이번 포스팅에서는 MySQL 8.0 버전에서의 User Comment 와 User Attribute 기능에 대해서 내용을 확인 해보려고 합니다. Comment 와 Attribute MySQL 8.0.21 버전에서 user 생성이나 변경(alter) 시 user comment 와 user attribute 를 추가할 수 있는 기능이 추가 되었습니다.

MySQL - InnoDB Page Tracking 을 사용한 Xtrabackup- 변경 사항 추적 기능

안녕하세요 이번 포스팅은 MySQL 8버전에서 추가된 InnoDB Page Tracking 기능 과 Xtrabackup 을 활용하는 방법에 대해서 확인해보려고 합니다.

MySQL Invisible Columns 을 사용한 table audit 기능 활용

안녕하세요. 이번 포스팅은 MySQL Invisible Columns 을 활용한 table audit 내용 기록과 관련된 내용을 살펴보도록 하겠습니다.포스팅은 lefred.be 사이트에서 다룬 내용을 번역 및 테스트를 진행한 내용으로 작성되어 있습니다.

MySQL - PK가 없을 경우 복제 지연 현상과 8.0의 REQUIRE_TABLE_PRIMARY_KEY_CHECK 기능

안녕하세요. 이번 포스팅에서는 PK가 없는 테이블을 사용하는 환경에서의 복제 지연 현상과 8.0 버전에서 새로 추가된 REQUIRE_TABLE_PRIMARY_KEY_CHECK 기능에 대해서 확인 해보려고 합니다.

MySQL timestamp 와 Y2K38 Problem

Y2K, Y2K20, Y2K38, MySQL Timestamp

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io