Execution of a SQL statement

Recommended (free):SQL

Zero, database driver

• The MySQL driver helps us connect to the database at the bottom level. Only after the connection is established can subsequent interactions be possible.

1. Database connection pool

• Database connection pools include Druid, C3P0, and DBCP
• Using connection pools saves a lot of money The overhead of constantly creating and destroying threads is the famous "pooling" idea. Whether it is a thread pool or an HTTP connection pool, you can see its presence

2. SQL interface

• The thread that processes the request in MySQL obtains the SQL statement after obtaining the request and hands it to the SQL interface for processing.

3. Query parser

• #Parses the SQL statement passed by the SQL interface and translates it into a language that MySQL can understand.

4. MySQL Query Optimizer

• MySQL will choose to use the corresponding index based on theminimum cost principle
#Cost = IO cost CPU cost
• IO cost: That is, the cost of loading data from disk to memory. By default, the IO cost of reading a data page is 1, MySQL reads data in the form of pages. That is, when a certain data is used, it will not only read this data, but also read the data adjacent to this data into the memory. This is a famous program. The principle of locality, so MySQL will read a whole page each time, and the cost of one page is 1. Therefore, the cost of IO is mainly related to the size of the page
• CPU cost: After reading the data into the memory, it is also necessary to detect whether the data meets the conditions and the cost of sorting and other CPU operations. Obviously it is related to Depending on the number of rows, by default, the cost of detecting records is 0.2.
MySQL optimizer will calculate the "IO cost CPU" index with the smallest cost to execute

5. Storage engine

The query optimizer will call the storage engine interface to execute SQL, which means that the actual
• execution of SQL is completed in the storage engine.
Data is stored in memory or disk
• Every time SQL is executed, its data will be loaded into memory. This memory is a very important component in InnoDB. ：
• Buffer Pool

##6. Executor

The executor finally goes according to a series of execution plans Call the storage engine interface to complete the execution of SQL

7. Buffer Pool

Buffer Pool ( Buffer Pool) is a very important memory structure in the InnoDB storage engine. It plays the role of a cache.

• Buffer Pool means that when we query for the first time, we will store the query results in the Buffer Pool, so that later When there is another request, it will first be queried from the buffer pool. If there is no search, it will be searched on the disk, and then placed in the Buffer Pool.
• The data used in the Buffer Pool will be locked.
• 
  

8. Three log files

1. Undo log file

: before the recorded data is modified Appearance

Function: Use undo log files to complete transaction rollback

2. Redo log files

: Record the appearance of the modified data

redo records the value after the data modification, regardless of whether the transaction is submitted or not.

• MySQL In order to improve efficiency, these operations are It is first placed in memory to complete. The updated data will be recorded in the redo log buffer, and then persisted to disk at a certain opportunity.
• 
  

3. Bin log log file:

Record the entire operation process

Implementation method to record Method redo log records in a loop writing method. When writing to the end, it will return to the beginning to write logs in a loop. bin log is recorded by appending. When the file size is larger than the given value, subsequent logs will be recorded to new files Usage scenarios

Property	redo Log	bin Log
File size	The size of the redo log is fixed (it can also be set in the configuration, generally the default is enough)	bin log can be set for each # through the configuration parametermax_bin log_size##bin logThe size of the file (but it is generally not recommended to modify it).
	redo logis implemented by theInnoDBengine layer (that is to say, Innodb storage causes excessive Yes)	bin logis implemented by the MySQL layer, and all engines can usebin loglog
	redo logSuitable for crash recovery (crash-safe) (this is actually very similar to the persistence feature of Redis)	bin logSuitable for master From replication and data recovery

bin log records the entire operation record (this is very important for master-slave replication)

The above is the detailed content of Execution of a SQL statement. For more information, please follow other related articles on the PHP Chinese website!