멀티 인덱스 최적화
게시판 주제로 프로젝트를 짤때, 주제별로(ex: 자유게시판, 질문게시판등으로 나눌때, 혹은 주제별로 카테고리를 나눌때) 게시판을 분류한다고 가정한다.
이때, 게시판을 페이지를 기준으로 리스트를 뽑아온다면, subject(주제)와 그에 해당하는 게시판 번호(s_bno)를 인덱스로 설정하면 좋을 것이다.
이 두 컬럼은 같이 쓰기 좋기에 다중 컬럼 인덱스로 설정할텐데 여기서 궁금점이 생겼다. 누군가는 다중 컬럼 인덱스는 카디널리티가 높은 순으로 설정하는 것이 빠르다고 말하고 있지만 내가 아는 mysql의 b tree 형식을 생각해보면 앞과 같이 페이지로 게시판 리스트를 뽑아온다면 달라질 것 같았기 때문이다.
실제로 일반적으로 알려진대로라면 s_bno를 앞에 둬야 더 검색이 빠를 것 같지만, 결과는 그 반대였다.
이는 아마 b tree 형식은 미리 정렬된 인덱스를 값을 비교해 leaf까지 도달해서 값이 더 크면 오른쪽으로 점차 탐색하고 아니면 왼쪽으로 탐색하는 구조 때문일 것 같았다.
수직 탐색에서는 카디널리티가 높은 s_bno가 앞선 쪽이 유리하지만, 수평 탐색에서는 카디널리티가 낮은 subject가 앞선 쪽이 유리할 것이기 때문이다.
실제로 subject 100개, s_bno 2000개인 테이블을 인덱스 순서를 달리해 explain 구문을 통해 분석했을 때, filterd 항목, 즉 수직 탐색에서 걸러진 비율은 1: 10으로 10배 정도 차이 났지만, 탐색 rows에서 57578 : 301 정도로 수평 탐색에서 꽤 큰 차이를 보인 걸로 예상된다.
일반적으로 flitered * rows의 수치가 낮을 수록 좋으므로, 실제로 거의 10배 이상 성능 차이가 났다. 물론 filtered와 rows가 추정된 계산값이라고는 하지만 이정도 차이와 시간 차이는 나름 유의미해보인다.
그리고, 여기서 추가로 알 수 있었던 점은 수평 탐색시 앞선 컬럼의 값을 기준으로 먼저 탐색하다가 그 값에 다다르면 그때 후자의 값으로 탐색하는 것 같다. explain의 rows 항목을 보면 57508의 값은 2000 * 30과 근사한 값이다.
limit 30으로 30개의 게시판을 뽑아왔을 때의 값이므로, s_bno를 앞서게 했을 때, 수직 탐색에서 바로 s_bno 값을 찾았더라도, 다음 1개를 찾으려면 s_bno 값을 전부 탐색해야 하기 때문이다.
그리고, 조건문이 둘다 등호인 경우는 성능 차이가 없다고 봐도 되었다.
즉, 등호만 사용하는 단일 검색은 순서가 상관없고, 부등호가 사용될 때 부등호를 사용하는 조건의 인덱스를 뒤에 두는 것이 더 빨랐다.
즉, 멀티 컬럼 인덱스의 순서는 카디널리티는 거의 상관없고, 조건문에서 컬럼이 등호에 쓰이는지 부등호에 쓰이는지에 따라 순서를 달리 해야한다는 것을 알게됐다.
생각보다 차이가 유의미했다는 점을 생각하면
만약 페이지를 통해 게시물을 나누고, 페이지 번호를 통해 게시물을 일정 갯수 보여준다면, 기본적으로 select from ? where subject=#{subject} order by bno limit ? ?의 형태거나 select from ? where subject=#{subject} and bno >= ? order by bno limit ?일 것이다.
후자가 더 일반적으로 효율적이다. 그리고, 문제는 bno를 전체 게시판 기준일지 주제별로 따로 나눌지의 문제인데