22讲MySQL有哪些“饮鸩止渴”提高性能的方法

短连接风暴

   是指数据库有很多链接之后只执行了几个语句就断开的客户端,然后我们知道数据库客户端和数据库每次连接不仅需要tcp的三次握手,而且还有mysql的鉴权操作都要占用很多服务器的资源。话虽如此但是如果连接的不多的话其实这点资源无所谓的。

   但是如果遇到连接高峰期,就会有问题,max_connections参数,用来控制一个MySQL实例同时存在的连接数的上限,超过这个值,系统就会拒绝接下来的连接请求,并报错提示“Too many connections”。对于被拒绝连接的请求来说,从业务角度看就是数据库不可用。

   那么我们有没有啥可以解决的办法呢?还是有的。

第一种方法:先处理掉那些占着连接但是不工作的线程。

     将现阶段没有工作的线程杀掉就可以释放一定的数据库资源,这是一种我感觉非常有效的方法,但是如果我们误杀了一个有用的线程呢?

       看下面这个例子。

   

 我们通过show processlist 看到了这个表单,然后我们发现session a 作为一个插入操作的事件,但是此时却在睡觉,我们如果只通过show processlist 就很可能将session a杀掉,然后session a就会回滚这样的话对于业务端,就会觉得数据没有更新成功,这样会产生非常大的误会,那我们该如何解决这个问题呢?你可以查information_schema库的innodb_trx表

  这个表就很清楚的显示trx_mysql_thread_id=4 说明线程4还在工作,那么我们只需要将使用命令kill connection + id将线程5杀掉即可,但是需要注意的是对于你处理业务的同事,可能对于他们来说,他们数据库的连接被断开他们是不知道的,数据库主动断开连接是有问题的。

这个客户端并不会马上知道。直到客户端在发起下一个请求的时候,才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

从数据库端主动断开连接可能是有损的,尤其是有的应用端收到这个错误后,不重新连接,而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去,“MySQL一直没恢复”。

第二种方法:减少连接过程的消耗。

慢查询性能问题

在MySQL中,会引发性能问题的慢查询,大体有以下三种可能:

  1. 索引没有设计好;

  2. SQL语句没写好;

  3. MySQL选错了索引。

接下来,我们就具体分析一下这三种可能,以及对应的解决方案。

导致慢查询的第一种可能是,索引没有设计好。

这种场景一般就是通过紧急创建索引来解决。MySQL 5.6版本以后,创建索引都支持Online DDL了,对于那种高峰期数据库已经被这个语句打挂了的情况,最高效的做法就是直接执行alter table 语句。

比较理想的是能够在备库先执行。假设你现在的服务是一主一备,主库A、备库B,这个方案的大致流程是这样的:

  1. 在备库B上执行 set sql_log_bin=off,也就是不写binlog,然后执行alter table 语句加上索引;

  2. 执行主备切换;

  3. 这时候主库是B,备库是A。在A上执行 set sql_log_bin=off,然后执行alter table 语句加上索引。

这是一个“古老”的DDL方案。平时在做变更的时候,你应该考虑类似gh-ost这样的方案,更加稳妥。但是在需要紧急处理时,上面这个方案的效率是最高的。

导致慢查询的第二种可能是,语句没写好。

比如,我们犯了在第18篇文章《为什么这些SQL语句逻辑相同,性能却差异巨大?》中提到的那些错误,导致语句没有使用上索引。

这时,我们可以通过改写SQL语句来处理。MySQL 5.7提供了query_rewrite功能,可以把输入的一种语句改写成另外一种模式。

比如,语句被错误地写成了 select * from t where id + 1 = 10000,你可以通过下面的方式,增加一个语句改写规则。

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();

这里,call query_rewrite.flush_rewrite_rules()这个存储过程,是让插入的新规则生效,也就是我们说的“查询重写”。你可以用图4中的方法来确认改写规则是否生效。

图4 查询重写效果

导致慢查询的第三种可能,就是碰上了我们在第10篇文章《MySQL为什么有时候会选错索引?》中提到的情况,MySQL选错了索引。

这时候,应急方案就是给这个语句加上force index。

同样地,使用查询重写功能,给原来的语句加上force index,也可以解决这个问题。

上面我和你讨论的由慢查询导致性能问题的三种可能情况,实际上出现最多的是前两种,即:索引没设计好和语句没写好。而这两种情况,恰恰是完全可以避免的。比如,通过下面这个过程,我们就可以预先发现问题。

  1. 上线前,在测试环境,把慢查询日志(slow log)打开,并且把long_query_time设置成0,确保每个语句都会被记录入慢查询日志;

  2. 在测试表里插入模拟线上的数据,做一遍回归测试;

  3. 观察慢查询日志里每类语句的输出,特别留意Rows_examined字段是否与预期一致。(我们在前面文章中已经多次用到过Rows_examined方法了,相信你已经动手尝试过了。如果还有不明白的,欢迎给我留言,我们一起讨论)。

