文章目录

  • 误删数据后除了跑路，还能怎么办？
  • 误删行
  • 误删库 / 表
  • 延迟复制备库
  • 预防误删库 / 表的方法
  • rm 删除数据
  • 为什么还有kill不掉的语句？
  • 收到 kill 以后，线程做什么？
  • 我查这么多数据，会不会把数据库内存打爆？
  • 到底可不可以使用join？
  • join语句怎么优化？
  • Multi-Range Read 优化


误删数据后除了跑路，还能怎么办？

  • 为了找到解决误删数据的更高效的方法，需要先对和 MySQL 相关的误删数据，做下分类：
  • 使用 delete 语句误删数据行；
  • 使用 drop table 或者 truncate table 语句误删数据表；
  • 使用 drop database 语句误删数据库；
  • 使用 rm 命令误删整个 MySQL 实例。

误删行

  • 使用 delete 语句误删了数据行，可以用 Flashback 工具通过闪回把数据恢复回来。
  • Flashback 恢复数据的原理，是修改 binlog 的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保 binlog_format=row 和 binlog_row_image=FULL。具体恢复数据时，对单个事务做如下处理：
  • 对于 insert 语句，对应的 binlog event 类型是 Write_rows event，把它改成 Delete_rows event 即可；
  • 同理，对于 delete 语句，也是将 Delete_rows event 改为 Write_rows event；
  • 而如果是 Update_rows 的话，binlog 里面记录了数据行修改前和修改后的值，对调这两行的位置即可。
  • 如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行。
  • 误删数据的事前预防。以下两个建议：
  • 把 sql_safe_updates 参数设置为 on。这样一来，如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话，这条语句的执行就会报错。
  • 代码上线前，必须经过 SQL 审计。

误删库 / 表

  • 这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份 binlog。

数据恢复流程 -mysqlbinlog 方法

MySQL 事件 PRESERVE mysql 事件如何重跑_mysql

  • 关于这个过程，说明如下几点：
  • 为了加速数据恢复，如果这个临时库上有多个数据库，可以在使用 mysqlbinlog 命令时，加上一个–database 参数，用来指定误删表所在的库。这样，就避免了在恢复数据时还要应用其他库日志的情况。
  • 在应用日志的时候，需要跳过 12 点误操作的那个语句的 binlog：
  • 如果原实例没有使用 GTID 模式，只能在应用到包含 12 点的 binlog 文件的时候，先用–stop-position 参数执行到误操作之前的日志，然后再用–start-position 从误操作之后的日志继续执行；
  • 如果实例使用了 GTID 模式，就方便多了。假设误操作命令的 GTID 是 gtid1，那么只需要执行 set gtid_next=gtid1;begin;commit; 先把这个 GTID 加到临时实例的 GTID 集合，之后按顺序执行 binlog 的时候，就会自动跳过误操作的语句。

延迟复制备库

  • 如果有非常核心的业务，不允许太长的恢复时间，我们可以考虑搭建延迟复制的备库。这个功能是 MySQL 5.6 版本引入的。一般的主备复制结构存在的问题是，如果主库上有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的数据表也都一起被误删了。延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N 秒的延迟。
  • 比如你把 N 设置为 3600，这就代表了如果主库上有数据被误删了，并且在 1 小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行 stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

预防误删库 / 表的方法

  • 第一条建议是，账号分离。这样做的目的是，避免写错命令。比如：
  • 我们只给业务开发同学 DML 权限，而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话，也可以通过开发管理系统得到支持。
  • 即使是 DBA 团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。
  • 第二条建议是，制定操作规范。这样做的目的，是避免写错要删除的表名。比如：
  • 在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。
  • 改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表。

rm 删除数据

  • 对于一个有高可用机制的 MySQL 集群来说，最不怕的就是 rm 删除数据了。只要不是恶意地把整个集群删除，而只是删掉了其中某一个节点的数据的话，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。这时，你要做的就是在这个节点上把数据恢复回来，再接入整个集群。

为什么还有kill不掉的语句？

  • 在 MySQL 中有两个 kill 命令：
  • 一个是 kill query + 线程 id，表示终止这个线程中正在执行的语句；
  • 一个是 kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。

收到 kill 以后，线程做什么？

  • 当用户执行 kill query thread_id_B 时，MySQL 里处理 kill 命令的线程做了两件事：
  • 把 session B 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY)；
  • 给 session B 的执行线程发一个信号。
  • kill 无效的情况：
  • 线程没有执行到判断线程状态的逻辑。还有由于 IO 压力过大，读写 IO 的函数一直无法返回，导致不能及时判断线程的状态。
  • 另一类情况是，终止逻辑耗时较长。
  • MySQL 客户端发送请求后，接收服务端返回结果的方式有两种：
  • 一种是本地缓存，也就是在本地开一片内存，先把结果存起来。如果你用 API 开发，对应的就是 mysql_store_result 方法。
  • 另一种是不缓存，读一个处理一个。如果你用 API 开发，对应的就是 mysql_use_result 方法。

我查这么多数据，会不会把数据库内存打爆？

  • 执行 show engine innodb status ，可以看到“Buffer pool hit rate”字样，显示的就是当前的命中率。
  • InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置成可用物理内存的 60%~80%。
  • 由于 MySQL 采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在 server 端保存完整的结果集。所以，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。而对于 InnoDB 引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。并且，由于 InnoDB 对 LRU 算法做了改进，冷数据的全表扫描，对 Buffer Pool 的影响也能做到可控。

到底可不可以使用join？

  • 两个结论，结论的前提是“可以使用被驱动表的索引”：
  • 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
  • 如果使用 join 语句的话，需要让小表做驱动表。
  • 能不能使用 join 语句？
  • 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
  • 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。
  • 所以你在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。
  • 如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？
  • 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
  • 如果是 Block Nested-Loop Join 算法：
  • 在 join_buffer_size 足够大的时候，是一样的；
  • 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。
  • 所以，这个问题的结论就是，总是应该使用小表做驱动表。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

join语句怎么优化？

回表是指，InnoDB 在普通索引 a 上查到主键 id 的值后，再根据一个个主键 id 的值到主键索引上去查整行数据的过程。

Multi-Range Read 优化

select * from t1 where a>=1 and a<=100;
  • 主键索引是一棵 B+ 树，在这棵树上，每次只能根据一个主键 id 查到一行数据。如果随着 a 的值递增顺序查询的话，id 的值就变成随机的，那么就会出现随机访问，性能相对较差。虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。
  • 因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。
  • 这就是 MRR 优化的设计思路。此时，语句的执行流程变成了这样：
  • 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
  • 将 read_rnd_buffer 中的 id 进行递增排序；
  • 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。
  • MRR 优化后的执行流程

    你知道的越多，你不知道的越多。