Analysis of query process of MySQL 02/14 Update SLTechnology News&Howtos

Analysis of query process of MySQL

2025-02-14 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

The query processing of relational database management system is generally divided into four stages:

See the picture below

How to verify the relationship between these stages in MySQL?

The database version of the experiment here: 5.6.16-64.2-56

OS:CentOS release 6.5

Kernel:2.6.32-431.el6.x86_64

Create a test library and tables and data:

Root@localhost [(none)]: 14: > CREATE DATABASE querydb /! 40100 DEFAULT CHARACTER SET utf8 /

Query OK, 1 row affected (0.00 sec)

Root@localhost [(none)]: 15: > use querydb

Database changed

Root@localhost [querydb]: 20: > create table t (id int auto_increment, name varchar (50), primary key (id)) engine=innodb

Query OK, 0 rows affected (0.02 sec)

Root@localhost [querydb]: 21: > insert into t values (NULL,'a')

Query OK, 1 row affected (0.00 sec)

Root@localhost [querydb]: 21: > insert into t values (NULL,'b')

Query OK, 1 row affected (0.00 sec)

Root@localhost [querydb]: 21: > insert into t values (NULL,'c')

Query OK, 1 row affected (0.01sec)

Open the profile of MySQL

Root@localhost [querydb]: 21: > set @ @ profiling=1

Query OK, 0 rows affected, 1 warning (0.00 sec)

First, let's query a normal sql statement to see what is going on inside MySQL.

Root@localhost [querydb]: 22: > select id,name from t

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

+-+ +

3 rows in set (0.00 sec)

Root@localhost [querydb]: 24: > show profiles

+-- +

+-- +

| | 1 | 0.00103500 | select id,name from t |

+-- +

1 row in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 24: > show profile for query 1

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000313 | |

| | checking permissions | 0.000029 | |

| | Opening tables | 0.000073 | |

| | init | 0.000058 | |

| | System lock | 0.000066 | |

| | optimizing | 0.000007 | |

| | statistics | 0.000044 | |

| | preparing | 0.000025 | |

| | executing | 0.000002 | |

| | Sending data | 0.000321 | |

| | end | 0.000007 | |

| | query end | 0.000018 | |

| | closing tables | 0.000018 | |

| | freeing items | 0.000017 | |

| | cleaning up | 0.000038 | |

+-+ +

15 ows in set, 1 warning (0.00 sec)

It can be seen from the above that

First, the permissions are checked, and after the permissions are checked, the open table operation is performed, and then the metadata is lock, then optimized, precompiled, and finally executed. By the time Sending data is reached, it has been pushed to the storage engine layer to pull the data. Finally, the lock is released, the table is closed, and the cleanup is performed.

Let's start with the characteristics of each stage:

Starting: grammatical Analysis and Lexical Analysis

Checking permissions: user rights check

Opening tables: table permission check

Init: column permission check for a table

System lock: get some lock information about the table

Optimizing: logical optimization (algebraic optimization), main RBO optimization

Statistics: physical optimization (non-algebraic optimization), mainly CBO optimization

Preparing and executing: generate code and execute

Sending data: it is also possible to include the process of executing, extracting, and sending data.

I. query and analysis

1. Simulated sql keyword error

Root@localhost [querydb]: 31: > selectt id,name from t

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selectt id,name from t' at line 1

Root@localhost [querydb]: 31: > show profiles

+-- +

+-- +

| | 1 | 0.00103500 | select id,name from t |

| | 2 | 0.00022225 | selectt id,name from t |

+-- +

2 rows in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 32: > show profile for query 2

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000154 | |

| | freeing items | 0.000026 | |

| | cleaning up | 0.000043 | |

+-+ +

3 rows in set, 1 warning (0.00 sec)

One more

Root@localhost [querydb]: 45: > select id,name fr0m t

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't'at line 1

Root@localhost [querydb]: 45: > show profiles

+-- +

+-- +

| | 1 | 0.00034325 | select id,name from t |

| | 2 | 0.00006925 | selectt id,name from t |

| | 3 | 0.00018800 | select id,name fr0m t |

+-- +

3 rows in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 45: > show profile for query 3

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000134 | |

| | freeing items | 0.000018 | |

| | cleaning up | 0.000036 | |

+-+ +

3 rows in set, 1 warning (0.00 sec)

By comparison, it is found that starting should be grammatical analysis and lexical analysis.

Why grammatical analysis and lexical analysis?

In fact, it is the same as making sentences in our usual mother tongue, such as:

Go to my dinner and eat these four words.

According to our normal logic, the sentence should be: I'm going to dinner.

But computers are different. There may be a lot of them:

Come to me for dinner.

I'm going to dinner.

