Shulou(Shulou.com)06/01 Report--

Following the author to understand the main categories of Mysql storage engine and how to choose, I believe you will benefit a lot after reading, the text is not much in the essence, hope that the main categories of Mysql storage engine and how to choose this short content is what you want.

There are two main categories of MySQL storage engines:

1. Transaction security table: InnoDB, BDB.

two。 Non-transactional security tables: MyISAM, MEMORY, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CSV, BLACKHOLE, FEDERATED, etc.

The default storage engine for MySQL is MyISAM (InnoDB is the default in version 5.7).

The parameter of the default storage engine: default-table-type is set in the configuration file.

Query the storage engines supported by the current database:

Show engines;show variables like 'have%'

View the current default storage engine:

Show variables like'% table_type%'

Specify the storage engine when creating a new table:

Create table (...) Engine=MyISAM

Here are four more commonly used storage engines: MyISAM, InnoDB, MEMORY, and MERGE.

1. MyISAM

1. Data file:

The MyISAM data table is stored as three files on disk, all of which have the same file name as the table name, with the following extensions:

(1) .frm: stores the data table structure definition.

(2) .MYD: store table data.

(3) .MYI: stores the table index.

Among them, data files and index files can be placed in different directories, evenly distributed IO, for faster speed. Specify the path to the index file and data file, which needs to be specified by the data directory and index directory statements when the table is created. (the file path needs to be absolute and have access)

Tables of type MyISAM may be corrupted for a variety of reasons, and corrupted tables may not be accessible, prompting them to be repaired or returning incorrect results after access. You can use the check table statement to check the health of the MyISAM table and use the repair table statement to repair the corrupted MyISAM table.

two。 Storage format:

(1) static table (default): fields are non-variable length (each record is fixed length). Storage is very fast, easy to cache, and easy to recover in the event of failure; it usually takes up more space than dynamic tables.

(2) dynamic table: it takes up relatively little space, but frequent updates and deletions of records will lead to fragmentation, which requires regular execution of optimize table or myisamchk-r commands to improve performance, and it is difficult to recover in case of failure.

(3) Compression table: it is created with myisampack tool and takes up very little disk space. Because each record is compressed separately, there is only a very small access expense.

The data of the static table is stored to fill in the blanks according to the width of the column, and these spaces are removed before returning the data to the application. If there is a space after the content that needs to be saved, it will also be removed when the result is returned. (in fact, it is the behavior of the data type char, which will also have the same problem in dynamic tables.)

Static and dynamic tables are automatically selected based on the type of column being used. )

3. Advantages and disadvantages:

(1) advantage: high speed of access.

(2) transactions and foreign keys are not supported.

4. Applicable:

If the application is mainly read and insert operations, only a few update and delete operations, and the transaction integrity, concurrency requirements are not very high, then the choice of this storage engine is very appropriate. MyISAM is one of the most commonly used storage engines in Web, data warehouse and other application environments.

II. InnoDB

1. Storage method:

InnoDB stores tables and indexes in the following two ways:

(1) use shared tablespace storage: table structures created in this way are saved in .frm files, and data and indexes are stored in tablespaces defined by innodb_data_home_dir and innodb_data_file_path, which can be multiple files.

(2) use multiple tablespace storage: the table structure created in this way is still saved in the .frm file, but the data and indexes for each table are saved separately in the .idb file. If it is a partition table, each partition corresponds to a separate .idb file with the file name "Table name + Partition name". You can specify the location of the data file for each partition when you create the partition, so that the IO of the table is evenly distributed across multiple disks.

To use the storage method of multiple table spaces, you need to set the parameter innodb_file_per_table and restart the CVM before it takes effect, and it only takes effect on newly created tables. There is no size limit for data files with multiple tablespaces, there is no need to set the initial size, and there is no need to set parameters such as the maximum limit and extension size of the file. Even in the multi-tablespace storage mode, sharing tablespaces is still necessary. InnoDB puts internal data dictionaries and work logs in this file, so it is not possible to directly copy .idb files when backing up tables that use multi-tablespace features. You can restore the data backup to the database by command:

ALTER TABLE tbl_name DISCARD TABLESPACE;ALTER TABLE tbl_name IMPORT TABLESPACE

However, this can only be restored to the original database of the table, and if you need to restore to other databases, you need to do so through mysqldump and mysqlimport.

two。 Data file:

The data file of InnoDB is determined by how the table is stored.

