MySQL一条查询SQL的执行过程（源码分析）

说明：
以下所有说明都以 MySQL 5.7.25 源码为例 ，存储引擎为InnoDB。

最基本的一条查询语句：

mysql> select * from t1;

表 t1 的创建语句为:
create table t1(a int, b int) engine = InnoDB;

服务端处理流程

mysqld 服务进程为每一个客户端 mysql 分配了一个会话 connection 以处理来自客户端的请求，然后返回请求的结果，这是一个最简单最基本的交互过程，总的大致流程为：

MySQL 每个会话建立之后，接受命令的初始接口为handle_connection，其处理的伪码如下：

handle_connection
  Global_THD_manager *thd_manager= Global_THD_manager::get_instance();
  Connection_handler_manager *handler_manager = Connection_handler_manager::get_instance();    
  Channel_info* channel_info= static_cast<Channel_info*>(arg);
  
  if my_thread_init();then
      => mysql_mutex_lock(&THR_LOCK_threads);
      => tmp->id= ++thread_id;
      => ++THR_thread_count;
      => mysql_mutex_unlock(&THR_LOCK_threads);
  fi  
  while 1
  do
      THD *thd= init_new_thd(channel_info); //初始化的时候有一系列的id设置
      <a>
         Channel_info_local_socket::create_thd
         Channel_info::create_thd
           THD::THD
             THD::init
               plugin_thdvar_init//初始化thdvar变量
                   thd->variables= global_system_variables;//利用系统全局变量进行初始化
               if variables.sql_log_bin; THEN
                 variables.option_bits|= OPTION_BIN_LOG
               else
                 variables.option_bits&= ~OPTION_BIN_LOG
               fi
      </a>
      thd_manager->add_thd(thd);//add的时候需要相关的读取，DBUG_ASSERT(thd->thread_id() != reserved_thread_id); Thread_id_array thread_ids; manager里面有array记录
          => mysql_mutex_lock(&LOCK_thd_list);
          => std::pair<THD_array::iterator, bool> insert_result=
          => thd_list.insert_unique(thd);
          => if (insert_result.second);then
          =>   ++global_thd_count;
          => fi
          => mysql_mutex_unlock(&LOCK_thd_list);  
      if thd_prepare_connection(thd);//prepare中，server_mpvio_initialize，mpvio->thread_id= thd->thread_id()
      else
        while thd_connection_alive(thd)
        do
          if do_command(thd)
        done
      fi
      thd->get_stmt_da()->reset_diagnostics_area()
      thd->release_resources()
  done

handle_connection里，首先从全局的THD中获取一个可用的实例，THD为服务端为每一个客户端connection分配的一个独立的线程服务体。

