MYSQL sql执行过程的一些跟踪分析(一)

2025-04-10 技术教程

与oracle或其他的数据库都差不多，一条sql的执行主要还是要经历了解析、优化、执行这几个过程，稍微具体下总结，MYSQL的主要过程如下：客户端发起连接-----连接器，主要分配线程，验证权限----分析器，对sql语句语法进行分析-----优化器，生成准确的执行计划-----执行器，执行语句，发起读写数据，返回结果--之后对数据读写是io线程与存储引擎的交互在客户端连接部分，涉及到TCP三次握手过程，我已在《MYSQL 连接登录过程分析》中尝试进行分析。http://blog.itpub.net/29863023/viewspace-2216731/尝试用strace追踪mysqld进程，观察发起一个连接去执行sql时的情况：

[root@cwdtest1~]#strace-f-F-ff-omysqld-strace-s1024-p62509strace:Process62509attachedwith32threads....strace:Process33059attached

[root@cwdtest1/]#mysql-uroot-pcwdrootmysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis24Serverversion:5.7.23-23-logSourcedistributionCopyright(c)2009-2018PerconaLLCand/oritsaffiliatesCopyright(c)2000,2018,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>select*fromcwdtest.test;+---------+----------+|col1|col2|+---------+----------+||aaaaaaaa||ccccccc|NULL|+---------+----------+2rowsinset(0.00sec)mysql>exitBye

从performance_schema.threads中可以看到新增的54号前台线程thread/sql/one_connection，其os 线程id是33059

***************************31.row***************************THREAD_ID:54NAME:thread/sql/one_connectionTYPE:FOREGROUNDPROCESSLIST_ID:28PROCESSLIST_USER:rootPROCESSLIST_HOST:localhostPROCESSLIST_DB:NULLPROCESSLIST_COMMAND:SleepPROCESSLIST_TIME:10PROCESSLIST_STATE:NULLPROCESSLIST_INFO:NULLPARENT_THREAD_ID:1ROLE:NULLINSTRUMENTED:YESHISTORY:YESCONNECTION_TYPE:SocketTHREAD_OS_ID:33059《======31rowsinset(0.00sec)分析strace的过程信息：获取线程id33059,之后设置setsockopt状态。这里看到open/dev/urandom,这是获取一个随机编号

set_robust_list(0x7f9aa60ea9e0,24)=0gettid()=33059setsockopt(67,SOL_TCP,TCP_NODELAY,[1],4)=-1EOPNOTSUPP(Operationnotsupported)setsockopt(67,SOL_SOCKET,SO_KEEPALIVE,[1],4)=0open("/dev/urandom",O_RDONLY)=68read(68,"'\275|\274\277\200Uw\2205\3)M\4E\364C\372\210\222\235\345\33I\216\252\206M\336C;\372",32)=32close(68)=0

之后是密码验证，以及一些版本消息等

sendto(67,"Q\0\0\0\n5.7.23-23-log\0\34\0\0\0{PR&1|0\0\377\367!\2\0\377\201\25\0\0\0\0\0\0\0\0\0\0\4\26:5.h\34U\"G%a\0mysql_native_password\0",85,MSG_DONTWAIT,NULL,0)=85recvfrom(67,"\272\0\0\1",4,MSG_DONTWAIT,NULL,NULL)=4recvfrom(67,"\205\246\377\1\0\0\0\1!\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0root\0\24\v\10A\216\"\344i\31'\331UMa\"\356\4\3640!\331mysql_native_password\0i\3_os\5Linux\f_client_name\10libmysql\4_pid\00533058\17_client_version\t5.7.23-23\t_platform\6x86_64\fprogram_name\5mysql",186,MSG_DONTWAIT,NULL,NULL)=186sendto(67,"\7\0\0\2\0\0\0\2\0\0\0",11,MSG_DONTWAIT,NULL,0)=11recvfrom(67,"!\0\0\0",4,MSG_DONTWAIT,NULL,NULL)=4recvfrom(67,"\3select@@version_commentlimit1",33,MSG_DONTWAIT,NULL,NULL)=33sendto(67,"\1\0\0\1\1'\0\0\2\3def\0\0\0\21@@version_comment\0\f!\0009\0\0\0\375\0\0\37\0\0\24\0\0\3\23Sourcedistribution\7\0\0\4\376\0\0\2\0\0\0",83,MSG_DONTWAIT,NULL,0)=83recvfrom(67,0x7f9a9000a730,4,MSG_DONTWAIT,NULL,NULL)=-1EAGAIN(Resourcetemporarilyunavailable)poll([{fd=67,events=POLLIN|POLLPRI}],1,28800000)=1([{fd=67,revents=POLLIN}])

