Summary of MySQL architecture


Summary of MySQL architecture

  1. Architecture

    *Connector: used to support the interaction between various languages and SQL, such as PHP, python, JDBC of Java;
     *Management services & Utilities: system management and control tools, including backup and recovery, MySQL replication, cluster, etc;
     *Connection pool: connection pool, which manages the resources to be buffered, including user password, permissions, threads, etc;
     *SQL interface: used to receive SQL commands from users and return query results needed by users
     *Parser: used to parse SQL statements;
     *Optimizer: query optimizer;
     *Cache and buffer: query cache, in addition to the cache of row records, there are table cache, key cache, permission cache, etc;
     *Pluggable storage engines: plug-in storage engines, which provide API for service layer to deal with specific files
  2. Hierarchical structure

    Connection layer: connection with client and password verification
           Service layer: this method analyzes, generates execution plan and returns results
           Storage engine layer: store data and provide data reading and writing
  3. How is an updated SQL executed?

    What’s the difference between the operation process of update and query? The difference lies in the operation record after getting the qualified data;
    When we change the data, we will read the data from the disk to the memory, which is called the concept of “also”. The size of the operating system is generally 4K, while the size of InnoDB is generally 16K;
    The operation of data modification in the disk is too slow, so the buffer pool technology is provided to read the data into the memory area. In the next reading, how to use the data directly if it exists, instead of accessing the disk;
    This memory area is called buffer pool
    When modifying data, the data in memory and disk are inconsistent, which is called dirty page; InnoDB has a special thread to write the data in memory area to disk at intervals, which is called dirty page brushing;

    4. InnoDB memory structure and disk structure
    Summary of MySQL architecture

  • Buffer pool

    The cache is the page information

  • redo log

    If the data has not been flushed into the disk, the database will be down and the data will be lost;
    This log is a log file that InnoDB does to modify the page,
    Database recovery operation from this file at startup (crash safe implementation)
    Redo log is called redo log, and the matching process between log and disk is called wal technology. The receiver technology in the integration of sparkstream’s Kafka uses wal technology, which writes log first and then disk

    Why do we use log first to write disk?

    Log writing is sequential I / O, which is reflected in Kafka. It is fast and efficient


    1. It is implemented by InnoDB and supports crash recovery
    2.It records what changes have been made to this page, physical log
    3. Fixed size. Once it is full, the trigger mechanism will synchronize the redo log with the disk; the subsequent logs will be covered

  • undo log

    Undo log (undo log or rollback log)The data state before the transaction is recorded(excluding select). If there is an exception when modifying data, undo log can be used to implement the rollback operation (keep atomicity).

update user set name = 'ldp' where id=1;

technological process:

1. When the transaction starts, the data is read from the memory area or disk and returned to the server
2. The actuator modifies the data to LDP
3. Record name = LLL to undo log
4. Record name = LDP to redo log
5. Engine interface, modify data in memory
6. Transaction submission


The log file of service layer records the logs of DDL and DML, and appends the form to realize data recovery and master-slave replication
When modifying data, there is a cache to use to cache -- > write data to redo log (prepare submit) - > record to binlog, (COMMIT) status -- > update complete

In redo log, there are two phases of commit (prepare and commit)