Как ускорить вставку в большое вертикальное хранилище с индексами

@Jack Famous · Регистрация: 10.05.2021

Студворк — интернет-сервис помощи студентам

Приветствую!

Есть огромная таблица: (2 млрд и растёт пакетами по 1-100 млн в день) записей, 6 (Int/BigInt/DateTime) полей, 3 индекса + Primary Key по 1му полю BigInt.
Вставка происходит очень долго (наверное, из-за перестройки индексов). Можно ли как-то ускорить этот процесс?
Например, "перенеся" перестройку индексов на другое время (не на момент после вставки).

Или только разделять таблицу на несколько?…

@uaggster · 01.08.2024, 09:51

1. Разделите таблицу на секции.
2. Заливайте данные в отдельную таблицу, по структуре соответствующей секции.
3. Для ускорения вставки - эта отдельная таблица может не содержать индексов вообще (куча). Индексы можно построить потом.
4. Делайте switch partition - эту заполненную таблицу в пустую секцию основной таблицы.

@katamoto · 01.08.2024, 09:51

Планы бы реальные посмотреть. И зачем на ней индексы перестраивать?

@Jack Famous · 01.08.2024, 09:56 **[ТС]**

Сообщение от uaggster

Разделите таблицу на секции

спасибо, но пока не очень понимаю, как это правильно сделать. Перед вставкой в "боевую" таблицу данные и так находятся в куче — готовые к вставке.
Блоки данных различаются только Int-числом в одном из полей — параметр/уровень/критерий строки.

Сообщение от katamoto

Планы бы реальные посмотреть.

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

Сообщение от katamoto

И зачем на ней индексы перестраивать?

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

@invm · 01.08.2024, 10:02

Сообщение от Jack Famous

растёт пакетами по 1-100 млн в день

Каким образом пакет добавляется?

@Jack Famous · 01.08.2024, 10:05 **[ТС]**

invm, приходит документ в XML. Документ разбирается в плоскую таблицу. Таблица насыщается параметрами из других таблиц. Плоская таблица конвертируется в вертикальную структуру. Происходит вставка новых данных в вертикальное хранилище.

@invm · 01.08.2024, 10:05

Секционирование сломает PK и запросы на основе PK

@Jack Famous · 01.08.2024, 10:06 **[ТС]**

Сообщение от invm

Секционирование сломает PK и запросы на основе PK

это проблема, т.к. Primary Key активно используется.

@invm · 01.08.2024, 10:06

Сообщение от Jack Famous

приходит документ в XML

см. XML bulk load

Сообщение от Jack Famous

Происходит вставка новых данных в вертикальное хранилище.

Построчно?

@Jack Famous · 01.08.2024, 10:13 **[ТС]**

Сообщение от invm

XML bulk load

ок, гляну, спасибо

Сообщение от invm

Построчно?

да — был штатный Insert Or Update. Пробовали Merge, но он был заметно медленнее.

UPD:

Сообщение от Jack Famous

Например, "перенеся" перестройку индексов на другое время (не на момент после вставки).

нашёл материалы по отключению индексов (1, 2)

@invm · 01.08.2024, 10:15

Сообщение от Jack Famous

да — был штатный Insert Or Update

В транзакции или autocommit?

Добавлено через 2 минуты

Сообщение от Jack Famous

нашёл материалы по отключению индексов (1, 2)

Обратное включение потребует полного перестроения (пересоздания) индексов

@Jack Famous · 01.08.2024, 10:16 **[ТС]**

Сообщение от invm

В транзакции или autocommit?

код был не мой. Не помню, но, кажется, транзакции не было.

Сообщение от invm

Обратное включение потребует полного перестроения (пересоздания) индексов

да — я это понимаю. Возможно, новое создание может быть быстрее "актуализации" …

@invm · 01.08.2024, 10:31

Сообщение от Jack Famous

транзакции не было.

Это и есть причина проблемы. Пачку insert or update нужно оборачивать в транзакцию

Сообщение от Jack Famous

Возможно, новое создание может быть быстрее "актуализации" …

Не может

@Jack Famous · 01.08.2024, 10:38 **[ТС]**

Сообщение от invm

Пачку insert or update нужно оборачивать в транзакцию

учту и проверю в следующий раз

Сообщение от invm

Не может

спасибо — учту. Большое спасибо за ценные советы!

Вообще, насколько работа с N таблицами — для каждого из N уровней/параметров/типов данных строк предпочтительнее работы с одной огромной (по количеству строк) таблицей, в которой на одно поле больше (N номер уровня), чем в этих N новых отдельных таблицах?

