The execution process of a query statement in MySQL server

Programmer's road to freedom 2020-11-08 15:46:12
execution process query statement mysql


Reprinted from : Geek time

MySQL Server side structure model

Now I'm going to give you MySQL Basic architecture diagram , You can see clearly from it SQL Statements in MySQL The execution process in each function module of .

In general ,MySQL Can be divided into Server Layer and storage engine layer .

Server Layers include connectors 、 The query cache 、 analyzer 、 Optimizer 、 Actuators etc. , cover MySQL Most of the core service functions of , And all the built-in functions ( Such as date 、 Time 、 Mathematics and cryptographic functions ), All cross-storage engine functionality is implemented in this layer , Like stored procedures 、 trigger 、 View etc. .

The storage engine layer is responsible for data storage and extraction . Its architecture pattern is plug-in , Support InnoDB、MyISAM、Memory Wait for multiple storage engines . Now the most commonly used storage engine is InnoDB, It is from MySQL 5.5.5 Version began to be the default storage engine .

in other words , You execute create table When building the watch , If the engine type is not specified , The default is InnoDB. however , You can also choose another engine by specifying the type of storage engine , For example create table Use in statement engine=memory, To specify the use of the memory engine to create tables . Different storage engines have different access to table data , The supported functionality also varies , In a later article , We'll talk about the choice of engines .

It's not hard to see from the picture , Different storage engines share one Server layer , From the connector to the actuator . You can first have an impression of the name of each component , I'll combine that with the one mentioned at the beginning SQL sentence , Take you through the whole execution process , Look at the function of each component in turn .

Connector as

First step , You will connect to this database first , It's the connector that receives you . The connector is responsible for establishing a connection with the client 、 Access permissions 、 Maintaining and managing connections . The connection command is usually written like this :


mysql -h$ip -P$port -u$user -p

After the command is lost , You need to enter the password in the interactive dialogue . Although the password can also be directly followed by -p It's on the command line , But this may cause your password to leak . If you're connected to a production server , It is strongly recommended that you do not do this .

Connect... In the command mysql It's a client tool , Used to establish a connection with the server . In the completion of the classic TCP After shaking hands , The connector is about to start authenticating your identity , This is the user name and password you entered .

  • If the user name or password is wrong , You'll get one "Access denied for user" Error of , Then the client program ends execution .
  • If the user name and password are authenticated , The connector will go to the permission table to find out the permissions you have . after , The permission judgment logic in this connection , Will depend on the permissions read at this time .

That means , After a user successfully establishes a connection , Even if you use the administrator account to modify the permissions of this user , It does not affect the permissions of existing connections . After the modification is completed , Only new connections will use the new permission settings .

When the connection is complete , If you don't follow up , This connection is idle , You can show processlist See it in the command . The picture in the text is show processlist Result , Among them Command The column is shown as “Sleep” This line , It means that there is an idle connection in the system .

If the client does not move for a long time , The connector will automatically disconnect it . This time is determined by the parameter wait_timeout The control of the , The default value is 8 Hours .

If after the connection is broken , The client sends the request again , You'll get an error alert : Lost connection to MySQL server during query. At this time, if you want to continue , You have to reconnect , Then the request is executed .

In the database , Long connection refers to the successful connection , If the client continues to have requests , Always use the same connection . Short join refers to the disconnection after a few queries are executed , Create another query the next time . The process of establishing a connection is usually more complicated , So I suggest that you try to minimize the action of establishing connection in use , That is, try to use long connections .

But after using all the long connections , You might notice , Sometimes MySQL Memory usage is rising very fast , This is because MySQL Memory temporarily used during execution is managed in connection objects . These resources will be released when the connection is broken . So if long connections accumulate , May cause too much memory , Killed by the system (OOM), From a phenomenological point of view MySQL Abnormal restart .

How to solve this problem ? You can consider the following two options .

  1. Regularly disconnect long connections . Use it for a while , Or it can be judged in the program that a large memory consuming query has been executed , disconnect , Then query and reconnect .

  2. If you're using a MySQL 5.7 Or later , You can do this after each large operation , Through execution mysql_reset_connection To reinitialize the connection resources . This process does not require reconnection and re-authorization , But it restores the connection to the state it was in when it was created .

Cache components

After the connection is established , You can do it select Statement . The execution logic will come to the second step : The query cache .

MySQL After getting a query request , I will go to query cache first , Have you executed this statement before . Previously executed statements and their results may be key-value On the form of , Is directly cached in memory .key Is the statement of the query ,value Is the result of a query .

If your query can be found directly in this cache key, So this value Will be returned directly to the client . If the statement is not in the query cache , I'm going to continue the execution phase . After execution , The execution result will be stored in the query cache . You can see , If the query hits the cache ,MySQL There is no need to perform the following complex operations , I can just return the result , This will be very efficient .

