MySQL architecture-mysql storage engine-InnoDB

InnoDB

InnoDB supports transaction processing, which processes many short-term transactions, which are often executed successfully rather than rolled back. InnoDB is MySQL's most popular storage engine. Common Non-transaction engine requirements, such as performance and automatic reply, are also fully available.

 

InnoDB stores data in columns and is called a table space ). Table space is black box for users, and InnoDB is responsible for internal processing. In MySQL and later versions, InnoDB can store data and index data to independent files. InnoDB also uses disk partitions to create tablespaces. The tablespace will be detailed later.

 

InnoDB uses MVCC for high concurrency processing. And it implements four isolation levels of the SQL standard. The default isolation level is non-repeated read, and the use of the next-key locking policy avoids the phantom read problem: innoDB locks the index structure instead of locking the interval to avoid Phantom reads caused by data insertion, rather than locking the rows you operate on.

 

InnoDB tables use clustered indexes ). Detailed descriptions in subsequent sections. The index structure of InnoDB is very different from that of other engines. The result is that it is very fast to find the primary key. However, secondary indexes contain primary keys. Therefore, if the primary key is too large. Other indexes will also be large. If there are too many indexes in a table, the primary key must be minimized. InnoDB cannot compress indexes.

 

Currently, InnoDB does not support index sorting, but the MyISAM engine does. Therefore, InnoDB reads data and creates indexes much slower than MyISAM. Any operation to modify the InnoDB table structure will re-create the entire table, including all the indexes.

 

When InnoDB was designed, most servers were slow in disk speed, single CPU, and limited memory. Today, Hard Drive servers with multiple cores, large memory, and fast speed are not very expensive. InnoDB has some scalability problems. The developer is solving the problem. However, many problems still exist so far. In the InnoDB concurrency adjustment chapter, we will detail how to improve the concurrency of InnoDB.

 

In addition to concurrency, InnoDB also provides foreign key constraints, which Mysql itself does not provide. InnoDB also provides the ability to quickly use primary key queries.

 

InnoDB has many internal optimization methods: Pre-read data from the hard disk in advance, and the adaptive hash index is used to quickly search for automatically created hash indexes in the memory, insert cache is also used to accelerate insert. The following sections detail these optimizations.

 

 

InnoDB is quite complex and it is strongly recommended to view official documents.

Thing model and lock http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#innodb-transaction-model

The above tutorial shows you what to pay attention to before using InnoDB.

 

From: http://xiayuanfeng.iteye.com/blog/401050