1. Введение

Задача "планировщика запроса" состоит в том, чтобы выяснить лучший алгоритм или "план запросов", чтобы достигнуть SQL-оператора. Начиная с SQLite version 3.8.0 (2013-08-26), компонент планировщика запроса был переписан так, чтобы это работало быстрее и произвело лучшие планы. Переписывание называют "планировщиком запроса следующего поколения" или "NGQP".

Эта статья рассматривает важность планирования запроса, описывает некоторые проблемы, врожденные, чтобы запросить планирование и схемы, как NGQP решает те проблемы.

NGQP почти всегда лучше, чем старый планировщик. Однако, там могут существовать унаследованные приложения, которые бессознательно зависят от неопределенного и/или подоптимального поведения в устаревшем планировщике запроса, и модернизация до NGQP тех унаследованных приложений может вызвать исполнительные регрессы. Этот риск рассматривают, и контрольный список предоставлен для того, чтобы снизить риск и для того, чтобы устранить любые проблемы, которые действительно возникают.

Этот документ сосредотачивается на NGQP. Если нужен более общий обзор SQLite-планировщика, который охватывает всю историю SQLite, см. здесь и здесь.

2. Обзор

Для простых запросов против единственной таблицы с немногими индексами обычно есть очевидный выбор для лучшего алгоритма. Но для больших и более сложных запросов, таких как многоканальные соединения со многими индексами и подзапросами, могут быть сотни, тысячи или миллионы разумных алгоритмов для вычисления результата. Работа планировщика запроса состоит в том, чтобы выбрать единственный "лучший" план запросов из этого множества возможностей.

Планировщики запроса это то, что делает движки базы данных SQL так удивительно полезными и сильными. Это верно для всех баз данных SQL, не только SQLite. Планировщик запроса освобождает программиста от тяжелой работы отбора конкретного плана запросов, и таким образом позволяет программисту сосредотачивать больше умственной энергии на высокоуровневых проблемах приложения и на обеспечении большего количества пользы конечному пользователю. Для простых запросов, где выбор плана запросов очевиден, это удобно, но не чрезвычайно важно. Но поскольку запросы и схемы и запросы становятся более сложными, умный планировщик запроса может значительно ускорить и упростить разработку приложений.

Написание хорошего планировщика запроса является большим искусством, чем наука. Планировщик запроса должен работать с неполной информацией. Это не может определить, сколько времени какой-то конкретный план возьмет, на самом деле не управляя тем планом. Таким образом, сравнивая два или больше плана, чтобы выяснить, который является "лучшим", планировщик запроса должен высказать некоторые предположения, а те предположения иногда будут неправильными. Хороший планировщик запроса это тот, который будет находить правильное решение достаточно часто, так что прикладные программисты редко должны принимать участие.

2.1. Планирование запросов в SQLite

SQLite вычисляет соединения, используя вложенные циклы, одну цикл для каждой таблицы в соединении. Дополнительные циклы могли бы быть вставлены для операций IN и OR в WHERE. SQLite рассматривает и их, но для простоты мы проигнорируем их здесь. Один или более индексов могли бы использоваться на каждом цикле, чтобы ускорить поиск, или цикл мог бы быть "полным сканированием таблицы", которое читает каждую строку. Таким образом планирование запроса разлагается на две подзадачи:

1. Выбор вложенного порядка различных циклов
2. Выбор хороших индексов для каждого цикла

Выбор порядка обычно является более сложной проблемой. Как только порядок соединения устанавливается, выбор индексов для каждого цикла обычно очевиден.

2.2. Гарантия стабильности планировщика запроса SQLite

Когда Query Planner Stability Guarantee (QPSG) позволят, SQLite будет всегда выбирать тот же самый план запросов для любого данного SQL-оператора пока:

1. схема базы данных не изменяется значительно
2. команда ANALYZE не запущена повторно
3. та же самая версия SQLite используется

По умолчанию QPSG выключен. Это может быть позволено во время компиляции, используя выбор времени компиляции SQLITE_ENABLE_QPSG или во время выполнения sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_QPSG,1,0).

