数据库极客时间-mysql
Source
Edit
History

提高性能的办法

一、大量短连接的问题

短连接一般指的是，连接到数据库后，执行很少的 SQL 语句后就断开。下次需要的时候再重连。但是 MySQL 在建立连接的过程中，成本是很高的。除了正常的网络连接三次握手外，还需要做登陆权限判断和获得这个连接的数据读写权限。

MySQL 的 max_connections 参数用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示 Too many connections。对于被拒绝连接的请求来说，从业务角度看就是数据库不可用。

遇到大量短连接的场景，如果只是单纯调高 max_connections 的值，是不行的。因为设置 max_connections 这个参数的目的就是想保护 MySQL，如果把他改的太大，让更新的连接进来之后，系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到 CPU 资源去执行业务的 SQL 请求。

这里有两种方法，但是都是有损的。

1. 先处理掉那些占着连接但不工作的线程

max_connections 的计算是，只要客户端和服务端是连接着的就占用一个计数位置。

  • 对于那些不需要保持的连接，我们可以通过 kill connection 主动踢掉。这个行为和事先设置 wait_timeout 的效果一致。
  • 设置 wait_timeout 参数表示的是，一个线程空闲 wait_timeout 这么多秒之后，就会被 MySQL 直接断开连接。

有些 session 中可能包含 插入语句，如果被断开此 session 的连接，这时，这个 session 还没有提交，所以 MySQL 只能按照回滚事务来处理。因此如果要断开连接，优先断开那种事务外空闲的 session。如何判断事务外空闲的 session 呢？

  • 使用 show processlist，筛选出 Command 为 Sleep 的线程。
  • 查看事务的具体状态，可以查看 information_schema.innodb_trx 表中的 trx_mysql_thread_id 字段，即可以获取到线程 id。并且可以看到此事务当前的状态。

于是，如果是连接数过多，可以优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。

  • 从服务端断开连接使用的是 kill connection + id 的命令，一个客户端处于 sleep 状态时，他的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一个请求的时候，才会收到这样的报错：ERROR 2013 (HY000): Lost connection to MySQL server during query。
  • 从数据库服务端主动断开连接可能是有损的，尤其是有的客户端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试查询。这会导致从客户端看上去，MySQL 一直没恢复。

2. 减少连接过程的消耗

一种可能的做法是，让数据库跳过权限验证阶段。

方法是：重启数据库，并使用 --skip-grant-tables 参数启动。这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。但是这种方法分险特别高。尤其是外网可访问的数据库的话，一定不能这么做。

实际上，在线上遇到的更多的是查询或者更新语句导致的性能问题。其中，查询问题比较典型的有两类，分别是：

  • 一类是新出现的慢查询导致的
  • 另一类是由 QPS（每秒查询数）突增导致的

还有关于更新语句导致的性能问题

二、慢查询导致的性能问题

在 MySQL 中，会引发性能问题的慢查询，有三种可能：

  • 索引没有设计好
  • 性能差的 SQL 语句
  • MySQL 选错了索引

1. 索引没有设计好

通过紧急创建索引来解决。MySQL 5.6 版本后，创建索引支持 Online DDL 了，对于那种高峰期数据库已经被这个语句打挂的情况，最高效的做法就是直接执行 alter table 语句。

如果我们是有 “主备” 架构的服务。比如有主库 A、备库 B。我们可以：

  • 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引
  • 执行主备切换
  • 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引

这个是紧急时的方案。如果要平时做变更，应该考虑 gh-ost 类似的方案。

2. 性能差的 SQL 语句 — 查询重写

有些 SQL 语句可能没有使用到索引。我们可以通过改写 SQL 语句来处理。MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式。

比如，错误的 SQL 语句：select * from t where id + 1 = 10000。可以通过下面的方式，增加一个语句改写规则：

1
2
3
4
mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");

// 为了让插入的新规则生效，也就是让 “查询重写” 生效
call query_rewrite.flush_rewrite_rules();

我们可以使用 show warnings; 来确认改写规则是否生效。

3. MySQL 选错索引

应急方案就是给这个语句加上 force index。

4. 提前规避慢查询

  • 上线前，在测试环境，把慢查询日志打开，并且把 long_query_time 设置为 0，确保每个语句都会被记录到慢查询日志。
  • 在测试表中插入模拟线上的数据，做一遍回归测试
  • 观察慢查询日志中每种语句的输出，特别留意 Rows_examined 字段是否于预期一致

三、QPS 突增问题

有时候由于业务突然出现高峰，导致某个语句的 QPS 突然暴涨，可能导致 MySQL 压力过大，影响服务。当然，最理想的情况是让业务把这个功能下掉，服务自然就会恢复。

如果我们确定要下掉这个功能。那么不同的背景，有不同的方法。

  • 假如是由于全新业务的 bug 导致。并且有加上了白名单。而且我们能够确定业务方会下掉这个功能，只是时间上没有那么快，因此就可以从数据库端直接把白名单去掉
  • 如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由他引发的 QPS 就会变成 0
  • 如果这个新增的功能与主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用 “查询重写” 功能，把压力最大的 SQL 语句直接写成 “select 1” 返回。不过这个操作风险很高，可能存在两个副作用：
    • 如果别的功能中也用到了这个 SQL 语句模板，会有误伤
    • 很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以 select 1 的结果返回的话，可能会导致后面的业务逻辑一起失败
    • 因此这是一种优先级最低的方案

四、小结

  • 在做业务开发时，连接异常断开是常有的事情，代码中要有正确的重连并重试的机制。
  • 避免大量的使用短连接
  • 做好 SQL 审计是规避风险的好办法之一