воскресенье, 28 июля 2013 г.

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)
Причем если бы мы искали точное соответствие по дате, то сработали бы оба индекса, но при поиске по диапазону только последний.

пятница, 10 мая 2013 г.

Функциональный индекс - индекс по части таблицы

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

К примеру, у нас есть таблица с историей продаж, но данные нам нужны только за последний месяц. В этом случае можно создать индекс:
CREATE INDEX retail.xif_rtl_sale_history_fnc ON retail.rtl_sale_history
  (
    CASE WHEN OPER_DATE > TO_DATE('01.04.2013','dd.mm.yyyy') THEN OPER_DATE END DESC,
    nmcl_id                         ASC
  )
  TABLESPACE  indx
/

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

В этом индексе сразу видно большой минус - нельзя использовать SYSDATE в логике функционального индекса из-за его размер будет расти и его нужно будет раз в некоторое время перенастраивать на новую дату.

Также, чтобы индекс правильно использовался, фильтр "CASE WHEN OPER_DATE > TO_DATE('01.04.2013','dd.mm.yyyy') THEN OPER_DATE END" в точном соответствии нужно добавить в запрос.