[MYSQL] 응용 용어2

콜레이션

특정 문자셋으로 데이터베이스에 저장된 값을 비교하거나 정렬하는 작업의 규칙을 의미

 

데이터 비교

숫자 비교 : 11 > 10

날짜 비교 : 2022/12/25 < 2023/07/01

 

문자의 경우, 설정된 콜레이션에 따라 대소 관계의 출력도 달라짐

- A, a, B, b의 대소관계

utf8_bin : A > B > a > b

utf8_general_ci : A > a > B > b

 

콜레이션은 데이터베이스 단위, 테이블 단위, 심지어 열 단위까지 세세하게 설정할 수 있음

학생 테이블의 콜레이션이 utf8_general_ci 로 설정되어 있다면 학번 열과 전공 코드 열에 콜레이션이 명시되어 있지 않더라도  utf8_general_ci 가 적용, 이때 만약 이름 열에 콜레이션 utf8_bin을 명시했다면 상위 테이블의 콜레이션을 무시하고 utf8_bin으로 설정됨 → 이름열은 콜레이션 utf8_bin, 학번 열과 전공 코드 열은 콜레이션 utf8_general_ci

 

 

TIP 캐릭터셋 VS 콜레이션
데이터를 저장하는 방식에 대한 캐릭터셋과 데이터 정렬에 대한 콜레이션 비교

캐릭터셋(character set)	콜레이션(collation)
데이터 저장을 어떻게 할 것인가 - 영문 + 숫자 - 중국어 - 다국어 - 예) utf8(다국), utf8mb4(다국어 + 이모지)	데이터 정렬은 어떻게 할 것인가 - a 와 A 간의 대소 관계 정의 - a 와 b 간의 대소 관계 정의 ... - 예) utf8_general_ci, utf8_bin (다국어 + 이모지)

---

통계정보

옵티마이저는 통계정보에 기반을 두고 SQL문의 실행 계획을 수립

통계정보는 데이터베이스 관리자(DBA)가 맡은 역할이긴 하지만, 쿼리를 수행하는 역할자 또한 통계정보가 현재 최신으로 관리되는지, 오래된 통계정보 때문에 SQL문이 엉뚱한 방향으로 수행되지 않는지 확인할 수 있음

MYSQL은 시스템 변수를 통해 활용할 통계정보의 수준을 정의

테이블의 통계정보와 인덱스 통계정보, 선택적인 열 통계정보를 토대로 어떤 인덱스를 활용해 데이터에 액세스할 것인지, 어떤 테이블을 드라이빙 테이블로 선택할지 등을 결정

따라서, 통계정보의 최신성 유지 및 관리가 매우 중요

---

히스토그램

테이블의 열값이 어떻게 분포되어 있는지를 확인하는 통계 정보

옵티마이저가 실행 계획을 최적화하고자 참고하는 정보로 잘못된 히스토그램 정보가 있다면 잘못된 실행 계획으로 SQL문이 수행될 수 있음

만약 특정 열갑들의 통계정보가 히스토그램으로 수집되지 않았다면, 중복이 제거된 열값의 개수(COUNT(DISTINCT 열명))로 대략적인 열값의 분포를 예측하고 실행 계획을 수립

 

MYSQL에서 내부적으로 열의 분포를 저장할 때는 높이균형 히스토그램(height balanced histogram) 방식 사용

저장된 데이터값들을 그룹화하고, 정해진 버킷(bucket)만큼 분리해서 열의 통계정보 데이터를 저장

 

Col2 열에는 A,B,C, ..., ZZZ까지 총 78종의 데이터와 10개의 버킷이 있다고 가정

버킷 10개와 유사한 건수만큼 나눠 정렬

 

 

실제 데이터베이스에서 관리하는 히스토그램의 버킷은 최댓값만 보관

버킷1 = AAA, 버킷2 = EE, 버킷3 = H, 버킷 4 = JJJ, ..., 버킷 10 = ZZZ

만약, WHERE 절 조건문에 Col1 = 'A' → 버킷 1에만 접근하여 데이터 분포 파악

Col1 BETWEEN E AND O → 버킷 2부터 6까지 총 5개 버킷에 접근(전체 데이터 대비 50% 이상의 영역을 스캔해야 하므로 인덱스 스캔보다 테이블 풀 스캔으로 쿼리가 수행되도록 통계정보를 제공)

 TIP 히스토그램 정보 직접 생성하기 (MYSQL 8.0 이상 사용 가능)

문법

ANALYZE TABLE 테이블명 UPDATE HISTOGRAM ON 열명 나열;​

예시

ANALYZE TABLE 사원 UPDATE HISTOGRAM ON 이름;​

확인

SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;​

 

* 참고

- 업무에 바로쓰는 SQL 튜닝(도서) - 2장 SQL 튜닝 용어를 직관적으로 이해하기