Reading Notes-high-performance MySQL

 

 

MySQL Server Architecture logic diagram

 

 

Exclusive lock

To solve concurrency, you need to use locks, including shared locks and exclusive locks, that is, read locks and write locks ).

Read locks are shared. Multiple users can read the same resource without interfering with each other. Write locks are exclusive locks, blocking other write locks and read locks,

 

 

Lock Granularity

 

To achieve better concurrency, you should lock only the data to be modified without locking all resources. The smaller the lock granularity, the larger the concurrency that can be performed at the same time. However, if the granularity is too small, the management of locks (check locks, obtain locks, release locks) will consume a lot of more resources, and the system performance will also be affected; the primary task of the database is read/write, not to manage locks. Therefore, we should consider the compromise method. The lock policy is to consider the balance between the lock overhead and data security.

 

Table lock)

Lock the entire table. The write lock priority in the read/write application queue is higher than the read lock. Other read and write operations are prohibited when there is a write operation.

Row locks)

It supports the maximum concurrency and overhead.

 

 

Transactions

A transaction, an atomic SQL operation, is also called a unit of work.

Acid

Atomicity: An atomic unit of work that cannot be divided, either full execution or full failure.

Consistency consistency ranges from one type of consistency to another, and the operations previously performed will not affect the database content.

The result is visible only after the isolation transaction is completed, and the intermediate process is invisible.

Durability: The data changes after a persistent transaction is committed are permanent.

 

Isolation level

The isolation level is divided into four levels, which define which results are visible and which are invisible in the transaction process.

Readuncommitted read uncommitted content

All transactions can see the execution results of other uncommitted transactions, also known as dirty read)

Readcommitted

At the beginning of the transaction, you can only see the transaction changes that have been committed. The changes in data are invisible to the outside world from the beginning to before the transaction is committed. It is also called nonrepeated)

Repeatable repeatable

When another transaction is committed during the transaction execution, the transaction cannot be changed. You can view the changes only after the transaction is completed.

One exception is Phantom read, that is, when other new records are added to the transaction during execution, the transaction will find one more record. Except for InnoDB, Phantom reads may not occur depending on the Implementation of search engines.

Serializable can be serializable, with the highest isolation level, the highest cost and the lowest performance. The magic read problem is solved by force transaction sorting. Locking each row of read data can cause a large number of timeout and lock contention.

 

Deadlock

The mechanism by which InnoDB handles deadlocks is to roll back transactions with at least exclusive row-level locks (a rough estimate of the easiest rollback transaction ).

 

Transaction log:

Transactions adopt the write ahead LOG method.

First update the data copy in the memory, and then write the logs into the log file, involving sequential Io disk operations. Wait for a certain time before synchronizing data through the log, which replaces the large number of random Io operations required to directly write data to the disk. Write Data to the disk using two disk write operations.

 

 

Two Phase Lock Protocal)

A MySQL transaction can obtain the lock at any time, but the lock is released only when it is committed or rolled back.

Multi-version Concurrency Control)

This is achieved by saving data snapshots. This means that no matter how long a transaction instance runs, the data seen during the process is the same. The data in a table seen by different transactions may be different.

MVCC only works at the Repeatable read and read committed transaction isolation level

 

Implementation of transaction isolation level Repeatable read:

 

Add two hidden fields, create_version and delete _ version, to each row. An incremental system version number is generated at the beginning of each transaction.

The insert record create_version is the current transaction version.

Update creates a new row copy record. The create_version record is the current transaction version number, and the delete_version record is marked as the current transaction version number.

Delete indicates that the delete_version of the old record is the current transaction version number.

 

Select

Query requires you to view the information created before the start of the transaction or created by the current transaction

At the same time, the transaction is deleted after or has not been deleted.

(Create <= cur) & (delete = NULL | Delete> cur)

 

The advantage is that a large number of read operations do not need to be locked to increase the read speed;

The disadvantage is that redundant data is recorded for each row, with more check operations and data sorting operations.

This can be: http://blog.csdn.net/chen77716/article/details/6742128

 

 

View table status

Showtable status like 't_ sales_opportunity'

 

MyISAM table repair

Checktable mytable

Repairtable mytable