MySQL Optimize with 공식문서 (5)

Ref.

https://dev.mysql.com/doc/refman/8.0/en/optimization.html

MySQL :: MySQL 8.0 Reference Manual :: 10 Optimization

 

LIMIT 쿼리 최적화

• LIMIT은 필요한 행 수만 빠르게 가져오기 위해 사용
• 결과 일부만 필요한 경우, 전체 조회 후 버리는 것보다 훨씬 효율적

 

최적화 동작

• LIMIT만 있어도 → 인덱스만으로 빠르게 조회 가능
• LIMIT + ORDER BY → 정렬 중 필요한 만큼만 보고 멈춤
• LIMIT + DISTINCT → 고유 행을 찾는 즉시 멈춤
• LIMIT + GROUP BY → 정렬 중 그룹이 바뀌면 바로 계산

 

정렬된 인덱스를 최대한 사용하려고 함

• ORDER BY 정렬 기준이 인덱스랑 맞으면 → 정렬 없이 바로 조회
• prefer_ordering_index 플래그가 ON이면 정렬 우선 인덱스 선호
  • OFF로 바꾸면 더 나은 조건 필터용 인덱스 선택 가능
  • SET optimizer_switch = 'prefer_ordering_index=off';

 

불완전 정렬 주의

• ORDER BY에 같은 값이 있는 행이 여러 개면 → 정렬 순서 비결정적
• 예: ORDER BY category LIMIT 5 결과는 매번 다를 수도 있음
• 해결: ORDER BY category, id처럼 추가 정렬 기준을 명시

 

Appendix

• LIMIT 0 → 빠르게 빈 결과 반환 (API에서 스키마만 확인할 때 유용)
• 임시 테이블을 사용할 경우에도 → LIMIT으로 필요한 메모리만 할당
• ORDER BY 정렬할 때 메모리 부족하면 → 디스크 정렬 발생 

 

전체 테이블 스캔 방지

전체 테이블 스캔이 발생하는 경우 (EXPLAIN에서 type=ALL)

1. 테이블이 너무 작음
   • 예: 10행 미만 & 짧은 행 → 인덱스보다 테이블 스캔이 빠름
2. WHERE절에 조건이 없음
   • 인덱스를 사용할 수 없는 조건
3. 조건이 너무 많은 행에 해당
   • 인덱스보다 전체 스캔이 비용이 낮다고 판단됨
4. 낮은 카디널리티 인덱스 사용 중
   • 예: 성별(gender), 상태(status) 같은 값이 몇 가지뿐인 컬럼

 

스캔 방지 전략

• ANALYZE TABLE
  • 통계 갱신 → 옵티마이저가 인덱스 가치 정확히 평가
• FORCE INDEX
  • 명시적으로 특정 인덱스를 사용하도록 강제

  SELECT * FROM t1 FORCE INDEX (idx_name) WHERE col = 1;
  

• max_seeks_for_key 제한
  • 키 조회 횟수 제한해 인덱스 사용 유도

  SET max_seeks_for_key = 1000;
  

   
  •  서버 시작 시: --max-seeks-for-key=1000

 

InnoDB 트랜잭션 관리 최적화

트랜잭션 커밋 전략

• 기본 설정 AUTOCOMMIT=1은 매 명령마다 커밋 → 성능 저하
• 가능하면 여러 작업을 하나의 트랜잭션으로 묶기
• SET AUTOCOMMIT=0; START TRANSACTION; -- 여러 작업 COMMIT;

 

로그 플러시 관련 설정

• innodb_flush_log_at_trx_commit=1 (기본): 매 커밋마다 로그 디스크 기록
• 성능 우선 시 0으로 설정 → 최대 1초치 손실 허용

 

대량 작업 시 주의사항

• 수만 건 이상 INSERT / UPDATE / DELETE 후 ROLLBACK 금지
  • 롤백이 원래 작업보다 느림
  • 재시작해도 롤백은 다시 실행됨
• 대처법:
  • COMMIT을 중간중간 수행
  • 작업을 나눠서 실행
  • innodb_change_buffering=all → 수정/삭제도 메모리 버퍼링
  • 버퍼 풀 크기 증가

 

장기 실행 트랜잭션 문제

• 커밋 지연되면 다른 트랜잭션이 옛날 데이터(undo log) 참조해야 함 → 읽기 성능 저하
• 커버링 인덱스도 못 쓰는 상황 발생

 

열 인덱스

일반적인 인덱스

