쏚쏚스포이드

이번에 쿼리문 성능개선 업무를 진행하며 판단한 기준을 정리
MySQL + INNO DB 스토리지 엔진 기준입니다. 다른 데이터베이스는 조금 다를 수 있습니다

//이번 예시인 example_table

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    column1 INT,
    column2 INT,
    grade VARCHAR(255),
    score INT
);

인덱스를 걸지 않고도 성능 개선이 가능한 경우

특정 상황에서는 INDEX가 없어도 쿼리문의 변형만으로 성능개선이 가능하다.

EXPLAIN SELECT * FROM example_table WHERE grade = 'A' AND score = 30;

=>

+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | example_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  4   | 25.00    | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+

type : ALL
=> Table Full Scan으로 해당 테이블 전체 데이터 스캔

• ‘AND’ 를 활용한 다중 WHERE절일 때
  • WHERE를 적용했을 때 고정적으로 더 적은 개수의 컬럼이 나오는 조건이 존재하는지
    • 위의 경우 학점과 점수의 연관 관계를 생각했을 때 score의 조건이 grade보다 더 적은 수의 컬럼으로 나오는게 명확

      example_table
      id	column1	column2	grade	score
      1	1	1	a	96
      2	1	1	a	97
      3	1	1	a	98
      4	1	1	a	99
      5	1	1	a	99
      6	1	1	b	92
      7	1	1	b	91
      8	1	1	b	90

  • 위의 조건에 맞는 컬럼(score)을 WHERE절의 앞에 위치 시킨다
    SELECT * FROM example_table WHERE score = 30 AND grade = 'A';

⇒ 물론 명확하지 않은 조건일 때는 해당 where절이 반드시 성능 향상을 시킬 것이라고 기대할 수 없으며, 인덱스를 활용하지 못할 때만 사용할 수 있는 방법

인덱스를 활용해 성능 개선

인덱스의 특징

• 인덱스는 해당 테이블의 빠른 조회를 위한 컬럼의 관련 데이터주소가 일방적으로 담긴 테이블이다
• 인덱스를 생성했을 때 기본적으로 정렬되어 있기에 이후 트리(B+Tree)탐색을 진행한다.
• 인덱스는 생성된 테이블에 대하여 기본키에 대한 인덱스가 하나 생성(예외인 경우 간혹 있음)되며, 추가로 인덱스 컬럼을 선정해 걸어둘 수 있다.

인덱스를 사용할 때 주의점

• 인덱스는 기존의 테이블에서 추가적으로 테이블을 생성하는 것이기 때문에 남발해서는 안됨
  • insert시 부하가 많이 올라감
• 인덱스가 복합적으로 있을 때는 DB가 자체적으로 판단해 여러 인덱스를 활용해 쿼리의 실행을 돕는다
  • 단, 쿼리의 실행 계획이 반드시 옳고 빠른 실행이 아닐 수 있음
• 인덱스 기능은 조회 에만 성능 향상 효과를 볼 수 있으며 삽입, 삭제, 갱신 시에 기존의 테이블 수정 + 인덱스 테이블 수정의 상황 때문에 조회 외의 작업도 빈번하게 일어난다면 전반적인 성능이 하락할 수도 있다

단일 컬럼 인덱스

• 하나의 컬럼만을 선택해 인덱스 테이블 생성
• 위의 WHERE절에서 이야기했던 것과 동일하게 선택성이 좋은 컬럼을 사용하는 것
  ex) grade가 아닌 score

다중 컬럼 인덱스

• 조건이 여러개 들어있는 조회문일 경우 단일 컬럼 인덱스보다 좋은 효율을 낼 수 있음
• 조회시 다중컬럼 인덱스에 없는 컬럼도 조회한다면 인덱스 테이블이 아닌 실제 테이블도 접근해야하기에 고려해서 설정해야 한다.
• 컬럼 순서가 중요
  • 선택성이 좋은 컬럼을 앞으로 구성
  • 범위 조건 컬럼보다 등치(=) 컬럼을 앞으로 구성
• 한개의 인덱스로 여러 sql을 커버할 수 있도록 설계하면 좋다
• 모든 조건을 인덱스로 구성하기엔 어려움이 있으니, 조건별로 카운트 해보고 적은 건수의 데이터 위주로 생성이 유리

ETC

쿼리를 실행하며 테스트 할 때는 캐시로 인해 명확한 판단이 힘들어질 수 있으므로 반드시 버퍼 캐시를 비워야함