MySQL storage engine sorting and MySQL storage engine sorting

MySQL storage engine sorting and MySQL storage engine sorting
01.MyISAMDefault storage engine before MySQL 5.0. MyISAM does not support transactions or foreign keys. Its advantage is that the access speed is fast, you can use this engine to create tables for applications that do not require transaction integrity or that use SELECT or INSERT statements. Each MyISAM is stored as three files on the disk. The file names are the same as the table names, but the extensions are :. frm (storage table definition );. MYD (MYData, storing data );. MYI (MYIndex, storage index ). Data Files and index files can be placed in different directories, and I/O is evenly distributed for faster speed. To specify the path of the INDEX file and DATA file, you must specify the path through the data directory And index directory statements when creating the table, that is to say, the index files and data files of different MyISAM tables can be placed in different paths. The file path must be an absolute path and have access permissions. MyISAM tables may be damaged due to a variety of causes. The damaged tables may not be accessible, and an error is returned after being repaired or accessed. MyISAM tables provide REPAIR tools. You can use the CHECKTABLE statement to check the health of MyISAM tables and use the repair table statement to REPAIR a damaged MyISAM TABLE. Table Corruption may cause abnormal database restart. You need to fix the problem as soon as possible and confirm the cause as much as possible. MyISAM tables support three different storage formats: static (fixed length) tables, dynamic tables, and compressed tables. 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 in a static table is stored, spaces are supplemented according to the column width definition, but these spaces are not obtained during application access. These spaces are removed before they are returned to the application. A dynamic table contains variable-length fields and records are not of a fixed length. In this way, the storage space is relatively small, but frequent update and deletion of records results in fragments, 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 myisampack tool and occupies a very small disk space. Because each record is compressed separately, there is only a small access expense.02.InnoDBThe default engine starting with MySQL 5.0, the InnoDB Storage engine provides transaction security with the ability to commit, roll back, and crash recovery. However, compared with the storage engine of MyISAM, InnoDB writes less efficiently and occupies more disk space to retain data and indexes.AUTO_INCREMENTThe automatic growth column of the InnoDB table can be inserted manually. However, if the inserted value is null or 0, the actual inserted value is the Automatically increasing value. You can use the "alter table *** AUTO_INCREMENT = n;" statement to forcibly set the initial recognition value of the auto-increment column. The default value starts from 1, but the default value is reserved in the memory, if the value is used before the database is restarted, the default value of this force will be lost, and you need to reset it after the database is started. 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 the first few columns of the composite index and then incremented. For example, create a new MyISAM-type table autoincre_demo, and automatically add column d1 as the second column of the composite index. After inserting some records into the table, it can be found that the auto-increment columns are sorted by the first column d2 of the composite index, and the foreign key constraint MySQL supports the foreign key storage engine only InnoDB. When the foreign key is created, the parent table must have corresponding indexes. When the child table creates a foreign key, the corresponding indexes are 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 has associated records. CASCADE indicates that when the parent table is updated or deleted, update or delete the corresponding records of the sub-Table. set null indicates that when the parent table is updated or deleted, the corresponding fields of the sub-table are set null. Exercise caution when selecting the last two methods, which may lead to data loss due to incorrect operations. The foreign key of the sub-table is specified in the on delete restrict on update cascade mode. When deleting a record in the master table, if the Sub-table has a corresponding record, it cannot be deleted, when a primary table updates a record, if the Sub-table has a corresponding record, the sub-table is updated accordingly. When a table is created with a foreign key reference by another table, the corresponding index or primary key of the table cannot be deleted. When importing DATA from multiple tables, if you need to ignore the import sequence of the tables, you can temporarily disable the foreign key check. Similarly, when performing the load data and alter table operations, you can temporarily close the foreign key constraint to speed up processing. The command to close is SET FOREIGN_KEY_CHECKS = 0;. After the command is executed, run SET FOREIGN_KEY_CHECKS = 1; "Statement to the original state.Storage MethodThere are two ways to store tables and indexes in InnoDB. The table structure is stored in the. frm file. The data and indexes are stored in the tablespace defined by innodb_data_home_dir and innodb_data_file_path. Multiple files can be created. The table structure of the table created in this method is still saved in the. frm file, but the data and indexes of each table are stored separately in. ibd. If it is a partition table, each partition corresponds to a separate. ibd file. The file name is "table name + partition name". You can specify the location of the data file for each partition when creating a partition, in this way, the I/O of the table is evenly distributed across multiple disks. To store multiple tablespaces, you need to set the innodb_file_per_table parameter and restart the service before it takes effect. For new tables, you can create multiple tablespaces, existing tables still use shared tablespace storage. If you change the existing multi-tablespace mode back to the shared tablespace mode, the new table is created in the shared tablespace, but the existing tables with multiple tablespaces are still saved in the original access mode. Therefore, after the parameters of multiple tablespaces take effect, they only take effect for the newly created table. There is no size limit on data files in multiple tablespaces. You do not need to set the initial size, or set parameters such as the maximum size and extended size of files. For tables that use the multi-tablespace feature, you can easily back up and restore a single table, but directly copy the table. ibd files cannot be copied directly because there is no data dictionary information for the shared tablespace. ibd files and. frm files cannot be correctly identified during recovery, but you can run the following command:

