Mysql 系列 | 性能优化 – 紧急临时处理

在实际开发过程中，业务高峰期常遇到 Mysql 响应变慢。为了不影响业务，要在短时间内临时提升性能。

短链接

原因分析

• 短链接是连接数据库后，执行很少的 SQL 后就断开，下次需要的时候再重新连接。这种情况很容易出现连接突然暴涨的情况

• Mysql 建立连接时除了网络连接三次握手外，还要权限判断和获取权限，成本较高。当响应变慢时，连接数就会暴涨

• Mysql 实例存在连接数上限，由参数 max_connections 设定。超出后会拒绝接下来的连接请求，导致业务瘫痪

解决方案

• 调高 max_connections 参数值，但是过多的连接进来，会耗费大量资源来校验权限，导致线程没有 CPU 资源处理请求

• 处理掉不工作的线程

  • 在 showprocesslist 的结果里，踢掉显示为 sleep 的线程，可能存在没提交的事务，是有损的。

  • 可以查 information_schema 库的 innodb_trx 表。优先断开事务外空闲太久的连接，再考虑事务内空闲连接

  • 服务端断开连接用 kill connection + id。主动断开连接，当应用端收到错误信息后不重新连接，而是直接用错误句柄重试查询，会导致业务代码没有处理好的地方直接报错。

• 减少连接过程的消耗，让数据库跳过权限验证阶段。

  • 重启数据库，并加上 –skip-grant-tables 参数，连接过程和语句执行过程都会跳过验证。但是风险极高，尤其是外网可访问数据库时。

  • Mysql8.0 中，启用 用–skip-grant-tables参数，会默认打开–skip-grant-tables参数，让数据库只能被本地客户端连接。

慢查询

原因分析

• 可能出现慢查询的情况，

  • 索引设计不合理

  • SQL 语句不合理

  • Mysql 选错了索引

解决方案

• 针对索引不合理

  • 紧急创建索引

  • 先在备库执行 set sql_log_bin = off，不再记 binlog，然后alter table加索引。第二步主备切换。第三步在切换后的备库上执行第一步的操作

• 针对语句没写好

  • 改写 SQL 语句，用 query_rewrite功能，把输入的语句改写成另一种模式

• 针对选错索引

  • 给语句加上force index，也可以使用上面说到的查询重写功能

QPS 突增

原因分析

• 业务突然出现高峰或者程序 bug，导致数据库压力过大

解决方案

• 要根据具体的业务代码决定

  • 如果是新功能，可以先把新功能下线，把相关连接全部关掉。如果用了单独的数据库用户，则把用户删掉。

  • 如果不能下线，则通过处理语句来限制。使用查询重写功能，直接重写成 select 1 返回。当然前提是不会影响到重要业务逻辑，一旦导致误伤会比较严重，优先级较低

预防措施

• 上线前测试环境打开 slow log，设置long_query_time=0，确保每条语句都记录再慢日志

• 测试表中模拟线上数据，回归测试

• 检查测试环境慢日志中 Rows_examined 字段是否符合预期

• 规范数据库使用规则，虚拟化、白名单机制、作业账号分离

项目中数据出现问题，一般都会比较严重。

提前做好数据审计，避免不必要的尴尬。

原文地址：http://www.cnblogs.com/rendd/p/16810812.html