MySQL Database engine

First, storage engine concept

Data in MySQL is stored in files (or memory) in a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different capabilities and capabilities. By selecting different technologies, you can gain additional speed or functionality to improve the overall functionality of your application.

Ii. what are the storage engines?

The main storage engines are:

1.InnoDB 2.mrg_myisam 3.memory4.blackhole 5.MyIsam 6.CVS 7.Archive 8.performance_schema 9.Federated

Mysql>Show Engines\g1. Row ***************************Engine:innodb support:default comment:supports transactions, Row-level Locking, andforeign keystransactions:yes xa:yes savepoints:yes2. Row ***************************Engine:mrg_myisam support:yes comment:collection of identical MYISAM Tablestransactions:no Xa:no Savepoints:no3. Row ***************************engine:memory Support:yes comment:hash based, storedinchMemory, useful fortemporary tablestransactions:no xa:no savepoints:no4. Row ***************************engine:blackhole support:yes Comment:/dev/NULL storage engine (anything you write to it disappears) Transactions:no Xa:no Savepoints:no5. Row ***************************Engine:myisam support:yes comment:myisam storage enginetransactions:no xa:no savepoints: NO6. Row ***************************engine:csv support:yes comment:csv storage enginetransactions:no xa:no savepoints:no7. Row ***************************engine:archive support:yes comment:archive storage enginetransactions:no xa:no savepoints : NO8. Row ***************************engine:performance_schema support:yes comment:performance schematransactions:no xa:no Sav Epoints:no9. Row ***************************engine:federated support:no comment:federated MySQL storage enginetransactions:null Xa:nul L Savepoints:null9 rowsinchSet (0.00 sec)

Iii. key features of the storage engine

1.InnoDB

InnoDB is a transactional storage engine with row and lock and foreign key constraints, suitable for the occasion:

1. Update multiple tables for multiple concurrent update requests 2. Support Transaction 3. You can recover from a disaster (through Bin-log logs, etc.) 4. FOREIGN KEY constraints. Only he supported the foreign key 5. Supports automatic increment of column properties auto_increment

2.mrg_myisam

The merge storage engine is a set of MyISAM, which means that he aggregates multiple tables of the MyISAM engine, but there is no data inside of him,
The real data is still in the MyISAM engine's table, but it can be queried, deleted, and updated directly. For example: we may encounter such problems, the same kind of data will be divided into multiple tables according to the time of the data
If the query at this time, it will be more troublesome, merge can directly aggregate multiple tables into a single table, and then delete the merge table (delete is defined), the original data will not affect.

3.Memory

Memory uses the logical medium is the RAM, the response speed should be very fast, but when the mysqld daemon crashes the data will be lost, in addition, the data required to store the data is the same length of the format,
For example, the BLOB and text types of data are not available (the length is not fixed). Use memory storage engine condition: 1. The target data is small and is accessed very frequently, storing data in memory if too large data can cause memory overflow.
The size of the memory meter can be controlled by the parameter max_heap_table_size, limiting the maximum size of the memory meter.  2. If the data is temporary and must be available immediately, then it can be placed in memory.  3. The data stored in the memory table is not much of a relationship if it is suddenly lost. "Note" Memory supports both hash index and B-Tree index, B-Tree index can use partial query and wildcard query, can also use operators such as <,> and >= to facilitate data mining,
Hash indexes are fairly fast but are much slower for the range.

4.Blackhole

"Black hole" storage engine, he will discard all the inserted data, the server will record the log of the Blackhole table, so it can be used to copy data to the backup database.
Look at some other information, said: can be used to act as dummy master, using blackhole as a "dummy master" to alleviate the load of master,
For master, "dummy master" is a slave role, as well as a log server and so on.

5.MyIsam

The MyIsam storage Engine is independent of the operating system, that is, it can be used on Windows, or it can be relatively simple to transfer data to the Linux operating system.
This storage engine creates three files when the table is created, and the. frm file is used to store the table's definition, and the. myd file is used to store the table's data, and the. myi file, which stores the index.
The operation of the operating system on large files is slow, so that the table is divided into three files, then. MyD this file alone to store data can naturally optimize database queries and other operations.  1. Transactions are not supported, but it does not mean that items with transactional operations cannot use the MyISAM storage engine, which can be controlled at the service level according to their own business requirements.  2. Foreign keys are not supported.  3. Queries are fast, and are more useful if the database insert and update operations are more numerous. 4. Lock the table. 

6.CSV

