Как обрабатывать большую таблицу в MySQL?

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

CREATE TABLE `item_property` (
    `property_id` int(11) NOT NULL,
    `item_id` int(11) NOT NULL,
    `value` double NOT NULL,
    PRIMARY KEY  (`property_id`,`item_id`),
    KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Эта база данных имеет две цели: сохранение (которое имеет первый приоритет и должно быть очень быстрым, я хотел бы выполнить несколько вложений (сотни) за несколько секунд), извлечение данных (выбирает с помощью item_id и property_id) (это второй приоритет, он может быть медленнее, но не слишком много, потому что это испортит мое использование БД).

В настоящее время эта таблица содержит 1,6 миллиарда записей, а простой счет может занять до 2 минут... Вставка не является достаточно быстрой, чтобы ее можно было использовать.

Я использую Zend_Db для доступа к моим данным и действительно буду счастлив, если вы не предложите мне разработать какой-либо элемент на стороне PHP.

Ответ 1

Если по каким-либо причинам вы не можете использовать решения, использующие разные системы управления базами данных или разбивая их на кластер, по-прежнему есть три основные возможности: радикально повысить производительность (и они работают в сочетании с кластерами тоже, конечно):

Настройка механизма хранения MyISAM
Используйте "LOAD DATA INFILE filename INTO TABLE tablename"
Разделите свои данные на несколько таблиц.

Что это. Прочтите остальные, только если вас интересуют детали:)

По-прежнему читаешь? Итак, вот так: MyISAM - краеугольный камень, так как он самый быстрый движок. Вместо того, чтобы вставлять строки данных с помощью регулярных SQL-операторов, вы должны выставлять их в файл и вставлять этот файл через регулярные промежутки времени (так часто, как вам нужно но, как редко, ваше приложение может быть лучше). Таким образом, вы можете вставить порядка миллиона строк в минуту.

Следующее, что ограничит вас, это ваши ключи/индексы. Когда эти can not подходят в вашей памяти (потому что они просто большие), вы столкнетесь с огромным замедлением как в вставках, так и в запросах. Вот почему вы разделили данные на несколько таблиц, все с той же схемой. Каждая таблица должна быть как можно больше, без заполнения вашей памяти при загрузке по одному. Точный размер зависит от вашей машины и индексов, конечно, но должен быть где-то между 5 и 50 миллионами строк/таблиц. Вы найдете это, если просто измерить время, затраченное на вставку огромного количества строк за другим, ища момент, когда он значительно замедляется. Когда вы знаете предел, создайте новую таблицу "на лету" каждый раз, когда ваша последняя таблица приблизится к этому пределу.

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

Если вам нужна еще больше настройки, перейдите к partitioning, как было предложено Eineki и oedo.

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

Ответ 2

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

Ответ 3

вау, это довольно большая таблица:)

если вам нужно быстро хранить, вы можете загружать свои вставки и вставлять их с помощью одного множественного оператора INSERT. однако это, безусловно, потребует дополнительного клиентского (php) кода, извините!

INSERT INTO `table` (`col1`, `col2`) VALUES (1, 2), (3, 4), (5, 6)...

также отключить любые индексы, которые вам НЕ НУЖНЫ, поскольку индексы замедляют команды вставки.

В качестве альтернативы вы можете посмотреть разбиение таблицы: linky

Ответ 4

Посмотрите на memcache, чтобы узнать, где он может быть применен. Также посмотрите на горизонтальное разбиение, чтобы уменьшить размеры таблиц/индексов.

Ответ 5

Во-первых: один стол с 1,6 млрд. записей кажется настолько немного большим. Я работаю над некоторыми довольно тяжелыми системами загрузки, где даже столы регистрации, которые отслеживают все действия, не получают этого за многие годы. Поэтому, если возможно, подумайте, если вы можете найти более оптимальный метод хранения. Не могу дать больше советов, так как я не знаю вашу структуру БД, но я уверен, что будет достаточно места для оптимизации. 1,6 млрд. Записей просто слишком велико.

Немного о производительности:

Если вам не нужны проверки ссылочной целостности, что маловероятно, вы можете переключиться на механизм хранения MyISAM. Это немного быстрее, но не имеет целостности ckecks и транзакций.

Для чего-нибудь еще потребуется больше информации.

Ответ 6

Вы рассмотрели возможность partitioning таблицы?

Ответ 7

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