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;
}