QPSGозначает, что, если все ваши запросы, которыми управляют эффективно во время тестирования, и если ваш запрос не изменяет схему, то SQLite внезапно не решит начать использовать другой план запросов, возможно вызывая исполнительную проблему после того, как ваш запрос задействован. Если ваш запрос будет работать в лаборатории, он продолжит работать так же после развертывания.

Движки базы данных SQL клиент-сервер промышленного класса обычно не дают эту гарантию. В них сервер отслеживает статистику по размерам таблиц и качеству индексов, а планировщик запроса использует те статистические данные, чтобы помочь выбрать лучшие планы. Поскольку содержание добавлено, удалено или изменено в базе данных, статистика разовьется и может заставить планировщика запроса начинать использовать иной план запросов для некоторого конкретного запроса. Обычно новый план будет лучше для развивающейся структуры данных. Но иногда новый план запросов будет вызывать исполнительное сокращение. С ядром базы данных клиент-сервер как правило, есть Database Administrator (DBA), чтобы иметь дело с этими редкими проблемами, поскольку они подходят. Но DBA недоступны, чтобы решить проблемы во встроенной базе данных, как SQLite, и следовательно SQLite старается гарантировать, чтобы планы неожиданно не изменялись после развертывания.

Важно отметить, что изменение версий SQLite могло бы вызвать изменения в планах запросов. Та же самая версия SQLite будет всегда выбирать тот же самый план запросов, но если вы повторно связываете свое приложение, чтобы использовать различную версию SQLite, тогда планы запросов могли бы измениться. В редких случаях изменение версии SQLite могло бы привести к исполнительному регрессу. Это одна из причин, по которой необходимо рассмотреть статическое соединение запросов с SQLite, а не пользоваться разделенной библиотекой SQLite в масштабе всей системы, которая могла бы измениться без контроля или ведома.

3. Трудный случай

"TPC-H Q8" это испытательный запрос из Transaction Processing Performance Council. Планировщики запроса в версиях SQLite 3.7.17 и ранее не выбирают хорошие планы относительно TPC-H Q8. И было определено, что никакой объем тюнинга устаревшего планировщика запроса не исправит это. Чтобы найти хорошее решение запроса TPC-H Q8 и продолжать улучшать качество планировщика запроса SQLITE, стало необходимо перепроектировать планировщик запросов. Эта секция пытается объяснить, почему эта модернизация была необходима, как NGQP отличается и обращается к проблеме TPC-H Q8.

3.1. Сведения о запросах

TPC-H Q8 это соединение с восемью путями. Как наблюдается выше, главная задача планировщика запроса состоит в том, чтобы выяснить лучший порядок восьми циклов, чтобы минимизировать работу, должен был закончить соединение. Упрощенную модель этой проблемы для случая TPC-H Q8 показывает следующая диаграмма:

В диаграмме каждая из этих 8 таблиц в пункте FROM запроса определяется большим кругом с этикеткой условия FROM: N2, S, L, P, O, C, N1 и R. Дуги в графе представляют предполагаемую стоимость вычисления каждого термина, предполагая, что происхождение дуги находится во внешнем цикле. Например, стоимость управления циклом S как внутренним циклом к L 2.30, тогда как стоимость управления S как внешним циклом к L 9.17.

"Стоимость" здесь логарифмическая. С вложенными циклами работа умножается, не добавляется. Но это обычно, чтобы думать о графах с совокупными весами и таким образом, граф показывает логарифм различных затрат. Граф показывает преимущество стоимости S, являющегося в L приблизительно 6.87, но это переводит на запрос, работающий приблизительно в 963 раза быстрее, когда S в L вместо того, чтобы быть за пределами него.

Стрелки из маленьких кругов, маркированных "*", указывают на стоимость управления каждым циклом без зависимостей. Наиболее удаленный цикл должен использовать это *-cost. У внутренних циклов есть выбор использования *-cost или стоимость, предполагающая, что одно из других условий находится во внешнем цикле, какой бы ни дает лучший результат. Можно думать *-costs как примечание стенографии, указывающее на многократные дуги, одну от каждого из других узлов в графе. Граф поэтому "полон", означая, что есть дуги (некоторые явные и некоторые подразумеваемые) в обоих направлениях между каждой парой узлов в графе.

