Home > Article > Backend Development > The difference between MyISAM and InnoDB
MyISAM |
InnoDB |
|
constitute the difference |
Each MyISAM stores three files on disk. The first file name starts with the table name and the extension indicates 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) |
Disk-based The resources are the InnoDB table space data file and its log file. The size of the InnoDB table is only limited by the size of the operating system file, which is generally 2GB |
Transaction processing |
MyISAM type table emphasizes Performance, its execution speed is faster than the InnoDB type, but it does not provide transaction support |
InnoDB provides transactions, supports transactions, foreign keys and other advanced database functions |
add, delete, modify and query operations |
If executed For a large number of selections, MyISAM is a better choice |
1. If your data needs to perform a large number of inserts and updates, for performance reasons, you should use InnoDB tables 2. When deleting from table, InnoDB The table will not be re-created but will be deleted row by row 3. The load table from master operation does not work for InnoDB. The solution is to first change the InnoDB table to a MyISAM table, import the data and then change it to InnoDB. tables, but not applicable to tables using InnoDB features (such as foreign keys) |
Operations for primary key auto-increment |
Internal processing of an auto_increment column per table MyISAM automatically for insert and update operations Update this column, which makes the primary key column faster (minimum 10%) auto_increment can be reset by alter and myisamchk For fields of type auto_increment, InnoDB must contain an index with only that field, but for myisam tables, you can Create first-sum indexes with other fields Better and faster auto_increment handling |
If you specify an auto_increment column for a table, the InnoDB table handle in the data dictionary contains an auto_increment counter called Counter, which is used to assign new values to the column. The auto-increment counter is only stored in the main memory, not on the disk |
The specific number of rows in the table |
select count(*) from table MyISAM just simply read and save it The number of rows. Note that when the count(*) statement contains the where condition, the operations of the two tables are the same |
InnoDB does not save the specific number of rows in the table, that is, execute select count(*) from table, you need to scan the entire table to calculate how many rows there are |
Lock |
Table lock |
provides row locks and provides lock-free reading consistent with Oracle type. In addition, InnoDB tables The row lock is not absolute. If Mysql cannot determine the scan range when executing the SQL statement, InnoDB will lock the entire table |
B+tree |
b-tree or btree |
The above has introduced the difference between MyISAM and InnoDB, including the relevant aspects. I hope it will be helpful to friends who are interested in PHP tutorials.