Detailed introduction to MySQL storage engine

　What is a storage engine?

A relational database table is a data structure used to store and organize information. A table can be understood as a table composed of rows and columns, similar to the form of an Excel spreadsheet. Some tables are simple, some are complex, some tables are not used to store any long-term data at all, and some tables are very fast to read, but very poor when inserting data; in the actual development process, we may need Various tables, different tables, mean that different types of data are stored, and there will be differences in data processing. For MySQL, it provides many types of storage engines. We can choose different storage engines according to our data processing needs, so as to maximize the use of MySQL's powerful functions. This blog post will summarize and analyze the characteristics of each engine and its applicable situations, and will not get entangled in deeper things. My learning method is to first learn to use it, know how to use it, and then find out how to use it. The following is a brief introduction to the storage engines supported by MySQL.

MySQL 5.5 and later uses the InnoDB storage engine by default. InnoDB and BDB provide transaction-safe tables, and other storage engines are non-transaction-safe 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. Commands: showengines and showvariableslike'have%' can list the engines supported by the current database. Records in which Value is displayed as disabled indicate that the database supports this engine and is disabled when the database is started. After MySQL5.1, there is an ENGINES table in the INFORMATION_SCHEMA database. The information it provides is exactly the same as the 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 the database.

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

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

　(1)MyISAM

It does not support transactions or foreign keys, especially applications with fast access speed, no requirements for transaction integrity, or applications based on SELECT and INSERT. Tables can be created using this engine.

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

.frm (storage table definition)

MYD (MYData, storage data)

MYI (MYIndex, storage index)

Data files and index files can be placed in different directories to evenly distribute IO and obtain faster speeds. To specify the path of the data file and index file, you need to specify it through the DATADIRECTORY and INDEXDIRECTORY statements when creating the table. The file path needs to use an absolute path.

Each MyISAM table has a flag. The server or the myisamchk program will set this flag when checking the MyISAM data table. The MyISAM table also has a flag to indicate whether the data table was closed normally after the last use. If the server is down or crashed, this flag can be used to determine whether the data table needs to be checked and repaired. If you want this check to happen automatically, you can use the --myisam-recover phenomenon when starting the server. This will cause the server to automatically check the table's flags every time it opens a MyISAM data table and make necessary repairs. MyISAM type tables may be damaged. You can use the CHECKTABLE statement to check the health of the MyISAM table, and use the REPAIRTABLE statement to repair a damaged MyISAM table.

MyISAM tables also support 3 different storage formats:

Static (fixed length) table

Dynamic table

Compressed table

　Static table is the default storage format. The fields in static tables are all non-variable length fields, so each record has a fixed length. The advantages of this storage method are that it is stored very quickly, is easy to cache, and is easy to recover from failures. The disadvantage is that it usually takes up more space than a dynamic table. . Static tables will fill in spaces according to the width definition of column definitions when data is stored, but these spaces will not be obtained when accessed. These spaces have been removed before being returned to the application. At the same time, please note: In some cases, it may be necessary to return spaces after the field, and when using this format, subsequent spaces will be automatically processed.

Dynamic tables contain variable-length fields and records are not of fixed length. The advantage of such storage is that it takes up less space. However, frequent updates and deletions of records will cause fragmentation. You need to execute the OPTIMIZETABLE statement or the myisamchk-r command regularly. Improves performance and makes recovery from failures relatively difficult.

The compressed table is created by the myisamchk tool and occupies a very small space. Because each record is compressed individually, there is only a very small access overhead.

　(2)InnoDB

InnoDB is a robust transactional storage engine. This storage engine has been used by many Internet companies and provides a powerful tool for users to operate very large data storage. solution. 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. In the following situations, using InnoDB is the most ideal choice:

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

　2. Affairs. The InnoDB storage engine is a 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 constraints. The only storage engine that supports foreign keys in MySQL is InnoDB.

　5. Support automatically adding column AUTO_INCREMENT attribute.

The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with MyISAM's storage engine, InnoDB's write processing efficiency is less efficient and takes up more disk space to retain data and indexes.

　1) Automatic growth column:

