Mysql 一条语句的执行过程

执行步骤

1.客户端连接到服务器，发送一条查询

2.服务器检查是否有开启查询缓存。如果有开启则判断是否命中，如果有命中则进入下一步
如果没有开启，则进入解析器

3.解析器做词法分析，比如是什么语句、之后再做语法分析。

4.优化器选择执行计划

5.调用存储引擎的API去执行器执行，

6.结果返回给客户端

具体实现

连接服务端，发送一条SQL

mysql 支持3种连接方式：

socket
named pipe
shared
memory named pipe 和 shared memory 只能在本地连接数据库，适用场景较少，

 (gdb) attach 17993
Attaching to process 17993
Reading symbols from /root/mysql-5.7.25/bin/mysqld...done.
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.
[New LWP 18113]
[New LWP 18112]
[New LWP 18078]
[New LWP 18028]
[New LWP 18027]
[New LWP 18026]
[New LWP 18024]
[New LWP 18023]
[New LWP 18022]
[New LWP 18021]
[New LWP 18020]
[New LWP 18019]
[New LWP 18018]
[New LWP 18017]
[New LWP 18016]
[New LWP 18015]
[New LWP 18014]
[New LWP 18013]
[New LWP 18010]
[New LWP 18009]
[New LWP 18008]
[New LWP 18007]
[New LWP 18006]
[New LWP 18005]
[New LWP 18004]
[New LWP 18003]
[New LWP 18002]
[New LWP 18001]
[New LWP 18000]
[New LWP 17994]
[Thread debugging using libthread_db enabled]
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libcrypt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libcrypt.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/librt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/librt.so.1
Reading symbols from /usr/lib64/libstdc++.so.6...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libstdc++.so.6
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libgcc_s.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libgcc_s.so.1
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /lib64/libfreebl3.so...(no debugging symbols found)...done.
Loaded symbols for /lib64/libfreebl3.so
Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnss_files.so.2
Reading symbols from /lib64/libnss_dns.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnss_dns.so.2
Reading symbols from /lib64/libresolv.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libresolv.so.2
0x00007f4e06ae9383 in poll () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install glibc-2.12-1.209.el6.x86_64 libgcc-4.4.7-18.el6.x86_64 libstdc++-4.4.7-18.el6.x86_64 nss-softokn-freebl-3.14.3-23.3.el6_8.x86_64

mysql 5.6中连接入口函数是 handle_connections_sockets，实现不太一样

mysql 5.7 入口 network_init

 (gdb) n
Single stepping until exit from function poll,
which has no line number information.
Mysqld_socket_listener::listen_for_connection_event (this=0x3b9b280) at /root/mysql-5.7.25/sql/conn_handler/socket_connection.cc:859
859	  if (retval < 0 && socket_errno != SOCKET_EINTR)

如果监听到连接返回 channel_info:
监听socket文件，没有新连接时，线程在这里等待
Mysqld_socket_listener::listen_for_connection_event

(gdb) p channel_info
$4 = (Channel_info *) 0x2981e200

进入Connection_handler_manager::process_new_connection

Connection_handler_manager::process_new_connection (this=0x3b3dac0, channel_info=0x2981e200) at /root/mysql-5.7.25/sql/conn_handler/connection_handler_manager.cc:261
261	  if (abort_loop || !check_and_incr_conn_count())

判断是否超过max_connections,里面每次去判断都会加1
if (abort_loop || !check_and_incr_conn_count())
{
channel_info->send_error_and_close_channel(ER_CON_COUNT_ERROR, 0, true);
delete channel_info;
return;
}

/* Connection_handler 有两个子类，
Per_thread_connection_handler：一个连接一个线程
One_thread_connection_handler：一个线程处理所有连接
一般都使用 Per_thread_connection_handler */

执行到
Per_thread_connection_handler::add_connection (this=0x3a6f7a0,

channel_info=0x2981e200) at /root/mysql-5.7.25/sql/conn_handler/connection_handler_per_thread.cc:394
268	  if (m_connection_handler->add_connection(channel_info))
(gdb) p !check_and_incr_conn_count()

add_connection 这里是真正的创建连接，上面都是判断连接数等。

查看 thread_cache 中是否有空闲thread，如有，使用cached thread

