Differences between MySQL MyISAM and InnoDB

the difference:

1. InnoDB supports transactions, but MyISAM does not. For InnoDB, each SQL statement is encapsulated as a transaction by default and automatically committed, which will affect the speed. Therefore, it is best to put multiple SQL statements between begin and commit to form a transaction.

2. InnoDB supports foreign keys, while MyISAM does not. Converting an InnoDB table containing foreign keys to MYISAM will fail;

3. InnoDB is a clustered index and uses B+Tree as the index structure. The data file is bound to the (primary key) index (the table data file itself is an index structure organized by B+Tree). There must be a primary key, and the efficiency of the primary key index is very high. However, the auxiliary index requires two queries: first query the primary key, and then query the data through the primary key. Therefore, the primary key should not be too large, because if the primary key is too large, other indexes will also be large.

MyISAM is a non-clustered index and also uses B+Tree as the index structure. The index and data files are separate, and the index saves the pointer to the data file. The primary key index and secondary indexes are independent.

In other words, the leaf nodes of InnoDB's B+ tree primary key index are data files, and the leaf nodes of the auxiliary index are the values ​​of the primary key; while the leaf nodes of MyISAM's B+ tree primary key index and auxiliary index are both address pointers to data files.

4. InnoDB does not save the specific number of rows in the table. When executing select count(*) from table, the entire table needs to be scanned. MyISAM uses a variable to save the number of rows in the entire table. When executing the above statement, you only need to read the variable, which is very fast (note that no WHERE conditions can be added);

So why doesn't InnoDB have this variable?

Due to the transactional nature of InnoDB, the number of rows in the table at the same time is different for different transactions. Therefore, the count statistic calculates the number of rows that can be counted for the current transaction instead of storing the total number of rows for quick query. InnoDB will try to traverse the smallest possible index unless the optimizer tells it to use another index. If the secondary index does not exist, InnoDB will also try to traverse other clustered indexes.
If the index is not completely in the buffer pool maintained by InnoDB, the count operation will be time-consuming. You can create a table that records the total number of rows and let your program update the corresponding data during INSERT/DELETE. As with the problem mentioned above, this solution does not work well if there are multiple transactions at this time. If the approximate row value is sufficient to meet your needs, you can try SHOW TABLE STATUS

5. InnoDB does not support full-text indexing, while MyISAM does. MyISAM is faster in terms of query efficiency in the field of full-text indexing. PS: InnoDB after 5.7 supports full-text indexing.

6. MyISAM tables can be compressed and queried

7. InnoDB supports table and row (default) level locks, while MyISAM supports table level locks

InnoDB's row locks are implemented on indexes rather than on physical row records. The implication is that if the access does not hit the index, the row lock cannot be used and will degenerate into a table lock.

8. InnoDB tables must have a primary key (if the user does not specify one, it will find or generate one on its own), while MyISAM does not have one.

9. Innodb storage files are frm and ibd, while Myisam is frm, MYD, MYI

Innodb: frm is the table definition file, ibd is the data file

Myisam: frm is the table definition file, myd is the data file, myi is the index file

How to choose:

1. Whether to support transactions. If yes, please choose InnoDB. If not, you can consider MyISAM.

2. If most of the queries in the table are read queries, you can consider MyISAM. If there are both read and write queries, please use InnoDB.

3. After the system crashes, MyISAM is more difficult to recover. Is this acceptable?

4. Starting from MySQL 5.5 version, Innodb has become the default engine of MySQL (previously it was MyISAM), which shows that its advantages are obvious to all. If you don’t know what to use, then use InnoDB, at least it won’t be bad.

Why does InnoDB recommend using auto-increment ID as the primary key?

Answer: The auto-increment ID ensures that the B+ index is expanded from the right each time an insertion occurs, thus avoiding frequent merging and splitting of B+ trees (compared to using UUID). If you use string primary keys and random primary keys, data will be inserted randomly, which is inefficient.

Four major features of the innodb engine

Insert buffer, double write, adaptive hash index (ahi), read ahead

The above is the detailed content of the difference between MySQL MyISAM and InnoDB. For more information about MySQL MyISAM and InnoDB, please pay attention to other related articles on 123WORDPRESS.COM!