Four main storage engines of MySQL and four engines of mysql

Four main storage engines of MySQL and four engines of mysql

A table is stored in a database that is closely related to each other. Therefore, the design of a table directly affects the entire database. When designing a table, we will focus on the storage engine used. Wait, storage engine? What is a storage engine?

What is a storage engine?

A relational database table is a data structure used to store and organize information. It can be understood as a table composed of rows and columns, similar to a workbook in Excel. Some tables are simple, some tables are complex, and some tables do not need to store any long-term data at all. Some tables read very quickly, but are poorly inserted; in the actual development process, we may need a variety of tables. Different tables mean that different types of data are stored and data processing is also different. For MySQL, it provides many types of storage engines. We can select different storage engines based on the data processing requirements to maximize the use of MySQL's powerful functions. This blog post will summarize and analyze the characteristics of each engine, as well as its applicability, and will not be entangled in deeper things. My learning method is to learn how to use it first, know how to use it, and then know how to use it. The following describes the storage engines supported by MySQL.

MySQL5.5 and later use the InnoDB Storage engine by default. InnoDB and BDB provide transaction security tables, and other storage engines are non-transaction security tables.

To modify the default engine, you can modify the default-storage-engine in the configuration file. You can use: showvariableslike 'default _ storage_engine '; to view the current database to the default engine. Command: showengines and showvariableslike 'have % 'can list the engines supported by the current database. The Value indicates that the database supports this engine and is disabled when the database is started. After MySQL5.1, an ENGINES table exists in the INFORMATION_SCHEMA database, which provides the same information as showengines; statement. You can use the following statement to query which storage ENGINES Support transaction processing: selectenginefrominformation_chema.engineswheretransactions = 'yes ';

You can use the engine keyword to specify the engine used when creating or modifying a database.

Main storage engines: MyISAM, InnoDB, MEMORY, and MERGE:

When creating a table, use engine =... or type =... to specify the engine to be used. ShowtablestatusfromDBname to view the specified table to the engine.

(1) MyISAM

It does not support transactions, nor does it support foreign keys, especially the fast access speed. It does not require transaction integrity or can be used to create tables for applications dominated by SELECT and INSERT.

Each MyISAM is stored as three files on the disk. The file names and table names are the same, but the extensions are:

. Frm (storage table definition)

MYD (MYData, data storage)

MYI (MYIndex, storage index)

Data Files and index files can be placed in different directories, and I/O is evenly allocated to get faster speed. To specify the path of the data file and index file, you must use the DATADIRECTORY and INDEXDIRECTORY statements when creating the table. The absolute path must be used for the file path.

Each MyISAM table has a flag. The server or myisamchk Program sets this flag when checking the MyISAM data table. The MyISAM table also has a flag to indicate whether the data table was normally disabled after it was used last time. If the server thinks it is a crash, this flag can be used to determine whether the data table needs to be checked and repaired. If you want this check to be performed automatically, you can use -- myisam-recover when starting the server. This will enable the server to automatically check and fix a MyISAM data table each time. MyISAM tables may be damaged. You can use the CHECKTABLE statement to check the health of MyISAM tables and use the REPAIRTABLE statement to repair a damaged MyISAM table.

MyISAM tables also support three different storage formats:

Static (fixed length) Table

Dynamic table

Compressed table

The static table is the default storage format. Fields in a static table are non-variable-length fields, so that each record has a fixed length. The advantage of this storage method is that it is very fast to store, easy to cache, and easy to recover when a fault occurs; the disadvantage is that it usually occupies more space than dynamic tables. When data is stored in a static table, spaces are supplemented according to the width definition of the column, but these spaces are not obtained during access. These spaces are removed before they are returned to the application. At the same time, note: In some cases, the space after the field may be returned. When this format is used, the Space following it will be automatically processed.

A dynamic table contains variable-length fields and records do not have a fixed length. In this way, the storage space is relatively small, but fragments are generated when records are updated or deleted frequently, you need to regularly execute the OPTIMIZETABLE statement or myisamchk-r command to improve performance, and it is relatively difficult to recover when a fault occurs.

The compressed table is created by the myisamchk tool, which occupies a very small space, because each record is compressed separately, so there is only a small access cost.

(2) 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 5.6.13 installed on my computer, InnoDB is used as the default storage engine. InnoDB also introduces row-level locking and foreign key constraints. InnoDB is the best choice in the following scenarios:

1. Update intensive tables. The InnoDB Storage engine is particularly suitable for processing 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, InnoDB tables can be automatically recovered from disasters.