The computer will combine sentences according to certain rules, just like our subject-predicate-object format, but there is a possibility that there are no grammatical errors or lexical errors but different meanings. Here is the assumption: if I want to eat and ask me to eat, there are no mistakes in grammar and morphology, but the semantics are different, so the computer will do semantic analysis.

The judgment rules here are judged by automata, also known as Turing machines (invented by the great father of computer science: Turing Award must be familiar to everyone ^ _ ^), but there will be a detailed introduction in Longshu (compilation principle).

Second, query and check

Root@localhost [querydb]: 45: > select ida,name from t; # Table exists, field does not exist

ERROR 1054 (42S22): Unknown column 'ida' in' field list'

Root@localhost [querydb]: 19: > show profiles

+-- +

+-- +

| | 1 | 0.00034325 | select id,name from t |

| | 2 | 0.00006925 | selectt id,name from t |

| | 3 | 0.00018800 | select id,name fr0m t |

| | 4 | 0.00096275 | select ida,name from t |

+-- +

4 rows in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 19: > show profile for query 4

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000531 | |

| | checking permissions | 0.000037 | |

| | Opening tables | 0.000133 | |

| | init | 0.000116 | |

| | end | 0.000017 | |

| | query end | 0.000018 | |

| | closing tables | 0.000027 | |

| | freeing items | 0.000032 | |

| | cleaning up | 0.000052 | |

+-+ +

9 rows in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 19: > select id,name from tab; # id,name is a field of table t, but tab table does not exist

ERROR 1146 (42S02): Table 'querydb.tab' doesn't exist

Root@localhost [querydb]: 23: > show profiles

+-+

+-+

| | 1 | 0.00034325 | select id,name from t |

| | 2 | 0.00006925 | selectt id,name from t |

| | 3 | 0.00018800 | select id,name fr0m t |

| | 4 | 0.00096275 | select ida,name from t |

| | 5 | 0.00117675 | select id,name from tab |

+-+

5 rows in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 23: > show profile for query 5

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000621 | |

| | checking permissions | 0.000039 | |

| | freeing items | 0.000055 | |

| | cleaning up | 0.000066 | |

+-+ +

7 rows in set, 1 warning (0.00 sec)

So you can see from the above that you can test the checking permissions here. I'm not testing here. Give the user test no permission at all: grant usage on. To test@'%' identified by 'test'; and give test2 users the right to query only the id column in the t-table: grant select (id) on querydb.t to test2@'%' identified by' test2';], here is a brief test

Checking permissions: check the permissions of the connected users of MySQL.

Opening tables: check the table permissions.

Init phase: perform permission checks on the columns in the table.

Third, check and optimize

The sql statement for the experiment:

Select 1

Select id,name from t

Select id,name from t where 0room1

Three statements to see the changes.

Root@localhost [querydb]: 36: > select 1

+-- +

| | 1 |

+-- +

| | 1 |

+-- +

1 row in set (0.00 sec)

Root@localhost [querydb]: 36: > show profiles

+-+

+-+

| | 1 | 0.00034325 | select id,name from t |

| | 2 | 0.00006925 | selectt id,name from t |

| | 3 | 0.00018800 | select id,name fr0m t |

| | 4 | 0.00096275 | select ida,name from t |

| | 5 | 0.00117675 | select id,name from tab |

| | 6 | 0.00115800 | select id,name,abc from t |

| | 7 | 0.00029450 | select 1 |

+-+

7 rows in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 36: > show profile for query 7

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000196 | |

| | checking permissions | 0.000006 | |

| | Opening tables | 0.000007 | |

| | init | 0.000016 | |

| | optimizing | 0.000010 | |

| | executing | 0.000013 | |

| | end | 0.000008 | |

| | query end | 0.000006 | |

| | closing tables | 0.000001 | |

| | freeing items | 0.000014 | |

| | cleaning up | 0.000019 | |

+-+ +

11 rows in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 36: > select id,name from t

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

+-+ +

3 rows in set (0.00 sec)

Root@localhost [querydb]: 38: > show profiles

+-+

+-+

| | 1 | 0.00034325 | select id,name from t |

| | 2 | 0.00006925 | selectt id,name from t |

| | 3 | 0.00018800 | select id,name fr0m t |

| | 4 | 0.00096275 | select ida,name from t |

| | 5 | 0.00117675 | select id,name from tab |

| | 6 | 0.00115800 | select id,name,abc from t |

| | 7 | 0.00029450 | select 1 |

| | 8 | 0.00074025 | select id,name from t |

+-+

8 rows in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 38: > show profile for query 8

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000274 | |

| | checking permissions | 0.000024 | |

| | Opening tables | 0.000059 | |

| | init | 0.000036 | |

| | System lock | 0.000029 | |

| | optimizing | 0.000008 | |

| | statistics | 0.000031 | |

| | preparing | 0.000021 | |

