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

Similar to oracle or other databases, the execution of a sql mainly goes through the processes of parsing, optimization and execution. To sum up, the main process of MYSQL is as follows: client initiates connections-connectors, mainly assigns threads, verifies permissions-parsers, parses the syntax of sql statements-optimizers, generates accurate execution plans-executors Execute the statement, initiate the read and write data, and return the result-- then the data read and write is the interaction between the io thread and the storage engine in the client connection part, which involves the TCP three-way handshake process, which I have tried to analyze in the "MYSQL connection login process analysis". Http://blog.itpub.net/29863023/viewspace-2216731/ tries to trace the mysqld process with strace, observing what happens when a connection is initiated to execute sql: [root@cwdtest1 ~] # strace-f-ff-o mysqld-strace-s 1024-p 62509strace: Process 62509 attached with 32 threads....strace: Process 33059 attached [root@cwdtest1 /] # mysql-uroot-pcwdrootmysql: [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 24Server version: 5.7.23-23-log Source distributionCopyright (c) 2009-2018 Percona LLC and/or its affiliatesCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 > exitBye

From performance_schema.threads, you can see the newly added 54 foreground thread thread/sql/one_connection, whose os thread id is 33059.

* 31. Row * * THREAD_ID: 54 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 28 PROCESSLIST_USER: root PROCESSLIST_HOST: localhost PROCESSLIST_DB: NULLPROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 10 PROCESSLIST_STATE: NULLPROCESSLIST_ 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) analyze the process information of strace: get thread id33059 The setsockopt status is then set. See open / dev/urandom here, which is to get a random number set_robust_list (0x7f9aa60ea9e0, 24) = 0gettid () = 33059setsockopt (67, SOL_TCP, TCP_NODELAY, [1], 4) =-1 EOPNOTSUPP (Operation not supported) setsockopt (67, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0open ("/ dev/urandom", O_RDONLY) = 68read (68) "'\ 275 |\ 274\ 277\ 200Uw\ 2205\ 3) M\ 4e\ 364C\ 3722,2272\ 345\ 33I\ 2162\ 206m\ 336C \ 372 ", 32) = 32close (68) = 0

Followed by password verification, and some version messages, etc.

Sendto (67, "Q\ 0\ 0\ 0\ n5.7.23-23-log\ 0\ 34\ 0\ 0 {PR&1 | 0\ 0\ 377\ 367!\ 2\ 0\ 377\ 25\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0, 4, MSG_DONTWAIT = 85recvfrom (67,"\ 272\ 0\ 0\ 1 ", 4, MSG_DONTWAIT NULL, NULL) = 4recvfrom (67 "\ 205\ 246\ 377\ 1\ 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\ 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,186, MSG_DONTWAIT, NULL, NULL) = 186sendto (67, "\ 7\ 0\ 0\ 2\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0", 11, MSG_DONTWAIT, NULL, 0) = 11recvfrom (67, "!\ 0\ 0\ 0\ 0", 4, MSG_DONTWAIT, NULL, NULL) = 4recvfrom (67, "\ 3select @ @ version_comment limit 1", 33, MSG_DONTWAIT, NULL, NULL) = 33sendto (67) "\ 1\ 0\ 0\ 1\ 1'\ 0\ 2\ 3def\ 0\ 0\ 0\ 21@@version_comment\ 0\ f!\ 0009\ 0\ 0\ 0\ 375\ 0\ 0 37\ 0\ 24\ 0\ 3\ 23Source distribution\ 7\ 0\ 4\ 376\ 0\ 0\ 2\ 0\ 0", 83, MSG_DONTWAIT, NULL, 0) = 83recvfrom (67, 0x7f9a9000a730, 4, MSG_DONTWAIT, NULL NULL) =-1 EAGAIN (Resource temporarily unavailable) poll ([{fd=67, events=POLLIN | POLLPRI}], 1, 28800000) = 1 ([{fd=67, revents=POLLIN}])

After receiving the query statement of shelect, we can see the file stat. / cwdtest/test.frm. Here, we get the file information, visit. / cwdtest/test.TRG, and find the prompt No such file or directory.

