테이블 인덱스 재 설계

tags

MySQL

indexing

thumbnail

Cardinality → 해당 컬럼의 중복된 수치

컬럼에 저장된 고유한 값의 개수 즉, 전체 데이터의 개수에 비해 고유한 값의 수가 얼마나 되는지를 나타냄.

중복도가 낮으면 카디널리티가 높고, 높으면 카디널리티가 낮음 수치가 높다는건 그만큼 그 컬럼에 해당해서 중복된 값이 낮으므로 유니크하다란 의미 (ex 주민번호, 계좌번호) 복합 인덱스 설계 시 카디널리티가 높은 순에서 낮은 순으로 구성

카디널리티가 높은 순으로 먼저 조회를 하기 때문

개발 서버 일상 테이블의 경우


SELECT 
	*,relate as id 
FROM board 
WHERE category = 2 AND z= 0 AND reg_date >= 1771524400 LIMIT 15;

실행했을 경우 대략 1분 20초

Rows 총 49,601,350

scripti 시작 시 50081414 rows 문구 노출

→ count로 조회했을 때와 다른 rows가 나와버림 (?)

COUNT 쿼리를 Explain 했을 때에는 50,081,414

0 PRIMARY 1 no A 50081416 1 name 1 name A 33068 1 category 1 category A 9368 1 z 1 z A 1705 1 suggest 1 suggest A 43204552 1 ismember 1 ismember A 227158 1 vote 1 vote A 8976 1 hit 1 hit A 45559 1 real_comment 1 real_comment A 9295 1 x 1 x A 12922 1 sitelink1 1 sitelink1 A 4013 1 idx_reg_date 1 reg_date A 45721232

Table, Non_unique, KeyName, SeqInIndex, ColumnName, Collation, Cardinality

무조건 많이 설정하지 않는다. (한 테이블당 3~5개가 적당 목적에 따라 상이)

조회 시 자주 사용하는 컬럼

고유한 값 위주로 설계

카디널리티가 높을 수록 좋다 (= 한 컬럼이 갖고 있는 중복의 정도가 낮을 수록 좋다.)

INDEX 키의 크기는 되도록 작게 설계

PK, JOIN의 연결고리가 되는 컬럼

단일 인덱스 여러 개 보다 다중 컬럼 INDEX 생성 고려

UPDATE가 빈번하지 않은 컬럼

JOIN시 자주 사용하는 컬럼

INDEX를 생성할 때 가장 효율적인 자료형은 정수형 자료(가변적 데이터는 비효율적)

MySQL 에서 범위 조건 >, <, BETWEEN 이후 컬럼은 인덱스 탐색을 사용하지 못함,


pt-online-schema-change \
--user=<USER> \
--password=<PASSWORD> \
--host=<HOST> \
--alter "ADD INDEX idx_category_regdate (category, reg_date),
         ADD INDEX idx_name_relate (name, relate, no),
         ADD INDEX idx_name_category_no (name, category, no),
         ADD INDEX idx_regdate_total_comment (reg_date, total_comment),
         DROP INDEX z,
         DROP INDEX vote,
         DROP INDEX real_comment,
         DROP INDEX sitelink1,
         DROP INDEX x,
         DROP INDEX name,
         DROP INDEX category" \
D=<DATABASE>,t=<TABLE> \
--max-load Threads_running=15 \
--critical-load Threads_running=30 \
--no-drop-old-table \
--chunk-size=1500 \
--chunk-time=0.5 \
--sleep=0.2 \
--progress time,10 \
--execute

위와 같이 인덱스를 재정의

⚠️

운영 테이블 마이그레이션 시 주의사항

몇몇 크론 잡으로 돌아가는 파일 내 use index () hint 쿼리가 작성되어있다. 만약 해당 힌트가 잘못됐을 경우 에러가 발생

예시로 개발 서버에서 name 테이블에 다일 인덱스인 category를 DROP 했다.

1.php에 use index(category)가 존재하여 이 인덱스 힌트를 지워주지 않으면 에러 발생 예상됨


SELECT 
	*,relate as id 
FROM zetyx_board_name 
	WHERE 
reg_date >= 776524400 AND z=0 AND category=2 
	ORDER BY real_comment DESC LIMIT 15;

기존 쿼리의 경우 변경한 인덱스 적용이 되지 않음, real_comment 에 대한 인덱싱이 없어서 full scan 발생

쿼리의 의도는 특정 날짜 이상의 게시물 중 댓글이 많은 순서대로 정렬하여 15개의 게시물을 추출한다.

만약 범위 조건 내 게시물이 많을 경우 (1000만건 이상의 rows) full scan이 발생한 이후 1000만건에 대한 real_comment 정렬이 실행되므로 인덱스를 전혀 활용하지 못함

개선 사항 1)

검색되는 범위를 줄인다.


SELECT *, relate AS id
FROM (
    SELECT *
    	FROM zetyx_board_name
    WHERE reg_date >= 776524400
      AND z = 0
      AND category = 2
    ORDER BY reg_date DESC
    LIMIT 1000
) t
ORDER BY real_comment DESC
LIMIT 15;

real_comment 가 많은 순서대로 15개의 게시물을 추출해야한다. 검색되는 게시물의 개수를 줄인 후 그 안에서 정렬로 탐색하는 방법

서브 쿼리로 해당 기간으로 범위를 좁힌 후 해당 게시물에서 댓글 수로 다시 한 번 정렬 후 15개 게시물 추출

문제점 : 서브 쿼리 내 limit 1000으도 두어 게시물 탐색 개수를 제한해버리면, 댓글 수를 체크하는 게시물이 제한되버림, 만약 1001 번째 게시물이 1000번보다 댓글 수가 더 많은데 해당 게시물을 제외 되버리는 경우가 발생함

