[MYSQL] 쿼리 속도개선

1. 이슈

 속도가 너무 오래 걸려 불편을 주는 Mysql 쿼리를 발견하여 이를 개선하고자 했다.

속도저하의 원인으로는 Join시 데이터가 많은 테이블의 값을 읽어오는데 오래 걸렸던 것으로 짐작된다. 

이를 해결하고자 인덱스, 쿼리개선,  DB 격리수준 변경 등 다양한 방법을 진행했다.

 

2. 쿼리

	SELECT
		A.name , B.code, C.text
	FROM SmallTable A
	INNER JOIN
		 MiddleTable B ON A.user_code = B.user_code
	INNER JOIN
		 LargeTable C ON A.user_code = C.user_code 
         AND C.ROLE = (SELECT ROLE FROM LargeTable
 				WHERE user_code = A.user_code AND STATUS = '1')
	WHERE A.user_code = (값);

예를 들어 데이터 개수가 A < B < C인 테이블들을 조인한다고 가정하자

이 쿼리는 C 테이블을 조인하면서 서브쿼리도 탐색해야 하기에 상당한 시간이 걸리게 된다.

이 쿼리에 대한 설명을 얻고자 한다면 EXPLAIN을 사용할 수 있다.

	EXPLAIN SELECT
		A.name , B.code, C.text
	FROM SmallTable A
	INNER JOIN
		 MiddleTable B ON A.user_code = B.user_code
	INNER JOIN
		 LargeTable C ON A.user_code = C.user_code 
         AND C.ROLE = (SELECT ROLE FROM LargeTable
 				WHERE user_code = A.user_code AND STATUS = '1')
	WHERE A.user_code = (값);

결과

이런 식으로 JOIN이 어떤 식으로 이뤄지는지 확인할 수 있다.

이 중 유의있게 봐야 할 것은 type이다. 

이 type이 ALL이라면 테이블의 처음부터 끝까지 조회하는 것이기에 데이터가 많을경우 오랜 시간이 걸리게 된다.

(필자는 이미 index를 사용한 상황이라 ALL타입은 나오지 않았다.)

또한 MYSQL 옵티마이저는 JOIN순서를 변경하기 때문에 사용자가 원하는 A -> B -> C 순서로 JOIN이 진행되지 않고

변경되어 진행됨을 알 수 있다.

이를 개선하기위해 먼저 첫 번째로 인덱스를 활용할 수 있다.

 

3. 인덱스 활용

어쩌면 쿼리의 속도를 개선하기위해 가장 먼저 해야 하는 단계라고 볼 수 있다.

인덱스는 아래처럼 생성할 수 있다.

CREATE [UNIQUE] INDEX 인덱스_이름 ON 테이블_이름 (열 이름)

-- SmallTable 인덱스 생성
CREATE INDEX USER_CODE_IDX ON SmallTable (user_code);

 

효율적인 INDEX를 만들기 위해서는 아래와 같은 컬럼에 인덱스를 걸면 좋다.

1.  WHERE 절에 자주 등장하는 컬럼
2.  ORDER BY 절에 자주 등장하는 컬럼
3.  JOIN이 자주 사용되는 열

위의 쿼리의 속도개선을 위해서는  JOIN절의 조건으로 사용되는 user_code들을 인덱스로 설정하는 것이 좋다.

추가적으로 서브쿼리의 속도도 개선시키려면 WHERE 조건에 사용되는 user_code와 STATUS를 묶어 인덱스를 설정하는 방법도 있다.

 

INDEX 장단점

 

장점

• 검색 속도가 상승
• 해당 쿼리의 부하가 줄어들어서, 결국 시스템 전체의 성능이 향상

단점

• 인덱스는 대략 테이블 크기의 10% 공간이 추가로 필요 (인덱스 페이지 때문)
• SELECT 가 아닌 데이터의 변경 작업 (INSERT, UPDATE, DELETE)이 자주 일어나면 오히려 성능에 악영향 (페이지 정렬 작업 때문)

