>

DB의 성능을 테스트하고 있는데 SQLite가 내 쿼리에 적합한 쿼리 계획을 선택하지 않았 음을 발견했습니다.

내 스키마 :

CREATE TABLE event (
    ID       INTEGER PRIMARY KEY NOT NULL,
    netAddr  INTEGER             NOT NULL,
    date     INTEGER             NOT NULL,
    value    INTEGER             NOT NULL);
INSERT INTO event ( netAddr, date, value )
    WITH RECURSIVE cnt(x) as (
        VALUES(1)
        UNION ALL
        SELECT x+1 FROM cnt LIMIT 1000000
    ) SELECT 250, x, x+1000 FROM cnt;
CREATE INDEX myIdx ON event ( netAddr, ID );

질문 :

SELECT value FROM event
WHERE netAddr = 250 AND ID > 100 AND ID < 110
ORDER BY ID DESC LIMIT 1;

너무 느리게 실행되었고 쿼리 계획을 검사했습니다 :

EXPLAIN QUERY PLAN
SELECT value FROM event
WHERE netAddr = 250 AND ID > 100 AND ID < 110
ORDER BY ID DESC LIMIT 1;

나에게 줬다

0|0|0|SEARCH TABLE event USING INDEX myIdx (netAddr=?)

SQLite는 쿼리가 원하는 방식으로 정렬되어 있어도 인덱스의 두 번째 열 사용을 거부합니다.

그런 다음 다른 색인으로 테스트했습니다 :

CREATE INDEX dateIdx ON event ( netAddr, date );
EXPLAIN QUERY PLAN
SELECT value FROM event
WHERE netAddr = 250 AND date > 100 AND date < 110
ORDER BY date DESC LIMIT 1;

나에게 줬다

0|0|0|SEARCH TABLE event USING INDEX dateIdx (netAddr=? AND date>? AND date<?)

쿼리 계획은 이제 예상대로입니다.

<올>
  • SQLite가 전체 인덱스를 사용하지 않는 이유 (예 : SEARCH TABLE event USING INDEX myIdx (netAddr=? AND ID>? AND ID<?) ) ?
  • 인덱스에서 rowID를 두 번째 열로 사용하면 어떤 문제가 있습니까?
  • 테이블이나 인덱스에 다른 열을 추가하지 않고 작동시키는 방법이 있습니까? (DB를위한 스토리지가 거의 없습니다.)
  • 수정 :

    SELECT value FROM event WHERE id = (SELECT id FROM event WHERE netAddr = 250 AND id > 100 AND id < 110 ORDER BY id DESC LIMIT 1) LIMIT 1; 와 함께 시도  그리고 SELECT value FROM event WHERE netAddr = 250 AND ID > 100 AND ID < 110 ORDER BY netAddr DESC, ID DESC LIMIT 1;  ypercubeᵀᴹ이 제안한 것처럼-결과는 "느린"쿼리와 동일합니다.

    ID 열의 이름을 다른 것으로 변경하려고했습니다. 결과는 동일합니다.

    편집 2 :

    SQLite 버전 3.8.2 및 3.8.8.1

    • 답변 # 1

      이론적으로 인덱스에서 rowid를 두 번째 열로 사용하는 것은 아무 문제가 없습니다.
      실제로 현재 SQLite 버전에서는 예상대로 작동합니다.

      sqlite>.eqp 켜기
      sqlite>SELECT 값 FROM 이벤트
         ...>netAddr = 250 AND ID>100 AND ID<110 인 경우
         ...>ID DESC LIMIT 1에 의한 주문;
      --EQP-- 0,0,0, INDEX myIdx를 사용하는 검색 테이블 이벤트 (netAddr =? AND ID>? AND ID

    • 답변 # 2

      실제로 CREATE INDEX myIdx ON event ( netAddr ); 를 사용하는 것으로 나타났습니다  작동합니다.

      EXPLAIN QUERY PLAN
      SELECT value FROM event
      WHERE netAddr = 250 AND ID > 100 AND ID < 110
      ORDER BY ID DESC LIMIT 1;
      
      

      주십시오

      0|0|0|SEARCH TABLE event USING INDEX myIdx (netAddr=? AND rowid>? AND rowid<?)
      
      

      SQLite 문서의 어느 곳에도 쓰지 않아도 인덱스의 rowid 열은 정렬 된 상태로 유지됩니다. 따라서 인덱스 ( columnXXX, rowid ) 를 명시 적으로 만들 필요는 없습니다.  그것을 ( columnXXX ) 를 만드는 것으로 충분하다

    관련 자료

  • 이전 sql server - 데이터 계층 응용 프로그램에서 자동 버전 증분
  • 다음 복제에서 표준 MySQL 데이터베이스 제외