Какие есть годные практики для работы с большим количеством записей в таблице (1M+)?

Question

[Crash]

Работаю над проектом, в котором достаточно интенсивно накапливаются данные, в одной из таблиц. Над данный момент в ней 60 с небольшим колонок и более 500к записей. Записей прибавляется примерно по 3-4к в сутки, в будущем интенсивность записи будет только нарастать. Интерфейс работы с ней начал подтормаживать, в связи с чем сейчас занимаюсь оптимизацией.

Для себя выделил следующие способы:

1. Индексы, выборочно для полей, по которым чаще всего осуществляется поиск.
2. Объединение нескольких колонок в одну, для однотипных данных. Они будут храниться в формате JSON.
3. Партиционирование. Разумеется работа в проекте не идет одновременно со всеми записями, большинство старых записей хранятся больше для истории. Но тем не менее, они могут понадобится в любой момент. Исходя из этого, пока планирую создать новую, "горячую" таблицу с идентичной структурой. В "горячей" таблице будут храниться свежие данные, скажем за месяц. В "холодной", исходной таблице - вообще за все время, включая свежие записи. Горячая таблица будет для оперативной работы, холодная - для поиска по запросу. В дальнейшем холодную таблицу можно будет разделить на несколько, провести шардинг.

Подскажите пожалуйста, что можно еще сделать?

Comments

[Евгений Ромашкан]

Миллион+ это копейки, вопрос тут скорее почему у вас по 60 колонок в одной таблице.

[Crash]

Евгений Ромашкан, нарастание количества записей за сутки идет драматично, поэтому копейки скоро превратятся в рубли, а те в свою очередь десятки и сотни рублей) Нужно решать вопрос с оптимизацией уже сейчас.

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

[Дима]

попробуйте Column store, например Clickhouse или clustered column Store в MS SQL. ваш миллион сожмется в десяток-сотню мегабайт. они как раз заточены под таблицы с сотнями столбцов. Если сложные джойны не нужны, а достаточно схемы звёзда, то самое то

Answer 1

[Melkij]

Объединение нескольких колонок в одну, для однотипных данных. Они будут храниться в формате JSON.

JSON объёмнее плоской таблицы хотя бы из-за необходимости в хранении ключей.
И, насколько знаю, mysql пока умеет только btree. Что подходит для произвольного поиска от слова никак. Только для заранее известного критерия.

3. Партиционирование.

планирую создать новую, "горячую" таблицу с идентичной структурой.

Слово неплохое упомянуто. Осталось понять зачем всё остальное. Партицировать таблицы умеет штатно даже такая удивительная штука как mysql и очень давно.

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

Типичная ошибка - пытаетесь строить аггрегаты и прочие count(*) поверх миллионов строк налету.

Answer 2

[ThunderCat]

Crash,

У каждой заявки множество специфичных конкретно для нее параметров.

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

1. Индексы, выборочно для полей, по которым чаще всего осуществляется поиск.

скорее для групп полей, по которым осуществляется поиск, выборка, объединение и сортировка. Кроме того - explain, slow log.

2. Объединение нескольких колонок в одну, для однотипных данных. Они будут храниться в формате JSON.

только если по ним не идет поиск, иначе это нифига не оптимизация, а скорее наоборот.

В остальном мысли о разделении на грячий/холодный стек верные. Можно задуматься еще о переносе части горячих строчек в какой-то мемори сторэйж типа редиса.

Answer 3

[Adamos]

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

Answer 4

[Vitaly Karasik]

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

Начните с активации slow query log и его анализа, с помощью pt-query-digest (https://www.percona.com/blog/2018/10/15/identifyin... например.
Кол-во записей в таблице не проблема, как правильно сказал Melkij.

Answer 5

[nrgian]

Какие есть годные практики для работы с большим количеством записей в таблице (миллион+)?

Большим?
Для современных компьютеров (даже слабеньких смартфонов) - это мизерное количество.

Индексы называется решение.

Не должно тормозить на ваших объемах.
Индексы не правильные. Или запросы.

Или вообще проблема не в БД, а уже после нее.

Сделать explain, посмотреть что там неэффективного.

И, важный момент, выборка данных должна фильтроваться средствами СУБД.
Отдаваться из СУБД на дальнейшую обработку в вашей программе должен мизер.

Партиционирование.

Вы это серьезно? На смешном миллионе записей?

Объединение нескольких колонок в одну, для однотипных данных. Они будут храниться в формате JSON.

Вообще никак не повлияет. Если только речь не идет о сотнях колонках, объединенных в одну.

Индексы, выборочно для полей, по которым чаще всего осуществляется поиск.

А вы уже знаете "насколько именно чаще"?
С цифрами?
Замеры провели?