쿼리 튜닝

실행계획을 적극적으로 확인해보자.

• 실행할 쿼리 앞에 EXPLAIN 키워드를 붙여서 실행하면 해당 쿼리 실행 계획을 알 수 있다.
• 실행 계획에는 실행할 쿼리가 실행될 때에 관여되는 사항들에 대한 정보가 출력된다.
• 이를 통해서 쿼리의 어떤 부분에서 자원을 많이 잡아먹는지 파악할 수 있다.

WHERE절에서의 CASE문을 사용하는 것은 조심해야 한다.

• CASE문에서 사용되는 컬럼은 인덱스가 걸려있어도 인덱스를 타지 않는다.
• OR 키워드를 통해서 대체할 수 있다.

현재 선택된 테이블이 가장 최선의 선택일까?

• 요구사항에 따라서 각 경우마다 다양한 테이블들이 사용될 것이다.
• 정말 적을 떄는 1개로 끝나는 경우도 있겠지만,
  보통은 여러 개의 테이블이 함께 사용될 것이다.
• 실행할 쿼리에 명시된 테이블들이 현재 요구사항에 알맞는 최선의 선택인지 항상 확인해봐야 한다.
• 이를 위해서는 테이블 구조를 확실히 파악하고 있어야 한다.

서브 쿼리가 늘 나쁜 것은 아니다.

• 서브 쿼리가 경우에 따라서는 과한 호출량을 만들어 낼수 있는 것은 사실이다.
• 다만 용량이 작거나 구조가 잘 만들어진 테이블의 경우에는 어설프게 FROM절쪽에 옮겨서
  JOIN 하는 것보다 서브 쿼리로 호출하는 것이 빠른 경우도 있다.
• 실제로 실행 계획을 통해서 예상 I/O를 확인하다보면 JOIN보다 서브 쿼리가 더 효율적인 경우가 존재한다.
• 물론 JOIN으로 뺴는 게 INNER JOIN으로 뺸다면 더 빨라지는 경우가 많긴 하겠지만
  항상 그런 경우만 있다는 보장은 없다.

필요한 속성만 조회하자.

• SELECT문에 *를 사용할 경우 해당 테이블에 있는 모든 속성에 대해서 조회할 수 있다.
• 그러나 필요하지도 않는 속성을 조회하게 되면 불필요한 자원 낭비가 발생한다.
• 또한, 가독성이 떨어지기 때문에 추후 유지 보수가 어려울 수 있다.

데이터는 전체가 아닌 일부만 가져오자.

• 당연한 얘기이긴 하지만 SELECT문을 실행하여 50건의 데이터를 가져온다고 가정했을 떄,
  전체를 가져와서 50건을 탐색하는 것보다는
  처음부터 50건만 검색하도록 하는 것이 성능도 올라가고 자원도 절약된다.
• DBMS에 따라 적용 방법이 다르다.
• 페이징과도 연관이 있다.

DISTINCT 키워드를 통한 중복 제거 방식은 피하자.

• 결과 데이터의 중복을 제거하기 위해 DISTINCT를 사용하면
  추가적인 처리 부담이 발생하여 성능 저하가 발생할 수 있다.
• DISTINCT보다는 GROUP BY를 통해 중복된 값을 그룹화하는 방식을 사용하면 된다.
• GROUP BY를 통한 중복 제거는 필요한 컬럼만 선택해서 그룹화할 수 있다는 장점이 있다.

대규모 쿼리는 사용량이 적은 시간대에 실행하는 것이 좋다.

• 기본적으로 각 서비스마다 사용량이 많은 시간과 적은 시간이 있다.
• 서비스의 사용량이 많은 시간은 당연히 데이터베이스 서버에도 대량의 트래픽이 발생한다.
• 대용량 트래픽이 발생하는 시간대에 대규모 쿼리를 실행하게 되면
  데이터베이스 서버에도 영향이 크지만,
  결과적으로는 시스템 전체의 성능을 떨어뜨려 다른 사용자에게 불편을 줄 수도 있으니
  대규모 쿼리는 사용량이 적은 시간대에 실행하는 것이 좋다.

