读书笔记：Mysql实战45讲（22-35讲）
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⽅案。平常做变更的时候，考虑类似于这样的⽅案，更加稳妥，但是着需要紧急处理时候，这个⽅案效率最⾼
语句没有写好：
在18章⽂章中提到那些错误，导致语句没有使⽤上索引。可以通过修改SQL语句来处理，MySQL 5.7提供了⼀个query_rewrite，可以把输⼊语句改成另⼀种模式
可以使⽤这些⽅法确认改写规则是否⽣效：批量更新sql语句
如果MySQL选错了索引，同样的，使⽤查询重写功能，可以给这个语句加上 force index。解决这个问题
在上线前怎么预先发现问题？
1、上线前，在测试环境把慢查询⽇志slow log打开，并且把long_query_time设置为0，确保每个语句都记录在⽇志⾥⾯
2、在测试表中插⼊模拟线上的数据，做⼀遍回归测试
3、观察慢查询⽇志每类语句数据，特别留意Rows_examined字段是否与预期⼀致
慢查询的⽇志记录myql.slow_log表中，格式如下：
可以看到，不管是表还是⽂件，都具体记录了：是那条语句导致慢查询（sql_text），该慢查询语句的查询时间（query_time），锁表时间（Lock_time），以及扫描过的⾏数（rows_examined）等信息。
这个时候，需要⼯具帮你检查所有的SQL语句返回结果，⽐如使⽤开源⼯具 pt-query-digest
QPS突增问题：
场景：有的时候由于业务突然出现⾼峰，或者程序bug，导致某个语句QPS突然暴涨，也可能导致MySQL压⼒过⼤，影响服务
如果是由⼀个新功能的bug导致。最理想情况让业务把这个功能下掉，服务⾃然就恢复，⽽下掉⼀个功能，如果从数据库端处理的话，对应于不同的背景有不同⽅法可⽤。具体如下：
这个操作风险很⾼，需要你特别细致，可能存在两个副作⽤：
所以⽅案3⽤于⽌⾎的，跟前⾯提到去掉权限验证⼀样，所有选项⾥优先级最低⼀个⽅案。⽅案1和2依赖于规范的运维体系：虚拟化、⽩名单机制、业务账号分离。
MYSQL是怎么保证数据不丢的?
在之前介绍了WAL机制，提到的结论只要redo log 和binlog保证持久化到磁盘，就能保证MySQL异常重启后数据恢复
binlog的写⼊机制：
事务执⾏过程，先把⽇志写到 binlog cache，事务提交的时候，再把binlog cache 写到binlog ⽂件中。
⼀个事务的binlog是不能拆开的，因此不论这个事务多⼤，也要确保⼀次性写⼊。这就涉及到了binlog cache保存问题
系统给binlog cache分配⼀个内存，每个线程⼀个，参数binlog_cache_size⽤于控制单个线程内binlog cache所占存的⼤⼩，如果超过这个参数⼤⼩就要暂存磁盘，事务提交的时候，执⾏器把binlog cache⾥的完整事务写⼊到binlog中，并清空binlog cache。
write：指的把⽇志写⼊⽂件系统的page cache，并没有持久化到磁盘所以锁读很快
fsync：才是将数据持久化到磁盘。⼀般这个情况，我们认为fsync才占磁盘IOPS
在出现IO瓶颈的场景中，将sync_binlog设置成⼀个⽐较⼤的值，可以提升性能。考虑到丢失⽇志可控性，⼀般不建议设为0，⽐较场景设置为100~1000中某⼀个数值。设置为N：风险是 如果主机发送异常重启，会丢失最近N个事务的bin log⽇志
扩展： 当设置成sync_binlog=0的时候，每次commit都只是write到page cache 并不会fsync，但是做
试验时binlog还是有原因？
我们看到的binlog的记录是从page cache读的，page cache是操作系统⽂件上的
redo log的写⼊机制：
事务执⾏过程中，⽣成的redo log 是要先写到redo log buffer的
redo log 存储的三种状态：
这三种状态分别是：
为了控制redo log写⼊策略，InnoDB提供了innodb_flsh_log_at_trx_commit参数：
InnoDB有⼀个后台线程，每隔1s，就会把redo log buffer中的⽇志，调⽤write写到⽂件系统的page cache，然后调⽤fsync持久化到磁盘。
实际上，除了后台线程每秒⼀次的轮询操作，还有两个场景会让⼀个没有提及事务的redo log写⼊磁盘中。
在介绍两阶段提交的时候说过，时序上redo log 先prepare，再写binlog，最后再把redo log commit，如果
把innodb_flush_log_trx_commit设置为1，那么redo log 在prepare阶段就要持久化⼀次，因为有个崩溃恢复逻辑就是要依赖于prepare 的redo log，再加上binlog来恢复。每秒⼀次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB就认为redo log在commit的时候不需要fsync了，只会write到⽂件系统的page cache中就够了
通常我们说的MySQL的双1配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit都设置成1。也就是说，⼀个事务完整提交前，需要等待两次刷盘，⼀次是redo log(prepare阶段)，⼀次是binlog。
当这样的话，意味着我从MySQL看到的TPS每秒两万的话，每秒就会写四万次磁盘，但是我从⼯具测试出来，磁盘能⼒也就是两万左右，怎么实现两万的TPS？
redo log的⼀个个写⼊点，每次写⼊长度为length的redo log, LSN(⽇志逻辑序列号  单调递增)的值就会加上length，LSN也会写到InnoDB的数据页，来确保数据页不会被多次执⾏重复的redo log。如图，三个并发事务（trx1,trx2,trx3）在prepare阶段，都写完redo log buffer，持久化到磁盘的过程
所以，⼀次组提交⾥⾯，组员越多，节约磁盘的IOPS的效果越好。但如果只有单线程压测，那就只能⼀个事务对应⼀次持久化操作了，在并发更新场景下，第⼀个事务写完redo log buffer以后，接下来这个fsync越晚调⽤，组员可能越多，节约IOPS的效果越好。
根据这个原理的MySQL⼀个有趣的优化： 拖时间
在第⼆讲中，把写binlog当成⼀个动作。但实际上，写binlog分成两步的：
1、先把binlog从binlog cache中写⼊磁盘上的binlog⽂件
2、调⽤fsync持久化
MySQL为了组提交的效果更好，把redo log 做fsync的时间拖到了步骤1之后。变成这样：
这个时候binlog fsync到磁盘也可以租提交，不过通常情况下第三步执⾏会很快，所以binlog的write和fsync间的间隔时间段，导致能集合到⼀起持久化的binlog⽐较少，因此binlog的组提交的效果通常不如redo log效果那么好

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。