Introduction to MySQL logical architecture and storage engine

MySQL logical architecture:

Concurrency control: implemented by locks

Read Lock: Also called shared lock, read lock does not block each other. A a,b,c,d can read the table after the lock table but cannot write the table.

Write Lock: Also called exclusive lock, write lock mutual blocking. A plus exclusive lock, other threads cannot read and write to the table.

Size of Lock:

Table Lock: Locks A table, the concurrent granularity is small. Represents the storage engine MyISAM

Row Lock: Lock a row of data, the concurrent granularity, the performance of the concurrent Operation table is good. Represents the storage engine InnoDB. The lock-granularity small system also has a large cost for locks.

If you read a lock on a table, then other threads will not be able to write to the table, if it is a row lock, then the thread only blocked read and write to this line of data, other rows in the table data other threads can read and write, improve concurrency performance.

Transactions: the equivalent of the N package into a statement, the package of n statements in the success of all successful execution, if one of the statements did not succeed, the transaction rollback to the original state transaction execution failed to write to the hard disk.

ACID (atomicityconsistency isolation Durability) atomicity, consistency, isolation, persistence. Is the characteristics of the transaction storage engine.

Atomicity: A transaction is equivalent to a packet of n statements, either successfully executed or failed, and it is atomic that a transaction cannot be successfully executed as part of a transaction.

Consistency: Transitions from one consistent state to another consistent state. Either successfully converted to a successful state or the failure is rolled back to its original state.

Isolation: A transaction does not see the B transaction.

Persistence: The success of a transaction is written to the hard disk, which is called persistence.

Storage Engine:

• The database exists in the directory, and the table in the library exists in that directory.

• View storage engine: Show table status like ' table_name ' G;

MyISAM Storage Engine

• There are 3 files in the table. MyD stored data. Myi stored index. frm stored table definition.

• Support for table locks

MyISAM tables are more prone to failure when they are large, requiring manual repair of the table, and the repair time can be very long. Repair Table step: Check the table, and then fix the table.

• Compressed table

InnoDB

• The table has 2 files in the IBDATA1 data file and the. frm table definition file in the database directory.

• Support Services

• Support for row locks

• FOREIGN KEY constraint

Based on clustered index, clustered index has very high performance on primary key query.

· InnoDB supports hot backups, while all other storage engines do not support hot backups.

Select the appropriate storage engine:

· MySQL5.5 The default storage primer engine is InnoDB, the simplest answer is if you don't know which storage engine to use then you use InnoDB, because it supports transactions and performs well. Some people say MyISAM performance is faster than InnoDB, which is not necessarily, the book said a lot of times InnoDB let MyISAM.

Select basis:

1, transaction: InnoDB support transactions, MyISAM is not a transaction-type storage engine.

2, INNODB Support Online hot backup.

3, Crash recovery, InnoDB is a transaction-type storage engine support, MyISAM crash after the repair of a large table is very slow.

4, unique characteristics, such as clustered index, foreign key constraints, row locks and so on.

If the data security requirements are not high, and the main read and the database table is not very large scenarios to apply MyISAM.

The amount of data is very large then still use InnoDB bar, when read and write very frequently when the row-level lock performance better.