第二十二章 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 中，哪些情况会引发性能问题的慢查询 ?

1. 索引没有设计好
2. SQL 语句没写好
3. 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​

  ​ 中的语句执行

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

  ​pt-query-digest​

  ​ 进行测试。

QPS 突增问题

• 可以用​

  ​查询重写​

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