Recvfrom (67, "\ 33\ 0\ 0\ 0", 4, MSG_DONTWAIT, NULL, NULL) = 4recvfrom (67, "\ 3select * from cwdtest.test", 27, MSG_DONTWAIT, NULL, NULL) = 27stat (". / cwdtest/test.frm", {st_mode=S_IFREG | 0640, st_size=8590,...}) = 0access (". / cwdtest/test.TRG", F_OK) =-1 ENOENT (No such file or directory) sendto (67) "\ 1\ 0\ 0\ 1\ 2 -\ 0\ 2\ 3def\ 7cwdtest\ 4test\ 4test\ 4col1\ 4col1\ f!\ 0\ 36\ 0\ 0\ 0\ 375\ 1\ 20\ 0\ 0\ 0\ 0\ 3\ 3def\ 7cwdtest\ 4test\ 4col2\ 4col2\ f!\ 0\ 36\ 0\ 0\ 0\ 0\ n\ 0\ 0\ 0\ 0\ n\ 0\ 0\ 0\ 4\ 0\ 10aaaaaaaa\ t\ 0\ 0\ 5 MSG_DONTWAIT, NULL, 0) = 141recvfrom (67, 0x7f9a9000a730, 4, MSG_DONTWAIT, NULL, NULL) =-1 EAGAIN (Resource temporarily unavailable) 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

Frm is the table structure definition file of MySQL You can view the hexadecimal data [root@cwdtest1 cwdtest] # hexdump-C-v test.frm00002130 20 20 20. | 00002150 04 00 05 63 6f 6c 31 00 05 05 63 6f 6c 32 00 |. Col1....col2. | 00002160 04 01e 1e 00020000 00 400 0000 00 0f 21 00 |. @.... | 00002170 00 05 1e 0021 0000 00 80 0000 0000f 21 |.! | 00002180 0000 ff 63 6f 6c 31 ff 63 6f 6c 32 ff 00 |... col1.col2.. | the information listed above can be obtained: 04 05 1e 1e 0002 0000 00 00 00 0000 0000 0f 21 0000-- Field col1 05 05 1e 1e 00 21 00 00 00 80 00 00 00 0F 21 00 00-Field col2 take the col1 field as an example: where 04 represents the column serial number (the initial column serial number is 4) 1e represents the field length, 1e is converted to decimal is 30, the word in the table is varchar (10), and the character set is utf8 / 3bit, so the length is 10'3 '30.40 means it cannot be empty. (DEFAULT NULL 80 not NULL 40 default 'VALUE' 00) 0f indicates that the field type is varhcar 21 means that the character set is the utf8 table creation statement: | test | CREATE TABLE `test` (`col1` varchar (10) NOT NULL, `col2` varchar (10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 | TRG file is the definition of record trigger in mysql. It is obvious that the test table does not create a trigger.

Sendto (67 "\ 1\ 0\ 0\ 1\ 2 -\ 0\ 2\ 3def\ 7cwdtest\ 4test\ 4test\ 4col1\ 4col1\ f!\ 0\ 36\ 0\ 0\ 0\ 375\ 1\ 20\ 0\ 0\ 0\ 0\ 3\ 3def\ 7cwdtest\ 4test\ 4col2\ 4col2\ f!\ 0\ 36\ 0\ 0\ 0\ 0\ n\ 0\ 0\ 0\ 0\ n\ 0\ 0\ 0\ 4\ 0\ 10aaaaaaaa\ t\ 0\ 0\ 5 \ 7ccccccc\ 373\ 7\ 0\ 0\ 6\ 376\ 0\ 0 "\ 0\ 0\ 0" The sendto function is called after MSG_DONTWAIT, NULL, 0) = 141 Send the result to the client. When you exit, it is closed. Shutdown (67, SHUT_RDWR) = 0 close (67) = 0 in the above trace log, the object operated by each function is basically 67. 67 is a file descriptor, and here it corresponds to socket. [root@cwdtest1 fd] # ls-rtl 672 lrwx- 1 root root 64 Jan 19 11:19 67-> socket: [16206507]

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.