Как обработать большую таблицу в 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) (это - вторая по важности задача, это может быть медленнее, но не слишком много, потому что это разрушило бы мое использование DB).
В настоящее время эта таблица размещает 1,6 миллиарда записей, и простое количество может занять до 2 минут... Вставка не достаточно быстра, чтобы быть применимой.
Я использую Zend_Db для доступа к моим данным и действительно был бы счастлив, если Вы не предлагаете, чтобы я разработал какой-либо элемент стороны PHP.
7 ответов
Если вы по каким-то причинам не можете пойти на решения, использующие различные системы управления базами данных или разбиение на кластеры, есть три основные вещи, которые вы можете сделать для радикального улучшения производительности (и они, конечно, работают в сочетании с кластерами):
- Настройка движка MyISAM-хранилища
- Использование "LOAD DATA INFILE filename INTO TABLE tablename"
- Разделение данных на несколько таблиц
Вот и все. Остальное читайте только если вам интересны подробности :)
Все еще читаете? Хорошо, тогда продолжим: MyISAM - это краеугольный камень, так как это самый быстрый движок на сегодняшний день. Вместо того, чтобы вставлять строки данных с помощью обычных SQL-заявок, вы должны собирать их в файл и вставлять этот файл через регулярные промежутки времени (так часто, как вам нужно, но так редко, как позволяет ваше приложение). Таким образом, вы можете вставлять порядка миллиона строк в минуту.
Следующее, что будет ограничивать вас, это ваши ключи/индексы. Когда они не смогут поместиться в памяти (потому что они просто слишком большие), вы столкнетесь с огромным замедлением как вставки, так и запросов. Вот почему вы разделяете данные на несколько таблиц, имеющих одну и ту же схему. Каждая таблица должна быть как можно больше и не заполнять память при загрузке по очереди. Точный размер зависит от вашей машины и индексов, конечно, но должен быть где-то между 5 и 50 миллионами строк/таблицу. Это можно определить, если просто измерить время, затрачиваемое на вставку огромной кучи строк друг за другом, и искать момент, когда это значительно замедляется. Когда вы узнаете предел, создавайте новую таблицу на лету каждый раз, когда ваша последняя таблица приближается к этому пределу.
Следствием мультитабличного решения является то, что вам придется запрашивать все ваши таблицы, а не только одну, когда вам нужны какие-то данные, что немного замедлит ваши запросы (но не слишком сильно, если у вас "всего" миллиард или около того строк). Очевидно, что и здесь можно провести оптимизацию. Если есть что-то фундаментальное, что можно использовать для разделения данных (например, дата, клиент или что-то еще), вы можете разделить их по разным таблицам с помощью какого-то структурированного шаблона, который позволит вам узнать, где находятся определенные типы данных, даже без запросов к таблицам. Используйте эти знания для запроса только тех таблиц, которые могут содержать запрашиваемые данные и т.д.
Если вам нужна еще большая настройка, используйте разделение, как советуют Eineki и oedo.
Также, чтобы вы знали, что все это не дикие спекуляции: В данный момент я провожу несколько подобных тестов на масштабируемость на наших собственных данных, и этот подход творит с нами чудеса. Нам удается вставлять десятки миллионов строк каждый день, а запросы занимают ~100 мс.
Во-первых: одна таблица с 1,6 миллиардами записей кажется слишком большой. Я работаю над некоторыми довольно тяжелонагруженными системами, где даже таблицы журналов, которые отслеживают все действия, не становятся такими большими с годами. Так что по возможности подумайте, сможете ли вы найти более оптимальный способ хранения. Не могу дать больше советов, так как я не знаю структуру вашей БД, но я уверен, что будет много места для оптимизации. 1,6 миллиарда записей - это слишком много.
Несколько моментов по производительности:
Если вам не нужны проверки ссылочной целостности, что маловероятно, вы можете переключиться на механизм хранения MyISAM. Это немного быстрее, но не хватает проверок целостности и транзакций.
Для всего остального потребуется дополнительная информация.
Загляните в кэш памяти, чтобы увидеть, где это можно применить. Также обратите внимание на горизонтальное разбиение, чтобы размеры / индексы таблиц были меньше.
Важно помнить, что установка MySQL по умолчанию не предназначена для такой тяжелой работы. Убедитесь, что вы настроили его для своей рабочей нагрузки.
Во-первых, не используйте InnoDb, поскольку вам, похоже, не нужна его основная функция по сравнению с MyISAM (блокировка, транзакция и т. Д.). Так что используйте MyISAM, это уже будет иметь значение. Затем, если это все еще недостаточно быстро, займитесь индексацией, но вы уже должны увидеть радикальную разницу.
вау, это довольно большая таблица :)
если вам нужно быстрое сохранение, вы можете группировать свои вставки и вставлять их с помощью одного множественного оператора INSERT. однако это определенно потребует дополнительного кода на стороне клиента (php), извините!
INSERT INTO `table` (`col1`, `col2`) VALUES (1, 2), (3, 4), (5, 6)...
также отключите любые индексы, которые вам не НУЖНЫ, поскольку индексы замедляют выполнение команд вставки.
в качестве альтернативы вы можете посмотреть на разделение вашей таблицы: linky