이 포스팅에서 SQL 쿼리를 튜닝하는 방법을 모두 다룰 수는 없습니다. 자주 가이드하는 격언을 실습을 통해 확인할 수 있도록 작성해보았습니다.
이 포스팅을 작성하는데, Real MySQL, 업무에 바로 쓰는 SQL 튜닝, 친절한 SQL 튜닝, SQL 튜닝의 시작 등의 책을 참고하였습니다.
0. 실습환경 세팅
$ docker run -d -p 23306:3306 brainbackdoor/data-tuning:0.0.3
Shell
복사
•
workbench를 설치한 후 localhost:23306 (ID : user, PW : password) 로 접속합니다.
1. 인덱스
A. 인덱스 컬럼을 가공하지 마세요.
EXPLAIN
SELECT *
FROM tuning.employee
WHERE SUBSTRING(id, 1, 4) = 1100
AND LENGTH(id) = 5
SQL
복사
EXPLAIN
SELECT *
FROM tuning.employee
WHERE id BETWEEN 11000 AND 11009
SQL
복사
•
인덱스 컬럼을 가공하지 않아야, 리프블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈출 수 있습니다.
◦
<>, NOT IN, NOT BETWEEN과 같은 NOT-EQUAL로 비교된 경우
◦
LIKE '%??'
◦
SUBSTRING(column, 1, 1), DAYOFMONTH(coulmn)과 같이 인덱스 칼럼이 변형된 경우
◦
WHERE char_column = 10 과 같이 데이터 타입이 다른 비교
B. 인덱스 순서를 고려하세요.
EXPLAIN
SELECT first_name, sex, COUNT(1) AS 카운트
FROM tuning.employee
GROUP BY first_name, sex
SQL
복사
EXPLAIN
SELECT first_name, sex, COUNT(1) AS 카운트
FROM tuning.employee
GROUP BY sex, first_name
SQL
복사
•
인덱스는 항상 정렬 상태를 유지하므로 인덱스 순서에 따라 ORDER BY, GROUP BY를 위한 소트 연산을 생략할 수 있습니다.
•
조건절에 항상 사용하거나, 자주 사용하는 컬럼을 인덱스로 선정합니다.
•
'=' 조건으로 자주 조회하는 컬럼을 앞쪽에 둡니다.
•
추가적으로, 아래 세 인덱스는 중복입니다. 마지막 인덱스를 남기고 모두 삭제해주세요.
◦
과세코드
◦
과세코드 + 이름
◦
과세코드 + 이름 + 연령
C. 인덱스를 제대로 사용하는지 확인하세요.
EXPLAIN
SELECT id
FROM tuning.employee
WHERE join_date LIKE '1989%'
AND id > 100000;
SQL
복사
SELECT
(SELECT COUNT(1) FROM tuning.employee WHERE join_date LIKE '1989%') AS '입사일자 필터',
(SELECT COUNT(1) FROM tuning.employee WHERE id > 100000) AS '사원번호 필터';
SQL
복사
EXPLAIN
SELECT id
FROM tuning.employee
WHERE join_date >= '1989-01-01' AND join_date < '1990-01-01'
AND id > 100000;
SQL
복사
Covered Index
인덱스 스캔과정에서 얻은 정보만으로 처리할 수 있어 테이블 액세스가 발생하지 않는 쿼리를 의미합니다.
EXPLAIN SELECT a.*
FROM (
-- 서브쿼리에서 커버링 인덱스로만 데이터 조건과 select column을 지정하여 조인SELECT id
FROM subway.member
WHERE age BETWEEN 30 AND 39
) AS b JOIN programmer a ON b.id = a.id
SQL
복사
D. 복합 인덱스시 범위 검색컬럼을 뒤에 둬야 해요.
CREATE INDEX `idx_employee_id_time` ON `tuning`.`record` (employee_id,time);
DROP INDEX `idx_employee_id_time` ON `tuning`.`record`;
CREATE INDEX `idx_time_employee_id` ON `tuning`.`record` (time,employee_id);
DROP INDEX `idx_time_employee_id` ON `tuning`.`record`;
SQL
복사
EXPLAIN SELECT * FROM tuning.record WHERE employee_id = 110183 AND time BETWEEN '2020-01-01' AND '2020-08-30';
SQL
복사
E. 인덱스 구성 확인하기
## 테이블 / 인덱스 크기 확인SELECT
table_name,
table_rows,
round(data_length/(1024*1024),2) as 'DATA_SIZE(MB)',
round(index_length/(1024*1024),2) as 'INDEX_SIZE(MB)'
FROM information_schema.TABLES
where table_schema = 'subway';
## 미사용 인덱스 확인
SELECT * FROM sys.schema_unused_indexes;
## 중복 인덱스 확인
SELECT * FROM sys.schema_redundant_indexes;
SQL
복사
2. 조인문
A. 조인 연결 key 들은 양쪽 다 인덱스를 가지고 있는 것이 좋아요.
한쪽에만 인덱스가 있을 경우, Join Buffer를 사용하여 성능 개선을 하나 일반적인 중첩 루프 조인에 비해 효율이 떨어집니다.
또한 테이블 크기와 상관없이 인덱스가 있는 테이블이 드라이빙 테이블이 되므로 주의해야 합니다.
B. 데이터가 적은 테이블을 랜덤액세스해야 해요.
EXPLAIN
SELECT
mapping.employee_id,
department.id
FROM tuning.employee_department mapping,
department
WHERE mapping.department_id = department.id
SQL
복사
EXPLAIN
SELECT STRAIGHT_JOIN
mapping.employee_id,
department.id
FROM tuning.employee_department mapping,
department
WHERE mapping.department_id = department.id
SQL
복사
드라이빙 테이블의 데이터가 적을 경우, 중첩 루프 조인을 수행하며 드리븐 테이블의 많은 양의 데이터에 인덱스 스캔을 합니다. 다만, 드리븐 테이블의 Primary Key를 사용하지 않을 경우 많은 양의 데이터에 랜덤 액세스로 테이블에 접근하므로 비효율적일 수 있어요. 드라이빙, 드리븐 테이블 관계는 대체로 드라이빙 테이블의 모수가 적은 것이 선호되나, 확인을 해볼 필요는 있어요.
C. 모수 테이블 크기를 줄이세요
EXPLAIN
SELECT employee.id, employee.last_name, employee.first_name, employee.join_date
FROM tuning.employee, tuning.salary
WHERE employee.id = salary.id
AND employee.id BETWEEN 10001 AND 50000
GROUP BY employee.id
ORDER BY SUM(salary.annual_income) DESC
LIMIT 150,10;
SQL
복사
EXPLAIN
SELECT employee.id, employee.last_name, employee.first_name, employee.join_date
FROM (
SELECT id
FROM tuning.salary
WHERE id BETWEEN 10001 AND 50000
GROUP BY id
ORDER BY SUM(salary.annual_income) DESC
LIMIT 150,10
) salary,
employee
WHERE employee.id = salary.id;
SQL
복사
D. 서브쿼리보단 조인문을 활용하세요.
•
MySQL 5.6 이후로 서브쿼리 최적화가 이루어집니다. (SEMI JOIN, MATERIALIZED 등) 다만, 8.0까지도 UPDATE, DELETE 등는 서브쿼리 최적화가 지원되지 않아요. 가능하면 JOIN 을 사용합시다.
EXPLAIN EXTENDED
SELECT *
FROM tuning.employee
WHERE id IN (SELECT id FROM salary);
SHOW WARNINGS;
SQL
복사
3. MySQL 서버 성능 개선대상 식별하기
## 프로세스 목록
SHOW PROCESSLIST;
## 슬로우 쿼리 확인
SELECT query, exec_count, sys.format_time(avg_latency) AS "avg latency", rows_sent_avg, rows_examined_avg, last_seen
FROM sys.x$statement_analysis
ORDER BY avg_latency DESC;
## 성능 개선 대상 식별
SELECT DIGEST_TEXT AS query,
IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,
COUNT_STAR AS exec_count,
SUM_ERRORS AS err_count,
SUM_WARNINGS AS warn_count,
SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000) AS exec_time_total,
SEC_TO_TIME(MAX_TIMER_WAIT/1000000000000) AS exec_time_max,
SEC_TO_TIME(AVG_TIMER_WAIT/1000000000000) AS exec_time_avg_ms, SUM_ROWS_SENT AS rows_sent,
ROUND(SUM_ROWS_SENT / COUNT_STAR) AS rows_sent_avg, SUM_ROWS_EXAMINED AS rows_scanned,
DIGEST AS digest
FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC
## I/O 요청이 많은 테이블 목록
SELECT * FROM sys.io_global_by_file_by_bytes WHERE file LIKE '%ibd';
## 테이블별 작업량 통계
SELECT table_schema, table_name, rows_fetched, rows_inserted, rows_updated, rows_deleted, io_read, io_write
FROM sys.schema_table_statistics
WHERE table_schema NOT IN ('mysql', 'performance_schema', 'sys');
## 총 메모리 사용량 확인
SELECT * FROM sys.memory_global_total;
## 스레드별 메모리 사용량 확인
SELECT thread_id, user, current_allocated
FROM sys.memory_by_thread_by_current_bytes
LIMIT 10;
## 최근 실행된 쿼리 이력 기능 활성화
UPDATE performance_schema.setup_consumers SET ENABLED = 'yes' WHERE NAME = 'events_statements_history'
UPDATE performance_schema.setup_consumers SET ENABLED = 'yes' WHERE NAME = 'events_statements_history_long'
## 최근 실행된 쿼리 이력 확인
SELECT * FROM performance_schema.events_statements_history
SQL
복사
4. MySQL 서버 튜닝
•
각 설정값이 어떤 의미를 가지는지 확인해봅니다.
A. 메모리 튜닝
a. Thread
MySQL은 커넥션마다 하나의 Thread를 생성하여 요청을 처리합니다. Thread를 메모리에 할당하고 해제하는데 비용이 크므로 이를 줄일 필요가 있습니다.
## 현재 쓰레드(연결) 개수 확인
mysql> SELECT * FROM performance_schema.threads
mysql> SHOW STATUS LIKE '%THREAD%';
SQL
복사
•
thread_cache_size는 지나치게 높여둘 필요는 없으며 일반적으로 threads_connected의 피크 치보다 약간 낮은 수치 정도를 설정하는 것이 좋습니다. 이를 통해 쓰레드가 생성되고 소멸되면서 겪게 되는 메모리, 각종 자원, 시간 등의 낭비를 줄일 수 있습니다.
b. Caching
버퍼는 mysqld에서 내부적으로 하나만 확보되는 Global Buffer와 Thread(Connection)별로 확보되는 Thread Buffer가 있습니다. Thread Buffer에 많은 메모리를 할당하면 성능이 올라가지만, 설정값 * Connection 수만큼 확보하므로 Connection이 갑자기 늘어나면 메모리가 부족해져 swap이 발생할 수도 있습니다.
•
innodb_buffer_pool_size : InnoDB의 데이터나 인덱스를 캐시하기 위한 메모리상의 영역, 글로벌 버퍼이므로 크게 할당할 것을 권합니다. 보통 시스템 전체 메모리의 80% 수준으로 설정합니다.(최대 512MB)
mysql> SHOW STATUS LIKE '%key%';
SQL
복사
•
key_buffer_size : 인덱스를 메모리에 저장하는 버퍼 크기를 의미하며, 보통 총 메모리 크기의 25% 정도로 설정합니다.
•
Key Buffer 사용률은 1 - ((Key_reads/Key_read_requests) * 100)입니다. 90% 이상일 경우 key_buffer_size가 효율적으로 설정되어 있다고 판단할 수 있습니다.
B. 커넥션 튜닝
mysql> SHOW VARIABLES LIKE '%max_connection%';
mysql> SHOW STATUS LIKE '%CONNECT%';
mysql> SHOW STATUS LIKE '%CLIENT%';
SQL
복사
•
connect_timeoutMySQL이 클라이언트로부터 접속 요청을 받는 경우 몇 초까지 기다릴지를 설정하는 변수로, 기본 값은 5초이며 일반적으로 수정할 필요는 없습니다.
•
Interactive_timeout‘mysql>’과 같은 콘솔이나 터미널 상에서의 클라이언트 접속을 의미하며, 기본 값으로 8시간이 잡혀 있으나 1시간 정도로 낮추는 것이 좋습니다.
•
wait_timeout접속한 후 쿼리가 들어올 때까지 기다리는 시간으로, 접속이 많은 DBMS에서는 이 값을 낮춰 sleep 상태의 Connection들을 정리하여 전체 성능을 향상시킬 수 있습니다. 하지만 값을 너무 낮추게 되면 지나치게 잦은 커넥션이 발생할 수 있으므로, 보통 15~20 사이의 값을 설정합니다. Aborted client는 2% 아래인 것이 바람직한 상태입니다.
•
max_connections서버가 허용하는 최대한의 커넥션 수입니다. 서버의 사양에 따라 달라질 수 있으며 일반적으로 120~250개 정도로 설정합니다. 하지만 접속이 많고 고용량 서버의 경우 1000개 정도의 높은 값을 설정하는 것도 가능하니, Too many connection 에러가 발생하지 않도록 적절한 값을 설정하는 것이 중요합니다.
•
back_logmax_connection 설정값 이상의 접속이 발생할 때 얼마만큼의 커넥션을 큐에 보관할지에 대한 설정 값으로, 기본 값은 50이며 접속이 많은 서버의 경우 이 값을 늘릴 필요가 있습니다.