4. 쿼리개선

두 번째 방법으로는 쿼리 자체를 개선하는 방법이 있다.

먼저는 테이블들의 조인순서를 올바르게 수정할 필요가 있다.

테이블의 정보를 담고있는 순서는 C < B < A이지만 JOIN순서는 A -> B -> C이다.

때문에 A가 가진 모든 데이터를 조회 -> B가 가진 모든 데이터 조회 -> ... 순으로 반복하게 된다.

이를 개선하여 처음부터 C 테이블을 위주로 조인하는 방법이 있다.

	SELECT
		A.name , B.code, C.text
	FROM LargeTable C
	INNER JOIN
		 SmallTable A ON A.user_code = C.user_code 
         AND C.ROLE = (SELECT ROLE FROM LargeTable
 				WHERE user_code = A.user_code AND STATUS = '1')
        INNER JOIN
		 MiddleTable B ON A.user_code = B.user_code            
	WHERE A.user_code = (값);

 

그러나 위에 언급했듯이 MYSQL 옵티마이저가 JOIN 순서를 바꾸기 때문에 사용자가 의도한대로 쿼리가 진행되지 않을 수도 있다.

이럴 때는 STARIGHT JOIN을 사용할 수 있다.

	SELECT
    	STRAIGHT_JOIN 
		A.name , B.code, C.text
	FROM SmallTable A
	INNER JOIN
		 MiddleTable B ON A.user_code = B.user_code
	INNER JOIN
		 LargeTable C ON A.user_code = C.user_code 
         AND C.ROLE = (SELECT ROLE FROM LargeTable
 				WHERE user_code = A.user_code AND STATUS = '1')
	WHERE A.user_code = (값);

 

이렇게 사용한다면 사용자가 의도한대로 JOIN순서가 A -> B -> C 로 작동하게 된다.

index를 사용하고 쿼리를 개선한 후에도 개선할 점이 있다면 MYSQL 자체의 설정을 변경해보는 방법이다.

 

5. MYSQL 격리 수준(isolation level) 변경

이는 획기적인 속도의 개선을 가져오진 않지만 고려해 볼 만 방법이다.

격리수준(isolation level)이란 트랜잭션끼리 얼마나 서로 고립되어 있는지를 나타내는 수준

즉, 한 트랜잭션이 다른 트랜잭션이 변경한 데이터에 대한 접근 강도를 의미한다.

레벨이 높아질수록 트랜잭션간 고립정도가 높아지며, 성능저하도 야기된다.

오라클 등 많은 곳에서는 격리 수준이 READ COMMITTED이지만 MYSQL에는 REPEATABLE READ가 기본 수준이다.

MYSQL의 격리수준이 더 높기 때문에 이를 READ COMMITTED으로 수정해 볼 수 있다.

DB isolation 단계

 

 

https://marobiana.tistory.com/35

MySql] JOIN 속도를 빠르게! EXPLAIN, STRAIGHT_JOIN

https://rachel0115.tistory.com/entry/MySQL-%EC%9D%B8%EB%8D%B1%EC%8A%A4-INDEX-%EC%A0%95%EB%A6%AC-%EB%8F%99%EC%9E%91-%EB%B0%A9%EC%8B%9D-%EC%83%9D%EC%84%B1-%EC%82%AD%EC%A0%9C-%EC%84%A4%EA%B3%84

[MySQL] - 인덱스 (INDEX) 정리 (동작 방식, 생성, 삭제, 설계)

https://velog.io/@bonjugi/MySQL-%EA%B2%A9%EB%A6%AC-%EB%A0%88%EB%B2%A8%EC%97%90-%EB%8C%80%ED%95%B4%EC%84%9C

MySQL 및 Aurora MySQL 기본 격리 레벨

잘못된 내용이 있다면 지적부탁드립니다. 방문해주셔서 감사합니다.