乐趣区

关于mysql:Mysql-实战-五-实践4

jiezi

十二、MySQL 中进步性能的办法

短连贯风暴

连贯到数据库后，执行很少的 SQL 语句就断开，下次须要的时候再重连。

  1. 先解决掉那些占着连贯然而不工作的线程。
    能够通过 kill connection 被动踢掉不工作的线程。这个行为跟当时设置wait_timeout 的成果是一样的。设置 wait_timeout 参数示意的是，一个线程闲暇 wait_timeout 这么多秒之后，就会被 MySQL 间接断开连接。
  2. 缩小连贯过程的耗费。
    有的业务代码会在短时间内先大量申请数据库连贯做备用，如果当初数据库确认是被连贯行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。

慢查问性能问题

  1. 索引没有设计好
    这种场景个别就是通过紧急创立索引来解决。比拟现实的是可能在备库先执行。假如你当初的服务是一主一备，主库 A、备库 B，这个计划的大抵流程是这样的：

    1. 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，而后执行 alter table 语句加上索引；
    2. 执行主备切换；
    3. 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，而后执行 alter table 语句加上索引。
** 或者思考 gh-ost。**
  1. SQL 语句没写好
    SQL 语句没写，导致语句没有应用上索引。
  2. MySQL 选错了索引
    应急计划就是给这个语句加上 force index。

如何防止上述问题：

  1. 上线前，在测试环境，把慢查问日志（slow log）关上，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查问日志；
  2. 在测试表里插入模仿线上的数据，做一遍回归测试；
  3. 察看慢查问日志里每类语句的输入，特地注意 Rows_examined 字段是否与预期统一。（咱们在后面文章中曾经屡次用到过 Rows_examined 办法了，置信你曾经入手尝试过了。如果还有不明确的，欢送给我留言，咱们一起探讨）。

QPS 突增问题

有时候因为业务忽然呈现顶峰，或者应用程序 bug，导致某个语句的 QPS 忽然暴涨，也可能导致 MySQL 压力过大，影响服务。最现实的状况是让业务把这个性能下掉，服务天然就会复原。

  1. 一种是由全新业务的 bug 导致的。假如你的 DB 运维是比拟标准的，也就是说白名单是一个个加的。这种状况下，如果你可能确定业务方会下掉这个性能，只是工夫上没那么快，那么就能够从数据库端间接把白名单去掉。
  2. 如果这个新性能应用的是独自的数据库用户，能够用管理员账号把这个用户删掉，而后断开现有连贯。这样，这个新性能的连贯不胜利，由它引发的 QPS 就会变成 0。
  3. 如果这个新增的性能跟主体性能是部署在一起的，那么咱们只能通过解决语句来限度。这时，咱们能够应用下面提到的查问重写性能，把压力最大的 SQL 语句间接重写成 ”select 1″ 返回。

十三、MySQL 是怎么保证数据不丢的？

binlog 的写入机制

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开的，因而不管这个事务多大，也要确保一次性写入。这就波及到了 binlog cache 的保留问题。

零碎给 binlog cache 调配了一片内存，每个线程一个，参数 binlog_cache_size 用于管制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的残缺事务写入到 binlog 中，并清空 binlog cache。


每个线程有本人 binlog cache，然而共用同一份 binlog 文件。
图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据长久化到磁盘，所以速度比拟快。图中的 fsync，才是将数据长久化到磁盘的操作。个别状况下，咱们认为 fsync 才占磁盘的 IOPS。

write 和 fsync 的机会，是由参数 sync_binlog 管制的：

  1. sync_binlog=0 的时候，示意每次提交事务都只 write，不 fsync；
  2. sync_binlog=1 的时候，示意每次提交事务都会执行 fsync；
  3. sync_binlog=N(N>1) 的时候，示意每次提交事务都 write，但累积 N 个事务后才 fsync。4.

redo log 的写入机制

事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 的。


redo log 存在三种状态，这三种状态别离是：

  1. 存在 redo log buffer 中，物理上是在 MySQL 过程内存中，就是图中的红色局部；
  2. 写到磁盘 (write)，然而没有长久化（fsync)，物理上是在文件系统的 page cache 外面，也就是图中的黄色局部；
  3. 长久化到磁盘，对应的是 hard disk，也就是图中的绿色局部。

日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，然而长久化到磁盘的速度就慢多了。为了管制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

  1. 设置为 0 的时候，示意每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
  2. 设置为 1 的时候，示意每次事务提交时都将 redo log 间接长久化到磁盘；
  3. 设置为 2 的时候，示意每次事务提交时都只是把 redo log 写到 page cache。

InnoDB 有一个后盾线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，而后调用 fsync 长久化到磁盘。

留神，事务执行两头过程的 redo log 也是间接写在 redo log buffer 中的，这些 redo log 也会被后盾线程一起长久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能曾经长久化到磁盘的。

实际上，除了后盾线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。

  1. 一种是，redo log buffer 占用的空间行将达到 innodb_log_buffer_size 一半的时候，后盾线程会被动写盘。留神，因为这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
  2. 另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 长久化到磁盘。假如一个事务 A 执行到一半，曾经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么依照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全副长久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起长久化到磁盘。

两阶段提交的时候，时序上 redo log 先 prepare，再写 binlog，最初再把 redo log commit。如果把 innodb_flush_log_at_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。

组提交（group commit）机制

日志逻辑序列号（log sequence number，LSN）。LSN 是枯燥递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redolog，LSN 的值就会加上 length。

组提交就是 write 完之后，fsync 晚一点调用，一次提交很多事务。

在并发更新场景下，第一个事务写完 redo log buffer 当前，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的成果就越好。

MySQL 呈现了 IO 性能瓶颈怎么办？

针对这个问题，能够思考以下三种办法：

  1. 设置 binlog_group_commit_sync_delay 和binlog_group_commit_sync_no_delay_count 参数，缩小 binlog 的写盘次数。这个办法是基于“额定的成心期待”来实现的，因而可能会减少语句的响应工夫，但没有失落数据的危险。
  2. 将 sync_binlog 设置为大于 1 的值（比拟常见是 100~1000）。这样做的危险是，主机掉电时会丢 binlog 日志。
  3. 将 innodb_flush_log_at_trx_commit 设置为 2。这样做的危险是，主机掉电的时候会丢数据。

不倡议把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0，示意 redo log 只保留在内存中，这样的话 MySQL 自身异样重启也会丢数据，危险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异样重启时就不会丢数据了，相比之下危险会更小。

十四、MySQL 的主备统一

退出移动版