[MySQL 튜닝] 튜닝 기본 ~ 심화

코린이·2025년 9월 7일

mysql mysql 튜닝 튜닝 튜닝 기본

MySQL 쿼리 성능 UP

목록 보기

4/4

✅ 인덱스의 키값 변형 지양

인덱스의 키값을 함수 등으로 변형하여 사용할 경우 인덱스에 저장된 키값과 달라지므로 인덱스 사용이 어려워질 수 있다.

이러한 이유로 인덱스 키값을 변형하는 쿼리문은 지양해야 한다.

[인덱스 키값 변형으로 인덱스를 못 사용하는 쿼리문]

SELECT S.ID, S.NAME
  FROM STUDENT S
 WHERE 1=1
   AND SUBSTRING(S.ID, 1, 3) = 100  # 함수로 인한 인덱스 사용 불가 
   AND LENGTH(S.ID) = 5             # 함수로 인한 인덱스 사용 불가
;

[튜닝 쿼리문]

SELECT S.ID, S.NAME
  FROM STUDENT S
 WHERE 1=1
   AND S.ID BETWEEN 10001 AND 10099
;

✅ 불필요한 함수 제거

쿼리문에 불필요한 함수가 있는 경우 해당 함수를 제거함으로써 쿼리문의 성능을 높일 수 있다.

테이블 설계 시 NAME과 GENDER 컬럼에 제약 조건으로 NOT NULL이 명시되어 있는 경우 IFNULL() 함수를 사용할 이유가 없어진다.

따라서 테이블의 제약 조건 등의 이유로 함수의 사용이 불필요하다고 판단되면 함수를 제거하는 게 성능상 좋다.

[불필요한 함수가 포함된 쿼리문]
SELECT
	S.ID
    , IFNULL(S.NAME, 'NO_NAME') AS NAME
    , IFNULL(S.GENDER, 'NO_GENDER') AS GENDER
  FROM STUDENT S
 GROUP BY IFNULL(S.GENDER, 'NO_GENDER')
;
[튜닝 후]
SELECT
	S.ID
    , S.NAME
    , S.GENDER
  FROM STUDENT S
 GROUP BY S.GENDER
;

✅ 컬럼 타입을 명확하게 사용하기

MySQL에서 조건문(WHERE)에 컬럼 타입을 틀리게 작성해도 DB엔진에 의해 묵시적으로 데이터 타입 변환이 이루어진다.

하지만, 데이터 타입 변환 과정에서 인덱스가 무시되어 Full Table Scan으로 동작할 가능성이 높다.

따라서 컬럼의 타입과 동일한 타입으로 조건식을 작성해야 한다.

GENDER 컬럼에서 0은 여자, 1은 남자로 처리한 다 가정

GENDER 컬럼의 타입은 varcha

[타입 변환이 발생하는 쿼리문]
SELECT
	S.ID
    , S.NAME
  FROM STUDENT S
 WHERE 1=1
   AND S.GENDER = 1
;
[튜닝 후]
SELECT
	S.ID
    , S.NAME
  FROM STUDENT S
 WHERE 1=1
   AND S.GENDER = '1'
;

✅ LIKE절 대체하기

LIKE절은 광범위한 데이터 조회를 생각하고 사용해야 한다.

만약 2000년대생의 학생을 조회하기 위해서 조건문에 LIKE '2000%' 처럼 작성하면 와일드 카드('%')에 어떠한 값이라도 들어올 수 있기 때문에 데이터 조회 범위과 증가한다.

아래는 LIKE '2000%'절의 탐색 대상

2000-01-01

2000-12-31

2000-99-99
....

2000adkfygadskfyagsdk

날짜 같은 값의 끝이 결정된 조건문의 경우 LIKE 대신 BETWEEN을 사용하여 범위 조회를 하는 게 이상적이다.

[불필요한 범위 까지 탐색하는 쿼리문]

SELECT
	S.ID
    , S.NAME
  FROM STUDENT S
 WHERE 1=1
   AND S.BIRTHDAY LIKE '2000%'
;

[튜닝 후]

SELECT
	S.ID
    , S.NAME
  FROM STUDENT S
 WHERE 1=1
   AND S.BIRTHDAY BETWEEN '2000-01-01' AND '2000-12-31'
;

✅ 불필요한 DISTINCT 제거

쿼리문의 중복을 제거하기 위해서는 DISTINCT 키워드를 사용한다.

하지만, 중복이 발생할 수 없는 쿼리문에서 DISTINCT 키워드를 사용하면 불필요한 임시 테이블이 메모리에 생성(Using temporary)된다.

이러한 이유로 쿼리문에 습관적으로 DISTINCT 키워드 사용을 지양해야 한다.

학생 테이블(STUDENT)과 반 테이블(CLASS)을 조인 하여 데이터를 조회할 때 학생 테이블의 PK를 사용했다.

