Виртуальная машина? Да, но не виртуальная машина Java. В этом посте я сосредоточусь на том, чтобы дать вам базовое понимание «Virtual DataBase Engine» или VDBE в SQLite.

Моя серия «Повышение производительности из SQLite» в первую очередь предназначена для инженеров Android, но эта статья, в частности, будет посвящена самому SQLite, и обсуждаемые темы будут актуальны для всех разработчиков, которые его используют.

SQLite - это виртуальная машина

Раньше мне казалось, что SQLite анализирует и запускает операторы как интерпретатор, но оказалось, что это не совсем так. В поисках версии MySQL EXPLAIN для SQLite я наткнулся на документацию, в которой описан принцип работы SQLite:

SQLite работает, переводя операторы SQL в байт-код, а затем запускает этот байт-код на виртуальной машине. - SQLite.org

Виртуальная машина SQLite называется «Virtual DataBase Engine» или для краткости «VDBE».

Возможно, вы уже знаете, что чаще всего называется программой с байт-кодом в SQLite: «подготовленный оператор». Также, как и в большинстве программ: подготовленные операторы могут принимать входные данные (? переменных).

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

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

Подготовка заявления

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

Токенизация и парсинг

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

Подход SQLite к синтаксическому анализу четко изложен в официальной документации. Например, если вы когда-либо заходили на sqlite.org, пытаясь понять, как вы должны написать оператор INSERT, вы, вероятно, видели диаграмму, которая частично объясняет, как работает синтаксический анализатор SQLite:

Диаграммы, подобные приведенной выше, представляют собой способ визуализации описания формы Бэкуса-Наура (BNF) грамматики SQL, которую понимает SQLite. Чтение синтаксических диаграмм - довольно простой процесс, если вы знаете, что искать:

  • Овалы с текстом в ALL CAPS являются ключевыми словами.
  • Овалы с текстом lower-case-and-hypenated представляют многоразовые предложения грамматики (их синтаксические диаграммы будут перечислены ниже на странице в документации).
  • Овалы с знаками препинания, такими как круглые скобки, запятые и т. Д., Являются буквальными символами, которые необходимы.
  • Стрелки, соединяющие овалы, показывают порядок, в котором ключевые слова, предложения и токены должны появляться для построения действительного оператора.

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

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

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

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

Компиляция

Наконец: после того, как SQLite определил, как лучше всего подойти к выполнению вашего оператора, он составляет список низкоуровневых инструкций байт-кода, которые описывают всю операцию. Этот список инструкций в буквальном смысле представляет собой программу, которая будет запускаться на VDBE. Более популярное название программы, скомпилированной SQLite, - «подготовленный оператор».

Каждая инструкция байт-кода состоит из кода операции (имени инструкции) и до 5 параметров (входных значений или ссылок на регистры). В современных версиях SQLite существует более сотни различных типов инструкций. Они охватывают диапазон между простыми инструкциями потока управления, такими как Eq: переход к инструкции, если два регистра имеют одинаковое значение, и более специфичными для базы данных инструкциями, такими как ResultRow: который предоставляет данные курсору базы данных в текущей позиции, указывая при значениях, которые были загружены в регистры VDBE.

После компиляции в байт-код подготовленный оператор не нуждается в синтаксическом анализе или повторном выполнении процесса планирования запроса. Именно это делает повторное использование подготовленных операторов таким быстрым по сравнению с отказом от их повторного использования.

"ОБЪЯСНЯТЬ"

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

SQLite предоставляет механизм, который вы можете использовать для проверки байт-кода, который он будет генерировать для любого оператора. Чтобы увидеть байт-код, просто добавьте к своему оператору EXPLAIN. Из документации:

Когда появляется ключевое слово EXPLAIN ... оно заставляет оператор вести себя как запрос, который возвращает последовательность инструкций виртуальной машины, которые он использовал бы для выполнения команды, если бы ключевое слово EXPLAIN не присутствовало.
- SQLite.org

Вывод EXPLAIN представляет собой серию строк, каждая из которых является инструкцией в байт-коде подготовленного оператора, и каждая имеет 8 столбцов:

  • addr - Адрес инструкции.
  • opcode - код операции инструкции.
  • От p1 до p5 - значения параметров для инструкции.
  • comment - Комментарии, объясняющие, что делает инструкция.

