饮鸩止渴的方案

  • 问题
  • 短连接风暴
  • 解决方案
  • 慢查询性能问题
  • 索引没有设计好
  • 语句没写好
  • mysql选错了索引


问题

业务高峰期，生产环境的MYSQL压力太大，没法正常响应，需要短时间内、临时性的提升一些性能

短连接风暴

正常的短连接模式就是连接到数据库后，执行很少的SQL语句就断开，下次需要的时候就重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况

sql语句： 查看数据库连接数的上限：

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

如果连接数超过上限，就会拒绝接下来的连接请求，对于被拒绝的请求来说，从业务角度就是不可用

解决方案

1. 增大连接数

这种方案由于增大了连接数，在连接的时候有会进行获取权限验证操作，会消耗CPU的物理资源

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

在处理不工作的线程时我们也要区分线程的状态：

如何提高MySQL的update 语句执行效率 mysql提高性能_连接数

如果是session A里面的线程的话，关闭连接会进行事务回滚，照样会消耗CPU资源，如果session B这种情况的话就好的多

show processlist只是能够看到会话是sleep状态，如果需要继续查的话，可以查information_schema库的innodb_trx表：

mysql> select * from Innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 422010600774368
                 trx_state: RUNNING
               trx_started: 2020-08-28 22:45:21
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 5030
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   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: 5030这个属性，表示id为5030的线程还处在事务当中

因此: 在kill connection的时候，优先断开不在事务内的连接，也就是trx_mysql_thread_id里面没有包含的连接

3. 减少连接过程中的消耗

有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段

跳过权限验证的方案：重启数据库，并使用skip-grant-tables参数启动，开启了这个参数后，会跳过连接过程和执行过程的权限验证阶段，这个操作是非常危险的，在MYSQL8之后，开启了之后默认会把skip-networking参数打开，表示这时候的数据库只能被本地的客户端连接

注意点：
除了短连接数暴增可能会带来性能问题外，实际上，我们在线上碰到更多的是查询或更新语句带来的性能问题，其中查询问题有两类，一类是由新出现的慢查询导致的，一类是由QPS（每秒查询数）突增导致的

慢查询性能问题

索引没有设计好

这种情况一般就是通过紧急创建索引来解决，mysql5.6之后，支持Online DDL

语句没写好

这种情况跟写SQL的人有很大关系，我们前面就探讨过SQL逻辑相同，但性能相差巨大的情况

解决方案：
mysql5.7提供了query_rewrite功能，可以把输入的语句改写成另外一种模式

insert into query_rewrite.rewrite_rules(pattern,replacement,pattern_database) values ('select * from t where id + 1 = ?','select * from t where id = ? - 1','shiro');
call query_rewrite.flush_rewrite_rules();

mysql选错了索引

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