Characteristics and optimization method of InnoDB engine

1. What is the InnoDB engine?
The InnoDB engine is another important storage engine for the MySQL database, and is becoming the new standard released by MySQL AB, which is included in all binary packages, compared to other storage engines, the INNODB engine has the advantage of supporting acid-compliant transactions ( Similar to PostgreSQL), as well as parameter integrity (with foreign keys), and so on. Now innobase implement dual authentication authorization. MySQL5.5.5 The default storage engine is the InnoDB engine. 2.InnoDB engine features.

1). Support transactions (a transaction is a logical set of operations that make up each unit of this set of operations, either fully successful or fail completely)

2). Row-level locking (typically locks the current row when updating): With an index implementation, the full table scan will still lock the entire table, paying attention to the effect of the gap lock.

3). Read-write blocking is related to the transaction isolation level.

4). It has very efficient caching features, can cache indexes, and can cache data.

5). The entire table and primary key are stored in cluster form, forming a balanced tree.

6). All secondary Index will hold the primary key information.

7). Support for partitioning, tablespace. Similar to Oracle database.

8). Support for FOREIGN KEY constraints, full-text indexing is not supported, supported after 5.5, no longer supported.

9). Compared to MyISAM, InnoDB has higher requirements for hardware resources.

Suitable production scenarios for the 3.InnoDB engine

1). Businesses that need to support transactions (e.g. transfer, payment)

2). row-level locking is a good fit for high concurrency, but it is necessary to ensure that queries are completed by index.

3). data Read and write and update are more frequent scenarios, such as: Bbs,sns, Weibo, and so on.

4). data consistency requires a high level of business. such as: transfer, recharge, etc.

5). Hardware Device memory is large, can use InnoDB better cache capacity to improve memory utilization, can reduce the cost of disk IO.

4.InnoDB Engine Tuning Essentials:

1). the primary key is as small as possible to avoid having too much space burden on the secondary index.

2). Avoid full table scans, because table locks are used.

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

4). In large batches of small inserts, as far as possible to control their own transactions and do not use autocommit automatic submission, there are switch parameters can control the submission.

5). set Innodb_flush_log_at_trx_commit parameter value rationally, do not pursue security excessively.

If the value of Innodb_flush_log_at_trx_commit is 0,log buffer, the log file is written to the disk every second, and no action is taken when committing the transaction.

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

Characteristics and optimization method of InnoDB engine