[SQL] 인덱스 / 기본 키 순서에 대한 SQL 쿼리

인덱스 / 기본 키 순서에 대한 SQL 쿼리

우리의 온라인 콘테스트 시스템에서, 정수 열 (USER_ID, 점수)와 자주 변경되는 테이블 순위가있다. 모두 고유 제한 조건으로 인덱싱됩니다. 쿼리의 두 가지가 필요합니다 :

두 경우 모두, 위치는 점수 상승에 대한 것입니다 : 테이블의 모든 현재보다 작은 새로운 점수가 위치 1이있을 것이다.

여기에 어려운 부분 : 우리가 아마 테이블 스캔을 감당할 수 없습니다. 표는 1000 만 개 기록까지 할 수 있습니다, 우리는 초당 최소 40 개 쿼리를 처리해야합니다.

어떻게 PostgreSQL의에서이 작업을 수행하려면?

나는 그것의 논리적 레코드 번호 사용 B-나무를 사용 버클리 DB의 비 SQL 솔루션을 가지고있다. 그것은 쉽게 충분히 좋은 성능을 가지고있다. 그러나 우리는 다시 구현하는 PostgreSQL의 쿼리에 의해 BDB 제거하고 싶습니다. 나는 명백한 시도

select 1+count(*) from standings where score < ? limit 1;

이 테이블 스캔이 발생합니다.

나는 BDB의 논리 레코드 번호 설비가 각 편집에 대한 전체 B-트리 잠금이 필요하기 때문에 대답은 "방법"있을 것으로 기대합니다. O를 얻으려면 성능 (N 로그), 각 노드에서 리프 계산에 의존한다. 모든 편집으로 변경해야 루트 경로에서 이러한 모든 카운트; 따라서, 로킹. 이러한 잠금은 PostgreSQL을의 디자인 원칙과 아마 모든 다중 사용자 데이터베이스에 대해입니다.

문제가 PostgreSQL을 함께 해결할 수없는 그래서 경우이 확인이 질문의 다음으로 좋은 결과입니다.

해결법

1. ==============================

   1.일반 테이블로, 많은 당신은 PostgreSQL을 9.1에서 할 수없는 존재한다. 인덱스 가시성 정보를 가지고 있지 않기 때문에, 테이블 스캔 () 결과를 계산합니다. 행이 그 동안 삭제되지 않습니다 확인하려면, PostgreSQL는 테이블을 방문한다.

   일반 테이블로, 많은 당신은 PostgreSQL을 9.1에서 할 수없는 존재한다. 인덱스 가시성 정보를 가지고 있지 않기 때문에, 테이블 스캔 () 결과를 계산합니다. 행이 그 동안 삭제되지 않습니다 확인하려면, PostgreSQL는 테이블을 방문한다.

   테이블이 읽기 전용 (또는 거의 업데이트되지) 않은 경우, 당신은 테이블에 행 번호를 추가 할 수 있습니다. 그런 다음 쿼리와 같은 :

   SELECT rownumber+1
   FROM   standings
   WHERE  score < ?
   ORDER  BY score DESC
   LIMIT  1;
   

   인덱스로 :

   CREATE INDEX standings_score_idx ON standings (score DESC);
   

   거의 즉시 결과를 얻을 것입니다. 그러나 그것은 분명한 이유 쓰기로드가있는 테이블에 대한 옵션이 아니다. 그래서하지 당신을 위해.

   좋은 소식 : 곧 PostgreSQL의 9.2의 새로운 주요 기능 중 하나는 바로 당신입니다 "커버 인덱스"또는 "인덱스 만 스캔". 나는 여기에 9.2 릴리스 노트를 인용 :

   로버트 하스의이 블로그 게시물이 카운트 성능에 미치는 영향 자세한 내용이 있습니다. 그것은 귀하의 경우처럼, 심지어 WHERE 절을 사용하여 성능을하는 데 도움이됩니다.

from https://stackoverflow.com/questions/11623928/sql-query-for-index-primary-key-ordinal by cc-by-sa and MIT license