조인은 필요한 만큼만 하자.

• 당연한 얘기지만 효율적인 서비스를 위해서는 데이터베이스 조회도 효율적으로 해야 한다.
• 컬럼도 필요한 만큼만 명시해야 하며, 조인할 테이블도 필요한 만큼만 해야한다.
• 조인되는 테이블이 늘어날 수록 조회 대상이 되는 컬럼의 수는 덧셈이 아닌 곱셈이 되는 것이기 때문에 조인되는 테이블은 매우 신중히 결정해야 한다.

WHERE절에서 좌항에 함수를 사용하는 것은 지양하자.

• WHERE에서 인덱스를 적용하고 싶은 컬럼이 존재한다면 해당 컬럼은 함수를 사용하게 하면 안 된다.
• 기본적으로 좌항에 함수를 사용하게 되면 인덱스를 적용받지 못 한다.

인덱스는 필요한 인덱스만 만들어야 한다.

• 인덱스는 쿼리의 조회 속도를 올려주는 분명 고마운 존재인 것은 맞다.
• 그러나 인덱스는 기본적으로 기존에 있던 데이터를 통해서 만들어지기 떄문에
  실제로 물리적인 공간을 차지한다.
• 인덱스가 많아질 수록 물리적인 공간을 많이 차지하게 되니
  요구사항을 정말 철저히 분석하여서 필요한 인덱스만 생성해야 한다.

인덱스는 함수에도 사용할 수 있다.

• 현재 이 게시글을 작성하고 있는 시점에는 비슷비슷한 소규모 프로젝트만 하다보니
  정확하게 알고 있는 것은 아니지만 주로 인덱스는 컬럼을 기준으로만 생성했다.
• 그러나 인덱스는 컬럼에만 걸 수 있는 게 아닌 함수에도 걸 수 있다.
• 예시 : CREATE INDEX name_replace_dot ON user_info ((REPLACE(NAME, '.', '')));

Virtual Column을 사용해 보자.

• 기본적으로 WHERE절에서 좌항에 함수를 사용하면 인덱스를 타지 않는다.
  • Virtual Column을 추가하면 해당 컬럼에 인덱스를 추가하여 함수 사용 시에도 인덱스를 타게 할 수 있다.
  • 함수를 사용해 비교해야 하는 경우가 많은 경우에만 사용하는 것이 좋다.
• 함수를 통해 가공된 원본 컬럼의 값이 저장된다.
  • 실제로 값이 저장되는 것이기 때문에 물리적인 공간을 차지한다.
• INSERT 시에 실제로 값을 넣을 수는 없다.
• 원본 컬럼이 UPDATE되면 Virtual Column도 함께 UPDATE 된다.
• 가상 컬럼이나 임시 컬럼 등으로 부른다.
• 예시 (Virtual Column 추가)
  • ALTER TABLE user_info ADD COLUMN name_replace_dot VARCHAR(30) GENERATED ALWAYS AS (REPLACE(name, '.', '')) STORED;
• 예시 (Virtual Column에 대한 인덱스 추가)
  • CREATE INDEX name_replace_dot ON user_info (name_replace_dot);

OR 연산자가 많이 쓰인다면 UNION ALL을 고려해보자.

• WHERE절에서 OR 연산자가 너무 많이 쓰이면
  옵티마이저가 전체 테이블 스캔을 수행하여 레코드를 검색하는 경우가 발생한다.
• UNION ALL을 사용하면 각각의 쿼리가 인덱스를 타서 효율성이 올라갈 수도 있다.
• OR와 UNION ALL 중에서 어떤 걸 쓸지는 경우에 따라서 판단하는 것이 좋다.

LIKE 연산자 사용 시 와일드카드를 용도에 맞게 사용하자.

• 기본적으로 인덱스는 원본 데이터를 바탕으로
  오름차순이나 내림차순으로 정렬된 데이터의 복사본이다.
• 즉, LIKE 연산 시 인덱스를 잘 타기 위해서는 LIKE '검색할_문자열%'처럼 사용하는 것이 좋다.
• LIKE '%검색할_문자열%'처럼 사용하게 되면 테이블의 모든 행을 스캔한다.
• DBMS에 따라 방법이 차이가 나긴 하지만 큰 문자열의 경우에는 FULLTEXT INDEX라는 방법이 있다.

