问题一:短连接风暴
短连接:连接到数据库执行很少SQL后就被断开。
在业务高峰期,可能会出现连接数暴涨的问题。
影响:MySQL建立连接的成本很高,除了正常的网络连接三次握手外,还需要做登录权限判断和获得这个连接的数据读写权限。
1.1 调大max_connections 连接数
1.2 处理保持连接的不工作线程(减少连接)
如何识别不工作线程?
首先,使用show processlist 查看连接状态。例如
然后,识别空闲线程。
select * from information_schema.innodb_trx;
根据trx_mysql_thread_id 使用kill connection [id] 进行连接的关闭。
但是注意,此方法是业务有损的。
一个客户端处于 sleep 状态时,它的连接被服务端主动断开后,这个客户端并不会马上知道。直到客户端在发起下一个请求的时候,才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。
也有部分应用,不会重新创建连接,会一直使用失效的连接去进行查询。
1.3 减少连接过程的消耗
骚操作,跳过数据库权限验证阶段。
跳过权限验证的方法是:重启数据库,并使用–skip-grant-tables 参数启动。这样,整个 MySQL 会跳过所有的权限验证阶段,包括连接过程和语句执行过程在内。
在 MySQL 8.0 版本里,如果你启用–skip-grant-tables 参数,MySQL 会默认把 --skip-networking 参数打开,表示这时候数据库只能被本地的客户端连接。
问题二 慢查询
性能问题的查询SQL的原因大致如下:
索引没有设计好。
查询语句没有写好。
MySQL选错索引。
2.1 索引没有设计好
可以尝试直接加索引,5.6以上支持Online DDL,比较高效;
备选方案:假设数据库为一主(A)一备(B)。
在备库 B 上执行 set sql_log_bin=off,也就是不写 binlog,然后执行 alter table 语句加上索引;
执行主备切换;
这时候主库是 B,备库是 A。在 A 上执行 set sql_log_bin=off,然后执行 alter table 语句加上索引。
实际不紧急场景,可以考虑使用gh-ost进行Online DDL。
2.2 查询语句没有写好
条件字段函数操作(where 语句中写函数操作)
索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
优化器并不是要放弃使用这个索引,而是会判断遍历主键还是遍历对应普通索引树,而判断的依据就是索引对应字段的大小。存储空间小的索引,可以在同一个数据页内存放下更多数据。
隐式类型转换
会导致其不走索引。
数据类型转换的规则:首先明确的是MySQL中,字符串与数字的比较,会将字符串转成数字。
隐式字符编码转换
utf8mb4 与 utf8 比较时会把 utf8转换成utf8bm4,因为utf8mb4是utf8的超集。
2.3 索引选错
应急方案:加force index
预防方案(检测SQL运行时长):
上线前,在测试环境,把慢查询日志(slow log)打开,并且把 long_query_time 设置成 0,确保每个语句都会被记录入慢查询日志;
在测试表里插入模拟线上的数据,做一遍回归测试;
观察慢查询日志里每类语句的输出,特别留意 Rows_examined 字段是否与预期一致。(我们在前面文章中已经多次用到过 Rows_examined 方法了,相信你已经动手尝试过了。如果还有不明白的,欢迎给我留言,我们一起讨论)。
如果新增的 SQL 语句不多,手动跑一下就可以。而如果是新项目的话,或者是修改了原有项目的 表结构设计,全量回归测试都是必要的。这时候,你需要工具帮你检查所有的 SQL 语句的返回结果。比如,你可以使用开源工具 pt-query-digest(https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html)。
问题三 QPS 突增问题
如果是bug导致QPS过高,可以单独下掉对应功能的数据库用户。
或者call query_rewrite.flush_rewrite_rules() 直接重写"select 1"返回
标签:语句,临时,性能,查询,索引,MySQL,query,连接
来源: https://www.cnblogs.com/wheelchen/p/14490708.html