MySQL Order By, Select For Update 최적화 하기

서론

---

인덱스가 쿼리 성능에 굉장히 많은 영향을 끼치는 것도 사실이지만 그 외에도 고려해야할 다른 요소들도 많다.

 

해당 글에서는 이전 글에서 다루지 않았던 인덱스 외 튜닝 포인트들을 (특히 실무를 진행하면서 만날법 한) 정리할 예정이다.

 

 

 

본론

---

Order By 관련 최적화

Order By 절에 명시된 칼럼에 인덱스가 있다면 이미 정렬되어 있는 상태이기 때문에 추가적인 정렬 작업이 필요 없어진다.

인덱스가 없다면 정렬 작업을 따로 수행해야 하는데

• 데이터 양이 Buffer 보다 적다 -> 메모리에서 정렬
• 데이터 양이 Buffer 보다 크다 -> 데이터들을 쪼개서 디스크에 넣고 정렬하고 합쳐서 정렬

 

실행계획을 확인했을 file sort가 있다면 정렬이 일어난거고 index가 있다면 인덱스를 통해 추가적인 정렬이 일어나지 않았음을 의미한다.

 

인덱스와 같이 사용하기

특히 limit과 사용할 때 인덱스가 있으면 필요한 데이터만 읽는 방면 없으면 전체 데이터를 읽고 정렬한 후 limit만큼 추출하기 때문에 차이가 많이난다.

 

인덱스 없는 Order By

 

인덱스 있는 Order By

 

type에서 index 사용 여부를 확인할 수 있고 extra에서도 index가 없을 때 file sort가 일어났음을 확인할 수 있다.

 

 

 

sort_buffer_size 튜닝

• 정렬해야하는 데이터가 많아 디스크에서 정렬을 해야할 경우 sort_buffer_size를 증가시켜서 디스크 정렬을 최소화 한다.
• sort_merge_passes 변수를 통해 디스크 정렬 수행 여부를 확인할 수 있다.

# 쿼리 실행
select * 
from product
order by name limit 1000;


# 디스크 정렬여부 다시 확인
show status like 'sort_merge_passes';

# sort_buffer_size 올리기
set session sort_buffer_size = 2* 262144;

//다시 쿼리 실행하고 디스크 정렬여부 확인

 

sort buffer size 올리기 전

 

 

sort buffer size 올리고 나서

 

Single-Pass 에서 Two-Pass 로 튜닝

• Single Pass : Sort Buffer에 데이터를 모두 넣고 정렬
• Two Pass : 정렬하는 컬럼, PK 만 넣고 정렬

Two Pass는 정렬을 먼저하고 나머지 데이터와 합치는 식으로 작동한다. 그래서 일반적으로는 single pass가 더 빠르나 데이터가 매우 커서 디스크에서 정렬이 일어나게 된다면 Two Pass가 성능이 더 좋을 수 있다.

 

max_length_for_sort_data 보다 크면 Two Pass, 작으면 Single Pass 가 일어나기 때문에 해당 값을 잘 조절해야 한다. 8.0.20 이후로 deprecated 되었다. (optimizer가 알아서 해준다 함)

 

 

Order By 순서 고정

 

Order By를 사용할 때 같은 값을 가진 데이터끼리의 순서를 고정시키고 싶으면 고유한 값을 가진 칼럼을 포함시키면 된다.(PK)

 

Order By가 인덱스를 사용하고 있다면 기본적으로 인덱스들은 PK를 내부적으로 포함하고 있기 때문에 성능에 영향이 없다.

Indexes other than the clustered index are known as secondary indexes. In InnoDB,
each record in a secondary index contains the primary key columns for the row,
as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.
MySQL Docs

 

SELECT FOR UPDATE 최적화

 

select for update란 조회하는 레코드에 잠금을 거는 기능이다. 다른 트랜잭션에서 해당 레코드에 쓰거나 변경할 수 없게 한다.

For index records the search encounters, locks the rows and any associated index entries,
the same as if you issued an UPDATE statement for those rows

 

이때 한 쓰레드가 잠금을 걸고 조회를 하고 있다면 다른 쓰레드의 경우 잠금이 풀릴 때까지 기다리게 된다. (row와 관련된 index를 잠근다.)

 

If a row is locked by a transaction, a SELECT ... FOR UPDATE or SELECT ... FOR SHARE transaction
that requests the same locked row must wait until the blocking transaction releases the row lock.

 

그리고 락이 걸린 컬럼을 조회하려는 트랜잭션은 해당 락이 풀릴 때까지 기다려야 한다.

 

이는 경우에 따라 다르겠지만 비효율적이고 데드락을 일으킬 수 있는 위험한 요소이다. time out과 같은 설정 값으로 해결할 수도 있지만 MySQL 8.0부터 지원해주는 2가지 기능을 활용할 수 있다.

- skip locked : 잠금이 걸린 레코드는 건너뛰고, 잠금이 없는 레코드만 조회

 

- nowait : 마찬가지로 잠금이 걸린 레코드는 건너뛰고, 만약 어떤 row도 조회하지 못하면 에러를 던진다. 

 

select for update 만 사용했을 때

selecrt for update skip locked 사용

결론

---

해당 내용을 공부하고 직접 사용해보면서 MySQL의 버전에 따라 다르지만 다양한 기능들을 알게되었다.

 

기본적으로는 query 공부를 주로 하여 query에 대한 기초를 잘 쌓고 이러한 지식들도 간간히 복습하면서 인덱스처럼 갖고 있다가 필요할 때 꺼내쓸 수 있게 할 예정이다!