Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the function introduction of MySQL storage engine". In daily operation, I believe many people have doubts about the function introduction of 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 "introduction to the function of MySQL storage engine". Next, please follow the editor to study!

What is a storage engine?

Data in MySQL is stored in files (or memory) using a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides a wide range of 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 working on a large amount of temporary data, you may need to use the in-memory MySQL storage engine. The memory storage engine can store all table data in memory. Or, you may need a database that supports transactions (to ensure the fallback ability of the data if the transaction is not successful).

These different technologies and associated 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 pre-set or enabled in the MySQL server. You can choose a storage engine for servers, databases, and tables to provide you with maximum flexibility when choosing how to store your information, how to retrieve it, and what performance and functionality you need your data to combine.

Relational database tables are data structures used to store and organize information. Tables can be understood as tables made up of rows and columns, similar to Excel spreadsheets. Some tables are simple, some tables are complex, some tables are not used to store any long-term data at all, some tables are read very fast, but when inserting data is very poor; and in the actual development process, we may need a variety of tables, different tables, it means that different types of data are stored, and there will be differences in data processing. For MySQL, it provides many types of storage engines (or table types that don't make sense). We can choose different storage engines according to the needs of data processing, so as to maximize the power of MySQL.

In the mysql client, use the following command to view the engines supported by MySQL.

Show engines

MyISAM

It does not support transactions, nor does it support foreign keys, especially for fast access, no requirements for transaction integrity, or applications based on SELECT and INSERT can basically use this engine to create tables.

Each MyISAM is stored as 3 files on disk, where the file name and table name are the same, but the extensions are:

.frm (storage table definition)

MYD (MYData, storing data)

MYI (MYIndex, storage index)

Data files and index files can be placed in different directories, evenly distributed IO, for faster speed. To specify the path to the data file and index file, you need to specify it through the DATA DIRECTORY and INDEX DIRECTORY statements when the table is created, and the file path needs to be an absolute path.

Each MyISAM table has a flag that is set by the server or myisamchk program when it examines the MyISAM data table. The MyISAM table also has a flag to indicate whether the datasheet has been normally closed since it was last used. If the server thinks it crashes or crashes, this flag can be used to determine whether the data table needs to be checked and repaired. If you want this check to occur automatically, you can use the-- myisam-recover phenomenon when starting the server. This allows the server to automatically check the flags of the data table and make the necessary repairs each time the server opens an MyISAM data table. A table of type MyISAM may be corrupted, so you can use the CHECK TABLE statement to check the health of the MyISAM table and use the REPAIR TABLE statement to repair a corrupted MyISAM table.

MyISAM's table also supports three different storage formats:

Static (fixed length) table

Dynamic table

Compression table

Where static tables are the default storage format. The fields in the static table are non-variable length fields, so that each record is of fixed length. The advantage of this storage method is that it is stored very fast, easy to cache, and easy to recover in the event of failure; the disadvantage is that it usually takes up more space than dynamic tables. Static tables fill in spaces based on the width of the column definition when storing data, but do not get these spaces at access time, which have been removed before being returned to the application. At the same time, it should be noted that in some cases, you may need to return the space after the field, and when using this format, the space after the field will be automatically disposed of.

Dynamic tables contain variable-length fields and records are not fixed-length, so the advantage of storage is that it takes up less space, but frequent updates and deletions of records will result in fragmentation, OPTIMIZE TABLE statements or myisamchk-r commands need to be executed regularly to improve performance, and recovery is relatively difficult in the event of a failure.

The compression table is created by the myisamchk tool and takes up a very small amount of space because each record is compressed separately, so there is only a very small access expense.

InnoDB

InnoDB is a robust transactional storage engine, which has been used by many Internet companies and provides a powerful solution for users to operate very large data storage. MySQL version 5.6.13 is installed on my computer, and InnoDB is the default storage engine. InnoDB also introduces row-level locking and foreign key constraints, and InnoDB is the ideal choice in the following situations:

1. Update dense tables. The InnoDB storage engine is particularly suitable for handling multiple concurrent update requests.

two。 Business. The InnoDB storage engine is the standard MySQL storage engine that supports transactions.

3. Automatic disaster recovery. Unlike other storage engines, InnoDB tables can automatically recover from disasters.

4. Foreign key constraint. The only storage engine that MySQL supports foreign keys is InnoDB.

5. Support the automatic addition of column AUTO_INCREMENT attributes.

In general, InnoDB is a good choice if transaction support is required and there is a high frequency of concurrent reads.

MEMORY

The starting point for using the MySQL Memory storage engine is speed. In order to get the fastest response time, the logical storage medium used is system memory. Although storing table data in memory does provide high performance, when the mysqld daemon crashes, all Memory data is lost. The acquisition of speed also brings some defects. It requires that the data stored in the Memory data table is in a fixed-length format, which means that variable-length data types such as BLOB and TEXT cannot be used. VARCHAR is a variable-length type, but it can be used within MySQL as a fixed-length CHAR type.

The Memory storage engine is generally used in the following situations:

1. The target data is small and is accessed very frequently. Store data in memory, so it will cause the use of memory. You can control the size of the Memory table through the parameter max_heap_table_size. By setting this parameter, you can limit the maximum size of the Memory table.

two。 If the data is temporary and requires it to be available immediately, it can be stored in a memory table.

3. If the data stored in the Memory table is suddenly lost, it will not have a substantial negative impact on the application service.

Memory supports both hash indexes and B-tree indexes. The advantage of B-tree index over hash index is that partial query and wildcard query can be used, and operators such as and > = can be used to facilitate data mining. The "equality comparison" of the hash index is very fast, but the "range comparison" is much slower, so the hash index value is suitable for use in the = and operator, not in the operator, nor in the order by clause.

You can use the USING clause to specify the version to use when the table is created. For example:

The copy code is as follows:

Create table users

(

Id smallint unsigned not null auto_increment

Username varchar (15) not null

Pwd varchar (15) not null

Index using hash (username)

Primary key (id)

) engine=memory

The above code creates a table that uses the HASH hash index on the username field. The following code creates a table using the BTREE index.

The copy code is as follows:

Create table users

(

Id smallint unsigned not null auto_increment

Username varchar (15) not null

Pwd varchar (15) not null

Index using btree (username)

Primary key (id)

) engine=memory

MERGE

The MERGE storage engine is a combination of MyISAM tables that must be identical in structure, and although their use is not as prominent as other engines, it is useful in some cases. To put it bluntly, the Merge table is the aggregator of several identical MyISAM tables; there is no data in the Merge table, and you can query, update, and delete tables of type Merge, which are actually operations on the internal MyISAM tables. Usage scenario of the Merge storage engine.

For information such as server logs, a common storage strategy is to divide the data into many tables, each name associated with a specific time end. For example, you can use 12 identical tables to store server log data, each named after each month. When it is necessary to generate reports based on data from all 12 log tables, this means that multiple table queries need to be written and updated to reflect the information in those tables. Instead of writing these possible error queries, combine these tables and use a query, and then delete the Merge table without affecting the original data. Deleting the Merge table only removes the definition of the Merge table and has no effect on the internal table.

ARCHIVE

Archive means archiving. After archiving, many advanced functions are no longer supported, only the most basic insert and query functions are supported. Indexing was not supported in Archive before MySQL version 5.5, but it has been supported since MySQL version 5.5. Archive has a good compression mechanism. It uses the zlib compression library and compresses records in real time when requested, so it is often used as a repository.

Some problems with storage engine

1. How do I see which storage engines are available on the server?

To determine which storage engines are available for your MySQL server, execute the following command:

The copy code is as follows:

Show engines

This order can be done.

two。 How to choose the right storage engine?

(1) the selection criteria can be divided into:

(2) whether it is necessary to support transactions

(3) whether hot backup is required

(4) crash recovery: whether it is acceptable to collapse.

(5) whether foreign key support is required

Then, according to the standard, select the corresponding storage engine.

At this point, the study on the "introduction to the functions of 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.