MySQL 删除100G大表中的绝大部分数据
MySQL 的存储引擎为 Innodb 的数据表，如何快速删除2T的大表
OK，这里就说了。假设，你有一个表erp,如果你直接进行下面的命令
drop table erp
这个时候所有的mysql的相关进程都会停止，直到drop结束，mysql才会恢复执行。
出现这个情况的原因就是因为，在drop table的时候，innodb维护了一个全局锁，drop完毕锁就释放了。
这意味着，如果在白天，访问量非常大的时候，如果你在不做任何处理措施的情况下，执行了删大表的命令，整个mysql就挂在那了，
在删表期间，QPS会严重下滑，然后产品经理就来找你喝茶了。所以才有了漫画中的一幕，你可以在晚上十二点，夜深人静的时候再删。
当然，有的人不服，可能会说："你可以写一个删除表的存储过程，在晚上没啥访问量的时候运行一次就行。"
我内心一惊，细想一下，只能说："大家还是别抬杠了，还是听我说一下业内通用做法。"
一个假设
先说明一下，在这里有一个前提，mysql开启了独立表空间，MySQL5.6.7之后默认开启。
也就是在my.cnf中，有这么一条配置(这些是属于mysql优化的知识，后期给大家介绍)
innodb_file_per_table = 1
查看表空间状态，用下面的命令
MariaDB [tbl_mimvp_proxy]> show variables like '%per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec)
如果innodb_file_per_table的value值为OFF，代表采用的是共享表空间。
如果innodb_file_per_table的value值为ON ，代表采用的是独立表空间。（默认设置）
于是，大家要问我，独立表空间和共享表空间的区别？
共享表空间：某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1(此文件，可以扩展成多个)。注意，在这种方式下，运维超级不方便。你看，所有数据都在一个文件里，要对单表维护，十分不方便。另外，你在做delete操作的时候，文件内会留下很多间隙，ibdata1文件不会自动收缩。换句话说，使用共享表空间来存储数据，会遭遇drop table之后，空间无法释放的问题。
独立表空间：每一个表都以独立方式来部署，每个表都有一个.frm表描述文件，还有一个.ibd文件。
# ll /var/lib/mysql/tbl_mimvp_proxy/ total 4794484 -rw-rw---- 1 mysql mysql 4809 Feb 7 2018 country.frm -rw-rw---- 1 mysql mysql 98304 Feb 7 2018 country.ibd -rw-rw---- 1 mysql mysql 61 Feb 28 2015 db.opt -rw-rw---- 1 mysql mysql 2211 Feb 7 2018 province.frm -rw-rw---- 1 mysql mysql 98304 Feb 7 2018 province.ibd
.frm文件：保存了每个表的元数据，包括表结构的定义等，该文件与数据库引擎无关。
.ibd文件：保存了每个表的数据和索引的文件。
注意，在这种方式下，每个表都有自已独立的表空间，这样运维起来方便，可以实现单表在不同数据库之间的移动。
另外，在执行drop table操作的时候，是可以自动回收表空间。
在执行delete操作后，可以通过alter table TableName engine=innodb可以整理碎片，回收部分表空间。
ps：my.cnf中的datadir就是用来设置数据存储目录
好了，上面巴拉巴拉了一大堆，我只想说一个事情:
在绝大部分情况下，运维一定会为mysql选择独立表空间的存储方式，因为采用独立表空间的方式，从性能优化和运维难易角度来说，实在强太多。
所以，我在一开始所提到的前提，mysql需要开启独立表空间。这个假设，百分九十的情况下是成立的。
如果真的遇到了，你们公司的mysql采用的是共享表空间的情况，请你和你们家的运维谈谈心，问问为啥用共享表空间。
正确姿势
假设，我们有datadir = /data/mysql/，另外，我们有有一个database，名为mytest。
在数据库mytest中，有一个表，名为erp，执行下列命令
mysql> system ls -l /data/mysql/mytest/
得到下面的输出(我过滤了一下)
-rw-r----- 1 mysql mysql 9023 8 18 05:21 erp.frm -rw-r----- 1 mysql mysql 2356792000512 8 18 05:21 erp.ibd
frm和ibd的作用，上面介绍过了。现在就是erp.ibd文件太大，所以删除卡住了。
如何解决这个问题呢？
这里需要利用了linux中硬链接的知识，来进行快速删除。
软链接其实大家可以类比理解为windows中的快捷方式，就不多介绍了，主要介绍一下硬链接。
至于这个硬链接，我简单说一下，不想贴一大堆话过来，看起来太累。
就是对于真正存储的文件来说，有一个
然后呢有一个文件名指向上面的node Index
那么，所谓的硬链接，就是不止一个文件名指向node Index，有好几个文件名指向node Index。
假设，这会又有一个文件名指向上面的node Index，即
这个时候，你做了删除文件名(1)的操作，linux系统检测到，还有一个文件名(2)指向node Index，因此并不会真正的把文件删了，而是把步骤(2)的引用给删了，这步操作非常快，毕竟只是删除引用。于是图就变成了这样
接下来，你再做删除文件名(2)的操作，linux系统检测到，没有其他文件名指向该node Index，就会删除真正的存储文件，这步操作，是删真正的文件，所以比较慢。
OK，我们用的就是上面的原理。
先给erp.ibd建立一个硬链接，利用ln命令
mysql> system ln /data/mysql/mytest/erp.ibd /data/mysql/mytest/erp.ibd.hdlk
此时，文件目录如下所示
-rw-r----- 1 mysql mysql 9023 8 18 05:21 erp.frm -rw-r----- 2 mysql mysql 2356792000512 8 18 05:21 erp.ibd -rw-r----- 2 mysql mysql 2356792000512 8 18 05:21 erp.ibd.hdlk
你会发现，多了一个erp.ibd.hdlk文件，且erp.ibd和erp.ibd.hdlk的inode均为2。
此时，你执行drop table操作
mysql> drop table erp; Query OK, 0 rows affected (0.99 sec)
你会发现，不到1秒就删除了。因为，此时有两个文件名称(erp.ibd和erp.ibd.hdlk)，同时指向一个inode.这个时候，执行删除操作，只是把引用给删了，所以非常快。
那么，这时的删除，已经把table从mysql中删除。但是磁盘空间，还没释放，因为还剩一个文件erp.ibd.hdlk。
如何正确的删除erp.ibd.hdlk呢？
如果你没啥经验，一定会回答我，用rm命令来删。这里需要说明的是，在生产环境，直接用rm命令来删大文件，会造成磁盘IO开销飙升，CPU负载过高，是会影响其他程序运行的。
那么，这种时候，就是应该用truncate命令来删，truncate命令在coreutils工具集中。
详情，大家可以去百度一下，有人对rm和truncate命令，专程测试过，truncate命令对磁盘IO，CPU负载几乎无影响。
删除脚本如下
TRUNCATE=/usr/local/bin/truncate
for i in `seq 2194 -10 10 `;
do
sleep 2
$TRUNCATE -s ${i}G /data/mysql/mytest/erp.ibd.hdlk
done
rm -rf /data/mysql/mytest/erp.ibd.hdlk ;
从2194G开始，每次缩减10G，停2秒，继续，直到文件只剩10G，最后使用rm命令删除剩余的部分。
其他情况
这里指的是，如果数据库是部署在windows上怎么办。这个问题，我来回答，其实不够专业。
因为我出道以来，还没碰到过，生产环境上，mysql是部在windows上的。
假设真的碰到了，windows下有一个工具叫mklink，是在windows下创建硬链接锁用，应该能完成类似功能
上面所讲的内容，中小型公司的研发比较容易遇到。因为中小型公司没有专业的DBA，研发啥都得干，全栈的技能
一、MySQL 快速删除全部数据（删除表）
DROP table tbl_name;
TRUNCATE table tbl_name;
二、MySQL 快速删除绝大部分数据（删除绝大部分数据）
两个删除策略：
1、慢的方法，是写脚本定时任务带where的delete语句 慢慢删除范围定小一点，表锁定的时间很短，客户端基本无感；
2、快的方法，是找个业务空的时候，新建表 —> 往新表中插入需保留的部分数据 —> 删除老表
快的策略详细步骤：
MySQL 删除绝大部分的多行数据时，innodb会超出lock table size的限制，最小化的减少锁表的时间的方案如下
1、选择不需要删除的数据，并把它们存在一张相同结构的空新表里
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
2、重命名原始表，并给新表命名为原始表的原始表名
RENAME TABLE t TO t_old, t_copy TO t;
3、删掉原始表
DROP TABLE t_old;
总结，当时删除大表的一部分数据时可以创建新表 —> 拷贝保留的数据插入 —> 重命名老表、新表 —> 删除旧表
Order of Deletion
If the DELETE statement includes an ORDER BY clause, rows are deleted in the order specified by the clause. This is useful primarily in conjunction with LIMIT. For example, the following statement finds rows matching the WHERE clause, sorts them by timestamp_column, and deletes the first (oldest) one:
DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp_column LIMIT 1;
ORDER BY also helps to delete rows in an order required to avoid referential integrity violations.
InnoDB Tables
If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:
1、Select the rows not to be deleted into an empty table that has the same structure as the original table:
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
2、Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:
RENAME TABLE t TO t_old, t_copy TO t;
3、Drop the original table:
DROP TABLE t_old;
No other sessions can access the tables involved while RENAME TABLE executes, so the rename operation is not subject to concurrency problems. See Section 13.1.36, “RENAME TABLE Syntax”.
三、转存数据并删除
转存数据有两个方式：insert ignore into 和 replace into
1、insert ignore into
INSERT IGNORE INTO tbl_mimvp_proxy SELECT * FROM tbl_mimvp_proxy_good where check_success_count <= 5 and check_dtime < 20160201000000;
2、replace into
REPLACE INTO tbl_mimvp_proxy SELECT * FROM tbl_mimvp_proxy_good where check_success_count <= 5 and check_dtime < 20160201000000;
MySQL 插入操作跳过(ignore)、覆盖(replace into)、更新(on duplicate key)
1、insert ignore into
当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。
所以使用ignore请确保语句本身没有问题，否则也会被忽略掉。
例如：
INSERT IGNORE INTO books (name) VALUES ('MySQL Manual')
2、on duplicate key update
当primary或者unique重复时，则执行update语句，如update后为无用语句，如id=id，则同1功能相同，但错误不会被忽略掉。
例如，为了实现name重复的数据插入不报错，可使用一下语句：
INSERT INTO books (name) VALUES ('MySQL Manual') ON duplicate KEY UPDATE id = id
3、insert … select … where not exist
根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。
例如：
INSERT INTO books (name) SELECT 'MySQL Manual' FROM dual WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1)
4、replace into
如果存在primary or unique相同的记录，则先删除掉，再插入新记录。（需要Delete、Insert权限）
注意若原记录存在(a1,b1,c1)，新记录为(a1,b2)，则replace后c字段为null
REPLACE INTO books SELECT 1, 'MySQL Manual' FROM books
MySQL 在 select 查询时追加(添加)一个字段并指定值
在 mysql 的查询结果中，我们需要追加一个字段来实现某些特定的功能，这时我们可以用到以下语法来实现
格式：值 as 字段
例如： "mimvp.com" as domain
完整示例：
select *, "mimvp.com" as domain from tbl_company;
则会增加一列为 domain 列名，其值全部为 "mimvp.com"
参考推荐：
MySQL DELETE Syntax （MySQL官网）
版权所有: 本文系米扑博客原创、转载、摘录，或修订后发表，最后更新于 2019-02-24 05:01:23
侵权处理: 本个人博客，不盈利，若侵犯了您的作品权，请联系博主删除，莫恶意，索钱财，感谢！