MySql storage engine Introduction

MySql storage engine Introduction

MySQL5.5 and later are used by defaultInnoDBThe storage engine. InnoDB and BDB provide transaction security tables. 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: show variables like 'default _ storage_engine '; to view the current database to the default engine. Command:Show enginesAndShow variables like 'have %'You 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, The INFORMATION_SCHEMA database has an ENGINES table, which provides the same information as the show engines statement. You can use the following statement to query which storage ENGINES Support transaction processing: select engine from information_chema.engines where transactions = '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, useEngine =...OrType =...To specify the engine to be used.Show table status from DBnameTo 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 data directory And index directory 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 check table statement to CHECK the health of MyISAM tables and use the repair table 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 optimize table 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
　　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 "alter table... 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, set null, and no action. The restrict and no action are the same, 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. set null indicates that the field corresponding to the sub-table is set null 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 set foreign_key_checks = 0; temporarily disable the foreign key constraint, set foreign_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 order by 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.

create table tab_memory engine=memory select id,name,age,addr from man order by id;

Use using hash/BTREE to specify specific indexes.

create index mem_hash using hash on tab_memory(city_id);

When starting the MySQL service, use the -- init-file option to insert... when a statement such as select or load data infile 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, delete from or truncate table 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.
(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 you must refresh the table with flush.

create table man_all(id int,name varchar(20))engine=merge union=(man1,man2) insert_methos=last;