collation | COLLATE 란 무엇일까

회사에서 업무를 하던 중

2건의 결과가 나오는 조회 쿼리가 5분이라는 수행시간이 걸리는 문제가 발생했다.

 

왜 이렇게 오래 걸리는지 원인을 찾기 위해 쿼리를 분석하던 와중에

"COLLATE"라는 키워드를 발견하였다.

 

찾아보니 COLLATE는 특정 문자열에 대해 

정렬/비교 시 collation을 강제로 지정하는 SQL 키워드였다.

 

여기서 collation은 문자열의 정렬 방식과 비교 방식을 결정하는 규칙인데

동일하지 않은 collation끼리 비교, 정렬하는 경우 같은 규칙으로 비교하기 위해

강제로 형변환을 해줄 때 COLLATE 키워드를 SQL에 사용한다.

 

 

문제가 발생했던 쿼리에서는

JOIN 시 ON 절에 적는 두 컬럼의 COLLATE가 동일하지 않아서

아래 SQL 처럼 COLLATE 키워드를 적어 형변환을 해주고 있었다.

SELECT a.id, b.name
FROM A a
INNER JOIN B b
    ON a.id = b.id COLLATE utf8_unicode_ci; -- B 테이블의 id 컬럼을 utf8_unicode_ci로 강제 형변환

 

 

수백 만건의 데이터가 쌓여있는 테이블을

이렇게 해당 컬럼의 모든 데이터를 형변환을 해가며

비교를 하고 있어서

수행 시간이 오래걸리고 있는 것이었다.

 

DB 컬럼 속성을 변경하는 것은 큰 일이기 때문에 아직 반영하지 못해서

COLLATE를 일치시켜주고 난 이후에 결과는 반영 이후에 추가로 글을 작성하겠다.

 

 

 

그래서

COLLATION란 무엇일까?

 

 

 

위에서 말한대로 collation은 문자열의 정렬 방식과 비교 방식을 결정하는 규칙이다.

데이터베이스, 테이블, 컬럼 단위에 적용할 수 있다.

 

규칙이 다르기 때문에 서로 다른 collation 인 컬럼끼리는 비교할 수가 없다.

collation을 적용하면 각 문자는 내부적으로 가중치(weight) 값을 가지며, 이 값에 따라 정렬 순서가 정해진다.

 

만약 대소문자 구분이 없는 collation을 설정한 경우, A와 a는 같은 가중치를 가진다.

 

 

 

 

가중치(WEIGHT)가 중요한 이유

 

가중치(weight)는 각 문자에 할당된 내부 값으로, 가중치 값을 기준으로 정렬 순서와 비교 결과가 정해진다.

대소문자 구분이 없는(collation이 ci인) 경우, A와 a는 같은 가중치를 가진다.

 

아래 예제에서 collation별로 a와 A를  어떻게 비교하는지 볼 수 있다.

SELECT 'a' = 'A' COLLATE utf8_general_ci; -- 결과: 1 (같음)
SELECT 'a' = 'A' COLLATE utf8_bin; -- 결과: 0 (다름)

 

utf8_general_ci는 대소문자 구분이 없는 방식인 것이고

utf8_bin은 대소문자를 구분하는 방식인 것이다.

 

 

MySQL/MariaDB에서는 WEIGHT_STRING() 함수를 사용하여 특정 문자열의 가중치를 확인할 수 있다.

 

 

따라서 테이블 생성 시 시스템에서 대소문자를 구분하는지

  ('a'),('A'),('À'),('á') 이런 문자들을 모두 동일하게 처리할 것인지 구분할 것인지

잘 판단하여 collation을 설정해야 한다.

 

 

COLLATE을 ON 절에서 매번 변환하면 느려지는 이유

1. 인덱스가 제대로 활용되지 않음
   1. MySQL은 ON 절에서 COLLATE를 적용하면 인덱스를 무시하고 풀 테이블 스캔을 수행할 가능성이 높다.
   2. PRIMARY KEY, FOREIGN KEY는 인덱스를 활용한 빠른 조인이 가능하지만,
      COLLATE 변환이 들어가면 모든 값을 변환한 후 비교해야 하므로 느려질 수 있다.
2. 대량의 데이터가 존재할수록 데이터 변환에 따른 연산 비용 증가
   1. 비교, 정렬하기 위한 컬럼의 collation이 강제로 변환된 후 비교되므로 추가적인 연산 비용이 발생한다.
   2. collation별로 문자의 가중치가 다르기 때문에 더 복잡한 정렬 규칙을 가진 collation으로 변환하는 과정에서 더 오랜 시간이 소요될 수 있다. 

 

 

느낀 점

 

DB에 문자열을 비교하는 방식이 지정되어 있다는 것을 몰랐다.

 

사용하는 언어, 환경에 따라 다른 collation이 필요하기 때문에 

다른 회사 혹은 나라에 따라서 collation이 다를 수 있구나 깨달았다.

 

이런 언어, 환경에 맞춰서 좀 더 효율적으로 쉽게 조회하고

DB를 관리하기 위해 만들어진게 collation이 아닐까 싶다.

 

DB는 진짜 어렵다.

 

 

 

 

https://dev.mysql.com/doc/refman/8.4/en/charset-collation-implementations.html

MySQL :: MySQL 8.4 Reference Manual :: 12.14.1 Collation Implementation Types