This is the 11th day of my participation in the August Wenwen Challenge.More challenges in August

| author: jiangxia

| CSDN：blog.csdn.net/qq_41153943

| the nuggets: juejin. Cn/user / 651387…

| zhihu: www.zhihu.com/people/1024…

| GitHub:github.com/JiangXia-10…

This article is about 1,165 words and is recommended to read for 9 minutes

A concept,

Unlike most databases, MySQL has a storage engine concept. You can choose the optimal storage engine for different storage requirements. Storage engine is the implementation of data storage, index building, update query data and other technologies. Storage engines are table-based, not library-based, so they can also be called table types.

Data in MySQL is stored in files (or memory) using various techniques. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different capabilities and capabilities. By choosing different technologies, you can gain additional speed or functionality that can improve the overall functionality of your application.

Generally speaking, if the same database uses different functions (such as the type of files to be stored, whether to support transactions, etc.), it needs to select the corresponding storage engine to improve storage performance. The storage engine here is just like the engine, if it is a car (database), just to work at ordinary times used to commute, so ordinary engine (storage engine), but there’s also like modified car enthusiasts, fitted on the car engine, so that the different functions and purposes.

Databases such as Orcal and SqlServer have only one storage engine, while MySQL provides a plug-in storage engine architecture. Therefore, MySQL supports multiple storage engines. You can use corresponding engines as required or write storage engines by yourself.

MySQL5.0 starts with a number of storage engines, such as: InnoDB, BDB, Memory, Merge, Example, Archive, CSV, Blackhole, Federated, etc. Among them, InnoDBh and BDB provide transaction safe tables, while other storage engines are non-transaction safe tables.

You can use the following command to view the storage engines supported by the current database

Check out the supported storage engine Show Engines. Show variables like 'storage_engine'Copy the code

InnoDB is the default storage engine supported by MySQL database. If you do not specify a storage engine when creating a new table, the system will use the default storage engine. MyISAM was the default storage engine before MySQL5.5, and InnoDB was changed after 5.5.

2. Features of common storage engines

There are three common storage engines for MySQ: InnoDB, MyISAM, and Memory.

1. InnoDB storage engine

InnoDB is the preferred engine for transactional databases with ACID support, row locking and foreign keys. InnoDB is the default storage engine used by MySQL. The main features of InnoDB are:

1. InnoDB provides MySQL with a transaction safe (ACID compliant) storage engine with commit, rollback and crash recovery capabilities. So compared to the MyISAM storage engine, InnoDB writes less efficiently and takes up more disk space to retain data and indexes.

InnoDB locks row-level and also provides an oracle-like unlocked read in SELECT statements. These capabilities increase multi-user deployment and performance. In SQL queries, you can freely mix innoDB-type tables with other Mysql table types, even within the same query.

3. InnoDB is designed for maximum performance in handling large amounts of data. Its CPU efficiency is probably unmatched by any other disk-based relational database engine

4. InnoDB Storage engine is fully integrated with Mysql server, InnoDB storage engine maintains its own cache pool for caching data and indexes in main memory. InnoDB keeps its tables and indexes in a logical table space that can contain several files (or raw disk files). This is different from, for example, MyISAM tables where each table is stored in separate files. InnoDB tables can be any size, even on operating systems where file sizes are now 2GB.

5. InnoDB is the only storage engine in MySQL that supports foreign key integrity constraints. When creating foreign keys, the parent table must have corresponding indexes, and the child table will automatically create corresponding indexes when creating foreign keys. In addition, when storing the data in the table, the storage of each table is stored by the primary key. If the primary key is not specified when the table is defined, InnoDB will generate a 6-byte ROWID for each row and use it as the primary key

6. InnoDB is used in many large database sites that require high performance.

InnoDB does not create directories. When InnoDB is used, MySQL will create a 10M size automatic extension data file named ibdata1 in MySQL data directory, and two 5M size log files named ib_logfile() and lib_logfile1

InnoDB stores tables and indexes in two ways:

• The table structure of a table created in this way is stored in a. FRM file. The data and indexes are stored in a tablespace defined by Innodb_datA_home_DIR and Innodb_datA_FILe_PATH.

• With multi-tablespace storage, the table structure of the tables created this way still exists in the.FRM file, but the data and indexes for each table are stored separately in the.ibD.

MyISAM storage engine

MyISAM is based on and extends the ISAM storage engine. It is one of the most commonly used storage engines in Web, data warehousing, and other application environments. MyISAM has high insert and query speed, but does not support transactions and foreign keys. The main features of MyISAM are:

1. Large files (up to 63 bit file length) are supported on file systems and operating systems that support large files.

Dynamically sized rows produce less fragmentation when delete is mixed with update and insert operations. This is done automatically by merging adjacent deleted blocks and extending to the next block if the next block is deleted

3. The maximum number of indexes per MyISAM table is 64, which can be changed by recompilation. The maximum number of columns per index is 16

BLOB and TEXT columns can be indexed

5. All numeric key values are stored in high byte priority to allow a higher index compression

Alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM Therefore, AUTO_INCREMENT columns of MyISAM type are updated faster than AUTO_INCERMENT of InnoDB type

Creating the database using the MyISAM engine will produce three files. File type: FRM file storage table definition, data file extension MYD (MYData), index file extension MYI(MYIndex)

3. MEMORY engine

MEMORY Storage engines store data from tables into MEMORY, providing fast access to query and reference data from other tables. The main features of MEMORY are:

1. Each table in MEMORY can have up to 32 indexes with 16 columns per index and a maximum key length of 500 bytes

2. The MEMORY storage engine performs HASH and BTREE indexes

It is possible to have non-unique keys in a MEMORY table

4. MEMORY tables use a fixed record length format

MEMORY does not support BLOB or TEXT columns

6, MEMORY supports AUTO_INCREMENT columns and indexes for columns that may contain NULL values

7. MEMORY tables are shared among all clients (just like any other non-temporary table)

8. MEMORY tables are stored in MEMORY, which is shared with internal tables created by the server during idle query processing. Once the service is shut down, the data in the table is lost.

DELETE FROM TRUNCATE TABLE or DELETE the entire TABLE when the contents of the TABLE are no longer needed.

The main features of the three storage engines can be summarized in a chart as follows:

Storage engine selection

Selecting a proper storage engine according to the characteristics of the application system can greatly improve the speed of data storage and access and data security. For complex application systems, you can combine multiple storage engines based on actual conditions. The following describes the common application environment of centralized storage engines.

InnoDB: InnoDB is MySQL default storage engine, used for transaction processing applications, support foreign keys, so if the application is taller to the requirement of the integrity of the transaction, under the condition of concurrent request data consistency, data operations in addition to insert and query, also contains a lot of update, and delete operations, you should choose the InnoDB storage engine is better. In addition to effectively reducing row locking due to deletes and updates, InnoDB storage engine also ensures complete commit and rollback of transactions.

MyISAM: The MyISAM storage engine can be used if the application is mainly read and insert operations, with few updates and deletes, and the transaction integrity is not high, but the performance is not high.

Memory: The Memory storage engine keeps all data in Memory, providing fast access when records and other similar data need to be located quickly. The limitations of the Memory storage engine are that tables that are too large cannot be cached in Memory, and that the data in the table can be recovered if the database terminates unexpectedly. Therefore, Memory storage engines are generally suitable for small tables that are infrequently updated so that access results can be obtained quickly.

Four,

So that’s an introduction to MySQL’s storage engine. Only a good understanding of the underlying concepts of database storage engine, so that we can better use the database in the development, help to improve the performance of the system.

Any questions or incorrect place welcome to discuss correction!

Today’s recommendation

Don’t you know what a database index is

The interview topic series of String and Stringbuffer, the difference between the StringBuilder (source code analysis)

Do you know why the String class cannot be inherited?

SpringBoot integration Druid configuration data source monitoring

Spring Note (5) : Four ways a container can register a component