Последний столбец: comment, скорее всего, будет пустым, если вы сами не скомпилируете SQLite и не установите -DSQLITE_ENABLE_EXPLAIN_COMMENTS option.

Объяснение «ОБЪЯСНИТЬ» на примере

Давайте воспользуемся EXPLAIN, чтобы взглянуть на байт-код для нескольких операторов. Поскольку в моей установке не были включены комментарии, я оставлю это. Каждый пример начинается с вывода, который вы получаете при использовании EXPLAIN, за которым следует пошаговое руководство по потоку, которое VDBE выполняет через программу байт-кода.

(Примечание: весь показанный здесь байт-код взят из SQLite 3.16.2 и может отличаться в других версиях.)

"Привет, мир"

sqlite> EXPLAIN SELECT "hello world";
addr  opcode         p1    p2    p3    p4             p5
----  -------------  ----  ----  ----  -------------  --
0     Init           0     1     0                    00
1     String8        0     1     0     hello world    00
2     ResultRow      1     1     0                    00
3     Halt           0     0     0                    00

Извини, пришлось.

  1. Init 0 1 0 - обозначает начало программы и переходит к адресу 1.
  2. String8 0 1 0 'hello world' - «привет, мир» хранится в регистре 1.
  3. ResultRow 1 1 0 - Сообщите курсору, что регистр 1 содержит строку вывода.
  4. Halt 0 0 0 - Выход с кодом ошибки 0 (все ок).

СОЗДАТЬ ТАБЛИЦУ

