In the process of using MySQL, I have some questions about the concepts of MyISAM and InnoDB, and what is the difference between the two engines has always been a question in my mind. In order to solve this puzzle, I searched the web and found the following information.

MyISAM is the default database engine of MySQL (before version 5.5), improved by the earlier ISAM (Indexed Sequential Access Method). Although the performance is excellent, it has one drawback: it does not support transaction. However, in these years of development, MySQL also imported InnoDB (another database engine) to strengthen the reference integrity and concurrent violation handling mechanism, and then gradually replaced MyISAM.

InnoDB, one of the database engines of MySQL, is one of the standards released by MySQL AB for binary, InnoDB was developed by Innobase Oy and acquired by Oracle in May 2006. Currently InnoDB is licensed under a two-track system, one under the GPL and the other under a proprietary software license.

What is the difference between MyISAM and InnoDB?

  1. Storage structure
  • MyISAM: Each MyISAM is stored on disk as three files (structure, table index, table data space). .frm file stores table definitions, .MYD (MYData) stores table data, .
  • InnoDB: By default all the tables are stored in the same data file (or may be multiple files, or separate tablespace files), the size of InnoDB table is only limited by the size of the operating system file, usually 2GB.
  1. Storage space
  • MyISAM: It can be compressed and the storage space is small. Support three different storage formats: static table (default, but note that there can’t be spaces at the end of the data, it will be removed), dynamic table, compressed table.
  • InnoDB: requires more memory and storage, it will create its own dedicated buffer pool in main memory for caching data and indexes.
  1. Portability, backup and recovery
  • MyISAM: The data is stored in the form of files, so it will be convenient in the cross-platform data transfer. It can be operated separately for a table during backup and recovery.
  • InnoDB: The free solution can be to copy data files, backup binlog, or use mysqldump, which is relatively painful when the amount of data reaches tens of G.
  1. Transaction support
  • MyISAM: Emphasis is on performance, each query has atomicity, its execution number is faster than InnoDB type, but does not provide transaction support.
  • InnoDB: Provides transaction support for transactions, external keys and other advanced database features. With transaction (commit), rollback (rollback) and crash recovery capabilities (crash recovery capabilities) of transaction-safe (ACID compliant) type table.
  1. AUTO_INCREMENT
  • MyISAM: Can be used with other fields to create a joint index. The auto-growth column of the engine must be an index, if it is a combined index, the auto-growth can be not the first column, he can sort and increment according to the previous columns.
  • InnoDB: InnoDB must contain indexes for only that field. The auto-grow column of the engine must be an index, and if it is a combined index it must also be the first column of the combined index.
  1. Table lock differences
  • MyISAM: Only supports table-level locking, when users operate myisam table, select, update, delete, insert statements will automatically lock the table, if the table after locking meets insert concurrency, new data can be inserted at the end of the table.
  • InnoDB: Support for transactions and row-level locks is the most important feature of innodb. Row locking substantially improves the new ability of multi-user concurrent operations. But InnoDB row lock, only in WHERE the primary key is effective, non-primary key WHERE are locked the whole table.
  1. Full index
  • MyISAM: Support full-text index of FULLTEXT type
  • InnoDB: does not support full-text indexes of FULLTEXT type, but innodb can use sphinx plug-in to support full-text indexes, and it works better.
  1. Table primary keys
  • MyISAM: allows the existence of tables without any indexes and primary keys, and the indexes are all the addresses where the rows are saved.
  • InnoDB: If no primary key or non-null unique index is set, a 6-byte primary key is automatically generated (not visible to the user), the data is part of the primary index, and the additional indexes save the value of the primary index.
  1. The specific number of rows of the table
  • MyISAM: The total number of rows of the table is saved, if select count(*) from table; it will directly take out the value.
  • If you use select count(*) from table; it will traverse the whole table and consume quite a lot, but after adding the wehre condition, both myisam and innodb handle it in the same way.
  1. CURD operations
  • MyISAM: If you perform a lot of SELECTs, MyISAM is a better choice.
  • DELETE From the performance InnoDB is better, but when DELETE FROM table, InnoDB will not recreate the table, but delete row by row. If you want to clear the table with a large amount of data, it is better to use the command truncate table.
  1. Foreign key
  • MyISAM: not supported
  • InnoDB: supported

Through the above analysis, you can basically consider using InnoDB to replace MyISAM engine, because InnoDB itself has many good features, such as transaction support, stored procedures, views, row-level locking, etc. In the case of a lot of concurrency, I believe InnoDB’s performance is definitely much stronger than MyISAM. In addition, no table is a panacea, only the right table type for the right type of business is used to maximize the performance of MySQL. If it is not a very complex Web application, non-critical applications, you can still continue to consider MyISAM, this specific case can be your own discretion.