The most important and distinctive feature of MySQL is his storage engine architecture, which is designed to separate query processing (precessing) and its system tasks (Server Task) from the storage/extraction of data.
1.1 MySQL Logic Architecture
Basic Service TierThe first layer structure: including connection processing, authorization authentication, security and other basic service functions;
Core Service TiersSecond-tier architecture: Includes query parsing, parsing, optimization (including rewriting of queries, determining the reading order of tables, selecting appropriate indexes, etc.), caching, and built-in functions, all of which are also implemented in this layer: stored procedures, triggers, views, etc.;
Storage Engine LayerThird layer structure: response to the upper server request, responsible for data storage and extraction;
1.2 concurrency control
read/write lockMySQL solves concurrency control problems by locking systems consisting of two types of locks. These two types of locks are known as shared locks and exclusive locks (exclusive lock);
Lock particle size
MySQL's lock granularity includes table and row-level locks. The lock strategy is to find a balance between the cost of locking and the security of the data (concurrent processing support), which will of course affect performance;
table Lock
Locks the entire table (MyISAM table or ALTER table operation).
row-level lock (row lock)
Row-level locks provide maximum support for concurrent processing (as well as maximum lock overhead), row-level locks are implemented only at the storage engine level, and the MySQL server layer is not implemented, and the server layer has no knowledge of lock implementations in the storage engine.
1.3 BusinessA transaction is a set of atomic SQL queries, or a separate unit of work. The statements within the transaction are either successfully executed or all failed to execute.
ACID Properties
atomicity (atomicity)A transaction must be considered an indivisible minimum unit of work, and all operations in the entire transaction either commit successfully or all fail back, and for a transaction it is not possible to perform only a subset of the operations, which is the atomicity of the transaction.
Consistency (consistency)The database is always transitioning from one consistent state to another, and any modifications made within the transaction are not saved to the database until the transaction is committed.
Isolation (Isolation)Changes made by a firm are not visible to other transactions until they are finally committed.
persistence (Durability)Once a transaction is committed, any modifications it makes will be persisted in the database.
Isolation Level (
TRANSACTION Isolation level
)There are four isolation levels defined in the SQL standard, each defining the modifications made in a transaction, which are visible within and between transactions, and which are not. Lower-level isolation can typically perform higher concurrency and lower system overhead.
UNCOMMITTED read (READ UNCOMMITTED)
Modifications in the transaction, even if they are not committed, are visible to other transactions; there is a dirty read (Dirty read) problem;
Submit Read (COMMITTED)
A task modification is not visible to other transactions from the beginning until it is committed;
REPEATABLE READ (repeatable Read)
The default isolation level of MySQL, which guarantees that the same record is read multiple times in the same thing, but this can be a phantom read (Phantom read) problem, which is when a transaction reads a record in a range, another transaction inserts a new record within that range. When the previous transaction reads the range record again, the Phantom Row (Phantom row) is generated, and the InnoDB is solved by the multi-version concurrency control (mvcc,multiversion Concurrency control);
Serializable (SERIALIZABLE)
The highest isolation level, which is enforced through serial execution of the transaction to avoid Phantom read problems; SERIALIZABLE locks on every row of data read (that is, all unformatted SELECT statements are implicitly converted to select ... Lock in SHARE MODE), which may cause problems with timeouts and lock contention;
comparison of four isolation levels
| Isolation level |
Dirty Read possibility |
Non-repeatable possibilities |
Phantom reading Possibilities |
Locking Read |
| READ UNCOMMITTED |
YES |
YES |
YES |
NO |
| READ COMMITTED |
NO |
YES |
YES |
NO |
| Repeatable READ |
NO |
NO |
YES |
NO |
| SERIALIZABLE |
NO |
NO |
NO |
YES |
dead LockDeadlocks are two or more transactions that occupy each other on the same resource and request a lock on the resources occupied by the other, leading to a vicious cycle. Deadlocks can occur when multiple transactions attempt to lock resources in different order. A deadlock occurs when a lock transaction locks the same resource at the same time. InnoDB handles deadlocks by rolling back transactions that hold the fewest row-level exclusive locks.
transaction LogTransaction logs can help improve the efficiency of transactions. With transaction logs, the storage engine modifies the table's data only by modifying its memory copy (buffer poor, which is read into the in-memory block), and then logging the modification behavior to a persistent (asynchronous flush) transaction log (redo log log file) on the hard disk. Instead of persisting the modified data itself to disk each time. The transaction log is appended so that the log operation is sequential I/O in a small area of the disk, rather than a random I/O that needs to move the heads in multiple places on the disk, the transaction log is relatively much faster. After the transaction log is persisted, the in-memory modified data can be slowly brushed back to disk in the background. Most of the current storage engines are implemented in this way, which we often call pre-write logging (Write-ahead Logging), which requires writing two disks to modify the data. If the modification of the data has been recorded to the transaction log and persisted, but the data itself has not yet been written back to the disk, the system crashes and the storage engine recovers this partially modified data automatically when it restarts.
a transaction in MySQLMySQL defaults to Auto-commit (autocommit) mode. If you do not explicitly start a transaction (begin or start transaction), then each query is committed as a transaction, in the current connection (session, Global), You can start or disable the autocommit mode by setting the autocommit variable. Show variables like '%autocommit% '; Set autocommit = 0; Non-transactional tables, without a commit or rollback concept, cannot be revoked for changes to their records. The Data definition language (DDL) or lock tables, such as ALTER TABLE, enforces commit commits to commit the current active transaction before execution. MySQL sets the isolation level by executing the SET TRANSACTION isolation levels repeatable READ command.
implicit and explicit lock- inThe InnoDB uses a two-phase locking protocol (two-phase locking protocol). Locks can be performed at any time during the execution of a transaction. Locks are released only when a commit or rollback is executed, and all the locks are released at the same time. InnoDB automatically locks when needed, depending on the isolation level. This is an implicit lock; InnoDB also supports explicit locking through specific statements: Select ... lock in share mode (Shared Lock) Select ... for update (exclusive lock) additionally MySQL in service The lock tables and unlock tables statements are also supported by the device layer, regardless of the storage engine.
1.4 Multi-version concurrency control (MVCC)
Implementation Method
MySQL's transactional storage engine does not implement a simple row-level lock. Based on the consideration of improving concurrency performance, the logic of (Mvcc,multiversion Concurrency control, multiple versioning concurrency controls) was added. MVCC can be understood as a variant of row-level locks, which in many cases avoids the overhead associated with locking operations. MVCC is achieved by saving the data at a time snapshot. Regardless of how long it takes to execute, the data that each transaction sees is consistent. The MVCC of InnoDB is achieved by saving two hidden columns after each row of records. These two columns, one saving the creation time of a row, the expiration time (or deletion time) of a saved row. Of course the storage is not the actual time value, but the system version number (Sestem version numbers), each start a new transaction, the system version number is automatically incremented. The system version number of the transaction start time as the version number of the transaction, which is used to compare the version number of each row of records queried.
Select
InnoDB checks each row of records according to the following two criteria:
- InnoDB only the version that encounters the current transactional version of the data row (that is, the system version number of the line is less than or equal to the system version number of the transaction), which ensures that the transaction reads the row, either before the transaction begins, or the transaction itself is inserted or modified.
- The deleted version of the row is either undefined or larger than the current transaction version number. This ensures that the transaction is read to a row that has not been deleted before the transaction begins.
InsertInnoDB saves the current system version number as the row version number for each newly inserted row.
Delete
InnoDB saves the current system version number for each row deleted as a row delete identity.
UpdateInnoDB to insert a row of records, save the current system version number as the line version number, and save the current system version number to the original line as the row delete identity.
1.5 MySQL's storage engine
InnoDBInnoDB data is stored in a table space (Tablspace). The tablespace is a black box managed by InnoDB and consists of a series of data files. The InnoDB employs MVCC to support high concurrency and achieves four standard isolation levels. Its default level is REPEATABLE READ (repeatable read), and the presence of Phantom reads is prevented through the gap lock (next-key locking) policy. The gap lock allows the InnoDB not only to lock the rows involved in the query, but also to lock gaps in the index to prevent the insertion of phantom rows. The InnoDB table is built on a clustered index table. Clustered indexes have a high performance on primary key queries. The primary key column must be included in the InnoDB table's level two index (secondary index, non-primary key indexes). InnoDB supports hot backup: MySQL exterprise backup Xtrabackup.
engine for conversion tablesALTER TABLE table_name ENGINE = INNODB;
High performance MySQL notes: 1th mysql Architecture
