1. SQL operation flowchart (InnoDB storage engine)

2. Flow chart analysis

1) The foreground operation triggers the mysql server to execute the request

Mysql is triggered by various operations of the foreground user, and a network connection is established with the database server through DBCP, C3P0, druid, and other database connection pools in the Web project

After the thread in the database connection pool listens to the request, it responds to the SQL statement received through the SQL interface to the query parser. The query parser resolves which fields of the table to be queried and what the query conditions are according to the SQL syntax. Then through the query optimizer processing, select the SQL optimal set of execution plan, and then the executor is responsible for calling a series of interfaces of the storage engine, execute the plan to complete the execution of the entire SQL statement

2) InnoDB storage engine – Buffer pool completes basic update operation

It is up to the storage engine to execute these execution plans. As shown in the figure, the first update of the user table with id=10 is definitely not available in the buffer pool. The raw data to be updated must be loaded from disk into the buffer pool first

At the same time, to ensure the security of concurrent data updates, this data is locked first to prevent other transactions from updating

Then, back up the updated value to undo log for easy rollback

Finally, the data in the cache page is updated to the latest data, which completes the execution process in the buffer pool

Redo Log and BinLog ensure transaction reliability

After the data is updated in the buffer pool, the updated data is written to the redo log and bin log logs in sequence (the data is still in the memory, and the subsequent flush policy is shown in the figure). In general, a double-write policy is configured to prevent data loss. After the redo log falls, bin log falls. Write the bin log file name, file path, and commit mark synchronization sequence to the redo log (commit mark updated to the redo log is an important criteria to determine whether the transaction committed successfully). The redo log and bin log ensure data consistency at the physical and logical levels, respectively, as shown in the figure

4) Persist the operation of the transaction

Backstage after a series of operations, in front of the InnoDB storage engine and a IO thread, during the slack in the database pressure, will be updated in the buffer pool, but haven’t written to disk data in the data (dirty data, because the disk and memory data has been inconsistent) to brush to disk, persistence to complete the transaction, as shown in figure

3. buffer pool

1) Memory structure

Query parameter: innodb_buffer_pool_size, default size 128 MB.

Note: In the buffer pool, the buffer page corresponds to the data page in the disk. In addition, there is a memory that describes the data, which is about 5% of the data page, or about 800 bytes. Therefore, the actual memory of a buffer pool is a bit larger than 128 MB.

2) 3 big linked lists

  • The free list

The buffer pool does not have a separate space to maintain the free list. Instead, two Pointers, free_pre and free_NEXT, are maintained in the description data, respectively, which constitute the free list.

The data is loaded into the cache page in the buffer pool, and the cache page node for that is deleted from the free list.

To determine whether the data has been loaded into the buffer pool, the hash table data structure maintained by the database itself needs to be introduced. The table space number + data page number is used as a key, and the address of the cached page is used as a value.

  • Flush the list

Flush flush flush flush flush flush flush flush flush flush flush flush flush flush flush flush

Avoid flushing all data in the entire buffer pool each time.

After the data is flushed to disk, the cache page is removed from the Flush list and added back to the free list.

– LRU list (least recently used)

The data structure is the same as the free linked list, which records the hit ratio of cached page data. If the data of a cached page is frequently added, deleted or modified, it is called high hit and is ranked at the head of the linked list, and vice versa.

When a piece of data is manipulated, the cache page is promoted to the head of the LRU list.

If there are not enough cached pages, infrequently manipulated data at the end of the LRU is flushed to disk and cached pages are added to the free list again.

Note: due to mysql pre-reading mechanism and a full table scan, often the adjacent data and the entire table data pages loaded into the buffer pool, the problem is not caused by the operation of data occupy the lru list the front of the plane, and may be pushed into operation data of the tail, causing the tail to reload data.

Lru list optimization

Parameter: Ratio of hot and cold data Innodb_old_blocks_pct The default value is 37

Time threshold for moving cold data to hot data Innodb_old_blocks_time The default value is 1000 ms

4. undo log

In this case, the data needs to be rolled back to the state before the update. Undo log is introduced to record the reverse operation of the current transaction operation (that is, the data before the operation), so as to ensure the rollback of data after the transaction failure.

The reverse log is logged to the redo log after the disk data page is loaded into the buffer pool cache page and before the data is updated.

5. redo log

The buffer pool updates the data submitted by the transaction. If the power fails before the transaction is flushed into the disk, the data will be lost if broken. Therefore, the redo log mechanism is introduced to ensure that the transaction is recorded in the redo log file when the transaction is committed. After the power failure, you only need to redo the transaction according to the redo log to ensure that the transaction data is not lost.

In addition, redo logs are written sequentially and perform much better than random writes.

Redo log format: log type + tablespace number + data page number + offset + change bytes + specific value.

Note that the redo log is not appended one by one. Instead, the redo log block is a 512K data structure, similar to the number of bytes per byte stream. When mysql is started, a redo log buffer pool is created to store the redo log block. The default space is 16 MB.

When does the redo log buffer flush?

1) More than half of the redo log buffer was written

2) The Innodb_flush_log_at_trx parameter controls several flush strategies after a transaction is committed

3) The background thread brushes the disk every 1s

4) When mysql is down