一、如何快速删除大表

     官方策略：1、 选择不要删除的行到与原始表具有相同结构的空表中： INSERT INTO t_copy SELECT * FROM t WHERE ... ;

                     2、 使用RENAME TABLE以原子移动原始表的方式进行，并重新命名拷贝到原来的名称： RENAME TABLE t TO t_old, t_copy TO t;

                     3、 删除原始表： DROP TABLE t_old;

        第3点小编补充下，如果单表数据文件SIZE达到T级以上，直接DROP会对服务器造成非常大的冲击力，小编目前还不知道会给磁盘IO造成多大的冲击力，反正想像一下，应该很恐怖，服务器宕机都有可能。

       大表的删除过程中，innodb存储引擎会维护一个全局独占锁，由于大表删除时间长。使得其他操作夯住，造成线上的业务的不可用，所以我们要缩短大表的删除时间。

DROP TABLE的内部原理： 

1.清除buffer pool中的flush list中与drop table有关的页面 

2.删除数据文件.ibd

       解决方法： 

       关于flush list的页面删除我们无法对源码进行修改来加速，所以只能从缩短删除数据文件.ibd的时间下手，我们利用系统的硬链接的特性来删除大表的.ibd文件。

        民间策略：

                     1、sudo ln /data/mysql/testdb/example_table.ibd    /data/mysql/testdb/example_table.ibd.hdlk

                     2、sudo ls -ln  /data/mysql/testdb    这回会发现索引为2了，有两个文件。

                     3、这时候在执行Drop  example_table   就会非常快了。

                     4、然后我们在策略的慢慢删除example_table.ibd.hdlk。

二、表删除问题汇总

(1)、mysql如何删除表

(2)、删除大表如何优化、解决

三、删除表原理与优化

删除表原理上分为2部分：

<1>buffer pool页面清除过程。

在删除表的时候，Innodb 会将文件在buffer pool中对应的页面清除。对于删除表的页面清除，只需要将页面从flash队列中删除即可，而不需要去做flush操作，减小对系统的冲击。

问题1：如果buffer pool很大，或者是在buffer pool中有很多需要被flush的页面，那么此时遍历扫描页面时就会占用比较长的时间，导致其他事务在用到相应buffer pool实例时被阻塞，从而影响整个数据库性能。

优化：涉及源码，优化困难

<2>删除ibd磁盘文件的过程。

问题1：表文件过大，直接删除会瞬时占用大量IO，造成IO阻塞

优化：使用硬链

原理：一个磁盘上的文件，可以由多个文件系统的文件引用，这多个文件的完全相同的，都指向同一个磁盘上的文件，当我们删除任何一个文件的时候，都不会影响真实的文件，只是会将其引用数据减1，只有当被引用数目变为1的时候，再次删除文件，才会真正被删除。删除时，这两种情况的区别很明显，一个是在减少被引用数目，一个是真正做IO来删除它

操作：

ln /data/mydata/my3306/testdb/table1.ibd /data/mydata/my3306/testdb/table1.ibd.hdlk

ls -lh /data/mydata/my3306/testdb 查看文件引用数(应该为2)

问题2：做完硬链，真正的大文件删除问题，直接rm 删除，会造成IO瞬时高峰

优化：使用工具，多次少量的删除

原理：利用系统文件的truncate，脚本工具为slowrm

四、slowrm

注：此工具作者为 “贾春生”，此处只分享~

3.1、需求

缓解大表删除带来的IO瞬间压力

3.2、slowrm功能

<1>将大文件按照指定速度进行删除，降低IO瞬间压力

3.3.1、背景描述

先简要介绍下slowrm工具开发的背景。

我们曾在《linux_mysql_DROP_TABLE操作步骤》中提到，对于大表的删除，应当先建立硬链，drop table后，再删除表数据文件。

对于大表的数据文件，可能会达到10G，也可以是100G级别，甚至更大。在linux下，这样的大文件在使用rm时，无疑会导致IO资源被强行占用，表现为硬盘的io_util基本上是100%左右，会对其它IO操作造成阻塞。更可怕的是，rm单个文件的过程是个原子过程，无法使用kill或kill -9来杀死rm进程，只能乖乖的等待它结束。

