Shulou(Shulou.com)06/01 Report--

There are several commonly used MYSQL engines, MRG_MYISAM, CSV, MyISAM, InnoDB, MEMORY, NDB, of which MyISAM and InnoDB are the most commonly used storage engines for mysql. Today we mainly discuss InnoDB engine.

What is the InnoDB engine

The InnoDB engine, another important storage engine for MYSQL databases, is becoming the standard for all new releases of MYSQL AB and is included in all binary installers.

Compared with other storage engines, the advantages of the InnoDB engine support ACID-compatible things, as well as parameter integrity (that is, external support).

The default storage engine of the database after MYSQL5.5.5 is the InnoDB engine.

Second, the characteristics of InnoDB engine

1. Support things: support 4 isolation levels of things and support multi-version reading (ACID: atomicity, consistency, isolation, persistence.)

2. Row-level locking (the current row is generally locked when updated, InnoDB is locked at the row level and also provides a consistent Oracle-style unlocked read in the SELECT statement): through the index, the full table scan will still lock the table.

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

4. It has a very efficient caching feature: it can cache indexes as well as data. (the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB stores its tables & indexes in one tablespace, which can contain multiple files (or raw disk partitions)

5. The whole table and primary key are stored in Cluster to form a balance tree.

6. All Secondar Index will save primary key information

7. Support partitioning, tablespaces, similar to oracle databases

8. Foreign key constraints are supported, and full-text indexing is supported after version 5.5

3. Four characteristics of transaction (ACID)

1. Atomicity

A transaction is an indivisible unit, and all SQL operations in the transaction either occur or do not occur.

2. Consistency

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

3. Isolation

When accessing the database concurrently, an executing transaction is invisible 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, it changes the data in the database permanently. If something goes wrong, the transaction is not allowed to be undone, only through the "compensatory transaction".

IV. InnoDB engine architecture

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

1. Maintain multiple internal data structures that all processes / threads need to access.

2. Cache the data on the disk, read it easily and quickly, and cache here before modifying the data of the disk file.

3. Redo log cache.

The main function of the background thread is to refresh the data in the memory pool, to ensure that the memory cache in the buffer pool is the latest data, and to refresh the modified data file to the disk file.

Background thread

There are seven threads in the background of the innodb storage engine-- four IO threads (insert buffer thread,log thread,read thread,write thread), one master thread, one lock monitoring thread, and one error monitoring thread.

Memory

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

Memory pool). You can use show engine innodb status to view innodb_buffer_pool usage.

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), lock information stored by innodb (lock info), data dictionary information (data dictionary).

How InnoDB works: read the data file into InnoDBbuffer pool by page (16K per page), then keep the cached data according to the least recently used algorithm (LRU), and finally pass a certain frequency

The rate flushes dirty pages to the file.

5. Production business scenarios applicable to InnoDB engine

1. Business that needs transaction support (with good food characteristics)

2. Row-level locking is well adapted to high concurrency, but you need to make sure that the query is done through the index.

3. Scenarios with frequent data updates

4. Services with high data consistency requirements

5. Hardware devices have large memory, so we can make use of the better cache capacity of InnoDB to improve memory utilization and reduce disk IO as much as possible.

VI. INNODB tablespace

The default tablespace file is ibdata1 file innodb_data_file_path to store data. Innodb_file_per_table can generate a tablespace .db file by table, but only the

Information such as data indexing and insert buffering, and other information such as undo information, system transaction information, double write buffer, etc. are still stored in the default tablespace (ibdata1 or tablespace group).

Key features, technologies to improve the performance of innodb

1. Insert cache

When a table has a nonclustered index, the insertion of the leaf nodes of the nonclustered index is not sequential, so discrete access to nonclustered index pages is needed, and the performance is degraded here.

Low, this is due to the principle of b + tree. Inserting the cache is used to solve this problem. For insert and update operations of nonclustered indexes, not every time is inserted directly

Index page, but first determine whether the inserted nonclustered index page is in the cache, if so, insert it directly, and if not, put it into an insert buffer, as if deceiving the database

