홈>2020-02-26 12:30

rowID가 인덱스의 두 번째 열인 경우 SQLite가 잘못된 쿼리 계획을 선택합니까?

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

index execution-plan sqlite

답변 # 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 ) 를 만드는 것으로 충분하다

Python2.net

rowID가 인덱스의 두 번째 열인 경우 SQLite가 잘못된 쿼리 계획을 선택합니까?

관련 자료