我在 CentOS 上的 MySQL 5.0.45 上运行了一个 InnoDB 表。更糟糕的是，它全部在虚拟机中运行。有多个表的记录很容易超过 200 万条。我注意到，从 900K 到 1M 条记录开始，数据库性能开始急剧下降。
我拥有所有必要的权力，可以对未来做出任何和所有改变，以尽可能地保持这个东西在当前情况下的良好运行。我应该使用 MyIsam 吗？只有几个索引，我最担心的是获得良好的写入性能。将数据写入表的程序将它们分批处理，每个表大约有 250 个请求，并按表执行这些请求以帮助解决问题。
我已将创建表语句添加到其中一个较大的表中，是的，这是一个非常宽的表 - 我明白。我已尝试将列尽可能窄，同时仍能可靠地容纳传入的数据。
编辑：
如果在运行过程中出现问题，程序确实会使用事务来回滚更改，但它基本上只是像消防水管一样将数据泵入数据库。一次 8 小时的运行可以轻松地将 400K 行放入每个表中，就像这个一样。这个表是 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;
答案1
据我所见，该表似乎是相当独立的（即您不需要执行任何 LOJ 来提取规范化数据）因此 MyISAM 肯定可以对访问速度产生积极影响。
其次，最重要的是，你的查询是否有正确的索引？200 万行数据是少数，但实际上并非如此那很多。您需要仔细检查所有SELECT查询，并确保每个查询都有适当的索引。这会占用一些磁盘空间，但代价是查询时间极快。
第三，这只是个人偏好，与你的具体问题没有太大关系，但我认为NDATA_INVOICE_USER_ELEMENT_ATTRIBUTE1这NDATA_INVOICE_USER_ELEMENT_ATTRIBUTE50可以设计成很多更智能的方法是将它们移动到一个名为的表中，DATA_INVOICE_USER_ELEMENT_ATTRIBUTES主键为，INVID,ATTRIBUTEID并将它们垂直存储在其中，这样您就立即为每行节省了 6.25kb 的空间。
答案2
确保至少你的索引适合内存。设置innodb_buffer_pool_size足够大。如果您需要事务或者有大量并发写访问 - 坚持使用 innodb。
如果它主要是读取并且很少更新 - myisam perheps + 调整它的内存分配。
尝试mysqltuner.pl获得一些通用建议并深入研究mysql性能博客了解更多详细信息。
答案3
200 万行并不算多。也许对于您的 VM 大小来说太多了？
您不必担心行数，而应担心数据集的大小。随着数据集大小的增加，缓冲池将无法容纳它，因此将开始从磁盘读取数据。
这是我关于的回答提高 MySQL/InnoDB 插入/更新性能