在初始化THD时，会在plugin_thdvar_init中获取系统变量参数读进THD中(thd->variables= global_system_variables;//利用系统全局变量进行初始化)。初始化完THD后，准备工作就绪，然后进入do_command以处理每个接受到的客户端请求命令。

do_command是服务端会话执行每一条命令的总入口。

do_command
  const bool classic=
    (thd->get_protocol()->type() == Protocol::PROTOCOL_TEXT ||
     thd->get_protocol()->type() == Protocol::PROTOCOL_BINARY)
  //清空thd的error信息
  thd->clear_error()				// Clear error message
  thd->get_stmt_da()->reset_diagnostics_area()
  return_value= dispatch_command(thd, &com_data, command)

do_command调用dispatch_command将命令进行分发，

bool dispatch_command(THD *thd, const COM_DATA *com_data, enum enum_server_command command)
  switch (command)
  case COM_INIT_DB:
  case COM_REGISTER_SLAVE:
  case COM_RESET_CONNECTION:
  case COM_CHANGE_USER:
  case COM_STMT_EXECUTE:
  case COM_STMT_FETCH:
  case COM_STMT_SEND_LONG_DATA:
  case COM_STMT_PREPARE:
  case COM_STMT_CLOSE:
  case COM_STMT_RESET:
  case COM_QUERY:
    if alloc_query(thd, com_data->com_query.query, com_data->com_query.length) ; THEN
      break
    fi
    //记录原始SQL
    if (opt_general_log_raw); THEN
      query_logger.general_log_write(thd, command, thd->query().str, thd->query().length)
    fi
    //#if ENABLED_PROFILING
    thd->profiling.set_query_source(thd->query().str, thd->query().length)
    //#endif
    //解析SQL语句
    mysql_parse(thd, &parser_state)
    break
    
  case COM_FIELD_LIST:
  case COM_QUIT:
  case COM_BINLOG_DUMP_GTID:
  case COM_BINLOG_DUMP:
  case COM_REFRESH:
  case COM_SHUTDOWN:
  case COM_STATISTICS:
  case COM_PING:
  case COM_PROCESS_INFO:
  case COM_PROCESS_KILL:
    if thd_manager->get_thread_id() & (~0xfffffffful); THEN
    ELSE
      thd->status_var.com_stat[SQLCOM_KILL]++
      sql_kill(thd, com_data->com_kill.id, false)
    fi
    break
  case COM_SET_OPTION:
  case COM_DEBUG:
  case COM_SLEEP:
  case COM_CONNECT:			    // Impossible here
  case COM_TIME:			      // Impossible from client
  case COM_DELAYED_INSERT:  // INSERT DELAYED has been removed.
  case COM_END:
  default:
    my_message(ER_UNKNOWN_COM_ERROR, ER(ER_UNKNOWN_COM_ERROR), MYF(0))
    break

dispatch_command中将根据command的类型进行处理，可以看出，普通的QUEYR走的是case COM_QUERY；
在case COM_QUERY中，调用mysql_parse进行解析SQL；

//解析SQL语句
mysql_parse(thd, &parser_state)
  mysql_reset_thd_for_next_command(thd)
  if query_cache.send_result_to_client(thd, thd->query()) <= 0; THEN
    err= parse_sql(thd, parser_state, NULL)
    mysql_execute_command(thd, true)
  ELSE
    if !opt_general_log_raw; THEN
      query_logger.general_log_write(thd, COM_QUERY, thd->query().str, thd->query().length)
    fi
  fi

在mysql_parse中，调用parse_sql进行SQL词法语法的解析工作，然后调用mysql_execute_command进行执行；

MySQL是利用bison（类似yacc）来进行词法分析的，简单的说，就是调用yychar = yylex(&yylval, &yylloc, YYTHD)获取到SQL中的一个个token，然后根据事先的规则进行处理，可以简单看下parse_sql的几个步骤。在yyreduce里，根据每一个token进行判断，然后进入不同的分支进行初始化；

parse_sql(thd, parser_state, NULL)
  /* Parse the query. */
  bool mysql_parse_status= MYSQLparse(thd) != 0

#define yyparse         MYSQLparse
#define yylex           MYSQLlex
#define yyerror         MYSQLerror
#define yydebug         MYSQLdebug
#define yynerrs         MYSQLnerrs
int yyparse (class THD *YYTHD)
  int yychar /* The lookahead symbol.  */
  YYLTYPE yylloc = yyloc_default
  int yynerrs/* Number of syntax errors so far.  */
  int yystate
  /* Number of tokens to shift before error messages enabled.  */
  int yyerrstatus
  /* The stacks and their tools:
     'yyss': related to states.
     'yyvs': related to semantic values.
     'yyls': related to locations.

     Refer to the stacks through separate pointers, to allow yyoverflow
     to reallocate them elsewhere.  */

  /* The state stack.  */
  yytype_int16 yyssa[YYINITDEPTH];
  yytype_int16 *yyss;
  yytype_int16 *yyssp;

  /* The semantic value stack.  */
  YYSTYPE yyvsa[YYINITDEPTH];
  YYSTYPE *yyvs;
  YYSTYPE *yyvsp;

  /* The location stack.  */
  YYLTYPE yylsa[YYINITDEPTH];
  YYLTYPE *yyls;
  YYLTYPE *yylsp;

  /* The locations where the error started and ended.  */
  YYLTYPE yyerror_range[3];

  YYSIZE_T yystacksize;
  
  int yytoken = 0/* Lookahead token as an internal (translated) token number.  */
  YYSTYPE yyval
yynewstate:
  yyssp++
yysetstate:
  *yyssp = yystate
yybackup:
  if yychar == YYEMPTY; THEN
    YYDPRINTF((stderr, "Reading a token:"))
    yychar = yylex(&yylval, &yylloc, YYTHD)    
  fi
  if yychar <= YYEOF; THEN
    yychar = yytoken = YYEOF
    YYDPRINTF((stderr, "Now at end of input.\n"))
  else
    yytoken = YYTRANSLATE(yychar)
    YY_SYMBOL_PRINT("Next token is", yytoken, &yylval, &yylloc)
  fi
  
  yyn += yytoken
  if yyn < 0 || YYLAST < yyn || yycheck[yyn] != yytoken; THEN
    goto yydefault;
  fi
  yyn = yytable[yyn]
  if yyn <= 0; THEN
    yyn = -yyn;
    goto yyreduce;
  fi
  /* Discard the shifted token.  */
  yychar = YYEMPTY
  yystate = yyn
  YY_IGNORE_MAYBE_UNINITIALIZED_BEGIN
  *++yyvsp = yylval
  YY_IGNORE_MAYBE_UNINITIALIZED_END
  *++yylsp = yylloc
  goto yynewstate
yydefault:
yyreduce:
  switch yyn/* yyn is the number of a rule to reduce with.  */
  case 2:
    break;
  case 3:
    break;  
  case 5:
    break;  
  case 
    break;  
  goto yynewstate
yyerrlab:
  goto yyerrlab1
yyerrorlab:
  goto yyerrlab1
yyerrlab1:      //yyerrlab1 -- common code for both syntax error and YYERROR.
  goto yynewstate;
yyacceptlab:    //yyacceptlab -- YYACCEPT comes here.
  yyresult = 0
  goto yyreturn;
yyabortlab:     //yyabortlab -- YYABORT comes here.
  yyresult = 1
  goto yyreturn;
yyexhaustedlab: //yyexhaustedlab -- memory exhaustion comes here.
  yyerror(&yylloc, YYTHD, YY_("memory exhausted"))
  yyresult = 2
yyreturn:
  return yyresult;

解析完一条SQL之后，现在回到mysql_execute_command函数中，进行查询；

mysql_execute_command
  switch lex->sql_command:
  case SQLCOM_SHOW_DATABASES:
  case SQLCOM_SHOW_TABLES:
  case SQLCOM_SELECT:
    res= execute_sqlcom_select(thd, all_tables)
    >execute_sqlcom_select
      if !open_tables_for_query(thd, all_tables, 0); THEN
        if lex->is_explain(); THEN
          Query_result *const result= new Query_result_send
          res= handle_query(thd, lex, result, 0, 0)
        else
          res= handle_query(thd, lex, result, 0, 0)
        fi
      fi
    <execute_sqlcom_select

handle_query进入了处理每条SQL命令的流程，每条SELECT查询语句流程主要可以分为：

• prepare 阶段；
• optimize 阶段；
• execute 阶段；
• cleanup 阶段；

handle_query
  const bool single_query= unit->is_simple()
  //phase 1: prepare
  if single_query; THEN
    unit->set_limit(unit->global_parameters())
    select->context.resolve_in_select_list= true
    select->set_query_result(result)
    select->make_active_options(added_options, removed_options)
    select->fields_list= select->item_list
    if select->prepare(thd); THEN
    fi
    unit->set_prepared()
  ELSE
    if unit->prepare(thd, result, SELECT_NO_UNLOCK | added_options, removed_options); THEN
    fi
  fi
  if lock_tables(thd, lex->query_tables, lex->table_count, 0); THEN
  fi
  //register query result in cache
  query_cache.store_query(thd, lex->query_tables)
  
  //phase 2: optimize
  if single_query; THEN
    if select->optimize(thd); THEN
    fi
    unit->set_optimized()
  ELSE
    if select->optimize(thd); THEN
    fi
  fi
  
  //phase 3: execute
  if lex->is_explain(); THEN
    if explain_query(thd, unit); THEN
    fi
  ELSE
    if single_query; THEN
      select->join->exec()
      unit->set_executed()
      if thd->is_error() ; THEN
        goto err;
    else
      if (unit->execute(thd)); THEN
        goto err;
  fi
  res= unit->cleanup(false)