22、MySQL有哪些 饮鸩止渴 提高性能的办法?
场景: 业务高峰期,生产环境的MySQL压力太大,没法正常响应,需要短期内、临时性地提高一些性能。但,如果是无损的方案的话,肯定不需要等到这个时候才上场
短连接风暴:
正常的短连接模式就是连接到数据库,执行很少的SQL语句就断开,下次需要的时候再建立连接的过程,成本是很高的。除了正常的网络连接三次握手外,还要登陆权限判断和获取这个连接的数据读写权限。但是短连接存在一个风险,就是一旦数据库处理得慢一些,连接数就会暴涨。max_connections参数,用户控制一个MYSQL实例存在连接数的上线。
一个比较自然想法就是调高这个值,但是有风险,改的太大让很多连接可以进来,那么系统的负载就会进一步加大,大量的资源消耗在权限认证上,结果可能适得其反,已经连接的线程拿不到CPU资源去执行业务的SQL请求。
第一个办法:先处理那些占着连接但是不工作的线程
max_connectons的计算,是只要连着就要占着一个计数位置。对于不需要保持连接,直接kill掉(这个和设置wait_timeout效果一样,标识一个线程空闲wait_timeout这么多秒以后,就会被MySQL直接断开连接)
可以从show processlist结果看,sleep线程的两种状态,而要看具体状态,查看information_schema库的innodb_trx表
可以优先断开事务外空闲太久的连接;如果这样还不够,可以考虑断开事务内空闲太久的连接。从服务端断开连接使用的是kill connection +id 命令。一个客户端处于sleep状态,它的连接被服务端断开后,这个客户端并不会马上知道。知道客户端发起下一个连接收到被断开的消息。
第二个办法:减少连接过程的消耗
有些业务代码会在段时间内先大量申请数据连接做备用,如果现在数据确认是被连接行为打挂了,那么一种可能做法,就是让数据跳过权限认证阶段
跳过权限认证的办法就是:重启数据库,并使用-
参数启动。整个MySQL会跳过所有权限认证阶段,包括连接过程和语句执行过程在内。(优先极高,尤其你的库外网可以访问的话)
在MySQL 8.0版本,如果启用-skip-grant-table参数,MySQL默认把--skip-networking参数打开,表示这个时候数据库只能被本地客户端连接。
慢查询性能问题:大体有三种可能
1、索引没有设计好
2、SQL语句没写好
3、MySQL选错了索引
索引没有设计好:
这种场景一般都是通过紧急创建所以呢来解决。MySQL5.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语句机上索引
这个是一个古老的DDL方案。平常做变更的时候,考虑类似于gh_ost这样的方案,更加稳妥,但是着需要紧急处理时候,这个方案效率最高
语句没有写好:
在18章文章中提到那些错误,导致语句没有使用上索引。可以通过修改SQL语句来处理,MySQL 5.7提供了一个query_rewrite,可以把输入语句改成另一种模式
可以使用这些方法确认改写规则是否生效:
如果MySQL选错了索引,同样的,使用查询重写功能,可以给这个语句加上 force index。解决这个问题
在上线前怎么预先发现问题?
1、上线前,在测试环境把慢查询日志slow log打开,