Виртуальная машина? Да, но не виртуальная машина 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
Извини, пришлось.
Init 0 1 0
- обозначает начало программы и переходит к адресу 1.String8 0 1 0 'hello world'
- «привет, мир» хранится в регистре 1.ResultRow 1 1 0
- Сообщите курсору, что регистр 1 содержит строку вывода.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
Init 0 27 0
- Запускай и сразу отправляйся по адресу 27.Transaction 0 1 0 0 01
- Запустите транзакцию записи в текущей базе данных и убедитесь, что схема базы данных является ожидаемой версией.Goto 0 1 0
- Вернуться к адресу 1.ReadCookie 0 3 2
- Считать файл cookie №2 (формат базы данных) из базы данных и сохранить его значение в регистре 3.
Что такое файлы cookie? Файлы cookie - это значения, которые присутствуют в фактических файл базы данных, который использует SQLite.If 3 5 0
- Перейти к адресу 5, если значение в регистре 3 не равно нулю. Интерпретация: мы пропустим установку некоторых файлов cookie для базы данных, если формат базы данных уже настроен (предположим, что его еще нет).SetCookie 0 2 4
- записать4
в файл cookie номер 2 для текущей базы данных. Это устанавливает формат файла базы данных равным 4.SetCookie 0 5 1
- записать1
в файл cookie номер 5 для текущей базы данных. Номер cookie 5 обозначает формат кодировки текста файла базы данных. Установив для него значение1
, мы выбираем UTF-8.CreateTable 0 2 0
- Создайте новую таблицу и поместите ее местоположение в файл базы данных в регистр 2.OpenWrite 0 1 0 5
- Откройте курсор чтения / записи с именем0
с 5 столбцами в таблице, корневая страница которой -1
.
Интерпретация: Страница 1 содержитsqlite_master
таблицу, в которую мы собираемся добавить запись, определяющую нашуblog
таблицу.NewRowid 0 1 0
- Получить новое значение rowid и поместить его в регистр1
.Blob 6 3 0 _
- Имеется пустой объект длиной6
байта, который мы собираемся сохранить в регистре3
.Insert 0 3 1 _ 08
- Используя курсор0
, запишите данные из регистра3
в таблицу, используя значение в регистре1
в качестве ключа, и увеличьте количество строк в таблице.
Интерпретация: Мы пишем blob-объект, который был недавно загружен в регистр 3 таблицы как новая строка, и использует построенный нами rowid в качестве ключа.Close 0 0 0
- Закройте ранее открытый курсор (курсор0
).Close 0 0 0
- Нет работы, так как курсор уже закрыт.Null 0 4 5
- записатьNULL
в регистры4
и5
.OpenWrite 1 1 0 5
- Откройте курсор чтения / записи с именем1
с 5 столбцами в таблице, корневой страницей которой является1
.
Интерпретация: Повторно откройте курсор в таблицеsqlite_master
.SeekRowid 1 16 1
- Используя курсор1
, перейдите к адресу16
, если курсор не содержит идентификатора строки значения в регистре1
. В противном случае двигайтесь дальше. (давайте двигаться дальше, так как мы только что что-то записали в значение в регистре 1)Rowid 1 5 0
- сохранить значение идентификатора строки для курсора1
в регистре5
.IsNull 5 24 0
- Перейти к адресу24
, если значение в регистре5
равноNULL
.
Интерпретация: Если по какой-то причине курсор не указывает на строку вsqlite_master
таблице, мы собираюсь бросить курить, перейдя в конец программы. (предположим, значение было неNULL
)String8 0 6 0 'table’
- сохранить'table'
в реестре6
.String8 0 7 0 'blog'
- сохранить'blog'
в реестре7
.String8 0 8 0 'blog'
- сохранить'blog'
в реестре8
.Copy 2 9 0
- Скопируйте значение из регистра2
в регистр9
.
Интерпретация: мы копируем расположение страницы файла базы данных из новой таблицы, которую мы создали еще во время инструкции по адресу 5 из регистра 2–9, потому что мы собираемся использовать его для вставки в таблицуsqlite_master
.String8 0 10 0 'CREATE TABLE ....’
- сохранить операторCREATE TABLE
в регистре10
.MakeRecord 6 5 11 'BBBDB'
- Создайте запись таблицы, используя регистры с6
по10
(6 + (5–1)), и сохраните ссылку на эту запись в регистре11
. СтрокаBBBDB
говорит, что первые три столбца и последний столбец в записи должны иметь сродство типа «blob», а четвертый должен быть числом.
Интерпретация: Мы ' мы, наконец, построим строку вsqlite_master
для нашей таблицыblog
.Insert 1 11 5
- С помощью курсора1
запишите данные записи, на которые указывает регистр11
, используя в качестве ключа идентификатор строки, который мы сохранили в регистре5
.SetCookie 0 1 1
- Установить значение cookie схемы равным1
. Файл cookie схемы - это файл cookie с номером 1, и он обозначает текущую версию схемы базы данных.ParseSchema 0 0 0 "tbl_name='blog'..."
- проанализировать все записи схемы вsqlite_master
, используя значение параметра p4 в качестве предложенияWHERE
. (это порождает еще один вызов в VDBE)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
Init 0 12 0
- Запускайте и сразу переходите к адресу 12.Transaction 0 1 1 0 01
- Запустите транзакцию записи в текущей базе данных и убедитесь, что схема базы данных является ожидаемой версией.
Помните: по адресу 24 программы байт-кодаCREATE TABLE
1
был сохранен как версия схемы. ; в этой инструкции мы просто убеждаемся, что он по-прежнему1
.String8 0 6 0 'now’
- сохранить'now'
в регистре6
.Goto 0 1 0
- Перейти к адресу1
.OpenWrite 0 2 0 4
- Откройте курсор чтения / записи с именем0
с4
столбцами в таблице, корень которой находится на странице2
.
Помните: таблицаblog
была создана на странице базы данных2
, и она имеет4
столбцы.NewRowid 0 1 0
- Получить новый идентификатор строки для таблицы и сохранить его в регистре1
.String8 0 2 0 'Winter is Coming'
- сохранить'Winter is Coming'
в реестре2
.String8 0 3 0 'Ned Stark'
- сохранить'Ned Stark'
в регистре3
.Function0 1 6 4 date(-1) 01
- вызвать функциюdate
, используя значение в регистре6
в качестве единственного аргумента, и сохранить результат в регистре4
.String8 0 5 0 'It comes in season 7.'
- сохранить'It comes in season 7.'
в реестре5
.HaltIfNull 1299 2 2 'blog.title' 01
- Если значение в регистре2
равно нулю, завершите программу с кодом ошибки1299
.
Интерпретация: Столбецtitle
вblog
был определен какNOT NULL
, поэтому нам нужно проверить, что значение мы собираемся сохранить, ну, не ноль. Если это так, мы выдадим ошибку с кодом SQLITE_CONSTRAINT_NOTNULL.HaltIfNull 1299 2 3 'blog.author' 01
- Если значение в регистре3
равно нулю, завершите программу с кодом ошибки1299
.
Интерпретация: Как иtitle
, столбецauthor
также определен какNOT NULL
.MakeRecord 2 4 7 'BBDB'
- Создайте запись таблицы, используя регистры с2
по5
(2 + (4–1)), и сохраните ссылку на эту запись в регистре7
. СтрокаBBDB
говорит, что запись должна состоять из двух больших двоичных объектов, числа и большого двоичного объекта (в указанном порядке).Insert 0 7 1 'blog' 1b
- Вставить запись, на которую указывает регистр7
с ключом в регистре1
, в таблицуblog
. Значение1b
дляp5
- это битовая маска, которая, помимо прочего, означает, что VDBE должен подсчитать количество измененных строк и сохранить идентификатор последней вставленной строки для последующего доступа.
Интерпретация: Это так. где запись в блоге Неда Старка фактически добавлена в таблицу.Halt 0 0 0
- Готово! Выйти с кодом ошибки0
(успех).
Попробуй сам!
Если ты еще со мной, отличная работа! Если вы хотите узнать больше о VDBE и байт-коде SQLite, попробуйте самостоятельно просмотреть EXPLAIN
результаты:
- Создайте таблицу с составным первичным ключом.
- Вставьте данные в эту таблицу.
- Создайте другую таблицу, добавьте в нее данные, относящиеся к данным в первой таблице, и выполните
SELECT
запрос, которыйJOIN
объединит две таблицы вместе. - Посмотрите, что делает
UNION
изSELECT
запросов.