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

“饮鸩止渴”！不惜一切提升数据库性能

author：陈镇坤27

创建时间：2021年11月24日20:40:35

编辑时间：2021年12月2日13:11:15、2021年12月7日00:51:55

转载请注明出处

————————————————————————————————

短连接风暴

问：在想要短期提高数据库性能的情况下，为什么说可以对短连接进行一些处理？

答：连接过程中，权限收集和校验是比较耗性能的。

问：在高频短连接访问数据库情况下，想要短期业务无损地提高服务器性能，怎么做？

答：将max_connections设置为合理的参数，因为超过这个连接数的连接会被拒绝，但过大的话，连接了的线程又拿不到CPU资源去执行。其次，即使是空闲的线程只要保持连接也会占用一个connection位置，所以kill掉这些空闲的连接也是一个思路。

此外，可以减少连接过程的消耗——权限校验（危险，推荐内网）。

问：你说kill掉空闲的线程，那我直接kill掉show processlist中的sleep的线程可以吗？为什么。

答：不可以。因为这样子可能是有损的，例如一个执行了多种操作但还没提交的事务，此时会回滚。

问：应该怎么干掉一些空闲的线程？

答：先执行show processlist查看哪些线程处于sleep中，再看information_schema的innodb_trx，根据trx_mysql_thread_id判断哪些sleep的线程处于事务当中，干掉事务外的sleep线程。

kill connection thread_id

问：从服务端干掉线程，需要注意什么？

答：客户端下一次请求时才会收到connection断开的报错，有的客户端会重新发起连接，而有的客户端会一直报错。

因此若DBA要干掉线程，应该通知到业务研发团队。

问：你说可以减少连接过程的权限验证，会产生什么问题呢？具体的操作步骤又是怎么样的？

答：没有权限验证，更容易被攻击，尤其是暴露在外网的数据库。

操作步骤：重启数据库的同时加入–skip-grant-tables 参数，之后，整个MySQL会跳过所有的权限验证（连接、查询缓存、分析、执行）

PS：8.0版本在使用该参数时，会默认打开–skip-networking，保证数据库只能被本地客户端连接。

慢查询性能

问：除了通过处理短连接和权限验证来提高性能之外，还可以重查询的哪些方面入手？

答：索引没设计好，sql没写好，MySQL选错了索引。

方式一：紧急创建索引。如果是表数据较少，直接在主库执行即可，如果表数据量较大，此时应该分三步走

1）从库set sql_log_bin=off ，关闭binlog，随后执行alter 语句；

2）主备切换；

3）新的从库set sql_log_bin=off ，关闭binlog，随后执行alter语句。

之所以这么做，是因为如果设置的MM结构，那么备库的DDL也会传回给主库，可能对主库造成很大影响（大事务更新，加MDL写锁，阻塞读取（第27章有简单提到，作者在本章节这里没有解释是因为MM结构缘故才采取的关闭binlog操作，让人一直不解））

方式二：使用5.7提供的**query_rewrite（查询重写）**功能。

PS：该插件在MySQL的share包中自带，但需要执行安装方能使用

[root@localhost share]# mysql -uroot -p < install_rewriter.sql

query_rewrite可以把一个语句改写成另外一个语句。

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

如上，则执行的语句被pattern匹配中时，会被替代为replacement中的语句执行。

判断有无生效，在执行pattern的语句后show warning即可得到相关信息。
有关query_rewrite的描述，可以看我MySQL专栏的另外一篇博客——MySQL的query_rewrite中文手册

问：SQL维护这一块，平时你们是怎么做的？

答：迭代的时候，上线前，测试环境中启动慢查询配置，模拟线上操作，观察日志中的每类语句输出，留意扫描行数是否与预期一致。

如果是新项目，最好还是进行全量回归测试，使用开源工具pt-query-digest进行测试。

QPS突增

问：如果应用模块功能出现bug，如果要去从数据库层面卸掉这个功能，怎么做？

答：可以用查询重写功能，但需要考虑到查询重写的模板“误杀”和即使重写，重写的结果是否会导致后面的业务逻辑失败的问题。

此外，还有虚拟化、白名单机制、业务账号分离等选项（非专业DBA，个人暂不了解）。