| | executing | 0.000002 | |

| | Sending data | 0.000172 | |

| | end | 0.000011 | |

| | query end | 0.000012 | |

| | closing tables | 0.000013 | |

| | freeing items | 0.000018 | |

| | cleaning up | 0.000031 | |

+-+ +

15 rows in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 38: > select id,name from t where 0room1

Empty set (0.00 sec)

Root@localhost [querydb]: 41: > show profiles

+-+

+-+

| | 1 | 0.00034325 | select id,name from t |

| | 2 | 0.00006925 | selectt id,name from t |

| | 3 | 0.00018800 | select id,name fr0m t |

| | 4 | 0.00096275 | select ida,name from t |

| | 5 | 0.00117675 | select id,name from tab |

| | 6 | 0.00115800 | select id,name,abc from t |

| | 7 | 0.00029450 | select 1 |

| | 8 | 0.00074025 | select id,name from t |

| | 9 | 0.00058500 | select id,name from t where 0room1 |

+-+

9 rows in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 41: > show profile for query 9

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000279 | |

| | checking permissions | 0.000015 | |

| | Opening tables | 0.000046 | |

| | init | 0.000057 | |

| | System lock | 0.000019 | |

| | optimizing | 0.000025 | |

| | executing | 0.000014 | |

| | end | 0.000005 | |

| | query end | 0.000008 | |

| | closing tables | 0.000010 | |

| | freeing items | 0.000020 | |

| | cleaning up | 0.000089 | |

+-+ +

12 rows in set, 1 warning (0.00 sec)

For ease of observation, I look at the execution plan of the statement here and then look at the statement that is executed.

The output below is not good-looking, you can see the screenshot below.

Root@localhost [querydb]: 10: > explain extended select id,name from t where 0room1

+-- +

+-- +

+-- +

1 row in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 11: > show warnings

+-+

+-+

+-+

1 row in set (0.00 sec)

It is found through the execution of three sql statements.

Optimizing stage: for algebraic optimization stage

Statistics and preparing phase: physical optimization phase. Literally, it is to collect statistics, generate execution plan, and select the optimal access path for execution.

At the same time, have you found Sending data, which means that you have gone to the storage engine layer to pull data? it is easier to understand that there is no Sending data in select 1 and where 0,1 above, but there is a problem here? The MySQL layer and the storage engine layer are implemented through interfaces, so how do you know which thread to return when the results of the query are returned? In fact, internally, each query will be assigned a query thread ID, according to the thread ID number.

I. query execution

Root@localhost [querydb]: 41: > select id,name from t

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

+-+ +

3 rows in set (0.00 sec)

Root@localhost [querydb]: 15: > show profiles

+-+

+-+

| | 1 | 0.00034325 | select id,name from t |

| | 2 | 0.00006925 | selectt id,name from t |

| | 3 | 0.00018800 | select id,name fr0m t |

| | 4 | 0.00096275 | select ida,name from t |

| | 5 | 0.00117675 | select id,name from tab |

| | 6 | 0.00115800 | select id,name,abc from t |

| | 7 | 0.00029450 | select 1 |

| | 8 | 0.00074025 | select id,name from t |

| | 9 | 0.00058500 | select id,name from t where 0room1 |

| | 10 | 0.00194675 | select id,name from t |

+-+

10 rows in set, 1 warning (0.00 sec)

Root@localhost [querydb]: 15: > show profile for query 10

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000763 | |

| | checking permissions | 0.000050 | |

| | Opening tables | 0.000184 | |

| | init | 0.000178 | |

| | System lock | 0.000076 | |

| | optimizing | 0.000025 | |

| | statistics | 0.000095 | |

| | preparing | 0.000055 | |

| | executing | 0.000002 | |

| | Sending data | 0.000335 | |

| | end | 0.000018 | |

| | query end | 0.000028 | |

| | closing tables | 0.000023 | |

| | freeing items | 0.000041 | |

| | cleaning up | 0.000076 | |

+-+ +

15 rows in set, 1 warning (0.00 sec)

Executing: it's the execution phase.

There is also a System lock phase: in fact, when performing DDL and DML operations, MySQL will lock the metadata of the table internally and other locks, such as S lock, X lock, IX lock, IS lock, etc., to solve or ensure the consistency between DDL operation and DML operation. [you can see or transaction processing and implementation]

Reference books:

[compilation principle] [US] Alfred V.Aho, [US] Monica S.Lam, [US] Ravi Sethi et al.; Zhao Jianhua, Zheng Tao et al.

[introduction to Database system] Wang Shan, sa Shixuan

[art of Database query Optimizer: principle Analysis and SQL performance Optimization] by Li Haixiang

[inside InnoDB Storage engine Technology] by Jiang Chengyao

[transaction processing and implementation]

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.