@antiiii
ID char(32)
Date int
....
primary key (ID, Date)
пара неуникальных ключей
и партиции по Date по годамID — 32-символьный уникальный хеш.
Avg row length показывает 369.
На сейчас в таблице ~90 млн строк.
Данные из неё не удаляются, только вставляются новые.
Вставка в таблицу иногда занимает дикое количество времени (в среднем ~секунда на строку, на некоторых строках прыгает до 5-10 секунд) и иногда падает с Lock wait timeout exceeded.
Насколько я понимаю, происходит это из-за того, что для вставки нового ID движку нужно перестроить индекс, что на таких объемах накладно.
Я попробовал на тестовой машине — добавил суррогатный auto increment, пару (ID, Date) сделал просто unique — это ускорило вставку на ~40%, и самый медленный запрос упал с 5-10 секунд до 0.8-1 сек. На боевой машине и боевой базе протестировать эти изменения, понятно, не могу.
Собственно, спрашиваю только чтобы уточнить наверняка: правильно ли я решаю эту проблему и действительно ли суррогатный ключ в таких ситуациях даёт прирост скорости?
Решения вопроса 2
@index0h
Запросы делались по одному, без оборачивания в транзакцию (насколько я понимаю, если запрос один — то и транзакция бесполезна, независимо от кол-ва строк?).
Не совсем. При вставке без явной транзакции, во внутрянке она таки создаетется. С коммитом транзакции происходит перерассчет индексов. Т.е. вставляя много строк отдельными запросами вы будете пересчитывать индексы каждый раз. Обвернув все в транзакцию, или вставляя пачкой индексы будут пересчитываться реже, что ускорит выполнение.
Кроме этого, batch-запросы с auto increment имеют намного меньшее max время вставки: 1,5 сек против 11,4 (да, 11 секунд на вставку 20 строк).
Верно, это вполне ожидаемый результат.
правильно ли я решаю эту проблему и действительно ли суррогатный ключ в таких ситуациях даёт прирост скорости?
Не совсем. Индекс не ускоряет вставку, что обычный, что комплексный, он ускоряет поиск. Чем меньше индекс — тем быстрее он рассчитается.
Очень похоже на то, что у вас проблема несколько иного характера. Индекс хранится в ОЗУ, тормоза на вставке вероятно у вас за счет того, что индекс не влазит в буфер движка. Это значит, что при вставке БД занимается вытеснением из буфера одних индексов другими, а это уже работа с файловой системой, что очень медленно.
В таких ситуациях стоит двигаться в сторону уменьшения размера самого индекса.
4
комментария
Ответы на вопрос 2
@neol
Indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.
If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.
https://dev.mysql.com/doc/refman/5.7/en/innodb-ind…
В вольном переводе каждый из ваших вторичных индексов будет содержать в себе значение из primary key (PK) и чем длиннее PK, тем больше все индексы в таблице.
Вполне вероятно, что при таких раскладах PK даже из 8 байтового bigint сильно выиграет у 36 байтового char+int (а то и 102, если с дуру сделали ID utf8mb4). Как минимум по занимаемому месту.
@rPman
Например сделай дополнительную таблицу, в которую будешь набирать изменения до определенного количества (или лучше по времени, раз в сутки/час/…) и потом одним запросом переносить, пакетом перестройка индекса идет быстрее.
Логично что и поиск/чтение данных нужно производить сразу из двух этих таблиц, т.е. замедление но незначительное. Если допускать дублирование данных в таблицах, то перенос будет простым и не сильно нагружающим базу (не надо лочить таблицы)
p.s. решать задачу нужно исходя из того как данные читаешь и как в них делаешь поиск. Именно от этого зависит каким образом можно реорганизовать хранение или даже отказаться от mysql
к примеру один из способов (а у вас он прямо напрашивается — маленький пакет данных и временные ряды) — если чтение данных такое же последовательное как и запись (например запросить данные на интервале) то можно при записи упаковывать данные в одну запись, по какому-то временному критерию (например данные за сутки), сериализовав их, само собой текущий интервал данных хранить классическим способом, и по заполнению, переносить.