MySQL连接的建立与使用

在 MYSQL的启动过程中，可以看到在 mysqld_main() 函数的最后调用了 mysqld_socket_acceptor->connection_event_loop() 函数用来处理MySQL的连接，这里通过源码分析一下MySQL连接的建立与使用过程：

1. 在死循环中调用 m_listener->listen_for_connection_event() 等待连接进入

2. 如果有连接进入，则调用 precess_new_connection() 处理连接

3. 调用 check_and_incr_conn_count() 检查是否有空余连接[当前连接数是否大于 max_connections], 如果没有空余连接, 结束处理流程；

      注意：这里允许 max_connections + 1 个连接，最后一个连接是为 super user保留的。

4. 如果存在空余连接，则对连接进行处理

5. 查看 thread cache 中是否有空闲 thread，如果有，使用 cached thread

6. 如果不存在，则创建一个新的线程来处理这个连接

7. 线程调用 handle_connection() 线程处理函数，初始化一个 thd 对象，并将其加入 thd list; 并初始化 lex 词法解析器，进行连接身份验证，初始化 thd，准备执行语句。

8. 调用 do_command(). 处理用户命令。

  /**
    Connection acceptor loop to accept connections from clients.
  */
  void connection_event_loop()
  {
    Connection_handler_manager *mgr= Connection_handler_manager::get_instance();
    while (!abort_loop)
    {
      // 等待连接进入
      Channel_info *channel_info= m_listener->listen_for_connection_event();
      if (channel_info != NULL)
        // 如果有连接进入，处理连接
        mgr->process_new_connection(channel_info);
    }
  }

　　

// 如果有连接进入，处理连接
void
Connection_handler_manager::process_new_connection(Channel_info* channel_info)
{
　 // check_and_incr_conn_count() 检查是否有空余连接, 如果没有空余连接, 结束处理流程
  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_accepted = true
  if (m_connection_handler->add_connection(channel_info))
  {
    inc_aborted_connects();
    delete channel_info;
  }
}

　　

// 在这里创建连接
bool Per_thread_connection_handler::add_connection(Channel_info *channel_info)
{
  int error = 0;
  my_thread_handle id;

  DBUG_ENTER("Per_thread_connection_handler::add_connection");

  // Simulate thread creation for test case before we check thread cache
  DBUG_EXECUTE_IF("fail_thread_create", error = 1; goto handle_error;);
  // 检查 thread cache 中是否有空闲 thread，如果有，使用 cached thread
  if (!check_idle_thread_and_enqueue_connection(channel_info))
    DBUG_RETURN(false);

  /*
    There are no idle threads avaliable to take up the new
    connection. Create a new thread to handle the connection
    没有可用的空闲线程来处理新的连接，创建一个新的线程来处理这个连接。
  */
  channel_info->set_prior_thr_create_utime();
  error = mysql_thread_create(key_thread_one_connection, &id,
                              &connection_attrib,
                              handle_connection,
                              (void *)channel_info);
#ifndef NDEBUG
handle_error:
#endif // !NDEBUG

  if (error)
  {
    connection_errors_internal++;
    if (!create_thd_err_log_throttle.log())
      sql_print_error("Can't create thread to handle new connection(errno= %d)",
                      error);
    channel_info->send_error_and_close_channel(ER_CANT_CREATE_THREAD,
                                               error, true);
    Connection_handler_manager::dec_connection_count();
    DBUG_RETURN(true);
  }

  Global_THD_manager::get_instance()->inc_thread_created();
  DBUG_PRINT("info", ("Thread created"));
  DBUG_RETURN(false);
}

bool Per_thread_connection_handler::check_idle_thread_and_enqueue_connection(
    Channel_info *channel_info)
{
  bool res = true;

  mysql_mutex_lock(&LOCK_thread_cache);
  // 如果 blocked_pthread > wake_thread，则 thread cache 中存在空闲 thread
  if (Per_thread_connection_handler::blocked_pthread_count > wake_pthread)
  {
    DBUG_PRINT("info", ("waiting_channel_info_list->push %p", channel_info));
    // 将 channel_info 放入 waiting_channel_info_list，wake_pthread ++
    waiting_channel_info_list->push_back(channel_info);
    wake_pthread++;
    mysql_cond_signal(&COND_thread_cache);
    res = false;
  }
  mysql_mutex_unlock(&LOCK_thread_cache);

  return res;
}

　　

