카디널리티 분포도 확인
카디널리티(Cardinality)
컬럼이나 인덱스가 가진 고유한 값의 개수를 의미한다.
카디널리티가 높을수록 인덱스의 선택성이 좋아지고 쿼리 성능이 향상될 수 있어서 MySQL에서는 이 값을 사용해 실행 계획을 세우고 어떤 인덱스를 사용할지 결정해야 한다.
- 높은 카디널리티
- 이메일 주소
- 주민등록번호
- 사용자 ID
- 낮은 카디널리티
- 성별: 남/여 (2개의 값)
- 결혼 여부: 기혼/미혼
히스토그램(Histogram)
컬럼의 데이터 분포를 시각적을 나타내는 통계 정보를 말한다.
수집 및 삭제
히스토그램을 통해 카디널리티 분포도를 확인할건데 이건 자동으로 수집되지 않기 때문에 ANALYZE TABLE 명령을 통해 수동으로 수집해야 한다.
# 생성
ANALYZE TABLE 테이블명 UPDATE HISTOGRAM ON 컬럼1, 컬럼2;
## 개별 생성도 가능
ANALYZE TABLE 테이블명 UPDATE HISTOGRAM ON 컬럼1;
# 삭제
ANALYZE TABLE 테이블명 DROP HISTOGRAM ON 컬럼1;확인
수집 명령을 내렸으면 information Schema로 데이터베이스를 변경해서 히스토그램을 조회할 수 있다.
use information_schema;
select * from COLUMN_STATISTICS
where TABLE_NAME = '테이블명';| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | HISTOGRAM |
|---|---|---|---|
| 스키마명 | 테이블명 | 컬럼명 | {"buckets": [["base64:type254:... |
히스토그램은 버킷 단위로 구분되어, 버킷 칼럼에 있는 데이터를 통해서 칼럼의 분포도를 확인할 수 있다.
나는 주문 테이블에 3명의 고객 정보를 6개로 넣어놨다.
그래서 버킷에는 어쩌구 유저가 6개중 3개를 가지고 있어서 50%, 저쩌구유저는 1개, 구구구 유저는 2개로 내가 지정한 컬럼으로 엄청 불균형하지 않은, 비슷한 수준의 효율을 기대할 수 있음을 확인할 수 있다.
{
"buckets": [ // 각 고유값과 누적 빈도 (0.6667은 누적 빈도)
[
"base64:type254:어쩌구==",
0.5 // 전체의 50%
],
[
"base64:type254:저쩌구==",
0.6666666666666666 // 전체의 16.7%(0.6667 - 0.5)
],
[
"base64:type254:구구구==",
1.0 // 전체의 33.3%(1.0-0.6667)
]
],
"data-type": "string", // 문자열 타입 컬럼
"null-values": 0.0, // NULL 값이 없음
"collation-id": 255, // 문자셋 정렬 규칙
"last-updated": "2025-02-11 08:32:24.923805",
"sampling-rate": 1.0, // 전체 데이터 분석
"histogram-type": "singleton", // 각 고유값을 개별적으로 저장
"number-of-buckets-specified": 100 // 최대 버킷 수 지정값
}히스토그램 타입은 singleton과 equi-height 2개 인데, 값이 별로 없을 땐 싱글톤이 사용된다.
-
기본 히스토그램 타입은 singleton이다.
-
equi-height를 사용하려면 명시적으로 타입을 지정해야 한다.
ANALYZE TABLE 테이블명 UPDATE HISTOGRAM ON 컬럼명 USING EQUI-HEIGHT BUCKETS;- equi-height가 유용한 경우는 숫자형 범위 데이터, 날짜/시간 데이터 등 값의 종류가 매우 많은 경우이다.
실행계획 확인
EXPLAIN
먼저 인덱스를 타고 있는지 확인을 하기 위해 explain 실행계획을 본다.
explain select * from 테이블명
where 컬럼1 =: data1 and 컬럼2 =: data2;
실행계획으로 아래 내용을 확인할 수 있다.
- 인덱스를 사용하는지
- 어떤 인덱스를 사용하는지
- 테이블 스캔을 하는지 (type: ALL)
주의할 점으로는 데이터가 매우 적다면 MySQL 옵티마이저가 인덱스를 무시하고 테이블 스캔을 선택할 수도 있다.
type
일반적으로 const, system 성능이 제일 좋고 ALL이 나쁘다.
1. system: 테이블에 단 하나의 행만 있는 경우
2. const: PRIMARY/UNIQUE 키를 사용해 단 하나의 행을 매치하는 경우
3. eq_ref: 조인에서 PRIMARY/UNIQUE 키를 사용하는 경우
4. ref: 인덱스를 사용하여 동일한 값을 가진 여러 행을 찾는 경우
5. fulltext: FULLTEXT 인덱스를 사용하는 경우
6. ref_or_null: ref와 비슷하지만 NULL 값도 찾는 경우
7. range: 인덱스를 사용해 특정 범위의 행을 찾는 경우
8. index: 인덱스 전체를 스캔하는 경우
9. ALL: 테이블 전체를 스캔하는 경우
rows
옵티마이저가 판단한 읽어야 하는 데이터의 수를 말한다.
- 실제 값과 차이가 있을 수 있음
- 통계 정보(ANALYZE TABLE)을 통해 정확도를 높일 수 있음
filtered
rows 중 where 조건을 만족한 행의 비율(%)을 말한다.
- rows가 1000이고 filtered가 50이면 약 500개의 행이 조건을 만족할 것으로 예상한다.
옵티마이저란
SQL 쿼리를 실행할 때 가장 효율적인 실행 계획을 찾아주는 엔진이다.
주요 역할
- 실행 계획 결정
- 여러 실행 가능 방법 중 가장 비용이 적게 드는 방식을 선택
- 예: 인덱스를 사용할지, 테이블 풀 스캔을 할지..
- 인덱스 선택
- 테이블에 여러 인덱스가 존재할 경우, 어떤 인덱스를 사용할지 결정
- 만약 인덱스가 있어도 풀 스캔이 더 효율적이라 판단되면 인덱스를 사용하지 않음
- 조인 순서 최적화
- 여러 테이블을 조인할 때 어떤 순서로 조인할지 결정
- 작은 결과셋을 먼저 조인하는 등의 전략 사용
- 통계 정보 활용
- 통계 정보(테이블의 크기, 데이터 분포 등)를 바탕으로 판단
- 통계 정보가 부정확하면 잘못된 실행 계획을 선택할 수 있음
실제 쿼리 소요 시간 확인
EXPLAIN ANALYZE
쿼리가 실제로 어떻게 처리되는지, 소요 시간이 얼마인지 볼 수 있다.
explain analyze
select * from 테이블명
where 컬럼1 =: data1 and 컬럼2 =: data2;
🕵️ 데이터 타입이 속도에 영향을 미친다!
- 컬럼1: enum
- 컬럼2: varchar(255)
처음엔 카디널리티를 고려하지 않고 컬럼1을 인덱스에 태웠다.
- 인덱스가 없었을때 속도: 0.0334
- 인덱스에 컬럼1을 넣었을때: 0.0167
- 인덱스에 컬럼2를 넣었을때: 0.029
근데 속도는 컬럼1이 더 빠르게 나왔다. 이유는 인덱스에서는 고정 길이 처리가 더 빨라서 enum인 컬럼1의 속도가 더 빠르게 나온거였다.
지금은 테스트라 데이터가 적은데, 실제 데이터가 쌓였을땐 카디널리티값이 높은 컬럼2가 더 적합하다.
show indexes from 테이블명 으로 인덱스를 보면 intex_type이 BTREE로 나오는데, 트리 깊이가 깊어지면 읽기 횟수가 더 많이 요구된다고 한다.
// B-Tree 구조
ENUM('CAT', 'DOG') -> 1byte
vs
VARCHAR(255) -> 가변 길이 + 길이 정보 저장 바이트
// 트리 깊이 영향
고정 길이(animal_type)
Level 1: [Root]
Level 2: [Branch1][Branch2]
Level 3: [Leaf1][Leaf2][Leaf3][Leaf4]
가변 길이(animal_info)
Level 1: [Root]
Level 2: [Branch1][Branch2][Branch3]
Level 3: [Leaf1][Leaf2][Leaf3][Leaf4][Leaf5][Leaf6]
Level 4: [추가 리프 노드들...]인덱스 추가
CREATE INDEX
create index 인덱스이름
on 테이블명 (컬럼1, 컬럼2, 컬럼3);인덱스 확인은 show indexes from 테이블명;
- 인덱스를 추가하고서 다시 EXPLAIN을 통해 실행 계획을 보면
type,rows,filtered등이 변경된걸 볼 수 있다. - EXPLAIN ANALYZE로 쿼리의 소요시간을 보고 개선이 되었는지도 확인을 하자.
