MyISAM to InnoDB: Why and How（MYSQL官方译文）

时间：2021-06-14 12:00:45

原文地址：https://www.mysql.com/why-mysql/presentations/myisam-2-innodb-why-and-how/

　　MySQL使用一个插拔式的存储引擎架构，可以让用户根据实际应用于负载进行选择最合适的存储引擎；然而目前InnoDB是最主流的存储引擎，一些用户之前需要使用全文搜索导致必须依赖MyISAM引擎而且在一些情况下不需要事务的支持，所有选择MyISAM。但是随着Mysql5.6版本的发布，选择MyISAM的这些理由已经不再那么有力，在MySQL5.6版本中InnoDB数据库引擎native的方式支持全文搜索，并且性能有显著提升。

在发布时候介绍了一些I浓浓DB的新特性，包括全文搜索以及性能提升。并且指出如何从 MyISAM到InnoDB的数据迁移方案，主要包含主题有：

  • Portability of InnoDB files (individual .idb files and the Undo log)
  • Performance improvements with InnoDB Read-only transactions
  • Impact of changes to InnoDB page size
  • InnoDB Full Text Search

两个数据引擎的对比：文章有点陈旧，目前InnoDB已经支持全文索引。

原文地址：https://clients.fluccs.com.au/knowledgebase/701/MySQL-Engines-InnoDB-vs-MyISAM--A-Comparison-of-Pros-and-Cons.html

MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons

The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM. To summarize the differences of features and performance,

  1. InnoDB is newer while MyISAM is older.
  2. InnoDB is more complex while MyISAM is simpler.
  3. InnoDB is more strict in data integrity while MyISAM is loose.
  4. InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
  5. InnoDB has transactions while MyISAM does not.
  6. InnoDB has foreign keys and relationship contraints while MyISAM does not.
  7. InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
  8. MyISAM has full-text search index while InnoDB has not.

In light of these differences, InnoDB and MyISAM have their unique advantages and disadvantages against each other. They each are more suitable in some scenarios than the other.

Advantages of InnoDB

  1. InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
  2. Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Disadvantages of InnoDB

  1. Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
  2. Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.
  3. No full-text indexing.

Advantages of MyISAM

  1. Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  2. Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
  3. Full-text indexing.
  4. Especially good for read-intensive (select) tables.

Disadvantages of MyISAM

  1. No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
  2. Doesn’t support transactions which is essential in critical data applications such as that of banking.
  3. Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.

相关文章