MySQL 콜레이션 사용 시 주의할 점은? 관련

MySQL > Article(s)

---

Article(s)

• 콜레이션 사용 시 주의사항
  • 1) 서로 다른 콜레이션을 가진 컬럼 값 비교 시
  • 2) 쿼리 WHERE 절에서 콜레이션 변경 시
  • 3) 고유키가 콜레이션의 영향을 받는 경우
  • 4) 기본 콜레이션에서의 한글 비교 문제 발생
  • 5) 대소문자 구분을 위한 콜레이션 선정하기
• 마치며

---

MySQL 콜레이션 사용 시 주의할 점은? | 요즘IT

지난 글에서는 전반적인 개요와 함께 MySQL 8.0에 새롭게 추가된 콜레이션들을 위주로 어떻게 동작하는지, 동작 방식과 설정하기 등을 살펴봤는데요. 이번 글에서는 MySQL의 콜레이션 사용 시 주의해야 할 점에 대해 살펴보겠습니다.

IT 지식이 무엇보다 중요해진 요즘, 여러분은 어떻게 공부하고 있나요? 가장 먼저 눈길이 가는 건 다양한 IT 강의 영상일 겁니다. 강의를 제공하는 교육 기업들과 함께, 요즘IT에서 ‘IT 강의 시리즈’를 준비했습니다. 엄선한 교육 영상을 TEXT로 읽고 필요한 정보를 빠르게 가져가세요.

이번 강의는 ‘Real MySQL’입니다. 당근마켓 인프라실 DB팀에서 DBA로 근무하는 이성욱, 백은빈 님이 강의를 맡았습니다. MySQL 8.0 버전을 중심으로 실무에 바로 적용할 수 있는 MySQL 활용법을 다루고 있죠. 전체 영상은 인프런에서 확인할 수 있습니다.

Real MySQL 시리즈

CHAR vs. VARCHAR, 언제 어떻게 써야 할까? | 요즘IT

---

CHAR vs. VARCHAR, 언제 어떻게 써야 할까?

VARCHAR vs. TEXT, 뭐가 다를까? | 요즘IT

---

VARCHAR vs. TEXT, 뭐가 다를까?

MySQL 콜레이션은 어떻게 동작할까? | 요즘IT

---

MySQL 콜레이션은 어떻게 동작할까?

MySQL 콜레이션 사용 시 주의할 점은? | 요즘IT

---

MySQL 콜레이션 사용 시 주의할 점은?

MySQL에서 SQL 문장 가독성 향상시키는 법 | 요즘IT

---

MySQL에서 SQL 문장 가독성 향상시키는 법

지난 글에서는 전반적인 개요와 함께 MySQL 8.0에 새롭게 추가된 콜레이션들을 위주로 어떻게 동작하는지, 동작 방식과 설정하기 등을 살펴봤는데요. 이번 글에서는 MySQL의 콜레이션 사용 시 주의해야 할 점에 대해 살펴보겠습니다.

---

콜레이션 사용 시 주의사항

콜레이션을 사용할 때 주의해야 할 점은 어떤 것들이 있을까요?

1) 서로 다른 콜레이션을 가진 컬럼 값 비교 시

첫 번째로 서로 다른 콜레이션을 가진 컬럼들의 값을 비교하는 경우, 에러가 발생하면서 쿼리가 수행되지 않습니다.

예를 들어, DB에 t1과 t2 두 개의 테이블이 있고, t1 테이블의 col1 컬럼과 T2 테이블의 col1 컬럼을 조인해서 데이터를 조회한다고 가정해 보겠습니다. 이때 각 컬럼의 콜레이션이 utf8mb4_0900_ai_ci, utf8mb4_0900_as_cs로 지정되어 있다고 할 때, 두 컬럼을 조인해서 데이터를 조회하면 위 그림처럼 에러가 발생하고 쿼리가 실행되지 않습니다.

이처럼 서로 다른 콜레이션을 가지는 컬럼들의 값을 비교하는 경우에는 쿼리 실행이 불가하므로, 동일한 하나의 콜레이션을 사용하도록 변경해야 합니다.

이를 위해 필요한 경우 실제로 컬럼들의 콜레이션 설정을 변경하거나, 쿼리에서 ‘COLLATE’ 키워드를 사용하여, 비교 연산을 수행하는 두 문자열 컬럼에 대해 원하는 콜레이션을 명시적으로 지정할 수 있습니다.

2) 쿼리 WHERE 절에서 콜레이션 변경 시