학생 ID(PK)는 유일한 값이고, 특정 반에서도 학생 ID(FK)는 유일한 값이기 때문에 조인한다고 해서 중복이 발생하지 않는다.
(여러 반에 동일한 학생 ID를 생성할 수 없다는 가정)

[불필요한 DISTINCT가 포함된 쿼리문]
SELECT DISTINCT
	S.STUDENT_ID
    , S.NAME
  FROM STUDENT S
  JOIN CLASS C
    ON S.STUDENT_ID = C.STUDENT_ID
 WHERE 1=1
   AND C.TEACHER_ID = '1001'
;
[튜닝 후]
SELECT
	S.STUDENT_ID
    , S.NAME
  FROM STUDENT S
  JOIN CLASS C
    ON S.STUDENT_ID = C.STUDENT_ID
 WHERE 1=1
   AND C.TEACHER_ID = '1001'
;

✅ 인덱스 컬럼 순서 고려하기

인덱스는 데이터를 저장할 때 외쪽 컬럼부터 정렬하여 저장한다.

인덱스 idx_tmp(a,b,c)은 a 컬럼부터 정렬하고 그다음 b, c 순서로 정렬된다.

a->b->c 순서로 정렬

이러한 이유로 인덱스 컬럼의 정렬 순서 및 설계 순서를 고려하여 쿼리문을 작성해야 한다.

[인덱스 설계 순서를 무시한 쿼리문]
GROUP BY에서 인덱스 사용을 위해 임시 테이블이 메모리에 생성(Using temporary)하여 데이터 정렬 후 그룹핑을 수행한다.
# INDEX : IDX_STUDENT_NAME_AGE(NAME, AGE)
SELECT
    S.NAME
    , S.AGE
    , COUNT(1) AS COUNT
  FROM STUDENT S
 GROUP BY S.AGE, S.NAME
;
[튜닝 후]
GROUP BY에서 인덱스 순서에 맞게 컬럼명을 명시한 경우 별도의 정렬 작업이 필요 없다.
# INDEX : IDX_STUDENT_NAME_AGE(NAME, AGE)
SELECT
    S.NAME
    , S.AGE
    , COUNT(1) AS COUNT
  FROM STUDENT S
 GROUP BY S.NAME, S.AGE
;

✅ 드라이빙 & 드리븐 테이블 확인하기

드라이빙 테이블과 드리븐 테이블은 옵티마이저에 의해 결정된다. 하지만, 옵티마이저의 결정이 꼭 최고의 선택이 아니다.

이러한 이유로 쿼리문이 느리다고 판단되면 드라이빙 테이블과 드리븐 테이블이 정확하게 설정되어 있는지 확인할 필요가 있다.

[잘못된 드라이빙 테이블 선택]
학생 수가 2,000명, 반 수가 36개일 때

드라이빙 테이블이 STUDENT, 드리븐 테이블이 CLASS이면 조건 절에서 4학년인 학생만을 필터링하고 드리븐 테이블과 데이터 비교를 하기 때문에 총 데이터 접근 수는 333 * 36 = 11,988번이다.

하지만 드라이빙 테이블이 CLASS, 드리븐 테이블이 STUDENT이면 드라이빙 테이블을 경량화할 수 있는 조건이 없기 때문에 테이블의 총접근 수는 2000 * 36 = 72,000번이다.

이러한 이유로 드라이빙 테이블은 경량화할 수 있는 테이블이 선택될 수 있도록 힌트를 줘야 한다.
# 튜닝 전 드라이빙 테이블이 CLASS, 드리븐 테이블이 STUDENT 이라 가정
SELECT
	S.STUDENT_ID
    , S.NAME
  FROM STUDENT S    # 학생 수가 2,000명
  JOIN CLASS C      # 반 수가 36개
    ON S.STUDENT_ID = C.STUDENT_ID
 WHERE S.GRADE = 4
;
[튜닝 후]
SELECT /*! STRAIGHT_JOIN */
	S.STUDENT_ID
    , S.NAME
  FROM STUDENT S    # 학생 수가 2,000명 -> 333명으로 경량화 (조건문으로 필터링 진행)
  JOIN CLASS C      # 반 수가 36개
    ON S.STUDENT_ID = C.STUDENT_ID
 WHERE S.GRADE = 4
;

⚡️ 불필요한 조인 제거하기

데이터의 불필요한 조인은 데이터의 스캔양만 늘리는 행위다.

특히 조건문 때문에 어쩔 수 없이 조인을 수행한 경우라면, 조인 방식이 아닌 다른 방식으로 쿼리문을 작성할 수 있는지 확인해 보는 게 좋다.

[불필요한 조인이 포함된 쿼리]

# PK : EMP(emp_id)
SELECT COUNT(1) AS cnt
  FROM EMP E
  JOIN DEPT D ON E.emp_id = D.emp_id
 WHERE D.LEVEL = 'A'
;