如何优化这个需要容纳mySQL百万行的5+表？

Question

这张表大约有12行。

指数：

idint(11)，唯一，自动增量，主

ATTACHMENTIDvarchar(14)，唯一

MLSNUMBERbigint(20)

POSITIONint(10)

FILETYPEvarchar(32)

我正在使用ON重复键更新子句进行扩展插入。每一个插入都有300-10,000条记录.表中的记录越多，查询所用的时间就越长。

对于0行，查询大约需要5-10秒。在大约300,000行之后，查询需要50秒。

优化这个表的最佳方法是什么？提前感谢！

Answer 1

因为表是InnoDB

• 大容量插入缓冲区(bulk_insert_buffer_size)对您没有好处，因为它只处理MyISAM表的批量插入。
• DISABLE KEYS / ENABLE KEYS只适用于MyISAM。InnoDB在系统表空间ibdata1中处理辅助索引处理。
  • InnoDB将对此发出警告
  • 见我的帖子：https://stackoverflow.com/a/9525780/491757
  • 参见错误报告：http://bugs.mysql.com/bug.php?id=5187

您可能不得不修改InnoDB缓冲区协议，以只处理插入：Mysql从婴儿加载卡在硬盘上等待

除非您计划将MLSNUMBER中的存储值大于2147483647，否则可能需要考虑将MLSNUMBER设置为INT (4个字节)，而不是BIGINT (8个字节)，以节省辅助索引创建时的空间。如果MLSNUMBER的值小于4294967296，也许MLSNUMBER应该是INT UNSIGNED。

Answer 2

当您的表有很多行时，它所花费的时间会越来越长，这是因为必须编写索引。在插入表之前，尝试打开表的索引，然后在插入后重新启用：

ALTER TABLE my_table DISABLE KEYS;

-- Your insert statement

ALTER TABLE my_table ENABLE KEYS;

禁用键只会影响非唯一键的更新，因此ON DUPLICATE UPDATE仍将按预期工作：

换桌子..。禁用键告诉MySQL停止更新非唯一索引。换桌子..。然后，应该使用启用键来重新创建缺少的索引。[src]