(gdb) p !check_idle_thread_and_enqueue_connection(channel_info)
$5 = true
(gdb) n
409	  channel_info->set_prior_thr_create_utime();
(gdb) n
413	                             (void*) channel_info);
(gdb) s
inline_mysql_thread_create (key=6, thread=0x7ffcccf7a550, attr=0x2dad080, 
    start_routine=0x16ca0b6 <handle_connection(void*)>, arg=0x29535590)
    at /root/mysql-5.7.25/include/mysql/psi/mysql_thread.h:1297
1297	  result= PSI_THREAD_CALL(spawn_thread)(key, thread, attr, start_routine, arg);
进入到inline_mysql_thread_create

用户进程

mysql_thread_create

这上面是监听连接的一个过程。
如果关闭连接后再重用发现在

402	  if (!check_idle_thread_and_enqueue_connection(channel_info))
(gdb) n
403	    DBUG_RETURN(false);
(gdb) n
433	}
结果是：
(gdb) p !check_idle_thread_and_enqueue_connection(channel_info)
$1 = true

它不需要去创建新的线程

用户连接进来：

handle_connection

handle_connection (arg=0x29be34e0) at /root/mysql-5.7.25/sql/conn_handler/connection_handler_per_thread.cc:263
263 THD *thd= init_new_thd(channel_info);

第一次循环执行prepare，后面跳过

300	    if (thd_prepare_connection(thd))
(gdb) s
thd_prepare_connection (thd=0x7fda8c000b70) at /root/mysql-5.7.25/sql/sql_connect.cc:891
初始化lex词法分析器
891	  lex_start(thd); 
进行连接的身份验证
892	  rc= login_connection(thd);