sqlite> EXPLAIN CREATE TABLE blog (
    title TEXT NOT NULL, 
    author TEXT NOT NULL, 
    pub_date INTEGER, 
    body TEXT
);
addr  opcode         p1    p2    p3    p4             p5
----  -------------  ----  ----  ----  -------------  --
0     Init           0     27    0                    00
1     ReadCookie     0     3     2                    00
2     If             3     5     0                    00
3     SetCookie      0     2     4                    00
4     SetCookie      0     5     1                    00
5     CreateTable    0     2     0                    00
6     OpenWrite      0     1     0     5              00
7     NewRowid       0     1     0                    00
8     Blob           6     3     0                   00
9     Insert         0     3     1                    08
10    Close          0     0     0                    00
11    Close          0     0     0                    00
12    Null           0     4     5                    00
13    OpenWrite      1     1     0     5              00
14    SeekRowid      1     16    1                    00
15    Rowid          1     5     0                    00
16    IsNull         5     24    0                    00
17    String8        0     6     0     table          00
18    String8        0     7     0     blog           00
19    String8        0     8     0     blog           00
20    Copy           2     9     0                    00
21    String8        0     10    0     CREATE TABLE blog (
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    pub_date INTEGER,
    body TEXT
)  00
22    MakeRecord     6     5     11    BBBDB          00
23    Insert         1     11    5                    00
24    SetCookie      0     1     1                    00
25    ParseSchema    0     0     0     tbl_name='blog' AND type!=...
26    Halt           0     0     0                    00
27    Transaction    0     1     0     0              01
28    Goto           0     1     0                    00
  1. Init 0 27 0 - Запускай и сразу отправляйся по адресу 27.
  2. Transaction 0 1 0 0 01 - Запустите транзакцию записи в текущей базе данных и убедитесь, что схема базы данных является ожидаемой версией.
  3. Goto 0 1 0 - Вернуться к адресу 1.
  4. ReadCookie 0 3 2 - Считать файл cookie №2 (формат базы данных) из базы данных и сохранить его значение в регистре 3.
    Что такое файлы cookie? Файлы cookie - это значения, которые присутствуют в фактических файл базы данных, который использует SQLite.
  5. If 3 5 0 - Перейти к адресу 5, если значение в регистре 3 не равно нулю. Интерпретация: мы пропустим установку некоторых файлов cookie для базы данных, если формат базы данных уже настроен (предположим, что его еще нет).
  6. SetCookie 0 2 4 - записать 4 в файл cookie номер 2 для текущей базы данных. Это устанавливает формат файла базы данных равным 4.
  7. SetCookie 0 5 1 - записать 1 в файл cookie номер 5 для текущей базы данных. Номер cookie 5 обозначает формат кодировки текста файла базы данных. Установив для него значение 1, мы выбираем UTF-8.
  8. CreateTable 0 2 0 - Создайте новую таблицу и поместите ее местоположение в файл базы данных в регистр 2.
  9. OpenWrite 0 1 0 5 - Откройте курсор чтения / записи с именем 0 с 5 столбцами в таблице, корневая страница которой - 1.
    Интерпретация: Страница 1 содержит sqlite_master таблицу, в которую мы собираемся добавить запись, определяющую нашу blog таблицу.
  10. NewRowid 0 1 0 - Получить новое значение rowid и поместить его в регистр 1.
  11. Blob 6 3 0 _ - Имеется пустой объект длиной 6 байта, который мы собираемся сохранить в регистре 3.
  12. Insert 0 3 1 _ 08 - Используя курсор 0, запишите данные из регистра 3 в таблицу, используя значение в регистре 1 в качестве ключа, и увеличьте количество строк в таблице.
    Интерпретация: Мы пишем blob-объект, который был недавно загружен в регистр 3 таблицы как новая строка, и использует построенный нами rowid в качестве ключа.
  13. Close 0 0 0 - Закройте ранее открытый курсор (курсор 0).
  14. Close 0 0 0 - Нет работы, так как курсор уже закрыт.
  15. Null 0 4 5 - записать NULL в регистры 4 и 5.
  16. OpenWrite 1 1 0 5 - Откройте курсор чтения / записи с именем 1 с 5 столбцами в таблице, корневой страницей которой является 1.
    Интерпретация: Повторно откройте курсор в таблице sqlite_master.
  17. SeekRowid 1 16 1 - Используя курсор 1, перейдите к адресу 16, если курсор не содержит идентификатора строки значения в регистре 1. В противном случае двигайтесь дальше. (давайте двигаться дальше, так как мы только что что-то записали в значение в регистре 1)
  18. Rowid 1 5 0 - сохранить значение идентификатора строки для курсора 1 в регистре 5.
  19. IsNull 5 24 0 - Перейти к адресу 24, если значение в регистре 5 равно NULL.
    Интерпретация: Если по какой-то причине курсор не указывает на строку в sqlite_master таблице, мы собираюсь бросить курить, перейдя в конец программы. (предположим, значение было не NULL)
  20. String8 0 6 0 'table’ - сохранить 'table' в реестре 6.
  21. String8 0 7 0 'blog' - сохранить 'blog' в реестре 7.
  22. String8 0 8 0 'blog' - сохранить 'blog' в реестре 8.
  23. Copy 2 9 0 - Скопируйте значение из регистра 2 в регистр 9.
    Интерпретация: мы копируем расположение страницы файла базы данных из новой таблицы, которую мы создали еще во время инструкции по адресу 5 из регистра 2–9, потому что мы собираемся использовать его для вставки в таблицу sqlite_master.
  24. String8 0 10 0 'CREATE TABLE ....’ - сохранить оператор CREATE TABLE в регистре 10.
  25. MakeRecord 6 5 11 'BBBDB' - Создайте запись таблицы, используя регистры с 6 по 10 (6 + (5–1)), и сохраните ссылку на эту запись в регистре 11. Строка BBBDB говорит, что первые три столбца и последний столбец в записи должны иметь сродство типа «blob», а четвертый должен быть числом.
    Интерпретация: Мы ' мы, наконец, построим строку в sqlite_master для нашей таблицы blog.
  26. Insert 1 11 5 - С помощью курсора 1 запишите данные записи, на которые указывает регистр 11, используя в качестве ключа идентификатор строки, который мы сохранили в регистре 5.
  27. SetCookie 0 1 1 - Установить значение cookie схемы равным 1. Файл cookie схемы - это файл cookie с номером 1, и он обозначает текущую версию схемы базы данных.
  28. ParseSchema 0 0 0 "tbl_name='blog'..." - проанализировать все записи схемы в sqlite_master, используя значение параметра p4 в качестве предложения WHERE. (это порождает еще один вызов в VDBE)
  29. Halt 0 0 0 - Завершить программу с кодом ошибки 0 (успешно!).

ВСТАВЛЯТЬ

Наконец, давайте посмотрим, что нужно, чтобы добавить строку в нашу недавно созданную blog таблицу.

