Explanation and analysis of the MySQL server connection process

Mysqld is the main process of the MySQL server. It can be said that mysqld is the real core of MySQL, and all work is carried out around the mysqld process. So to dissect the behemoth mysql, the code of mysqld is the best breakthrough.

Everything starts from the familiar main() function. In fact, it starts from the mysqld_main() function. These codes are all in mysqld.cc. mysqld_main() then calls win_main)(). The win_main() function mainly does some initialization work.

After the initialization work is completed, MySQL is ready to accept connections. Then our protagonist Handle_connections_methods() function appears. The main job of this function is to create three new sub-processes, which accept connections in three ways: TCP/IP, named pipes, and shared memory. Under normal circumstances, customers use TCP/IP (socket) to connect to the MySQL server, which is the most flexible communication method. However, in the application environment of embedded software, the latter two communication methods need to be used.

• Simplified handle_connections_methods() function:

static void handle_connections_methods()
{
  mysql_mutex_lock(&LOCK_thread_count);
  mysql_cond_init(key_COND_handler_count, &COND_handler_count, NULL);
  handler_count=0;
  handler_count++;
  mysql_thread_create(key_thread_handle_con_namedpipes, &hThread, &connection_attrib, handle_connections_namedpipes, 0))；
  handler_count++;
  mysql_thread_create(key_thread_handle_con_sockets, &hThread, &connection_attrib, handle_connections_sockets_thread, 0))；
  handler_count++;
  mysql_thread_create(key_thread_handle_con_sharedmem, &hThread, &connection_attrib, handle_connections_shared_memory, 0))
  while (handler_count > 0)
    mysql_cond_wait(&COND_handler_count, &LOCK_thread_count);
  mysql_mutex_unlock(&LOCK_thread_count);
}

After creating three new threads, the handle_connectins_methods() function enters a long loop and does not exit until all three connection threads have exited. Here I mainly look at the socket connection thread. Our research object is this handle_connections_sockets_thread. After this thread initialized itself, it directly called handle_connections_sockets();

The handle_connections_sockets() function uses select() to call the listening port of mysqld, and then waits for the client to connect. After a client connects, a new THD type variable will be created in this function. This variable is a "social butterfly", starting from the connection establishment, to SQL syntax analysis, query execution, result return, etc. This variable is always there, and in short it is a very important variable.

There is also the structure struct st_vio, which is a transfer station for commands. A vio type structure is also defined in the "Social Butterfly" THD. The function of this structure is to read the communication content from the storage socket, and then assign its value to the vio variable of THD. The VIO type describes a request in detail, including the content of the request, time, socket address of the request, etc. What happens next is to pass this "social butterfly" to the service thread, and create_thread_to_handle_connection() implements this function.

• The following is the deleted code

void create_thread_to_handle_connection(THD *thd)
{
  if (cached_thread_count > wake_thread)
  {
    mysql_cond_signal(&COND_thread_cache);
  }
  else
  {
    mysql_thread_create(key_thread_one_connection, &thd->real_id, &connection_attrib, handle_one_connection, (void*) thd)))；    
  }
}

This function will check whether there are idle cache threads (MySQL will not destroy the service thread immediately after disconnection, but cache it). If there is, the cache thread will be used. If not, a new thread will be created to serve the connection. At this point, a connection has entered the service thread, and the connection thread returns and continues to wait for the connection.

The following content is all implemented in the service thread. There is a very detailed code trace in "In-depth Understanding of MySQL". Interested students can take a look. I've attached the function calling sequence for reference.

handle_one_connection()
mysql_thread_create()
handle_one_connection()
do_handle_one_connection()
init_new_connection_thread()
init_new_connection_handler_thread()
do_command()
dispatch_command()
mysql_parse()
mysql_execuate_command()

         

The above is the detailed content of Explanation and analysis of the MySQL server connection process. For more information, please follow other related articles on the PHP Chinese website!