mysql提高_mysql深入系列-mysql提高性能的方法（临时方案）

背景

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

问题:

短连接风暴

1.正常的短连接模式，连接到数据库后，连接执行很少的sql就断开连接；下次需要的时候再重连，但是在业务高峰期间的时候，就可能出现连接突然暴涨的情况。

说明：mysql中的max_connections参数，控制一个mysql实例同时存在的连接数上限；如果>max_connections值，系统拒绝链接，并报错：“Too many connnecions”;

从业务角度看到是数据库链接不可用。

2.机器负载较高的时候，处理现有的请求时间变长，每个连接保持的时间也变长，这样有新的连接的话，有可能会超过max_connections的限制。

3.mysql建立连接的成本高，除了正常的3次握手外，还需要做登录权限判断和获取这个链接的数据的读和写的权限。

解决方案:

1.先处理掉那些占用着连接但是不工作的线程(如果是连接过多，考虑优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。)

命令：show processlist

展示连接的详细信息：

命令：select * from information_schema.INNODB_TRX

查看事务的具体的状态：trx_mysql_thread_id = 6 表示id = 6的线程还在事务中；

开启事务，在t表中插入一条数据：

通过查询information_schema库中的INNODB_TRX的表发现多了trx_mysql_thread_id = 14的事务；

mysql> select *from INNODB_TRX \G;*************************** 1. row ***************************trx_id:102202trx_state: RUNNING

trx_started:2019-02-03 17:49:14trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight:2trx_mysql_thread_id:14trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use:0trx_tables_locked:1trx_lock_structs:1trx_lock_memory_bytes:1136trx_rows_locked:0trx_rows_modified:1trx_concurrency_tickets:0trx_isolation_level: REPEATABLE READ

trx_unique_checks:1trx_foreign_key_checks:1trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched:0trx_adaptive_hash_timeout:0trx_is_read_only:0trx_autocommit_non_locking:0

*************************** 2. row ***************************trx_id:281479467028936trx_state: RUNNING

trx_started:2019-02-01 17:02:13trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight:0trx_mysql_thread_id:9trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use:0trx_tables_locked:0trx_lock_structs:0trx_lock_memory_bytes:1136trx_rows_locked:0trx_rows_modified:0trx_concurrency_tickets:0trx_isolation_level: REPEATABLE READ

trx_unique_checks:1trx_foreign_key_checks:1trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched:0trx_adaptive_hash_timeout:0trx_is_read_only:0trx_autocommit_non_locking:0

2 rows in set (0.00 sec)

再开一个窗口使用kill connection id命令kill connection 14;然后再第一个未提交的事务中查询的时候，发现报错：“ERROR 2006 (HY000): MySQL server has gone away no connection”

从数据库端主动断开连接可能是有损的，尤其是有的应用端收到这个错误后，不重新连接而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去，“mysql 一直没恢复”。

2.减少连接过程中的消耗(不推荐，风险高)

如果现在确认数据库被连接行为打挂了，可能的做法是跳过数据库的权限验证阶段；

跳过权限验证的方法：重启数据库，并使用-skip-grant-tables参数启动；整个mysql会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。

慢查询性能问题

1.索引没有设计好

mysql5.6版本以后，创建索引支持Online DDL 对于高峰期数据库被这个语句打挂了的情况，最高效的做法是直接执行 alter table；

备库上先执行alter增加索引的操作：假设服务是一主一备：主库是A，备库是B，这个方法大致流程是这样的：

在备库B执行 set sql_log_bin=off，也就是不写binlog，然后执行alter table add index 语句增加索引；

执行主备切换；

这时候主库变为B，备库是A。在A上执行set sql_log_bin=off，然后用alter table语句加上索引；

这是一个常用的DDL方案，平时变更的时候考虑类似的gh-ost这样的方案，更加稳妥。但是在紧急处理的时候，上面的方案效率是最高的；

2.sql语句没有写好

3.mysql选错了索引

前面的的1，2是经常会发生的，这两种是可以避免的。

上线前，在测试环境，把慢查询的日志(show log)打开，并且吧long_query_time设置成0，确保每个语句都会被记录到慢查询日志当中；

在测试表里面插入模拟线上的数据，做一遍回归测试；

观察慢查询日志里每类语句的输出，特别留意Rows_examined字段是否 与预期一致。

可用使用开源工具digest：https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html 检查所有sql语句的返回结果。

QPS突增的问题

由于业务突然出现高峰，或者应用程序的bug，导致某个SQL的QPS突然暴涨，也可能导致Mysql的压力过大，影响服务；

应急方法：

1. 一种是如果是由于全新业务bug导致的，假设你的DB运维比较规范的话，也就是白名单是一个个加的，这种如果能确定业务方会下掉这个功能，只是时间没那么快，那么直接可以从数据库端直接把白名单去掉。

2.如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有的链接。新功能的链接不成功，引发的qps就会变为0；