MySQL 链接方式

2018-07-22 database mysql

与 Oracle 或者 Postgre 不同，MySQL 采用的是线程模型，在这里介绍通过 socket 链接到服务器之后，线程与链接直接是怎么处理的。

简介

现在 MySQL 支持三种处理链接的方式：no-threads、one-thread-per-connection 和 pool-of-threads，默认使用 one-thread-per-connection。而线程池的方式，只有企业版才支持；单线程则通常用户调试或者嵌入式的模式，因此，在此主要介绍每个连接单线程的方式。

在启动时可以通过 --thread-handling=XXX 参数指定，也可以在配置文件中指定，而当前使用的链接方式可以通过 SHOW VARIABLES LIKE 'thread_handling' 查看。注意，该选项是只读的，也就是链接方式只能在启动时进行设置。

MariaDB 在 5.5 引入了一个动态的线程池方案，可以根据当前请求的并发情况自动增加或减少线程数，在此的线程池就是 MariaDB 的解决方案。

  1. 单线程 one_thread_scheduler()
    在同一时刻，最多只能有一个链接连接到 MySQL ，其他的连接会被挂起，一般用于实验性质或者嵌入式应用。
  2. 多线程 one_thread_per_connection_scheduler()
    同一时刻可以支持多个链接，针对每个链接分配一个线程来处理这个链接的所有请求，直到连接断开，线程才会结束。
    这种方式存在的问题就是需要为每个连接创建一个新的 thread，当并发连接数达到一定程度，性能会有明显下降，因为过多的线程会导致频繁的上下文切换，CPU cache 命中率降低和锁的竞争会更加激烈。
  3. 线程池 pool_of_threads_scheduler()
    解决多线程的方法就是降低线程数，这样就需要多个连接共用线程，这便引入了线程池的概念。线程池中的线程是针对请求的，而不是针对连接的，也就是说几个连接可能使用相同的线程处理各自的请求。

注意，后面主要介绍 MariaDB 的实现方式。

设置

如上所述，可以通过设置服务器的启动参数来设定连接的方式，通过 mysqld --verbose --help 命令可以查看所支持的选项，启动后可以通过 SHOW STATUS 查看与行状态，通过 SHOW VARIABLES 查看启动时的变量。

$ mysqld --thread-handling=no-threads/one-thread-per-connection/pool-of-threads

$ cat /etc/my.cnf
[mysqld]
thread_handling=pool-of-threads

mysql> SHOW VARIABLES LIKE 'thread_handling';       ← 查看连接配置
+-----------------+---------------------------+
| Variable_name   | Value                     |
+-----------------+---------------------------+
| thread_handling | one-thread-per-connection |
+-----------------+---------------------------+
1 row in set (0.01 sec)

除了上述的链接方式之外，为了防止链接过多，导致在管理时无法登陆，MariaDB 提供了额外的链接方式，可以通过设置如下的参数实现 --extra-port=3308 --extra-max-connections=1 。

注意，如果 extra-max-connections 设置为 2 则实际上可以创建三个链接，而且只支持 one-thread-per-connection 类似的方式。

监控状态

MySQL 启动后，会监听端口，当有新的客户端发起连接请求时，MySQL 将为其分配一个新的 thread，去处理此请求。从建立连接开始，CPU 要给它划分一定的 thread stack，然后进行用户身份认证，建立上下文信息，最后请求完成，关闭连接，释放资源。

高并发情况下，将给系统带来巨大的压力，不能保证性能。MySQL 通过线程缓存来是实现线程重用，减小这部分的消耗；一个连接断开，并不销毁承载其的线程，而是将此线程放入线程缓冲区，并处于挂起状态，当下一个新的连接到来时，首先去线程缓冲区去查找是否有空闲的线程，如果有，则使用之，如果没有则新建线程。

mysql> SHOW VARIABLES LIKE 'thread_cache_size'; ← 可以重用线程的个数
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+
1 row in set (0.03 sec)

mysql> SHOW STATUS LIKE 'threads%';             ← 查看状态
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |       ← 已被线程缓存池缓存的线程个数
| Threads_connected | 2     |       ← 当前MySQL的连接数
| Threads_created   | 1065  |       ← 已创建线程个数，可用来判断thread_cache_size大小
| Threads_running   | 1     |       ← 正在运行的线程数
+-------------------+-------+
4 rows in set (0.13 sec)

源码导读

首先大致介绍线程管理。

数据结构

MariaDB 支持的链接类型可以通过 enum scheduler_types 查看，目前只支持上述的三种类型。根据启动时的配置项，会将所使用的链接方式会保存在 scheduler_functions thread_scheduler 变量中。

struct scheduler_functions
{
  uint max_threads, *connection_count;
  ulong *max_connections;
  bool (*init)(void);
  bool (*init_new_connection_thread)(void);
  void (*add_connection)(THD *thd);
  void (*thd_wait_begin)(THD *thd, int wait_type);
  void (*thd_wait_end)(THD *thd);
  void (*post_kill_notification)(THD *thd);
  bool (*end_thread)(THD *thd, bool cache_thread);
  void (*end)(void);
};

在初始化时，会通过如下函数设置全局变量 thread_scheduler 的值。

mysqld_main()
 |-init_common_variables()
   |-get_options()

该选项会在 mysqld_get_one_option()@sql/mysqld.cc 中处理，这个是在解析参数时调用的，相关的部分如下：

case OPT_ONE_THREAD:
    thread_handling = SCHEDULER_NO_THREADS;
    break;

在主函数中，调用 logger.init_base() 之后，调用了一个 init_common_variables() 函数，里面初始化了这个变量的值。在 init_common_variables() 中调用了一个 get_options()@sql/mysqld.cc 函数，在该函数中对全局变量 thread_handling 进行初始化，然后设置相应的模式：

if (thread_handling <= SCHEDULER_ONE_THREAD_PER_CONNECTION)
    one_thread_per_connection_scheduler(thread_scheduler, &max_connections,
                                             &connection_count);
else if (thread_handling == SCHEDULER_NO_THREADS)
    one_thread_scheduler(thread_scheduler);
else
    pool_of_threads_scheduler(thread_scheduler,  &max_connections,
                                         &connection_count);

这三个函数，其实就是设置了一个类型为 struct scheduler_functions 的 thread_scheduler 变量，只不过这些参数是一些函数指针罢了，也就是说在具体的调用中，只需要调用 add_connection 或 end_thread 即可，不需要知道到底是调用了哪个函数。

其它

介绍一些与线程相关的内容。

链接池和线程池

链接池是部署在应用端，为了防止客户端会频繁建立链接然后中断链接，当用户不需要该链接时，会在客户端缓存这些链接，这样如果下次用户再需要建立链接时可以直接复用该链接。

链接池可以有效减小服务器和客户端的执行时间，但是不会影响查询性能。