Small shellfish _mysql common storage engine differences

Storage Engine Differences Brief:
1. Storage Engine
2. Difference between MyISAM and InnoDB

first, the storage engine

1. What is a storage engine

the way to store and manage data in layman's words

Image examples:

A bicycle keeper in a place: Lee, Zhang. Every day there are many people to access bicycles. Lee's Management method is: The owner of their own storage, Lee also does not record what the other store is what the car. When you pick up your car, you pay a management fee. Also do not check whether the other's own car. On the contrary, Zhang, when the owner of the car storage, record the characteristics of the store and the corresponding vehicle information. When people come to pick up the car, Zhang must also carefully check, the characteristics of the car and whether the corresponding.

From the above example:

A, from the point of view of efficiency: Lee is faster than Zhang, because it does not have to proofread

b, from a security point of view: Zhang than Lee to be safe, because it was proofread.

Summary: In fact, the MySQL storage engine is the same, each storage engine has its own access mode, so in the choice, should be based on the actual situation.

2. View all MySQL engines

Mysql>show engines;

second, the difference between MyISAM and InnoDB

These two storage engines are differentiated because they are the most frequently used MySQL engines, so it is necessary to differentiate them. It is advantageous to use flexibly in the actual situation.

1. MyISAM Storage Engine

A, table composition

Set up Table MyTest

CREATE TABLE mytest (id int unsigned,content char (5)) Engine=myisam;

Table corresponds to the disk's file

Summarize:

A1, suffix name: frm is a table structure file, MyD is a data file, Myi is an index file

A2, the establishment of the MyISAM engine table, will build these three files, in the MySQL database backup or transfer, easy to operate

B. Bulk INSERT Data

Insert 1000 rows of data

C, query the number of tables

Summary: you can see that Mysiam has a record of the number of rows in the table, rather than needing to record

D. Whether the transaction is supported

Summary: MyISAM does not support transactions, so if you are doing transactional operations on the database, but the transaction cannot succeed, you will have to look at your table engine to see if the engine supports transactions.

E, operation on auto_increment column (modify the ID column of table MyTest )

Inserting data

Delete the data with ID 3, and then insert the data

At this point, after the deletion of the ID 3, the data that continues to be added, the ID does not start at 3, but skips 3, starting from 4.

Treatment scenarios:

After deleting the data, reset the Auto_increment

Summary: The table of the MyISAM engine stores the auto_increment in the table and stores it on the file disk. Even restarting the service is not lost.

2. InnoDB Storage Engine

A, table structure

CREATE TABLE Myinnodb Table

CREATE TABLE Myinnodb (id int unsigned,content char (5)) Engine=innodb;

Where frm is a table structure file, IBD is a data and index file

B, BULK Insert data (insert 1000 rows of data)

C, Query the total number of tables

Summary: The InnoDB engine table, when calculating the total number of rows, is a

D. Whether the transaction is supported

Summary: InnoDB engine table supports transactional operations

E, operation on auto_increment column (modify the ID column of table Myinnodb)

Inserting data

Delete Data with ID 3

Continue inserting data

It is found that this is consistent with the MyISAM engine.

Treatment scenarios:

Summary: InnoDB can reset the auto_increment, but its auto_increnment column is stored only in main memory, not on disk

Restart Service

3. The difference between MyISAM and InnoDB

	MyISAM	InnoDB
Document composition	frm, MYD, MYI	frm, IBD
Whether the transaction is supported	Tables of the MyISAM type emphasize performance, which is performed more quickly than the InnoDB type, but does not provide transactional support	InnoDB provides transaction support transactions, external keys and other advanced database functions
Calculate Table Row Count	Select COUNT (*) from Table,myisam as long as you simply read the number of rows saved, note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same	The exact number of rows in a table is not saved in InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows
The size of the lock	Table lock	Row lock
Operation on the Auto_increment	Stored on disk, can be reset by alter	is stored only in main memory, not on disk

4. Difference between table lock and row lock

When performing a database write operation (insert,update,delete), the Mysiam table locks the table, and the InnoDB table locks the row. Popular point is that you execute an UPDATE statement, then the Mysiam table will lock the entire table, the other inserts and delete, update will be rejected, until the completion of the UPDATE statement execution will not be executed sequentially.

While the lock line, that is, you execute the UPDATE statement is, will only lock this record, only the other write to this record, update operations will be blocked and wait until the UPDATE statement is completed before execution, for other records of the write operation will not affect.

5. Selection of MyISAM and InnoDB

A, business needs support Services, select InnoDB

B, the majority of business inquiries, select MyISAM

6. Summary

MyISAM Manage non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is a better choice if you need to perform a large number of select queries in your application.

InnoDB is used for transactional applications and has many features, including acid transaction support. If you need to perform a large number of insert or update operations in your app, you should use InnoDB, which can improve the performance of multiple user concurrency operations.

Add:

1, for the INNODB data structure, first of all to understand the two conceptual problems: Shared table space and exclusive table space.

What are shared table spaces and exclusive table spaces

Shared tablespaces and exclusive tablespaces are all about how data is stored.

Shared tablespace: All table data for a single database, the index file is all in one file, and the default file path for this shared tablespace is in the data directory. The default file name is: Ibdata1 is initialized to 10M.

Exclusive tablespace: Each table will be generated in a separate file way for storage, each table has a. frm table description file, and an. ibd file. This file includes the data content of a single table and the index content, which, by default, is stored in the table's location.

The quieter become,the more you is able to hear!

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Small shellfish _mysql common storage engine differences