Пример 2ух структур

Как ускорить вставку в большое вертикальное хранилище с индексами

Каждая из таблиц в несколько раз меньше единой структуры. Для 1го уровня данных вообще всего около 10 тыс строк. Таблицы однообразны, с 2-3мя индексами помимо PK. Соединения между ними осуществляются преимущественно по связке "MasterPK—SlaveParentID".
Вроде, скорость очень неплохая получается и табличные функции очень хорошо работают. Вставка происходит намного быстрее, чем в одно большое хранилище.
Что думаете?

@uaggster · 01.08.2024, 13:23

Сообщение от Jack Famous

Вообще, насколько работа с N таблицами — для каждого из N уровней/параметров/типов данных строк предпочтительнее работы с одной огромной (по количеству строк) таблицей, в которой на одно поле больше (N номер уровня), чем в этих N новых отдельных таблицах?

Тут нужно плясать "от использования".
Если вы строите DWH - то лучше построить ее в виде звездочки/снежинки.
https://www.guru99.com/ru/star... using.html
https://habr.com/ru/articles/441538/

@Jack Famous · 01.08.2024, 14:56 **[ТС]**

uaggster, спасибо! Сейчас поизучаю …

UPD. Описание текущей структуры

Схема у нас вырисовается похожая на снежинку, но ещё более атомарная — как видно из примера, на каждый признак создаётся отдельная таблица, связанная напрямую только только с одной другой таблицей из структуры (кроме 1го корневого узла, который ни с кем не связан). На самом деле, может быть связана с 2мя, т.к. в поле Val часто хранится ID текста из таблицы текстов (общей для всех других таблиц этой структуры). Также, там (в поле Val) может хранится ссылка на ещё какую-либо таблицу вместо таблицы текстов или реального значения (если оно может быть представлено в виде BigInt).

Нельзя сказать, что мы строим DWH — я почитал про это и у нас совсем не то. В 1ю очередь, нет никакого "хранилища, отличающегося от БД" — все данные хранятся в БД. Есть несколько серверов из которых поступает информация. Эта информация ETL'ится в общую/основную БД на одном из серверов и потом частично распределяется обратно по серверам — в соответствии с потребностями. Типа актуализируется.

Мой вопрос связан как раз с организацией этого основного хранилища. Данных много и прирастают быстро. Основная операция — Insert. Реже — Update и ещё реже — Delete. По совету от invm обращу внимание, чтобы эти операции проводились внутри транзакции.

Плюсом одной (не считая вспомогательной таблицы текстов и ей подобных — они остались) огромной таблицы было то, что она одна (легко контролировать и распределять данные по серверам, где также будет только одна такая таблица) и то, что её структура позволяла создавать практически неограниченное количество уровне вложенности без изменения структуры таблицы. Самосоединения (как основной метод получения данных из неё) выполнялись достаточно быстро.

Основным минусом было время Insert/Update/Delete.

На новой структуре всё стало заметно быстрее. В том числе, соединения.
Индексы на каждой таблицы стали сильно меньше и даже меньше в сумме по всем таблицам.
Стало возможным изменить тип поля Val с BigInt на Bit/TinyInt/SmallInt/Int/DateTime/Float/Decimal — в зависимости от данных каждой таблицы.
PK во многих таблицах также заменили с BigInt на Int.

Но, конечно, контролировать (и актуализировать на других серверах) этот пакет таблиц стало сильно сложнее. Ну ничего

Пока возвращаться к "огромной вертикалке" смысла не вижу …
Если будут идеи/замечания/предложения — буду рад обсудить.

@uaggster · 01.08.2024, 15:00

Сообщение от Jack Famous

Если будут идеи/замечания/предложения — буду рад обсудить.

Лучше нарисовать. Т.к. слабо понятно (мне).

@Jack Famous · 01.08.2024, 15:07 **[ТС]**

Сообщение от uaggster

Лучше нарисовать

так тут же скрин (под спойлером)

@uaggster · 01.08.2024, 15:25

Гм... А у меня вопрос тогда.
А каково основное назначение БД?
Если, например, это какая-то аналитическая БД, основное назначение которой - быстро (ну, + -, иногда неделя - это тоже быстро) считать некие отчеты - то вы, на мой взгляд, совершенно зря отошли от денормализованной структуры.
На больших диапазонах - соединения вообще, в принципе, плохо работают.
Если у вас слева миллиард записей, справа - десяток миллионов, то, как ты не старайся - быстро их не соединишь.
Тут простая то фильтрация по каким-то индексированным полям и то со скрипом работает.
Вы так для чего сделали?
(без подвоха вопрос).

