MySQL性能提高方案

短连接模式问题

短连接模式：线程连接到数据库后，执行完很少的SQL就直接断开。

MySQL建立连接成本高，包括三次握手以及登录权限校验。因此连接资源非常珍贵，如果某个连接处理SQL比较慢，就会出现以下情况：

• 连接数暴涨，超过max_connections后数据库就会拒绝连接，业务上看数据库处于不可用状态。
• 如果调高max_connections的值，可能导致系统负载进一步扩大，已经连接的线程可能拿不到CPU资源执行SQL业务。

max_connections：最大连接数。超过该阈值后拒绝后续所有线程的连接。

wait_timeout：最大线程空闲时长。当一个线程空闲wait_timeout秒后，就会被MySQL直接踢掉断开连接。

💡异常连接和断开很常见，业务端代码要保证有连接重试。

方案一、占据连接不工作的线程

事务外空闲连接：一些select查询语句，短查询。断开连接后影响不大。

如果连接数过多，可以断开事务外空闲太久的连接，还不够可以断开事务内空闲太久的连接。而如何找到并判断哪些线程是空闲的连接，哪些是事务中的连接？可以通过如下语句查看具体事务状态：

select * from information_schema.innodb_trx\G

trx_mysql_thread_id会显示还处在事务中的线程id，因此剩余的线程就是空闲的事务之外的。在服务端使用下面语句断开连接5，此时连接5的线程发起下次请求后会报错，提示已断开连接。

kill connection 5

方案二、减少连接过程的消耗

场景：短连接数暴增，数据库被多个连接行为打挂了，而实际连接的线程数量还没有达到最大阈值。

方案：可以使用-skip-grant-tables参数重启数据库，这样整个MySQL会跳过所有的权限验证阶段，包括连接过程和语句执行过程。

问题：数据库存在安全问题。

慢查询性能问题

在上线之前，可以通过模拟数据对数据库进行预排查，从而节省下故障复盘时间：

1. 上线前在测试环境，把慢查询日志(slow log)打开，同时把long_query_time设置为0，保证所有SQL语句都会被记录在慢查询日志。
2. 在测试表插入模拟线上的数据，做一遍回归测试。
3. 观察慢查询日志里每类语句的输出，并留意Rows_examined字段(server层统计)扫描行数是否符合预期。
4. 可以使用开源工具pt-query-digest分析所有SQL语句返回的结果。

MySQL引发慢查询的性能问题，大体上包含如下三种可能，下面具体分析以下三种情况以及对应的解决方案。

情况一、索引没有设计好

MySQL支持Online DDL，在这种场景下通过紧急创建索引来解决。在上线或者并发场景下，最高效的做法就是直接执行alter table语句。

假设数据库服务是一主一备，那么DDL方案如下，虽然比较古老但是效率是最高的：

1. 备库上执行set sql_log_bin=off，不写binlog，然后执行alter table语句在备库上添加索引。
2. 执行主备切换
3. 在由主库切换过来的备库上执行set sql_log_bin=off，然后执行alter table，与当前主库保持同步。

更稳妥的方案是采用gh-ost这样的方案。

情况二、语句没写好

SQL语句没写好导致没有使用上B+树索引的快速定位功能。

MySQL5.7提供query_rewrite语句重写功能。具体来说可以提前在MySQL中插入一条新的规则匹配样式，当前客户端的SQL查询语句如果满足规则，则MySQL会自动修改为性能更高的SQL语句。

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

情况三、MySQL选错了索引

方案：直接在原语句或者在查询重写上加force index，指定SQL语句使用哪个索引。

QPS暴增问题

某个SQL语句的QPS暴涨，或者是某个新功能bug，导致整个MySQL压力过大。

最理想的解决方案是让业务把整个“功能”下掉，具体可以有以下几种方法：

• 方案一：全新的业务bug导致。DB运维如果比较规范，此时可以业务方将功能下掉，然后数据库方面直接将相关的白名单去掉。

• 方案二：如果这个功能是单独特定数据库用户导致的。那么可以用管理员账号直接删除该用户，断开现有连接。

• 方案三：如果这个功能和主体功能是部署在一起并且是耦合的。可以通过控制功能对应的SQL语句来降低QPS，比如可以利用上面的SQL重写，把所有压力大的SQL语句全都改写为“select 1”并返回。

上述方案一二的依赖于规范的运维体系。方案三如果业务和功能耦合度比较高，可能会影响误伤其它的业务，所以仅适合应急情况下的止血处理。

磁盘IO瓶颈问题解决方案

• 方案一、设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count两个参数，延长刷盘的时机，减少磁盘IO次数。

  缺点：可能会延长SQL语句的i响应时间

• 方案二、设置sync_binlog改为大于1，分组刷盘提交

  缺点：数据库宕机可能会导致内存中N个事务日志的丢失

• 方案三、设置innodb_flush_log_at_trx_commit为0，将redolog写到文件系统的page cache

  缺点：主机断电丢失redolog