Проблема нахождения лучшего плана запросов эквивалентна нахождению стоящего минимум пути через граф, который посещает каждый узел точно однажды.

Примечание: сметы в графе TPC-H Q8 выше были вычислены планировщиком запроса в SQLite 3.7.16 и преобразованы для использования натурального логарифма.

3.2. Осложнения

Представление проблемы планировщика запроса выше это упрощение. Затраты это оценки. Мы не можем знать то, что это истинная стоимость управления циклом, пока мы на самом деле им не управляем. SQLite высказывает предположения для стоимости управления на основе наличия индексов и ограничений, найденных в операторе Where. Эти предположения обычно довольно хороши, но они могут иногда быть выключены. Использование команды ANALYZE, чтобы собрать дополнительную статистическую информацию о базе данных может иногда позволять SQLite высказать лучшие предположения о стоимости.

Затраты состоят из многократных чисел, нн одного числа, как показано в графе. SQLite вычисляет несколько различных предполагаемых затрат для каждого цикла, которые применяются в разное время. Например, есть стоимость "установки", которая понесена только однажды, когда запрос начинается. Стоимость установки это стоимость вычисления автоматического индекса для таблицы, у которой еще нет индекса. Тогда есть стоимость управления каждым шагом цикла. Наконец, есть оценка числа строк, произведенных циклом, которая является информацией, необходимой в оценке затрат на внутренние циклы. Сортировка затрат может играть роль, если у запроса есть пункт ORDER BY.

В общем запросе зависимости не должны быть в единственном цикле, следовательно матрица зависимостей не могла бы быть графом. Например, одно из ограничений оператора Where могло бы быть S.a=L.b+P.c, подразумевая, что S должен быть внутренним циклом сразу в L и P. Такие зависимости не могут быть оттянуты как граф, так как нет никакого пути к дуге, чтобы произойти в двух или больше узлах сразу.

Если запрос содержит пункт ORDER BY, GROUP BY или если запрос использует ключевое слово DISTINCT, выгодно выбрать путь через граф, который заставляет строки естественно появляться в сортированном порядке, чтобы никакой отдельный шаг сортировки не требовался. Автоматическое устранение пунктов ORDER BY может сделать большое различие в производительности, таким образом, это другой фактор, который нужно рассмотреть в полноценном внедрении.

В запросе TPC-H Q8 затраты на установку все незначительны, все зависимости между отдельными узлами, и нет никакого ORDER BY, GROUP BY или пункта DISTINCT. Таким образом для TPC-H Q8, граф выше это разумное представление затрат. Общий случай включает большое дополнительное осложнение, которым для ясности пренебрегают в остатке от этой статьи.

3.3. Нахождение лучшего плана запросов

До version 3.8.0 (2013-08-26) SQLite всегда использовал "Nearest Neighbor" или "NN", ища лучший план запросов. Эвристическое NN делает единственное пересечение графа, всегда выбирая самую дешевую дугу в качестве следующего шага. Эвристические работы NN удивительно хороши в большинстве случаев. И NN быстр, так, чтобы SQLite был в состоянии быстро найти хорошие планы относительно даже больших соединений с 64 путями. Напротив, другие движки базы данных SQL, которые делают более обширный поиск, имеют тенденцию срываться, когда количество таблиц в соединении выходит за предел 10 или 15.

К сожалению, план запросов, вычисленный NN для TPC-H Q8, не оптимален. План вычислил использование NN: R-N1-N2-S-C-O-L-P со стоимостью 36.92. Нотация в предыдущем предложении означает, что таблица R во внешнем цикле, N1 находится в следующем внутреннем цикле, N2 находится в третьем и т.д. вниз к P, который находится в самом внутреннем. Кратчайший путь через граф (как найдено через исчерпывающий поиск) является P-L-O-C-N1-R-S-N2 со стоимостью 27.38. Различие не могло быть большим, но надо помнить, что затраты логарифмические, таким образом, кратчайший путь почти в 750 раз быстрее, чем путь, найденный, используя эвристическое NN.

