1.短连接风暴
现象描述：
- 1）情况描述
正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。 - 2）建立连接成本比较高
MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。在数据库压力比较小的时候，这些额外的成本并不明显。 - 3）负载变高时
短连接模型存在一个风险，就是一旦数据库处理得慢一些，连接数就会暴涨。max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。
在机器负载比较高的时候，处理现有请求的时间变长，每个连接保持的时间也更长。这时，再有新建连接的话，就可能会超过 max_connections 的限制。 - 4）增大max_connections可能会适得其反
碰到这种情况时，一个比较自然的想法，就是调高 max_connections 的值。但这样做是有风险的。因为设计 max_connections 这个参数的目的是想保护 MySQL，如果我们把它改得太大，让更多的连接都可以进来，那么系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到 CPU 资源去执行业务的 SQL 请求。
1.1 先处理掉那些占着连接但是不工作的线程。
max_connections 的计算，不是看谁在 running，是只要连着就占用一个计数位置。对于那些不需要保持的连接，我们可以通过 kill connection 主动踢掉。这个行为跟事先设置wait_timeout 的效果是一样的。设置 wait_timeout 参数表示的是，一个线程空闲wait_timeout 这么多秒之后，就会被 MySQL 直接断开连接。
如果断开 session A 的连接，因为这时候 session A 还没有提交，所以MySQL 只能按照回滚事务来处理；而断开 session B 的连接，就没什么大影响。所以，如果按照优先级来说，你应该优先断开像 session B 这样的事务外空闲的连接。
mysql> show processlist;
+---------+-------------+-----------------------+----------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-------------+-----------------------+----------+---------+------+----------+------------------+
| 2421400 | wz | 47.99.62.246:58886 | sqllearn | Sleep | 28 | | NULL |
| 2421401 | wz | 47.99.62.246:58888 | sqllearn | Sleep | 13 | | NULL |
| 2421402 | wz | 47.99.62.246:58890 | sqllearn | Query | 0 | starting | show processlist |
+---------+-------------+-----------------------+----------+---------+------+----------+------------------+
查看事务具体的状态：
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 2743315
trx_state: RUNNING
trx_started: 2019-08-15 08:38:28
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 2421400
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
可以看出 trx_mysql_thread_id: 2421400线程还在事务中。
断开连接：
mysql> kill connection 2421400;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_20 where id=22;
Empty set (0.00 sec)
mysql> select * from test_20 where id=22;
ERROR 2013 (HY000): Lost connection to MySQL server during query
从数据库端主动断开连接可能是有损的，尤其是有的应用端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去，“MySQL 一直没恢复”。
1.2 减少连接过程的消耗——风险极高
有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。
跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables 参数启动。这样，整个MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。
在 MySQL 8.0 版本里，如果你启用–skip-grant-tables 参数，MySQL 会默认把 --skip-networking 参数打开，表示这时候数据库只能被本地的客户端连接。可见，MySQL 官方对skip-grant-tables 这个参数的安全问题也很重视。
2.慢查询性能问题
在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：
- 1）索引没有设计好；
- 2）SQL 语句没写好；
- 3）MySQL 选错了索引。
2.1 索引没有设计好
景一般就是通过紧急创建索引来解决。MySQL 5.6 版本以后，创建索引都支持 OnlineDDL 了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行alter table 语句。
操作流程：
- step1.在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
- step2. 执行主备切换；
- step3. 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。
平时在做变更的时候，你应该考虑类似 gh-ost 这样的方案，更加稳妥。但是在需要紧急处理时，上面这个方案的效率是最高的。
2.2 语句没写好
通过改写 SQL 语句来处理。MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式。
语句被错误地写成了 select * from t where id + 1 = 10000，可以进行如下处理：
insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "sqllearn");
call query_rewrite.flush_rewrite_rules();
该特性需要新装一个数据库：
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| query_rewrite |
| sys |
+--------------------+
2.3 MySQL选错了索引
应急方案就是给这个语句加上 force index。同样地，使用查询重写功能，给原来的语句加上 force index，也可以解决这个问题。
预先发现问题的方法：
- step1.上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成0，确保每个语句都会被记录入慢查询日志；
- step2. 在测试表里插入模拟线上的数据，做一遍回归测试；
- step3. 观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致。
如果新增的 SQL 语句不多，手动跑一下就可以。而如果是新项目的话，或者是修改了原有项目的表结构设计，全量回归测试都是必要的。这时候，你需要工具帮你检查所有的 SQL 语句的返回结果。比如，你可以使用开源工具 pt-query-digest(https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html)。
3.QPS突增问题
有时候由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。
最理想的情况是让业务把这个功能下掉，服务自然就会恢复。
下掉一个功能，如果从数据库端处理的话，对应于不同的背景，有不同的方法可用：
- 1）一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。
- 2）如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。
- 3） 如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。
这个操作的风险很高，需要你特别细致。它可能存在两个副作用：
- 1）如果别的功能里面也用到了这个 SQL 语句模板，会有误伤；
- 2）很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以 select 1 的结果返回的话，可能会导致后面的业务逻辑一起失败。
所以，方案 3 是用于止血的，跟前面提到的去掉权限验证一样，应该是你所有选项里优先级最低的一个方案。
同时你会发现，其实方案 1 和 2 都要依赖于规范的运维体系：虚拟化、白名单机制、业务账号分离。由此可见，更多的准备，往往意味着更稳定的系统。
4.其他问题
- 如果一个数据库是被客户端的压力打满导致无法响应的，重启数据库是没用的
这个问题是因为重启之后，业务请求还会再发。而且由于是重启，buffer pool 被清空，可能会导致语句执行得更慢。 - 有时候一个表上会出现多个单字段索引（而且往往这是因为运维工程师对索引原理不够清晰做的设计），这样就可能出现优化器选择索引合并算法的现象。但实际上，索引合并算法的效率并不好。而通过将其中的一个索引改成联合索引的方法，是一个很好的应对方案。
- 客户端程序的连接器，连接完成后会做一些诸如 show columns 的操作，在短连接模式下这个影响就非常大了。
这个提醒我们，在 review 项目的时候，不止要 review 我们自己业务的代码，也要 review 连接器的行为。一般做法就是在测试环境，把 general_log 打开，用业务行为触发连接，然后通过 general log 分析连接器的行为。 - 如果你的数据库请求模式直接对应于客户请求，这往往是一个危险的设计。因为客户行为不可控，可能突然因为你们公司的一个运营推广，压力暴增，这样很容易把数据库打挂。
在设计模型里面设计一层，专门负责管理请求和数据库服务资源，对于比较重要和大流量的业务，是一个好的设计方向。
网友评论