(5) mysql common storage engine _ MySQL

Overview ?? Before MySQL 55, the default storage engine was MyISAM, and then changed to InnoDB. To modify the default engine, you can modify the default-storage-engine in the configuration file. You can use showengines to view the engines supported by the current database. Overview of using selectenginefro

?? Before mysql5.5, the default storage engine is MyISAM, and after 5.5, it is changed to InnoDB. To modify the default engine, you can modify the default-storage-engine in the configuration file. You can use show engines to view the engines supported by the current database. Use select engine from information_schema.engines where transactions = 'yes'; to check which engines support transactions. When creating a table, use engine =... or type =... to specify the engine to be used.

MyISAM It does not support transactions or foreign keys. Its advantage is that the access speed is fast.The engine can be used to create tables for applications that do not require transaction integrity or that use select/insert as the primary engine.

Each MyISAM has three files on the disk. the file name is the same as the table name, but the extension is:

. Frm (table definition ). MYD (MYDate: Data storage ). MYI (MYIndex: Storage index ). MYD file and. MYI files can be placed in different directories and specified through the data directory and index directory statements. 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 supports three different storage formats:

Static (fixed length) table dynamic table compression table

?? In a static table, if the content to be saved is followed by spaces, the public content will be removed when the results are returned.
?? In a dynamic table, the record is not fixed. the advantage is that the occupied space is relatively small. The disadvantage is that frequent updates and deletion of records will produce fragments, so you need to regularly execute optimize table to improve performance.
?? The compressed table is created by myisampack, which occupies a very small disk space. Because each record is compressed separately.

InnoDB InnoDB supports transaction securityCompared with the MyISAM engine, InnoDB writes less efficiently and occupies more disk space. InnoDB auto-increment columns can be inserted manually, but if the inserted value is null or 0, the actually inserted value will be automatically increased. You can use last_insert_id () to query the value of the last inserted record of the current thread. You can use the alert table *** auto_increment = n; statement to forcibly set the automatic growth value. 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. MySQL only supports the InnoDB storage engine.When creating a foreign key, the parent table must have the corresponding index, and the child table will automatically create the corresponding index when creating the foreign key. 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.

There are two ways to store tables and indexes in InnoDB:

Use shared tablespace storage. Use multi-tablespace storage. MEMORY Memory uses 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. 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. Memory is mainly used for code tables with less frequent changes in content, or as an intermediate result table for statistical operations. MERGE The merge storage engine is a combination of MyISAM tables., These MyISAM tables must have the same structure, and there is 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. The difference between a merge table and a partition table is that a merge table does not intelligently write records to the corresponding table, but a partition table does. Summary

Still a table as a summary: