一、前言
注意,文章中讲的方案皆是有损的,需要评估业务能否承受。
二、正文
场景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 退化为间隙锁。)。