그런데 쿼리에서 이렇게 직접 콜레이션을 변경하는 경우에는 한 가지 문제점이 있는데요. 바로 두 번째 주의사항으로 쿼리의 WHERE절에서 COLLATE 키워드를 통해 컬럼의 콜레이션을 변경하는 경우에는 해당 컬럼의 인덱스가 존재하더라도, 쿼리에서 인덱스를 사용할 수 없습니다.

예를 들어, DB에 t1이라는 테이블이 있고, ‘name’이라는 문자열 타입의 컬럼을 가지는데 이때 컬럼의 콜레이션은 utf8mb4_0900_as_cs 즉, 악센트와 대소문자를 모두 구분하는 콜레이션으로 설정되어 있다고 가정해 보겠습니다. 그리고 테이블에 name 컬럼에 대한 인덱스도 존재합니다.

테이블에 수십만 건의 데이터가 저장된 상황에서, 위 그림의 왼쪽 하단과 같이 name 컬럼의 값이 소문자로 구성된 문자열인 “esther”와 동일한 데이터를 검색하면, 한 건의 데이터가 빠르게 반환되는 것을 알 수 있습니다.

이는 쿼리 처리 시 인덱스를 사용하기 때문인데요. 반면에 오른쪽과 같이 WHERE 절에서 name 컬럼에 대해 COLLATE 키워드를 사용해, 컬럼에 설정된 것과 다른 콜레이션을 지정해 데이터를 조회할 때는 결과에 해당하는 데이터는 2건밖에 안 되지만, 인덱스를 사용하지 못해 쿼리의 실행 시간이 더 오래 걸린 것을 알 수 있습니다.

두 쿼리의 실행 계획을 확인해 보면, COLLATE 키워드를 사용한 쿼리는 실제로 인덱스를 사용하지 못하고, 테이블의 전체 데이터를 풀 스캔하는 형태로 처리되는 것을 알 수 있습니다.

일반적으로 인덱스 데이터는 인덱싱되는 문자열 컬럼에 지정된 콜레이션 규칙에 따라 정렬되어서 저장되고, 인덱스를 통한 데이터 검색이나 비교 연산 등도 컬럼에 설정된 콜레이션을 바탕으로 수행됩니다.

그러므로 쿼리에서 COLLATE 키워드를 사용해, 컬럼의 콜레이션을 명시적으로 변경하는 경우 실제 인덱스에서 사용하는 콜레이션과 달라지게 됩니다. 그래서 인덱스를 사용할 수 없는 것입니다. 따라서 COLLATE 키워드를 사용하면, 의도치 않게 인덱스를 사용하지 못하고 테이블 풀 스캔이 수행될 수 있으므로 주의해야 합니다.

그런데 필요에 의해 특정 쿼리 안에서만 이렇게 COLLATE 키워드를 계속 사용해야 되는 경우가 있는데요. 이럴 땐 위 그림과 같이 컬럼을 그대로 지정해서 사용하는 일반 인덱스가 아닌, 표현식을 사용하는 함수 기반 인덱스로 해서 쿼리에서 사용하는 형태 그대로 표현식에 지정해 인덱스를 생성해 줍니다.

이렇게 인덱스를 생성한 상태에서 앞서 테이블 풀 스캔으로 처리됐던 쿼리를 실행하면, 이번엔 인덱스를 제대로 사용하는 것을 알 수 있습니다.

3) 고유키가 콜레이션의 영향을 받는 경우

다음으로 세 번째 주의사항은 테이블의 고유키, 즉, PRIMARY KEY나 UNIQUE KEY도 콜레이션에 영향을 받는다는 것입니다.

앞서 인덱스는 인덱싱되는 컬럼에 설정된 콜레이션을 바탕으로 동작한다고 했는데요. 그 연장선상에 있는 내용입니다. 고유키의 경우 일반 인덱스와 비교했을 때, 추가적으로 인덱싱되는 컬럼 값의 중복 여부를 확인하게 되는데요. 문자열 컬럼의 경우 중복 여부 확인을 위해 값을 비교할 때, 마찬가지로 콜레이션이 사용됩니다. 콜레이션에 따라 같은 값이라 해도 중복으로 판단될 수도 있고, 그렇지 않을 수도 있습니다.

예를 들어, DB에 t1이라는 테이블이 있고, 테이블에 있는 name이라는 컬럼에 대해 UNIQUE KEY가 존재하고, 이 name 컬럼에 설정된 콜레이션은 utf8mb4_0900_as_cs라고 가정할게요. 또한 테이블에는 소문자로 구성된 ‘esther’라는 데이터가 저장되어 있고, 대문자로 구성된 ‘ESTHER’라는 데이터를 새롭게 저장하려고 합니다.