如果是在繁忙的线上服务所在的机器上做这样的删除操作，很可能会对线上服务产生影响。因此需要有一个平滑删除大文件的方案。而这样的方案最终是与数据库无关的，而是一个通用方案。

3.3.2、设计思路

对于删除大文件的操作，采用每次将文件截断一点，分多次截断，最终直至文件大小被截断为0。

如下面的过程示例：

block_size = 1024576bytes;

while file_size >=block_size

begin

ftruncate (file, new_size);

(其中(old_size -new_size = block_size)

sleep 0.05 seconds;

end

ftruncate(file, 0);

因为既然rm一个文件是不可中断的，那么我们就将删除过程打散，分多次删除一个，每次删除一点。幸好现在的linux提供了支持该思路的API，truncate/ftruncate函数，可以对文件截断至任意长度()。经过测试，发现每次截断一点，分多次截断，最终直至文件大小被截断为0，这样删除大文件需要更多的时间，但是对磁盘IO的影响却大大减小了。因此采用该思路是可行的。

3.4、slowrm使用

3.4.1、选项及参数

该工具的使用主要涉及两个参数：

-b #size

每次需要截断掉的文件大小，单位为字节，参数值需要是整数，默认值4096 * 256Bytes( = 1MB);

-s #seconds

每两次截断操作之间的时间间隔，单位为秒，参数值可以为小数。这两个参数用以控制删除速度，及删除过程对磁盘IO的压力。默认值0.1 second

-h, --help

查看帮助信息。

直接输入slowrm，或输入slowrm –h或slowrm --help可以查看帮助。

3.4.2、删除速度

实际上，可以看出，由#size * #seconds = #ioBPS Bytes/s，

即可计算得出每秒该操作消耗的IO吞吐量(字节数)，也即大约每秒文件所减小的字节数。

<div font-size:15px;line-height:24px;white-space:normal;widows:1;"="" style="word-wrap: break-word; color: rgb(102, 102, 102); font-size: 13.3333px; line-height: 26px; white-space: normal; margin: 0px; font-family: Helvetica;">

可以看到默认删除速度是:4096 * 256Bytes / 0.1second= 10MB/s，被删除的文件每秒钟可以减少10MB。

用户可以通过上述两个参数来控制删除的速度以及对磁盘IO的压力。

3.5、slowrm与rm对比

3.6、注意

3.6.1、删除文件类型

目前，该工具只支持删除具体常规文件，无法对目录进行递归删除，但可以支持对多个文件进行删除，如slowrm *.logmysql.err.2014*

也无法对软链接，文件等进行删除。

3.6.2、硬链的删除

如果slowrm要删除的文件的硬链接数目大于1，那么就会直接删除该硬链；

如果待删除的文件硬链数目等于1，那么会按照平滑删除的方式，逐步截断该文件。

3.7、测试使用

制造大文件

开始删除

大小情况

压力情况

3.8、日常使用

1、开启一个screen

screen -S slowrm_test

2、开始删除文件

slowrm file

或者指定速度，正常默认即可(10MB/s)

  上述部分内容来自：      https://blog.csdn.net/u011277123/article/details/73497418

五、分库分表策略

    分库分表种类：1、垂直拆、2、水平拆  3、水平拆之后在垂直拆  。拆的越多，负载越小，问题就是增大中间件的处理规则或者是前端的sql拼写方式，进行跨库，跨表查询，增加开发难度。

    分库分表的原则：1、能不分就不分  （硬件设备、网络、软件版本、参数调优已经没有优化空间了，才考虑分表）

                              2、表设计不合理，把经常访问的表中，经常不访问的字段并且是text等类型，就必须把这种字段拆分出去。

                              3、日志表，评论表有瞬间无限增长的可能，必须要拆分，新浪微博被（鹿同学的一个表白，从DBA的角度来考虑，评论表的压力肯定扛不住的撒）

                              4、站在业务、开发角度进行分表、从业务角度解耦到实例，库表进行解耦，防止一个实例上的一个项目问题，拖垮所有项目。（这个也不是歧视哪个项目，要把它分出去，而是你这个项目影响大啊，都影响到其他项目了~，你得理解哈哈~）

六、MySQL性能关注不到的漏点

1、单表数据文件大小限制到多少为合理。热点表必须得限制。胆小的百万级，胆大的千万级。高手的就过亿。哈哈 ~。

      其原理就是，数据越大并且索引不够优化，稍微有个漏网的SQL,就会发生全表扫描的噩梦。   这个是成正比的，数据越大，索引越差，性能就急聚的下降。

     有的互联网公司DBA比较胆小、小编觉得还是从分析和预期的角度来弄规范。根据数据的热点程度和未来发展速度。所以一般就是百万方。千万级就可以了。我觉得规范不能一棒子打死，有的表非常热，那肯定是百万级就必须分表了，有的表非常冷，表关系和结构非常复杂，只有写没有读，那分它不是给前端开发找罪受，就是给自己找罪受。哈哈~

2、Delete 语句并非是直接删除数据库里的数据，而是相当于把它标记删除，有点像windows上的回收站。日志不丢、随时可以回滚。存在的问题就是，会占用大量空间。

      innodb 里面有个 purge 线程会进行定期清理，但是innodb 也不会释放这些空间给操作系统。这就会在数据文件中产生数据空洞，并且一直存在，越滚越大，直接降低某个表的访问性能。

      可以通过  show  table status 查看表的碎片化和空洞，需要查看两个参数  数据的 Data_length 和Data_free 的比例。如果Data_free所占比例非常大，那这个表必须要优化了。

      就好比举个例子,我从来不在windows 的C盘装软件，和存储东西。但是发现C盘空间越来越小，开机速度越来越慢。有两个原因，软件卸载不干净，它所产生的数据文件依然存在、软件运行日志还保留在C盘里面。 windows 上就好办了，简单粗暴，找个文件分析工具，找出TopN大文件。看着像第三方软件跑出来的日志和数据文件。都扔到回收站。或者改个名，重启下系统，看看有木有问题，没有问题直接shitf+delete。

    MySQL用ALTER TABLE   XXX   ENGINE=InnoDB  或 OPTIMIZE TABLE  XXX来重建表空间，就会解决表空洞和碎片的问题。

3、合理设计表结构。

     这块不啰嗦了，减少SQL请求，一次请求命中索引，快速返回数据。ok 这就是合理设计表结构的目标。

七、MySQL组复制

组复制是对传统复制、半同步复制的补充和改进，在半同步的基础上也是以插件的方式实现了强一致性的特性，16年12月12日由官方发布一个开源的插件的方式来实现组复制，利用组复制就可以实现类似于PXC的高可用、强一致性的mysql集群了，要想用组复制，只要去官方下载5.7.17 以后版本即可。组复制简写(MGR)

之前博客有写过，MGR和PXC的一些新版本的对，MGR是新星，虽然有些毛刺。稳定性不如PXC，坑也有一些。

但是未来发展角度，小编认为MGR还是首选。

为什么首选，在分布式数据一致性上，在之前一直是MySQL的硬伤，所以在若干年前大数据hadoop 火起来的原因，其不是Hadoop本身HDFS系统的原因，是因为它是靠分布式数据一致性的灵魂组件“Zookeeper”. 别看Hadoop，Hbase 外表光鲜靓丽。实则没有zookeeper 它们撒也不是。

是因为官方 对MySQL也是跟Zookeeper 实现差不多的同类的Paxos算法为基础，做出来一个MGR的插件来实现类似于Hadoop高可用的服务。

一个复制组由若干个节点(数据库实例)组成，组内各个节点维护各自的数据副本(Share Nothing)，通过一致性协议实现原子消息和全局有序消息，来实现组内实例数据的一致。

由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N / 2 + 1）决议并通过，才能得以提交。

确实比半同步进化很多，比如我有4台节点的mysql实例，必须三个节点都应答通过了，这个事务才能提交。

它所具备的特性！

• 数据一致性保障：确保集群中大部分节点收到日志

• 多节点写入支持：多写模式下支持集群中的所有节点都可以写入

• Fault Tolerance: 确保系统发生故障（包括脑裂）依然可用，双写对系统无影响

组复制的有哪些预备条件？

a、只支持innodb存储引擎

b、每张表都需要有主键

c、只支持ipv4网络环境

d、要求高网络带宽(通常是千兆内网)和低网络延迟

以下的参数在mysql数据库实例上必须要配置

1、--log-bin=bin-log     #记录mysql的binlog

2、--log-slave-update    #记录slave上进行apply relay-log时的binlog

3、--binlog-format=row  #binlog的格式为行模式

4、--gtid-mode=on      #开启gtid模式

5、--master-info-repository=TABLE   #将master-info信息记录到mysql表当中

6、--relay-log-info-repository=TABLE  #将relay-log信息记录到mysql表当中

7、--transaction-write-set-extraction=XXHASH64  #每个事务收集的wirte set和encode使用xxhash64哈希算法

8、开启多线程复制

--slave-parallel-workers=N  #N为具体的复制线程个数

--slave-preserve-commit-order=1 #slave上apply relay-log时事务顺序提交

--slave-parallel-type=LOGICAL_CLOCK  #使用多线程复制

3.MySQL 组复制实现了基于复制协议的多主更新。

1)复制组由多个 server成员构成，并且组中的每个 server 成员可以独立地执行事务。但所有读写（RW）事务只有在冲突检测成功后才会提交。只读（RO）事务不需要在冲突检测，可以立即提交。

