High performance MySQL chapter 1th MySQL Architecture and history (1)

MySQL Logical architecture diagram:

First-tier client

Second tier (service layer): the parts that can be publicly extracted for all types of storage engines. This is where the rest of the storage engine is pumped away. Such as: query parsing, analysis optimization, built-in functions, stored procedures, triggers, views.

Third tier (storage engine layer): The storage Engine is responsible for the storage and extraction of MySQL data. The server communicates with the storage engine through the API. These APIs mask the specific implementation differences of different storage engines. The Storage Engine API contains operations such as "Start a transaction", "get a row of data based on a primary key." The storage engine itself does not parse SQL.

Note: Mongdb also has a storage engine.

The database controls concurrent transactions through read and write locks, also called shared and exclusive locks.

With regard to lock granularity, MySQL is given to the storage engine to manage itself, and each storage engine can implement its own lock granularity and lock policy.

Different locking strategies bring different effects, with the least cost of table locks, but the worst concurrency. Row-level locks have the best concurrency performance, but are the most expensive.

Although the lock policy is managed on the storage engine, the service layer uses the table directly, ignoring the locking mechanism of the storage engine layer, when performing ALTER TABLE operations.

Isolation level:

MySQL supports the four isolation levels:

• READ UNCOMMITTED: Uncommitted reads
• Read commited commit (default isolation level for Oracle)
• REPEATABLE Read REPEATABLE reading (default isolation level for MySQL)
• Serializable serialization

Note: Repeatable reading solves the problem of non-repeatable reads (the same data is not the same when it was read two times). Serialization solves the problem of Phantom reading (two executions of the same where statement result in inconsistent result bars).

Question: About serialization, the book says that MySQL locks the data that each line reads. If you do this, you can certainly guarantee repeatable reads, but how can you guarantee phantom reading, because locking the data you read does not block the insert operation?

Deadlock: The cause of the deadlock, one is the operation of the business scenario, and the other is caused by the way the storage engine is implemented.

The InnoDB is able to identify the deadlock and rollback the transaction that holds the least exclusive lock.

Transaction log:

The transaction log can improve the efficiency of the transaction, MySQL in the Update table only need to modify the in-memory data, and then record the modification behavior in the transaction log of the hard disk, the process of this transaction is finished. You do not have to wait for the data file on your hard drive to be returned. The transaction log is a sequential storage space on the hard disk, not a random disk storage, and each add is appended, and the contents of the transaction log are persisted to the hard disk data file. This is called pre-write logging and requires two hard drives to be written.

If a power outage occurs, the data can be recovered through the transaction log after a restart.

Note: MongoDB's newest Wiredtiger storage engine also has the concept of a transaction log, but not a transaction log, called a pre-write log (Wal:write-ahead Logging), because Mongdb has no transactions.

High performance MySQL chapter 1th MySQL Architecture and history (1)