(1) shared tablespace files: defined by parameters innodb_data_home_dir and innodb_data_file_path, used to store data dictionaries and logs, etc.

(2) .frm: store the table structure definition.

(3) .idb: it is used to store table data and indexes when using multiple tablespace storage, but not if shared tablespace storage is used.

3. Foreign key constraints:

InnoDB is the only MySQL engine that supports foreign key constraints. Foreign key constraints allow the database to ensure data integrity and consistency through foreign keys, but the introduction of foreign keys will slow down speed and performance. When creating a foreign key, the parent table must have a corresponding index, and the child table will automatically create the corresponding index when the foreign key is created.

Examples of the use of foreign key constraints:

CREATE TABLE `dep` (`id` smallint (6) NOT NULL AUTO_INCREMENT, `name` varchar (20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `emp` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (20) DEFAULT NULL, `dep_ id` smallint (6) NOT NULL, PRIMARY KEY (`id`), KEY `idx_fk_dep_ id` (`dep_ id`), CONSTRAINT `fk_emp_ dep` FOREIGN KEY (`dep_ id`) REFERENCES `dep` (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

KEY: defines the name of the index constraint.

CONSTRAINT: defines the foreign key constraint name. (it should be unique in the database, if you do not specify the system will automatically generate a constraint name)

ON: specifies the impact of parent table operations on child tables (restrict is not defined by default).

Restrict and no action: the parent table cannot be updated or deleted if the child table has related records.

Cascade: when the parent table is updated or deleted, the corresponding records of the child table are also updated or deleted.

Set null: when the parent table is updated or deleted, the corresponding field of the child table is set to null.

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. When importing data from multiple tables, if you need to ignore the import order of the table, you can temporarily turn off the check of foreign keys; when performing load data and alter table operations, you can also temporarily close the foreign key constraints to speed up the processing.

Close command:

Set foreign_key_checks=0

Open the command:

Set foreign_key_checks=1

4. Advantages and disadvantages:

(1) advantage: provides transaction security with commit, rollback and crash recovery capabilities.

(2) disadvantages: it is less efficient than MyISAM,InnoDB writes and takes up more disk space to retain data and indexes.

5. Applicable:

If the application has high requirements for transaction integrity, data consistency is required under concurrent conditions, and data operations include many update and delete operations in addition to insert and query, then InnoDB storage engine should be a more appropriate choice. InnoDB storage engine can not only effectively reduce the locking caused by deletions and updates, but 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.

III. MEMORY

1. Data file:

Each MEMORY table corresponds to only one .frm disk file, which is used to store the structural definition of the table, and the table data is stored in memory. HASH indexes are used by default instead of BTREE indexes.

two。 Advantages and disadvantages:

(1) advantage: the access speed is very fast because the data is stored in memory.

(2) disadvantages: once the service is turned off, the data in the table will be lost; there is a limit on the size of the table.

3. Applicable:

The Memory storage engine is mainly used in those code tables whose contents do not change frequently, or as the intermediate result table of statistical operations, so that it is easy to analyze the intermediate results efficiently and get the final statistical results.

IV. MERGE

1. Engine principle:

Merge storage engine is a combination of a group of MyISAM tables, these MyISAM tables must have the same structure, the merge table itself has no data, and the merge type table can be queried, updated and deleted, which are actually carried out on the internal actual MyISAM table.

Define the insert operation of the merge table through the insert_ method clause: use first or last to cause the insert operation to be acted on the first or last table accordingly. If it is not defined or defined as No, it means that the insert operation cannot be performed on the merge table. The drop operation on the merge table only removes the definition of merge and has no effect on the internal table.

two。 Data file:

(1) .frm: stores the table definition.

(2) .MRG: stores information about combined tables, including which tables the merge table consists of and the basis for inserting new data. You can modify the merge table by modifying the .mrg file, but refresh it through flush tables after modification.

3. Examples of use:

CREATE TABLE `m1` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE `m2` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (20) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE `m` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (20) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION= (`m1`, `m2`)

4. Applicable:

Used to logically group a series of equivalent MyISAM tables together and reference them as a single object. The advantage of MERGE table is that it can break through the restriction on the size of a single MyISAM table, and the access efficiency of MERGE table can be effectively improved by distributing different tables on multiple disks. This is ideal for VLDB environments such as data warehousing.

After reading the main categories of Mysql storage engine and how to choose this article, many readers will want to know more about it. For more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.