데이터베이스 질문과 답변
복합인덱스에서 범위 조건은 뒤로 가야 효율이 좋다고 하는데 실제로 테스트를 해보니까 뒤로 가야 모든 컬럼이 인덱스를 타더라구요.
근데 이해가 안갑니다.... 왜 범위조건이 먼저 나오면 안되는지 실제로 범위조건 뒤는 인덱스가 아니라 필터로 처리되던데....
이게 목차로 따지면 복합인덱스는 첫번째 컬럼으로 큰 목차를 만들고 만들어진 큰 목차 하나하나에 작은 목차들이 생성된다고 생각하면 되는건가요?
이렇게 되면 먼저 범위 조건을 하면 그 안에 있는 작은 목차들을 하나하나 찾아야 되서 필터처리가 되는건 이해가 가는데 반대의 경우는 이해가 안가더라구요. 먼저 값에 해당하는 목차들을 선별해서 그 안에서 범위를 비교하는 느낌인건가요?
고객이 100명이 있다고 가정했을 때 37 번 고객의 10월 실적을 검색하면
1. 고객+일자 인덱스의 경우
- 한명의 고객에 대해 10월의 자료가 한군데 몰려있게 됩니다.
2. 일자+고객 인덱스의 경우
- 10월1일 부터 검색을 시작하면 37번 고객을 찾은뒤에 38번~100번 사이 불필요한 고객들을 버리고
- 10월2일 로 넘어와서 1번~36번 고객 버리고 37번 선택 뒤에 38번~100번 고객 버리고
- 10월3일 ...
- ...
- 10월31일 ...
감사합니다! 이해가 됬어요.
그리고 추가적으로 궁금한게 이게 데이터를 액세스 하는 양이 달라서 이러하다 라는 식으로 표현하더라구요. 설명해주신대로 데이터를 비교하는거 자체가 1번 조건일때가 훨씬 유리한데 범위조건으로 먼저 처리하면 그 뒤에 필터는 인덱스를 활용한 처리가 아니라 인덱스를 빠져나와 그 값을 하나하나 체크하는 것이라고 이해해도되는걸까요?
10월 1일 37번부터 10월31일까지 쭈욱 인덱스 안에서 찾은 후에 버리는 과정이 있습니다.
넓은 범위를 인덱스 스캔하면서 대부분이 버려지는 것이죠.
감사합니다!
혹시 하나만 더 여쭤봐도 될까요...
1. 선분 + 점 인덱스
2. 점 + 선분 인덱스
1번 인덱스는 블록을 읽어들인 개수가 27,000,000개(9초 걸립니다) 정도이고 2번 인덱스는 4,000,000개(3초 걸립니다)를 읽습니다.
근데 인덱스를 아예 만들지 않고 그냥 쿼리를 실행하면 1,000,000개(9초에서 10초 걸립니다) 읽더라구요. 속도는 2번 인덱스를 사용한게 3배 이상빠른데 읽어들인 블록의 수가 더 적을 수가 있나요?
이게 인덱스를 사용하면 single block i/o고 사용하지 않으면 multi block i/o라고 하던데 그러면 1,000,000개가 single block이 아니라 multiblock이라는 뜻인가요? 그래서 개수가 더 적은거일까요?
위에서 이미 설명 드렸듯이...
쓸데없이 많은 범위를 읽고 난 뒤에 필요 없는 부분이 버려집니다.
그리고 설명드린 부분은 인덱스 스캔 부분만 설명 드린 것이고
인덱스 스캔 후에 테이블 렌덤엑세스하는 부분은 설명 생략했습니다.
언급하신 수치는 테이블 접근까지 고려하셔야 합니다.
그리고 언급하신 수치가 레코드수인지? 블럭수인지? 읽기카운트인지? 모호하네요?
풀스캔이 오히려 적은 블럭을 읽는다???
쿼리 실행계획에 Buffers: shared hit=354 read=943706 라고 나오고
shared hit이 버퍼 캐시에서 i/o 블록수라고 하고 read는 디스크 i/o를 통한 블록수라고 합니다.
그래서 어떻게 full table scan이 블록을 덜 읽을수가 있나 싶었습니다.
테이블에 데이터가 4000만개정도가 있고 하나의 월에 3000만개가 몰려있거든요. 이 부분을 생각하면 가능한건가 싶기도 하고 말이 안되는거 같기도 하고 합니다....
354 와 943706 은 적은 수치인데?
2천7백만, 4백만, 백만이라는 큰 수치는 어디서 나온거죠?
읽어들인 블록수는 버퍼캐시에서 읽은거랑 디스크에서 읽은거랑 합치면 될거라고 생각해서 합쳐서 말씀드렸습니다.
2천 7백만은 1. 선분+점 인덱스를 쓰면 총 읽은 블록수가 그렇더라구요.
Buffers: shared hit=26783124 read=115327, temp read=41390 written=41515
4백만은 2. 점+선분 인덱스를 사용했더니 읽은 블록수가 그렇습니다.
Buffers: shared hit=4003518 read=2452, temp read=40641 written=40766
백만은 인덱스없이 돌리니까 Buffers: shared hit=354 read=943706
이렇게 나온겁니다.
postgreSQL 인가요?
일단 이론적인 부분으로만 답변 드렸습니다.
실행게획 읽는 법은 저도 좀 어색한 편이라, 포스그레는 특히나..
1번보다는 2번이 빠른 것은 이해가 가는데.
주어진 상황이라면 차라리 풀스캔이 2번보다도 더 빠를 것 같은데...
느린 모양이네요? 왜인지는 저도 잘 모르겠네요.
네 postgreSQL입니다.
1,2인덱스를 사용한건 대부분을 버퍼 캐시에서 읽어들이긴 하니까 그럼 이게 디스크i/o가 훨씬 많아서 이런 결과가 나온다고 하면은 블록 i/o가 더 적을수있다는게 가능한건가요?
풀스캔보다 인덱스 스캔이 블럭을 더 많이 읽는다는 것은
인덱스 순서와 테이블 순서가 많이 달라서
인덱스 스캔 후 테이블 렌덤엑세스 할 때 왔다 갔다 하면서 읽어서
같은 블럭을 여러번 중복으로 읽어서 그런 듯 합니다.
감사합니다. 덕분에 많은 도움이 됬습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.