Article From:

MysqlThere are many storage engines, the most commonly used are MyISAM and InnoDB. Each type of storage engine has its own characteristics, which storage engine can be combined with the use scenario of data in the project.

1:View the storage engine supported by MySQL database

Commands can be used to list the current version of the data-supported storage engine:


You can use the following commands to see what the default storage engine for the current version is:

SHOW VARIABLES LIKE ‘storage_engine’;

MyISAMA comparison between InnoDB and InnoDB


Contrast itemMYISAMInnoDB
Transaction supportMyISAMTransaction is not supported; MyISAM is non-transactional and secureInnoDBSupport. InnoDB is transaction-safe. Autocommit is opened by default. It is better to merge transactions and commit together to reduce the overhead caused by multiple submissions of the database and greatly improve the performance.
 storage structureMyISAMThree files are stored on disk. The name of the first file begins with the name of the table, and the extension indicates the file type. Frm file storage table definition. The extension of the data file is. MYD (MYData). The extension of the index file is. MYI (MYIndex).  All tables are stored in the same data file (or may be multiple files, or separate tablespace files). The size of InnoDB tables is limited to the size of operating system files, generally 2GB.
storage spaceIt can be compressed and has small storage space. Three different storage formats are supported: static tables (default, but note that there are no spaces at the end of the data, which will be removed), dynamic tables, compressed tables.More memory and storage are needed, which creates its own dedicated buffer pool in main memory for caching data and indexing
Portability, backup and recoveryData is stored in the form of files, so it is very convenient to transfer data across platforms. When backing up and restoring, you can operate on a table separately.Free solutions can be copying data files, backing up binlog, or using mysqldump
AUTO_INCREMENTJoint indexes can be created with other fields. The engine’s auto-growth column must be an index. If it is a composite index, the auto-growth column may not be the first column. It can be sorted and incremented according to the previous columns.InnoDBIt must contain an index with only that field. The engine’s auto-growth column must be an index, and if it is a composite index, it must be the first column of the composite index.
Table lock differenceOnly table level locks are supported. When users operate MyISAM tables, select, update, delete, insert statements will automatically lock the tables. If the tables after locking satisfy insert concurrency, new data can be inserted at the end of the table.Supporting transaction and row-level locks is innodb’s greatest feature. Line lock greatly improves the new capability of multi-user concurrent operation. But InnoDB’s row locks are only valid when the primary key of WHERE is valid, and all non-primary key WHEREs lock the whole table.
Full-text indexFull-text indexing support (FULLTEXT type)Full-text indexing (FULLTEXT type) is not supported, but InnoDB can use Sphinx plug-ins to support full-text indexing, and the effect is better.
Table primary keyTables that do not have any indexes or primary keys are allowed to exist. Indexes are addresses that hold rows.If no primary key or non-empty unique index is set, a 6-byte primary key (invisible to the user) is automatically generated. The data is part of the primary index, and the additional index stores the value of the primary index. InnoDB has a larger primary key range, up to twice the size of MyISAM.
Number of specific rows in the tableSave the total number of rows in the table, and if select count (*) from table; the value will be fetched directly.If you use Select count (*) from table instead of saving the total number of rows of the table, you will traverse the entire table, which is quite expensive, but with the addition of wehre conditions, MyISAM and InnoDB handle the same way.
CURDoperationMyISAM is a better choice if you perform a lot of SELECT.If your data executes a lot of INSERT or UPDATE, you should use InnoDB tables for performance reasons. DelETE is better in performance than InnoDB, but InnoDB does not rebuild tables when DELETE FROM table is used.It’s a line-by-line deletion. If you want to empty a table with a large amount of data on innodb, you’d better use the truncate table command.
foreign keyI won’t support itSupport

  Reference resources:


Leave a Reply

Your email address will not be published. Required fields are marked *