22 | 如何让MySQL临时提升下性能

一、前言

​注意，文章中讲的方案皆是有损的，需要评估业务能否承受。

二、正文

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

PS：暴涨的危害是什么？连接的成本高，CPU资源都浪费在中括号中【除了正常的网络连接 三次握手 外，还需要做 登录权限判断 和获得这个 连接的数据读写权限】

解决方案

第一种方法：通过show processlist命令kill connection + id掉那些sleep的线程。即：占着连接但是不工作的线程。进一步查看事务状态的话，通过select * from information_schema.innodb_trx\G命令判断链接是存在事务中，还是事务外。为什么？因为断开连接也是有原则的，优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。

额外注意点，从 数据库端主动断开连接 可能是 有损 的，尤其 是有的 应用端 收到这个错误后，不重新连接，而是 直接用 这个已经 不能用的句柄重试查询。这会 导致从应用端看上去，“MySQL 一直没恢复”。因此需要通知到研发团队！！！
第二种方法：通过重启数据库，并使用–skip-grant-tables 参数启动。这样，跳过整个 MySQL 所有的权限验证阶段，包括连接过程和语句执行过程在内。

重点注意，风险极高，如果外网可访问数据库，就相当于把自家的老窝给暴露给敌人一样。

场景2：慢查询性能问题，三种可能性：1.索引没有设计好【由于5.7后支持Online DDL所以可以紧急创建索引，但是建议先在备库关闭binlog写（set sql_log_bin = off）,在执行alter，切换主备后继续执行前置步骤】。2.SQL语句没写好【通过5.7以后的query_rewrite 功能把输入一种语句改成另外一种，比如：2A所示】。3.MySQL选错索引【使用force index】。

总结：通过事前观察slow log可以避免慢查询。

案例：

2A：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();

如何确认2A是否生效？

mysql>select * from t where id +1 = 10000;

mysql>show warning;

Level Code Message

Note 1105 Query ‘select * from t where id + 1 = 10000’ rewritten to ‘select * from t where id = 10000-1’ by a query rewrite plugin;

场景3：QPS 突增问题，DB层面两种方案：1.数据库端增加白名单。2.删掉客户端的用户名。

三、总结

🏁：1.在Innodb中锁就是加在索引上的，这是一个基础设定。

🏁：2.如果一个 select * from … for update 语句，优化器决定使用全表扫描，那么就会把主键索引上 next-key lock 全加上

🏁：3.<= 到底是间隙锁还是行锁？其实，这个问题，你要跟“执行过程”配合起来分析。在 InnoDB 要去找“第一个值”的时候，是按照等值去找的，用的是等值判断的规则；找到第一个值以后，要在索引内找“下一个值”，对应于我们规则中说的范围查找.

🏁：4.“有行”才会加行锁。如果查询条件没有命中行，那就加 next-key lock。当然，等值判断的时候，需要加上优化 2（即：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。）。