Table of Contents

I. Overview of MySQL Storage Engine
- 1.1 What is a storage engine?
- 1.2 Which storage engines does MySql support
- 1.3 Introduction to various search engines
Second, commonly used storage engines and usage scenarios< /li>

1. Overview of MySQL storage engine

1.1 What Is it a storage engine?

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 provides a wide range of different functions and capabilities. By choosing different technologies, you can gain additional speed or functionality to improve the overall functionality of your application.

For example, if you are researching a large amount of temporary data, you may need to use an in-memory storage engine. The memory storage engine can store all table data in memory. Or, you may need a database that supports transaction processing (to ensure the ability to roll back data when the transaction is unsuccessful).

These different technologies and supporting related functions are called storage engines (also known as table types) in MySQL.

MySQL is configured with many different storage engines by default, which can be preset or enabled in the MySQL server. You can choose a storage engine suitable for servers, databases, and tables to provide you with maximum flexibility in choosing how to store your information, how to retrieve this information, and what performance and functionality you need your data to combine.

This flexibility in choosing how to store and retrieve your data is the main reason why MySQL is so popular. Other database systems (including most commercial options) only support one type of data storage.

Unfortunately, the “one size fits all needs” approach adopted by other types of database solutions means you either sacrifice some performance, or you use it for a few hours or even days. Time to adjust your database in detail. Using MySQL, we only need to modify the storage engine we use.

1.2 What storage engines does MySql support

The storage engines supported by mysql5.6 include InnoDB, MyISAM, MEMORY, CSV, BLACKHOLE, FEDERATED, MRG_MYISAM, ARCHIVE, PERFORMANCE_SCHEMA. NDB and InnoDB provide transaction-safe tables, and other storage engines are non-transaction-safe tables.

1.3 Introduction to various search engines

InnoDB: The default storage engine of MySql 5.6 version. InnoDB is a transaction-safe storage engine that has the functions of commit, rollback, and crash recovery to protect user data. InnoDB’s row-level locking and Oracle-style consistent lock-free read improve its multi-user concurrency and performance. InnoDB stores user data in a clustered index to reduce the I/O overhead caused by ordinary queries based on the primary key. In order to ensure data integrity, InnoDB also supports foreign key constraints.

MyISAM: MyISAM neither supports transactions nor foreign keys. Its advantage is fast access speed, but table-level locking limits its performance in terms of read and write loads. , So it is often used in read-only or read-based data scenarios.

Memory: Store all data in memory, which is used in scenarios where non-critical data is quickly searched. The Memory type table accesses data very quickly, because its data is stored in memory and uses HASH index by default, but once the service is closed, the data in the table will be lost

BLACKHOLE: Black hole storage engine, similar to Unix’s /dev/null, Archive only receives but does not save data. Queries on tables of such engines often return an empty set. This kind of table can be used in the master-slave configuration where DML statements need to be sent to the slave server, but the master server does not keep this kind of data backup.

CSV: Its table is really a comma-separated text file. The CSV table allows you to import and export data in CSV format, interacting data with scripts and applications in the same read and write format. Since the CSV table has no index, you’d better put the data in the InnoDB table in ordinary operations, and only use the CSV table in the import or export phase.

NDB: (aka NDBCLUSTER)-This cluster data engine is especially suitable for applications that require the highest degree of uptime and availability. Note: The NDB storage engine is not supported in the standard MySql 5.6 version. Currently it can support

MySql cluster versions are: MySQL Cluster NDB 7.1 based on MySql 5.1; MySQL Cluster NDB 7.2 based on MySql 5.5; MySQL Cluster NDB 7.3 based on MySql 5.6. MySQL Cluster NDB 7.4, which is also based on MySql 5.6, is currently in the development stage.

Merge: allows MySql DBA or developers to group a series of identical MyISAM tables and reference them as an object. It is suitable for very large-scale data scenarios, such as data warehouses.

Federated: provides the ability to connect to different MySql servers from multiple physical machines to create a logical database. It is suitable for distributed or data market scenarios.

Example: This storage engine is used to save examples of MySql source code that illustrates how to start writing a new storage engine. It is mainly aimed at interested developers. This storage engine is a “stub” that does nothing. You can use this engine to create tables, but you cannot save any data to them, nor can you retrieve any indexes from them.

2. Common storage engines and usage scenarios

InnoDB: For transaction processing applications, support Foreign keys and row-level locks. If the application has relatively high requirements for the integrity of things, requires data consistency under concurrent conditions, and data operations include many update and delete operations in addition to inserts and queries, then the InnoDB storage engine is more suitable. InnoDB not only effectively reduces locks caused by deletions and updates, but also ensures the complete submission and rollback of transactions. It is a suitable choice for systems that require high data accuracy such as billing systems or financial systems.

MyISAM: If the application is based on read operations and insert operations, there are only a few update and delete operations, and the integrity and concurrency of the transaction are not high. Then you can choose this storage engine.

Memory: All data is stored in memory, which can provide extremely fast access in environments where fast location records and other similar data are required. The defect of Memory is that there is a limit to the size of the table. Although the database is abnormally terminated, the data can be restored normally, but once the database is closed, the data stored in the memory will be lost.