MySQL оптимизирует скорость INSERT замедление из-за индексов

Размер таблицы замедляет вставку индексов по логарифму N, предполагая индексы B-дерева.

Означает ли это, что для вставки каждой новой строки скорость вставки будет замедляться в лог файле N, где N, я предполагаю, что это число строк? даже если я вставляю все строки только в одном запросе? то есть:

INSERT INTO mytable VALUES (1,1,1), (2,2,2),  (3,3,3), .... ,(n,n,n)

Где n ~ 70 000

В настоящее время я имею ~ 1.47 миллиона строк в таблице со следующей структурой:

CREATE TABLE mytable (
   `id` INT,
   `value` MEDIUMINT(5),
   `date` DATE,
   PRIMARY_KEY(`id`,`date`)
) ENGINE = InnoDB

Когда я вставляю вышеуказанный способ в транзакцию, время фиксации составляет ~ 275 секунд. Как я могу это оптимизировать, так как новые данные должны быть добавлены каждый день, а время вставки просто будет замедляться.

Кроме того, есть ли что-нибудь помимо запросов, которые могут помочь? возможно, некоторые настройки конфигурации?

Возможный метод 1 - Удаление индексов

Я читал, что удаление индексов непосредственно перед вставкой может помочь вставить скорость. А после вставок я добавляю индекс снова. Но здесь единственным индексом является первичный ключ, и падение его на мой взгляд не поможет. Кроме того, в то время как первичный ключ отбрасывается, все выбранные запросы будут калечить медленно.

~~Я не знаю никаких других возможных методов.~~

Изменить: Вот несколько тестов по вставке ~ 60 000 строк в таблицу с ~ 1.47 мил строк:

Использование простого запроса, описанного выше: 146 секунд

Использование MySQL LOAD DATA infile: 145 секунд

Используя MySQL LOAD DATA, infile и splitting файлы csv, как было предложено Дэвидом Джаши в его ответе: 136 секунд для 60 файлов с 1000 строк каждый, 136 секунд для 6 файлов с 10 000 строк каждый

Удаление и повторное добавление первичного ключа: удаление ключа заняло 11 секунд, 0,8 секунды для вставки данных, но 153 секунды для повторного добавления первичного ключа, всего за 165 секунд

Ответ 1

Если вам нужны быстрые вставки, первое, что вам нужно, это правильное оборудование. Это предполагает достаточный объем оперативной памяти, SSD вместо механических дисков и довольно мощный процессор.

Поскольку вы используете InnoDB, вы хотите оптимизировать его, поскольку конфигурация по умолчанию предназначена для медленных и старых машин.

Здесь рассказывается о настройке InnoDB.

После этого вам нужно знать одно - и то, как базы данных делают свои вещи изнутри, как работают жесткие диски и так далее. Я упрощу механизм в следующем описании:

Сделка - это MySQL, ожидающий жесткого диска, чтобы подтвердить, что он написал данные. То, что транзакции медленны на механических приводах, они могут выполнять 200-400 операций ввода-вывода в секунду. В переводе это означает, что вы можете получать 200 запросов в секунду с помощью InnoDB на механическом диске. Естественно, это упрощенное объяснение, просто чтобы определить, что происходит, это не полный механизм транзакции.

Так как запрос, особенно тот, который соответствует размеру вашей таблицы, относительно мал по байтам - вы эффективно тратите драгоценные IOPS на один запрос.

Если вы обертываете несколько запросов (100 или 200 или более, нет точного номера, вы должны протестировать) в одной транзакции, а затем фиксируете его - вы мгновенно достигнете большего количества записей в секунду.

Ребята из Percona достигают 15k вставку в секунду на относительно дешевом оборудовании. Даже 5k вставляет вторую, это неплохо. Таблица, такая как ваша, невелика, я провел тесты на аналогичной таблице (еще 3 столбца), и мне удалось получить до 1 миллиарда записей без заметных проблем, используя 16-гигабайтный компьютер с SSD-накопителем на 240 ГБ (1 диск, без RAID, используется для целей тестирования).

TL; DR: - перейдите по ссылке выше, настройте свой сервер, получите SSD, оберните несколько вложений в 1 транзакции и получите прибыль. И не отключайте индексирование, а затем, он не применяется всегда, потому что в какой-то момент вы потратите время обработки и времени ввода-вывода для их создания.

Ответ 2

Снижение индекса обязательно поможет. Также рассмотрите возможность использования LOAD DATA. Вы можете найти сравнение и тесты здесь

Кроме того, при построении PRIMARY KEY используйте поля, которые сначала входят в вашу таблицу, последовательно, т.е. переключают места второго и третьего полей в структуре.

Ответ 3

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

Ответ 4

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

В противном случае подумайте о создании компромиссов со структурой, которая улучшит скорость вставки. В качестве альтернативы, подумайте о том, как вы можете счастливо жить с 3-минутной вставкой.