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

Editor to share with you the comparative analysis of InnoDB and MyISAM in MySQL, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Compare InnoDB with MyISAM1 and storage structure

MyISAM: each MyISAM is stored as three files on disk. They are: table definition file, data file, index file. 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: MyISAM supports three different storage formats: static tables (default, but note that there can be no spaces at the end of the data, will be removed), dynamic tables, and compressed tables. When the table is created and the data is imported, it is no longer modified, and the compressed table can be used to greatly reduce the disk footprint.

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. CRUD 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.

11. Foreign key

MyISAM: not supported

InnoDB: support

The above is all the contents of the article "Comparative Analysis of InnoDB and MyISAM in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.