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

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. Текст он ищет прекрасно, а для фильтрации можно использовать атрибуты.

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

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 (используем 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 и показываем клиенту. Переиндексацию можно делать раз в несколько часов (мы делаем раз в сутки — это приемлемо в нашем случае). Для более критичных к реальному времени ситуаций можно использовать дописывание небольших порций данных в ReplacingMergeTree.

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

TL;DR

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