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)
我们有一组包含元级数据的表，如组织、组织用户、组织部门等。所有这些表都需要大量的阅读，很少有写操作。此外，表格的大小将会非常小(最大的记录数量约为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)
另一组表存储OLTP数据，如账单事务、用户操作等，这些都将是读写繁重的。这些表将非常庞大(每个表大约有3000万条记录)
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.
对于第一组表，我们计划使用MyISAM，对于第二组表，我们使用InnoDb引擎。我们的许多特性也需要从这两组表中连接。
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?
在将MyISAM表与InnoDB表连接时，是否存在性能问题?此外，在这种设计中，是否还可能遇到其他问题(db备份、调优等)?
Any feedback would be greatly appreciated.
如有任何反馈，将不胜感激。
2 个解决方案
#1
46
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.
每当涉及到MyISAM和InnoDB的连接时，InnoDB表就会出现表级锁行为，而不是行级锁，因为MyISAM参与了查询，而MVCC不能应用于MyISAM数据。在某些情况下，MVCC甚至不能应用于InnoDB。
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.
从另一个角度来看，如果任何MyISAM表通过插入、更新或删除进行更新，则连接查询中涉及的MyISAM表将从其他DB连接中锁定，连接查询必须等到能够读取MyISAM表之后。不幸的是，如果在JOIN查询中有InnoDB和MyISAM的混合，那么InnoDB表就必须像它的MyISAM伙伴一样在连接查询中经历一个间歇性锁，因为它被阻止了。
请记住，MVCC仍然允许读取未提交和可重复读取的事务正常工作，并允许其他事务使用某些数据视图。对于已提交的和可序列化的，我不能这么说。
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 TABLE against the MyISAM tables. InnoDB index cardinality is NEVER STABLE !!! If you run SHOW 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 run OPTIMIZE TABLE against an InnoDB table, that will only defragment the table. OPTIMIZE TABLE will run ANALYZE TABLE internally to generate index statistics against the table. That works for MyISAM. InnoDB ignores it.
MySQL依赖索引基数来确定优化的解释计划。索引基数在MyISAM表中是稳定的，直到大量的插入、更新和删除发生在该表上，您可以通过该表定期对MyISAM表运行优化表。InnoDB索引基数从不稳定!如果您运行来自*innodbtable*的SHOW INDEXES，那么每次运行该命令时，都会看到索引基数的变化。这是因为InnoDB会深入到索引中来估计基数。即使对InnoDB表运行优化表，也只会对表进行碎片整理。优化表将在内部运行分析表，以生成针对表的索引统计信息。这对MyISAM作品。InnoDB忽略它。
My advice for you is to go all out and convert everything to InnoDB and optimize your settings accordingly.
我给你的建议是全力以赴，把所有东西都转换成InnoDB，并相应地优化你的设置。
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 !!!.
信不信由你，在选择更新的过程中，InnoDB/MyISAM仍然有一个开放的票据。如果你读它，它总结出如下的决心:不要做它!
#2
0
I don't think transaction management will work properly or at all since MyISAM tables don't handle it.
我不认为事务管理会正常工作，或者因为MyISAM表无法处理它。
#1
46
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.
每当涉及到MyISAM和InnoDB的连接时，InnoDB表就会出现表级锁行为，而不是行级锁，因为MyISAM参与了查询，而MVCC不能应用于MyISAM数据。在某些情况下，MVCC甚至不能应用于InnoDB。
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.
从另一个角度来看，如果任何MyISAM表通过插入、更新或删除进行更新，则连接查询中涉及的MyISAM表将从其他DB连接中锁定，连接查询必须等到能够读取MyISAM表之后。不幸的是，如果在JOIN查询中有InnoDB和MyISAM的混合，那么InnoDB表就必须像它的MyISAM伙伴一样在连接查询中经历一个间歇性锁，因为它被阻止了。
请记住，MVCC仍然允许读取未提交和可重复读取的事务正常工作，并允许其他事务使用某些数据视图。对于已提交的和可序列化的，我不能这么说。
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 TABLE against the MyISAM tables. InnoDB index cardinality is NEVER STABLE !!! If you run SHOW 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 run OPTIMIZE TABLE against an InnoDB table, that will only defragment the table. OPTIMIZE TABLE will run ANALYZE TABLE internally to generate index statistics against the table. That works for MyISAM. InnoDB ignores it.
MySQL依赖索引基数来确定优化的解释计划。索引基数在MyISAM表中是稳定的，直到大量的插入、更新和删除发生在该表上，您可以通过该表定期对MyISAM表运行优化表。InnoDB索引基数从不稳定!如果您运行来自*innodbtable*的SHOW INDEXES，那么每次运行该命令时，都会看到索引基数的变化。这是因为InnoDB会深入到索引中来估计基数。即使对InnoDB表运行优化表，也只会对表进行碎片整理。优化表将在内部运行分析表，以生成针对表的索引统计信息。这对MyISAM作品。InnoDB忽略它。
My advice for you is to go all out and convert everything to InnoDB and optimize your settings accordingly.
我给你的建议是全力以赴，把所有东西都转换成InnoDB，并相应地优化你的设置。
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 !!!.
信不信由你，在选择更新的过程中，InnoDB/MyISAM仍然有一个开放的票据。如果你读它，它总结出如下的决心:不要做它!
#2
0
I don't think transaction management will work properly or at all since MyISAM tables don't handle it.
我不认为事务管理会正常工作，或者因为MyISAM表无法处理它。