Одно решение этой проблемы состоит в том, чтобы изменить SQLite, чтобы сделать исчерпывающий поиск лучшего пути. Но исчерпывающий поиск требует времени, пропорционального K! (где K это количество таблиц в соединении), и поэтому когда вы получаете соединения с 10 путями, время, чтобы управлять sqlite3_prepare() становится очень большим.

3.4. Самые близкие N соседей или эвристический "N3"

NGQP использует новое эвристическое действие для поиска лучшего пути через граф: "N самых близких соседи" (далее "N3"). С N3 вместо того, чтобы выбрать всего одного самого близкого соседа для каждого шага, алгоритм отслеживает пути лучших N в каждом шаге для некоторого маленького целого числа N.

Допустим, N=4. Тогда для графа TPC-H Q8 первый шаг находит, что эти четыре кратчайших пути посещают любой единственный узел в графе:

R (cost: 3.56)
N1 (cost: 5.52)
N2 (cost: 5.52)
P (cost: 7.71)

Второй шаг находит, что эти четыре кратчайших пути посещают два узла, начинающиеся с одного из этих четырех путей от предыдущего шага. В случае, где два или больше пути эквивалентны (у них есть тот же самый набор посещаемых узлов, хотя возможно в различном порядке), сохраняется только путь первой и самой низкой цены. Мы имеем:

R-N1 (cost: 7.03)
R-N2 (cost: 9.08)
N2-N1 (cost: 11.04)
R-P {cost: 11.27}

Третий шаг начинается с четырех самых коротких путей с двумя узлами и находит четыре самых коротких пути с тремя узлами:

R-N1-N2 (cost: 12.55)
R-N1-C (cost: 13.43)
R-N1-P (cost: 14.74)
R-N2-S (cost: 15.08)

В запросе TPC-H Q8 есть 8 узлов, таким образом, этот процесс повторяется в общей сложности 8 раз. В общем случае K-путевое соединение требует хранения O(N) и время вычисления O (K*N), что значительно быстрее, чем точное решение O^(2K).

Но какое значение выбрать для N? Можно было бы попробовать N=K. Это делает алгоритм O ^(K2), который на самом деле все еще довольно эффективен, так как максимальное значение K равняется 64, а K редко превышает 10. Но это недостаточно для проблемы TPC-H Q8. С N=8 на TPC-H Q8 алгоритм N3 находит решение R-N1-C-O-L-S-N2-P со стоимостью 29.78. Это большое улучшение по сравнению с NN, но это все еще не оптимально. N3 находит оптимальное решение для TPC-H Q8, когда N равняется 10 или больше.

Начальное внедрение NGQP выбирает N=1 для простых запросов, N=5 для двухсторонних соединений и N=10 для всех соединений с тремя или больше таблицами. Эта формула для отбора N могла бы измениться в последующих версиях.

4. Опасности модернизации до NGQP

Эта секция была важна, когда NGQP был новым. Но пять лет прошли, NGQP был развернут успешно на миллиардах устройств, и все прошло нормально. Опасность модернизации исчезла. Эта секция сохраняется для исторической только справки. Современный читатель может перейти непосредственно к контрольному списку планировщика запроса.

Для большинства запросов модернизизация от устаревшего планировщика запроса к NGQP требует небольшого усилия. Просто замените более старую версию SQLite более новой версией SQLite, повторно соберите, и приложение будет работать быстрее. Нет никаких изменений API или модификаций процедур компиляции.

Но как с любым изменением планировщика запроса, модернизация до NGQP действительно несет маленький риск представления исполнительных регрессов. Проблема здесь не состоит в том, что NGQP неправильный или хуже по сравнению с устаревшим планировщиком запроса. Учитывая достоверную информацию о селективности индексов, NGQP должен всегда выбирать план, который так же хорош или лучше, чем прежде. Проблема состоит в том, что некоторые запросы могут использовать индексы низкого качества и низкой селективности, не управляя ANALYZE. Старые планировщики запроса смотрят на много меньше возможных внедрений для каждого запроса и таким образом, они, возможно, нашли хороший план просто благодаря удаче. NGQP, с другой стороны, смотрит еще на многие возможности плана запросов, и он может выбрать различный план запросов, который работает лучше в теории, принимая хорошие индексы, но который дает исполнительный регресс на практике из-за формы данных.