不要吝啬这段花在上线前的“额外”时间,因为这会帮你省下很多故障复盘的时间。

如果新增的SQL语句不多,手动跑一下就可以。而如果是新项目的话,或者是修改了原有项目的 表结构设计,全量回归测试都是必要的。这时候,你需要工具帮你检查所有的SQL语句的返回结果。比如,你可以使用开源工具pt-query-digest(pt-query-digest — Percona Toolkit Documentation)。

QPS突增问题

有时候由于业务突然出现高峰,或者应用程序bug,导致某个语句的QPS突然暴涨,也可能导致MySQL压力过大,影响服务。

我之前碰到过一类情况,是由一个新功能的bug导致的。当然,最理想的情况是让业务把这个功能下掉,服务自然就会恢复。

而下掉一个功能,如果从数据库端处理的话,对应于不同的背景,有不同的方法可用。我这里再和你展开说明一下。

  1. 一种是由全新业务的bug导致的。假设你的DB运维是比较规范的,也就是说白名单是一个个加的。这种情况下,如果你能够确定业务方会下掉这个功能,只是时间上没那么快,那么就可以从数据库端直接把白名单去掉。

  2. 如果这个新功能使用的是单独的数据库用户,可以用管理员账号把这个用户删掉,然后断开现有连接。这样,这个新功能的连接不成功,由它引发的QPS就会变成0。

  3. 如果这个新增的功能跟主体功能是部署在一起的,那么我们只能通过处理语句来限制。这时,我们可以使用上面提到的查询重写功能,把压力最大的SQL语句直接重写成"select 1"返回。

当然,这个操作的风险很高,需要你特别细致。它可能存在两个副作用:

  1. 如果别的功能里面也用到了这个SQL语句模板,会有误伤;

  2. 很多业务并不是靠这一个语句就能完成逻辑的,所以如果单独把这一个语句以select 1的结果返回的话,可能会导致后面的业务逻辑一起失败。

所以,方案3是用于止血的,跟前面提到的去掉权限验证一样,应该是你所有选项里优先级最低的一个方案。

同时你会发现,其实方案1和2都要依赖于规范的运维体系:虚拟化、白名单机制、业务账号分离。由此可见,更多的准备,往往意味着更稳定的系统。

 

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/1591.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

Web自动化——前端基础知识(二)

1. Web前端开发三要素 web前端开发三要素 什么是HTMl? Html是超文本标记语言,是用来描述网页的一种标记语言HTML是一种标签规则的形式将内容呈现在浏览器中可以以任意编辑器创建,其文件扩展名为.html或.htm保存即可 什么是CSS?…

ElasticSearch-第五天

目录 es中脑裂问题 脑裂定义 脑裂过程分析 解决方案 数据建模 前言 nested object 父子关系数据建模 父子关系 设置 Mapping 索引父文档 索引子文档 Parent / Child 所支持的查询 使用 has_child 查询 使用 has_parent 查询 使用 parent_id 查询 访问子文档 …

学习 Python 之 Pygame 开发魂斗罗(一)

学习 Python 之 Pygame 开发魂斗罗(一)Pygame回忆Pygame1. 使用pygame创建窗口2. 设置窗口背景颜色3. 获取窗口中的事件4. 在窗口中展示图片(1). pygame中的直角坐标系(2). 展示图片(3). 给部分区域设置颜色5. 在窗口中显示文字6. 播放音乐7. 图片翻转与…

人脸活体检测系统(Python+YOLOv5深度学习模型+清新界面)

摘要:人脸活体检测系统利用视觉方法检测人脸活体对象,区分常见虚假人脸,以便后续人脸识别,提供系统界面记录活体与虚假人脸检测结果。本文详细介绍基于YOLOv5深度学习技术的人脸活体检测系统,在介绍算法原理的同时&…

【C++】用手搓的红黑树手搓set和map

目录 一、set/map的底层结构 1、set/map的源码 2、利用模板区分set/map 3、利用仿函数控制比较大小 二、set/map的迭代器(红黑树的迭代器) 1、红黑树的begin、end迭代器 2、红黑树迭代器的operator 3、红黑树迭代器的operator-- 三、set的const…

人工智能大模型之ChatGPT原理解析

前言 近几个月ChatGPT爆火出圈,一路狂飙;它功能十分强大,不仅能回答各种各样的问题,还可以信写作,给程序找bug…我经过一段时间的深度使用后,十分汗颜,"智障对话"体验相比&#xff0c…

Spring-Data-Redis 和 Redisson TLS/SSL 连接

先决条件已经部署好redis tls环境。如未部署好,可参考:Redis 6.0 Docker容器使用SSL/TLS已知redis tls环境使用的证书:其中:ca.crt :服务器证书ca.key:服务器私钥redis.crt:客户端证书redis.key:客户端私钥证书处理生成证书p12文件…

Linux环境C语言开发基础