4. Foreign key constraints. MySQL only supports the InnoDB Storage engine.

5. The AUTO_INCREMENT attribute can be automatically added.

The InnoDB Storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with the storage engine of MyISAM, InnoDB writes less efficiently and occupies more disk space to retain data and indexes.

1) Auto-increment column:

The automatic growth column of the InnoDB table can be inserted manually. However, if the inserted column is null or 0, the actual inserted column is automatically inserted to the value after the insertion. You can use "ALTERTABLE... AUTO_INCREMENT = n; "The statement forces you to set the starting value of the automatic growth value. The default value is 1, but this value is saved in the memory by default. This value will be lost after the database is restarted. You can use LAST_INSERT_ID () to query the value of the last inserted Record of the current thread. If multiple records are inserted at a time, the returned value is the automatic growth value used by the first record.

For an InnoDB table, the auto-increment column must be an index. For a composite index, it must also be the first column of the composite index. For a MyISAM table, the auto-increment column can be another column of the composite index, auto-increment columns are sorted by composite index to the first few columns and then incremented.

2) foreign key constraints:

MySQL only supports the InnoDB Storage engine for Foreign keys. When creating foreign keys, the parent table must have corresponding indexes, when a sub-Table creates a foreign key, the corresponding index is automatically created.

When creating an index, you can specify operations for the child table When deleting or updating the parent table, including restrict, cascade, setnull, and noaction. Restrict is the same as noaction, which means that the parent table cannot be updated when the sub-table is associated. casecade indicates that when the parent table is updated or deleted, update or delete the records corresponding to the sub-Table. setnull indicates that the fields corresponding to the sub-table are setnull when the parent table is updated or deleted.

When a table is created with a foreign key reference by another table, the index or primary key of the table cannot be deleted.

You can use setforeign_key_checks = 0; temporarily disable the foreign key constraint, setforeign_key_checks = 1; open the constraint.

(3) MEMORY

Memory uses the content in memory to create a table. Each MEMORY table corresponds to a disk file in the format of. frm. MEMORY tables can be accessed very quickly because they store data in the MEMORY and use HASH indexes by default. However, once the server is disabled, data in the table will be lost, but the table will continue to exist.

By default, memory data tables use Hash indexes. Using these indexes for "equal comparison" is very fast, but the speed for "range comparison" is much slower. Therefore, hash index values are suitable for operators "=" and "<=>", but not "<" or ">, it is also not suitable for use in orderby statements. If you really want to use the "<" or ">" or betwen operator, you can use the btree index to speed up the process.

The data rows stored in the MEMORY data table use a format with the same length. Therefore, the processing speed is accelerated, which means that the variable-length data types such as BLOB and TEXT cannot be used. VARCHAR is a variable-length type, but it can be used because it is regarded as a CHAR type with Fixed Length in MySQL.

Createtabletab_memoryengine = memoryselectid, name, age, addrfrommanorderbyid;

Use USINGHASH/BTREE to specify specific indexes.

Createindexmem_hashusinghashontab_memory (city_id );

When starting the MySQL service, use the -- init-file option to set insertinto... when a statement such as select or loaddatainfile is put into this file, you can load the table from a persistent and stable data source at service startup.

The server needs enough MEMORY to maintain the MEMORY table used at the same time. When the MEMORY table is no longer used, the MEMORY occupied by the MEMORY table should be released, you should execute DELETEFROM or truncatetable or delete the entire table.

The size of data stored in each MEMORY table is limited by the max_heap_table_size system variable. The initial value of this system variable is 16 Mb, when creating a MEMORY table, you can use the MAX_ROWS clause to specify the maximum number of rows in the table.

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

(4) MERGE

The merge storage engine is a combination of MyISAM tables. These MyISAM tables must have the same structure and have no data in the MERGE table. You can query, update, and delete tables of the MERGE type, these operations are actually performed on the internal MyISAM table. The insert operation on the MERGE table is based on the inserted Table defined by the INSERT_METHOD clause. It can have three different values, the values of first and last make the insert operation take effect on the first or last table. If this clause is not defined or NO, insertion cannot be performed on the MERGE table. You can perform the drop operation on the MERGE table. This operation only deletes the definition of the MERGE table and has no impact on the internal table. MERGE retains two files starting with the MERGE table name on the disk :. definition of the frm file storage table ;. the MRG file contains information about the combined tables, including which tables are composed of MERGE tables and the basis for data insertion. You can modify the. MRG file to modify the MERGE table, but refresh the table with flushtable after modification.

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