Chapter 1 Mysql architecture and history

The most important and distinctive feature of Mysql is its storage engine architecture, which is designed to isolate query processing from other system tasks and data storage/extraction. This separation of processing and storage allows you to choose how to store data based on performance, features, and other requirements.

1.1 logical Architecture of Mysql



  1. The top layer is the basic services layer that most software has, and most web-based client/server tools or services have a similar architecture. (Connection processing, authorization, security, etc.)
  2. The second layer is a special layer of Mysql. Many core functions are located in this layer, including query parsing, analysis, optimization, caching, and all the built-in functions. All functions across storage engines are implemented here: stored procedures, triggers, views, etc
  3. The third layer contains the storage engine. The storage engine is responsible for storing and extracting data from Mysql. InnoDB (transaction support, default lock granularity is row level lock), MyISAM (before version 5.5 is the default engine, concurrency is poor, does not support transaction, default lock level is table lock), MEMORY engine, etc.

1.2 Concurrency Control

  1. When concurrent access occurs, shared or exclusive locks can solve the problem, namely read and write locks.
  2. In order to improve concurrency, resources can be precisely controlled by controlling the granularity of locks, but locks themselves also need to consume resources, so it is necessary to find a balance between the granularity of locks and security, so now the commercial use is generally row-level locks


1.3 transactions

  1. Four characteristics of transactions: ACID (atomic, consistent, isolated, persistent).


1.3.1 Isolation Level

Isolation levels are defined in the SQL standard and are software independent.

  1. READ UNCOMMITTED The READ is not committed
  2. READ COMMITTED Data (a transaction can READ uncommitted data, i.e. a dirty READ)
  3. REPEATABLE READ REPEATABLE READ (execute the same query twice, get different result, focus on difference result)
  4. SERIALIZABLE execution solves the problem of phantom reading (when a transaction reads a record in a range, another transaction inserts a new record in that range, when the previous transaction reads again, phantom rows will be generated. InnoDB uses MVCC to solve this problem, with different results.)


1.3.2 deadlock

The four necessary conditions for deadlocks are cyclic wait, request and hold, inalienability, and mutual exclusion.

Solution: it is best to specify the priority of lock acquisition when writing business code. For example, lock A and LOCK B are called to each other to generate deadlock. This situation will not occur when the lock of A is obtained before the lock of B is obtained.


1.3.3 Transaction logging

Mysql’s transaction logging can improve the efficiency of transactions by apending them, similar to redis’s AOF backup type (each write command is written sequentially to a log file). If changes to the data are recorded to the transaction log and persisted, but the data itself has not been written, the system crashes and the storage engine can recover from the transaction log upon restart. The recovery method depends on the storage engine type.


1.4 Multi-version Concurrency Control (MVCC)

Most of Mysql’s transactional storage engines go beyond simple row-level locking and generally implement MVCC as well.

MVCC: MVCC is implemented by keeping a snapshot at a point in time. Each transaction sees the same data no matter how long it takes to execute, and different data may be read depending on the start time of the transaction. InnoDB MVCC saves two hidden columns at the end of each row record, one to store the expiration time of the row, and one to store the system version number. Each transaction starts, the system version number increases, and the query starts to record the current system version number. If the query ends, it is used to compare whether it is equal. MVCC is only used at the committed read and repeatable read transaction levels. Uncommitted reads always read the most recent rows that do not match the current version of the transaction. Serialization locks all rows that are read.


1.5 storage Engine of Mysql

1.5.1  InnoDB

InnoDB is Mysql’s default storage engine and is good at handling a large number of short-term transactions. Excellent performance.

  1. Data is stored in a table space and consists of a series of data files.
  2. High concurrency is supported with MVCC, repeatable reads by default, and phantom reads are prevented by gap locking (InnoDB not only locks rows involved in queries, but also locks gaps in indexes to prevent phantom rows from being inserted).
  3. It is built based on the clustered index and has high query performance on the primary key. However, its secondary index must contain the primary key column. If the primary key column is large, other indexes will be large. Therefore, when the table has many indexes, the primary key should be as small as possible.
  4. Predictable prefetch, adaptive hash indexes that can automatically create hash indexes in memory to speed up read operations, and insert buffers that can speed up insert operations are used to read data from disk.
  5. Supports hot backup. Other storage engines do not support hot backup

1.5.2 MyISAM (Mainly Insert and SELECT logging applications)

There is no support for transactions, no support for self-recovery after a crash, you can consider using MyISAM if you are using read-only data, the table is small and can tolerate repair.

1.5.6 Storage engine for converting tables

Alter table test ENZGING = Innodb


1.6 version History of Mysql