sqlite> EXPLAIN INSERT INTO blog (title, author, pub_date, body) VALUES ('Winter is Coming', 'Ned Stark', date('now'), 'It comes in season 7.');
addr  opcode         p1    p2    p3    p4                     p5
----  -------------  ----  ----  ----  ---------------------  --
0     Init           0     12    0                            00
1     OpenWrite      0     2     0     4                      00
2     NewRowid       0     1     0                            00
3     String8        0     2     0     Winter is Coming       00
4     String8        0     3     0     Ned Stark              00
5     Function0      1     6     4     date(-1)               01
6     String8        0     5     0     It comes in season 7.  00
7     HaltIfNull     1299  2     2     blog.title             01
8     HaltIfNull     1299  2     3     blog.author            01
9     MakeRecord     2     4     7     BBDB                   00
10    Insert         0     7     1     blog                   1b
11    Halt           0     0     0                            00
12    Transaction    0     1     1     0                      01
13    String8        0     6     0     now                    00
14    Goto           0     1     0                            00
  1. Init 0 12 0 - Запускайте и сразу переходите к адресу 12.
  2. Transaction 0 1 1 0 01 - Запустите транзакцию записи в текущей базе данных и убедитесь, что схема базы данных является ожидаемой версией.
    Помните: по адресу 24 программы байт-кода CREATE TABLE 1 был сохранен как версия схемы. ; в этой инструкции мы просто убеждаемся, что он по-прежнему 1.
  3. String8 0 6 0 'now’ - сохранить 'now' в регистре 6.
  4. Goto 0 1 0 - Перейти к адресу 1.
  5. OpenWrite 0 2 0 4 - Откройте курсор чтения / записи с именем 0 с 4 столбцами в таблице, корень которой находится на странице 2.
    Помните: таблица blog была создана на странице базы данных 2, и она имеет 4 столбцы.
  6. NewRowid 0 1 0 - Получить новый идентификатор строки для таблицы и сохранить его в регистре 1.
  7. String8 0 2 0 'Winter is Coming' - сохранить 'Winter is Coming' в реестре 2.
  8. String8 0 3 0 'Ned Stark' - сохранить 'Ned Stark' в регистре 3.
  9. Function0 1 6 4 date(-1) 01 - вызвать функцию date, используя значение в регистре 6 в качестве единственного аргумента, и сохранить результат в регистре 4.
  10. String8 0 5 0 'It comes in season 7.' - сохранить 'It comes in season 7.' в реестре 5.
  11. HaltIfNull 1299 2 2 'blog.title' 01 - Если значение в регистре 2 равно нулю, завершите программу с кодом ошибки 1299.
    Интерпретация: Столбецtitle в blog был определен как NOT NULL, поэтому нам нужно проверить, что значение мы собираемся сохранить, ну, не ноль. Если это так, мы выдадим ошибку с кодом SQLITE_CONSTRAINT_NOTNULL.
  12. HaltIfNull 1299 2 3 'blog.author' 01 - Если значение в регистре 3 равно нулю, завершите программу с кодом ошибки 1299.
    Интерпретация: Как и title, столбец author также определен как NOT NULL.
  13. MakeRecord 2 4 7 'BBDB' - Создайте запись таблицы, используя регистры с 2 по 5 (2 + (4–1)), и сохраните ссылку на эту запись в регистре 7. Строка BBDB говорит, что запись должна состоять из двух больших двоичных объектов, числа и большого двоичного объекта (в указанном порядке).
  14. Insert 0 7 1 'blog' 1b - Вставить запись, на которую указывает регистр 7 с ключом в регистре 1, в таблицу blog. Значение 1b для p5 - это битовая маска, которая, помимо прочего, означает, что VDBE должен подсчитать количество измененных строк и сохранить идентификатор последней вставленной строки для последующего доступа.
    Интерпретация: Это так. где запись в блоге Неда Старка фактически добавлена ​​в таблицу.
  15. Halt 0 0 0 - Готово! Выйти с кодом ошибки 0 (успех).

Попробуй сам!

Если ты еще со мной, отличная работа! Если вы хотите узнать больше о VDBE и байт-коде SQLite, попробуйте самостоятельно просмотреть EXPLAIN результаты:

  • Создайте таблицу с составным первичным ключом.
  • Вставьте данные в эту таблицу.
  • Создайте другую таблицу, добавьте в нее данные, относящиеся к данным в первой таблице, и выполните SELECT запрос, который JOIN объединит две таблицы вместе.
  • Посмотрите, что делает UNION из SELECT запросов.