• 단일 열 기준 인덱스가 가장 일반적
• B-트리 기반 인덱스는 =, >, >=, <, <=, BETWEEN, IN 등의 연산자에서 성능 향상
• 테이블당 최소 16개 인덱스, 256바이트 이상 길이 지원 (스토리지 엔진마다 다름)

 

인덱스 접두사

• 문자열 열의 앞부분만 인덱스에 포함하여 인덱스 크기 절약
• CREATE INDEX idx ON test(varchar_col(10));
• 사용 가능한 최대 길이:
  • InnoDB (REDUNDANT/COMPACT): 767 bytes
  • InnoDB (DYNAMIC/COMPRESSED): 3072 bytes
  • MyISAM: 1000 bytes
• 문자열 접두사 길이는:
  • 문자 수 (CHAR/VARCHAR/TEXT 등)
  • 바이트 수 (BINARY/VARBINARY/BLOB 등)
• 검색어가 접두사보다 길면 나머지 비교는 풀 스캔처럼 처리됨

 

FULLTEXT 인덱스 (전체 텍스트 검색)

• InnoDB, MyISAM에서 지원 (열 타입: CHAR, VARCHAR, TEXT)
• 접두사 인덱스 지원 X (항상 전체 열)
• 최적화되는 경우:
  • MATCH(...) AGAINST(...) 쿼리
  • ORDER BY score DESC LIMIT N
  • COUNT(*) WHERE MATCH(...) AGAINST(...) > 0
• 주의: EXPLAIN 시점에 표현식이 즉시 평가됨 → 일반 쿼리보다 느릴 수 있음

 

공간 인덱스 (Spatial Index)

• 공간 데이터 (GEOMETRY, POINT, 등)에 사용
• MyISAM, InnoDB에서 지원 (각기 R-Tree or B-Tree 방식)
• ARCHIVE 엔진은 공간 인덱스 미지원

 

MEMORY 엔진 인덱스

• 기본 인덱스 타입: HASH
• 필요시 명시적으로 BTREE 지정 가능
• CREATE TABLE t ( col INT, INDEX(col) USING BTREE ) ENGINE=MEMORY;

 

B-트리와 해시 인덱스 비교

B-트리 인덱스 특성 (기본 인덱스 구조)

특징 설명

범위 검색 가능	=, >, >=, <, <=, BETWEEN, LIKE 'abc%' 등
접두사 인덱스 활용 가능	가장 왼쪽 열부터 시작하는 경우 유효
정렬 최적화 가능	ORDER BY, GROUP BY에서 사용 가능
NULL 검사 가능	IS NULL 도 인덱스로 처리 가능
LIKE 최적화	LIKE 'abc%'는 사용 가능LIKE '%abc'는 사용 불가
일부 OR 조건은 비효율적	OR 조건이 접두사 규칙을 어기면 인덱스 미사용
카디널리티 낮으면 비사용	값이 너무 많거나 적으면 인덱스 대신 풀스캔

예시 (사용됨)

-- 접두사 순서 OK
SELECT * FROM t WHERE a = 1 AND b = 2;

-- LIKE 접두사
SELECT * FROM t WHERE name LIKE 'Pat%';

예시 (사용 안 됨)

-- 접두사 순서 X
SELECT * FROM t WHERE b = 2 AND c = 3;

-- 와일드카드 앞에
SELECT * FROM t WHERE name LIKE '%Pat';

 

 

해시 인덱스 특성 (MEMORY 엔진 등 사용)

특징 설명

정확한 값 조회에 매우 빠름	= 또는 <=> 조건만 지원
범위 검색 불가	>, <, LIKE, BETWEEN 등은 사용 불가
정렬 사용 불가	ORDER BY, GROUP BY에 인덱스 사용 못함
부분 키 검색 불가	전체 키가 있어야만 사용 가능
카디널리티 통계 없음	옵티마이저가 행 수 추정 못함 (비효율적 플랜 가능)

해시 인덱스는 정확한 키 기반 조회가 많은 시스템에 적합

 

B-트리 vs 해시 인덱스 요약 비교

항목 B-트리 인덱스 해시 인덱스

지원 조건	범위 + 정렬 + 일부 LIKE	정확한 값(=) 비교만
정렬 사용	사용 가능	사용 불가
범위 검색	가능	불가
LIKE 지원	접두사만	불가
NULL 검색	IS NULL 지원	불가
적용 엔진	InnoDB, MyISAM 등	MEMORY 등 일부에서만