이때 정상적으로 대문자 데이터가 저장되는 것을 알 수 있는데요. name 컬럼에 설정된 콜레이션이 대소문자를 구분하는 콜레이션이므로, 두 값을 다른 값으로 인식해 중복으로 판단하지 않아 값이 정상적으로 저장된 것입니다.

만약 name 컬럼이 대소문자를 구분하지 않는 utf8mb4_0900_ai_ci 콜레이션으로 지정되어 있다면 어떻게 될까요? 위 그림과 같이 대문자 ‘ESTHER’ 데이터는 저장되지 못하고, 중복 에러가 발생하게 됩니다.

이처럼 문자열 데이터의 경우 데이터의 고유성을 판단하는데 콜레이션이 영향을 미칩니다. 따라서 문자열 컬럼에 UNIQUE 제약을 설정할 때는 이러한 부분을 고려해, 실제 원하는 형태로 동작하는지 확인 후 설정하는 것이 중요합니다.

4) 기본 콜레이션에서의 한글 비교 문제 발생

네 번째 주의사항은 MySQL의 기본 콜레이션인 utf8mb4_0900_ai_ci 콜레이션에서 한글 문자열 비교 시, 예상과 다르게 동작하는 부분에 관한 내용입니다.

utf8mb4_0900_ai_ci 콜레이션에서는 문자 ‘가’와 ‘ㄱㅏ’가 동일한 값으로 인식되는데요. 위 그림에서 예시로 나와 있는 것처럼 음절 단위의 문자로 구성된 ‘가나다’와 개별 한글 문자로 나열된 ‘ㄱㅏㄴㅏㄷㅏ’가 같은 값으로 인식됩니다.

또한 ‘가나다’로 데이터를 조회하는 경우, 위 그림의 오른쪽처럼 다양한 형태의 값들이 같은 값으로 인식되는 것을 알 수 있습니다.

반면에 ‘각’과 ‘ㄱㅏㄱ’ 또는 ‘가ㄱ’은 같다고 인식하지 않는데요. 왜 이렇게 동작하는지 구체적인 예시를 통해 살펴보겠습니다.

먼저 utf8mb4_0900_ai_ci 콜레이션에서 완성형 문자인 ‘가’와 개별 문자인 ‘ㄱㅏ’에 대해서 가중치 값이 어떻게 계산되는지 확인해 보겠습니다.

위 그림의 가운데 나타나 있는 데이터는 DUCET 데이터로, 위의 3개는 한글 초성 ‘ㄱ’, 중성 ‘ㅏ’, 종성 ‘ㄱ’에 대한 가중치 데이터이고, 아래 2개는 한글 문자 ‘ㄱ’, 한글 문자 ‘ㅏ’에 대한 가중치 데이터입니다.

DUCET에서는 한글에 대해서 초성, 중성, 종성별로, 또 개별 문자별로 가중치 데이터가 각각 존재합니다. 이 데이터와 실제 MySQL에서 계산된 가중치 값을 비교해 보겠습니다.

위 그림의 왼쪽 아래를 보면, 문자 ‘가’에 대해 계산된 가중치 값이 나타나 있는데요.

앞서 말했듯이 한글의 경우 음절 문자는 초성, 중성, 종성으로 분해해서 가중치 값을 계산합니다. 그래서 초성 ‘ㄱ’과 중성 ‘ㅏ’의 가중치 데이터가 사용되고, 이때 콜레이션이 ai_ci 콜레이션이므로 첫 번째 가중치 값만 사용되어, 위와 같이 값이 출력된 것을 알 수 있습니다. 그리고 출력된 값을 보면 알 수 있듯이 각 문자별 가중치 값이 연결되어, 최종적인 가중치 값으로 나타나는 것을 알 수 있습니다.

오른쪽에 있는 ‘ㄱㅏ' 문자열에 대해서도 가중치 값을 확인해 보면, 한글 문자 ‘ㄱ’과 한글 문자 ‘ㅏ’의 가중치 데이터에서 첫 번째 값을 바탕으로 가중치 값이 계산된 것을 알 수 있습니다.

두 값을 보면 같은 가중치 데이터가 사용된 것은 아니지만, 한글 초성 ‘ㄱ’과 한글 문자 ‘ㄱ’ 그리고 한글 중성 ‘ㅏ’와 한글 문자 ‘ㅏ’의 첫 번째 가중치 값이 동일하다 보니, 결과적으로 계산된 가중치 값이 동일해 같은 값으로 인식된 것을 알 수 있습니다.

