第二十二章 MySQL有哪些“饮鸩止渴”提高性能的方法？

lewis 1年前 (2024-04-18) 阅读数 14 #技术

第二十二章 MySQL有哪些“饮鸩止渴”提高性能的方法？短连接风暴

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

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

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

将max_connections 设置为合理的参数，因为超过这个连接数的连接会被拒绝，但过大的话，连接了的线程又拿不到 CPU 资源去执行其次，即使是空闲的线程只要保持连接也会占用一个connection 位置，所以 kill 掉这些空闲的连接也是一个思路此外，启用 –skip-grant-tables 参数，可以让整个 MySQL 跳过所有的权限验证阶段，这样也可以减少连接过程的消耗但是，极力不推荐在外网环境下这么做

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

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

应该怎么干掉一些空闲的线程 ?

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

kill

从服务端干掉线程，需要注意什么 ?

客户端下一次请求时才会收到connection 断开的报错，有的客户端会重新发起连接，而有的客户端会一直报错因此若 DBA 要干掉线程，应该通知到业务研发团队

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

没有权限验证，更容易被攻击，尤其是暴露在外网的数据库跳过权限验证的方法：重启数据库，并使用–skip-grant-tables 参数启动这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内注意：8.0 版本在使用该参数时，MySQL 会默认把-skip-networking 参数打开，保证这时候数据库只能被本地的客户端连接慢查询性能问题

在 MySQL 中，哪些情况会引发性能问题的慢查询 ?

索引没有设计好SQL 语句没写好MySQL 选错了索引

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

在备库 B 上执行set sql_log_bin=off，也就是不写binlog，然后执行 alter table 语句加上索引执行主备切换这时候主库是 B，备库是 A。在 A 上执行set sql_log_bin=off，然后执行alter table 语句加上索引

方式二：使用 5.7 提供的 query_rewrite (查询重写) 功能

该插件在 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 中的语句执行

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

迭代的时候，上线前，测试环境中启动慢查询配置，模拟线上操作，观察日志中的每类语句输出，留意扫描行数是否与预期一致如果是新项目，最好还是进行全量回归测试，使用开源工具pt-query-digest 进行测试。QPS 突增问题

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

可以用查询重写功能，但需要考虑到查询重写的模板“误杀”和即使重写，重写的结果是否会导致后面的业务逻辑失败的问题此外，还有虚拟化、白名单机制、业务账号分离等选项