MySQL Storage engine

Preface

In the database is a sheet of inextricably linked tables, so the table design is good or bad, will directly affect the entire database. While designing the table, we will focus on one issue and what storage engine to use. Wait a minute, storage engine? What is a storage engine?

what is a storage engine?

Relational database tables are data structures for storing and organizing information, and you can interpret tables as tables of rows and columns, similar to the form of spreadsheets in Excel. Some tables are simple, some tables are complex, some tables do not have to store any long-term data, some tables are read very fast, but when inserting data is very poor, and we in the actual development process, we may need a variety of tables, different tables, it means the storage of different types of data, data processing will also exist differences, then. For MySQL, it offers many types of storage engines, and we can take advantage of MySQL's powerful capabilities by choosing different storage engines based on the need for data processing.

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.

InnoDB is the default storage engine for MySQL

operation of MySQL

• Show MySQL-supported operations engine

show engines;

• Show the structure of a table

DESC sina_oauthuser

• When creating a table, specify the storage engine

Or:

Create table tableName(columnName(列名1)  type(数据类型)  attri(属性设置),columnName(列名2)  type(数据类型)  attri(属性设置),……..) engine = engineName

• View the storage engine for a table

SHOW CREATE TABLE sina_cookie

• View the current status of a table

show table status like ‘sina_cookie‘

• Modify the table's storage engine

Alter table tableName engine =engineName

MyISAM

In the MySQL client, use the following command to view the MySQL-supported engine.

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; whenever we build a table for a MyISAM engine, we build three files on the local disk, and the file name is the table name. For example, if I build a tb_demo table for the MyISAM engine, the following three files will be generated:

TB_DEMO.FRM, storage table definition;
Tb_demo. MYD, storing data;
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:

• Select the intensive table. The MyISAM storage engine is very fast in filtering large amounts of data, which is its most prominent advantage.
• Inserts a dense table. 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:

• Updates a dense table. The InnoDB storage engine is ideal for handling multiple concurrent update requests.
• Transaction. The InnoDB storage engine is a standard MySQL storage engine that supports transactions.
• Automated Disaster recovery. Unlike other storage engines, the InnoDB table can automatically recover from a disaster.
• FOREIGN KEY constraints. MySQL supports the foreign key storage engine only InnoDB.
• Supports automatic increment of column Auto_increment property.

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:

• 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.
• If the data is temporary and needs to be immediately available, it can be stored in the memory table.
• 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 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.

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

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 code above creates a table that uses a hash hash index on the username field. The following code creates a table that uses the Btree index.

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 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.

Here's a simple example of how to build a table with the engine as a merge type.

create table tb_log1(    id int unsigned not null auto_increment,     log varchar(45),    primary key(id)) engine=myisam;

 Insert  into Tb_log1 (log) values(' tb_log1_1 '); Insert  into Tb_log1 (log) values(' tb_log1_2 '); Insert  into Tb_log1 (log) values(' tb_log1_3 '); Insert  into Tb_log1 (log) values(' tb_log1_4 '); Insert  into Tb_log1 (log) values(' tb_log1_5 ');

create  table  tb_log2 (id int  unsigned not  null  auto_increment, log  varchar  (45 ), primary  key  (ID)) Engine=myisam;   

insert  into  tb_log2 (log) values  ( );  insert  into  tb_ log2 (log) values  ( ' tb_log2_2 ' );  insert  into  tb_ log2 (log) values  ( ' tb_log2_3 ' );  insert  into  tb_ log2 (log) values  ( ' tb_log2_4 ' );   

First create a table of two engines for MyISAM (must be the MyISAM engine). Insert the above data, and then create the merge table for the merge operation.

create table tb_merge(    id int unsigned not null auto_increment,     log varchar(45),     primary key(id))engine=merge     union(tb_log1,tb_log2) insert_method=last;

This gives a table with the engine merge and merges the TB_LOG1 and tb_log2 two tables. To query the Tb_merge table, you can get the following data:

+----+-----------+| ID | log |  +----+-----------+|  1  | tb_log1_1 |  |  2  | tb_log1_2 |  |  3  | tb_log1_3 |  |  4  | tb_log1_4 |  |  5  | tb_log1_5 |  |  1  | tb_log2_1 |  |  2  | tb_log2_2 |  |  3  | tb_log2_3 |  | 4  | tb_log2_4 |  +----+-----------+

Now we will mainly explain the table statement of the merge table above.

• Engine=merge

Indicate that using the merge engine, some students may see the example of Engine=mrg_myisam, is also right, they are one thing.

• union= (T1, T2)

Indicates which tables are hooked up in the merge table, and you can modify the union value by ALTER TABLE to implement the ability to delete and remove the Merge table sub-table. Like what:

alter table tb_merge engine=merge union(tb_log1) insert_method=last;

• Insert_method=last

Insert_method indicates the insertion method, the value can be: 0 is not allowed to insert, first inserted into the Union in a table, last inserted into the final table in the Union.

• The merge table and the member data tables that comprise the merge data table structure must have exactly the same structure. The data columns of each member data table must define the same name and type in the same order, and the index must be defined in the same order and in the same way.

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

• 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: show engines; This command will be done.

• How do I choose the right storage engine?

Selection criteria can be divided into:

(1) Whether support services are required;
(2) Whether it is necessary to use hot standby;
(3) Crash recovery: can accept crashes;
(4) Whether foreign key support is required;

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

• InnoDB and MyISAM

https://www.pureweber.com/article/myisam-vs-innodb/

MySQL Storage engine