Использование Clickhouse для фильтрации по колонкам

admin

19.08.2020 07:52

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

mysql> describe products;
+-----------------+----------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| Field           | Type                                                                                                           | Null | Key | Default | Extra          |
+-----------------+----------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| id              | bigint(20) unsigned                                                                                            | NO   | PRI | NULL    | auto_increment |
| title           | varchar(1024)                                                                                                  | YES  |     | NULL    |                |
| reviews         | int(10) unsigned                                                                                               | NO   |     | 0       |                |
| price           | float unsigned                                                                                                 | NO   |     | 0       |                |
...

## Таких колонок больше 40

В интерфейсе у клиента есть возможность фильтровать и сортировать данные почти по любому полю. Причем для числовых фильтров и дат можно выбирать диапазоны. Таблица насчитывает почти 100 млн записей, и MySQL (хорошо настроенный) плохо справляется с такими запросами:

mysql> SELECT * FROM products
WHERE rank > 100000 AND price > 25 AND price < 50 AND reviews > 1000
ORDER BY queued DESC LIMIT 10
...
10 rows in set (***23.17 sec***)

## Типичный запрос – несколько фильтров и сортировка

Вторая сложная задача – к выбранным фильтрам может быть добавлен поиск по тексту (по полю title). А это либо использование внутреннего полнотекстового движка Mysql либо внешнего инструмента (вроде sphinx).

Почему Mysql не подойдет

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

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

Но!

Особенность нашей задачи такова, что для фильтрации может быть выбрана любая из сорока колонок. А это значит, что нужно создать минимум 40 индексов. А наличие такого количества индексов создает следующие три проблемы:

1. Производительность вставки

Она упадет, т.к. Mysql должен будет управлять большим количеством индексов. Кроме этого, будет нагружена дисковая подсистема – писать придется значительно больше.

2. Расход дискового пространства

База станет занимать намного больше места на диске. Мы, например, используем только 4 самых необходимых индекса, и соотношение размера данных к индексам выглядит так:

# Данные  **==============================** 60%
# Индексы **====================** 40%

## И это на 4х индексах!

При этом таблица занимает 50Гб на диске. Если создать 40 индексов, эта картинка может выглядеть так:

# Данные  **==========** 20%
# Индексы ***========================================*** 80%

## Мы не рискнули проверять, скорее всего все будет еще хуже

3. Медленные запросы

Из-за какой-либо возможности прогнозировать последовательность выбора колонок (могут быть какие-угодно), медленные запросы все равно останутся.

Использование Sphinx’a

Поскольку нам нужно решать и задачу полнотекстового поиска и эффективной фильтрации, мы решили реализовать поиск на Sphinx’e. Текст он ищет прекрасно, а для фильтрации можно использовать [http://sphinxsearch.com/docs/current/attributes.html атрибуты].

Базовая конфигурация выглядит так:

source source

{

type = mysql

sql_host = 127.0.0.1

sql_user = пользователь

sql_pass = всем_паролям_пароль

sql_db = db

**sql_query = SELECT * FROM products

sql_attr_bool = is_qualitative

sql_attr_float = price

sql_attr_string = type**

…

}

index products

{

source = source

path = /var/lib/sphinxsearch/data/products

}

## Настойка индекса Sphinx с фильтрами

Вся индексация занимает около 20…30 минут. Дельта-индексы помогут получить более актуальные данные в индексе, но это позже. Теперь, если выполнить тот же запрос в Sphinx’e (используем [http://sphinxsearch.com/docs/current/sphinxql.html Mysql-протокол]), увидим хорошее улучшение в скорости:

mysql> SELECT * FROM products
WHERE rank > 100000 AND price > 25 AND price < 50 AND reviews > 1000
ORDER BY queued DESC LIMIT 10;
...
10 rows in set (**3.17 sec**)

## Sphinx фильтрует значительно быстрее, и не нужно создавать никаких доп. индексов

Однако нам этого не достаточно, т.к. мы собираемся получить производительность выборок меньше 0.5 секунды. Хотя при текстовом поиске, скорость отличная:

mysql> SELECT * FROM products
WHERE **match('iphone case')** and rank > 100000 AND price > 25 AND price < 50 AND reviews > 1000
ORDER BY queued DESC LIMIT 10;
...
10 rows in set (**0.10 sec**)

## Текстовый поиск и фильтрация работают достаточно быстро

Индекс Сфинкса на диске занимает 11Гб, что точно меньше всех возможных индексов Mysql.

Clickhouse для фильтрации

Вспоминаем, что Clickhouse – векторное хранилище. А это значит, что фильтровать данные по любым колонкам – это ключевое преимущество такой базы данных.

Чтобы построить такой поисковый индекс на Clickhouse, достаточно выгрузить данные из Mysql и сложить их в MergeTree таблицу:

clickhouse-client 🙂 CREATE TABLE search_index

(

id UInt32,

reviews UInt32,

rank UInt32,

…

) ENGINE = MergeTree()

PARTITION BY category

ORDER BY id

SETTINGS index_granularity = 8192

## Таблица в Clickhouse для хранения индекса

Теперь нужно загрузить данные из Mysql в Clickhouse. Сначала экспортируем данные из Mysql в TSV:

mysql> SELECT * **INTO OUTFILE “/var/lib/mysql-files/index.ch.tsv”** FROM products;

## экспортирует всю таблицу в TSV файл

Это самый длительный процесс, он занимает около 20 минут. Теперь делаем вставку в Clickhouse-таблицу:

cat **index.ch.tsv** | clickhouse-client –query “**INSERT INTO search_index FORMAT TSV**”

## Вставляем данные в Clickhouse, что занимает пару минут

Теперь пробуем выполнить наш запрос с фильтрами уже в Clickhouse:

clickhouse-client :) SELECT * FROM search_index WHERE rank > 100000 AND price > 25 AND price < 50 AND reviews > 1000 ORDER BY queued DESC LIMIT 10;
...
10 rows in set. **Elapsed: 0.054 sec**.

## То что нужно!

При этом размер таблицы в Clickhouse составляет всего 1.2Гб. Теперь интерфейс нашего приложения делает следующее:

Если запрос содержит текстовые фильтры – отправляем его в Sphinx.
Если нет – в Clickhouse.

По полученным идентификаторам (id) выгружаем данные из Mysql и показываем клиенту. Переиндексацию можно делать раз в несколько часов (мы делаем раз в сутки – это приемлемо в нашем случае). Для более критичных к реальному времени ситуаций можно использовать дописывание небольших порций данных в [https://clickhouse.yandex/docs/en/operations/table_engines/replacingmergetree/ ReplacingMergeTree].

Почему не избавиться от Mysql и не хранить все данные в Clickhouse? Колоночные базы данных не предназначены для большого (и даже небольшого) количества обновлений (там нет классического UPDATE, только эмуляция). А в нашем случае – их около 10 миллионов в сутки.

<h2>TL;DR

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