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

This article mainly introduces "what is Mysql storage engine". In daily operation, I believe many people have doubts about what is Mysql storage engine. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "what is Mysql storage engine"! Next, please follow the editor to study!

InnoDB storage engine

InnoDB is the default transactional engine of MySQL, and it is also the most important and widely used storage engine. It is designed to handle a large number of short-term transactions. The performance and automatic crash recovery of InnoDB make it very popular in the requirements of non-transactional storage. Unless there is a very special reason to use another storage engine, the InnoDB engine should be given priority. InnoDB uses MVCC to support high concurrency and implements four standard isolation levels. InnoDB is based on a clustered index. And many internal optimizations have been made, including predictability pre-reading when reading data from disk, adaptive hash indexes that can create hash indexes in memory to speed up read operations, and insert buffers that can speed up insert operations.

MyISAM storage engine

MySQL 5.1 and earlier versions of the default storage engine. Provides a large number of features, including full-text indexing, compression, spatial functions, etc., but does not support transactions and row-level locks, and there is no doubt that it cannot be safely recovered after a crash. For read-only data, or if the table is small enough to tolerate repair repair operations, you can continue to use MyISAM. The main features are: locking and concurrency, locking the entire table instead of rows (causing performance problems), sharing locks on all tables that need to be read, and exclusive locks on tables when writing, but when the table has a read query, new records can also be inserted into the table (called concurrent insert) Repair, check and repair operations can be performed manually or automatically, but can result in some data loss, and the repair operation is very slow. (check table mytable,repair table mytable); support prefix indexing and full-text indexing, which is based on participle and can support complex queries; delay updating indexing and specify the DELAY_KEY_WRITE option, so that after each modification is completed, the modified index data will not be written to disk immediately, but to the key buffer in memory, and the corresponding index block will be written to disk only when cleaning the key buffer or closing the table. This method can greatly improve write performance, but when the database or host crashes, it will cause index corruption and need to be repaired. Compressed tables are supported, but they cannot be modified after compression (unless they are decompressed, modified, and then compressed again). It helps to reduce disk space consumption, reduce disk Imax O and improve query performance.

Other storage engines Memory engines

All the data is stored in memory without disk IO, which is at least an order of magnitude higher than the MyISAM table, which is suitable for fast data access, and the data will not be modified and the restart will be lost. It is suitable for scenarios such as finding or mapping tables, caching periodically aggregated data or saving intermediate data generated by data analysis.

Unlike temporary tables, temporary tables refer to tables created using create temporary table statements, which can use any storage engine, temporary tables are visible only in a single connection, and will no longer exist when the connection is disconnected.

Archive engine

Only insert and select operations are supported, and all writes are cached and inserted rows are compressed using zlib, so there are fewer tables than MyISAM. Suitable for log and data acquisition applications, it is a simple engine optimized for high-speed insertion and compression.

How to choose the right engine

For the most part, InnoDB is the right choice and the default storage engine. Unless you need to use some features that InnoDB does not have, and there is no other way to replace it, the InnoDB engine should be preferred.

Business

If transaction support is required, InnoDB is by far the most stable and validated choice.

Backup

If you need an online hot backup, then choosing InnoDB is the most basic requirement, and if you can shut down the server regularly to perform the backup, then the backup factor can be ignored.

Crash recovery

When the amount of data is relatively large, how to recover quickly after the system crash is a problem to be considered. Relatively speaking, the probability of damage after a MyISAM crash is much higher than that of InnoDB, and the recovery speed is also slow.

Unique characteristics

For example, only MyISAM supports geospatial search, InnoDB supports clustered indexes, and so on.

Application example log application

This kind of application requires high insertion speed, and the database can not become a bottleneck. MyISAM or Archive storage engine is more suitable for this kind of application because of low overhead and fast insertion speed. If you need to analyze the recorded log, the sql that generates the report may lead to a significant reduction in insertion efficiency, how to solve it? First, use the built-in replication scheme of MySQL to copy a copy of the data to the standby database, and then execute a time-consuming and CPU query on the standby database, and the main database is used for efficient insertion. Second, the table is stored in different time periods, and the frequent query operations on the history table will not interfere with the insert operation on the latest current table.

A read-only table or, in most cases, a read-only table

In the scenario of reading more and writing less, if you don't mind the crash recovery of MyISAM, then MyISAM is appropriate. MyISAM will only write the data to memory and wait for the operating system to brush the data out to disk on a regular basis.

Do not believe empirical arguments such as "MyISAM is faster than InnoDB". This conclusion is not absolute. In many known scenarios, the speed of InnoDB can be beyond the reach of MyISAM, especially when using clustered indexes, where all the data you need to access can be stored in memory. When designing the above types of applications, InnoDB is recommended. With the increase of application pressure, MyISAM may deteriorate rapidly, and all kinds of lock contention, data loss after crash and other problems will follow.

Order processing

Transaction support is a necessary option, and considering foreign key support, InnoDB is the best choice for order processing applications.

At this point, the study on "what is the Mysql storage engine" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.