[MySQL 성능 최적화] 데이터베이스 튜닝 실습 8 - 생성한 인덱스를 사용하지 않는 경우 SQL문 튜닝

iiingkeep·2024년 7월 24일

Sql튜닝 db dbeaver mysql sql 데이터베이스 성능개선 성능최적화 인덱스

Database

목록 보기

10/21

생성한 인덱스를 사용하지 않는 경우 SQL문 튜닝

목표

100만개의 데이터를 삽입한 테이블에서 특정 컬럼을 기준으로 인덱스를 생성한 후 데이터 조회 시 인덱스를 사용하지 않는 경우에 대해 알아보고 그런 상황에서 효율적으로 조회할 수 있는 방법에 대해 탐구한다.

성능 측정 수치

소요시간(ms)

성능 개선 시 성능이 개선 됐는지 정확한 판단을 하기 위해 개선 이전의 수치와 개선 이후의 수치를 정확히 측정해서 비교할 것

쿼리를 여러 번 실행해 평균적으로 어느 정도의 시간이 소요되는지 측정

과정

테이블 생성 및 확인

DROP TABLE IF EXISTS users; 

 CREATE TABLE users (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(100),
     salary INT,
     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

users 테이블에 100만개의 데이터 삽입 및 확인

 SET SESSION cte_max_recursion_depth = 1000000; 

 INSERT INTO users (name, salary, created_at)
 WITH RECURSIVE cte (n) AS
 (
   SELECT 1
   UNION ALL
   SELECT n + 1 FROM cte WHERE n < 1000000 
 )
 SELECT 
     CONCAT('User', LPAD(n, 7, '0')) AS name, 
     FLOOR(1 + RAND() * 1000000) AS salary, 
     TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at
 FROM cte;

name과 salary 컬럼에 대한 인덱스 생성

SELECT * FROM users
		WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY); 
  
  EXPLAIN SELECT * FROM users
		WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

각각의 SQL문에 대한 실행 계획 조회

# 이름을 기준으로 내림차순 정렬하여 전체 유저 조회
EXPLAIN SELECT * FROM users 
		ORDER BY name DESC;

# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE SUBSTRING(name, 1, 10) = 'User000000';

# 2달치 급여(salary)가 1000 이하인 유저 조회
SELECT * FROM users
WHERE salary * 2 < 1000
ORDER BY salary;

결과 1

이름을 기준으로 내림차순 정렬하여 전체 유저 조회

User000000으로 시작하는 이름을 가진 유저 조회

2달치 급여(salary)가 1000 이하인 유저 조회

name과 salary 컬럼에 인덱스를 각각 생성했지만 위의 조건을 만족하는 결과를 얻기 위해 데이터를 조회한 결과 모두 Full Table Scan을 진행하며 비효율적으로 데이터를 조회하는 것을 확인

위의 SQL문을 변경하여 각각 다시 조회

# 이름을 기준으로 내림차순 정렬하여 전체 유저 조회
 EXPLAIN SELECT * FROM users 
 ORDER BY name DESC LIMIT 10;

# User000000으로 시작하는 이름을 가진 유저 조회
 EXPLAIN SELECT * FROM users
 WHERE name LIKE 'User000000%';

 # 2달치 급여(salary)가 1000 이하인 유저 조회
 EXPLAIN SELECT * FROM users
 WHERE salary < 1000 / 2
 ORDER BY salary;

결과 2

이름을 기준으로 내림차순 정렬하여 10명의 유저 조회

User000000으로 시작하는 이름을 가진 유저 조회

2달치 급여(salary)가 1000 이하인 유저 조회

이전에 생성했던 인덱스를 사용해 효율적인 조회를 하는 것을 확인

결론

특정 컬럼을 기준으로 인덱스를 생성했지만 조회 시 인덱스가 사용되지 않는 경우에 대해 먼저 알아보고, SQL문을 수정한 뒤 다시 조회 시 인덱스를 잘 활용하는 경우를 비교해 봤다.

인덱스가 사용되지 않는 이유는 각각 다음과 같았다.

많은 양의 데이터를 한 번에 조회 시 Index Range Scan보다는 Full Table Scan이 더 효율적이라고 판단하여 옵티마이저는 인덱스를 사용하지 않는다.
SQL문에서 인덱스를 생성한 기준 컬럼이 가공됐다.
여기서 가공이란, SUBSTRING(name, 1, 10)이나 salary * 2과 같이 함수나 연산 등을 이용해 컬럼을 처리한 것을 뜻한다.

해결 방안

많은 양의 데이터를 한 번에 조회하는 것은 성능 측면에서 좋지 않다. LIMIT이나 WHERE등의 구문을 이용해 한 번에 조회하는 데이터 양을 조정한다.
한 번에 많은 양의 데이터를 조회해야 한다면 굳이 사용되지 않을 인덱스를 생성하지 않는다.
SQL문에서 컬럼을 가공하지 않은 상태로 조건이 성립하도록 조정한다.