이번에는 ‘각’과 ‘ㄱㅏㄱ’에 대한 가중치 값을 확인해 보겠습니다. 이 둘은 앞선 경우와 다르게 서로 다른 값으로 인식되는데요. WEIGHT_STRING 함수를 통해 가중치 값을 조회해 보면, 음절 문자인 ‘각’의 경우 초성, 중성, 종성에 해당하는 각 문자들의 가중치 데이터에서 첫 번째 값들이 사용된 것을 알 수 있습니다.

그리고 ‘ㄱㅏㄱ’의 경우, 일반 문자 ‘ㄱ’과 일반 문자 ‘ㅏ’에 대한 가중치 데이터에서 첫 번째 값들이 사용되었습니다. 두 문자열의 가중치 값을 비교해보면, 결과적으로 종성 ‘ㄱ’과 한글 문자 ‘ㄱ’의 1단계 가중치 값이 다름에 따라, 두 문자열의 가중치 값도 달라집니다. 이로 인해 최종적으로 서로 다른 값으로 인식되는 것을 알 수 있습니다.

utf8mb4_0900_ai_ci 콜레이션에서는 첫 번째 가중치 값, 즉 Primary 레벨의 값만 사용되었는데요. 세 번째 가중치 값인 Tertiary 레벨의 값까지 사용하는 as_cs 콜레이션에서는 가중치 값이 어떻게 달라지는지 확인해 보겠습니다.

이전과 동일하게 ‘가’와 ‘ㄱㅏ’에 대한 가중치 값을 확인해 보면, ‘가’의 경우 음절을 구성하는 초성, 중성 문자들에 대해 각각의 가중치 데이터에서 같은 단계에 속하는 값들을 묶은 후, 단계별 데이터를 0byte로 연결한 값이 최종적으로 반환되는 것을 알 수 있습니다.

오른쪽의 ‘ㄱㅏ’의 가중치 값을 보면, 동일한 방식으로 만들어진 것을 알 수 있습니다. 두 문자열의 가중치 값을 살펴보면, 첫 번째 값만 사용하던 ai_ci 콜레이션 때와 비교했을 때 두 값이 다른 것을 확인할 수 있습니다.

한글 초성 ‘ㄱ’과 한글 중성 ‘ㅏ’의 세 번째 가중치 값과 한글 문자 ‘ㄱ’과 ‘ㅏ’의 세 번째 가중치 값이 다름으로 이러한 결과가 나온 것입니다. 그러므로 ai_ci 콜레이션과는 달리 as_cs 콜레이션에서 두 문자열은 다르다고 인식됩니다.

다음으로 ‘각’과 ‘ㄱㅏㄱ’에 대해서도 as_cs 콜레이션에서의 가중치 값을 한번 확인해 볼게요. 그림을 보면 앞서 살펴본 경우와 동일한 방식으로 가중치 데이터가 조합된 것을 알 수 있습니다. 두 문자열의 경우 ai_ci 콜레이션처럼 as_cs 콜레이션에서도 가중치 값이 일치하지 않으므로, 서로 다른 값으로 인식됩니다.

이처럼 기본 콜레이션을 사용하는 상황에서 한글 문자를 비교하는 경우, 예상과 다르게 동작하는 이슈가 있는데요. 사실 이 부분은 MySQL에서 문자의 가중치 값을 계산하기 위해 사용하는 데이터가 명확하게 존재하고, 실제로 이를 바탕으로 계산된 값이라는 것을 확인할 수 있기 때문에 MySQL의 동작 버그라고 볼 순 없습니다.

근본적으로 문자들의 가중치 값이 조정되어야 한다고 생각하는데요. 이는 표준값을 바꾸는 문제여서 개인이 나서서 무언가를 하기도 어렵고, 쉽지 않은 과정입니다. 그래서 일단은 기본 콜레이션에서 한글의 경우, 이렇게 동작하는 부분이 있음을 인지하고 사용해야 될 것으로 보입니다.

만약 정확한 한글 구분이 필요한 경우, 컬럼에 다른 콜레이션을 사용하거나, 위 그림처럼 쿼리의 WHERE절에 비교 조건을 추가해 사용하는 것도 하나의 방법입니다.

예시 쿼리를 보면, utf8mb4_unicode_520_ci 콜레이션으로 한 번 더 비교하는 것을 알 수 있는데요. utf8mb4_unicode_520_ci 콜레이션에서는 ‘가’와 ‘ㄱㅏ’를 다른 문자로 인식하기 때문에 쿼리 결과에서 ‘ㄱㅏ을’인 데이터는 제외됩니다.

따라서 기본 콜레이션이 아닌 다른 콜레이션으로 컬럼의 콜레이션을 설정하거나, 이처럼 쿼리에 추가로 조건을 줄 때는 원하는 결과를 반환하도록 적절한 콜레이션을 설정해야 합니다. 이를 위해서는 콜레이션별로 문자들을 어떻게 구분하고 있는지 확인이 필요합니다.