Ключевые пункты:

• NGQP будет всегда находить равный или лучший план запросов, по сравнению с предшествующими планировщиками запроса, пока у него есть доступ к точным данным ANALYZE в файле SQLITE_STAT1.

• NGQP будет всегда находить хороший план запросов, пока схема не содержит индексы, у которых есть больше, чем приблизительно 10 или 20 строк с той же самой стоимостью в крайнем левом столбце индекса.

Не все запросы удовлетворяют этим условиям. К счастью, NGQP будет все еще обычно находить хорошие планы запросов, даже без этих условий. Однако, действительно (редко) возникают случаи, где исполнительные регрессы могут произойти.

4.1. Тематическое исследование: модернизация Fossil к NGQP

Fossil DVCS это система управления версиями, которая раньше отслеживала весь исходный код SQLite. Хранилище Fossil это файл базы данных SQLite. Fossil это система управления версиями для SQLite и испытательная платформа для SQLite. Каждый раз, когда улучшения сделаны в SQLite, Fossil одно из первых приложений, чтобы проверить и оценить те улучшения. Таким образом, Fossil была ранним последователем NGQP.

К сожалению, NGQP вызвал исполнительный регресс в Fossil.

Один из многих отчетов, что Fossil делает доступным, является временной шкалой изменений единственного отделения, показывая все слияния в и из того отделения. См. https://www.sqlite.org/src/timeline?nd&n=200&r=trunk для типичного примера такого отчета. Создание такого отчета обычно берет всего несколько миллисекунд. Но после модернизации до NGQP мы заметили, что этот отчет брал ближе к 10 секундам для ствола хранилища.

Основной запрос, используемый, чтобы произвести временную шкалу отделения, показывают ниже. Читатели, как ожидают, не поймут детали этого запроса. Комментарий будет следовать.

SELECT blob.rid AS blobRid, uuid AS uuid, datetime(event.mtime,'localtime')
       AS timestamp, coalesce(ecomment, comment) AS comment,
       coalesce(euser, user) AS user, blob.rid IN leaf AS leaf, bgcolor
       AS bgColor, event.type AS eventType,
       (SELECT group_concat(substr(tagname,5), ', ') FROM tag, tagxref
               WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid AND
               tagxref.rid=blob.rid AND tagxref.tagtype>0) AS tags, tagid AS
               tagid, brief AS brief, event.mtime AS mtime FROM event
               CROSS JOIN blob WHERE blob.rid=event.objid AND
               (EXISTS(SELECT 1 FROM tagxref
       WHERE tagid=11 AND tagtype>0 AND rid=blob.rid) OR
             EXISTS(SELECT 1 FROM plink JOIN tagxref ON rid=cid
                           WHERE tagid=11 AND tagtype>0 AND pid=blob.rid) OR
             EXISTS(SELECT 1 FROM plink JOIN tagxref ON rid=pid
                           WHERE tagid=11 AND tagtype>0 AND cid=blob.rid))
       ORDER BY event.mtime DESC LIMIT 200;

Этот запрос не особенно сложный, но несмотря на это он заменяет сотни или возможно тысячи строк процедурного кода. Суть запроса: просмотрите вниз таблицу EVENT, ища новые 200 регистраций, которые удовлетворяют любое из трех условий:

1. У регистрации есть признак "trunk".
2. У регистрации есть потомок, у которого есть признак "trunk".
3. У регистрации есть предок, у которого есть признак "trunk".

Первое условие заставляет все регистрации ствола быть показанными, второе и третье условия заставляет регистрации, которые сливаются с ним или отделяются от него, также быть включенными в список. Эти три условия осуществляются тремя OR-связанными запросами EXISTS в WHERE запроса. Замедление, которое произошло с NGQP, было вызвано вторыми и третьими условиями. Проблема та же самая в каждом, таким образом, мы исследуем только второе. Подзапрос второго условия может быть переписан (с незначительными и несущественными упрощениями) следующим образом:

SELECT 1 FROM plink JOIN tagxref ON tagxref.rid=plink.cid
       WHERE tagxref.tagid=$trunk AND plink.pid=$ckid;

