Mysql-Storage Engine

1. Storage Engine: is a table-level type.

2. How to view the table storage Engine:

(1), mysql> Show table status in MyDB where Name= ' classes ' \g;

1. Row

Name:classes Table Name

ENGINE:INNODB Storage Engine

Version:10 current version of the table (multi-version concurrency)

Row_format:compact line Format

Number of data rows in the Rows:4 table

avg_row_length:4096 average number of bytes per row

data_length:16384 Total bytes of data in the table

max_data_length:0 table can occupy the maximum space, Unit bytes

index_length:0 size of index, per byte

data_free:8388608

Auto_increment:5 Next Auto_increment value

Create_time:2015-12-24 18:35:10 creation time of the table

Update_time:null table Data Last modified time

Check_time:null When to use the check table or myisamchk the last time the meter was detected

Collation:utf8_general_ci Sorting rules

Checksum:null

Create_options: The option specified when creating the table.

Comment: Notes for tables

(2), Mysql>use mydb;

Mysql> Show Table status like ' classes ' \g;

1. Row

Name:classes

Engine:innodb

Version:10

Row_format:compact

Rows:4

avg_row_length:4096

data_length:16384

max_data_length:0

index_length:0

data_free:8388608

Auto_increment:5

Create_time:2015-12-24 18:35:10

Update_time:null

Check_time:null

Collation:utf8_general_ci

Checksum:null

Create_options:

Comment:

3. Features of the InnoDB storage engine:

(1), support transaction: transaction log.

(2), support foreign key

(3), MVCC Multi-version concurrency control

(4), support clustered index is also called the clustered index, because the clustered index index file and data files are placed together, so the clustered index can only have one,

Clustered indexes are generally made with primary keys. Then, other nonclustered indexes are often referred to as secondary indexes

(index files and data files for secondary indexes are not placed together), and secondary indexes can have multiple.

(5), support hot backup, if the table is stored with a separate table space (Innodb_file_pre_table=on).

(6), support row-level lock.

(7), the database can recover data after the crash, because there is a transaction log.

4. Features of the MyISAM storage engine:

(1), does not support transactions.

(2), foreign keys are not supported.

(3), row-level locks are not supported, table-level locks are supported.

(4), Support deferred update index.

(5), support full-text indexing.

(6), the application scenario: Read more write less, smaller data table, can tolerate the crash after the modification operation and data loss.

5. Features of the archive storage Engine:

(1), only support insert and select, Support good compression function.

(2), suitable for storing log information or other data collection by time series implementation of the application of the class.

(3), transactions and indexes are not supported.

6, third-party storage engine:

(1), XtraDB: Enhanced version of the InnoDB, provided by Percona;

编译安装时，下载XtraDB的源码替换为Mysql存储引擎中的InnoDB的源码，XtraDB已作为MariaDB中的默认的存储引擎。

(2), tokudb: Using fractal trees Index, high performance, especially suitable for storing big data tables,

has been introduced into the new version of MARIADB.

(3), pbxt:mariadb with this storage engine, to provide appropriate support for SSD drives,

Support transactions, MVCC, foreign key constraints, etc., performance is also relatively high.

Mysql-Storage Engine