How to quickly delete a very large table in MySQL

MySQL Quick deletion of very large tables

If you delete a large table directly in MySQL, it may cause MySQL to hang, which may have a negative impact on your business. The prerequisite for deleting a very large table is that the table is an independent table space, so that the deletion is effective.

Table creates a hard link

# du -sh pay_bills.ibd 
175G        pay_bills.ibd
# 创建硬链接
# ln pay_bills.ibd pay_bills.ibd_hdlk

Perform table deletion

In Linux, each stored file will have an Inode Index pointing to the file, and multiple file names can be passed The same Inode Index points to the same storage file.

If the Inode Index referenced by the file name is also referenced by other file names, only the reference between the file name and the Inode Index will be deleted

If the Inode Index referenced by the file name is If it is not referenced by other file names, delete the reference between the file name and the Inode Index and delete the storage file pointed to by the Inode Index.

In fact, only a file reference to pay_bills.ibd is deleted. The reference to the physical file of pay_bills.ibd_hdlk still exists, so no OS-level deletion operation will be performed. The IO fluctuation is not large, which reduces the need for MySQL impact.

mysql> drop table pay_bills;
Query OK, 0 rows affected (3.24 sec)

Execute file deletion

Install the truncate tool

# yum install coreutils -y

Execute the deletion script

#!/bin/bash
TRUNCATE=/usr/bin/truncate
# 从175G开始每次删除2G，最后如果脚本truncate后还剩下部分文件，使用rm删除
for i in `seq 175 -2 1`; do 
  $TRUNCATE -s ${i}G  pay_bills.ibd_hdlk
  sleep 1
done
rm -f pay_bills.ibd_hdlk

MySQL quickly clears large table data

First time for the project When going online, the amount of data created for performance testing is huge, and some of it is unusable, but some of it must be retained. In many cases, it is necessary to systematically clean the data or filter out the useful data before inserting it into the table. middle! Keep the table structure or rebuild the table (export the table structure in the database), and re-execute the SQL statement.

MySQL deletes or clears table data

Five ways to clear table data

1. Truncate– deletes all data and retains the table structure. It cannot Undo and restore

2. delete– is extremely slow to delete row by row and is not suitable for deleting large amounts of data

3. drop– deletes the table, and the data and table structure are deleted together

4. Export the table structure and execute it again;

5. Export all table structures of the database and execute it again to clear all tables (consistent with 4)

Clear table data syntax

truncate table table name; (only the table structure is retained, very fast)

delete from table name;

delete from table name where column name="value ";

DROP `t_product_events` IF EXIST;
CREATE TABLE `t_product_events` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '事件ID',
`level` int(11) NULL DEFAULT NULL,
`product_id` bigint(20) NOT NULL COMMENT '产品类型ID',
`identifier` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '事件名称',
`description` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '事件描述',
`type` int(11) NOT NULL COMMENT '事件类型，0：info（信息）、1：alert（告警）、2：error（故障）',
`ref_id` int(11) UNSIGNED NULL DEFAULT 0 COMMENT '引入模板时有意义',
`original_required` tinyint(2) NOT NULL,
`update_required` tinyint(2) NOT NULL DEFAULT 0 COMMENT '是否是标准功能的必选事件，0：可选，1：必选',
`custom` tinyint(2) NOT NULL COMMENT '0：模板导入，1：自定义',
`method` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '事件对应的方法名称（根据identifier生成）',
`create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
`ref` bigint(20) NULL DEFAULT NULL,
`related` int(11) NULL DEFAULT 0 COMMENT '被预发布或者已发布关联个数',
`copyright` tinyint(2) NULL DEFAULT 0 COMMENT '是否发布过， 1 发布过 0未发布',
`prerelease` tinyint(2) NULL DEFAULT 0 COMMENT '是否预发布过， 1 预发布过 0未预发布',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_product_id`(`product_id`) USING BTREE COMMENT '查询优化'
) ENGINE = InnoDB AUTO_INCREMENT = 497560 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '产品类型事件表' ROW_FORMAT = Compact;

The above is the detailed content of How to quickly delete a very large table in MySQL. For more information, please follow other related articles on the PHP Chinese website!