1,mysql中,会引发性能问题的慢查询,大概有下述三种可能:
1)索引没设计好;
2)sql语句没有写好;
3)mysql选错了索引
1.1 对于索引没设计好导致的慢查询的解决方法:
由于mysql 5.6后 创建索引已经支持online ddl了,所以最高效的解决方法是直接执行alter table加索引。具体方法为:
1).在备库b上执行set sql_log_bin=off,即不写binlog,然后执行alter table语句加索引;
2).执行主备切换;
3).这时候主库是b,备库是a,在a上执行set sql_log_bin=off,然后执行alter table语句加上索引。
1.2 对于语句没写好导致的慢查询的解决方法:
改写sql语句。mysql 5.7提供了 query_rewrite功能,可以把输入的一种语句改写成另外一种语句模式。
比如:语句被错误地写成了 select * from t where id+1=1000; ,就可以通过下面的方式,增加一个语句改写规则:
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();
其中,call query_rewrite.flush_rewrite_rules();这个存储过程,是让插入的新规则生效,也就是“查询重写”
1.3.索引选错导致慢查询的解决方法:
应急方案就是给这个语句加上force index. 使用重写功能,给原来的语句加上force index.
通常,导致慢查询最多的可能其实是前2种,即:索引没设计好和语句没写好,而这2种情况,在上线前是可以避免的,通过以下测试:
1)上线前,在测试环境,把慢查询日志(show_log)打开,并且把long_query_time设置为0,确保每个语句都被记录入慢查询日志;
2)在测试表里插入模拟线上的数据,做一遍回归测试;
3)观察慢查询日志里每类语句的输出,特别留意rows_examined字段是否与预期一致。
如果新增的语句不多,手动跑一些;若是新项目,或是修改了原有项目的表结构设计,则需要全量回归测试,此时,可以通过工具 pt-query-digest帮你检查所有sql语句的返回结果。
2.对于qps突然增大的情况,有效的解决方法为:
1)账号,接口级别的限流
2)引导到备库执行