Mysql has a variety of storage engines, currently commonly used is MyISAM and InnoDB these two engines, in addition to these two engines, there are many other engines, some official, but also some companies developed their own. This article mainly gives a brief overview of the common MySQL engine. First, this is often asked questions in the interview. Second, this is also a problem that can not be ignored in database design.
MyISAM
MyISAM was the default mysql engine prior to 5.5.5, and it supports the b-tree /FullText/R-tree index types.
The lock level is table lock. Table lock has the advantages of low overhead and fast locking. The disadvantages are large lock granularity, high lock impulse probability and low capacity to accommodate concurrency. This engine is suitable for query-oriented services.
This engine does not support transactions, nor does it support foreign keys.
MyISAM emphasizes fast read operations. It stores the number of rows of the TABLE, so SELECT COUNT(*) FROM the TABLE only needs to read the saved value directly without performing a full TABLE scan.
InnoDB
One of the biggest highlights of InnoDB storage engine is transaction support, rollback support, Hash/B-tree index type.
The lock level is row lock. The advantage of row lock is that it is suitable for frequent table changes with high concurrency, and the high concurrency is better than MyISAM. The disadvantage is that the system is expensive, the index caches not only itself but also data, and requires more memory than MyISAM.
InnoDB does not store the exact number of rows in a table, that is, when performing select count(*) from a table, InnoDB scans the entire table to calculate how many rows there are.
Support transactions, support foreign keys.
An ACID transaction
A Transaction Atomicity: A transaction either executes at all or not at all. That is, a transaction cannot stop halfway through execution. For example, if you withdraw money from an ATM machine, the transaction can be divided into two steps: 1) transferring the card and 2) transferring the money. It’s impossible to swipe a card and not have the money come out. These two steps have to be done at the same time, or not at all. C Consistency of a transaction: the execution of a transaction does not change the Consistency of data in the database. For example, if integrity constrains a+b=10 and a transaction changes A, then B should change as well. I Independence: The independence of transactions, also known as Isolation, refers to the state in which two or more transactions will not be staggered. This can lead to data inconsistency. D Durability: Transaction Durability means that after a transaction succeeds, changes made to the database by that transaction persist in the database without being rolled back for no reason.
Memory
Memory is a Memory level storage engine. Data is stored in Memory, so it can store a small amount of data.
Storage engines have poor data consistency support due to the nature of memory. The lock level is table lock and does not support transactions. However, the access speed is very fast and the hash index is used by default.
The Memory storage engine uses the contents of Memory to create tables. Each Memory table actually corresponds to only one disk file, which is represented as a. FRM file on disk.
conclusion
| MyISAM | InnoDB | |
|---|---|---|
| Storage structure | Each table is stored in three files: FRM – lattice definition MYD(MYData)- data file MYI(MYIndex)- index file | All tables are stored in the same data file (or multiple files, or separate table space files). The size of InnoDB tables is limited only by the size of the operating system file, which is generally 2GB |
| The storage space | MyISAM can be compressed and has less storage space | InnoDB tables require more memory and storage, and it creates its own buffer pool in main memory for caching data and indexes |
| Portability, backup, and recovery | Since MyISAM data is stored as a file, it is convenient for cross-platform data transfer. You can operate on a single table during backup and restore | Free solutions can be copying data files, backing up binlogs, or using mysqldump, which can be painful at tens of gigabytes |
| The transaction security | Atomicity per query is not supported | Support for transaction-Safe (ACID Compliant) tables with commit, rollback, and Crash recovery capabilities |
| AUTO_INCREMENT | MyISAM tables can be indexed jointly with other fields | InnoDB must contain an index with only this field |
| SELECT | MyISAM better | |
| INSERT | InnoDB better | |
| UPDATE | InnoDB better | |
| DELETE | InnoDB is even better because it doesn’t rebuild tables, it deletes them row by row | |
| COUNT without WHERE | MyISAM is better. Because MyISAM keeps the exact number of rows of the table | InnoDB does not store the exact number of rows of a table, so it is slow to scan statistics line by line |
| COUNT with WHERE | The same | InnoDB also locks tables |
| The lock | Only table locks are supported | Table locking and row locking are supported. Row locking greatly improves the capability of multi-user concurrent operations. InnoDB locks rows only WHERE primary key is valid, non-primary key WHERE all tables are locked |
| A foreign key | Does not support | support |
| FULLTEXT FULLTEXT index | support | Full text indexes can be obtained from InnoDB using Sphinx, which is slower |
In Internet projects, with the reduction of hardware cost and the application of cache and middleware, we generally choose InnoDB storage engine as the main choice, and rarely choose MyISAM. When the business really develops to a certain extent, its own storage engine cannot meet the requirements, then the company should have the strength to independently develop storage engines to meet their own needs or buy a commercial storage engine.