물리적IO 논리적IO
저번글에서 수직탐색과 수평탐색을 알아보았습니다.
수직탐색과 수평탐색 모두 데이터를 읽는 과정입니다.
데이터는 모두 디스크에 있고, 일부는 메모리에도 있습니다.
한번 읽은 데이터는 메모리에 올라가며, 메모리가 가득차면 우선순위에 따라 다시 메모리에서 제거됩니다.
mysql에서는 버퍼풀(innodb buffer pool) 이라는 할당된 메모리 공간에서 이뤄집니다.
트리의 수평/수직탐색에서 한번 탐색한 노드는 노드 단위로 버퍼풀로 이동합니다.
하나의 노드는 기본 16KB이고 설정에서 변경이 가능합니다(aws aurora는 변경불가능)
3층 구조의 트리에서 수직탐색하여 한 건의 데이터를 읽더라도
루트노드-중간노드-리프노드의 총 3개의 노드를 탐색하여 총 48KB를 읽게 됩니다.
논리적IO는 메모리를 포함해서 읽은 페이지(=노드)의 횟수이고,
물리적IO는 메모리에 없어서 실제로 디스크에서 읽은 횟수입니다.
3개의 노드를 읽어야 하는데 이미 메모리에 2개가 있어서 디스크에서는 1개만 읽었다면
논리적IO는 3이며 물리적IO는 1이 됩니다.
쿼리의 최적화는 기본적으로 논리적 IO를 최소화하는걸 목표로 합니다.
페이지 16KB
16KB는 몇 개 정도의 데이터가 들어갈지 가늠해보겠습니다.
int타입의 컬럼 하나를 갖는 인덱스를 만든 경우입니다.
int타입의 컬럼 사이즈는 4byte를 차지합니다.
인덱스엔 자동으로 pk가 추가된다고 했으니 int타입의 pk라고 가정하면 4byte를 차지합니다.
마지막으로 자식노드의 주소를 저장해야 하므로 약 12byte가 들어갑니다.
= 하나의 행이 약 20byte를 차지합니다.
16kb = 16 * 1024 = 16,384 byte 이므로 하나의 페이지에 약 800개가 들어갈 수 있습니다.
인덱스 컬럼과 pk의 타입을 uuid로 변경해보겠습니다.(binary)
인덱스컬럼 16byte + pk 16byte + 자식노드의 주소 12byte = 44byte
16384 / 44 = 372 개 입니다.
실제로는 모든 페이지를 가득 채우지 않으니 더 적게 들어간다고 볼 수 있습니다.
인덱스가 작을수록 하나의 페이지에 더 많은 개수의 데이터가 들어가고,
원하는 개수를 검색하기 논리적/물리적IO가 더 작아지게 됩니다.
참고
물리적/논리적 IO 횟수
mysql의 status 값을 확인해서 물리적io와 논리적io를 추적할 수 있습니다.
# 논리적 IO
show status like 'Innodb_buffer_pool_reads';
# 물리적 IO
show status like 'Innodb_buffer_pool_read_requests';쿼리 실행 전후의 차이를 계산하면
해당 쿼리에서 논리적io와 물리적io가 몇 회 발생했는지 알 수 있습니다.
Tree의 노드 개수
mysql 쿼리로 특정 테이블/인덱스 트리의 페이지 개수를 알 수 있습니다.
(권한이 없을 수 있습니다.)
SELECT *
FROM mysql.innodb_index_stats s
WHERE table_name = '{tableName}'하나의 테이블/인덱스별로 여러개 나오는데
stat_name=n_leaf_pages 에 해당하는 stat_value가 리프 페이지의 개수,
stat_name=size 에 해당하는 stat_value가 총 페이지의 개수 입니다.
