Mysql architecture

  1. Connection layer: manages connections, authenticates and authorizes
  2. Service layer:
    • The parserSQL statement parsing (lexical, syntax) –> Parse tree
    • The optimizerGenerate execution plan, cost-based index selection –> execution plan
    • actuatorOperation storage engine, return result > query result
    SHOW VARIABLES LIKE 'optimizer_trace'; set optimizer_trace='enabled=on'Copy the code
  3. Storage engine: Provides read and write interfaces for the Server layer
    • InnoDB: 5.1 starts as default storage engine, row locks, supports transactions
    • MySIAM: table level lock, no transaction support
    • Archive: Only select and INSERT operations are supported. The data compression rate is high and is suitable for log and data collection scenarios
    • Memory: Based on Memory storage, hash index query is fast, data loss during restart, temporary tables are used in Memory engine (innoDB if the result is too large or contains text and blob data)

2. Learn about InnoDB

  1. InnoDB memory structure

    • Buffer Pool
    Read: Caches the data read from the disk to the memory in the unit of pages. The next time the same page is read, the cache is directly queried. Write: When writing data, modify the pages in the cache first, and then write the data to the disk through the background thread (brush dirty pages).Copy the code
    • Change Buffer (part of the Buffer Pool)
    Write Change Buffer directly to data that does not require uniqueness checking. Merge data page, background thread, database shutdown, redo log fullCopy the code
    • AdaptiveHashIndex (part of the Buffer Pool)
    This region is the index of the data in the memory regionCopy the code
    • Log buffer (redo)
    SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; 0 Write delay, flush disks once per second (buffer writes to disks every second) 1 Real-time write and real-time flush Every transaction is flushed (directly writes to disks at commit time) 2 Real-time write and delayed flush Every commit is written to the OS cache, and disk flushing is performed by the OSCopy the code
    • Storage locked in memory

  2. The log file

    • Binlog (Server layer)
    Log files on the Server layer record all DDL and DML statements and are logical logs that can be used for primary/secondary replication and data recoveryCopy the code
    • Redo (innoDB)
    Redo logs, which provide crash-safe capability, record what changes are made to the data page, which is a physical log, so that the data is not lost after the log is written to redoCopy the code
    • Undo (innoDB)
    Innodb implements transaction logging and is also used for MVCC snapshot readsCopy the code