MySQL's common storage engine

MySQL Communication architecture

1. Top level is a driver for connecting databases for various applications

2.Mysql allocates a thread in the connection pool to handle connection requests from clients

3. Verification of the security legitimacy of the connection

The number of concurrent connections can not be too large, is the performance bottleneck of the whole system

4.SQL Interface SQL Interface checks if the SQL statement sent by the client conforms to the SQL syntax

5.Parser SQL parser optimizes validated SQL statements and checks whether users have queries or execute permissions

6.Optimlzer SQL Execution Plan Select the best path to find data

7. Return data and cache query results

Execution path of the MYQL query

1. Client sends execute query SQL statement
2.MYSQL server receives SQL statement and queries the cache first
The cache is based on the hash operation, so the characters in the SQL statement are required to be identical
3. If there is no cache, send the SQL statement to the parser
4. Optimized parsing SQL to find the best execution path
5. Execute SQL statement to return query results

MyISAM Storage Engine

Transaction not supported

Table-Level locking

Read/write blocking, write unreadable, cannot be written at read time

Cache index Only

FOREIGN KEY constraints are not supported

Clustered index not supported

Read data faster and consume less resources

MVCC (multiple version concurrency control mechanism) high concurrency is not supported

Poor recovery of crashes

MySQL5.5.5 Pre-default database engine

Application scenario: read-only (or less write), small table (can accept long-time repair operation)

MyISAM Engine Files:

TBL_NAME.FRM: Table-style definition

Tbl_name. MYD: Data files

Tbl_name. MYI: Index File

InnoDB Engine Features

Support transactions, suitable for handling a large number of short-term transactions

Row-level Locks

Read-write blocking is related to transaction isolation level

Data and indexes that can be cached

Support for clustered Indexes

Crash Recovery better

Supports MVCC high concurrency

Support full-text indexing from MySQL5.5

Starting with MySQL5.5.5 as the default database engine

InnoDB database files

Data and indexes for all InnoDB tables are placed in the same table space

Tablespace file: Under directory defined by DataDir

Data files: ibddata1, Ibddata2, ...

Each table uses a single table space to store the data and indexes of the table

Enabled: Innodb_file_per_table=on

Two types of files in the database stand-alone directory

Data files (storing data and indexes): TB_NAME.IBD

Tabular definition: tb_name.frm

Managing the Storage Engine

To view the storage engines supported by MySQL:

Show engines;

To view the current default storage engine:

Show variables like '%storage_engine% ';

To set the default storage engine:

Vim/etc/my.conf

[Mysqld]

Default_storage_engine= InnoDB;

View the storage engines used by all tables in the library

Show table status from Db_name;

View the storage engine for the specified table in the library

Show table status like ' Tb_name ';

Show CREATE TABLE tb_name;

To set the storage engine for a table:

CREATE TABLE tb_name (...) Engine=innodb;

ALTER TABLE tb_name Engine=innodb;

MySQL's common storage engine