Comparison of three common Mysql database engines: mysql Database Engine

Comparison of three common Mysql database engines: mysql Database Engine

(Click the blue icon on the top to quickly follow it)

ISAM: ISAM is a well-defined and time-tested data table management method. It is designed to take into account that the number of database queries is much larger than the number of updates. Therefore, ISAM performs read operations quickly without occupying a large amount of memory and storage resources. The two major disadvantages of ISAM are that it does not support transaction processing or fault tolerance: If your hard disk crashes, data files cannot be recovered. If you are using ISAM in a key task application, you must always back up all your real-time data. With its copy feature, MYSQL can support such backup applications.

MyISAM: MyISAM is the MySQL ISAM extension format and default database engine. In addition to providing a large number of functions for indexing and field management not available in ISAM, MyISAM also uses a table lock mechanism to optimize multiple concurrent read/write operations, the cost is that you need to run the optimize table command frequently to restore the space wasted by the update mechanism. MyISAM also has some useful extensions, such as the MyISAMCHK tool used to fix database files and the MyISAMPACK tool used to restore wasted space. MYISAM emphasizes fast read operations, which may be the main reason why MySQL is so favored by WEB development: In WEB development, a large number of data operations you perform are read operations. Therefore, most VM providers and INTERNET platform providers only allow MYISAM format. An important defect in MyISAM format is that data cannot be restored after the table is damaged.

InnoDB: The InnoDB database engine is a direct product that creates MySQL flexibility. This technology is MYSQL + API. When using MYSQL, almost every challenge you face comes from the fact that the ISAM and MyISAM database engines do not support transaction processing or foreign keys. Although it is much slower than ISAM and MyISAM engines, InnoDB includes support for transaction processing and Foreign keys, both of which are not available in the first two engines. As mentioned above, if your design requires one or both of these features, you will be forced to use one of the two engines.

MEMORY:MEMORY is a special storage engine in MySQL. It creates a table using the content stored in the memory, and stores all the data in the memory. These features are very different from the previous two. Each MEMORY-based storage engine table corresponds to a disk file. The file name is the same as the table name and the type is frm. This file only stores the table structure. The data files are stored in the memory, which is conducive to rapid data processing and improves the efficiency of the entire table. It is worth noting that the server needs enough MEMORY to maintain the use of the MEMORY storage engine tables. If you do not need it, you can release the memory or even delete unnecessary tables. By default, MEMORY uses hash indexes. Faster than B-type tree indexes. If you want to use a B-type tree index, you can specify it when creating the index. Note that MEMORY is rarely used because it stores data in the MEMORY. If the MEMORY encounters an exception, the data will be affected. If it is restarted or shut down, all data will disappear. Therefore, the life cycle of a MEMORY-based table is short and generally one-time.

In practice, selecting a suitable storage engine is a complicated problem. Each storage engine has its own advantages and disadvantages, and it cannot be said in general who is better than who is.

 

InnoDB: Supports transaction processing, foreign keys, crash recovery, and concurrency control. If you need to have high transaction integrity requirements (such as banks) and require concurrency control (such as ticket sales), InnoDB has a great advantage. If you need to frequently update or delete databases, you can also select InnoDB because it supports transaction commit and rollback ).

MyISAM: Fast data insertion, low space and memory usage. If the table is mainly used to insert new records and read records, MyISAM can achieve high processing efficiency. If the application integrity and concurrency requirements are relatively low, you can also use it.

MEMORY: All data is stored in the memory. The data processing speed is fast, but the security is not high. If you need fast read/write speed and have low data security requirements, you can choose MEMOEY. It has requirements on the table size and cannot create a large table. Therefore, such databases are only used in relatively small database tables.

Note that tables of multiple storage engines can be used in the same database. If a table requires high transaction processing, you can select InnoDB. In this database, you can select MyISAM storage for tables with high query requirements. If the database needs a temporary table for query, you can select the MEMORY storage engine.