MySQL's storage engine

What is a storage engine?

For example: A video can be MP4 format or AVI format, but users seem to be similar, the intuitive difference is that occupancy system space and clarity is not the same. Storage engine almost, for MySQL, regardless of what engine to store, users see the data city-like, different engine access, engine functions, the size of the space occupied, read performance may be different.

The most commonly used storage engines for MySQL are MyISAM and InnoDB.

MyISAM: Before mysql5.5 was the default storage engine, it was modified to InnoDB after 5.5 because the InnoDB engine had advantages over transactional referential integrity and high concurrency.

Transactions: (not only for MySQL, all relational databases have transactional characteristics)

A transaction is a logical set of SQL statement operations that make up each SQL statement that makes up this set of operations, either full success or failure at execution time.

Four characteristics of transactions (ACID)

Atomicity (atomicity): An indivisible unit in a transaction, where all operations, such as SQL, occur, or do not occur

Consistency (consistency): Data integrity must persist until and after a transaction occurs

Isolation (Isolation): When a database is concurrently accessed, an executing transaction is not visible to other sessions, and data between multiple concurrent transactions is isolated from each other before execution is completed

Persistence (Durability): Once a transaction is committed, it changes the data in the database permanently, and if an error is not allowed to be revoked, only through the "compensatory transaction"

Opening of a transaction

Start Transcation Open Transaction

Rollback rollback of a transaction and execution of a rollback if an error is performed

Commit COMMIT TRANSACTION Exit transaction

InnoDB engine

The biggest feature is the transaction.

InnoDB shared table space, by default there is only one file, in this file in the classification, can also be opened, a table one for the read

-RW-RW----1 mysql mysql 12582912 February 20:37 ibdata1

InnoDB Engine Features

1. Support Transactions

2. Row-level locking (update is generally locked in the current row): Through the index implementation, the full table scan will still be a table lock, notice the effect of Gap lock

3. Read-write blocking related to transaction isolation

4. Has very efficient cache features, can cache the index, but also can cache the data

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

6. All Secondary index will save primary key information

*7. Support for partitions, table spaces, similar to Oracle

Summary: Support transactions, support row-level locks, support foreign keys, table space, high hardware requirements

InnoDB Engine Tuning Essentials

1. The primary key is as small as possible to avoid excessive space burden on secondary index

2. Avoid full table sweep because table locks are used

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

4. In large batches of small inserts, as far as possible to control their own transactions and not to use automatic submission, there is a switch to control the way of submission

5. Reasonable set innodb_flush_log_at_trx_commit value of 0,log buffer wonderful will be brushed write log file to disk, do not do anything when committing a transaction

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

About InnoDB Some of the parameters in the configuration file: About more optimization references

innodb_additional_mem_pool_size  

Setup method, in the my.cnf file:
innodb_additional_mem_pool_size = 16M

1) Memory utilization aspects:
innodb_buffer_pool_size 
This is the most important parameter of InnoDB., and MyISAM's key_buffer_size have similarities, but also have the difference.
This parameter mainly caches the index of the InnoDB table, the data, and the buffer when the data is inserted.

MySQL's storage engine