2)换句话说，对于任何 RW 事务，提交操作并不是由始发 server 单向决定的，而是由组来决定是否提交。准确地说，在始发 server 上，当事务准备好提交时，该 server 会广播写入值（已改变的行）和对应的写入集（已更新的行的唯一标识符）。然后会为该事务建立一个全局的顺序。最终，这意味着所有 server 成员以相同的顺序接收同一组事务。因此，所有 server 成员以相同的顺序应用相同的更改，以确保组内一致。

3)组复制使您能够根据在一组 server 中复制系统的状态来创建具有冗余的容错系统。因此，只要它不是全部或多数 server 发生故障，即使有一些 server 故障，系统仍然可用，最多只是性能和可伸缩性降低，但它仍然可用。server 故障是孤立并且独立的。它们由组成员服务来监控，组成员服务依赖于分布式故障检测系统，其能够在任何 server 自愿地或由于意外停止而离开组时发出信号。

4)他们是由一个分布式恢复程序来确保当有 server 加入组时，它们会自动更新组信息到最新。并且多主更新确保了即使在单个服务器故障的情况下也不会阻止更新，不必进行 server故障转移。因此，MySQL 组复制保证数据库服务持续可用。

5)值得注意的一点是，尽管数据库服务可用，但当有一个 server 崩溃时，连接到它的客户端必须定向或故障转移到不同的 server。

这不是组复制要解决的问题。连接器，负载均衡器，路由器或其他形式的中间件更适合处理这个问题。

总之，MySQL 组复制提供了高可用性，高弹性，可靠的 MySQL 服务。

MGR的限制

1、仅支持InnoDB表，并且每张表一定要有一个主键，用于做write set的冲突检测；

2、必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主与write set

3、COMMIT可能会导致失败，类似于快照事务隔离级别的失败场景

4、目前一个MGR集群最多支持9个节点

5、不支持外键于save point特性，无法做全局间的约束检测与部分部分回滚

6、二进制日志不支持binlog event checksum

备注：以上部分文和图片来自于互联网和官方wiki。不小心关闭浏览器懒得在找，如有侵权内容，请速度联系小编QQ：1071235258.  备注：（因MySQL 本身就开源，此篇文章只做知识分享，无任何商业用途！）

打赏

微信扫一扫，打赏作者吧～