接受到shelect 的查询语句，我们可以看到会stat ./cwdtest/test.frm 这个文件，这里是获取文件信息，并在去访问./cwdtest/test.TRG，发现提示No such file or directory。

recvfrom(67,"\33\0\0\0",4,MSG_DONTWAIT,NULL,NULL)=4recvfrom(67,"\3select*fromcwdtest.test",27,MSG_DONTWAIT,NULL,NULL)=27stat("./cwdtest/test.frm",{st_mode=S_IFREG|0640,st_size=8590,...})=0access("./cwdtest/test.TRG",F_OK)=-1ENOENT(Nosuchfileordirectory)sendto(67,"\1\0\0\1\2-\0\0\2\3def\7cwdtest\4test\4test\4col1\4col1\f!\0\36\0\0\0\375\1\20\0\0\0-\0\0\3\3def\7cwdtest\4test\4test\4col2\4col2\f!\0\36\0\0\0\375\0\0\0\0\0\n\0\0\4\0\10aaaaaaaa\t\0\0\5\7ccccccc\373\7\0\0\6\376\0\0\"\0\0\0",141,MSG_DONTWAIT,NULL,0)=141recvfrom(67,0x7f9a9000a730,4,MSG_DONTWAIT,NULL,NULL)=-1EAGAIN(Resourcetemporarilyunavailable)poll([{fd=67,events=POLLIN|POLLPRI}],1,28800000)=1([{fd=67,revents=POLLIN|POLLHUP}])recvfrom(67,"\1\0\0\0",4,MSG_DONTWAIT,NULL,NULL)=4recvfrom(67,"\1",1,MSG_DONTWAIT,NULL,NULL)=1shutdown(67,SHUT_RDWR)=0close(67)=0futex(0x1dca184,FUTEX_WAIT_PRIVATE,46,NULL<detached...>(END)来看看./cwdtest/test.frm 和./cwdtest/test.TRG两个文件：
frm是MySQL的表结构定义文件，通过hexdump可以查看其中16进制数据

[root@cwdtest1cwdtest]#hexdump-C-vtest.frm

0000213020202020202020202020202020202020||0000214020202020202020202020202020202000|.|00002150040005636f6c3100050005636f6c3200|...col1....col2.|0000216004051e1e0002000000400000000f2100|.........@....!.|000021700005051e1e0021000000800000000f21|......!........!|000021800000ff636f6c31ff636f6c32ff00|...col1.col2..|以上列信息整理可得：04 05 1e 1e 00 02 00 00 00 40 00 00 00 0f 21 00 00 --- 字段col105 05 1e 1e 00 21 00 00 00 80 00 00 00 0f 21 00 00-----字段col2以col1字段为例：其中04代表列序号（初始列序号为4），1e 表示字段长度，1e转化成十进制是30，表中是字是varchar(10)，字符集是utf8占3bit,所以长度是10*3=30.

40表示不可为空，(DEFAULTNULL80,NOTNULL40,DEFAULT'VALUE'00)

0f表示字段类型是varhcar21表示字符集是utf8

表创建语句：

|test|CREATETABLE`test`(`col1`varchar(10)NOTNULL,`col2`varchar(10)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8|TRG文件是mysql中记录触发器的定义，很明显这里test表并没有创建触发器。
sendto(67, "\1\0\0\1\2-\0\0\2\3def\7cwdtest\4test\4test\4col1\4col1\f!\0\36\0\0\0\375\1\20\0\0\0-\0\0\3\3def\7cwdtest\4test\4test\4col2\4col2\f!\0\36\0\0\0\375\0\0\0\0\0\n\0\0\4\0\10aaaaaaaa\t\0\0\5\7ccccccc\373\7\0\0\6\376\0\0\"\0\0\0", 141, MSG_DONTWAIT, NULL, 0) = 141之后便是调用sendto函数，往客户端发送结果。当退出时便是关闭。shutdown(67, SHUT_RDWR) = 0close(67) = 0在以上的trace日志里每个函数操作的对象基本都是67，67则是文件描述符，而这里对应的是socket。[root@cwdtest1 fd]# ls -rtl 672lrwx------ 1 root root 64 Jan 19 11:19 67 -> socket:[16206507]