SQLite: две особенности этой базы

Хочу рассказать про 2 отличия SQLite от других БД, решение которых придется долго искать, если вы только начинаете работать с ней.

1. Приведение к верхнему регистру UPPER юникод строк UTF8.
Необходимость в этом у меня появилась при регистронезависимом поиске по подстроке LIKE и как оказалось поумолчанию эта функций в SQLite нет и предлагается самим ее реализовать.
В интернете множество вариантов решения через настройку SQLite и доустановку дополнительных пакетов. Но мне такой способ не подходил, т.к. на шаред хостинге это не сделать.

К счастью SQLite позволяет делать собственные пользовательские функции, чем мы и воспользуемся.
Далее код на php для создания собственной функции UPPER - UPPER_UTF8.

createFunction('UPPER_UTF8', 'upper_ru', 1);
//array('SQLITE_DB', 'upper_ru') - если используете в составе класса 'SQLITE_DB'

?>

После этих нехитрых манупуляций в составе SQLite появится новая функция UPPER_UTF8 и с помощью ее можно будет делать регистронезависимые запросы:

SELECT 
  t.* 
FROM
  table t
WHERE
  UPPER_UTF8(t.col) LIKE UPPER_UTF8('%кАкойТо Запрос%')

2. Правильное создание индексов для поиска по диапазону в БД SQLite.
Если вы хотите осуществить быстрый поиск по таблице, то во всех субд для этого необходимо создать индекс. Но в SQLite еще нужно соблюсти правильную последовательность.
Так если вы создаете индекс по нескольким полям, то столбец диапозона должен обязательно идти последним.

Далее 2 примера, сначала неправильный:

--таблица
CREATE TABLE "cashes" (
    "id" INTEGER PRIMARY KEY  NOT NULL,
    "date" DATE NOT NULL,
    "uid" INT(10) NOT NULL DEFAULT ('1'),
    "visible" TINYINT(4) NOT NULL DEFAULT ('1')
);

--индекс
CREATE INDEX "XIF_CASHES_DUV" on cashes (date DESC, uid ASC, visible ASC);

--вызов плана
EXPLAIN QUERY PLAN SELECT
      c.id, c.uid, c.date
     FROM cashes c
     WHERE
      c.date BETWEEN '2013-04-01' AND '2013-06-01'
      AND c.uid = 1 AND c.visible = 1
     ORDER BY
      c.date;

--план
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_USR (uid=?) (~2 rows)

А дальше, как нужно было правильно создавать индекс. Поле даты - поле диапозона, должно было идти последним:

--индекс
CREATE INDEX "XIF_CASHES_DUV" on cashes (uid ASC, visible ASC, date DESC);

--план запроса
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_DUV (uid=? AND visible=? AND date>? AND date<?) (~42 rows)

Причем если бы мы искали точное соответствие по дате, то сработали бы оба индекса, но при поиске по диапазону только последний.

Что быстрей LEFT JOIN или подзапрос в SELECT

Часто требуется выбрать дополнительные параметры плюсом к основной выборке.
Это можно сделать двумя способами:

• LEFT JOIN к основному запросу
• Подзапрос в секции SELECT

Пример запроса, выбирающего номенклатуру и товарную группу номенклатуры:

Подзапрос в секции SELECT:

SELECT nom.Наименование,
(SELECT tg.Наименование FROM Товарная группа tg WHERE tg.ID = nom.Товарная группа)
FROM Номенклатура nom

Вариант с LEFT JOIN:

SELECT nom.Наименование, tg.Наименование
FROM Номенклатура nom
LEFT JOIN Товарная группа tg
 ON(tg.ID = nom.Товарная группа)

После разбора плана выполнения запроса в MS SQL Server, было выявлено:

• При выборке в секции SELECT требуется дополнительное время на слияние основной выборки и подзапроса. Вышло 1% от общего времени.(+ LEFT JOIN / - SELECT)
• Оказалось, что каждый LEFT JOIN выполняется отдельным потоком! В то время как подзапросы выполняются последовательно после основной выборки. (+ LEFT JOIN / - SELECT)
• Каждый LEFT JOIN объединяется в результирующую выборку, что требует дополнительной памяти. В то время как подзапрос вернет нам одно значение на каждую строку, т.е. для получения результата нам нужно меньше памяти. (- LEFT JOIN / + SELECT)

Вывод: В условиях многоядерных серверов LEFT JOIN имеет неоспоримые преимущества

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

SELECT nom.Наименование,
(SELECT TOP 1 tg.Наименование FROM Товарная группа tg WHERE tg.ID = nom.Товарная группа)
FROM Номенклатура nom

Такой запрос выполнялся дольше на 96% по сравнению с аналогичным без TOP 1

Разбор плана показал, что 1% дополнительного времени тратится вложенный цикл, а 95% на просмотр определенных строк не кластеризованного индекса!
Из этого можно сделать один вывод: никогда не используйте без надобности ограничения в подзапросах.

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