Section 1 basic procedures for SQL execution

1.1 logical architecture diagram of mysql



Broadly speaking, mysql is divided into a service layer (where functionality across storage engines is located) and a storage engine layer.

  • The Server layer contains connectors, query caches, analyzers, optimizers, and actuators covering most of the functionality, as well as built-in functions.
  • Storage engine is responsible for data storage and extraction, and its architectural pattern is plug-in, pluggable. Support InnoDB (5.5.5 as the default engine), MyISAM, Memory and other storage engines
  1. Establish a connection: The first step is to establish a connection to the database. Connectors are responsible for establishing connections to clients, obtaining permissions, and maintaining and managing connections. After the classical TCP connection, the connector starts to authenticate the identity, and you need to enter the user name and password. However, as long as the connection is continuous, even if you use the super account to modify the connection permission is not aware, show processList to check idle connections. The disconnect interval is controlled by the wait_timeout parameter. The process of establishing a connection is complicated, so you are advised to use long connections as much as possible. (1. After periodically disconnecting long connections, run mysql_reset_connection to initialize resources after each large operation.)
  2. Query cache: After receiving the SQL statement, it will take the statement to the cache to query a wave, if hit directly returns the result, high efficiency, but experts recommend not to use the cache in most cases, because cache invalidation is very frequent, once a table is updated, all the query cache on the table will be invalidated. Unless the system configuration table is suitable for caching, the business table is not suitable. Mysql removed the cache query function directly after version 8.0
  3. Mysql > select a from db. Test; SQL > select a from db. Test;
  4. Optimizer: Optimizer is to optimize the user’s statements, trying to choose the execution method that he thinks is best (though rarely wrong). After the optimizer executes, the execution plan of the SQL is determined.
  5. Actuators: mysql knows what you need to do through the parser and executes the battle plan advised by the optimizer. At the start of the execution, the executor will determine whether you have permissions on the table, if not, return no permissions error. If so, invoke the interface provided by the engine


Select * from T if there is no field K in table T and you commit select * from TwhereK =1, the error will be reported at that stage?? The parser phase, of courseCopy the code


2.1 SQL statement update process

2.1.1 Redo log (InnoDB only)

Rigid update scenario: If the disk needs to be written for each update operation, and then the disk needs to find corresponding records and update again, the I/O cost in the whole process is too high.



Alternative ideas:

  • Write redo log first, then disk. Update data when the disk is not busy
  • Redo log space is limited. Write pos is the position of the current record. It moves backwards as you write, and returns to the beginning of file 0 at the end of file 3. Checkpoint is the current point of erasure, which is also iterated backwards, updating the record to the data file before erasure
  • Between write POS and checkpoint is the empty portion of the pink board that can be used to record new operations. If write POS catches up to checkpoint, the “fan board” is full and no new updates can be performed. You must stop to erase some records and push checkpoint forward
  • With Redolog, the database has data recovery capability


Binlog Logs (server layer)

  • The redo log is a physical log. The binlog is a logical log. It records the original logic of the statement
  • Redolog is written in a loop, fixed space will run out. The binlog file can be appended. The binlog file will be switched to the next one after being written to a certain size without overwriting the previous log

Write to redolog: two-phase commit

If you don’t use two-phase commit, an interrupt occurs while writing redolog and then binlog.

  • 1. Write redo log and then binlog. MySQL restarts unexpectedly when the redo log is complete. The value of c in the redo log is 1 because the system can recover data even after a redo log has crashed. But because the binlog didn’t finish writing the crash, the binlog didn’t record the statement. Therefore, when the log is backed up later, the saved binlog does not contain this statement. Then you will notice that if you need to restore the temporary library with this binlog, the temporary library will miss the update due to the loss of the binlog, and the restored line c will have a value of 0, which is different from the original library.
  • 2. Write binlog and redo log. If there is a crash after the binlog has written, the transaction is invalid after the crash because the redo log has not been written, so the value of c in this row is 0. But binlog already records “change c from 0 to 1”. The value of c in the restored row is 1, which is different from that in the original library.

Other usage scenarios: When the database needs to be expanded, it is common to use full backup and binlog of the application. In other words, the two-phase commit is to keep the write states of the two logs logically consistent


3 transaction

In mysql, transactions are implemented at the engine level

3.1 Four characteristics of transactions: ACID

  1. Atomicity: Transactions are indivisible and the overall state is consistent
  2. Consistency: Data integrity remains consistent before and after a transaction occurs
  3. Isolation: Multiple users concurrently use transactions without affecting each other
  4. Persistence: After a transaction is committed, changes to the data are permanent

