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

1. Storage engine: it 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: current version of the 10 table (concurrent with multiple versions)

Row_format: Compact line format

Rows: 4 rows of data in the table

Avg_row_length: 4096 average number of bytes per line

Data_length: total number of bytes of data in the 16384 table

Max_data_length: the maximum space that a table can occupy, in bytes

Index_length: the size of the 0 index, in bytes

Data_free: 8388608

Auto_increment: 5 the next Auto_ increment value

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

Update_time: the time when the NULL data was last modified

Check_time: the time that NULL last checked the table using check table or myisamchk

Collation: utf8_general_ci collation

Checksum: NULL

Create_options: the option specified when creating the table.

Comment: comments on the tabl

(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. Characteristics of InnoDB storage engine:

(1) supporting transactions: transaction log.

(2) support foreign keys

(3) MVCC multi-version concurrency control

(4) clustering index is also called clustered index, because the index file and data file of clustered index are placed together, so there can be only one clustered index.

The primary key is generally used as a clustered index. Then, other non-clustered indexes are often called secondary indexes.

(the index files and data files of the secondary index are not placed together.) there can be more than one secondary index.

(5) Hot backup is supported, provided that independent tablespaces (innoDB_file_pre_table=ON) are used for table storage.

(6) row-level locks are supported.

(7) the data can be recovered after the database crash because of the transaction log.

4. Characteristics of MyISAM storage engine:

(1) transactions are not supported.

(2) Foreign keys are not supported.

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

(4) delayed updating of the index is supported.

(5) full-text index is supported.

(6) applicable scenarios: tables with more reads and less writes and smaller data; can tolerate modification operations and data loss after crashes.

(7) this storage engine should deal with disk fragments regularly, otherwise the speed will become slower and slower, the command is: optimize table table name.

5. Characteristics of ARCHIVE storage engine:

(1) only insert and select are supported, and good compression is supported.

(2) it is suitable for storing log information or other applications of data collection based on time series.

(3) transactions and indexes are not supported.

6. Third-party storage engine:

(1), XtraDB: enhanced version of InnoDB, provided by Percona

When compiling and installing, the source code of downloading XtraDB is replaced with the source code of InnoDB in Mysql storage engine, and XtraDB has been used as the default storage engine in MariaDB.

(2), TokuDB: use Fractal Trees index with high performance, especially suitable for storing big data's table

Has been introduced into the new version of MariaDB.

(3) PBXT:MariaDB comes with this storage engine to provide appropriate support for SSD hard drives.

Support for transactions, MVCC, foreign key constraints, etc., and high performance.

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.