But for the most part I would advise you not to use the query cache , Why? ? Because query caching often does more harm than good .

Query cache failures are very frequent , As long as there is an update to a table , All query caches on this table will be cleared . So it's likely that you're struggling to save the results , It's not in use yet , It's all cleared by an update . For databases that are under pressure to update , The hit rate for the query cache will be very low . Unless your business is a static table , It takes a long time to update . such as , A system configuration table , Then the query on this table is suitable for the query cache .

Fortunately MySQL This is also provided “ According to the need to use ” The way . You can set the parameters query_cache_type Set to DEMAND, So for the default SQL Statements do not use the query cache . For the statements that you decide to use the query cache , It can be used SQL_CACHE Explicitly specify , Like the following statement :


mysql> select SQL_CACHE * from T where ID=10;

It should be noted that ,MySQL 8.0 Version of the query cache directly removed the entire block , in other words 8.0 It's starting to disappear completely .

Analyzer component

If the query cache is not hit , It's about time to actually execute the statement . First ,MySQL Need to know what you're going to do , So you need to SQL Statement parsing .

The analyzer will do “ Lexical analysis ”. What you enter is a string with multiple Spaces SQL sentence ,MySQL You need to identify the strings in it , What is the .

MySQL From what you typed "select" This keyword recognizes , This is a query statement . It also takes strings “T” Identify a “ Table name T”, Put the string “ID” Identify a “ Column ID”.

After these identifications , Just do it “ Syntax analysis ”. According to the result of lexical analysis , The parser will follow the grammar rules , Judge the one you typed SQL Does the statement satisfy MySQL grammar .

If your statement is wrong , Will receive “You have an error in your SQL syntax” Error warning , Take the following statement select Less initial letters “s”.


mysql> elect * from t where ID=1;
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 'elect * from t where ID=1' at line 1

A general syntax error will indicate the first place where the error occurred , So what you want to focus on is immediately “use near” The content of .

Optimizer components

Through the analyser ,MySQL You know what you're gonna do . Before we start executing , It's also handled by the optimizer .

The optimizer is when there are multiple indexes in a table , Decide which index to use ; Or there are multiple table associations in a statement (join) When , Determine the join order of the tables . Let's say you execute the following statement , This statement executes two tables join:


mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
  • You can start from the table t1 The inside out c=10 The record of ID value , According to ID Values are associated to tables t2, To determine t2 Inside d Is the value of 20.
  • You can also start from the table t2 The inside out d=20 The record of ID value , According to ID The value associated with the t1, To determine t1 Inside c Is the value of 10.

The logical result of the two execution methods is the same , But the efficiency of the execution will be different , The role of the optimizer is to decide which scheme to use .

After the optimizer phase is complete , The execution of this statement is determined (explain see ), Then we go to the executor phase . If you have any questions , For example, how does the optimizer choose indexes , Is it possible to choose wrong etc , No problem , I'll expand the description of the optimizer separately in a later article .

Actuator assembly

MySQL I know what you are going to do through the analyzer , You know what to do with the optimizer , So it goes into the actuator stage , Start statement execution .

At the beginning of execution , Let's first judge what you do to this watch T Do you have permission to execute the query , without , An error with no permissions is returned , As shown below ( In engineering implementation , If you hit the query cache , When the query cache returns results , Do authority verification . Queries will also be called before the optimizer precheck Verify permissions ).


mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

If you have authority , Open the table and continue . When I open my watch , The actuator is defined according to the engine of the table , Use the interface provided by the engine .

For example, the table in our example T in ,ID Field has no index , So the execution process of the actuator is like this :

  • call InnoDB The engine interface takes the first row of this table , Judge ID Value is 10, If not, skip , If it is, the row will exist in the result set ;
  • Call the engine interface “ The next line ”, Repeat the same logic of judgment , Until you get to the last row of the table .
  • The executor returns the record set composed of all the rows that meet the conditions in the traversal process to the client as a result set .

thus , This statement is executed .

For tables with indexes , The execution logic is similar . The first time I call this “ Let's take the first row that satisfies our condition ” This interface , And then we loop “ That satisfies the next row ” This interface , These interfaces are all defined in the engine .

You will see a... In the slow query log of the database rows_examined Field of , Indicates how many rows were scanned during the execution of the statement . This value is accumulated each time the actuator calls the engine to get a row of data .

In some cases , The executor is called once , Inside the engine, it scans multiple lines , So the engine scans the number of rows followed rows_examined It's not exactly the same . We will talk about the internal mechanism of storage engine in a special article later , There will be detailed instructions .

版权声明
本文为[Programmer's road to freedom]所创,转载请带上原文链接,感谢

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云