关于MySQL高并发处理机制是如何实现

限流算法目前程序开发过程常用的限流算法有两个：漏桶算法和令牌桶算法。

漏桶算法

漏桶算法的原理比较简单，请求进入到漏桶中，漏桶以一定的速率漏水。当请求过多时，水直接溢出。可以看出，漏桶算法可以强制限制数据的传输速度。如图所示，把请求比作是水滴，水先滴到桶里，通过漏洞并以限定的速度出水，当水来得过猛而出水不够快时就会导致水直接溢出，即拒绝服务。

图片来自网络

漏桶的出水速度是恒定的，那么意味着如果瞬时大流量的话，将有大部分请求被丢弃掉（也就是所谓的溢出）。

令牌桶算法

令牌桶算法的原理是系统以一定速率向桶中放入令牌，如果有请求时，请求会从桶中取出令牌，如果能取到令牌，则可以继续完成请求，否则等待或者拒绝服务。这种算法可以应对突发程度的请求，因此比漏桶算法好。

图片来自网络

漏桶算法和令牌桶算法的选择

两者的主要区别漏桶算法能够强行限制处理数据的速率，不论系统是否空闲。而令牌桶算法能够在限制数据的平均处理速率的同时还允许某种程度的突发流量。如何理解上面的含义呢？漏桶算法，比如系统吞吐量是 120/s，业务请求 130/s，使用漏斗限流 100/s，起到限流的作用，多余的请求将产生等待或者丢弃。对于令牌桶算法，每秒产生 100 个令牌，系统容量 200 个令牌。正常情况下，业务请求 100/s 时，请求能被正常被处理。当有突发流量过来比如 200 个请求时，因为系统容量有 200 个令牌可以同一时刻处理掉这 200 个请求。如果是漏桶算法，则只能处理 100 个请求，其他的请求等待或者被丢弃。

MySQL并发能力每秒600次以上，Redis并发能力每秒1000次。

MySQL和Redis都可以处理大量的并发请求，但具体的性能取决于应用程序的需求。MySQL的并发能力可以由它的QPS来衡量，而Redis的并发能力可以由它的QPS和KV存储来衡量。一般来说，MySQL的并发能力通常在每秒600次以上，而Redis的并发能力可以达到每秒1000次。

并发能力是指程序或问题的不同部分或单元可以乱序或部分执行的能力。

在MySQL 8.0 之前， 我们假设一下有一条烂SQL，

mysqlselect * from t1 order by rand()

以多个线程在跑，导致CPU被跑满了，其他的请求只能被阻塞进不来。那这种情况怎么办？

大概有以下几种解决办法：

设置max_execution_time 来阻止太长的读SQL。那可能存在的问题是会把所有长SQL都给KILL 掉。有些必须要执行很长时间的也会被误杀。

自己写个脚本检测这类语句，比如order by rand()， 超过一定时间用Kill query thread_id 给杀掉。

那能不能不要杀掉而让他正常运行，但是又不影响其他的请求呢？

那mysql 8.0 引入的资源组（resource group，后面简写微RG）可以基本上解决这类问题。

比如我可以用 RG 来在SQL层面给他限制在特定的一个CPU核上，这样我就不管他，让他继续运行，如果有新的此类语句，让他排队好了。

为什么说基本呢？目前只能绑定CPU资源，其他的暂时不行。

那我来演示下如何使用RG。

创建一个资源组user_ytt. 这里解释下各个参数的含义，

type = user 表示这是一个用户态线程，也就是前台的请求线程。如果type=system，表示后台线程，用来限制mysql自己的线程，比如Innodb purge thread,innodb read thread等等。

vcpu 代表cpu的逻辑核数，这里0-1代表前两个核被绑定到这个RG。可以用lscpu，top等列出自己的CPU相关信息。

thread_priority 设置优先级。user 级优先级设置大于0。

mysqlmysql>create resource group user_ytt type = user  vcpu = 0-1 thread_priority=19 enableQuery OK, 0 rows affected (0.03 sec)

RG相关信息可以从 information_schema.resource_groups 系统表里检索。

mysqlmysql>select * from information_schema.resource_groups+---------------------+---------------------+------------------------+----------+-----------------+| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |+---------------------+---------------------+------------------------+----------+-----------------+| USR_default         | USER                |                      1 | 0-3      |               0 || SYS_default         | SYSTEM              |                      1 | 0-3      |               0 || user_ytt            | USER                |                      1 | 0-1      |              19 |+---------------------+---------------------+------------------------+----------+-----------------+3 rows in set (0.00 sec)

我们来给语句select guid from t1 group by left(guid,8) order by rand() 赋予RG user_ytt。

mysql>show processlist+-----+-----------------+-----------+------+---------+-------+------------------------+-----------------------------------------------------------+| Id  | User            | Host      | db   | Command | Time  | State                  | Info                                                      |+-----+-----------------+-----------+------+---------+-------+------------------------+-----------------------------------------------------------+|   4 | event_scheduler | localhost | NULL | Daemon  | 10179 | Waiting on empty queue | NULL                                                      || 240 | root            | localhost | ytt  | Query   |   101 | Creating sort index    | select guid from t1 group by left(guid,8) order by rand() || 245 | root            | localhost | ytt  | Query   |     0 | starting               | show processlist                                          |+-----+-----------------+-----------+------+---------+-------+------------------------+-----------------------------------------------------------+3 rows in set (0.00 sec)

找到连接240对应的thread_id。

mysqlmysql>select thread_id from performance_schema.threads where processlist_id = 240+-----------+| thread_id |+-----------+|       278 |+-----------+1 row in set (0.00 sec)

给这个线程278赋予RG user_ytt。没报错就算成功了。

mysqlmysql>set resource group user_ytt for 278Query OK, 0 rows affected (0.00 sec)

当然这个是在运维层面来做的，我们也可以在开发层面结合 MYSQL HINT 来单独给这个语句赋予RG。比如：

mysqlmysql>select /*+ resource_group(user_ytt) */guid from t1 group by left(guid,8) order by rand()....8388602 rows in set (4 min 46.09 sec)

RG的限制：

Linux 平台上需要开启 CAPSYSNICE 特性。比如我机器上用systemd 给mysql 服务加上

systemctl edit mysql@80 [Service]AmbientCapabilities=CAP_SYS_NICE

mysql 线程池开启后RG失效。

freebsd,solaris 平台thread_priority 失效。

目前只能绑定CPU，不能绑定其他资源。

---