Shulou(Shulou.com)06/01 Report--

MyISAM is the default database engine for MySQL (prior to version 5.5) and is improved by the early ISAM (Indexed Sequential Access Method: indexed sequential access method). Although the performance is excellent, there is one drawback: transaction processing (transaction) is not supported. However, over the years, MySQL also introduced InnoDB (another database engine) to enhance referential integrity and concurrency violation handling mechanisms, and then gradually replaced MyISAM.

InnoDB, one of the database engines of MySQL, is one of the standards for publishing binary for MySQL AB. InnoDB was developed by Innobase Oy and acquired by Oracle in May 2006. Compared with traditional ISAM and MyISAM, the most important feature of InnoDB is that it supports ACID-compatible transaction (Transaction) function, similar to PostgreSQL. At present, InnoDB adopts two-track authorization, one is GPL authorization, the other is proprietary software authorization.

What is the difference between MyISAM and InnoDB?

1. Storage structure

MyISAM: each MyISAM is stored as three files on disk. The name of the first file starts with the name of the table, and the extension indicates the file type. The .frm file stores the table definition. The data file has the extension .MYD (MYData). The index file has the extension .myi (MYIndex).

InnoDB: all tables are stored in the same data file (it may be multiple files, or separate tablespace files), and the size of InnoDB tables is only limited by the size of the operating system file, usually 2GB.

2. Storage space

MyISAM: can be compressed, small storage space. Three different storage formats are supported: static tables (default, but note that there can be no spaces at the end of the data and will be removed), dynamic tables, and compressed tables.

InnoDB: more memory and storage is required, and it sets up its dedicated buffer pool in main memory for caching data and indexes.

3. Portability, backup and recovery

MyISAM: data is stored as a file, so it is convenient in cross-platform data transfer. You can operate on a table separately during backup and restore.

InnoDB: the free solution can be to copy data files, back up binlog, or use mysqldump, which is relatively painful when the amount of data reaches dozens of gigabytes.

4. Transaction support

MyISAM: the emphasis is on performance. Each query is atomic and executes several times faster than the InnoDB type, but does not provide transaction support.

InnoDB: provides advanced database functions such as transaction support transactions and foreign keys. Transaction security (transaction-safe (ACID compliant)) table with transaction (commit), rollback (rollback), and crash repair capability (crash recovery capabilities).

5 、 AUTO_INCREMENT

MyISAM: you can create a federated index with other fields. The auto-growing column of the engine must be an index, and if it is a combined index, the auto-grow may not be the first column, and it can sort and increment according to the previous columns.

The InnoDB:InnoDB must contain an index with only this field. The auto-growing column of the engine must be an index, and if it is a composite index, it must also be the first column of a composite index.

6. Table lock difference

MyISAM: only table-level locks are supported. When users manipulate myisam tables, the select,update,delete,insert statement automatically locks the table. If the locked table meets insert concurrency, you can insert new data at the end of the table.

InnoDB: support for transaction and row-level locks is the most important feature of innodb. Row lock greatly improves the new energy of multi-user concurrent operation. But the row lock of InnoDB, only the primary key of WHERE is valid, the WHERE of non-primary key will lock the whole table.

7. Full-text indexing

MyISAM: full-text indexing supporting FULLTEXT types

InnoDB: full-text indexing of type FULLTEXT is not supported, but innodb can use the sphinx plug-in to support full-text indexing, and the effect is better.

8. Table primary key

MyISAM: tables that do not have any indexes and primary keys are allowed, and indexes are the addresses where rows are saved.

InnoDB: if a primary key is not set or a non-empty unique index is set, a 6-byte primary key is automatically generated (invisible to the user). The data is part of the primary index, and the additional index holds the value of the primary index.

9. The specific number of rows of the table

MyISAM: holds the total number of rows of the table, which will be fetched out directly if select count (*) from table;.

InnoDB: the total number of rows of the table is not saved, and if you use select count (*) from table;, you will traverse the entire table, which is quite expensive, but after adding the wehre condition, myisam and innodb handle it in the same way.

10. CURD operation

MyISAM: it's better to do a lot of SELECT,MyISAM.

InnoDB: if your data does a lot of INSERT or UPDATE, you should use the InnoDB table for performance reasons. DELETE InnoDB is better in terms of performance, but when DELETE FROM table, InnoDB will not re-establish the table, but delete it row by row. If you want to empty a table with a large amount of data on innodb, you'd better use the command truncate table.

11. Foreign key

MyISAM: not supported

InnoDB: support

Through the above analysis, we can basically consider using InnoDB to replace the MyISAM engine, because InnoDB has many good features, such as transaction support, stored procedures, views, row-level locking, and so on. In the case of a lot of concurrency, I believe that the performance of InnoDB must be much better than MyISAM. In addition, any kind of table is not omnipotent. Only by selecting the appropriate table type for the business type, can we maximize the performance advantages of MySQL. If it is not very complex Web applications, non-critical applications, you can continue to consider MyISAM, this specific situation can be considered by yourself.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.