Таблица PLINK вмещает отношения поколений между регистрациями. Таблица TAGXREF отображает признаки в регистрации. Для справки соответствующие части схем для этих двух таблиц показывают здесь:

CREATE TABLE plink(pid INTEGER REFERENCES blob, cid INTEGER REFERENCES blob);
CREATE UNIQUE INDEX plink_i1 ON plink(pid,cid);
CREATE TABLE tagxref(tagid INTEGER REFERENCES tag, mtime TIMESTAMP,
                     rid INTEGER REFERENCE blob, UNIQUE(rid, tagid));
CREATE INDEX tagxref_i1 ON tagxref(tagid, mtime);

Есть только два разумных способа осуществить этот запрос. Есть много других возможных алгоритмов, но ни один из других не претендует на то, чтобы быть "лучшим" алгоритмом.

1. Найдите всех потомков регистрации $ckid и проверьте каждого, чтобы видеть, есть ли у него признак $trunk.

2. Найдите все регистрации с меткой $trunk и проверьте каждую, чтобы видеть, является ли это потомком $ckid.

Интуитивно, мы, люди понимаем, что алгоритм 1 является лучшим. У каждой регистрации, вероятно, будет немного потомков (один наиболее распространенный случай), и каждый из них может быть проверен на признак $trunk в логарифмическое время. Действительно, алгоритм 1 является более быстрым выбором на практике. Но у NGQP нет интуиции. NGQP должен использовать твердую математику, и алгоритм 2 немного лучше математически. Это вызвано тем, что в отсутствие другой информации NGQP должен предположить, что индексы PLINK_I1 и TAGXREF_I1 имеют равное качество и одинаково отборные. Алгоритм 2 использует одну область индекса TAGXREF_I1 и обе области индекса PLINK_I1, тогда как алгоритм 1 использует только первую область каждого индекса. Когда алгоритм 2 использует больше материала, индекс NGQP правилен, чтобы судить, что он лучший алгоритм. Очки близки и алгоритм 2 едва-едва уступает algorithm-1. Но algorithm-2 действительно является верным выбором здесь.

К сожалению, алгоритм 2 медленнее, чем алгоритм 1 в этом применении.

Проблема состоит в том, что индексы не имеют равного качества. У регистрации, вероятно, будет только один потомок. Таким образом, первая область PLINK_I1 будет обычно сужать поиск только к единственной строке. Но есть тысячи и тысячи регистраций, помеченных "trunk", таким образом, первая область TAGXREF_I1 будет давать мало помощи в сужении поиска.

У NGQP нет способа знать, что TAGXREF_I1 почти бесполезен в этом запросе, если ANALYZE не управляли на базе данных. Команда ANALYZE собирает статистику по качеству различных индексов и хранит те статистические данные в таблице SQLITE_STAT1. Имея доступ к этой статистической информации, NGQP легко выбирает алгоритм 1 в качестве лучшего алгоритма с большим отрывом.

Почему наследие не запросило планировщик, чтобы выбрать алгоритм 2? Легко: потому что алгоритм NN даже не рассмотрел алгоритм 2. Графы проблемы планирования похожи на это:

В случае "without ANALYZE" слева алгоритм NN выбирает цикл P (PLINK) как внешний цикл, потому что 4.9 меньше, чем 5.2, приводя к пути P-T, который является алгоритмом 1. NN смотрит только на единственный лучший выбор в каждом шаге, таким образом, это полностью пропускает то, что 5.2+4.4 делает немного более дешевый план, чем 4.9+4.8. Но алгоритм N3 отслеживает 5 лучших путей для соединения с 2 путями, таким образом, он заканчивает тем, что выбрал путь T-P из-за его немного более низкой общей стоимости. Путь T-P является алгоритмом 2.

Обратите внимание на то, что с ANALYZE сметы лучше выровнены с действительностью, и алгоритм 1 отобран и NN и N3.

Оценки затрат в двух новых графах были вычислены NGQP с использованием двоичного логарифма и немного отличающиеся предположения стоимости по сравнению с наследием запрашивают планировщик. Следовательно, сметы в этих последних двух графах не непосредственно сопоставимы со сметами в графе TPC-H Q8.

