Mysql storage engine and mysql Storage

Mysql storage engine and mysql Storage
08-mysql storage engine

---- Storage engine Introduction

(1 ),InnoDBEngine

InnoDB is a storage engine of mysql. innodb provides transaction logs, rollback, crash repair capabilities, and multi-version Concurrency Control for mysql tables.

Mysql has included the innoDB Storage engine since 3.23.34a.

InnoDB is the first table engine that provides foreign key constraints, and innoDB has the ability to process transactions. Other engines cannot compete with it.

Innodb supports auto-increment columns using auto_increment. The auto-increment Column cannot be empty.

The innodb Storage engine supports the foreign key Z (foreign key). The foreign key is located in a sub-table, the foreign key is dependent on the parent table, and the word associated with the foreign check in the parent table.

The segment must be the primary key. When you delete or update information of the parent table, the Word Table must also be changed accordingly,

In the innodb Storage engine, the table structure of the created table is stored in the. frm file, and the data and indexes are stored in the tablespace defined by innodb_data_home_dir and innodb_data_file_path.

Data File: *. myd
Index file: *. myi
Table definition file: *. frm

Advantages and disadvantages of the Inoodb storage engine:

Advantage: it provides good transaction management, crash, repair capability and concurrency control,

Disadvantage: The read/write efficiency is relatively low, and the Occupied data space is relatively large.

 

 

(2 ),MyISAMEngine

MyISAM storage tables are divided into three files with the same name as the table. Extensions include frm, MYD, and MYI,

Structure of the file storage table with Frm Extension

File storage data with the extension of MYD

File Storage index with MYI Extension

Advantage: Low space occupation and fast processing speed

Disadvantage: transaction log integrity and concurrency are not supported

 

(3 ),MEMORYEngine

All data in the special Mysql engine is stored in the memory and stored in the enterprise production environment. Almost

Yes. Because the data is stored in the memory, if the memory is abnormal. Data integrity will be affected.

Advantage: fast storage

Disadvantages: lack of stability and integrity

 

---- Storage Engine View

The storage engine is a feature of Mysql. Mysql can choose from multiple storage engines and different storage methods to determine whether to perform transaction processing.

(1) query the engines supported by Mysql

Mysql-> show engines;

Mysql-> show engines \ G; + G and no G display methods are different.

 

 

 

(2) query Mysql support engine information

Mysql-> show variables like 'have %'

 

Column 1: Variable_name indicates the name of the storage engine,

Column 2: Value indicates Mysql support, YES indicates support, and NO indicates not support

DISABLED indicates that it is supported but enabled.

 

(3) query the default storage engine of Mysql

Mysql-> show variables like 'Storage _ engine ';

If you want to modify the storage engine, you can modify it in my. ini.

Default-storage-engine = engine type

---- How to select a storage engine in an enterprise production environment is a complicated problem. Each storage engine has its own advantages. It cannot be said in general, who is better than who is.
The innodb Storage engine is usually used.
How can I view the current storage engine of MySQL?

In general, mysql provides multiple storage engines by default. You can view the following information:

Check what storage engine your mysql currently provides:
Mysql> show engines;

Check the default storage engine of your mysql instance:
Mysql> show variables like '% storage_engine % ';

You need to check the engine used by a table (the storage engine used for the table is displayed after the parameter engine in the display result ):
Mysql> show create table name;

MYSQL storage engine problems

View default and supported storage engines
Mysql> show engines;

View the storage engine of a table
Mysql> show create table users;

To modify the storage engine, you must specify the storage engine when creating a table or modify it later.
Create table mytable (id int, titlechar (20) ENGINE = INNODB

Alter table engineTest ENGINE = INNODB;

The default storage engine cannot be modified in real time (at least I have not found any modification method)