Four main storage engines for MySQL

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. This blog post will summarize and analyze the features of each engine, as well as the application scenarios, and will not be entangled in deeper things. My Learning method is to learn to use, know how to use, and then to understand how it can be used. The following is a brief introduction to the MySQL-supported storage engine.

MySQL5.5 later uses the InnoDB storage engine by default, where InnoDB and BDB provide transaction-safe tables, and other storage engines are non-transactional security tables.

To modify the default engine, you can modify the Default-storage-engine in the configuration file. Can be by: showvariableslike ' default_storage_engine '; View the current database to the default engine. Commands: Showengines and Showvariableslike ' have% ' can list the engines that the current database supports. A record where value is displayed as disabled indicates that the database supports this engine and is disabled when the database is started. After MySQL5.1, there is a engines table in the INFORMATION_SCHEMA database that provides information that is identical to the showengines; You can use the following statement to query which storage engines support the processing of things: selectenginefrominformation_chema.engineswheretransactions= ' yes ';

You can specify the engine that you use when you create or modify a database by using the engine keyword.

Main storage Engine: MyISAM, InnoDB, Memory and merge introduction:

When creating a table by engine= ... Or type= ... To specify what you want to use to the engine. Showtablestatusfromdbname to view the specified table to the engine.

A MyISAM

It does not support transactions, does not support foreign keys, especially fast access, there is no requirement for transactional integrity, or a SELECT, insert-based application can basically use this engine to create tables.

Each myisam is stored as 3 files on disk, with the same file name and table names, but with the following extensions:

. frm (save table definition)

MYD (MYData, storing data)

MYI (myindex, storage index)

Data files and index files can be placed in different directories, evenly allocated Io, to get faster speeds. To specify the path to the data and index files, you need to specify the file path by using the DataDirectory and Indexdirectory statements when creating the table, which requires an absolute path.

Each MyISAM table has a flag that is set by the server or MYISAMCHK program when it checks the MyISAM data table. The MyISAM table also has a flag indicating whether the data table has been shut down properly since it was last used. This flag can be used to determine if a data table needs to be checked and repaired if the server thinks it is a crash or a machine. If you want this check to occur automatically, you can use the--myisam-recover behavior when you start the server. This causes the server to automatically check the data table flags and perform the necessary repair processing each time a MyISAM data table is opened. Tables of type MyISAM may be corrupted, you can use the CHECKTABLE statement to check the health of the MyISAM table, and fix a corrupt to MyISAM table with repairtable statements.

The MyISAM table also supports 3 different storage formats:

static (fixed-length) table

Dynamic tables

Compression table

Where the static table is the default storage format. The fields in the static table are non-variable length fields, so each record is fixed-length, the advantage of this storage method is that the storage is very fast, easy to cache, the fault is easy to recover, the disadvantage is that the space occupied is usually more than the dynamic table. A static table complements the spaces defined by the column definition when the data is stored, but does not receive these spaces when accessed, and these spaces are removed before they are returned to the app. It is also important to note that, in some cases, you may need to return a space after the field, and the following space will be automatically processed when using this format.

Dynamic tables contain variable-length fields, where records are not fixed lengths, so storage has the advantage of less space, but frequent updates to delete records can be fragmented, requiring regular execution of optimizetable statements or MYISAMCHK-R commands to improve performance, and recovery is relatively difficult in the event of a failure.

Compressed tables are created by the Myisamchk tool and occupy very little space because each record is individually compressed, so there is very little access expense.

Two 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. The MySQL5.6.13 version installed on my computer, 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.

The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared to the MyISAM storage engine, InnoDB writes are less efficient and consume more disk space to preserve data and indexes.

1) Auto-Grow column:

