你的假设MyISAM一直在接受新的开发是不正确的。MyISAM没有接受任何重大的新开发。MySQL明显正在向淘汰MyISAM的方向发展,并且不鼓励使用MyISAM。
Oracle公司尚未宣布任何具体的日期或版本,以便他们将删除MyISAM。我的猜测是MyISAM永远不会被完全删除,因为有太多的网站无法升级,而不进行昂贵的测试,以确保他们特定的应用程序转换为InnoDB时不会遇到任何回归问题。
但是你可能会注意到,在MySQL 5.7手册中,关于MyISAM的章节已经降级为
替代存储引擎,这应该是一个提示,它正在接收较低的优先级。
在MySQL 5.7中,MyISAM仍然用于某些系统表,例如
mysql.user
,
mysql.db
等。但是在5.6和5.7中引入的新系统表都是InnoDB。在MySQL 8.0中,所有系统表都是InnoDB。
MyISAM仍然不支持
ACID的任何属性。没有事务,没有一致性特性,也没有持久的写入。请参阅我的答案
MyISAM versus InnoDB。
就价值而言,MyISAM仍然不支持外键。但是即使使用InnoDB,我很少看到真正的生产站使用外键。
MyISAM仅支持表级锁定(除了一些插入附加到表末尾的情况,如手册中所述)。
MySQL 5.7在MyISAM和InnoDB中都支持全文索引和空间索引。这些功能不再是继续使用MyISAM的原因。
像mysqldump
这样的逻辑备份工具和Percona XtraBackup这样的物理备份工具无法在不获取全局锁的情况下备份MyISAM表。
你问是否可以在同一个模式中创建不同存储引擎的各种表。是的,你可以,这与许多版本的MySQL相同。
你问是否可以连接不同存储引擎的表(顺便说一句,表不需要在同一个模式中才能连接)。是的,你可以连接这些表,MySQL会处理所有细节。这与许多版本的MySQL相同。
但是,当您在事务中更新MyISAM表和InnoDB表,然后回滚时,可能会出现一些奇怪的情况。 InnoDB表中的更改将被回滚,但是MyISAM表中的更改不会被回滚,因此如果您不小心,数据完整性可能会被破坏。这也与许多版本的MySQL相同。
MyISAM比InnoDB更具优势的情况很少,而且越来越少了。
Some table-scan queries and bulk inserts are faster in MyISAM. InnoDB is better at indexed searches.
MyISAM may use less storage space than the equivalent data stored in an uncompressed InnoDB table. You can further compact MyISAM tables with myisampack, but this makes the MyISAM table read-only.
There are other options these days for compact storage of data in transactional storage engines, for example InnoDB table compression, or MyRocks.
SELECT COUNT(*) FROM MyTable
queries (with no WHERE clause) are very fast in MyISAM, because the accurate count of rows is persisted in the MyISAM metadata. InnoDB (or other MVCC implementations) doesn't keep this count persisted, because every transaction viewing the table might "see" a different row count. Only a storage engine that has table-level locking and no transaction isolation like MyISAM, can optimize this case.
Auto-increment that numbers independently for each distinct value in another key column. Again, this requires table-level locking, so it's not supported in InnoDB.
CREATE TABLE MyTable (
group_id INT NOT NULL,
seq_id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (group_id, seq_id)
) ENGINE=MyISAM;
It's still easy to move a MyISAM table from server to server, because the .MYD and .MYI files are self-contained. You can kind of do something similar with InnoDB tables, but you have to use the intricate feature of transportable tablespaces. But this easy-to-move-tables quality of MyISAM no longer works in MySQL 8.0, because of their new data dictionary feature.
Under certain load, MyISAM might be a better choice for internal_tmp_disk_storage_engine
, which defaults to InnoDB in MySQL 5.7. If you run lots of queries that create temp tables on disk (in-memory temp tables won't benefit), it can put a strain on the InnoDB engine. But you'd have to have a high query rate for this to matter, and if your queries create so many temp tables on disk, you should try to optimize the queries differently.
MyISAM allows you to set multiple key caches, and define caches for specific tables. But the MyISAM key caches are only for index structures, not for data.
参考文献:
https://www.percona.com/blog/2016/10/11/mysql-8-0-end-myisam/
https://www.percona.com/blog/2017/12/04/internal-temporary-tables-mysql-5-7/
http://jfg-mysql.blogspot.com/2017/08/why-we-still-need-myisam.html