Portfolio

Understanding How Mysql Collation and Character Settings Impact Performance

Character Set과 Encoding 이해하기 Charset과 Collation 이 쿼리 실행에 미치는 영향 유니코드란? 인코딩 utf-8 (Universal Coded Character Set + Transformation Format - 8bit)euc-kr, cp949 Collation Database CharacterSets and Collations 운영 중 CharacterSet, Collation 변경 TroubleShooting

Character Set과 Encoding 이해하기

CharacterSet은 말그대로 문자열의 집합임 (문자, 숫자, 심볼에 대해 대해 고유한 표현을 가지는)

데이터베이스의 각 컬럼에 대해 character set을 명시할 수 있고, 이는 각 컬럼에 어떤 문자들의 집합이 허용되는지가 결정이 됨

만약, multilingual website를 운영한다면 utf-8 character-set을 사용할 수 있음. utf-8은 영어, 중국어, 아라비아어 등의 다양한 언어를 지원함. 아라비아어와 중국어를 사용하는데 그 문자들을 지원하지 못하는 character set을 이용한다면 문제가 생김

데이터가 database에 저장될 때, 명시된 character set의 binary representation으로 변환이 되고 저장이 됨

database에서 데이터를 조회할 때는, binary representation을 다시 문자로 변환함(character set의 encoding rule에 따라)

Character encoding은 문자들을 바이너리 데이터로 어떻게 표현할지에 사용되는 방법. 인코딩 방식에 따라 character가 어떻게 binary 값으로 바뀌고 다시 돌아오는지가 결정됨

Encoding : 문자열 → bytearray

Decoding: bytearray → 문자열

Charset과 Collation 이 쿼리 실행에 미치는 영향

데이터베이스의 character set이 쿼리에 의해 보내진 데이터의 character set이 맞지 않으면 MySQL 은 실행 중에 character set 변환을 실행해야 함 (쿼리 실행 시간이 느려짐)

collation 셋팅은 문자열 비교에 영향을 미치는데, WHERE과 JOIN 연산을 포함한 쿼리에서 자주 일어남. 효율적이지 않은 collation을 사용하는 것은 최적이 아닌 쿼리 성능으로 실행됨. 예를 들어 collation이 대소문자 구분이 없는데, 대소문자 구분이 있는 쿼리가 대부분이라면 쿼리의 실행속도를 느리게 함

유니코드란?

유니코드 블럭 , ASCII

유니코드란 숫자(비트)와 글자(우리가 직접 보는 문자), 즉 키와 값이 1:1로 매핑된 형태의 코드

ASCII가 0x41 = A 로 매핑된 것처럼 아스키 코드로 표현할 수 없는 문자들을 유니코드라는 이름 아래 전 세계의 모든 문자를 특정 숫자(키)와 1:1 매핑한 것

현재 유니코드는 2^20 + 2^16 개수 만큼의 공간을 사용하고 있어서 왠만한 모든 문자가 다 담겨있다.

U+ 라는 접두어가 붙어있으면 유니코드 라는 의미. 아스키코드의 0x41은 대문자 A이고, 이를 유니코드표에서 찾으면 U+0041이 된다.

인코딩

utf-8이나 utf-16 같은 인코딩 방식은 이 유니코드표의 숫자 키들을 어떻게 표현하는지에 대한 것임

예로, utf-8은 가변 바이트를 사용하기에 1바이트로 표현이 충분한 A 같은 경우는 0x41로 표현함

반면 utf-16은 16비트 즉, 2바이트로 표현하기에 0x0041로 표현

utf-32도 있는데 4바이트로 표현하기 때문에 0x00000041이 됨 (그러나, UTF16과 UTF32의 인코딩 방식의 표현 방법에 따라 조금 다르다)

utf-8 (`U`niversal Coded Character Set + `T`ransformation `F`ormat - `8`bit)

utf-8 wiki

유니코드를 위한 가변 길이 문자 인코딩 방식 중 하나

