[우아한테크코스] 10월 25일 TIL
[DB] 조회 성능 개선하기
SQL
FROM: 데이터를 가져올 집합 명시
WHERE: 데이터를 걸러낼 조건
GROUP BY: 걸러낸 데이터를 그룹화할 조건
HAVING: GROUP BY로 그룹화한 데이터를 다시 조건에 맞게 필터링할 조건
SELECT: 집계할 데이터 명시
ORDER BY: 데이터 정렬
서브쿼리는 SELECT절, WHERE절, FROM절에 추가할 수 있다.
JOIN 시에 A에서 B를 조인하면 드라이빙 테이블(outer table)은 A, 드리븐 테이블(inner table)은 B이다. 가능한 적은 결과가 나오는 테이블을 드라이빙 테이블이 되어야 한다. 드라이빙으로부터 읽은 레코드의 건수만큼 매번 드리븐 테이블을 풀 테이블 스캔해야하기 때문이다. (mysql에서는 join, cross join, inner join 모두 동일하다)
MySQL 아키텍쳐
최적화 대상
-
Client(Connection 연결하고 요청 오가는 부분)
쿼리 요청 수 줄이기
PreparedStatement로 쿼리문장 분석, 컴파일, 실행 단계 캐싱
DB Connection pool 활용해 Connection 재사용
Paging이나 Fetchsize 조정으로 요청 사이즈 줄이기
-> Spring Data Access. Public Cloud, MySQL 버전을 잘 활용하면 알아서 최적화 되어있다.
-
Database Engine
파일시스템에 저장된 데이터가 조회되면 그 데이터 조회 시 I/O가 일어나지 않도록 한다.
서버 파라미터 튜닝
-
FileSystem
SSD를 사용해 SQL을 최적화해 필요 이상의 데이터 블럭을 읽지 않도록 방지
1. SELECT 시에는 꼭 필요한 칼럼만
2. WHERE 절에 가급적 별도의 연산 걸지 않기
3. LIKE 절에 와일드카드 문자열(%)은 String 앞에 오지 않도록
4. SELECT DISTINCT, UNION DISTINCT와 같이 중복값 제거는 오랜 시간이 걸리므로 되도록 사용하지 않을 것
5. 같은 내용이라면 group by 연산 시 HAVING 보다는 WHERE절이 우선순위가 빠르므로 효율적
6. 3개 이상의 테이블을 INNER JOIN할 때는 크기가 가장 큰 테이블을 FROM 절에, INNER JOIN 절에는 남은 테이블을 작은 순서대로 배치
7. 자주 사용하는 데이터의 형식에 대해서는 미리 전처리된 테이블 따로 보관하기
SQL 쿼리 동작 방식
-
Query Cache
SQL 문이 key, 실행 결과가 value가 되어 Map 형태로 저장되다가 데이터 변경 시 모두 없어져야 한다는게 성능 저하를 일으켜 8.0 버전부터 삭제됨
-
Parser
서버가 이해할 수 있도록 쿼리문을 분리
-
Preprocessor
문법을 확인
-
Optimization
실행계획(Query execution plan) 작성
- 쿼리 분석: where인지 join인지
- 인덱스 선택: 테이블 조건과 인덱스를 통해 사용할 인덱스 결정
- 조인 처리: 여러 테이블에 조인이 된 경우 어떤 순서로 테이블을 읽을지 결정
-
Handler
실행 엔진(Query Execution Engine)의 요청에 따라 데이터를 디스크에 저장하고 읽어오는 역할 수행
실행 계획
mysql workbench에서 돋보기를 통해 실행 계획을 조회할 수 있다.
EXPLAIN SELECT * FROM 'table';를 통해서도 조회할 수 있다.
0.2가 쿼리문에 대한 비용(evaluation cost)을 나타내고, 1row가 조회한 로우 수를 나타낸다.
-
id
sql문이 수행되는 순서
같은 id는 join의 경우
-
Select_type
- 👍simple: 단순한 select 문
- 👍Primary: 서브쿼리를 감싸는 외부 쿼리, union 있으면 첫번째 select 문
- subquery: 독립적으로 수행되는 서브쿼리(select, where절에 추가된 경우)
- 👍Derived: from 절에 작성된 서브쿼리
- union: union, union all로 합쳐진 select
- 👎dependent subquery: 서브쿼리가 바깥쪽 select 쿼리에 정의된 컬럼 사용
- 👎dependent union: 외부에 정의된 컬럼을 union으로 결합된 쿼리에서 사용
- materialized: in 절 구문의 서브쿼리를 임시 테이블에 생성한 후 조인 수행
- 👎uncacheable subquery: RAND(), UUID() 등 조회마다 결과 달라지는 경우 사용
-
type
- 👍system: 테이블에 데이터가 없거나 하나만 있는 경우
- 👍const: 조회되는 데이터가 하나인 경우
- 👍eq_ref: 조인이 수행될때 드리븐 테이블의 데이터에 PK 혹은 고유 인덱스로 단 하나의 데이터 조회
- ref: eq_ref와 같으나 데이터가 2개 이상
- 👎index: 인덱스 풀 스캔
- range: 인덱스 레인지 스캔
- 👎all: 테이블 풀 스캔
-
key
옵티마이저가 실제로 선택한 인덱스
-
rows
sql문 수행을 위해 접근하는 데이터의 모든 행 수
-
extra
- distinct: 중복 제거
- using where: where 절로 필터
- 👎using temporary: 중간결과 저장을 위한 임시 테이블 생성 (distinct, group by, order by의 구문 포함된 경우)
- 👍using index: 물리적인 데이터 파일이 아닌 인덱스만 읽어서 처리
- 👎using filesort: 정렬
DB 서버 튜닝
1. 메모리 튜닝
-
Thread
커넥션 하나에 thread 하나로 메모리 할당과 해제에 드는 비용 줄이기
## 현재 쓰레드(연결) 개수 확인 SELECT * FROM performance_schema.threads SHOW STATUS LIKE '%THREAD%'; -
Caching
내부적으로 하나의 Global Buffer
Thread별로 Thread Buffer
Thread Buffer에 많은 메모리가 할당되면 성능은 좋아지지만 설정값 * Connection 수만큼 확보해 Connection이 급증하면 메모리가 부족해져 swap 발생할 수 있음
-
innodb_buffer_pool_size
InnoDB의 데이터나 인덱스를 캐시하기 위한 메모리 상의 영역으로 Global Buffer라 시스템 전체 메모리의 80% 수준으로 설정 (최대 512MB)
-
key_buffer_size
인덱스를 메모리에 저장하는 버퍼 크기로 총 메모리의 25%로 설정
key_buffer 사용률: 1-((Key_reads/Key_read_requests)*100)
사용률 90% 이상이면 효율적으로 설정되어 있다고 판단
-
2. 커넥션 튜닝
SHOW VARIABLES LIKE '%max_connection%';
SHOW STATUS LIKE '%CONNECT%';
SHOW STATUS LIKE '%CLIENT%';
-
Connection_timeout
mysql이 클라이언트로부터 접속 요청을 받을 때 몇초 기다릴지 설정하는 변수
기본 값 5초, 수정할 필요 없음
-
Interactive_timeout
터미널에서 접속할 시간
기본 값 8시간, 1시간 정도가 좋음
-
Wait_timeout
커넥션이 접속한 후 쿼리가 들어올 때까지 기다리는 시간
DBMS에서는 이걸 낮춰 sleep하는 Connection을 정리해 전체 성능 향상
값을 너무 낮추면 너무 잦은 커넥션이 생기므로 15~20 값 설정
aborted client 는 2% 아래
-
max_connections
서버가 허용하는 최대의 커넥션 수
일반적으로 150~250 개 설정
접속이 많고 고용량인 경우 1000개 정도의 높은 값 설정 가능
too many connection 에러가 발생하지 않도록 적절한 값 설정
-
back_log
max_connection 설정값 이상의 접속이 발생할 때 얼마의 커넥션을 큐에 보관할 지에 대한 설정
기본값 50, 접속 많은 서버는 늘리는 것이 좋음
Index
-
Sequential Access
물리적으로 인접한 페이지를 차례대로 읽는다. 인접한 페이지를 여러 개 읽는 데에 용이하다.
-
Random Access
정해진 순서 없이 이동해 디스크의 물리적인 움직임이 필요하다. 다중 페이지 읽기가 불가능하다. 주소를 알고 있을 때 더 쉽게 접근할 수 있다.
DB table에서 데이터를 찾는 방법
-
Table Full Scan
sequential access + MultiBlock I/O
파티션 활용과 병렬처리로 해결 가능
-
Index Range Scan
random access + Single Block I/O
레코드 하나를 읽기 위해 매번 I/O가 발생한다는 단점이 있어 큰 테이블에서 소량 데이터를 검색할 때 유리
따라서 이 횟수를 줄이기 위해 스캔 범위를 줄이는 것이 중요
인덱스는 MySQL은 InnoDB를 사용해 B-Tree 알고리즘을 기본적으로 사용해 루트 노드, 브랜치 노드, 리프 노드로 이루어져 있다.
스캔의 시작과 끝 지점을 찾는 수직적 탐색이 있고, 데이터를 찾는 수평적 탐색이 있다.
루트 노드와 브랜치 노드는 인덱스 키와 자식 노드 정보로 구성된 페이지로 구성되어 있어 range를 줄이기 위해 스캔의 시작과 끝 지점을 찾기 위한 수직적 탐색이 들어간다.
리프 노드는 양방향 Linked List로 구성되어 있다. 필요한 컬럼을 인덱스가 모두 갖고 있는 경우는 인덱스만 스캔하면 되지만 아닌 경우는 ROWID(데이터블럭 주소 + 로우 번호)를 통해 테이블 액세스가 필요하다. Primary Key는 Clustered Index로 순차적으로 저장되어 있어 물리적 위치를 알 수 있다. 데이터 페이지가 버퍼 풀에 있는지 확인해보고 없으면 디스크에서 읽어온다. 읽으면 버퍼풀에 적재한다. 이게 수평적 탐색이다.
Secondary Index 탐색 -> Primary key Index 탐색 으로 이루어짐
하지만 결과적으로 인덱스를 통해 테이블에 접근하는 경우가 많아질 경우 table full scan 처리 시간은 동일한데 랜덤 액세스 때문에 오히려 느려진다.
여러 쓰레드가 db에 접속해서 트랜잭션 때문에 병목이 발생하므로 OLTP 환경에서는 index가 좋음
생성
column 주는 순서가 매우 중요하다. 순서에 따라서 index table이 생성되기 때문에
CREATE INDEX `idx_user_id` ON `subway`.`programmer` (id)
CREATE INDEX `idx_user_Country_OpenSource` ON `subway`.`programmer` (Country, OpenSource)
삭제
DROP INDEX `idx_user_id` ON `subway`.`programmer`
-
index range scan
인덱스 선두 컬럼이 조건절에 존재해야 한다.
CREATE INDEX `idx_user_Country_OpenSource` ON `subway`.`programmer` (Country, OpenSource) -
index full scan
리프 블록 전체 스캔할 때 (select *)
-
index unique scan
인덱스가 존재하는 컬럼이 중복값이 입력되어 있지 않아 ‘=’로 검색해 하나로만 가면 될 때
index가 걸려있어도 scan할 범위가 많으면 random보다 sequential이 효율적이기에 full table scan이 들어간다. mysql은 mysql engine과 storage engine이 있는데 어느 엔진에서 filter를 거는 것이 효율적일지를 판단하고 실행하는 것이다.
인덱스 튜닝
인덱스 스캔 효율화
랜덤 액세스 최소화
## 프로세스 목록
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');
## 최근 실행된 쿼리 이력 기능 활성화
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
테이블 액세스 최소화
-
인덱스 컬럼 추가
인덱스는 정렬되므로 조건절에 해당하는 pk 범위를 줄여 랜덤 액세스 횟수가 줄어들게 할 수 있다.
쿼리 튜닝
-
인덱스
-
인덱스 컬럼을 가공하지 말기
이미 정렬된 인덱스를 가공하면 인덱스를 탈 수 없기 때문에 아래와 같이 컬럼 가공하지 말 것
- <>, NOT IN, NOT BETWEEN과 같은 NOT-EQUAL로 비교된 경우
- LIKE ‘%??’
- SUBSTRING(column, 1, 1), DAYOFMONTH(coulmn)과 같이 인덱스 칼럼이 변형된 경우
- WHERE char_column = 10 과 같이 데이터 타입이 다른 비교
-
인덱스 순서 고려하기
인덱스는 항상 정렬된어 있으므로 Group by, Order by와 같은 정렬 연산이 필요 없다.
조건절에 항상 들어가거나 자주 사용하는 컬럼을 인덱스로 선정
=으로 자주 조회하는 컬럼을 앞 쪽에 인덱스 부여
예를 들어
과세코드+이름+연령으로 인덱스를 부여했을 때, 과세코드로 정렬 -> 이름으로 정렬 -> 연령으로 정렬된 상태이므로과세코드나과세코드 + 이름의 인덱스가 불필요 -
인덱스를 제대로 사용하는지 확인
조건 절에서 더 효율적으로 인덱스를 처리하고 있는지 확인, 더 적은 필터링을 할 수 있도록 고민
Covered Index와 같이 인덱스 과정에서 얻은 정보만으로 처리할 수 있어 테이블 액세스가 발생하지 않는 쿼리로 인덱스를 가장 잘 활용하는 경우
-
복합 인덱스 시 범위 검색컬럼을 뒤에
between과 같은 범위 검색을 뒤에 두어야 한다. 최소한의 range를 만들 수 있도록 해야 한다.
정렬된 상태에서 어떻게 탐색이 들어가야 좋을 지 고민
사원번호가 선행되면 2칸, 입출입시간이 선행되면 5칸인 것처럼..
-
인덱스 구성 확인하기
## 테이블 / 인덱스 크기 확인 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;
-
-
조인문
-
조인 연결 key 들은 양쪽 다 인덱스를 가지는 것이 좋다.
한쪽에만 인덱스가 있으면 Join buffer, 양쪽에 있으면 중첩 루프 조인을 한다. 중첩 루프 조인이 효율적인데다가 인덱스가 있는 테이블이 드라이빙 테이블이 되므로 주의
-
데이터가 적은 테이블을 랜덤액세스하는 것이 좋다.
드라이빙 테이블의 데이터가 적을 경우 중첩 루프 조인을 수행해 드리븐 테이블의 많은 양의 데이터에 인덱스 스캔을 하는데 드리븐 테이블의 pk를 사용하지 않으면 비효율적이다.
인덱스를 사용하면 pk를 알아내서 랜덤액세스 하는게 너무 많아지면 드라이빙이 적은게 좋지 않을 수도 있다.
-
모수 테이블 크기를 줄이는 것이 좋다.
전체로 join을 거는 것보다 서브쿼리나 from 절에 미리 걸어서 가지고 오는 테이블 사이즈를 줄이는 것을 고민해보자
-
서브쿼리보다는 조인문 활용하기
5.6 버전 이후로 semi join, materialized와 같은 서브쿼리 최적화가 이루어지지만 update, delete는 여전히 안되므로 조인문 사용하기
-
쿼리 튜닝 미션
쿼리연습
-
활동중인(Active) 부서의 현재 부서관리자 중 연봉 상위 5위안에 드는 사람들이 최근에 각 지역별로 언제 퇴실했는지 조회해보세요. (사원번호, 이름, 연봉, 직급명, 지역, 입출입구분, 입출입시간)
-
1차 1.527sec, 답은 맞지만 5명 제한 없음
select 사.사원번호, 원.이름, 급.연봉, 직.직급명, 사.지역, 사.입출입구분, 사.입출입시간 from 급여 급 inner join 부서관리자 관 on 관.사원번호 = 급.사원번호 inner join 부서 부 on 관.부서번호 = 부.부서번호 inner join 사원출입기록 사 on 사.사원번호 = 관.사원번호 inner join 사원 원 on 원.사원번호 = 사.사원번호 inner join 직급 직 on 직.사원번호 = 원.사원번호 where 사.입출입구분 = 'O' and 직.직급명 = 'Manager' and 부.비고 = 'Active' and 급.종료일자 > 사.입출입시간 and 직.종료일자 > 사.입출입시간 order by 급.연봉 desc;
-
2차 0.654sec, where 절을 각 join 문에 서브쿼리로 주어 개선, 5명 제한 없음
select 사.사원번호, 사원.이름, 급.연봉, 직.직급명, 사.지역, 사.입출입구분, 사.입출입시간 from 사원 사원 inner join 부서관리자 관 on 관.사원번호 = 사원.사원번호 inner join (select 사원번호, 직급명 from 직급 where 직급명 = 'Manager' and 종료일자 = '9999-01-01') 직 on 직.사원번호 = 관.사원번호 inner join (select 사원번호, 연봉 from 급여 where 종료일자 = '9999-01-01') 급 on 급.사원번호 = 직.사원번호 inner join (select 부서번호 from 부서 where 비고 = 'Active') 부 on 관.부서번호 = 부.부서번호 inner join (select 사원번호, 지역, 입출입구분, 입출입시간 from 사원출입기록 where 입출입구분 = 'O') 사 on 사.사원번호 = 급.사원번호 order by 급.연봉 desc, 사.입출입시간 desc;
-
3차 0.427sec, 부서관리자 연봉 top 5명 거르고 그들의 입출입 기록 도출
select 부서관리자들.사원번호, 부서관리자들.이름, 부서관리자들.연봉, 부서관리자들.직급명, 사.지역, 사.입출입구분, 사.입출입시간 from (select 사원.사원번호, 사원.이름, 급.연봉, 직.직급명 from 사원 inner join 부서관리자 관 on 관.사원번호 = 사원.사원번호 inner join (select 사원번호, 직급명 from 직급 where 직급명 = 'Manager' and 종료일자 = '9999-01-01') 직 on 직.사원번호 = 관.사원번호 inner join (select 사원번호, 연봉 from 급여 where 종료일자 = '9999-01-01') 급 on 급.사원번호 = 직.사원번호 inner join (select 부서번호 from 부서 where 비고 = 'Active') 부 on 관.부서번호 = 부.부서번호 limit 5) 부서관리자들 inner join (select 사원번호, 지역, 입출입구분, 입출입시간 from 사원출입기록 where 입출입구분 = 'O') 사 on 사.사원번호 = 부서관리자들.사원번호 order by 부서관리자들.연봉 desc, 사.입출입시간 desc;
-
index 추가하기
실행계획 실행 시 아래와 같이 Full Table Scan 임을 볼 수 있었다.
하지만 비용과 시간이 가장 많이 드는 부분은 사원출입기록에 접근하는 부분이었고, 이에 따라 사원번호+입출입시간을 인덱스로 잡아서 처리해보았다.
create index i_입출입구분_사원번호 on 사원출입기록 (사원번호, 입출입시간);
결과는 0.013sec이었다.
실행기록에서도 full table scan이 아닌 non-unique key로 바뀌었고 row와 비용도 줄어들었음을 알 수 있었다.
하지만 사원번호도 단일, 입출입시간도 단일인데 두개를 같이 거는 것에 의미가 있을까? 라고 생각했고 사원번호만으로 인덱스를 걸어보았다.
create index i_사원번호 on 사원출입기록 (사원번호);
결과는 0.012sec으로 유사하거나 조금 빨랐다.
그렇다면 더 많은 컬럼을 인덱스로부터 얻어낼 수 있는 복합 인덱스가 유리하다고 판단했고, 첫번째 인덱스로 처리하였다.
인덱스 설계
Coding as a Hobby와 같은 결과를 반환하세요.
-
쿼리
select hobby, count(*) / (select count(*) from programmer)*100 as percent from programmer group by hobby desc;결과 441ms
-
인덱스 적용
create index i_hobby on programmer (hobby);결과 177ms
-
desc 제거하고, round로 반올림 추가하고, id에 pk를 달아주었다.
select hobby, round(count(*) / (select count(*) from programmer)*100, 1) from programmer group by hobby;결과 50ms
결론적으로 인덱스 적용과 pk가 성능 개선에 가장 큰 효과를 준 것 같다!
프로그래머별로 해당하는 병원 이름을 반환하세요.
-
쿼리 작성
SELECT c.programmer_id, h.name FROM subway.covid c inner join programmer p on p.id = c.programmer_id inner join hospital h on h.id = c.hospital_id결과 0.030
-
인덱스 적용
create index i_covid_programmer_hospital on covid (programmer_id, hospital_id); create index i_hospital_id_name on hospital (id, name);결과 0.026
hospital에도 id, name에 인덱스를 주고 싶었지만 text 형식이라 안되고, 32개의 데이터를 id, name으로 인덱싱하는 것은 불필요하다고 생각했는데 이 생각이 맞는지 모르겠습니다..
프로그래밍이 취미인 학생 혹은 주니어들이 다닌 병원 이름을 반환하고 id 기준 정렬
-
쿼리 작성
select p.id, h.name from (select id from programmer where (hobby = 'Yes' and student = 'Yes') or years_coding = '0-2 years') p inner join covid c on c.programmer_id = p.id inner join hospital h on h.id = c.hospital_id;결과 0.082
-
인덱스 적용
위에서 i_covid_programmer_hospital을 적용했음에도 인덱스가 적용되지 않았다.
따라서 hospital id에 pk를 적용해주었다.
결과 0.033
다음과 같이 인덱스 적용이 성공적으로 되었고, 시간도 줄었다!
서울대병원에 다닌 20대 India 환자들을 병원에 머문 기간별로 집계
-
쿼리 작성
select c.stay, count(c.id) from covid c inner join (select id, country from programmer where country = 'India') p on p.id = c.id inner join (select id, age from member where age between 20 and 29) m on c.member_id = m.id inner join (select id from hospital where name = '서울대병원') h on h.id = c.hospital_id group by c.stay;결과 42ms
-
인덱스 추가
hospital id pk, unique 추가
Programmer index 추가
create index i_p on programmer (country, id);결과 38ms
시간은 크게 개선되지 않았지만 programmer의 비용이 개선되었다.
서울대병원에 다닌 30대 환자들을 운동 횟수별로 집계하세요.
-
쿼리작성
select p.exercise, count(p.id) from programmer p inner join covid c on c.programmer_id = p.id inner join (select id from hospital where name = '서울대병원') h on h.id = c.hospital_id inner join (select id, age from member where age between 30 and 39) m on c.member_id = m.id group by p.exercise; -
인덱스 추가
covid에 아래 인덱스 추가
create index i_c on covid (programmer_id, member_id, hospital_id);Hospital name archer(255)로 변경하고 인덱스 추가
create index i_h on hospital (name);결과 67ms
잘 동작하고 있음을 알 수 있다!
A. 페이징 쿼리
웹 애플리케이션에서는 테이블의 내용을 1~20건 단위로 나눠서 보여주는 것이 일반적입니다. 테이블의 레코드를 일정 단위로 잘라서 조회하는 것을 페이징 쿼리라고 합니다. 일반적으로는 아래와 같이 작성합니다.
SELECT * FROM subway.programmer ORDER BY id LIMIT 20, 10;
이렇게 작성할 경우에 10개의 레코드만 읽는게 아니라, 첫번째 레코드부터 20번째 레코드까지 읽어서 버리고 10개의 레코드를 읽어 반환합니다. 이에 뒷 페이지로 갈수록 성능이 급격히 저하됩니다. 따라서 아래와 같이, 테이블의 PK를 WHERE 조건절에 넣어주는 것이 좋습니다.
SELECT * FROM subway.programmer
WHERE subway.programmer.id >= 20000
ORDER BY id LIMIT 0, 10;
Spring Data JPQL은 LIMIT 명령어를 지원하지 않으므로, Pageable 객체를 활용해야 합니다.
@Query("SELECT * FROM subway.programmer WHERE subway.programmer.id >= ?1")
List<User> findAll(Pageable pg);
B. MySQL Replication with JPA
-
MySQL Replication의 master/slave는 1:n관계입니다. master는 갱신쿼리를 바이너리 로그파일로 기록하고, 이 로그파일의 내용이 slave로 전송되어 순차적으로 실행함으로써 복제됩니다. 따라서 MySQL Replication은 준동시성입니다. I/O 스레드가 비동기로 동작하기에 마스터에서 생성한 바이너리 로그가 슬레이브에 수신되기 전에 장애가 날 경우 손실이 발생할 수 있습니다.
-
데이터조작쿼리(INSERT, UPDATE, DELETE)는 마스터로, 데이터조회쿼리(SELECT)는 슬레이브로 받아서 부하를 분산할 수 있습니다.
아래 설정을 참고하여 MySQL Replication 구성을 해봅니다. 애플리케이션과 DB를 연결하는 작업은 다음 단계에서 진행합니다.
master 서버 설정
$ docker run --name mysql-master -p 13306:3306 -v ~/mysql/master:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=masterpw -d mysql
$ docker exec -it mysql-master /bin/bash
$ mysql -u root -p
mysql> CREATE USER 'replication_user'@'%' IDENTIFIED WITH mysql_native_password by 'replication_pw';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: binlog.000002
Position: 683
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
slave 서버 설정
$ docker run --name mysql-slave -p 13307:3306 -v ~/mysql/slave:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=slavepw -d mysql
$ docker exec -it mysql-slave /bin/bash
$ mysql -u root -p
mysql> SET GLOBAL server_id = 2;
mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.1', MASTER_PORT = 13306, MASTER_USER='replication_user', MASTER_PASSWORD='replication_pw', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=683;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
애플리케이션 설정
spring.datasource.hikari.master.username=root
spring.datasource.hikari.master.password=masterpw
spring.datasource.hikari.master.jdbc-url=jdbc:mysql://localhost:13306/subway?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC&allowPublicKeyRetrieval=true
spring.datasource.hikari.slave.username=root
spring.datasource.hikari.slave.password=slavepw
spring.datasource.hikari.slave.jdbc-url=jdbc:mysql://localhost:13307/subway?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC&allowPublicKeyRetrieval=true
public class ReplicationRoutingDataSource extends AbstractRoutingDataSource {
public static final String DATASOURCE_KEY_MASTER = "master";
public static final String DATASOURCE_KEY_SLAVE = "slave";
@Override
protected Object determineCurrentLookupKey() {
boolean isReadOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
return (isReadOnly)
? DATASOURCE_KEY_SLAVE
: DATASOURCE_KEY_MASTER;
}
}
@Configuration
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = {"nextstep.subway"})
class DataBaseConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.hikari.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.hikari.slave")
public DataSource slaveDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean
public DataSource routingDataSource(@Qualifier("masterDataSource") DataSource master,
@Qualifier("slaveDataSource") DataSource slave) {
ReplicationRoutingDataSource routingDataSource = new ReplicationRoutingDataSource();
HashMap<Object, Object> sources = new HashMap<>();
sources.put(DATASOURCE_KEY_MASTER, master);
sources.put(DATASOURCE_KEY_SLAVE, slave);
routingDataSource.setTargetDataSources(sources);
routingDataSource.setDefaultTargetDataSource(master);
return routingDataSource;
}
@Primary
@Bean
public DataSource dataSource(@Qualifier("routingDataSource") DataSource routingDataSource) {
return new LazyConnectionDataSourceProxy(routingDataSource);
}
}
public List<Line> findLines() {
...
@Transactional(readOnly = true)
public List<StationResponse> findAllStations() {
findLines()메서드는 master에서findAllStations()메서드는 slave에서 조회합니다.@Transactional(readOnly = true)를 사용할 경우 slave를 활용합니다.
