MySQL 콜레이션 (feat. 정렬, 비교)

회사 업무 중 MySQL5.7을 사용하던 당시 생성된 테이블과
이후 생성된 테이블의 COLLATION이 일치하지 않아,
두 컬럼의 JOIN 시 에러가 발생하는 상황이 있었다. 

둘 중 하나의 컬럼의 COLLATION을 명시적으로 변경하여야 JOIN이 가능했다.

 

이러한 이슈가 다시 발생하지 않도록 콜레이션에 대해 알아보자.

콜레이션이란

콜레이션은 문자열 컬럼의 값에 대한 비교나 정렬 순서를 위한 규칙을 의미한다.

비교나 정렬 작업에서

• 영어 대소문자를 같은 것으로 처리할지
• 더 크거나 작은 것으로 판단할지

에 대한 규칙을 정의하는 것이다.

 

따라서 각 문자열 컬럼의 값을 비교하거나 정렬할 때는

문자 집합뿐 아니라 콜레이션의 일치 여부에 따라 결과가 달라지며

쿼리의 성능 또한 상당한 영향을 받는다.

 

콜레이션의 특징

• 하나의 문자 집합에 속한 콜레이션은 다른 문자 집합과 공유해서 사용할 수 없다.
• 테이블이나 컬럼에 문자 집합만 지정하면 해당 문자 집합의 디폴트 콜레이션이 해당 컬럼의 콜레이션으로 지정된다.
• 반대로 문자 집합을 지정하지 않고 콜레이션만 지정하면 해당 콜레이션이 소속된 문자 집합이 묵시적으로 그 컬럼의 문자 집합으로 사용된다.

 

콜레이션의 이름 구성

• utf8_bin
• utf8mb4_general_ci

일반적으로 콜레이션의 이름은 2개 또는 3개의 파트로 구분돼 있고, 각 파트는 다음과 같은 의미로 사용된다.

 

• 3개의 파트로 구성된 콜레이션 이름
  • 첫 번째 파트는 문자 집합의 이름이다.
  • 두 번째 파트는 해당 문자 집합의 하위 분류를 나타낸다.
  • 세 번째 파트는 대문자나 소문자의 구분 여부를 나타낸다. ci면 case insensitive로 대소문자를 구분하지 않고, cs 면 case sensitive로 대소문자를 별도의 문자로 구분한다.
• 2개의 파트로 구성된 콜레이션 이름
  • 첫 번째 파트는 문자 집합의 이름이다.
  • 두 번째 파트는 항상 "bin"이라는 키워드가 사용된다. "bin"은 이진 데이터(binary)를 의미하며, 이진 데이터로 관리되는 문자열 컬럼은 별도의 콜레이션을 가지지 않는다. 비교 및 정렬은 실제 문자 데이터의 바이트 값을 기준으로 수행된다.

 

문자열 컬럼의 특성

CHAR, VARCHAR와 같은 문자열 컬럼에서는 

타임의 이름과 길이만 같다고 해서 똑같은 타입이 아니다.

• 타입의 이름
• 문자열의 길이
• 문자 집합과 콜레이션

까지 일치해야 똑같은 타입이라고 할 수 있다.

문자 집합과 콜레이션이 모두 일치해야만

조인이나 WHERE 조건이 인덱스를 효율적으로 사용할 수 있다.

 

문자 집합이나 콜레이션이 다르다면 비교 작업에서 콜레이션의 변환이 필요하기 때문에

인덱스를 효율적으로 이용하지 못할 때가 많으므로 주의해야 한다.

(콜레이션 이슈가 있었던 사내 DB 테이블들은 JOIN 시 인덱스를 사용하지 못하고 있었던 것이다.)

 

정렬과 검색 기준이 다를 때

때로는 WHERE 조건의 검색은 대소문자를 구분하지 않고 실행하되,

정렬은 대소문자를 구분해서 해야 할 때도 있다.

 

이 때는 검색과 정렬 중 하나는 인덱스를 사용하는 것을 포기할 수 밖에 없다.

주로 이러한 상황에서는 콜레이션을 "_ci" (case insensitive)로 만들어 검색은 인덱스를 사용하도록 하고,

정렬은 인덱스를 사용하지 않는 명시적 정렬(using filesort) 형태로 처리한다.

 

검색과 정렬 모두 인덱스를 이용하는 방법은 

정렬을 위한 콜레이션을 사용하는 컬럼을 하나 더 추가하고

검색을 위한 원본 컬럼과 동일한 데이터를 항상 복사하여 채워두는 것이다.

 

데이터의 양이나 업무의 중요도를 적절히 반영한 선택이 필요하다.

 

NO PAD vs PAD SPACE

문자열 뒤의 공백이 비교 결과에 영향을 미치는지는

information_schema 데이터베이스의 COLLATIONS 뷰에서 PAD_ATTRIBUTE 컬럼의 값으로 판단할 수 있다.

• PAD SPACE: 비교 대상 문자열의 길이가 같아지도록 문자열 뒤에 공백을 추가해서 비교를 수행한다.
• NO PAD: 별도로 문자열의 길이를 일치시키지 않고 그대로 비교한다.

MySQL에서 지원하는 대부분의 콜레이션은 PAD SPACE이며, utf8mb4_0900으로 시작하는 콜레이션만 NO PAD다.

LIKE를 사용한 문자열 패턴 비교에서는 공백 문자가 유효 문자로 취급된다.

 

참고 자료

• Real MySQL 8.0 2 - 15.1 문자열 (CHAR와 VARCHAR)