The auto-grow column of the InnoDB table can be inserted manually, but if it is empty or 0 inserted, then the actual insert is automatically increased to the value. Can be passed "altertable ... Auto_increment=n; " Statement to set the starting value of the autogrow value by default to 1, but the force to default value is saved in memory and the value is lost after the database restarts. You can use LAST_INSERT_ID () to query the value used by the last inserted record for the current thread. If you insert more than one record at a time, the auto-grow value used by the first record is returned.

For InnoDB tables, the autogrow column must be an index. If it is a composite index, it must also be the first column of the combined index, but for the MyISAM table, the autogrow column can be the other column of the combined index, so that when the record is inserted, the autogrow column is incremented by the combined index to the previous column sort.

2) FOREIGN KEY constraint:

MySQL supports foreign key storage engine only InnoDB, when creating foreign keys, the parent table must have a corresponding index, the child table when creating foreign keys will also automatically create the corresponding index.

When you create an index, you can specify the appropriate actions for the child table when the parent table is deleted, updated, including restrict, Cascade, SetNull, and Noaction. Where restrict and noaction are the same, it means that the parent table cannot be updated when the child table is associated, casecade means that the parent table updates or deletes the records corresponding to the child table when updating or deleting, and SetNull indicates that the parent table is updated or deleted. The fields corresponding to the child table are setnull.

When a table is created with a foreign key reference by another table, the table's corresponding index or primary key is forbidden from deletion.

You can use Setforeign_key_checks=0, temporarily turn off foreign key constraints, Setforeign_key_checks=1, and open constraints.

Three MEMORY

Memory creates a table using the content that exists in it. Each memory table actually corresponds to a disk file in the format of. frm. The memory type of table access is very fast because it goes to the data in memory, and the hash index is used by default, but once the server shuts down, the data in the table is lost, but the table continues to exist.

By default, the memory data table uses a hash index, which makes the "equality comparison" very fast, but the "range comparison" is much slower. Therefore, the hash index values are suitable for use in the "=" and "<=>" operators, not suitable for use in the "<" or ">" operators, also not applicable in the sentence. If you do want to use the < or > or the betwen operator, you can use the Btree index to speed up.

The data rows stored in the Memory data table use the same length format, thus speeding up processing, which means that you cannot use a variable length data type such as BLOB and text. VARCHAR is a variable-length type, but it can be used because it is treated as a fixed-length char type within MySQL.

Createtabletab_memoryengine=memoryselectid,name,age,addrfrommanorderbyid;

Use Usinghash/btree to specify a specific to index.

Createindexmem_hashusinghashontab_memory (city_id);

When you start the MySQL service, use the--init-file option to put a statement such as Insertinto...select or loaddatainfile into this file to load the table from a persistent data source when the service starts.

The server needs enough memory to maintain the memory table that is used at the same time, and when it is no longer used, it is necessary to perform deletefrom or truncatetable or delete the entire table in order to release the memories that are occupied by the storage table.

The size of the amount of data placed in each memory table is constrained by the max_heap_table_size system variable, the initial value of this system variable is 16M, and the max_rows clause can be used to specify the maximum number of rows in the table when creating the memory table.

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.

Four MERGE

The merge storage engine is a combination of a set of MyISAM tables in which the MYISAM table structure must be identical, there is no data in the merge table, operations on tables of the merge type can be queried, updated, deleted, and these operations are actually operations on the internal MyISAM table. For the insert operation on the merge table, which is the inserted table defined by the INSERT_METHOD clause, there can be 3 different values, first and last values allow the insert operation to be performed on the initial or final table, without defining this clause or no, Indicates that the merge table cannot be inserted. You can drop operations on the merge table, which simply removes the definition of the merge table and has no effect on the internal table. The merge retains 2 files that start with the merge table name: the definition of the. frm file storage table; the. mrg file contains information about the combined table, including which tables are composed of the merge table, and the basis for inserting the data. Can be modified by. MRG file to modify the merge table, but the modifications are to be refreshed by flushtable.

Createtableman_all (Idint,namevarchar ()) engine=mergeunion= (man1,man2) insert_methos=last;

Four main storage engines for MySQL