MYSQL sql执行过程的一些追踪分析
发布时间:2022-03-30 23:09:10 所属栏目:MySql教程 来源:互联网
导读:与oracle或其他的数据库都差不多,一条sql的执行主要还是要经历了解析、优化、执行这几个过程,稍微具体下总结,MYSQL的主要过程如下: 客户端发起连接-----连接器,主要分配线程,验证权限----分析器,对sql语句语法进行分析-----优化器,生成准确的执行
与oracle或其他的数据库都差不多,一条sql的执行主要还是要经历了解析、优化、执行这几个过程,稍微具体下总结,MYSQL的主要过程如下: 客户端发起连接-----连接器,主要分配线程,验证权限----分析器,对sql语句语法进行分析-----优化器,生成准确的执行计划-----执行器,执行语句,发起读写数据,返回结果--之后对数据读写是io线程与存储引擎的交互 在客户端连接部分,涉及到TCP三次握手过程,我已在《MYSQL 连接登录过程分析》中尝试进行分析。 http://blog.itpub.net/29863023/viewspace-2216731/ 尝试用strace追踪mysqld进程,观察发起一个连接去执行sql时的情况: [root@cwdtest1 ~]# strace -f -F -ff -o mysqld-strace -s 1024 -p 62509 strace: Process 62509 attached with 32 threads .... strace: Process 33059 attached [root@cwdtest1 /]# mysql -uroot -pcwdroot mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 24 Server version: 5.7.23-23-log Source distribution Copyright (c) 2009-2018 Percona LLC and/or its affiliates Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> select * from cwdtest.test; +---------+----------+ | col1 | col2 | +---------+----------+ | | aaaaaaaa | | ccccccc | NULL | +---------+----------+ 2 rows in set (0.00 sec) mysql> exit Bye 从performance_schema.threads中可以看到新增的54号前台线程thread/sql/one_connection,其os 线程id是33059 *************************** 31. row *************************** THREAD_ID: 54 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 28 PROCESSLIST_USER: root PROCESSLIST_HOST: localhost PROCESSLIST_DB: NULL PROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 10 PROCESSLIST_STATE: NULL PROCESSLIST_INFO: NULL PARENT_THREAD_ID: 1 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID: 33059 《====== 31 rows in set (0.00 sec) 分析strace的过程信息: 获取线程id33059,之后设置 setsockopt状态。这里看到open /dev/urandom,这是获取一个随机编号 set_robust_list(0x7f9aa60ea9e0, 24) = 0 gettid() = 33059 setsockopt(67, SOL_TCP, TCP_NODELAY, [1], 4) = -1 EOPNOTSUPP (Operation not supported) setsockopt(67, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0 open("/dev/urandom", O_RDONLY) = 68 read(68, "'275|274277200Uw22053)M4E364C37221022223534533I216252206M336C;372", 32) = 32 close(68) = 0 之后是密码验证,以及一些版本消息等 sendto(67, "Q n5.7.23-23-log 34 {PR&1|0 377367!2 37720125 426:5.h34U"G%a mysql_native_password ", 85, MSG_DONTWAIT, NULL, 0) = 85 recvfrom(67, "272 1", 4, MSG_DONTWAIT, NULL, NULL) = 4 recvfrom(67, "2052463771 1! root 24v10A216"344i31'331UMa"35643640!331mysql_native_password i3_os5Linuxf_client_name10libmysql4_pid 053305817_client_versiont5.7.23-23t_platform6x86_64fprogram_name5mysql", 186, MSG_DONTWAIT, NULL, NULL) = 186 sendto(67, "7 2 2 ", 11, MSG_DONTWAIT, NULL, 0) = 11 recvfrom(67, "! ", 4, MSG_DONTWAIT, NULL, NULL) = 4 recvfrom(67, "3select @@version_comment limit 1", 33, MSG_DONTWAIT, NULL, NULL) = 33 sendto(67, "1 11' |