具有多ID引用字段的高事务表的MySQL索引计划

Question

场景:包含20个ID字段的高事务表，这些字段链接到具有这两个字段的其他表

• 写入:大量插入、更新和删除(通过一个进程/连接)
• 读取:通过变量选择条件运行的数十个查询，这些条件可能包括从1到所有20个ID字段(通过许多进程/连接)。

如果您可以通过减少索引的数量来加快加载速度，并通过确保join/filter字段是索引的，就可以加快查询的速度。在这个特定的表上，在加载期间暂时删除索引不是一个选项，因为查询可以同时运行。

在阅读了大量关于索引计划、使用复合索引、事务和表锁定的内容之后，我完全糊涂了！

问题：

1.)一般来说，应该通过表锁定来最好地解决更新问题，因为删除索引不是一个选项(已经使用了事务)。

2.)关于读取索引计划，并考虑到每个选择的ID字段总数和可变字段数，是否最好保持20个单独的索引和一些最常见的化合物，还是实现某种散列？

在阅读对类似线程的其他回复时，我意识到“一般”的答案是“一般的”皱眉。但是，我不知道如何模拟一个具有1键+20个ID字段的表，其中包含删除、更新和插入查询的示例代码，以及包含不同ID字段组合的十几个select查询的示例代码，这将比我更好地说明这个问题(尽管！)。

任何指导或参考都会受到赞赏(请注意，我已经在MYSQL文档中花费了大量时间)。

Answer 1

所以对于mysql来说，2-3百万行没有那么大。我有一个合适的索引计划，对600,000,000+行表(~40 of )进行了优化(尽管在那种情况下，我知道并限制了选择范围，以使优化工作更容易，这是将大型表作为4表连接的一部分)

一个包含20列INT(11)和1,000,000,000行的InnoDB表在我的测试服务器上完全占据了106 my (第一列上只有一个主键)。第二个单列索引添加20。

在这一点上，我的建议是构建您的数据库结构，用有代表性的数据填充它，并模拟您期望的负载。然后考虑您可能希望添加/删除的索引，并对性能差异进行基准测试。

如果然后遇到示例查询，您就无法在堆栈溢出上进行优化询问。听起来你好像做了你的研究。

如果您的数据库位于内存充足的框上(并且正在使用InnoDB)，则可能需要查看如何调整InnoDB缓冲池：http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html

如果您知道所有的更新都来自一个进程，但是所有的读取都来自多个来源，那么您可能会发现主从复制通过在多个服务器上传播读取负载可能会有所帮助。http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-scaleout.html