将InnoDB表与MyISAM表联接 [英] Joining InnoDB tables with MyISAM tables

问题描述

我们有一组表，这些表包含元级别的数据，例如组织，组织用户，组织部门等.所有这些表都将以很少的写操作被大量读取.而且，表的大小会很小(最大记录数约为30K-40K)

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)

另一组表存储OLTP数据，例如账单交易，用户操作等，这些数据将被大量读取和写入.这些表将非常庞大(每个表大约有3000万条记录)

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)

对于第一组表，我们计划与MyISAM一起使用，对于第二组表，我们计划与InnoDb引擎一起使用.我们的许多功能还需要在这两个集合中的表上加入JOINS.

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 .

What jumps out immediately at me is MyISAM.

每当有涉及MyISAM和InnoDB的联接时，由于MyISAM参与查询和 MVCC 应用于InnoDB.

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.

从另一个角度看，如果正在通过INSERT，UPDATE或DELETE更新任何MyISAM表，则将JOIN查询中涉及的MyISAM表与其他数据库连接锁定，并且JOIN查询必须等到MyISAM表可以被删除为止.读.不幸的是，如果在JOIN查询中混合使用InnoDB和MyISAM，则由于无法进行写入操作，InnoDB表必须像JOIN查询中的MyISAM合作伙伴一样经历间歇性锁定.

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.

请记住， MVCC仍将允许READ-UNCOMMITTED和REPEATABLE-READ事务正常运行，并使某些数据视图成为可能可用于其他交易.对于READ-COMMITTED和SERIALIZABLE ，我不能说同样的话.

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.

MySQL依靠索引基数来确定优化的EXPLAIN计划.在表上发生许多INSERT，UPDATE和DELETE之前，索引基数在MyISAM表中保持稳定，这样您就可以定期对MyISAM表运行OPTIMIZE TABLE. InnoDB索引基数从未稳定！如果运行SHOW INDEXES FROM *innodbtable*;，则每次运行该命令时，索引基数都会更改.这是因为InnoDB会深入研究索引以估计基数.即使对InnoDB表运行OPTIMIZE TABLE，也只会对该表进行碎片整理. OPTIMIZE TABLE将在内部运行ANALYZE TABLE以生成针对该表的索引统计信息.这适用于MyISAM. InnoDB会忽略它.

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.

我对您的建议是全力以赴，并将所有内容转换为InnoDB并相应地优化设置.

My advice for you is to go all out and convert everything to InnoDB and optimize your settings accordingly.

信不信由你，有一个 InnoDB/MyISAM上仍然有一张公开票在SELECT FOR UPDATE期间加入 .如果您阅读了此文件，则它的分辨率总结如下: 不要这样做！ ！ .

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表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！