MySQL Storage engine

What is a MySQL database

Typically, a database is a collection of data, and a database can be a collection of files on a memory or a collection of some memory data.
What we usually call MySQL database, SQL Server database and so on is actually a database management system, they can store data, and provide the ability to query and update data in the database, and so on. Depending on how the database stores the data and how the data is implemented, there are differences and similarities between the databases.
The MySQL database is a relational database of open source code. Currently, it can provide features such as support for SQL language, subqueries, stored procedures, triggers, views, indexes, transactions, locks, foreign key constraints, and image replication. In the late stages, we will explain these features in detail.
As with large database systems such as Oracle and SQL Server, MySQL is also a database of client/server systems and single-process multithreaded architectures.
One important feature of MySQL that differs from other database systems is the support for the plug-in storage engine.

So what is a storage engine?

The storage engine is how to store the data, how to index the stored data, and how to update and query the data. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, the type that stores and operates this table).
There is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. the MySQL database provides a variety of storage engines. Users can choose different storage engines for the data table according to different requirements, and users can write their own storage engine according to their own needs.
What storage engines are in MySQL?
  1 MyISAM: This engine was first provided by MySQL. This engine can also be divided into static MyISAM, dynamic MyISAM and compression MyISAM three kinds:
static MyISAM: If the length of each data column in the datasheet is pre-fixed, the server will automatically select this type of table. Because each record in the data table occupies the same amount of space, the table accesses and updates are highly efficient. When data is compromised, recovery is easier to do.
Dynamic MyISAM: If the varchar, xxxtext, or Xxxblob fields appear in the datasheet, the server will automatically select this type of table. Compared with static MyISAM, this kind of table storage space is relatively small, but because of the length of each record is different, so the data in the data table can be stored in memory after multiple modifications, resulting in a decrease in execution efficiency. Also, there may be a lot of fragmentation in memory. Therefore, this type of table is often defragmented with the Optimize table command or the Optimization tool.
compression MyISAM: The two types of tables mentioned above can be compressed with the Myisamchk tool. This type of table further reduces the amount of storage consumed, but the table can no longer be modified after it is compressed. In addition, because it is compressed data, such a table should be read to the first time to extract the rows.
However, regardless of the MyISAM table, it does not currently support transactional, row-level, and foreign key constraints.
2 MyISAM Merge engine: This type is a variant of the MyISAM type. Merging tables is the merging of several identical MyISAM tables into a single virtual table. Often applied to logs and data warehouses.
3 InnoDB:The InnoDB table type can be thought of as a further update to the MyISAM product, which provides the functionality of transaction, row-level locking mechanisms, and foreign key constraints.
4 Memory (heap): This type of data table only exists in memory. It uses a hash index, so the data is accessed very quickly. because it exists in memory, this type is often applied to temporary tables.
5 Archive:This type only supports SELECT and INSERT statements, and does not support indexing. Often applied to logging and aggregation analysis.
of course, MySQL supports more than just a few types of tables.

Let's look at how to view and set the data table type.

Show ENGINES--View storage engine

DESC TableName--View table structure

Show CREATE TABLE Tablesname--Display the creation statement

Show table status like ' tablename '--Displays the current state value of the table

Modify the storage engine, you can use the command ALTER TABLE tableName engine =enginename

The MyISAM table is independent of the operating system, which means that it can be easily ported from a Windows Server to a Linux server, and every time we build a table for a MyISAM engine, three files are created on the local disk, which is indicated by the file name. For example, if I build a tb_demo table for the MyISAM engine, the following three files will be generated:

1.TB_DEMO.FRM, storage table definition;
2.tb_demo. MYD, storing data;
3.tb_demo. MYI, stores the index.

The MyISAM table cannot handle transactions, which means that there is a table for transaction processing requirements and cannot use the MyISAM storage engine. The MyISAM storage engine is ideal for use in the following situations:

1. Select the intensive table. The MyISAM storage engine is very fast in filtering large amounts of data, which is its most prominent advantage.
2. Insert intensive tables. The concurrent Insert feature of MyISAM allows data to be selected and inserted at the same time. For example, the MyISAM storage engine is ideal for managing mail or Web server log data.

InnoDB

InnoDB is a robust transactional storage engine that has been used by many Internet companies to provide a powerful solution for users to operate very large data stores. 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, which are ideal for use with InnoDB in the following situations:

1. Update the dense table. The InnoDB storage engine is ideal for handling multiple concurrent update requests.
2. Transactions. The InnoDB storage engine is a standard MySQL storage engine that supports transactions.
3. Automatic disaster recovery. Unlike other storage engines, the InnoDB table can automatically recover from a disaster.
4. Foreign KEY constraints. MySQL supports the foreign key storage engine only InnoDB.
5. Support automatic increment of column auto_increment attribute.

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. To get the fastest response time, the logical storage medium used is system memory. Although storing table data in memory does provide high performance, all memory data will be lost when the mysqld daemon crashes. The speed of the acquisition also brings some drawbacks. It requires that the data stored in the Memory data table use a constant length format, which means that a variable length data type such as BLOB and text cannot be used, and varchar is a variable-length type, but because it is a fixed-length char type within MySQL, So it can be used.

Memory storage engines are typically used in the following situations:

1. The target data is small and is accessed very frequently. Storing the data in memory, so it will cause the use of memory, can be controlled by the parameter max_heap_table_size the memory table size, set this parameter, you can limit the memory table maximum size.

2. If the data is temporary and required to be immediately available, it can be stored in the memory table.

3. Data stored in the memory table, if suddenly lost, will not have a substantial negative impact on the app service.

Memory supports both hash index and B-Tree index. The B-Tree index is better than the hash index, you can use partial queries and wildcard queries, or you can use operators such as <, >, and >= to facilitate data mining. Hash indexes are very fast for equality comparisons, but are much slower for range comparison, so the hash index values are suitable for use in operators of = and <>, not in the < or > operators, nor in the ORDER BY clause.

MERGE

The merge storage engine is a combination of a set of MyISAM tables that must be identical in structure, although their use is not as prominent as other engines, but is useful in some cases. To be blunt, the merge table is just a few aggregators of the same MyISAM table, and there is no data in the merge table, and the merge type table can be queried, updated, deleted, and actually operated on the internal MyISAM table. The usage scenario for the merge storage engine.

For server logs, the most common storage strategy is to divide the data into tables, each associated with a specific time-end. For example, you can use 12 identical tables to store server log data, each named by the name of each month. When it is necessary to generate a report 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 queries that may have errors, instead of merging the tables with a single query and then deleting the merge table without affecting the original data, deleting the merge table simply removes the definition of the merge table and has no effect on the internal table.

ARCHIVE

Archive is archived, many of the advanced features are no longer supported after archiving, supporting only the most basic insert and query functions. Prior to MySQL version 5.5, archive did not support indexing, but it began to support indexing in MySQL 5.5. Archive has a good compression mechanism, which uses the Zlib compression library, which is compressed in real time when the record is requested, so it is often used as a repository.

Some problems with the storage engine

1. How can I see which storage engines are available for the server?
To determine which storage engines your MySQL server can use, execute the following command:

Copy the Code code as follows:

Show engines;

This command will take care of it.

2. How do I choose the right storage engine?
(1) Selection criteria can be divided into:
(2) Whether support services are required;
(3) Whether it is necessary to use hot standby;
(4) Crash recovery: can accept crashes;
(5) Whether foreign key support is required;
Then, according to the standard, select the corresponding storage engine.

MySQL Storage engine