Query Optimization (2)

목표는 쿼리가 더 빠르게 동작할 수 있도록 하는 것. 필요한 작업을 세 가지로 구분한다.

• 알맞은 인덱스 생성하기
• '느리지 않은' 쿼리문 작성하기
• 쿼리 성능을 알맞게 측정하기

1. 알맞은 인덱스 생성하기

검색에 자주 사용되는 컬럼이 있다면 인덱스를 생성함으로써 더 빠른 검색 쿼리를 기대해볼 수 있다. 단, 삽입에 소요되는 시간 비용이 증가한다는 것은 유의해야 한다.

기본적으로 테이블에서 레코드를 검색하고자 한다면 MySQL은 테이블의 첫 레코드부터 마지막 레코드까지 Full Scan을 하면서 해당하는 레코드를 찾을 것이다. 하지만 레코드가 10만개, 100만개 이상이라면? Full Scan에 너무 많은 시간을 소요하게 된다. 따라서 Full Scan을 하지 않고 빠르게 레코드를 검색할 수 있도록 우리는 인덱스를 생성해 사용한다.

http://tcpschool.com/mysql/mysql_index_create

1-1. Syntax

MySQL에서 인덱스와 관련된 문법은 다음과 같다.

-- post 테이블의 lat, long 컬럼에 대한 인덱스 생성
create index idx_location on post (lat);
create index idx_location on post (lat);
-- 또는 한번에?
create index idx_location on post (lat, long);

-- 인덱스 보기
show index from post;

단, 현재 프로젝트에서는 TypeORM을 사용하므로 위처럼 쿼리를 통해 인덱스를 생성하지 않을 것이다. TypeORM에서 인덱스를 추가하는 것은 다음 문서를 참고한다. 단일 컬럼, 다중 컬럼에 대해 인덱스를 생성하는 것까지 좋은 예제 코드를 제공한다.

typeorm/indices.md at master · typeorm/typeorm

1-2. 어떤 컬럼에 인덱스를 설정해야 하는가? 선택의 기준

https://yurimkoo.github.io/db/2020/03/14/db-index.html

1. 카디널리티가 높은가?
   • 카디널리티가 높아 중복의 정도가 낮을수록 인덱스를 사용하기에 적합하다.
2. 선택도가 낮은가?
   • 선택도 : 전체 레코드 수에 대해 평균적으로 검색에 의해 선택되는 레코드의 수
   • 선택도가 낮을수록 인덱스를 사용하기에 적합하다.
3. 활용도가 높은가?
   • 많이 활용된다면 당연히 인덱스를 통해 성능을 개선하는 것이 좋을 것이다.
4. 중복도가 낮은가?
   • 1번의 카디널리티와는 관계 없음. 하나의 컬럼에 대해 중복된 인덱스가 많아지면 안된다는 의미.

1-3. 주의사항

• 다중 컬럼 인덱스를 사용할 경우, 인덱스의 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있다. 즉, 두 번째 컬럼의 정렬은 첫 번째 컬럼이 똑같은 레코드에서만 의미가 있다.
• Tips: https://www.burndogfather.com/238

2. '느리지 않은' 쿼리문 작성하기

최적의 성능을 내는 쿼리문을 작성하는 것도 중요하다고 판단하였다.

Query Optimization (1) · boostcampwm-2021/WEB19-sajagachi Wiki

3. 쿼리 성능을 알맞게 측정하기

성능을 알맞게 측정하는 작업은 성능을 향상시키는 것만큼이나 중요한 것 같다.

3-1. 캐시 삭제하기

MySQL의 캐시 작업으로 인해 정확한 쿼리 성능 측정이 어려울 수 있다.

MySQL : 쿼리 성능 측정을 방해하는 요소를 제거하기

3-2. 쿼리 실행 계획 보기 (EXPLAIN)

EXPLAIN 명령어를 사용해 쿼리 실행 계획을 알아낼 수 있다. 쿼리문이 인덱스를 잘 활용하고 있는지, 테이블 Full Scan이 발생하지는 않는지 조사할 수 있을 것 같다.

실행계획을 분석해서 SQL 성능튜닝을 해보자(feat.MySQL)

EXPLAIN 결과에서 각 항목별 의미

MySQL Explain 실행계획 사용법 및 분석 - Useful Guide

MySQL Workbench에서 Visual Explain 보기

MySQL Workbench의 VISUAL EXPLAIN으로 인덱스 동작 확인하기 - LINE ENGINEERING

3-3. 시간 측정

단순히 쿼리를 실행할때마다 하단에 출력되는 소요 시간을 볼 수도 있겠지만, 다음과 같이 프로파일링 기능을 켜는 방법도 있다고 한다. (MySQL 최신 버전에서 작동하지 않을 수 있음) 시간 측정을 하기 전에 충분히 많은 레코드를 생성해두어야 하겠다. 대충 100만개 정도?

Measuring actual MySQL query time

최적화 작업 전후 비교

Before	After