将 InnoDB 表与 MyISAM 表连接
我们有一组表，其中包含元级别数据，如组织、组织用户、组织部门等。所有这些表都将被大量读取，而写入操作很少。此外，表大小将非常小（最大记录数约为 30K - 40K）。
另一组表存储 OLTP 数据，例如账单交易、用户操作等，这些数据的读写量都很大。这些表将非常巨大（每个表大约有 3000 万条记录），
对于第一组表，我们计划使用 MyISAM，对于第二组表，使用 InnoDb 引擎。我们的许多功能还需要对这两个集合中的表进行 JOINS。
将 MyISAM 表与 InnoDB 表连接是否存在性能问题？另外，这种设计可能会遇到其他任何问题（数据库备份、调整等）吗？
任何反馈将不胜感激。
We have a set of tables which contain the meta level data like organizations, organization users, organization departments etc. All these table are going to be read heavy with very few write operations. Also, the table sizes would be quite small (maximum number of records would be around 30K - 40K)
Another set of table store OLTP data like bill transactions, user actions etc which are going to be both read and write heavy. These tables would be quite huge (around 30 Million records per table)
For the first set of tables we are planning to go with MyISAM and for the second ones with InnoDb engine. Many of our features would also require JOINS on tables from these 2 sets.
Are there any performance issues in joining MyISAM tables with InnoDB tables? Also, are there any other possible issues (db backups, tuning etc) we might run into with this kind of design?
Any feedback would be greatly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我立即想到的是 MyISAM。
第 1 方面：JOIN 本身
每当存在涉及 MyISAM 和 InnoDB 的联接时，InnoDB 表最终都会出现表级锁定行为而不是行级锁定，因为 MyISAM 参与查询并且 MVCC 无法应用于 MyISAM 数据。 MVCC 在某些情况下甚至无法应用于 InnoDB。
第 2 方面：MyISAM 的参与
从另一个角度来看，如果通过 INSERT、UPDATE 或 DELETE 更新任何 MyISAM 表，JOIN 查询中涉及的 MyISAM 表将被其他数据库连接锁定，并且 JOIN 查询必须等到 MyISAM 更新。可以读取表格。不幸的是，如果 JOIN 查询中混合使用了 InnoDB 和 MyISAM，则 InnoDB 表将不得不像 JOIN 查询中的 MyISAM 伙伴一样经历间歇性锁定，因为写入被阻止。
请记住，MVCC 仍然允许 READ-UNCOMMITTED 和 REPEATABLE-READ 事务正常工作，并让某些数据视图可用于其他事务交易。我不能对 READ-COMMITTED 和 SERIALIZABLE 说同样的话。
方面#3：查询优化器
MySQL 依靠索引基数来确定优化的 EXPLAIN 计划。索引基数在 MyISAM 表中是稳定的，直到表发生大量 INSERT、UPDATE 和 DELETE，这样您就可以定期对 MyISAM 表运行 OPTIMIZE TABLE 。 InnoDB 索引基数永远不稳定！如果您运行 SHOW INDEXES FROM *innodbtable*;，每次运行该命令时您都会看到索引基数发生变化。这是因为 InnoDB 将深入索引来估计基数。即使您对 InnoDB 表运行 OPTIMIZE TABLE ，也只会对表进行碎片整理。
OPTIMIZE TABLE将在内部运行ANALYZE TABLE以针对表生成索引统计信息。这适用于 MyISAM。 InnoDB 会忽略它。我给你的建议是全力以赴，将所有内容都转换为 InnoDB，并相应地优化你的设置。
更新 2012-12-18 15:56 EDT
不管你信不信，仍然有 在 SELECT FOR UPDATE 期间加入 InnoDB/MyISAM 的开放票证。如果你读过它，它总结了如下解决方案：不要这样做！ ！。
What jumps out immediately at me is MyISAM.
ASPECT #1 : The JOIN itself
Whenever there are joins involving MyISAM and InnoDB, InnoDB tables will end up having table-level lock behavior instead of row-level locking because of MyISAM's involvement in the query and MVCC cannot be applied to the MyISAM data. MVCC cannot even be applied to InnoDB in some instances.
ASPECT #2 : MyISAM's Involvement
From another perspective, if any MyISAM tables are being updated via INSERTs, UPDATEs, or DELETEs, the MyISAM tables involved in a JOIN query would be locked from other DB Connections and the JOIN query has to wait until the MyISAM tables can be read. Unfortunately, if there is a mix of InnoDB and MyISAM in the JOIN query, the InnoDB tables would have to experience an intermittent lock like its MyISAM partners in the JOIN query because of being held up from writing.
Keep in mind that MVCC will still permit READ-UNCOMMITTED and REPEATABLE-READ transactions to work just fine and let certain views of data be available for other transactions. I cannot say the same for READ-COMMITTED and SERIALIZABLE.
ASPECT #3 : Query Optimizer
MySQL relies on index cardinality to determine an optimized EXPLAIN plan. Index cardinality is stable in MyISAM tables until a lot of INSERTs, UPDATEs, and DELETEs happen to the table, by which you could periodically run
OPTIMIZE TABLEagainst the MyISAM tables. InnoDB index cardinality is NEVER STABLE !!! If you runSHOW INDEXES FROM *innodbtable*;, you will see the index cardinality change each time you run that command. That's because InnoDB will do dives into the index to estimate the cardinality. Even if you runOPTIMIZE TABLEagainst an InnoDB table, that will only defragment the table.OPTIMIZE TABLEwill runANALYZE TABLEinternally to generate index statistics against the table. That works for MyISAM. InnoDB ignores it.My advice for you is to go all out and convert everything to InnoDB and optimize your settings accordingly.
UPDATE 2012-12-18 15:56 EDT
Believe it or not, there is still an open ticket on InnoDB/MyISAM joining during a SELECT FOR UPDATE. If you read it, it sums up the resolution as follows : DON'T DO IT !!!.
我认为事务管理将无法正常工作或根本无法工作，因为 MyISAM 表无法处理它。
I don't think transaction management will work properly or at all since MyISAM tables don't handle it.