다음은 콜레이션별로 비교 결과를 살펴볼 대상 문자 목록입니다. 모든 문자들을 비교해 볼 수 없으므로, 대표적인 경우들로 해서 간단한 설명과 함께 표로 정리해 보았습니다.

앞서 정리한 비교 대상 문자 목록을 바탕으로, 콜레이션별로 문자 비교 결과를 작성한 표입니다.

utf8mb4 문자 집합에 속하는 콜레이션 중 몇 가지를 선정해 문자들을 비교해 보았는데요. 콜레이션 변경이 필요한 경우, 이 표를 참고해 적절한 콜레이션을 선택해도 좋을 것 같습니다.

5) 대소문자 구분을 위한 콜레이션 선정하기

마지막으로 대소문자 구분을 위해서 어떤 콜레이션을 사용하면 좋을지에 대해 살펴보겠습니다. MySQL의 기본 콜레이션은 ci 콜레이션으로 대소문자를 구분하지 않습니다. 그래서 만약 대소문자 구분이 필요한 경우에는 다른 콜레이션을 사용해야 되는데요.

동일한 utf8mb4 문자집합 내에서 대소문자를 구분하는 콜레이션에는 utf8mb4_bin, utf8mb4_0900_bin, 그리고 utf8mb4_0900_as_cs 콜레이션이 있습니다.

세 콜레이션 모두 대소문자 구분이 가능한데, 그럼 어떤 콜레이션을 사용하던지 특별히 상관이 없는 걸까요? 세 콜레이션은 대소문자를 구분하는 건 동일하지만 동작하는 방식은 다릅니다. 그래서 경우에 따라 좀 더 적절한 콜레이션을 사용하는 것이 좋습니다.

각 콜레이션별로 특징을 한번 살펴보겠습니다. 먼저 첫 번째로 utf8mb4_bin 콜레이션은 문자열의 코드 포인트 값을 바탕으로 비교합니다. 그리고 문자열에 후행 공백이 있더라도 이를 무시하고 비교합니다.

두 번째로 utf8mb4_0900_bin 콜레이션은 문자열의 utf8mb4 인코딩 값을 바탕으로 비교합니다. 저장된 문자열 인코딩 값을 그대로 사용해 비교하므로, utf8mb4_bin 콜레이션과 정렬 순서는 동일하지만 처리 속도가 좀 더 빠릅니다. 그리고 0900_bin 콜레이션은 문자열의 후행 공백을 인식합니다.

마지막으로 utf8mb4_0900_as_cs 콜레이션은 DUCET 가중치 데이터를 바탕으로 비교하며, 마찬가지로 문자열의 후행 공백을 인식합니다. 각 콜레이션의 특징들을 바탕으로 대소문자 구분이 필요할 때, 경우에 따라 어떤 콜레이션을 사용하면 좋을지 정리하면 다음과 같습니다.

만약 대소문자 구분이 필요하고 후행 공백 인식을 원하지 않는 경우, 즉 뒤에 공백이 있는 소문자 ‘a’와 일반 소문자 ‘a’가 동일한 문자열이라고 판단하길 원하면, utf8mb4_bin 콜레이션을 사용하면 됩니다. 그리고 대소문자 구분이 되면서 후행 공백을 인식하길 원하거나, 특별히 상관이 없을 때 단순히 대소문자가 잘 구분되는 정도로 충분한 경우에는 utf8mb4_0900_as_cs를, 모든 문자가 명확히 구분되었으면 할 때는 utf8mb4_0900_bin을 사용하면 됩니다.

as_cs 콜레이션의 경우 가중치 데이터를 사용하기 때문에, 예상과는 다르게 서로 다른 문자가 동일한 가중치 값을 가질 수도 있으므로 이와 같은 경우를 구분해 보았습니다.

---

마치며

지금까지 MySQL의 콜레이션에 대해 전반적인 내용과 주의사항을 함께 살펴보았는데요. 콜레이션이 좀 생소했던 분들은 이번에 설명드린 내용을 통해 좀 더 익숙해지고, 스키마나 쿼리 설계 시에도 요구사항에 맞는 적절한 콜레이션을 선택해서 사용하시면 좋을 것 같습니다.

title⟪Real MySQL 시즌 1⟫ : 개발자를 위한 기초부터 고급까지 MySQL 활용 가이드 로드맵 - 인프런

descMySQL, DBMS/RDBMS 스킬을 학습할 수 있는 로드맵을 인프런에서 만나보세요.