У меня есть таблица InnoDB, работающая на MySQL 5.0.45 в CentOS. Что еще хуже, все это работает на виртуальной машине. Существует несколько таблиц, вероятность которых очень легко превысит 2 миллиона записей. Я заметил, что начиная с отметки от 900К до 1М производительность БД начинает резко падать.
У меня есть все силы, необходимые для внесения любых изменений в будущее, чтобы эта штука работала как можно лучше в данных обстоятельствах. Следует ли мне использовать MyIsam? Есть только несколько индексов, и меня больше всего беспокоит получение хорошей производительности записи. Программа, которая записывает данные в таблицы, группирует их в группы по 250 запросов на таблицу и выполняет их для каждой таблицы, чтобы облегчить ситуацию.
Я включил оператор создания таблицы для одной из больших таблиц, и да, это очень широкая таблица, насколько я понимаю. Я попытался сделать столбцы как можно более узкими, но при этом смог надежно разместить входящие данные.
РЕДАКТИРОВАТЬ:
Программа действительно использует транзакции для отката изменений, если что-то попадает в вентилятор во время выполнения, но в основном это просто перекачка данных в стиль пожарного шланга базы данных. За один 8-часовой прогон можно легко поместить 400 тыс. Строк в КАЖДУЮ таблицу, подобную этой. Это одна из 25 таблиц одинакового размера и с одинаковыми индексами. Все они объединены для запроса по LINE и RUN_ID. Производительность чтения - особо не беспокоит. Я стараюсь писать как можно быстрее.
CREATE TABLE IF NOT EXISTS `TMD_INDATA_INVOICE` (
`ID` int(11) NOT NULL auto_increment,
`LINE` int(11) NOT NULL,
`RUN_ID` int(11) NOT NULL,
`INDATA_INVOICE_ALLOCATION_GROUP_NAME` varchar(128) default NULL,
`INDATA_INVOICE_ALLOCATION_GROUP_OWNER` varchar(128) default NULL,
`INDATA_INVOICE_ALLOCATION_NAME` varchar(128) default NULL,
`INDATA_INVOICE_IS_AUDITED` varchar(5) default NULL,
`INDATA_INVOICE_BASIS_PERCENT` varchar(32) default NULL,
`INDATA_INVOICE_COUNTRY_OF_ORIGIN` varchar(64) default NULL,
`INDATA_INVOICE_CUSTOMER_GROUP_NAME` varchar(128) default NULL,
`INDATA_INVOICE_CUSTOMER_GROUP_OWNER` varchar(128) default NULL,
`INDATA_INVOICE_CUSTOMER_NAME` varchar(128) default NULL,
`INDATA_INVOICE_CUSTOMER_TAX_CATEGORY` varchar(128) default NULL,
`INDATA_INVOICE_DELIVERY_TERMS` varchar(128) default NULL,
`INDATA_INVOICE_DEPARTMENT_OF_CONSIGN` varchar(128) default NULL,
`INDATA_INVOICE_DOCUMENT_TYPE` varchar(128) default NULL,
`INDATA_INVOICE_END_USE` varchar(128) default NULL,
`INDATA_INVOICE_END_USER_NAME` varchar(128) default NULL,
`INDATA_INVOICE_FILTER_GROUP_NAME` varchar(128) default NULL,
`INDATA_INVOICE_FILTER_GROUP_OWNER` varchar(128) default NULL,
`INDATA_INVOICE_FISCAL_DATE` varchar(32) default NULL,
`INDATA_INVOICE_INPUT_RECOVERY_TYPE` varchar(50) default NULL,
`INDATA_INVOICE_INVOICE_NUMBER` varchar(128) default NULL,
`INDATA_INVOICE_IS_AUDITING_MESSAGES` varchar(5) default NULL,
`INDATA_INVOICE_IS_AUDIT_UPDATE` varchar(5) default NULL,
`INDATA_INVOICE_IS_BUSINESS_SUPPLY` varchar(5) default NULL,
`INDATA_INVOICE_IS_CREDIT` varchar(5) default NULL,
`INDATA_INVOICE_IS_EXEMPT` varchar(5) default NULL,
`INDATA_INVOICE_IS_NO_TAX` varchar(5) default NULL,
`INDATA_INVOICE_IS_REPORTED` varchar(5) default NULL,
`INDATA_INVOICE_IS_REVERSED` varchar(5) default NULL,
`INDATA_INVOICE_IS_ROUNDING` varchar(5) default NULL,
`INDATA_INVOICE_IS_SIMPLIFICATION` varchar(5) default NULL,
`INDATA_INVOICE_MODE_OF_TRANSPORT` varchar(128) default NULL,
`INDATA_INVOICE_MOVEMENT_DATE` varchar(32) default NULL,
`INDATA_INVOICE_MOVEMENT_TYPE` varchar(128) default NULL,
`INDATA_INVOICE_NATURE_OF_TRANSACTION_CODE` varchar(128) default NULL,
`INDATA_INVOICE_OVERRIDE_AMOUNT` varchar(128) default NULL,
`INDATA_INVOICE_OVERRIDE_RATE` varchar(32) default NULL,
`INDATA_INVOICE_PORT_OF_ENTRY` varchar(128) default NULL,
`INDATA_INVOICE_PORT_OF_LOADING` varchar(128) default NULL,
`INDATA_INVOICE_PRODUCT_MAPPING_GROUP_NAME` varchar(128) default NULL,
`INDATA_INVOICE_PRODUCT_MAPPING_GROUP_OWNER` varchar(128) default NULL,
`INDATA_INVOICE_REGIME` varchar(128) default NULL,
`INDATA_INVOICE_SUPPLY_EXEMPT_PERCENT` varchar(32) default NULL,
`INDATA_INVOICE_SUPPLY_TYPE` varchar(128) default NULL,
`INDATA_INVOICE_TITLE_TRANSFER_LOCATION` varchar(128) default NULL,
`INDATA_INVOICE_VENDOR_NAME` varchar(128) default NULL,
`INDATA_INVOICE_VENDOR_NUMBER` varchar(128) default NULL,
`INDATA_INVOICE_VENDOR_TAX` varchar(128) default NULL,
`INDATA_INVOICE_VERSION` varchar(5) default NULL,
`INDATA_INVOICE_CALCULATION_DIRECTION` varchar(5) default NULL,
`INDATA_INVOICE_CALLING_SYSTEM_NUMBER` varchar(128) default NULL,
`INDATA_INVOICE_COMPANY_NAME` varchar(128) default NULL,
`INDATA_INVOICE_COMPANY_ROLE` varchar(20) default NULL,
`INDATA_INVOICE_CUSTOMER_NUMBER` varchar(128) default NULL,
`INDATA_INVOICE_CURRENCY_CODE` varchar(32) default NULL,
`INDATA_INVOICE_EXTERNAL_COMPANY_ID` varchar(128) default NULL,
`INDATA_INVOICE_HOST_SYSTEM` varchar(128) default NULL,
`INDATA_INVOICE_INVOICE_DATE` varchar(32) default NULL,
`INDATA_INVOICE_POINT_OF_TITLE_TRANSFER` varchar(32) default NULL,
`INDATA_INVOICE_REGISTRATIONS_BUYER_ROLE` varchar(32) default NULL,
`INDATA_INVOICE_REGISTRATIONS_MIDDLEMAN_ROLE` varchar(32) default NULL,
`INDATA_INVOICE_REGISTRATIONS_SELLER_ROLE` varchar(32) default NULL,
`INDATA_INVOICE_VAT_GROUP_REGISTRATION` varchar(32) default NULL,
`INDATA_INVOICE_TRANSACTION_TYPE` varchar(5) default NULL,
`INDATA_INVOICE_UNIQUE_INVOICE_NUMBER` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE1` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE2` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE3` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE4` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE5` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE6` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE7` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE8` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE9` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE10` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE11` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE12` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE13` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE14` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE15` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE16` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE17` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE18` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE19` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE20` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE21` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE22` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE23` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE24` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE25` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE26` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE27` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE28` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE29` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE30` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE31` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE32` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE33` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE34` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE35` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE36` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE37` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE38` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE39` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE40` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE41` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE42` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE43` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE44` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE45` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE46` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE47` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE48` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE49` varchar(128) default NULL,
`INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE50` varchar(128) default NULL,
`INDATA_INVOICE_ORIGINAL_DOCUMENT_ID` varchar(128) default NULL,
`INDATA_INVOICE_ORIGINAL_DOCUMENT_ITEM` varchar(128) default NULL,
`INDATA_INVOICE_ORIGINAL_DOCUMENT_TYPE` varchar(128) default NULL,
`INDATA_INVOICE_ORIGINAL_INVOICE_DATE` varchar(32) default NULL,
`INDATA_INVOICE_ORIGINAL_INVOICE_NUMBER` varchar(128) default NULL,
`INDATA_INVOICE_ORIGINAL_MOVEMENT_DATE` varchar(32) default NULL,
PRIMARY KEY (`ID`),
KEY `RUN_ID` USING BTREE (`RUN_ID`),
KEY `LINE` (`LINE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4011 ;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `TMD_INDATA_INVOICE`
--
ALTER TABLE `TMD_INDATA_INVOICE`
ADD CONSTRAINT `TMD_INDATA_INVOICE_ibfk_1` FOREIGN KEY (`RUN_ID`) REFERENCES `RunHistory` (`id`) ON DELETE CASCADE;
Из того, что я вижу, таблица кажется довольно самодостаточной (т.е. вам не нужно выполнять какие-либо LOJ для извлечения нормализованных данных), поэтому MyISAM определенно может положительно повлиять на скорость доступа.
Во-вторых, что наиболее важно, есть ли у вас правильные индексы для ваших запросов? 2 миллиона строк - это несколько, но это не совсем так который много. Вам нужно внимательно пройти через все свои SELECT запросы и убедитесь, что у вас есть соответствующий индекс для каждого из них. Это займет немного места на диске, но компромисс - невероятно быстрое время запроса.
В-третьих, и это просто личное предпочтение и не имеет отношения к вашей конкретной проблеме, я не думаю, но NDATA_INVOICE_USER_ELEMENT_ATTRIBUTE1 к NDATA_INVOICE_USER_ELEMENT_ATTRIBUTE50 - это может быть много умнее, переместив их в таблицу под названием DATA_INVOICE_USER_ELEMENT_ATTRIBUTES с ПК INVID,ATTRIBUTEID и храните их там вертикально, и вы сразу же сэкономите 6,25 КБ пространства на строку.
убедитесь, что хотя бы ваши индексы умещаются в памяти. устанавливать innodb_buffer_pool_size достаточно большой. если вам нужны транзакции или у вас много одновременного доступа на запись - придерживайтесь innodb.
если он в основном только для чтения и небольшие обновления - myisam perheps + настроил для него выделение памяти.
пытаться mysqltuner.pl за общие советы и погрузитесь в mysqlperformanceblog для получения более подробной информации.
2млн строк - это немного. Может быть, слишком много для вашего размера виртуальной машины?
Вам не следует беспокоиться о количестве строк столько, сколько о размере вашего набора данных. По мере увеличения размера набора данных он не помещается в буферный пул и начинает считывать данные с диска.
Вот мой ответ о улучшение производительности вставки / обновления MySQL / InnoDB