Блог

У меня есть запрос, содержащий подзапрос, который всегда вызывает СКАНИРОВАНИЕ очень большой таблицы, что приводит к сокращению времени запроса.

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

 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
 

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

Вероятно, несоответствующие типы данных. Делает человек.PersonID и relationship.PersonID имеют одинаковое определение данных.

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

Я не могу воспроизвести. Я выполнил следующие инструкции при новой установке 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
• у вас есть другие ограничения, например, уникальные индексы.

Можете ли вы запустить приведенный выше сценарий и проверить, получаете ли вы те же результаты, что и я?

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

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

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

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

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

Это:

 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
       );
 

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

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