mysql:如何更快地插入具有多个索引的表？

Question

我有一个大约有50个索引的分区表，我将插入大约2000万个索引记录每一天。

每次我调用 insert into tableA value (..),(..)... 来插入 2000 条记录。这大约需要五个小时。我无法从文件加载数据。因此，我创建了一个与 tableA 相同的表 talbeB，但 tableB 没有索引。首先，我将数据插入到 tableB 中，然后调用 insert into tableA select * from tableB where mydate = 20181119。第一阶段约40分钟，第二阶段约1小时。

我想知道是否有更快的方法。非常感谢!

Answer 1

• 不要单独为标志或其他低基数列建立索引。优化器不会使用索引。
• UNIQUE 索引比非唯一索引成本更高。你有多少个。
• 在单个 INSERT 语句中一次批量处理 100 行，而不是 2000 行。
• 是否涉及复制？
• 表是 InnoDB 吗？ (MyISAM 还有很多其他问题，以及一些解决方案。)
• 您有多少内存？ innodb_buffer_pool_size 的值是多少？
• “第一阶段”是什么意思？
• 按日期分区？提供实际的分区定义。有好的方法，也有无用的方法。
• 有多少个分区？超过 50 个存在性能问题。告诉我有多少，并提供 SHOW VARIABLES 和 SHOW GLOBAL STATUS；如果您的数量太多，可能有一个解决方法。
• 您会清除“旧”数据吗？这就是分区的原因吗？如果不是，那么分区可能弊大于利。
• 不要使用RAM磁盘；它会影响 RAM 的更好使用。
• 要插入多少行以及插入频率？也就是说，这是每小时 2M 行的负载吗？还是250/秒的连续负载？或者其他一些模式？
• SSD 驱动器？
• 这听起来像一个数据仓库。有很多这样的技术。主要的是将事实表中的内容卸载到汇总表中。执行此操作后，您可能可以删除 50 个索引中的大部分。此外，从汇总表构建“报告”的速度可能是直接从事实表构建“报告”的 10 倍。

请提供SHOW CREATE TABLE以供进一步讨论。您可以混淆列名，但要与索引保持一致并具有实际的数据类型。

德国之声:http://mysql.rjweb.org/doc.php/datawarehouse
汇总表:http://mysql.rjweb.org/doc.php/summarytables
高速摄取(旨在“连续”):http://mysql.rjweb.org/doc.php/staging_table
分区:http://mysql.rjweb.org/doc.php/partitionmaint