The automatic growth column of the InnoDB table can be inserted manually, but if the inserted value is empty or 0, the actual value inserted is the value after automatic growth. The starting value of the automatic growth value can be forcibly set through the "ALTERTABLE...AUTO_INCREMENT=n;" statement. The default value is 1, but the forced default value is stored in memory and will be lost after the database is restarted. You can use LAST_INSERT_ID() to query the value used by the current thread to insert the record last time. If multiple records are inserted at one time, the auto-increment value used by the first record is returned.

For InnoDB tables, the auto-growing column must be an index. If it is a composite index, it must also be the first column of the composite index. However, for MyISAM tables, the automatic growth column can be other columns of the composite index. In this way, after inserting a record, the automatic growth column is incremented after sorting the previous columns of the composite index. of.

2) Foreign key constraints:

The only storage engine that supports foreign keys in MySQL is InnoDB. When creating a foreign key, the parent table must have a corresponding index, and the child table must have a corresponding index when creating a foreign key. Corresponding indexes will also be automatically created.

When creating an index, you can specify the corresponding operations on the child table when deleting or updating the parent table, including restrict, cascade, setnull and noaction. Among them, restrict and noaction are the same, which means that the parent table cannot be updated when the child table is related; casecade means that when the parent table is updated or deleted, the records corresponding to the child table are updated or deleted; setnull means that the parent table is updated or deleted. When deleting, the corresponding field of the subtable is setnull.

When a table is referenced by a foreign key created by another table, the corresponding index or primary key of the table is prohibited from being deleted.

You can use setforeign_key_checks=0; to temporarily turn off foreign key constraints, and setforeign_key_checks=1; to turn on constraints.

　(3)MEMORY

Memory uses the contents stored in memory to create tables. Each MEMORY table actually corresponds to a disk file, the format is .frm. MEMORY type table access is very fast because the data is stored in memory and HASH indexes are used by default. However, once the server is shut down, the data in the table will be lost, but the table will continue to exist.

By default, memory data tables use hash indexes. Using this index to perform "equality comparison" is very fast, but "range comparison" is much slower. Therefore, the hash index value is suitable for use in the "=" and "" operators, but is not suitable for use in the "" operators, and is also not suitable for use in the orderby clause. inside. If you really want to use the "" or betwen operators, you can use a btree index to speed things up.

The data rows stored in the MEMORY data table use a constant-length format, thus speeding up processing. This means that variable-length data types such as BLOB and TEXT cannot be used. VARCHAR is a variable-length type, but because it is treated internally by MySQL as a fixed-length CHAR type, it can be used.

Createtabletab_memoryengine=memoryselectid,name,age,addrfrommanorderbyid;

Use USINGHASH/BTREE to specify the index.

　createindexmem_hashusinghashontab_memory(city_id);

Use the --init-file option when starting the MySQL service and put statements like insertinto...select or loaddatainfile into this file. Tables can be loaded from a durable data source when the service starts.

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

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. At the same time, 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 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, when the mysqld daemon crashes, all Memory data is lost. With the speed gained comes some drawbacks. It requires that the data stored in the Memory data table use a constant-length format, which means that variable-length data types such as BLOB and TEXT cannot be used. VARCHAR is a variable-length type, but because it is MySQL internally treats it as a CHAR type with a fixed length, so it can be used.

　(4)MERGE

The merge storage engine is a combination of a set of MyISAM tables. The structures of these MyISAM tables must be exactly the same. There is no data in the MERGE table. You can query the MERGE type table. , update, and delete operations. These operations are actually operations on the internal MyISAM table. For the insert operation into the MERGE table, the inserted table is defined according to the INSERT_METHOD clause. It can have three different values. The first and last values ​​make the insert operation act on the first or last table accordingly. Define this clause or set it to NO, indicating that the MERGE table cannot be inserted. You can perform a drop operation on the MERGE table. This operation only deletes the definition of the MERGE table and has no impact on the internal tables. MERGE retains two files starting with the MERGE table name on the disk: the .frm file stores the definition of the table; the .MRG file contains information about the combined table, including which tables the MERGE table is composed of, and the basis for inserting data. The MERGE table can be modified by modifying the .MRG file, but it must be refreshed through flushtable after modification.

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

The above is the detailed content of Detailed introduction to MySQL storage engine. For more information, please follow other related articles on the PHP Chinese website!