MyISAM vs InnoDB

summary_2018/09 mysql

1、日常

1.1、MyISAM vs InnoDB

2、技术

2.1、MyISAM vs InnoDB

2.1.1、适应场景？？

• 如果你需要更多的读取和更少的插入/更新/删除，MyISAM可能会更有效率；如果你需要外键，事务等更加负杂的操作，InnoDB会更加有效。
• InnoDB比MyISAM有更多的复杂性，也非常值得学习，在大多数情况我们都应该使用它，除非有充足的理由。

2.1.2、特性之间的区别

• 参照完整性（Referential Integrity）
  
  • 参照完整性可确保表之间的关系保持一致。即外键约束。InnoDB是一个关系型DBMS（RDBMS），因此具有参照完整性，而MyISAM则没有。
• 锁定级别（Table-locking vs Row-locking）
  
  • InnoDB建立在聚簇索引上，使用MVCC实现高并发性，读不加锁，写支持行级锁(当然也支持粒度更大的锁)，提供更高的并发性能；但MyISAM在整个表上使用共享和独占锁，相互阻塞。但是，允许并行读取和插入新行。
• 事务和回滚 Transactions & Rollbacks
  
  • InnoDB支持事务，可以回滚任何未提交的操作；而在MyISAM中，当操作中断时，发生的更改将会生效且不能回滚。
• 可靠性（Reliability）
  
  • MyISAM offers no data integrity - Hardware failures, unclean shutdowns and canceled operations can cause the data to become corrupt. This would require full repair or rebuilds of the indexes and tables.
  • InnoDB, on the other hand, uses a transactional log, a double-write buffer and automatic checksumming and validation to prevent corruption. Before InnoDB makes any changes, it records the data before the transactions into a system tablespace file called ibdata1. If there is a crash, InnoDB would autorecover through the reply of those logs.
  • MyISAM不会同步写入磁盘。它写入文件系统缓存，并且您的操作系统决定何时最终将缓存写入磁盘。每次重新启动服务器时，都可能丢失一些您认为已写入数据库的MyISAM数据。如果数据十分重要，请使用InnoDB。
• FULLTEXT Indexing
  
  • InnoDB does not support FULLTEXT indexing until MySQL version 5.6.4.However, this is not a valid reason to use MyISAM. It’s best to change to a hosting provider that supports up-to-date versions of MySQL.
• memory
  
  • InnoDB requires a lot of memory (buffer pool). The data and indexes are cached in memory. Changes are written to the log buffer (physical memory) and are flushed every second to the log files (method depends on innodb_flush_log_at_trx_commit value). Having the data in memory is a huge performance boost.
  • MyISAM only caches indexes (key_buffer_size) so that's where you would allocate most of your memory if you're only using MyISAM.
• Disk
  
  • Disk footprint is 2x-3x less than InnoDB's. -- As of Version 5.7, this is perhaps the only real advantage of MyISAM.
• back up/copy
  
  • more quickly back up/copy MyISAM-based tables and databases than InnoDB, since MyISAM tables are stored in individual files (vs table space files)--lock tables and copy raw files; with InnoDB, you must lock tables and run mysqldump, and then, when restoring, send all of the SQL commands back through a MySQL client.

2.1.3、总结

• 相比于InnoDB，MyISAM更加简单，支持的特性也相对较少；在一致性要求比较高，数据比较重要，读写并发较高等大多数场景都应该使用InnoDB，当仅对于读的性能而言，MyISAM可能表现会更好，适用于读多/写多的场景，读写都频繁的场景并不适合。
• 而且我们也可以为不同的表选取不同的存储引擎来适应不同的场景，但要在程序中注意不同的存储引擎接受的操作是不同的。

2.1.4、参考资料

• MyISAM vs InnoDB
• What is the difference between MyISAM and InnoDB when it comes to MySQL databases?