/**
  Thread handler for a connection
  线程处理函数。
  @param arg   Connection object (Channel_info)

  This function (normally) does the following:
  - Initialize thread
  - Initialize THD to be used with this thread
  - Authenticate user
  - Execute all queries sent on the connection
  - Take connection down
  - End thread  / Handle next connection using thread from thread cache
*/
extern "C" void *handle_connection(void *arg)
{
  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);
  bool pthread_reused MY_ATTRIBUTE((unused)) = false;
 
  if (my_thread_init())
  {
    connection_errors_internal++;
    channel_info->send_error_and_close_channel(ER_OUT_OF_RESOURCES, 0, false);
    handler_manager->inc_aborted_connects();
    Connection_handler_manager::dec_connection_count();
    delete channel_info;
    my_thread_exit(0);
    return NULL;
  }

  for (;;)
  {
    // 初始化一个 thd 对象
    THD *thd = init_new_thd(channel_info);
    if (thd == NULL)
    {
      connection_errors_internal++;
      handler_manager->inc_aborted_connects();
      Connection_handler_manager::dec_connection_count();
      break; // We are out of resources, no sense in continuing.
    }

    // 将新创建的 thd 添加到 thd list；
    thd_manager->add_thd(thd);
    /**
     1. 初始化 lex 词法解析器
     2. 进行连接身份验证
     3. 初始化 thd , 准备执行语句
    */
    if (thd_prepare_connection(thd))
      handler_manager->inc_aborted_connects();
    else
    {
      // 只要连接 alive，就会一直循环下去
      while (thd_connection_alive(thd))
      {
        // 处理命令，这是 MySQL 的核心操作
        if (do_command(thd))
          break;
      }
      // 减少当前用户的连接计数等
      end_connection(thd);
    }
    // 关闭一个连接
    close_connection(thd, 0, false, false);
    // 释放资源
    thd->get_stmt_da()->reset_diagnostics_area();
    thd->release_resources();

    // Clean up errors now, before possibly waiting for a new connection.
#if OPENSSL_VERSION_NUMBER < 0x10100000L
    ERR_remove_thread_state(0);
#endif /* OPENSSL_VERSION_NUMBER < 0x10100000L */
    // 从 thd list 中移除 thd
    thd_manager->remove_thd(thd);
    // 减少当前连接数
    Connection_handler_manager::dec_connection_count();

    delete thd;

    if (abort_loop) // Server is shutting down so end the pthread.
      break;
    // 阻塞当前的物理线程，使得物理线程被新的连接重用
    channel_info = Per_thread_connection_handler::block_until_new_connection();
    if (channel_info == NULL)
      break;
    pthread_reused = true;
    if (abort_loop)
    {
      // Close the channel and exit as server is undergoing shutdown.
      channel_info->send_error_and_close_channel(ER_SERVER_SHUTDOWN, 0, false);
      delete channel_info;
      channel_info = NULL;
      Connection_handler_manager::dec_connection_count();
      break;
    }
  }

  my_thread_end();
  my_thread_exit(0);
  return NULL;
}

　　

/**
  Block the current pthread for reuse by new connections.
  阻塞当前的物理线程，供新的连接使用
  @retval NULL   Too many pthreads blocked already or shutdown in progress.
  @retval !NULL  Pointer to Channel_info object representing the new connection
                 to be served by this pthread.
*/

Channel_info *Per_thread_connection_handler::block_until_new_connection()
{
  Channel_info *new_conn = NULL;
  mysql_mutex_lock(&LOCK_thread_cache);
  if (blocked_pthread_count < max_blocked_pthreads &&
      !kill_blocked_pthreads_flag)
  {
    /* Don't kill the pthread, just block it for reuse */
    DBUG_PRINT("info", ("Blocking pthread for reuse"));

    /*
      mysys_var is bound to the physical thread,
      so make sure mysys_var->dbug is reset to a clean state
      before picking another session in the thread cache.
    */
    DBUG_POP();
    assert(!_db_is_pushed_());

    // Block pthread ++
    blocked_pthread_count++;
    while (!abort_loop && !wake_pthread && !kill_blocked_pthreads_flag)
      // 这里等待信号
      mysql_cond_wait(&COND_thread_cache, &LOCK_thread_cache);
    blocked_pthread_count--;

    if (kill_blocked_pthreads_flag)
      mysql_cond_signal(&COND_flush_thread_cache);
    else if (wake_pthread)
    {
      // wake_pthread 计数器 -1 
      wake_pthread--;
      if (!waiting_channel_info_list->empty())
      {
        // 如果 waiting_channel_info_list 不是空的, 则取出第一个
        new_conn = waiting_channel_info_list->front();
        waiting_channel_info_list->pop_front();
        DBUG_PRINT("info", ("waiting_channel_info_list->pop %p", new_conn));
      }
      else
      {
        assert(0);
      }
    }
  }
  mysql_mutex_unlock(&LOCK_thread_cache);
  return new_conn;
}