You can use the SCV file as a table for MySQL, but the index is not supported. All fields in the CSV engine table must be non-empty, and two of the tables created are CSV files and CSM files.

7.Archive

Archive is the meaning of archiving, only supports inserting and querying two functions, after MySQL5.5 support indexing function, he has a good compression mechanism, using zlib compression library,
Compression is performed in real time when the request is recorded, and is often used as a repository. Suitable for storing large amounts of independent data as historical records. has a high insertion speed but poor support for queries.

8.performance_schema

MySQL5.5 later added a storage engine, that is Performance_schema, he is mainly used to collect database server performance parameters. A MySQL user cannot create a table that stores that type. He provides the following features: 1. Provides details of the process wait, including locks, mutex variables, and file information. 2. Save the historical event summary information to make a detailed judgment of the MySQL server's performance. 3. It is easy to add and remove monitoring points, and it is possible to change the monitoring cycle of the MySQL server in order to be enabled in configuration file MY.CNF. 

9.Federated

The Federated storage Engine is a proxy for accessing the MySQL server, although the engine appears to provide a good cross-server flexibility, but often poses problems that are disabled by default.

Comparison of MyISAM and InnoDB instances
1. Create two tables with MyISAM and InnoDB as the storage engine

CREATE TABLE Testmyisam (-ID int unsigned primary key auto_increment, name varchar) NOT NULL  ) Engine=myisam; CREATE TABLE Testinnodb (    ->id int unsigned primary key auto_increment,     ->name varchar () NOT NULL     -& gt;) Engine=innodb;

The contents of the two tables are consistent but the storage engine is different

2. Inserting 1 million data

Of course there is no manual insertion, creating a stored procedure that inserts 1 million of the data.

Mysql> create procedure insertmyisam ()       -> begin       -> set @i = 1;      - > while @i <= 1000000      -> do       -> insert into testmyisam (name)  values (concat ("WY",  @i));       -> set @i = @i + 1;       -> end while;      -> end//   Mysql> create procedure insertinnodb ()       -> begin       -> set @i = 1;      - > while @i <= 1000000      -> do       -> insert into testinnodb (name)  values (concat ("WY",  @i));       -> set @i = @i + 1;      ->  end while;      -> end//  

Insert (1 million) the time in the table of the MyISAM storage engine is as follows:

Mysql> call Insertmyisam; //Query OK, 0 rows affected (49.69 sec)

Insert (1 million) the time in the table of the InnoDB storage engine is as follows:

Mysql> call Insertinnodb; //Query OK, 0 rows affected (1 hour min 14.12 sec)

At that time, the default is to turn on automatic commit transactions, so the execution is very slow, you can close the auto-commit, and then call the stored procedure to insert 1 million of the data, after the completion of the automatic commit, it will be much faster.

mysql> Set autocommit = 0; Query OK, 0 rows Affected (0.00 sec)

3. Total number of query data

The following is an analysis of INNODB's SQL statements:

Mysql> DESC SELECT COUNT (*) fromtestinnodb\g; 1. Row ***************************ID:1select_type:simple table:testinnodb type:index possible_keys:null key:primary Key_len:4ref:null rows:997134extra:using Index1 rowinchSet (0.03 sec)

The following is an analysis of an SQL statement that MyISAM (the total number of his data is stored in other tables so there is no effect on the number of rows):

Mysql> DESC SELECT COUNT (*) fromtestmyisam\g; 1. Row ***************************ID:1select_type:simple table:null type:null possible_keys:null key:null key _len:null ref:null rows:null extra:select tables optimized away1 rowinchSet (0.00 sec)

4. Querying a range of data

Columns with no indexes

mysql> select * from testmyisam where  name >  "wy100"  and name <  "wy100000", +-------+---------+  |  id    | name    |  +-------+---------+   |  1000 | wy1000  |  | 10000 | wy10000 |   +-------+---------+  2 rows in set  (0.43 sec)      mysql> select * from testInnoDB where name >  "wy100"   and name <  "wy100000", +-------+---------+  | id    |  name    |  +-------+---------+  |  1000 |  wy1000  |  | 10000 | wy10000 |  +-------+---------+    

Columns that have indexes

For tables that use the MyISAM storage engine:

<pre name= "code" class= "SQL" >select * from Testmyisam where ID > Ten and ID < 999999; execution time: <pre name= "Code" c lass= "SQL" >999988 rows in Set (0.91 sec)

For tables that use the InnoDB storage engine:

SELECT * from Testinnodb where ID > Ten and ID < 999999; execution time: 999988 rows in Set (0.69 sec)

　　

MySQL Database engine