1. How is a normal query executed

SELECT a,b,c FROM test WHERE id = 101;
Copy the code

step

  1. When the service starts, the Mysql client initializes the connection pool
  2. The connection is established with the server
  3. When a user sends a request, the Tomcat thread executes an SQL statement through the client link pool
  4. The Mysql server gets the requested connection
  5. Mysql server links are read and fetched by worker threads from network links
  6. The worker thread is handed over to the SQL interface for execution, which parses the SQL
  7. It is parsed by SQL parser according to SQL syntax
  8. The query optimizer optimizes the parsed SQL syntax to generate an execution plan
  9. The execution plan is handed over to the executor, which invokes the storage engine interface to execute the specific SQL
  10. Access memory data. If the disk data does not exist in the memory, access the disk data and load it to the memory

Data storage structure

Version 5.7 Default line format: Dynamic

Line structure

Page structure

The size of each page is 16KB

Index structure
Normal index

Secondary indexes

The primary key index is obtained from the secondary index, and the real data is retrieved from the table by the primary key query B+ tree

How is INSERT/UPDATE/DELETE performed

  1. Find the corresponding data page of the data and cache it in the cache pool
  2. Write raw data to undo log
  3. Update cached data
  4. Write data to an in-memory redo log buffer
  5. The executor prepares to commit the transaction
  6. Write redo log buffer to disk in the pre-commit phase
  7. Write binlog to disk
  8. Commit transaction, flush buffer pool data to disk, commit state; Redo log Adds a commit flag

undo log

www.yuque.com/docs/share/…

redo log

www.yuque.com/docs/share/…

binlog

www.yuque.com/docs/share/…

Crash Data was rolled back

  1. Failure after buffer pool data is updated: The undo log data is read and rolled back
  2. Database phase 2 commit, Redo log and binlog cases

    1. Binlog has records, and redo log has a commit flag: transactions that complete normally do not need to be rolled back

    2. The binlog file contains a log, and the redo log is in the prepare state. If there is a matching redo log, the transaction is recommitted, and the redo log adds a commit flag. If no, the data is rolled back

    3. Binlog No log was recorded. The redo log was in the prepare state: data was rolled back

3, the Buffer Pool

www.yuque.com/docs/share/…