MySQL Storage engine

Using the external health foreign key

Foreign key (current table field name) References table name (field name in Table name) on UPDATE cascade on DELETE Cascade Engine=innodb;

Foreign Health The current table refers to another table name. Synchronize Update series synchronously delete concatenation

1. Table Storage Engine If the InnoDB storage engine

2. The type of the external health field to match

3. The referenced field should have a clear index (index.unique,primary key)

Three. mysql storage engine I/O insert out to read

1.mysql Architecture

MySQL is a connection pool, SQL interface, parser, optimizer,

Caching and buffering, management tools, storage engines, physical storage devices

1. Connection pooling: Number of process limits, memory check, cache check, etc.

2.SQL interface: The command sent by the user via SQL client, received by SQL interface, SQL operation is:

DML Data Manipulation Language: Query, modify, upgrade data, etc.

DDL Data Definition Language: Create a new database, a new index, delete a user, etc.

3. Parser: Parse query statement transaction object access permissions

4. Optimizer: Optimize access path, generate execution tree

5. Cache and buffer to save SQL query results

6. Management tools, Backup, recovery, security, porting, clustering, etc. these tools generally deal with file systems

There is no need to deal with mysql-server, they correspond to commands.

7. Storage Engine: A program used to manage the file system and transform the logical structure into a physical structure. Responsible for performing actual data I/O operations for the database

Different storage engines have different functions and storage methods;

8. Physical storage device (file system)

Show engines see which storage engines are supported by the current database server

Show variables like "Table_type" shows the engine used by the current server

Variables such as

Create Talbe table name (field list) engine= storage engine name;

CREATE TABLE s1 (id int) Engine=innodb;

Show CREATE table S1;

Query what is the search engine

1) Change engine <-Method 1

ALTER TABLE name engine= storage Engine Name

ALTER TABLE T1 ENGINE=MYISAM

View the modified results

Show Create t1\g;

2) Change Engine <-Method 2

Mysql_converttable_format--usr=root--password=wsyht123--socker=/data/3306/mysql.sock--engine-MyISAM wsyht T1 # WSYHT is the library name, and T1 is the table name

Show variables like "Table_type";

Show Table Type

Vim/etc/my.cnf

On the file, open the following file

Default-storage-engine=innodb;

The default storage engine InnoDB

YG_ID Employee Number

Yg_name Employee Name

CREATE TABLE Studb.yg_tab (

yg_id Int (2) primary key auto_increment,

Yg_name varchar (TEN) is not NULL,

) Engine=innodb;

Gz_tab

ID Payroll record Number

GZ_ID Employee Number

GZ Salary

CREATE TABLE Studb.gz_tab (

ID int (2) primary key auto_increment,

gz_id Int (2) is not NULL,

GZ Float (7,2),

Foreign KEY (gz_id) references Yg_tab (yg_id) on update

Cascade on DELETE cascade) Engine=innodb;

Insert into Studb.yg_tab (yg_name) vlaues ("Jim");

Insert into Gz_tab (GZ_ID,GZ) values (1,100000.99);

Update Yg_tab set yg_id=8 where yg_id=2; Update table Records

Update Yg_tab This table is set to Id=8, the condition id=2 is changed in the number

MyISAM

The storage engine used by default

Exclusive table Space

Non-technical transaction and transaction rollback

Support table lock

InnoDB

Shared table Spaces

Support for transaction and transaction rollback

Support Row lock

Transactions: Your one-time SQL operation from the start to the correct execution of the junction rate is called a transaction

Transaction rollback:

Set autocommit=0;

Turn off auto-commit

Then submit

Commit Submit, the problem executes the error, will roll back rollback;

Start transaction;

Open a transaction

CREATE TABLE t200 (id int) Engine=innodb; You must open the transaction in the case of InnoDB

INSERT into t200 values (200);

Select *from t200;

Rollback

Rollback revert to Previous state

Commit a commit;

Lock mechanism (to resolve concurrent access problems for users)

Lock type

Write Lock (delete update insert) Mutex exclusive lock

Read lock (select desc Show) shared lock

Phantom reading

Dirty Read

Lock particle size

Table Lock (MyISAM) concurrent access low system overhead

Row lock (INNODB) concurrent access high system overhead

1 Writelock

This article is from the "Wsyht blog" blog, make sure to keep this source http://wsyht2015.blog.51cto.com/9014030/1733798

MySQL Storage engine