Talking about the InnoDB engine of MySQL engine

MySQL commonly used engines mainly have a few, Mrg_myisam, CSV, MYISAM, InnoDB, Memory, NDB, MYISAM, InnoDB is the most commonly used in MySQL storage engine, today mainly discusses InnoDB engine.

First, what is the InnoDB engine

The InnoDB engine is another important storage engine for MySQL database, and is becoming the standard for all new releases of MySQL AB, which is included in all binary installation packages.

Compared to other storage engines, the advantages of the InnoDB engine support acid-compatible things, as well as parameter integrity (i.e. externally built support).

MYSQL5.5.5 The default storage engine for the database after the InnoDB engine

Second, the characteristics of the InnoDB engine

1, support things: support 4 things isolation level, support multi-version read (ACID: atomicity, consistency, isolation, durability.)

2. Row-level locking (typically locks the current row when updating, InnoDB locks at the row level and also provides an Oracle-style, non-locking read in the SELECT statement): With the index implementation, the full table scan will still be the lock table.

3. Read-write blocking is related to the isolation level of things

4, with very efficient caching features: Can cache the index, can also cache the data (InnoDB storage engine to cache data and indexes in the main memory to maintain its own buffer pool. InnoDB stores its tables and indexes in a table space, the tablespace can contain multiple files (or raw disk partitions).

5. The entire table and primary key are stored in cluster mode, forming a balance tree

6. All Secondar index will save primary key information

7, support partition, table space, similar to Oracle database

8. Support FOREIGN KEY constraint, support full-text index after version 5.5

Iii. four characteristics of the business (ACID)

1. Atomic Nature

A transaction is an indivisible unit in which all SQL light operations in a transaction occur or do not occur.

2. Consistency

The integrity of the data must be consistent before and after a transaction occurs.

3, the isolation of

When a database is concurrently accessed, an executing transaction is not visible to other sessions until the execution is complete, and the data between multiple concurrent transactions is isolated from each other.

4. Persistence

Once a transaction is committed, its changes to the data in the database are permanent, and if an error is made, the transaction is not allowed to be revoked, only through "compensatory transactions".

Iv. InnoDB engine Architecture

Multiple memory blocks of InnoDB make up the memory pool, which is responsible for the following tasks:

1. Maintain multiple internal data structures to be accessed by all processes/threads.

2, cache the data on the disk, convenient and fast read, and in the disk file data to be cached here before modification.

3. Redo the log cache.

The primary role of a background thread is to refresh the data in the memory pool, ensure that the memory cache in the buffer pool is the most recent data, and also brush the modified data file to a disk file

Background thread

The InnoDB storage engine has 7 threads in the background,-–4 IO threads (insert buffer thread,log thread,read thread,write thread), 1 master thread, a lock monitoring thread, An error monitoring thread.

Memory

The InnoDB storage engine memory consists of the following three parts: buffer pool, redo log cache (redo log buffer), additional memory pool (additional

Memory pool). You can use Show engine InnoDB status to view the usage of Innodb_buffer_pool.

Innodb_buffer_pool_size: Specifically, the database types in the buffer pool are: Index page, Database page, undo page, insert cache page (insert buffer), adaptive

Hash (adaptive Hashindex), InnoDB-stored lock information (lock info), data dictionary information (database dictionary).

InnoDB works by reading data files by page (16K per page) into the Innodbbuffer pool, then preserving the cached data by the least recently used algorithm (LRU) and finally passing a certain frequency

Refresh the dirty page to a file.

Five , the InnoDB engine suitable production business scenario

1. Businesses that require business support (with good food characteristics)

2, row-level locking for high concurrency has a good adaptability, but need to ensure that the query is done by indexing

3, the data update more frequent scene

4, data consistency requires a higher business

5, the hardware device memory is large, can use InnoDB better cache ability to improve memory utilization, reduce disk IO as much as possible

Vi. InnoDB Table Space

The default Tablespace file stores data for the Ibdata1 file Innodb_data_file_path, innodb_file_per_table can produce a tablespace. db file by table, but only the table

Information such as data index and insert buffer, other information such as undo information, system transaction information, double write buffer, etc. or in the default tablespace (IBDATA1 or table space group).

Seven, the key characteristics, for the InnoDB high performance technology

1. Insert Cache

When a table has a nonclustered index, the insertion of a leaf node for a nonclustered index is not sequential, which requires a discrete access to the nonclustered index page, where performance falls

Low, this is due to the principle of B + tree. Inserting a cache is used to solve the problem. Insert and update operations for nonclustered indexes are not inserted directly every time

Index page, it is the first to determine whether the inserted nonclustered index page is in the cache, if it is inserted directly, if it is not placed in an insert buffer, it is like spoofing the database

This nonclustered index has been inserted into the leaf node. The merge operation of the cache and nonclustered index page byte points is then inserted at a certain frequency. The use of the insert cache needs to be met

The following two conditions (that is, a non-unique secondary index): The index is a secondary index, and the index is not unique.

2, two times write

Two times addressed to InnoDB is the reliability that is primarily used to resolve partial write failures (partial page write). Before applying the redo day, we need a copy of the page when the write fails

When it occurs, the page is restored by a copy of the page and then re-made, which is doublewrite.

Doublewrite has two parts, part of doublewrite buffer in memory, 2M in size, and 128 pages in the shared table space on the physical disk.

That is, two districts, the same size is 2M. When the Zhang ye of the buffer pool refreshes, the hard disk is not written directly, but the dirty page is copied to the memory by the memcpy function first Doublewrite

Buffer, then two writes by Doublewrite buffer, each write 1M to the shared tablespace on the physical disk, and then immediately call the Fsync function, synchronize the disk.

3. Adaptive Hasi Index

Because InnoDB does not support hash indexing, but in some cases the hash index is very efficient, so there is the adaptive hash index function, the INNODB storage engine will monitor the table

Index Lookup, if you observe the establishment of a hash index can improve performance, then automatically set up a hash index

4. Start, close, resume

Innodb_fast_shutdown affects InnoDB table closure. This parameter has 0, 1, 23 parameters

0 complete full purge and merge Insertbuffer operations when MySQL is off

1 Default value refreshes only some dirty pages in the buffer pool to disk

2 write logs to the log file there will be no transaction loss but will be recovery the next time you start

Innodb_force_recovery affects the recovery state of the entire InnoDB storage engine, which defaults to 0, which means that when recovery is required, all recovery operations need to be performed when a valid recovery is not possible, such as when a data page occurs corruption,mysql the database may be down and writes the error to the error log.

Eight, InnoDB engine tuning essentials

1, the primary key as small as possible, to avoid the Secondar index to bring too much space burden

2. Avoid full table scan because table-level locks are used

3. Cache all indexes and data as much as possible. , improve response speed and reduce disk IO consumption

4, in the high-volume insertion time, as far as possible to control things and do not use autocommit automatic submission has a switch to control the way of submission

"Cancel Auto-commit things

Mysql> set global init_connect= "Set autocommit=0";

init_connect= ' SET autocommit=0 '//Add these to mysqld

】

5, reasonable set innodb_flush_log_at_trx_commit parameter value, do not over-pursuit of security,

(If innodb_flush_log_at_trx_commit=0 log buffer is written to disk every second, no action is made when committing the thing)

6 Avoid primary key updates, as this will result in a lot of data movement

Ix. How to batch change MySQL engine in production environment

ALTER TABLE table_name ENGINE = INNODB

Ten, InnoDB and MyISAM difference summary

1.InnoDB does not support indexes of type Fulltext.

The exact number of rows in the table is not saved in 2.InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.

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 sentence, 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

In addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the scope to scan when executing an SQL statement, the InnoDB table also locks the full table , such as the Update table set num=1 where name like "% aaa% "

3. For a field of type auto_increment, the InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a combined cable with other fields.

4.DELETE from table, InnoDB does not reestablish the table, but deletes one row at a time.

The 5.LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for tables that use additional InnoDB features, such as foreign keys.

Xi. How to choose InnoDB and MyISAM

Select the InnoDB table when your database has a large number of writes, updates, and queries are relatively small or the data integrity requirements are high. Select the Mysiam table when your database is primarily query-focused, compared with fewer updates and writes, and less stringent on business data integrity requirements. Because the query operation of the Mysiam table is more efficient and faster than the InnoDB.

This article is from the "Silent Dialogue" blog, please be sure to keep this source http://chbinmile.blog.51cto.com/6085145/1874561

Talking about the InnoDB engine of MySQL engine