1. 1 ALTER TABLE tbl_name DISCARD TABLESPACE;2 ALTER TABLE tbl_name IMPORT TABLESPACE;

    

Restore the backup to the database, but such a single table backup can only be restored to the database where the table was originally located, rather than to other databases. To restore a single table to the target database, use mysqldump and mysqlimport. Note: Even when multiple tablespaces are stored, shared tablespaces are still required. InnoDB stores internal data dictionaries and unused logs in this file. 03. MEMORYThe MEMORY storage engine uses the content in MEMORY to create tables. Each MEMORY table corresponds to only one disk file in the format of. frm. MEMORY tables have fast access because their data is stored in the MEMORY and the HASH index is used by default. However, once the service is disabled, the data in the table will be lost. When creating an index for a MEMORY table, you can specify whether to use a HASH index or a BTREE index: 04. MERGEThe MERGE storage engine is a combination of MyISAM tables. These MyISAM tables must have the same structure and have no data, you can query, update, and delete tables of the MERGE type. These operations are actually performed on internal MyISAM tables. Insert operations for MERGE tables are defined using the INSERT_METHOD clause, which can have three different values, if the FIRST or LAST value is used, the insert operation is applied to the FIRST or LAST table. If this clause is not defined or NO, the insert operation cannot be performed on the MERGE table. You can perform the DROP operation on the MERGE table. This operation only deletes the MERGE definition and has no impact on the internal table. The MERGE table retains two files on the disk. The file name starts with the name of the table. frm file storage table definition, another. the MRG file contains information about the combined tables, including the tables that the MERGE table consists of and the basis for inserting new data. You can modify the. MRG file to modify the MERGE table, but you must refresh the table with flush tables after modification. We can see that the data in the payment_all table is the result set after the records in the payment_2006 and payment_2007 tables are merged. Insert a record for the MERGE table. Because the definition of the MERGE table is INSERT_METHOD = LAST, the record will be inserted to the LAST table. Although the record inserted here is from January 1, 2006, but it will still be written to the payment_2007 table. This is also the difference between a MERGE table and a partition table. a merge table cannot intelligently write records to the corresponding table, but a partition table can. Generally, we use MERGE tables to transparently query and update multiple tables, while such operation log tables recorded by time can be transparently inserted. 05. How to Select a storage engineMyISAM: Default MySQL plug-in storage engine. If the application is dominated by read operations and insert operations, there are only a few update and delete operations, and the transaction integrity and concurrency requirements are not very high, then this storage engine is very suitable. MyISAM is one of the most commonly used storage engines in Web, data warehousing, and other application environments. InnoDB: used for transaction processing applications. It supports foreign keys. If the application has a high requirement on transaction integrity and data consistency is required under the concurrent conditions, data operations include not only insertion and query, but also many update and delete operations, therefore, the InnoDB Storage engine should be a suitable choice. In addition to effectively reducing the locks caused by deletion and update, the InnoDB Storage engine can also ensure the complete Commit and Rollback of transactions ), innoDB is a suitable choice for systems that require high data accuracy, such as billing systems or financial systems. MEMORY: stores all data in RAM and provides extremely fast access in environments where you need to quickly locate records and other similar data. MEMORY has a limitation on the table size. A large table cannot be cached in the MEMORY. The second is to ensure that the table data can be recovered, the data in the table can be recovered after the database ends abnormally. MEMORY tables are usually used for small tables with less frequent updates to quickly obtain access results. MERGE: Used to logically combine a series of equivalent MyISAM tables and reference them as an object. The advantage of the MERGE table is that it can break through the limit on the size of a single MyISAM table and distribute different tables on multiple disks, which can effectively improve the access efficiency of the MERGE table. This is suitable for VLDB environments such as data warehousing. Note: The above are just some suggestions about the storage engine Selection Based on our implementation experience. However, the features of different applications vary widely. It is not absolute to choose which storage engine to use, this requires testing based on your own applications to get the most suitable results.