The nonclustered index has been inserted into the leaf node. The merge operations of cached and nonclustered index page word nodes are then inserted at a certain frequency. The use of the insert cache needs to be satisfied

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

2. Write twice

Two writes bring reliability to innodb, which is mainly used to solve partial write failures (partial page write). Before applying the redo date, we need a copy of the page when the write fails

When it occurs, first restore the page through a copy of the page, and then redo it, which is called doublewrite.

The doublewrite consists of two parts, one is the in-memory doublewrite buffer with a size of 2m, and the other is the 128pages associated in the shared tablespace on the physical disk

That is, two regions, the same size of 2m. When the buffer pool is also refreshed, it does not write to the hard disk directly, but copies the dirty pages to the doublewrite in memory first through the memcpy function.

Buffer, and then write 1m to the physical disk of the shared tablespace twice via doublewrite buffer, and then immediately call the fsync function to synchronize the disk.

3. Adaptive hash index

Because innodb does not support hash indexes, but in some cases hash indexes are very efficient, so the adaptive hash index function appears, and the innodb storage engine monitors the

Index lookup, if it is observed that building a hash index can improve performance, then automatically build a hash index

4. Start, shut down, restore

Innodb_fast_shutdown affects InnoDB table shutdown. This parameter has three parameters: 0, 1 and 2.

Complete all full purge and merge insertbuffer operations when 0 MySQL is closed

1 default value only flushes some dirty pages in the buffer pool to disk

2. Writing logs to the log file will not cause any transaction loss, but recovery will be performed the next time you start.

Innodb_force_recovery affects the recovery status of the entire innodb storage engine. The default value is 0, which means that when recovery is needed, all recovery operations need to be performed. When effective recovery cannot be performed, for example, if the data page occurs, the corruption,mysql database may be down, and the error is written to the error log.

VIII. Essence of InnoDB engine tuning

1. The primary key should be as small as possible to avoid excessive space burden on Secondar Index.

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

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

4. When inserting in large quantities, try to control things by yourself instead of using the autocommit auto-submission switch to control the submission mode.

[cancel automatic submission of things

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

Init_connect='SET autocommit=0' / / add these to the mysqld

]

5. Set the innodb_flush_log_at_trx_commit parameter value reasonably and don't pursue security excessively.

(if innodb_flush_log_at_trx_commit=0 log buffer writes log files to disk every second and does nothing when submitting things)

6 avoid primary key updates, as this will lead to a lot of data movement

IX. How to change the mysql engine in batch in the production environment

Alter table table_name ENGINE = INNODB

Summary of the differences between InnoDB and MYISAM

1.InnoDB does not support indexes of type FULLTEXT.

The specific number of rows of the table is not saved in 2.InnoDB, that is, when select count (*) from table is executed, InnoDB scans the entire table to calculate how many rows there are, but MyISAM can simply read out the number of saved rows. Note that when the count (*) statement contains the 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 rows. To put it colloquially, if you execute a update statement, the mysiam table will lock the entire table, and other insert, delete and update will be shut out until the update statement is executed.

In addition, the row lock of the InnoDB table is not absolute. If the MySQL cannot determine the range to scan when executing a SQL statement, the InnoDB table will also lock the entire table, such as update table set num=1 where name like "% aaa%"

3. For fields of type AUTO_INCREMENT, the InnoDB must contain an index with only that field, but in the MyISAM table, a federated index can be established with other fields.

When 4.DELETE FROM table, InnoDB does not re-establish the table, but deletes it row by row.

The 5.LOAD TABLE FROM MASTER operation does not work with InnoDB, and the solution is to change the InnoDB table to the MyISAM table first, and then to the InnoDB table after importing the data, but it does not apply to tables that use additional InnoDB features such as foreign keys.

11. How to choose InnoDB and MYISAM

Choose the innodb table when your database has a large number of writes and updates and fewer queries or high data integrity requirements. When your database is mainly query-based, with fewer updates and writes, and less stringent business data integrity requirements, choose the mysiam table. Because the query operation efficiency and speed of mysiam table are faster than innodb.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.