Shulou(Shulou.com)05/31 Report--

This article mainly introduces what is the implementation of a SQL, the article is very detailed, has a certain reference value, interested friends must read it!

Zero, database driver

The MySQL driver helps us connect to the database at the bottom, and only when the connection is established can we have the following interaction.

Database connection pool

Database connection pools include Druid, C3P0, and DBCP

The use of connection pooling greatly saves the cost of constantly creating and destroying threads. This is the famous "pooling" idea, which can be seen in both thread pooling and HTTP connection pooling.

II. SQL interface

After getting the request, the thread handling the request in MySQL gets the SQL statement and hands it over to the SQL interface for processing.

Third, query parser

Parse the SQL statement passed through the SQL interface and translate it into a language that MySQL can understand.

4. MySQL query optimizer

MySQL will choose to use the corresponding index according to the principle of minimum cost.

Cost = IO cost + CPU cost

IO cost: that is, the cost of loading data from disk into memory. By default, the IO cost of reading data pages 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 will also read the adjacent data into memory. This is the famous principle of program locality, so MySQL reads a whole page at a time, and the cost of one page is 1. So the cost of IO is mainly related to the size of the page

CPU cost: after the data is read into memory, it also tests whether the data meets the criteria and the cost of CPU operations such as sorting, which is obviously related to the number of rows. By default, the cost of testing records is 0.2.

The MySQL optimizer calculates the index with the lowest cost of "IO cost + CPU" to execute

Storage engine

The query optimizer invokes the interface of the storage engine to execute SQL, which means that the actual execution of SQL is done in the storage engine.

The data is stored in memory or disk.

Each time the SQL is executed, its data is loaded into memory, which is a very important component of InnoDB: buffer pool Buffer Pool

VI. Actuator

Finally, according to a series of execution plans, the executor calls the interface of the storage engine to complete the execution of SQL.

7. Buffer Pool

Buffer Pool (buffer pool) is a very important memory structure in InnoDB storage engine, which acts as a cache.

Buffer Pool is the first time that when we query, we will store the query result in Buffer Pool, so that when we have another request, we will first query it from the buffer pool. If we don't look it up in disk, then put it in Buffer Pool.

The data used in Buffer Pool is locked.

Eight or three log files

1. Undo log file: record what the data looks like before it is modified

Function: use undo log files to complete transaction rollback

2. Redo log file: record what the data looks like after modification

Redo records the value of the modified data, regardless of whether the transaction is committed or not

MySQL in order to improve efficiency, so first put these operations in memory to complete, the updated data will be recorded in redo log buffer, and then at some point will be persisted to disk.

3. Bin log log file: record the whole operation process

The size of the nature redo Logbin Log file size redo log is fixed (it can also be set in the configuration, generally the default is sufficient) bin log can set the size of each bin log file through the configuration parameter max_bin log_size (but it is generally not recommended to modify it). The implementation method redo log is implemented by the InnoDB engine layer (that is, it is unique to Innodb storage) bin log is implemented in the MySQL layer, and all engines can use bin log logging. Redo log records in a circular way. When writing to the end, it will go back to the beginning and cycle to write the log. Bin log records by appending. When the file size is larger than the given value, subsequent logs will be recorded on the new file using scenario redo log for crash recovery (crash-safe) (which is actually very similar to the persistence feature of Redis) bin log is suitable for master-slave replication and data recovery

Bin log records the entire operation record (this is of great significance for master-slave replication)

The above is all the contents of the article "what is the situation of implementing a SQL?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.