어차피 탐색 범위는 현재 시간 기준으로 기간을 산정하기 때문에 limit을 제거해도 문제 없을 것으로 보여짐

개선점 : 쿼리 실행 시간 대폭 단축 43s → 0.095s

개선 사항 2)

필요한 id만 추출해서 IN 조건으로 댓글 수 탐색 방법

서브 쿼리를 사용한 방식과 비슷한 결과를 보임


-- 1단계: 후보 id만 빠르게
SELECT no
FROM zetyx_board_name
WHERE category = 2
  AND z = 0
  AND reg_date >= 576524400
ORDER BY reg_date DESC
LIMIT 1000;

-- 2단계: 실제 데이터 + 정렬
SELECT *, relate AS id
FROM zetyx_board_name
WHERE no IN ( ... )
ORDER BY real_comment DESC
LIMIT 15;

—

대용량 테이블 zetyx_board_name_enter 작업 체크

개발 DB 기준 총 82,425,799 개의 Rows를 가짐

Table	Non_unique	Key_name	Seq_in_index	Column_name	Cardinality	Sub_part	Index_type	Nullable
zetyx_board_name_enter	0	PRIMARY	1	(PK 컬럼)	81,664,664	-	BTREE	NO
zetyx_board_name_enter	1	name	1	name	31,247	-	BTREE	YES
zetyx_board_name_enter	1	category	1	category	19,766	-	BTREE	YES
zetyx_board_name_enter	1	suggest	1	suggest	81,664,664	191	BTREE	YES
zetyx_board_name_enter	1	z	1	z	1,303	-	BTREE	YES
zetyx_board_name_enter	1	x	1	x	20,969	-	BTREE	YES
zetyx_board_name_enter	1	real_comment	1	real_comment	11,058	-	BTREE	YES
zetyx_board_name_enter	1	hit	1	hit	79,730	-	BTREE	YES
zetyx_board_name_enter	1	vote	1	vote	8,772	-	BTREE	YES
zetyx_board_name_enter	1	ismember	1	ismember	324,464	-	BTREE	YES
zetyx_board_name_enter	1	sitelink1	1	sitelink1	2,733	191	BTREE	YES


EXPLAIN SELECT *,relate as id FROM zetyx_board_name_enter WHERE reg_date >= 166994393 AND z=0 AND category=10 ORDER BY real_comment DESC LIMIT 15;

실행 방식: `index_merge`

category, z 두 개 단일 인덱스를 각각 조회 후 교집합(intersect)

즉, 복합 인덱스가 없어서 임시로 합치는 방식

문제

index merge는 보통 차선책

각각 스캔 → 결과 합침 → 추가 필터 → 정렬 → CPU + I/O 비용 증가

rows: `933,505`

약 93만 건 후보 조회

filtered: 33.33% → 약 30만 건 실제 처리

문제

조건 대비 너무 많은 row 스캔

인덱스 선택도가 좋지 않음 (특히 z, category)

`Using intersect(category,z)`

두 인덱스 결과를 메모리에서 교집합 처리

문제

단일 인덱스 2번 타는 구조라 비효율

특히 데이터 많을수록 비용 급증

`Using filesort`

정렬을 인덱스로 못하고 별도 수행

문제

ORDER BY 컬럼에 맞는 인덱스 없음

LIMIT 있어도 정렬 비용 발생

결론 (문제 요약)

복합 인덱스 없음 → index_merge 발생

90만 row 스캔 → 과도한 I/O

filesort 발생 → 정렬 비효율


EXPLAIN SELECT *, relate AS id
FROM zetyx_board_name_enter
WHERE reg_date >= 166994393
  AND z = 0
  AND subject NOT LIKE '[마플]%'
  AND category = 10
ORDER BY real_comment DESC
LIMIT 15;

퀄리 실행 분석


1	SIMPLE	zetyx_board_name_enter		index_merge	category,z	category,z	4,5		933505	29.63	Using intersect(category,z); Using where; Using filesort

항목	LIKE 절 없음	LIKE 절 포함	해석
id	1	1	단일 쿼리
select_type	SIMPLE	SIMPLE	서브쿼리 없음
table	zetyx_board_name_enter	zetyx_board_name_enter	동일 테이블
type	index_merge	index_merge	복합 인덱스 없음 → 병합 사용
key	category, z	category, z	동일 인덱스 사용
key_len	4,5	4,5	동일
rows	933,505	933,505	약 93만 건 스캔
filtered	33.33%	29.63%	쿼리2가 더 많이 버림
Extra	Using intersect(category,z); Using where; Using filesort	Using intersect(category,z); Using where; Using filesort	동일

항목	`_zetyx_board_name_enter_old`	`zetyx_board_name_enter`
접근 방식 (type)	index_merge	range
사용 인덱스	category, z (개별 인덱스 병합)	idx_category_regdate
rows (예상 스캔 수)	933,505	1,115,916
필터 효율 (filtered)	33.33%	10.0%
추가 작업	Using intersect, Using filesort	Using index condition, Using filesort
특징	인덱스 2개 병합 후 교집합	복합 인덱스 기반 범위 스캔

쿼리 비교


1	SIMPLE	_zetyx_board_name_enter_old		index_merge	category,z	category,z	4,5		933505	33.33	Using intersect(category,z); Using where; Using filesort

1	SIMPLE	zetyx_board_name_enter		range	idx_category_regdate,idx_regdate_total_comment	idx_category_regdate	8		1115916	10.0	Using index condition; Using where; Using filesort

대용량 테이블 zetyx_board_name_enter 작업 체크

실행 방식: index_merge

rows: 933,505

Using intersect(category,z)

Using filesort