C语言是一门面向过程的计算机编程语言,与C、C#、Java等面向对象编程语言有所不同。C语言的设计目标是提供一种能以简易的方式编译、处理低级存储器、仅产生少量的机器码以及不需要任何运行环境支持便能运行的编程语言。C语言诞生于美国的贝尔实验室,由丹…

信创办公–基于WPS的PPT最佳实践系列(表格和图标常用动画)

信创办公–基于WPS的PPT最佳实践系列(表格和图标常用动画) 目录应用背景操作步骤图表常用动画效果:擦除效果表格常用动画效果:轮子效果应用背景 文不如表,表不如图。在平时用ppt做总结时,我们会经常用到图…

手撕数据结构与算法——树(三指针描述一棵树)

🏆作者主页:king&南星 🎄专栏链接:数据结构 🏅文章目录🌱树一、🌲概念与定义二、🌳定义与预备三、🌴创建结点函数四、🍀查找五、🍁插入六、&a…

SpringBoot接口 - 如何生成接口文档之Swagger技术栈

SpringBoot开发Restful接口,有什么API规范吗?如何快速生成API文档呢?Swagger 是一个用于生成、描述和调用 RESTful 接口的 Web 服务。通俗的来讲,Swagger 就是将项目中所有(想要暴露的)接口展现在页面上&am…

我的创作纪念日——一年的时间可以改变很多

机缘 不知不觉来到CSDN已经创作一年了。打心底讲,对于在CSDN开始坚持创作的原因我用一句话来概括最合适不过了——“无心插柳柳成荫” 为什么这么说呢? 这要从我的一篇博客说起——《输入命令Javac报错详解》: 那也是我第一次接触到Java这…

PostMan工具的使用

PostMan工具的使用 1 PostMan简介 代码编写完后,我们要想测试,只需要打开浏览器直接输入地址发送请求即可。发送的是GET请求可以直接使用浏览器,但是如果要发送的是POST请求呢? 如果要求发送的是post请求,我们就得准备页面在页…

基于OpenCV的传统视觉应用 -- OpenCV图像处理 图像模糊处理 图像锐化处理

图像处理 图像处理是用计算机对图像进行分析,以获取所需结果的过程,又称为影像处理。图像处理一般是指数字图像的处理。数字图像是用工业相机、摄像机、扫描仪等设备经过拍摄得到的一个大的二维数组,该数组的元素称为像素,其值称…

C++造轮子飙车现场之无锁、有锁环形队列实现

先看带锁的实现。 带锁版本 circular_queue.h // 头文件防卫 #ifndef CIRCULAR_QUEUE_H #define CIRCULAR_QUEUE_H#include <mutex> // 互斥量 #include <condition_variable> // 条件变量template <typename T> class CircularQueue { public:// 构造函数…

公司测试员用例写得乱七八糟,测试总监制定了这份《测试用例编写规范》

统一测试用例编写的规范&#xff0c;为测试设计人员提供测试用例编写的指导&#xff0c;提高编写的测试用例的可读性&#xff0c;可执行性、合理性。为测试执行人员更好执行测试&#xff0c;提高测试效率&#xff0c;最终提高公司整个产品的质量。 一、范围 适用于集成测试用…

vue3 项目搭建教程(基于create-vue,vite,Vite + Vue)

vue3 项目搭建教程&#xff08;基于create-vue&#xff0c;vite&#xff0c;Vite Vue&#xff09; 目录 一、搭建vue3 项目前提条件 二、通过create-vue搭建vue3 项目 三、搭建一个 Vite 项目 四、构建一个 Vite Vue 项目 五、打开Vue 项目管理器 六、Vite Vue 项目目…

云开发--实现发送邮件+短信+链接跳转小程序功能

目录 1、小程序实现发送邮件 准备一个qq邮箱&#xff0c;并启动SMTP服务 确定小程序云开发环境&#xff0c;并新建云函数 2、小程序实现发送短信 确定应用 确定签名 确定模板 编写云函数-发送短信 3、链接跳转小程序 H5 配置 生成 URL Link 学习记录&#xff1a; …

【洛谷刷题】蓝桥杯专题突破-深度优先搜索-dfs(4)

目录 写在前面&#xff1a; 题目&#xff1a;P1149 [NOIP2008 提高组] 火柴棒等式 - 洛谷 | 计算机科学教育新生态 (luogu.com.cn) 题目描述&#xff1a; 输入格式&#xff1a; 输出格式&#xff1a; 输入样例&#xff1a; 输出样例&#xff1a; 解题思路&#xff1a; …

Java进阶2 排序查找与Lambda、正则表达式

排序查找与Lambda、正则表达式● 导图一、 基础算法1.排序1.1 冒泡排序1.2 选择排序2. 查找2.1 基础查找2.2 二分查找二、Lambda表达式1&#xff09;初识Lambda2&#xff09;函数式编程3&#xff09;.Lambda表达式的标准格式4&#xff09;Lambda的注意事项5&#xff09;Lambda表…