3.2 Transaction isolation level

  1. Uncommitted reads: Dirty reads will result
  2. Commit read (default isolation level of Oracle and SQL Server) : Repeatable reads are also snapshot reads, which results in unrepeatable reads
  3. Repeatable reads (Mysql default isolation level) : In this level, the common query is to use the same “snapshot” read, but, unlike the “read committed”, when a transaction starts, is not allowed to “modify operations (Update)”, and “not repeatable read” precisely because between two reads the data change, therefore, “repeatable read” can effectively avoid the “not repeatable read”, But it is impossible to avoid “phantom reads” because phantom reads are caused by “Insert or Delete” operations.
  4. serialization

3.2 Implementation of Transaction Isolation (Repeatable Read)



The current value is 4, but when querying this record, transactions started at different times will have different Read-views. As you can see, in views A, B, and C, the value of this record is 1, 2, and 4, respectively. The same record can have multiple versions in the system, which is called multi-version concurrency control (MVCC) for the database. For read-view A, to get to 1, the current value must be rolled back by all of the operations in the graph.

Rollback logs are deleted when the current system does not have earlier logs. Therefore, it is not recommended to use long transactions. Long transactions lock rollback logs and cannot be deleted.

3.3 How transactions are started

  1. Displays the declaration to start, BEGIN, or Start TRANSACTION. The accompanying commit statement is COMMIT, and the rollback statement is rollback.
  2. Set autocommit=0, this command will turn off autocommit for this thread. This means that if you only execute a SELECT statement, the transaction is started and will not commit automatically. The transaction persists until you actively execute a COMMIT or ROLLBACK statement, or disconnect. Some clients start with the set Autocommit =0 command by default, which results in all subsequent queries in a transaction. If the connection is long, this results in an unexpectedly long transaction. Therefore, it is recommended to use set AutoCOMMIT =1 to start the transaction explicitly.


4 Index (1)

The meaning of the index is similar to the contents of a Xinhua dictionary

4.1 Common models for indexes

  1. Hash table: it is a structure that stores data in key-value pairs. As long as we input the key to be searched, records can be obtained at O(1) speed. When hash conflicts occur, a linked list with the same key can be generated using zipper method. And is not conducive to interval query
  2. Ordered array: Ordered array in the equivalent query and interval range query performance O(logn), but obviously not suitable for more modified scenarios, he will spend a lot of performance to maintain his order, high cost
  3. Binary tree: A complete binary tree obviously has high query efficiency. However, as the insertion efficiency increases with the height of the tree, the number of DISK I/OS also increases greatly. Once disks are involved, the performance deteriorates. Therefore, the binary tree is transformed into an N-tree under the restriction of the disk. The short and fat feature ensures that the disk read times are few and the read speed is not slow.
  4. Both leaf nodes and non-leaf nodes of A B tree will store data, which leads to a decrease in the number of Pointers that can be saved by non-leaf nodes. If the number of Pointers is small, storing a large amount of data can only increase the height of the tree

4.2 InnoDB index model

Each index of InnoDB corresponds to a B+ tree, which is divided into primary key index (also called cluster index) and non-primary key index according to the content of leaf node. The primary key index of the leaf node stores all information, while the non-primary key index stores the primary key. If you need to obtain other fields of record, you need to perform a table back operation.

4.3 Index Maintenance

  • Page splitting and page merging can be triggered when data is inserted or deleted. The default size of each page is 16K, which maintains a approximate utilization range.
  • Obviously, trying to have a single primary key for a table will result in even space utilization and less frequent page splits and merges
  • The selection of primary key fields should not be too large, because the page children of the secondary index are primary keys, which will cause other secondary indexes to take up much more space


5 Index (2)

5.1 Overwriting indexes

Overwrite indexes can directly return queried columns at query execution time without going back to the table

5.2 Left-most Prefix Rule

  1. B+ trees can use the left-most matching principle of indexes to locate records
  2. When establishing a joint index, the index reuse capability is the evaluation standard. For example, if you want to use INDEX A and index B, you can only re-create a secondary index for index B

5.3 Index push-down

Again, let’s take the union index (name, age) of the citizen table. Now if there is a demand: retrieve all the boys in the table whose name begins with Zhang and whose age is 10. So, the SQL statement is written like this

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;Copy the code

Before MySQL 5.6, you could only return tables one by one from ID3. Find the rows on the primary key index and compare the field values. The index condition pushdown feature introduced in MySQL 5.6 can be used to determine the fields contained in the index during the index traversal process and directly filter out the records that do not meet the conditions to reduce the number of entries back to the table

This can be done within the index, with four returns to the table prior to 5.6, and only two returns to the table after 5.6