@Jack Famous · 01.08.2024, 16:54 **[ТС]**

Сообщение от uaggster

А каково основное назначение БД?

ну дык, хранить данные и представлять их в различных видах. Не знаю, как объяснить.

Сообщение от uaggster

зря отошли от денормализованной структуры

вертикалка (одна огромная таблица) заметно медленнее работала в получении результата запросов. По причине того, видимо, что индексов хоть и всего 3, но это огромные индексы. Запросы сильно не изменились — просто, если в вертикалке это самосоединения, то сейчас это соединения с другими таблицами.

Сообщение от uaggster

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

обычно, выборки куда меньше — миллионы и менее записей. Плюс, иду от меньшей таблицы к большей. Да ещё и с фильтрацией, и не на одном уровне.

Сообщение от uaggster

Вы так для чего сделали?

для ускорения получения данных и операций Insert/Update/Delete. Про получение описал выше, а IUD быстрее за счёт хотя бы индексов (опять же). Они намного меньше, чем в одной огромной таблице и перестраиваются быстрее. Возможно, и в логе дело — он тоже меньше должен стать.

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Обработчик клика мыши в браузере ПК и касания экрана в браузере на мобильном устройстве 8Observer8 02.02.2026 Содержание блога Для начала пошагово создадим рабочий пример для подготовки к экспериментам в браузере ПК и в браузере мобильного устройства. Потом напишем обработчик клика мыши и обработчик. . .	Философия технологии iceja 01.02.2026 На мой взгляд у человека в технических проектах остается роль генерального директора. Все остальное нейронки делают уже лучше человека. Они не могут нести предпринимательские риски, не могут. . .	SDL3 для Web (WebAssembly): Вывод текста со шрифтом TTF с помощью SDL3_ttf 8Observer8 01.02.2026 Содержание блога В этой пошаговой инструкции создадим с нуля веб-приложение, которое выводит текст в окне браузера. Запустим на Android на локальном сервере. Загрузим Release на бесплатный. . .	SDL3 для Web (WebAssembly): Сборка C/C++ проекта из консоли 8Observer8 30.01.2026 Содержание блога Если вы откроете примеры для начинающих на официальном репозитории SDL3 в папке: examples, то вы увидите, что все примеры используют следующие четыре обязательные функции, а. . .
SDL3 для Web (WebAssembly): Установка Emscripten SDK (emsdk) и CMake для сборки C и C++ приложений в Wasm 8Observer8 30.01.2026 Содержание блога Для того чтобы скачать Emscripten SDK (emsdk) необходимо сначало скачать и уставить Git: Install for Windows. Следуйте стандартной процедуре установки Git через установщик. . . .	SDL3 для Android: Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 29.01.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами. Версия v3 была полностью переписана на Си, в. . .	Инструменты COM: Сохранение данный из VARIANT в файл и загрузка из файла в VARIANT bedvit 28.01.2026 Сохранение базовых типов COM и массивов (одномерных или двухмерных) любой вложенности (деревья) в файл, с возможностью выбора алгоритмов сжатия и шифрования. Часть библиотеки BedvitCOM Использованы. . .	SDL3 для Android: Загрузка PNG с альфа-каналом с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 28.01.2026 Содержание блога SDL3 имеет собственные средства для загрузки и отображения PNG-файлов с альфа-каналом и базовой работы с ними. В этой инструкции используется функция SDL_LoadPNG(), которая. . .

@Jack Famous 932 / 365 / 43 Регистрация: 10.05.2021 Сообщений: 1,564 Записей в блоге: 10

	Как ускорить вставку в большое вертикальное хранилище с индексами 01.08.2024, 09:26. Показов 1122. Ответов 23 Метки fast, insert, speedup, vertical (Все метки) Приветствую! Есть огромная таблица: (2 млрд и растёт пакетами по 1-100 млн в день) записей, 6 (Int/BigInt/DateTime) полей, 3 индекса + Primary Key по 1му полю BigInt. Вставка происходит очень долго (наверное, из-за перестройки индексов). Можно ли как-то ускорить этот процесс? Например, "перенеся" перестройку индексов на другое время (не на момент после вставки). Или только разделять таблицу на несколько?… 0