4.2. Решение проблемы

Запуск ANALYZE на хранилище данных немедленно решило исполнительную проблему. Однако, мы хотим, чтобы Fossil был прочен и всегда работал быстро, независимо от того, было ли хранилище проанализировано. Поэтому запрос был изменен, чтобы использовать оператора CROSS JOIN вместо простого оператора JOIN. SQLite не переупорядочит таблицы CROSS JOIN. Это давняя особенность SQLite, которая специально предназначена, чтобы позволить хорошо осведомленным программистам проводить в жизнь особый порядок вложения цикла. Как только соединение было изменено на CROSS JOIN (добавлением единственного ключевого слова), NGQP был вынужден выбрать более быстрый алгоритм 1 независимо от того, была ли статистическая информация собрана, используя ANALYZE.

Мы говорим, что алгоритм 1 "быстрее", но это не строго верно. Алгоритм 1 быстрее в общих репозиториях, но возможно построить хранилище, в котором каждая регистрация находится на различной исключительно названной ветке, и все регистрации это потомки регистрации корня. В этом случае TAGXREF_I1 стал бы более отборным, чем PLINK_I1, а алгоритм 2 действительно будет более быстрым выбором. Однако, такие хранилища очень вряд ли появятся на практике.

4.3. Обновление 2017: лучшее исправление

Предшествующий текст был написан в начале 2013 перед первым выпуском версии 3.8.0 SQLite. Этот параграф был добавлен в середине 2021. В то время как все предыдущее обсуждение остается верным, много улучшений было сделано в планировщике запроса, делая эту целую секцию в основном спорной.

В 2017 Fossil был расширен, чтобы использовать новый PRAGMA optimize. Каждый раз, когда Fossil собирается закрыть соединение с базой данных к своему хранилищу, это сначала управляет "PRAGMA optimize", который в свою очередь заставит работать ANALYZE, если это будет необходимо. Обычно ANALYZE не необходим, и таким образом, нет никакой измеримой потери производительности для того, чтобы сделать это. Но время от времени ANALYZE можно было бы управлять на нескольких таблицах в хранилище данных. Проблемы планирования запроса, такие как описанная здесь, больше не возникают в Fossil. То, что ANALYZE управляют периодически, чтобы сохранять таблицу sqlite_stat1 актуальной, означает, что настройка запросов больше не требуется. Мы не должны были отлаживать запрос в Fossil ранее.

Поэтому текущая рекомендация для предотвращения проблем, таких как эта, состоит в том, чтобы просто управлять "PRAGMA optimize" (возможно сначала "PRAGMA analysis_limit=200") только до закрытия каждого соединения с базой данных. CROSS JOIN все еще доступно, но если вы будете сохранять статистику планировщика запроса в таблице sqlite_stat1 актуальной, это обычно не будет необходимо.

5. Контрольный список для предотвращения или решения проблем планировщика запроса

1. Не паникуйте! Случаи, где планировщик запроса выбирает низший план, на самом деле довольно редки. Вы вряд ли будете натыкаться на любые проблемы в своем запросе. Если у вас нет исполнительных проблем, вы не должны волноваться об этом.

2. Создайте соответствующие индексы. Большинство проблем производительности sql возникает не из-за проблем планировщика запроса, а скорее из-за отсутствия соответствующих индексов. Удостоверьтесь, что индексы доступны, чтобы помочь выполнению больших запросов. Большинство исполнительных запросов может быть решено одной или двумя командами CREATE INDEX без изменений кода приложения.

3. Избегайте создавать низкокачественные индексы. Низкокачественный индекс (в целях этого контрольного списка) является тем, где есть больше чем 10 или 20 строк в таблице, у которых есть та же самая стоимость для крайнего левого столбца индекса. В частности избегайте использования булевых или "enum" колонок как крайних левых столбцов ваших индексов.

   Исполнительная проблема Fossil, описанная в предыдущей части этого документа, возникла как раз потому, что было более, чем десять тысяч записей в таблице TAGXREF с той же самой стоимостью для крайнего левого столбца (колонка TAGID) индекса TAGXREF_I1.