进入login_connection
login_connection (thd=0x7fda8c000b70) at /root/mysql-5.7.25/sql/sql_connect.cc:732
732 DBUG_ENTER(“login_connection”);
(gdb) n
733 DBUG_PRINT(“info”, (“login_connection called by thread %u”,
(gdb) n
737 thd->get_protocol_classic()->set_read_timeout(connect_timeout);
(gdb) n
738 thd->get_protocol_classic()->set_write_timeout(connect_timeout);
(gdb) n
740 error= check_connection(thd);

回到 handle_connection

handle_connection (arg=0x29be34e0) at /root/mysql-5.7.25/sql/conn_handler/connection_handler_per_thread.cc:304
304	      while (thd_connection_alive(thd))
(gdb) n
306	        if (do_command(thd))
(gdb) n
304	      while (thd_connection_alive(thd))
(gdb) n
306	        if (do_command(thd))
(gdb) n
304	      while (thd_connection_alive(thd))
(gdb) n
306	        if (do_command(thd))

发现
while (thd_connection_alive(thd))
{
if (do_command(thd))
break;
}

这一层的循环用来处理读取请求，每当处理完一条命令，就会循环一次。

执行到do_command，
这边如果没有操作就会在这里等待。

(gdb) s
do_command (thd=0x7fda90000b70) at /root/mysql-5.7.25/sql/sql_parse.cc:901
901	     thd->get_protocol()->type() == Protocol::PROTOCOL_BINARY);
(gdb) n
900	    (thd->get_protocol()->type() == Protocol::PROTOCOL_TEXT ||
(gdb) n
901	     thd->get_protocol()->type() == Protocol::PROTOCOL_BINARY);
(gdb) n
903	  NET *net= NULL;
(gdb) n
906	  DBUG_ENTER("do_command");
(gdb) n
912	  thd->lex->set_current_select(0);
(gdb) n
920	  thd->clear_error();				// Clear error message
(gdb) n
921	  thd->get_stmt_da()->reset_diagnostics_area();
(gdb) n
923	  if (classic)
(gdb) p classic

if (classic)
{

This thread will do a blocking read from the client which
will be interrupted when the next command is received from
the client, the connection is closed or “net_wait_timeout”
number of seconds has passed.

net= thd->get_protocol_classic()->get_net();
my_net_set_read_timeout(net, thd->variables.net_wait_timeout);
net_new_transaction(net);
}

932	    my_net_set_read_timeout(net, thd->variables.net_wait_timeout);
(gdb) p thd->variables.net_wait_timeout
$8 = 28800

这里可以看到对应的等待超时的参数设置。

到dispatch_command是真正的执行sql.

1022	  if (classic)
(gdb) n
1023	    my_net_set_read_timeout(net, thd->variables.net_read_timeout);
(gdb) n
1025	  return_value= dispatch_command(thd, &com_data, command);
(gdb) p return_value
$11 = false
(gdb) p command
$12 = COM_QUERY

在继续又回到
handle_connection (arg=0x29be34e0) at /root/mysql-5.7.25/sql/conn_handler/connection_handler_per_thread.cc:304
304 while (thd_connection_alive(thd))
等待操作

这是mysql 接受处理的一步

具体的sql 处理

进入到dispatch_commond

里面是一个大的switch
(gdb) n
1337 switch (command) {
(gdb) p command
$4 = COM_QUERY

获得网络中真正的执行sql

1460	    if (alloc_query(thd, com_data->com_query.query,
(gdb) p com_data->com_query.query
$6 = 0x7fb22800a4b1 "SHOW FULL TABLES FROM `debug` WHERE Table_type = 'VIEW'"

前面几次执行的mysql 本身的查询，比如 show database 等

1470 if (opt_general_log_raw)
(gdb) p opt_general_log_raw
$8 = false
是有没有打开general_log,如果是，就要写
query_logger.general_log_write(thd, command, thd->query().str,
thd->query().length);

进行sql解析：

1484	    mysql_parse(thd, &parser_state);

//初始化词法和语法的分析器

5439	  mysql_reset_thd_for_next_command(thd);
(gdb) p thd->query().str
$12 = 0x7f3db0005890 "SHOW FULL TABLES WHERE Table_type != 'VIEW'"
5440	  lex_start(thd);

判断是否命中查询缓存

if (query_cache.send_result_to_client(thd, thd->query()) <= 0)

解析SQL语句

5457	      err= parse_sql(thd, parser_state, NULL);

这边我理解的是语法和词法分析

执行sql
error= mysql_execute_command(thd, true);
这一步我的理解是包含了优化和执行

首先是解析后的语法结构

接着后面是一些判断，没仔细看，很复杂

后面是一个大的switch

2761	  switch (lex->sql_command) {
(gdb) p lex->sql_command
$29 = SQLCOM_SHOW_TABLES

里面有对应的

case SQLCOM_REPLACE:
case SQLCOM_INSERT:
case SQLCOM_REPLACE_SELECT:
case SQLCOM_INSERT_SELECT:
{
DBUG_ASSERT(first_table == all_tables && first_table != 0);
DBUG_ASSERT(lex->m_sql_cmd != NULL);
res= lex->m_sql_cmd->execute(thd);
break;
}
case SQLCOM_DELETE:
case SQLCOM_DELETE_MULTI:
case SQLCOM_UPDATE:

因为测试的一个select语句这边走到

case SQLCOM_SELECT:
{
DBUG_EXECUTE_IF("use_attachable_trx",
thd->begin_attachable_ro_transaction(););
thd->clear_current_query_costs();
res= select_precheck(thd, lex, all_tables, first_table);  对权限等会进行预检
if (!res)
res= (thd, all_tables);
thd->save_current_query_costs();
DBUG_EXECUTE_IF("use_attachable_trx",
thd->end_attachable_transaction(););
break;
}

执行select
res= execute_sqlcom_select(thd, all_tables);

权限的检查会在执行阶段去判断，如果没有权限会返回权限的错误。
对于比如字段不存在的检查也是在这一步里面

补充sql 解析的调用路径
Breakpoint 1, dispatch_command (thd=0x7efac40124f0, com_data=0x7efb3a9e2d80, command=COM_QUERY)
at /root/mysql-5.7.25/sql/sql_parse.cc:1460

mysql_parse (thd=0x7efac40124f0, parser_state=0x7efb3a9e2610) at /root/mysql-5.7.25/sql/sql_parse.cc:5458

mysql_execute_command (thd=0x7efac40124f0, first_level=true) at /root/mysql-5.7.25/sql/sql_parse.cc:2445

execute_sqlcom_select (thd=0x7efac40124f0, all_tables=0x7efac4016bb0)
at /root/mysql-5.7.25/sql/sql_parse.cc:5099

st_select_lex::prepare (this=0x7efac4015cc0, thd=0x7efac40124f0) at /root/mysql-5.7.25/sql/sql_resolver.cc:97
97 DBUG_ENTER(“SELECT_LEX::prepare”);