데이터베이스 스키마를 최적화하자.

• 데이터베이스의 구조가 좋다면 당연히 쿼리의 성능또한 올라간다.
• 다양한 최적화 방법이 있다.
  • 테이블 정규화
  • 테이블 역정규화
  • 최적의 데이터 유형 사용
  • NULL 값을 최대한 배제
  • 하나의 테이블이 너무 많을 갯수의 컬럼을 갖지 않게 하기

WHERE절에서 사용하는 컬럼에 연산 걸지 않기

• 비교 대상이 되는 컬럼에 연산을 걸게 되면 미리 정렬해둔 값의 목록인 인덱스를 타지 못해서 Full Table Scan이 발생한다.
• Virtual Column같은 방법이 있긴 한데 처음부터 연산을 걸지 않는 게 좋긴 하다.

파티션으로 분리를 해보자.

• 파티션으로 구분할 경우에 조건에 따른 범위 검색 시에 성능이 증가한다.

AUTO_INCREMENT를 사용하자.

• AUTO_INCREMENT는 기본 키로 사용되기 위해 최적화 되어 있다.

ON DUPLICATE KEY UPDATE를 사용하자.

• UPSERT라고도 불리는 방식으로써, INSERT와 UPDATE를 하나의 쿼리에서 동아가게 하기 위한 키워드다.
• INSERT와 UPDATE를 각각 진행하여 서버에 불필요하게 왔다갔다 해야하는 필요가 없어진다.
• ※ ON DUPLICATE KEY UPDATE는 MySQL 5.7.21 이전 버전에서는 데드락을 발생시킬수도 있다.

날짜를 비교할 때 BETWEEN A AND B는 사용하지 말자.

• 날짜 BETWEEN A AND B보다는 날짜 >= A AND 날짜 <= B 형식을 사용하는 것이 좋다.
• 날짜 BETWEEN A AND B 형식은 날짜 >= A AND 날짜 <= B 형식에 비해서 CPU Cycle의 소모가 더 많다.
• 특히 BETWEEN A AND B는 예상 외로 함수 취급 당해서 인덱스를 타지 않는다.

날짜 검색이 많은 테이블은 INT형도 고려해보자.

• UNIX_TIMESTAMP([date]) 함수 사용 시 날짜를 INT형으로 바꿔준다.
• 날짜 자체를 처음부터 INT형으로 저장해서 비교하면 DATETIME형을 쓰는 것보다 인덱스를 잘 타게 된다.
• FROM_UNIXTIME(unix_timestamp[,format])를 사용하면 UNIX_TIMESTAMP([date])를 통해 INT형으로 변환된 데이터를 다시 DATE 형식으로 바꿔준다.
• 검색 자체는 빨라지긴 하지만 이로 인한 문제점이 발생할 수도 있다.
  • 단순 검색 시 문자열이 아닌 숫자로 노출
  • 날짜 연산을 위한 단계가 많아짐
• 예시
  • REG_DATE > UNIX_TIMESTAMP('2024-01-01 00:00:00') AND REG_DATE < UNIX_TIMESTAMP('2024-01-31 23:59:59')

조인을 의심해보자.

• 조인을 위해 명시한 테이블들은 명시한 순서대로 연결되는 것이 아니다.
• 옵티마이저는 내부적으로 조인 순서를 배치하는 역할도 하는데,
  그 결과가 좋을 때도 있지만 나쁠 때도 있다.
• SELECT 키워드 뒤에 STRAIGHT_JOIN 키워드를 추가하여 해당 SELECT문은 명시된 순서대로 조인한다는 것을 옵티마이저에 알려주자.
• STRAIGHT_JOIN을 사용하면 성능이 올라갈 수도 있지만 무조건은 아니니 잘 판단해야 한다.
  • 명시된 테이블의 순서를 변경해야 하는 경우도 있다.
  • 조인의 순서보다는 조회할 속성이라던가 WHERE절의 조건에 문제가 있을 수도 있다.