MySQL - 6. MySQL storage engine introduction


一, what is the storage engine

Storage engine refers to the type of the table. The storage type of the database determines how the table is stored on the computer. The user can select a suitable storage engine according to different storage methods, whether to perform transaction processing, and the like.

二, How to view the MySQL storage engine

(一) View the storage engine supported by MySQL: SHOW ENGINES

练习 one, view the storage engine supported by MySQL: SHOW ENGINES;


练习Second, more beautiful display, plus \G, displayed in grid form: SHOW ENGINES \G;

这里写图片描述 这里写图片描述

support: It means that MySQL supports this storage engine, yes means support, no means no support
Comment: indicates a comment or comment on this storage engine
Transactions: whether to support transaction processing, yes is support, no is not supported
XA: Is it a distributed transaction processing XA specification, yes Yes support, no is not supported
Savepoints: Whether to support savepoints. Yes means support, no means no support

Our default storage engine is InnoDB

(2) View display support storage engine information: SHOW VARIABLES LIKE'hava%'


(3) View default Storage engine: SHOW VARIABLES LIKE'storage_engine' (not good) SHOW VARIABLES LIKE '%engine%' (easy to use)


三, MySQL common storage engine and features

With these storage engines, built The table can be used to specify the storage engine to use. Different tables can use different storage engines. When it comes to future optimization or business logic, choose the right storage engine.

(一)InnoDB Storage Engine

Features: (1) Mainly to provide MySQL with transactions, including rollback, including repair capabilities and multi-version concurrency control of a transaction completely. (2) Only it supports external constraints. (3) The table structure of the table it creates is stored in a .fim file. (4) Data and index are stored in the tablespace defined by innodbdatahomedr and innodbdatafilepass respectively.

Disadvantages: The reading efficiency is relatively low, and the occupied space is relatively large

(2) MyISAM storage engine

Features: (1) When we create a table with the MyISAM engine, we store our table in three files with the same name and table name as fim, mid and mii. Fim is the structure of the storage table, fid is to store our data, fii is to store our index. (2) The table based on our MyISAM storage engine supports three different storage formats, which are static (the default storage format, the field is fixed length), dynamic type (including our variable length field, the length of the field is not fixed) And compression type (created with MyISAM's apache tool, it occupies less disk)

Advantages: Take up small disk space, process speed block, insert read data fast

Disadvantage: Do not support transactions . No transaction integrity, security and transaction concurrency processing

(3) MEMORY storage engine

Features: (1) Store the contents in memory to create the table, and all the data is also stored Our in-memory (fast) (2) The table based on the MEMORY storage engine actually corresponds to a disk file, the file name is the same as the table name, and the type is also the fim type. However, this file only stores the structure of the table, and the data is stored in the memory, which facilitates the rapid processing of the data. It can increase the speed of the table. (3) The server needs enough memory to maintain the use of the engine. After the use is released,

Disadvantages: If the power is unexpectedly interrupted or an abnormality occurs, the integrity of the data may be affected. The safety is not high, the life cycle is short, and the best is once. Can't build too big table

4, how to choose the right storage engine