@uaggster 1304 / 358 / 97 Регистрация: 14.10.2022 Сообщений: 1,087
	01.08.2024, 09:51
	1. Разделите таблицу на секции. 2. Заливайте данные в отдельную таблицу, по структуре соответствующей секции. 3. Для ускорения вставки - эта отдельная таблица может не содержать индексов вообще (куча). Индексы можно построить потом. 4. Делайте switch partition - эту заполненную таблицу в пустую секцию основной таблицы. 1

@katamoto 139 / 105 / 36 Регистрация: 27.07.2022 Сообщений: 357
	01.08.2024, 09:51
	Планы бы реальные посмотреть. И зачем на ней индексы перестраивать? 0

@Jack Famous 932 / 365 / 43 Регистрация: 10.05.2021 Сообщений: 1,564 Записей в блоге: 10
	01.08.2024, 10:05 [ТС]
	invm, приходит документ в XML. Документ разбирается в плоскую таблицу. Таблица насыщается параметрами из других таблиц. Плоская таблица конвертируется в вертикальную структуру. Происходит вставка новых данных в вертикальное хранилище. 0

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	01.08.2024, 10:05
	Секционирование сломает PK и запросы на основе PK 1

@Jack Famous 932 / 365 / 43 Регистрация: 10.05.2021 Сообщений: 1,564 Записей в блоге: 10
	01.08.2024, 14:56 [ТС]
	uaggster, спасибо! Сейчас поизучаю … UPD. Описание текущей структуры Схема у нас вырисовается похожая на снежинку, но ещё более атомарная — как видно из примера, на каждый признак создаётся отдельная таблица, связанная напрямую только только с одной другой таблицей из структуры (кроме 1го корневого узла, который ни с кем не связан). На самом деле, может быть связана с 2мя, т.к. в поле Val часто хранится ID текста из таблицы текстов (общей для всех других таблиц этой структуры). Также, там (в поле Val) может хранится ссылка на ещё какую-либо таблицу вместо таблицы текстов или реального значения (если оно может быть представлено в виде BigInt). Нельзя сказать, что мы строим DWH — я почитал про это и у нас совсем не то. В 1ю очередь, нет никакого "хранилища, отличающегося от БД" — все данные хранятся в БД. Есть несколько серверов из которых поступает информация. Эта информация ETL'ится в общую/основную БД на одном из серверов и потом частично распределяется обратно по серверам — в соответствии с потребностями. Типа актуализируется. Мой вопрос связан как раз с организацией этого основного хранилища. Данных много и прирастают быстро. Основная операция — Insert. Реже — Update и ещё реже — Delete. По совету от invm обращу внимание, чтобы эти операции проводились внутри транзакции. Плюсом одной (не считая вспомогательной таблицы текстов и ей подобных — они остались) огромной таблицы было то, что она одна (легко контролировать и распределять данные по серверам, где также будет только одна такая таблица) и то, что её структура позволяла создавать практически неограниченное количество уровне вложенности без изменения структуры таблицы. Самосоединения (как основной метод получения данных из неё) выполнялись достаточно быстро. Основным минусом было время Insert/Update/Delete. На новой структуре всё стало заметно быстрее. В том числе, соединения. Индексы на каждой таблицы стали сильно меньше и даже меньше в сумме по всем таблицам. Стало возможным изменить тип поля Val с BigInt на Bit/TinyInt/SmallInt/Int/DateTime/Float/Decimal — в зависимости от данных каждой таблицы. PK во многих таблицах также заменили с BigInt на Int. Но, конечно, контролировать (и актуализировать на других серверах) этот пакет таблиц стало сильно сложнее. Ну ничего Пока возвращаться к "огромной вертикалке" смысла не вижу … Если будут идеи/замечания/предложения — буду рад обсудить. 0

@uaggster 1304 / 358 / 97 Регистрация: 14.10.2022 Сообщений: 1,087
	01.08.2024, 15:25
	Гм... А у меня вопрос тогда. А каково основное назначение БД? Если, например, это какая-то аналитическая БД, основное назначение которой - быстро (ну, + -, иногда неделя - это тоже быстро) считать некие отчеты - то вы, на мой взгляд, совершенно зря отошли от денормализованной структуры. На больших диапазонах - соединения вообще, в принципе, плохо работают. Если у вас слева миллиард записей, справа - десяток миллионов, то, как ты не старайся - быстро их не соединишь. Тут простая то фильтрация по каким-то индексированным полям и то со скрипом работает. Вы так для чего сделали? (без подвоха вопрос). 0

Как ускорить вставку в большое вертикальное хранилище с индексами

Решение