MySQL의 CHARSET과 COLLATION

 

 

MySQL에서 테이블을 생성할 때, 캐릭터셋(CHARSET)과 콜레이션(COLLATION)을 설정할 수 있습니다.

캐릭터셋과 콜레이션은 테이블뿐만 아니라 컬럼 단위로도 설정이 가능합니다.

이러한 설정을 잘못하면 조인 SQL 자체가 실행이 안될 수도 있으며, 이는 성능 저하로 연결될 수도 있습니다.

 

캐릭터셋과 콜레이션은 데이터베이스에서 문자 데이터를 저장하고 비교하는 방식을 뜻합니다.

캐릭터셋(Character Set)

• 데이터베이스에 저장할 수 있는 문자들의 집합
• 각 문자셋은 다양한 문자를 인코딩하는 코드를 정의합니다. UTF-8, ASCII, LATIN1등이 있습니다.
• UTF-8은 국제적으로 널리 사용되는 캐릭터셋입니다. 거의 모든 언어와 이모지까지 지원합니다.
• 캐릭터셋에 따라 같은 텍스트도 다른 방식으로 저장될 수 있습니다.

콜레이션(Collation)

• 문자 데이터를 비교하고 정렬할때 사용하는 규칙의 집합입니다.
• 대소문자 구분, 악센트 구분, 문자의 정렬 순서 등을 정의합니다.
• 콜레이션에 따라 언어와 문자의 우선 순위가 달라질 수 있습니다.

 

이러한 캐릭터셋과 콜레이션은 MySQL이 설치되면서 서버 레벨(DBMS)에서 정의가 됩니다.

my.cnf나 my.ini에 기본으로 값이 설정되어 있으며, 변경이 가능합니다.

현재 운영중인 시스템에서 서버레벨에서 이 설정을 변경하는 순간 매우 큰 혼란이 만들어지게 됩니다.

서버 레벨에서 정의한 캐릭터셋과 콜레이션은 데이터베이스를 생성할 때 상속됩니다.

그리고 데이터베이스에 만들어진 캐릭터셋과 콜레이션은 다시 테이블에 상속됩니다.

캐릭터셋과 콜레이션이 이미 상속되어 테이블이 만들어진 상태에서 이와 같은 설정을 변경한다면 어떤 여파가 있을지 상상하기도 힘듭니다. 그러므로 캐릭터셋과 콜레이션은 서비스를 시작하기 전에 정의되어야 합니다. 아니, 개발을 시작하기 전 설계 단계에서부터 정의되어야 맞습니다.

 

 

MySQL 관련된 블로그나 자료를 찾다 보면, 테이블을 생성할 때, 캐릭터셋이나 콜레이션을 지정해서 생성하는 스크립트를 볼 수 있습니다. 이러한 스크립트를 카피해서 사용하다 보면, 예상하지 못한 문제에 마주칠 수 있습니다.

먼저, 자신이 접속한 데이터베이스의 캐릭터셋과 콜레이션을 살펴봅니다.

# 사용할 기본 데이터베이스 변경하기
USE MYTUNDB;

# 현재 계정이 사용하는 기본 데이터베이스 확인하기
SHOW VARIABLES LIKE 'character_set%';
-- character_set_database   utf8mb4
-- character_set_connection utf8mb4

SHOW VARIABLES LIKE 'collation%';
-- collation_database   utf8mb4_0900_ai_ci
-- collation_connection utf8mb4_0900_ai_ci

 

위와 같이 기본 캐릭터셋과콜레이션이 있음에도 불구하고, 아래와 같이 COLLATE를 기본과 다른 설정으로 생성을 해봅니다. 인터넷을 검색하다 알게된걸 그냥 카피해서 사용한 것이죠. 데이터베이스의 기본 콜레이트는 utf8mb4_0900_ai_ci이지만, 아래 테이블은 utf8mb_unicode_ci 를 사용했습니다. 

CREATE TABLE MYTUNDB.T_ORD_TEST
    (
        ORD_SEQ               BIGINT UNSIGNED  NOT NULL COMMENT'주문번호',
        CUS_ID                VARCHAR(40)  NOT NULL COMMENT '고객ID',
        ORD_DT                DATETIME  NULL COMMENT '주문일시',
        ORD_ST                VARCHAR(40)  NULL COMMENT '주문상태',
        PAY_DT                DATETIME  NULL COMMENT '결제일시',
        PAY_TP                VARCHAR(40)  NULL COMMENT '결제유형',
        ORD_AMT               DECIMAL(18,3)  NULL COMMENT '주문금액',
        PAY_AMT               DECIMAL(18,3)  NULL COMMENT '결제금액',
        PRIMARY KEY(ORD_SEQ)
    ) 
CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci 
COMMENT '주문테스트';

-- 테스트를 위한 데이터 입력 및 인덱스 생성
INSERT INTO MYTUNDB.T_ORD_TEST SELECT* FROM T_ORD;
CREATE INDEX T_ORD_TEST_X01 ON T_ORD_TEST(CUS_ID);

 

이제 기존에 원래 있던 테이블과 조인 SQL을 만들어 실행해 봅니다.

SELECT  *
FROM    MYTUNDB.M_CUS T1
        INNER JOIN MYTUNDB.T_ORD_TEST T2 ON (T2.CUS_ID = T1.CUS_ID)
WHERE   T1.CUS_ID = 'CUS_0001'

 

 위 SQL을 실행해보면 다음과 같은 에러를 만납니다.

Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

 

두 테이블(M_CUS와 T_ORD_TEST)간에 조인 조건으로 사용한 CUS_ID가 테이블별로 다른 콜레이트를 사용하기 때문에 발생한 에러입니다. 두 테이블의 CUS_ID에 대한 콜레이트는 아래 SQL로 확인할 수 있습니다.

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM   INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('M_CUS','T_ORD_TEST') AND COLUMN_NAME = ('CUS_ID')
AND    TABLE_SCHEMA = 'MYTUNDB';


TABLE_NAME|COLUMN_NAME|CHARACTER_SET_NAME|COLLATION_NAME    |
----------+-----------+------------------+------------------+
m_cus     |CUS_ID     |utf8mb4           |utf8mb4_0900_ai_ci|
t_ord_test|CUS_ID     |utf8mb4           |utf8mb4_unicode_ci|

 

이와 같이 콜레이트가 다른 상황에서 SQL을 실행하기 위한 임시 방편은 아래와 같이 조인시 콜레이트를 재정의하는 것입니다. 아래는 T1(M_CUS)의 콜레이트를 T2(T_ORD_TEST)와 동일하게 변경한 경우입니다.

EXPLAIN ANALYZE
SELECT  *
FROM    MYTUNDB.M_CUS T1
        INNER JOIN MYTUNDB.T_ORD_TEST T2 ON (T2.CUS_ID = T1.CUS_ID COLLATE utf8mb4_unicode_ci)
WHERE   T1.CUS_ID = 'CUS_0001'
;

-> Index lookup on T2 using T_ORD_TEST_X01 (CUS_ID=('CUS_0001' collate utf8mb4_unicode_ci))  (cost=11.55 rows=33) (actual time=0.168..0.183 rows=33 loops=1)

 

실행계획을 보면, T_ORD_TEST_X01이라는 인덱스를 사용해 결과를 처리하고 있습니다.

인덱스를 사용한다고 성능이 무조건 좋은건 아니지만, 원하는 방향으로 처리가 되고 있습니다.

만약에 콜레이트를 T1쪽이 아닌 T2쪽의 콜레이트를 변경하면 어떻게 될까요? 아래와 같이 T2(T_ORD_TEST)를 FULL SCAN하는 것을 알 수 있습니다.

EXPLAIN ANALYZE
SELECT  *
FROM    MYTUNDB.M_CUS T1
        INNER JOIN MYTUNDB.T_ORD_TEST T2 ON (T2.CUS_ID COLLATE utf8mb4_0900_ai_ci = T1.CUS_ID)
WHERE   T1.CUS_ID = 'CUS_0001'
;

-> Filter: ((t2.CUS_ID collate utf8mb4_0900_ai_ci) = 'CUS_0001')  (cost=308.70 rows=3047) (actual time=0.373..3.084 rows=33 loops=1)
    -> Table scan on T2  (cost=308.70 rows=3047) (actual time=0.073..2.184 rows=3047 loops=1)

 

결론은!. MySQL에서 테이블을 생성할때, 가능하면 캐릭터셋이나 콜레이트를 별도지정하지 않는 것입니다

그러면 데이터베이스의 기본 캐릭터셋과 콜레이트를 따르게 되어 있습니다.

그러므로 데이터베이스를 생성하는 시점에,  더 나아가서 MySQL을 설치하는 시점에,

앞으로 사용할 캐릭터셋과 콜레이션이 적절한지 검토하고 사용을 시작해야 합니다.

 

 

SQL 튜닝 입문 강의가 필요하신 분은 아래 링크를 참고해주세요.!

 

- StartUP Tuning For MySQL 강의

https://cafe.naver.com/dbian/6958

StartUP Tuning - MySQL을 활용한 SQL 튜닝 입문

 

- StartUP Tuning For PostgreSQL 강의

https://cafe.naver.com/dbian/7181

StartUP Tuning - postgreSQL을 활용한 SQL 튜닝 입문