Построение регистронезависимого индекса в Tarantool

18 Aug 2021, 20:00

Введение

Ранее я уже писал статью по созданию приложения на платформе Tarantool, теперь же я утроился на работу в компанию Picodata, которая оказывает услуги по его эксплуатации.

Сегодня у меня появилась задача разобраться как построить регистронезависимый индекс (case insensitive index) для поиска по текстовым данным в Tarantool.

Регистронезависимый индекс полезен когда вам надо сравнивать данные независимо от регистра. Например строки ААА, aAa, aaa для такого индекса будут эквивалентны.

Построение индекса

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

t = box.schema.space.create('collate_test_1', { engine = 'vinyl', format = {{name="name", type="string"}} })
t:create_index('insens', {parts = {
    {'name', type = 'string', collation='unicode_ci'},
}})

Чтобы индекс сработал у запроса нужно добавить подсказу collate "unicode_ci" в sql запрос рядом с нужным полем:

select * from "collate_test_1" where "lastname" = 'иван' collate "unicode_ci"

Если посмотреть план выполнения запроса, то можно увидеть следующее:

--- metadata:
  - name: selectid
    type: integer
  - name: order
    type: integer
  - name: from
    type: integer
  - name: detail
    type: text
  rows:
  - [0, 0, 0, 'SEARCH TABLE collate_test_1 USING COVERING INDEX insens (name=?)
      (~1 row)']

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

Особенности

если в индексе и запросе установлены разные collate, то индекс не сработает. Например:

box.cfg{}

local fields = {
    {name = 'id', type = 'unsigned'},
    {name = 'firstname', type = 'string'},
    {name = 'lastname', type = 'string'},
    {name = 'state', type = 'integer'}
}

t = box.schema.space.create('collate_test_4', { engine = 'vinyl', format = fields})
t:create_index('insens', {parts = {
    {'firstname', type = 'string', collation='unicode_ci'},
}})
t:create_index('insens1', {parts = {
    {'lastname', type = 'string', collation='unicode'},
}})

box.execute([[explain query plan select * from "collate_test_4" where "firstname"='петров' collate "unicode" and "lastname" = 'иван' collate "unicode_ci" ]])

-- План выполнения
-- ---
-- - metadata:
--   - name: selectid
--     type: integer
--   - name: order
--     type: integer
--   - name: from
--     type: integer
--   - name: detail
--     type: text
--   rows:
--   - [0, 0, 0, 'SCAN TABLE collate_test_4 (~262144 rows)']

План запроса показывает что индекс не используется

если индекс составной и у одного из полей в индексе при запросе не указан collate - индекс не работает. Пример:

box.cfg{}

local fields = {
    {name = 'id', type = 'unsigned'},
    {name = 'firstname', type = 'string'},
    {name = 'lastname', type = 'string'},
    {name = 'state', type = 'integer'}
}

t = box.schema.space.create('collate_test_1', { engine = 'vinyl', format = fields })
t:create_index('insens', {parts = {
    {'firstname', type = 'string', collation='unicode_ci'},
    {'lastname', type = 'string', collation='unicode_ci'},
    {'state', type = 'integer'}
}})

box.execute([[explain query plan select * from "collate_test_1" where "firstname"='петров' and "lastname" = 'иван' collate "unicode_ci" and "state" = 1 ]])

-- План выполнения
-- ---
-- - metadata:
--   - name: selectid
--     type: integer
--   - name: order
--     type: integer
--   - name: from
--     type: integer
--   - name: detail
--     type: text
--   rows:
--   - [0, 0, 0, 'SCAN TABLE collate_test_1 (~262144 rows)']

если collate стоит в конце запроса - индекс не работает. Пример:

box.cfg{}

local fields = {
    {name = 'id', type = 'unsigned'},
    {name = 'firstname', type = 'string'},
    {name = 'lastname', type = 'string'},
    {name = 'state', type = 'integer'}
}

t = box.schema.space.create('collate_test_1', { engine = 'vinyl', format = fields })
t:create_index('insens', {parts = {
    {'firstname', type = 'string', collation='unicode_ci'},
    {'lastname', type = 'string', collation='unicode_ci'},
    {'state', type = 'integer'}
}}

box.execute([[explain query plan select * from "collate_test_1" where ("firstname"='петров' and "lastname" = 'иван' and "state" = 1) collate "unicode_ci"]])

-- План выполнения
-- ---
-- - metadata:
--   - name: selectid
--     type: integer
--   - name: order
--     type: integer
--   - name: from
--     type: integer
--   - name: detail
--     type: text
--   rows:
--   - [0, 0, 0, 'SCAN TABLE collate_test_1 (~262144 rows)']

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

box.cfg{}

local fields = {
    {name = 'id', type = 'unsigned'},
    {name = 'firstname', type = 'string'},
    {name = 'lastname', type = 'string'},
    {name = 'state', type = 'integer'}
}

t = box.schema.space.create('collate_test_2', { engine = 'vinyl', format = fields })
t:create_index('id', { parts = { { 1 } } })
t:create_index('insens', {parts = {
    {'firstname', type = 'string', collation='unicode_ci'},
    {'lastname', type = 'string', collation='unicode'},
    {'state', type = 'integer'}
}})

box.execute([[explain query plan select * from "collate_test_2" where "firstname"='петров' collate "unicode_ci" and "lastname" = 'иван' collate "unicode_ci" and "state" = 1 ]])

-- План выполнения
-- ---
-- - metadata:
--   - name: selectid
--     type: integer
--   - name: order
--     type: integer
--   - name: from
--     type: integer
--   - name: detail
--     type: text
--   rows:
--   - [0, 0, 0, 'SEARCH TABLE collate_test_2 USING COVERING INDEX insens (firstname=?)
--      (~8 rows)']

Для более полной информации можете посмотреть планы исполнения различных запросов с collate на github.

Заключение

В статье показан пример создания case insensitive index для Tarantool, а также были описаны его некоторые особенности которые нужно учитывать при построении запросов.