utf-8 인코딩은 유니코드 한 문자를 나타내기 위해 1바이트에서 4바이트까지를 사용함. 예를 들어 U+0000 부터 U+007F 범위에 있는 ASCII 문자들은 UTF-8에서 1바이트만으로 표시됨

4바이트로 표현되는 문자는 모두 기본 다국어 평면(BMP) 바깥의 유니코드 문자이며, 거의 사용되지 않는다.

euc-kr, cp949

EUC-KR 위키

이 인코딩 방식들은 2바이트로 한글을 표현할 수 있게 만든 방식임. 일부 한자도 포함되고 ASCII 값은 그대로 1바이트로 표현함

오래전부터 쓰이던 게 EUC-KR 이고 이 인코딩에서 표현할 수 없는 한글이 있어 마이크로소프트에서 코드페이지 949를 사용하기 시작함. CP949는 EUC-KR 보다 더 많은 한글을 표현할 수 있으며, 윈도우에서 주로 쓰이는 인코딩 기법임

Collation

[mysql] character set 과 collation 확인하기

[Blog ] Mysql character set 과 collations

Collation is the assembly of written information into a standard order. (WIkipedia)

비교 혹은 정렬을 위한 설정값

콜레이션은 해당 문자셋을 어떻게 정렬할지를 결정하는 알고리즘을 의미하며 다음과 같이 많은 고려 사항이 있다. 대소문자 구분이 안되는 콜레이션으로 컬럼 값을 설정했을 때, 해당 컬럼에 unique constraint 가 적용되어 있다면, east와 East는 unique constraint 로 insert가 불가하게 됨

Collation은 문자열 비교가 어떻게 실행되는지를 결정하고, 그래서 sorting 연산과 검색 연산에서 인덱스가 어떻게 사용되는지에 영향을 미침

대소문자 구분 여부('A' 와 'a')

악센트( 'O' 와 'Ò' )

Locale-specific rules (e.g. 'A' vs. 'Å' vs. 'AA' in Danish and Norwegian)

Numeric characters (e.g. '2' vs. 'ⅱ')

Punctuation (e.g. 'blackbird' vs. 'black-bird')

Database CharacterSets and Collations

[ Mysql ] Database character sets and Collations

운영 중 CharacterSet, Collation 변경

위 sql 과 같이 데이터 변환 시 문제가 생기지 않는 지를 확인해보아야 함

TroubleShooting

Incorrect string value: '\xEF\xBF\xBDc\xEB\xAC...' for column 'name' at row 1

application에서는 utf-8 encoding을 사용하고 DB에는 euc-kr encoding을 사용할때, data update 치려고 하니 위와 같은 에러가 발생했었음

이유 (Chatgpt)

인코딩 차이: 애플리케이션이 UTF-8로 인코딩된 문자열을 생성하고 있는데, 데이터베이스는 EUC-KR로 인코딩된 문자열만 허용합니다. 이로 인해 UTF-8에서 표현 가능한 특정 문자가 EUC-KR에서는 잘못된 바이트 시퀀스로 변환되어 데이터베이스에 삽입할 수 없습니다.

잘못된 문자열 값: 예외 메시지에 있는 '\xEF\xBF\xBD'는 UTF-8에서 지원되지 않는 문자 (일명 “replacement character”)를 나타냅니다. 이는 주로 잘못된 인코딩 변환이나 특정 문자에 대한 지원 부족으로 인해 나타납니다.

해결 방안:

데이터베이스의 문자셋을 UTF-8으로 변경하여 애플리케이션과 일관성을 유지합니다.

애플리케이션에서 데이터를 데이터베이스로 삽입하기 전에 EUC-KR로 인코딩하거나, 해당 문자열이 EUC-KR로 지원되는 문자만 포함하고 있는지 확인합니다.

이상적인 방법은 애플리케이션과 데이터베이스에서 동일한 인코딩을 사용하도록 설정하는 것입니다.

유니코드와 인코딩, Collation