When it comes to MySQL, it would be difficult to understand MyISAM and InnoDB, which are two of the most well-known and widely used MySQL storage engines. Today, I will talk to you about the difference between MyISAM and InnoDB in MySQL...
What is MyISAM?
MyISAM is the default storage engine for the MySQL relational database management system (before 5.5)。 This MySQL table storage structure extends many useful features from the old ISAM code. In the new version of MySQL, the InnoDB engine is widely replacing MyISAM due to its advantages in terms of transactions, referential integrity, and higher concurrency.
Each MyISAM table corresponds to three files on the hard disk. All three files have the same file name, but have different extensions to indicate their type purpose: .frm file holds the definition of the table, but this file is not part of the MyISAM engine, but part of the server; .MYD holds the table's data; .MYI is the index file of the table.
What is InnoDB?
InnoDB is another storage engine for MySQL, and the new version of the standard released by MySQL AB is included in all binary installation packages.5.5 onwards as the default storage engine。 Its advantages over other storage engines are its support for ACID-compatible transactions (similar to PostgreSQL) and parameter integrity (i.e., support for foreign keys).
Oracle Corporation acquired Innobase in October 2005. Innobase uses dual authentication authorization. It is distributed using GNU and also allows other groups that want to incorporate InnoDB into commercial software to obtain a license.
The most popular storage engines are MyISAM and InnoDB. The main differences between MyISAM and InnoDB are performance and transaction control. MyISAM is an extended implementation of the early ISAM (Indexed Sequential Access Method, ISAM is no longer supported after MySQL 5.0), ISAM is designed to handle situations where the read frequency is much greater than the write frequency, so ISAM and later MyISAM do not consider the support of things, exclude TPM, do not need transaction records, ISAM query efficiency is considerable, and the memory consumption is very small.
MyISAM inherits these benefits while keeping up with the times a large number of useful new features and related tools. For example, considering concurrency control, table-level locks are provided, and although MyISAM itself does not support fault tolerance, it can be used to recover from failures through myisamchk. And since MyISAM uses its own independent storage files (MYD data file and MYI index file) for each table, it is very convenient to backup and restore (copy overwrite is sufficient), and it also supports online recovery. Compared to other storage engines, MyISAM has most tools for checking and repairing tables. MyISAM tables can be compressed, and they support fulltext search. They are not transaction-safe, and they do not support foreign keys, so if your application does not require transactions and only handles basic CRUD operations, then MyISAM is the way to go.
InnoDB is designed for high concurrency read and write situations, using MVCC (Multi-Version Concurrency Control) and row-level locks to provide ACID-compliant transaction support. InnoDB supports foreign key reference integrity and has fault recovery capabilities. In addition, the performance of InnoDB is actually quite good, especially when processing large amounts of data, in official terms: InnoDB's CPU efficiency is incomparable to other disk-based relational database storage engines. However, InnoDB's backup and recovery is a bit more troublesome, unless you use the Mulit-tablespace support provided by version 4.1 or later, because unlike MyISAM, InnoDB's data files do not correspond to each table independently. Instead, the shared tablespace is used, and the simple copy overwriting method is not suitable for him, and the data must be restored after stopping MYSQL. Using per-table tablespacesd makes each table correspond to a separate tablespace file, the situation is much simpler. It has the same characteristics as BDB types, and they also support foreign keys. InnoDB tables are fast and have richer features than BDB, so it is recommended if you need a transactionally secure storage engine.
In general, InnoDB is a good choice if transaction support is needed and has a high concurrent read and write frequency. BDB can be considered if the frequency of concurrent reads and writes is not high, but BDB will no longer be supported in MySQL 5.1 and later versions. This option is gone
By default, InnoDB transactions are open (set autocommit = 0), which means that every time a record is inserted, the InnoDB table will treat it as a separate transaction. So if we insert 10,000 records and do not close the transaction, then the InnoDB type table will treat it as 10,000 transactions, and the total time of insertion at this time is a lot, at this time, we must turn off the transaction first and then insert it, so the speed will be very fast As for Heap and BDB (Berkeley DB), relatively speaking, the penetration rate is not as good as the previous two, but in some cases, The Heap storage engine is still very applicable, which stores data in memory, and is extremely fast because there is no disk I/O waiting. But since it is a memory storage engine, any modifications made will disappear after the server restarts. Heap is a great place to use BDB for testing, as it is MySQL's first transactionally secure storage engine. Built on the basis of the Berkeley DB database library, it is also transactionally secure, but BDB is obviously not as popular as InnoDB, because most of the storage engines in MySQL that support transactions are also looking for MVCC or row-level locking storage engines, while BDB only supports Page-level Lock.
InnoDB engine
InnoDB is a transactional storage engine that supports rollbacks and is designed to provide high-performance services when processing large amounts of data, and it establishes buffer pools in memory at runtime to buffer data and indexes.
InnoDB engine advantages:
1. Support transaction processing and ACID transaction features;
2. Four isolation levels of SQL standard are realized;
3. Support row-level lock and foreign key constraints;
4. You can use transaction logs for data recovery.
5. The lock level is row lock, which is suitable for frequent table modification with high concurrency, and high concurrency is better than MyISAM. The disadvantage is that the system consumption is large.
6. The index not only caches itself but also caches data, which requires more memory than MyISAM.
InnoDB engine disadvantages:
Because it does not save the number of rows in the table, the entire table is scanned when using COUNT statistics.
MyISAM engine
MyISAM is the default engine before MySQL 5.5.5 and is designed to read fast.
MyISAM Engine Advantages:
1. High-performance reading;
2. Because it saves the number of rows in the table, the whole table will not be scanned when using COUNT statistics;
MyISAM engine disadvantages:
1. The lock level is a table lock, and the advantage of the watch lock is that the overhead is small and the lock is fast; The disadvantages are that the lock granularity is large, the probability of lock impulse is high, and the concurrency capacity is low, which is suitable for query-based services.
2. This engine does not support transactions or foreign keys.
3. INSERT and UPDATE operations need to lock the entire table;
4. It stores the number of rows in the table, so when SELECT COUNT(*) FROM TABLE, it only needs to directly read the saved values without scanning the whole table.
Applicable scenarios
MyISAM is suitable for: (1) doing a lot of count calculations; (2) Infrequent insertion and very frequent queries; (3) There is no business.
InnoDB is suitable for: (1) high reliability requirements or transactions; (2) Table updates and queries are quite frequent, and the chance of table locking is relatively high.
Table comparison
| Properties | MyISAM | Heap | BDB | InnoDB | | transactions | Not supported | Not supported | In the tank | In the tank | | Lock granularity | Table lock | Table lock | Page Lock(page, 8KB) | Lock | | storage | Split files | in memory | One file per table | Tablespace | | Isolation level | not | not | Read Committed | All | | Portable format | be | N/A | not | be | | Citation completeness | not | not | not | be | | Data primary key | not | not | be | be | | MySQL caches data records | not | Yes | Yes | Yes | | usability | Full version | Full version | MySQL－Max | Full version |
Some differences in details
1. InnoDB does not support indexes of FULLTEXT type, which has been supported since MySQL 5.6 (experimental).
2. InnoDB does not save the specific number of rows of the table, that is, when executing select count() from table, InnoDB has to scan the entire table to calculate how many rows there are, but MyISAM only needs to simply read out the number of saved rows. Note that when the count() statement contains a where condition, the operation is the same for both tables.
3. For fields of AUTO_INCREMENT type, InnoDB must contain an index with only that field, but in the MyISAM table, you can create a joint index with other fields.
4. When DELETE FROM table, InnoDB will not re-create the table, but delete it line by line.
5. The LOAD TABLE FROM MASTER operation does not work for InnoDB, the solution is to first change the InnoDB table to the MyISAM table, import the data, and then change it to the InnoDB table, but it is not applicable to the table that uses additional InnoDB features (such as foreign keys).
6. In addition, the row lock of the InnoDB table is not absolute, if MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table.
7. InnoDB does not support full-text indexing, while MyISAM does. Full-text indexing refers to creating a reverse order index of each word in char, varchar, and text (except for stop words). MyISAM's full-text index is actually useless, because it does not support Chinese word segmentation, and must be written to the data table by the user after word segmentation, and words with less than 4 Chinese characters will be ignored like stop words.
|
Posted on 4/13/2020 9:53:49 AM
|
|
|
