SQL: таблицы, JOIN, подзапросы, представления

[denis_tnt]

Обзор: один движок, два языка

Главная идея SQL в Tarantool 3.x, которую надо удержать в голове на весь урок: SQL не отдельная база поверх NoSQL, а второй язык доступа к тем же самым данным. Когда вы пишете

Код: Выделить всё

CREATE TABLE

, Tarantool создаёт обычный спейс (space) в движке memtx или vinyl, а столбцы превращаются в поля кортежей (tuple) с заданным форматом. Поэтому таблица, созданная через SQL, видна в Lua как

Код: Выделить всё

box.space.T

, а спейс, созданный в Lua, при наличии формата и первичного индекса виден в SELECT. Реляционная модель здесь - это контракт (формат, ограничения, индексы), наложенный на тот же кортежный сторадж.

SQL-таблица и NoSQL-спейс в Tarantool - это один и тот же объект. SQL добавляет к нему формат полей, типы, ограничения и индексы; данные физически лежат в кортежах.

SQL Tarantool соответствует большей части обязательных требований стандарта SQL JOIN, подзапросы, представления, индексы, группировки, транзакции. Запросы выполняются через

Код: Выделить всё

box.execute([[ ... ]])

; разделять SQL и NoSQL-операции можно даже в одной транзакции.

Как это устроено внутри

Таблица = спейс + формат + первичный индекс
CREATE TABLE обязательно требует PRIMARY KEY. Причина внутренняя: первичный ключ становится первичным индексом спейса (TREE), а в Tarantool кортеж невозможно адресовать без первичного индекса. Отсюда жёсткое правило: PRIMARY KEY в SQL означает одновременно UNIQUE и NOT NULL - по NULL-ключу строку нельзя однозначно найти в индексе.

Ограничения (constraints)
PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK - это объекты схемы. UNIQUE физически реализуется как вторичный уникальный индекс, поэтому уникальность - это всегда индекс, а не отдельная структура. FOREIGN KEY и CHECK живут в метаданных спейса и проверяются движком при вставке/обновлении.

JOIN и подзапросы: эфемерные индексы
JOIN не хранит результат - планировщик соединяет потоки кортежей на лету. Если для условия соединения или WHERE есть подходящий индекс, используется поиск по индексу (SEARCH). Если индекса нет, Tarantool строит временный эфемерный индекс (ephemeral) в памяти под конкретный запрос, либо честно перебирает все строки (SCAN). Подзапрос в FROM материализуется в такую же эфемерную структуру. Посмотреть стратегию можно через

Код: Выделить всё

EXPLAIN QUERY PLAN

.

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


Реляционная модель поверх кортежного стораджа Tarantool

Ключевые команды и примеры

Создание таблиц с ограничениями

Код: Выделить всё

box.execute([[
  CREATE TABLE authors (
    id    INTEGER PRIMARY KEY,
    name  STRING NOT NULL,
    UNIQUE (name)
  );
]])

box.execute([[
  CREATE TABLE books (
    id        INTEGER PRIMARY KEY,
    title     STRING NOT NULL,
    author_id INTEGER,
    year      INTEGER CHECK (year > 0),
    FOREIGN KEY (author_id) REFERENCES authors (id)
  );
]])

INNER и LEFT JOIN
INNER JOIN отдаёт только совпавшие пары, LEFT JOIN сохраняет все строки левой таблицы, дополняя несовпавшие NULL-ами.

Код: Выделить всё

-- только книги, у которых есть автор
SELECT b.title, a.name
FROM books AS b
INNER JOIN authors AS a ON b.author_id = a.id;

-- все авторы, даже без книг (у таких book = NULL)
SELECT a.name, b.title
FROM authors AS a
LEFT JOIN books AS b ON a.id = b.author_id;

Подзапрос

Код: Выделить всё

-- авторы, у которых есть хотя бы одна книга
SELECT name FROM authors
WHERE id IN (SELECT author_id FROM books);

Представление

Код: Выделить всё

CREATE VIEW author_books AS
  SELECT a.name AS author, b.title AS book
  FROM authors AS a
  JOIN books AS b ON a.id = b.author_id;

SELECT * FROM author_books WHERE author = 'Lem';

SEQSCAN и план запроса

Код: Выделить всё

-- полный перебор без индекса требует явного SEQSCAN (с 3.0)
SELECT * FROM SEQSCAN books WHERE title LIKE 'A%';

box.execute([[EXPLAIN QUERY PLAN
  SELECT b.title FROM books b JOIN authors a ON b.author_id = a.id;]])
-- в detail увидите SCAN / SEARCH USING INDEX / EPHEMERAL INDEX

Частые заблуждения и грабли

• Таблица без PRIMARY KEY невозможна. Это не каприз парсера, а требование стораджа - без первичного индекса спейс не адресуется.
• NULL в первичном или уникальном ключе. PRIMARY KEY всегда NOT NULL. На обычном UNIQUE-индексе поведение NULL отличается от классических СУБД - не закладывайтесь на привычную семантику.
• Попытка INSERT/UPDATE/DELETE по VIEW. Представление только для чтения. Чтобы писать через него, нужен триггер INSTEAD OF на эту вьюху.
• FOREIGN KEY обходится из NoSQL. Внешний ключ, заданный в SQL, проверяется на SQL/box-пути, но прямой

  Код: Выделить всё

  box.space.books:insert{...}

  из Lua может нарушить ссылочную целостность - проверки уровня SQL там не срабатывают так же.
• Ссылка на ещё не созданную таблицу. В отличие от SQLite, FOREIGN KEY на несуществующую таблицу запрещён - создавайте родительскую таблицу первой.
• Забытый SEQSCAN. С версии 3.0 запрос, требующий полного перебора, без ключевого слова SEQSCAN падает с ошибкой. Это защита от случайных тяжёлых сканов, а не баг. Поведение регулируется compat-опцией sql_seq_scan.
• Регистр идентификаторов. Обычные имена приводятся к ВЕРХНЕМУ регистру; имя в двойных кавычках "books" остаётся как есть, и

  Код: Выделить всё

  SELECT * FROM books

  его не найдёт.

Мини-лаба

• Запустите Tarantool, выполните

  Код: Выделить всё

  box.cfg{}

  . Создайте две таблицы из примера выше (authors, books) с FOREIGN KEY. Вставьте 2 авторов и 3 книги, причём одного автора оставьте без книг, а одну книгу - с author_id, который существует. Сделайте LEFT JOIN authors к books и убедитесь, что автор без книг попал в результат с NULL в столбце title. Затем создайте VIEW author_books и выполните

  Код: Выделить всё

  EXPLAIN QUERY PLAN

  для своего JOIN-запроса - найдите в выводе строки SCAN или SEARCH и определите, использовался ли индекс по author_id.

Контрольные вопросы

• Почему CREATE TABLE в Tarantool обязательно требует PRIMARY KEY и почему первичный ключ не может быть NULL? Свяжите ответ с устройством спейса и первичного индекса.
• Чем результат INNER JOIN отличается от LEFT JOIN для строки левой таблицы, у которой нет пары справа?
• Что физически происходит при выполнении JOIN, если по столбцу соединения нет индекса? Какие два варианта (SCAN и эфемерный индекс) и как их увидеть?
• Можно ли выполнить INSERT в представление напрямую? Если нет, какой механизм позволяет имитировать запись через VIEW?