Почему подзапрос вызывает сканирование, когда нет статического списка?

У меня есть запрос, содержащий подзапрос, который всегда вызывает SCAN очень большой таблицы, что приводит к плохим временам запросов.

Это запрос, который я использую:

SELECT PersonId 
  FROM person 
 WHERE PersonId IN (
                    SELECT PersonId 
                      FROM relationship 
                     WHERE RelatedToPersonId = 12270351721
                   );

План запроса отображается как:

SCAN TABLE person (~100000 rows)
EXECUTE LIST SUBQUERY 1
SEARCH TABLE relationship USING INDEX relationship_RelatedToPersonId_IDX (RelatedToPersonId=?) (~10 rows)

Тот же запрос со статическим списком (эквивалентный результатам подзапроса):

SELECT PersonId 
  FROM person 
 WHERE PersonId IN (12270351727,12270351730,12270367969,12387741400);

И план запроса для этого:

SEARCH TABLE person USING COVERING INDEX sqlite_autoindex_person_1 (PersonId=?) (~5 rows)
EXECUTE LIST SUBQUERY 1

Почему первый запрос запрашивает сканирование, если второй не работает?

Ответы

Ответ 1

Стол должен быть отсканирован, потому что вы включаете другое поле (RelatedToPersonId) в предложение WHERE вашего подзапроса. Список PersonIDs может перейти непосредственно к индексу.

Ответ 2

Вероятно, непревзойденные типы данных. Does person.PersonId и relationship.PersonId имеют одинаковое определение данных.

Ответ 3

Я не могу воспроизвести. Я выполнил следующие инструкции на новой версии SQLite версии 3.7.8:

create table person (id int not null primary key, name varchar(20));
insert into person values (1, 'a');
insert into person select id + 1, name || 'b' from person;
insert into person select id + 2, name || 'c' from person;
insert into person select id + 4, name || 'd' from person;
insert into person select id + 8, name || 'e' from person;
insert into person select id + 16, name || 'f' from person;
insert into person select id + 32, name || 'g' from person;
insert into person select id + 64, name || 'h' from person;
insert into person select id + 128, name || 'i' from person;
insert into person select id + 256, name || 'j' from person;
insert into person select id + 512, name || 'k' from person;
insert into person select id + 512, name || 'l' from person;
insert into person select id + 1024, name || 'l' from person;
insert into person select id + 2048, name || 'm' from person;
insert into person select id + 4096, name || 'n' from person;
insert into person select id + 8192, name || 'o' from person;
insert into person select id + 16384, name || 'p' from person;
insert into person select id + 32768, name || 'q' from person;
insert into person select id + 65536, name || 'r' from person;
select count(*) from person;

create table relation (id int, related int);
insert into relation select id, id + 1 from person;
insert into relation select id, id + 2 from person;
insert into relation select id, id + 3 from person;
insert into relation select id, id + 4 from person;
insert into relation select id, id + 5 from person;
insert into relation select id, id + 6 from person;
insert into relation select id, id + 7 from person;
insert into relation select id, id + 8 from person;
insert into relation select id, id + 9 from person;
insert into relation select id, id + 10 from person;
delete from relation where related not in (select id from person);

create index relatedToPerson on relation(related);
explain query plan select id from person 
    where id in (select id from relation where related = 2345);

Результаты для инструкции плана запроса:

0|0|0|SEARCH TABLE person USING COVERING INDEX sqlite_autoindex_person_1 (id=?)(~25 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SEARCH TABLE relation USING INDEX relatedToPerson (related=?) (~10 rows)

Почему он не работает для вас? Причины, о которых я могу думать:

  • отношение вашего стола не содержит столбец PersonId (пожалуйста проверить)
  • вы используете другую версию SQLite
  • У вас есть другие ограничения, например уникальные индексы.

Вы можете запустить script выше и проверить, получили ли вы те же результаты, что и я?

Ответ 4

Я не уверен, почему есть сканирование таблицы. Но здесь нет необходимости использовать подзаголовок: вы также можете использовать соединение:

SELECT person.PersonId 
  FROM person JOIN relationship ON person.PersonId = relationship.PersonId
 WHERE RelatedToPersonId = 12270351721;

Не уверен, что sqlite поддерживает этот синтаксис JOIN, но это также можно переписать с помощью FROM и WHERE:

SELECT person.PersonId 
  FROM person, relationship 
 WHERE person.PersonId = relationship.PersonId
   AND RelatedToPersonId = 12270351721;

И, возможно, это больше подходит для оптимизатора.

Ответ 5

Это:

SELECT PersonId 
  FROM person 
 WHERE PersonId IN (12270351727,12270351730,12270367969,12387741400);

- это просто синтаксический сахар для этого:

SELECT PersonId 
  FROM person 
 WHERE (
        PersonId = 12270351727
        OR PersonId = 12270351730
        OR PersonId = 12270367969
        OR PersonId = 12387741400
       );

Ответ 6

Это вызывает полное сканирование, потому что подзапрос выполняется для каждой строки в person. Подзапросы не "кэшируют" свой результирующий набор, преобразованный в IN.

Надеюсь, что это поможет.