4. Если необходимо использовать низкокачественный индекс, несомненно, надо управлять ANALYZE. Низкокачественные индексы не смутят планировщика запроса, пока планировщик запроса знает, что индексы имеют низкое качество. И способ, которым планировщик запроса узнает об этом, это содержание таблицы SQLITE_STAT1, которое вычисляется командой ANALYZE.

   Конечно, ANALYZE работает эффективно только, если у вас есть существенное количество содержания в вашей базе данных. Создавая новую базу данных, вы ожидаете накапливать много данных, можно управлять командой "ANALYZE sqlite_schema", чтобы составить таблицу SQLITE_STAT1, затем предварительно наполнить sqlite_stat1 (обычными запросами INSERT) содержанием, которое описывает типичную базу данных для запросов, возможно, содержанием, которое вы извлекли после управления ANALYZE на хорошо наполненной базе данных шаблона в лаборатории. Или вы могли бы просто управлять "PRAGMA optimize" перед закрытием соединения с базой данных так, чтобы ANALYZE управляли автоматически по мере необходимости, чтобы поддержать актуальной таблицу sqlite_stat1.

5. Инструментуйте свой код. Добавьте логику, которая сообщает быстро и легко, какие запросы занимают слишком много времени. Тогда просто улучшайте именно те определенные запросы.

6. Используйте функции SQL unlikely() и likelihood() . SQLite обычно предполагает, что у условий в операторе Where, который не может использоваться индексами, есть сильная вероятность того, чтобы быть верным. Если это предположение неправильное, оно могло бы привести к подоптимальному плану запросов. Функции unlikely() и likelihood() могут использоваться, чтобы предоставить намеки планировщику запроса об условиях оператора Where, которые, вероятно, не верны, и таким образом помочь планировщику запроса в отборе самого лучшего плана.

7. Примените CROSS JOIN, чтобы провести в жизнь особый порядок вложения цикла на запросах, которые могли бы использовать низкокачественные индексы в непроанализированной базе данных. SQLite рассматривает оператор CROSS JOIN особо, вынуждая левую таблицу быть внешним циклом относительно таблицы справа.

   Избегите этого шага, если это возможно, поскольку он побеждает одно из огромных преимуществ целого языкового понятия SQL: прикладной программист не должен заниматься планированием запроса. Если вы действительно используете CROSS JOIN, в вашем цикле разработки, надо прокомментировать использование CROSS JOIN тщательно так, чтобы можно было вынуть его позже, если это возможно. Избегайте использования CROSS JOIN рано в цикле разработки, поскольку это преждевременная оптимизация, которая известна как the root of all evil.

8. Используйте одноместный оператор "+", чтобы дисквалифицировать условия оператора WHERE. Если планировщик запроса настаивает на том, чтобы выбирать низкокачественный индекс для конкретного запроса, когда много индексов более высокого качества доступно, то тщательное использование одноместных операторов "+" в WHERE может вынудить планировщика запроса уйти от низкокачественного индекса. Избегайте использования этой уловки, если вообще возможно, а особенно избегайте этого рано в цикле разработки приложения. Остерегайтесь добавления одноместного оператора "+" к выражению равенства: это могло бы изменить результат того выражения, если включается близость типа.

9. Используйте синтаксис INDEXED BY, чтобы провести в жизнь выбор конкретных индексов на проблемных запросах. Как с предыдущими двумя советами, избегайте этого шага, если это возможно, и особенно избегайте делать это рано в развитии, поскольку это преждевременная оптимизация.

6. Итог

Планировщик запроса в SQLite обычно делает потрясающую работу по отбору алгоритмов для управления вашими SQL-операторами. Это верно для устаревшего планировщика запроса и еще более верно для нового NGQP. Может быть случайная ситуация, где из-за неполной информации планировщик запроса выбирает неоптимальный план. Это будет происходить менее часто с NGQP, чем с устаревшим планировщиком запроса, но это могло бы все еще произойти. Только в тех редких случаях разработчики приложений должны принять участие и помочь планировщику запроса сделать правильную вещь. В общем случае NGQP это просто новое улучшение к SQLite, которое делает выполнение приложения немного быстрее и не требует, чтобы разработчик о нем думал.