讲师中心 微信公众号
首页
文章
后端开发 web前端 数据库 开发工具 php框架 常见问题 人工智能 Java 系统教程 电脑教程 硬件教程 手机教程 软件教程 游戏教程 自媒体 新闻
专题
后端开发 web前端 数据库 开发工具 php框架 人工智能 Java 系统教程 电脑教程 硬件教程 手机教程 软件教程 游戏教程 新闻
AI工具
AI聊天问答 Agent智能体 AI文本写作 AI绘画作图 AI设计工具 AI视频创作 AI音频制作 AI办公学习 AI编程开发 AI提示词
学习
大前端 后端开发 数据库 移动端 运维开发 计算机基础
编程手册
大前端 后端开发 数据库 移动端 运维开发 计算机基础
下载
js特效 网站源码 工具下载 类库下载 网站素材 学习资源 插件扩展 手机游戏
最近更新
当前位置：首页 > 数据库 > mysql教程 >

正文

0

0

mysql删除数据时为什么不使用delete

醉折花枝作酒筹

醉折花枝作酒筹

发布时间：2021-07-21 09:26:20

|

2831人浏览过

|

来源于CSDN

转载

有些表的数据量增长很快，对应sql扫描了很多无效数据，导致sql慢了下来，通过确认之后，这些大表都是一些流水、记录、日志类型数据，只需要保留1到3个月，此时需要对表做数据清理实现瘦身。

mysql删除数据时为什么不使用delete

这篇文章我会从InnoDB存储空间分布，delete对性能的影响，以及优化建议方面解释为什么不建议delete删除数据。

InnoDB存储架构

从这张图可以看到，InnoDB存储结构主要包括两部分：逻辑存储结构和物理存储结构。

逻辑上是由表空间tablespace —> 段segment或者inode —> 区Extent ——>数据页Page构成，Innodb逻辑管理单位是segment，空间分配的最小单位是extent，每个segment都会从表空间FREE_PAGE中分配32个page，当这32个page不够用时，会按照以下原则进行扩展：如果当前小于1个extent，则扩展到1个extent；当表空间小于32MB时，每次扩展一个extent；表空间大于32MB，每次扩展4个extent。

物理上主要由系统用户数据文件，日志文件组成，数据文件主要存储MySQL字典数据和用户数据，日志文件记录的是data page的变更记录，用于MySQL Crash时的恢复。

Innodb表空间

InnoDB存储包括三类表空间：系统表空间，用户表空间，Undo表空间。

系统表空间： 主要存储MySQL内部的数据字典数据，如information_schema下的数据。

用户表空间： 当开启innodb_file_per_table=1时，数据表从系统表空间独立出来存储在以table_name.ibd命令的数据文件中，结构信息存储在table_name.frm文件中。

Undo表空间： 存储Undo信息，如快照一致读和flashback都是利用undo信息。

从MySQL 8.0开始允许用户自定义表空间，具体语法如下：

CREATE TABLESPACE tablespace_name
    ADD DATAFILE 'file_name'               #数据文件名
    USE LOGFILE GROUP logfile_group        #自定义日志文件组，一般每组2个logfile。
    [EXTENT_SIZE [=] extent_size]          #区大小
    [INITIAL_SIZE [=] initial_size]        #初始化大小 
    [AUTOEXTEND_SIZE [=] autoextend_size]  #自动扩宽尺寸
    [MAX_SIZE [=] max_size]                #单个文件最大size，最大是32G。
    [NODEGROUP [=] nodegroup_id]           #节点组
    [WAIT]
    [COMMENT [=] comment_text]
    ENGINE [=] engine_name

这样的好处是可以做到数据的冷热分离，分别用HDD和SSD来存储，既能实现数据的高效访问，又能节约成本，比如可以添加两块500G硬盘，经过创建卷组vg，划分逻辑卷lv，创建数据目录并mount相应的lv，假设划分的两个目录分别是/hot_data 和 /cold_data。

这样就可以将核心的业务表如用户表，订单表存储在高性能SSD盘上，一些日志，流水表存储在普通的HDD上，主要的操作步骤如下：

#创建热数据表空间
create tablespace tbs_data_hot add datafile '/hot_data/tbs_data_hot01.dbf' max_size 20G;
#创建核心业务表存储在热数据表空间
create table booking(id bigint not null primary key auto_increment, …… ) tablespace tbs_data_hot;
#创建冷数据表空间
create tablespace tbs_data_cold add datafile '/hot_data/tbs_data_cold01.dbf' max_size 20G;
#创建日志，流水，备份类的表存储在冷数据表空间
create table payment_log(id bigint not null primary key auto_increment, …… ) tablespace tbs_data_cold;
#可以移动表到另一个表空间
alter table payment_log tablespace tbs_data_hot;

Inndob存储分布

创建空表查看空间变化

mysql> create table user(id bigint not null primary key auto_increment, 
    -> name varchar(20) not null default '' comment '姓名', 
    -> age tinyint not null default 0 comment 'age', 
    -> gender char(1) not null default 'M'  comment '性别',
    -> phone varchar(16) not null default '' comment '手机号',
    -> create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    -> update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
    -> ) engine = InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户信息表';
Query OK, 0 rows affected (0.26 sec)
# ls -lh user1.ibd 
-rw-r----- 1 mysql mysql 96K Nov  6 12:48 user.ibd

设置参数innodb_file_per_table=1时，创建表时会自动创建一个segment，同时分配一个extent，包含32个data page的来存储数据，这样创建的空表默认大小就是96KB，extent使用完之后会申请64个连接页，这样对于一些小表，或者undo segment，可以在开始时申请较少的空间，节省磁盘容量的开销。

# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 6:      #总共分配的页数
Freshly Allocated Page: 2     #可用的数据页
Insert Buffer Bitmap: 1       #插入缓冲页
File Space Header: 1          #文件空间头
B-tree Node: 1                #数据页
File Segment inode: 1         #文件端inonde，如果是在ibdata1.ibd上会有多个inode。

插入数据后的空间变化

mysql> DELIMITER $$
mysql> CREATE PROCEDURE insert_user_data(num INTEGER) 
    -> BEGIN
    ->     DECLARE v_i int unsigned DEFAULT 0;
    -> set autocommit= 0;
    -> WHILE v_i < num DO
    ->    insert into user(`name`, age, gender, phone) values (CONCAT('lyn',v_i), mod(v_i,120), 'M', CONCAT('152',ROUND(RAND(1)*100000000)));
    ->  SET v_i = v_i+1;
    -> END WHILE;
    -> commit;
    -> END $$
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;

#插入10w数据
mysql> call insert_user_data(100000);
Query OK, 0 rows affected (6.69 sec)
# ls -lh user.ibd
-rw-r----- 1 mysql mysql 14M Nov 6 10:58 /data2/mysql/test/user.ibd

# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0001>   #增加了一个非叶子节点，树的高度从1变为2.
........................................................
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 896:
Freshly Allocated Page: 493
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 400
File Segment inode: 1

delete数据后的空间变化

mysql> select min(id),max(id),count(*) from user;
+---------+---------+----------+
| min(id) | max(id) | count(*) |
+---------+---------+----------+
|       1 |  100000 |   100000 |
+---------+---------+----------+
1 row in set (0.05 sec)
#删除50000条数据，理论上空间应该从14MB变长7MB左右。
mysql> delete from user limit 50000;
Query OK, 50000 rows affected (0.25 sec)

#数据文件大小依然是14MB，没有缩小。
# ls -lh /data2/mysql/test/user1.ibd 
-rw-r----- 1 mysql mysql 14M Nov  6 13:22 /data2/mysql/test/user.ibd

#数据页没有被回收。
# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0001>
........................................................
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 896:
Freshly Allocated Page: 493
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 400
File Segment inode: 1
#在MySQL内部是标记删除，
mysql> use information_schema;

Database changed
mysql> SELECT A.SPACE AS TBL_SPACEID, A.TABLE_ID, A.NAME AS TABLE_NAME, FILE_FORMAT, ROW_FORMAT, SPACE_TYPE,  B.INDEX_ID , B.NAME AS INDEX_NAME, PAGE_NO, B.TYPE AS INDEX_TYPE FROM INNODB_SYS_TABLES A LEFT JOIN INNODB_SYS_INDEXES B ON A.TABLE_ID =B.TABLE_ID WHERE A.NAME = 'test/user1';
+-------------+----------+------------+-------------+------------+------------+----------+------------+---------+------------+
| TBL_SPACEID | TABLE_ID | TABLE_NAME | FILE_FORMAT | ROW_FORMAT | SPACE_TYPE | INDEX_ID | INDEX_NAME | PAGE_NO | INDEX_TYPE |
+-------------+----------+------------+-------------+------------+------------+----------+------------+---------+------------+
|        1283 |     1207 | test/user | Barracuda   | Dynamic    | Single     |     2236 | PRIMARY    |       3 |          3 |
+-------------+----------+------------+-------------+------------+------------+----------+------------+---------+------------+
1 row in set (0.01 sec)

PAGE_NO = 3 标识B-tree的root page是3号页，INDEX_TYPE = 3是聚集索引。 INDEX_TYPE取值如下：
0 = nonunique secondary index; 
1 = automatically generated clustered index (GEN_CLUST_INDEX); 
2 = unique nonclustered index; 
3 = clustered index; 
32 = full-text index;
#收缩空间再后进行观察

MySQL内部不会真正删除空间，而且做标记删除，即将delflag:N修改为delflag:Y，commit之后会会被purge进入删除链表，如果下一次insert更大的记录，delete之后的空间不会被重用，如果插入的记录小于等于delete的记录空会被重用，这块内容可以通过知数堂的innblock工具进行分析。

B2S商城系统
B2S商城系统

B2S商城系统B2S商城系统是由佳弗网络工作室凭借专业的技术、丰富的电子商务经验在第一时刻为最流行的分享式购物（或体验式购物）推出的开源程序。开发采用PHP+MYSQL数据库，独立编译模板、代码简洁、自由修改、安全高效、数据缓存等技术的应用，使其能在大浏览量的环境下快速稳定运行，切实节约网站成本，提升形象。注意：如果安装后页面打开出现找不到数据库等错误，请删除admin下的runtime文件夹和a

下载

Innodb中的碎片

碎片的产生

我们知道数据存储在文件系统上的，总是不能100%利用分配给它的物理空间，删除数据会在页面上留下一些”空洞”，或者随机写入（聚集索引非线性增加）会导致页分裂，页分裂导致页面的利用空间少于50%，另外对表进行增删改会引起对应的二级索引值的随机的增删改，也会导致索引结构中的数据页面上留下一些"空洞"，虽然这些空洞有可能会被重复利用，但终究会导致部分物理空间未被使用，也就是碎片。

同时，即便是设置了填充因子为100%，Innodb也会主动留下page页面1/16的空间作为预留使用（An innodb_fill_factor setting of 100 leaves 1/16 of the space in clustered index pages free for future index growth）防止update带来的行溢出。

mysql> select table_schema,
    ->        table_name,ENGINE,
    ->        round(DATA_LENGTH/1024/1024+ INDEX_LENGTH/1024/1024) total_mb,TABLE_ROWS,
    ->        round(DATA_LENGTH/1024/1024) data_mb, round(INDEX_LENGTH/1024/1024) index_mb, round(DATA_FREE/1024/1024) free_mb, round(DATA_FREE/DATA_LENGTH*100,2) free_ratio
    -> from information_schema.TABLES where  TABLE_SCHEMA= 'test'
    -> and TABLE_NAME= 'user';
+--------------+------------+--------+----------+------------+---------+----------+---------+------------+
| table_schema | table_name | ENGINE | total_mb | TABLE_ROWS | data_mb | index_mb | free_mb | free_ratio |
+--------------+------------+--------+----------+------------+---------+----------+---------+------------+
| test         | user      | InnoDB |        4 |      50000 |       4 |        0 |       6 |     149.42 |
+--------------+------------+--------+----------+------------+---------+----------+---------+------------+
1 row in set (0.00 sec)

其中data_free是分配了未使用的字节数，并不能说明完全是碎片空间。

碎片的回收

对于InnoDB的表，可以通过以下命令来回收碎片，释放空间，这个是随机读IO操作，会比较耗时，也会阻塞表上正常的DML运行，同时需要占用额外更多的磁盘空间，对于RDS来说，可能会导致磁盘空间瞬间爆满，实例瞬间被锁定，应用无法做DML操作，所以禁止在线上环境去执行。

#执行InnoDB的碎片回收
mysql> alter table user engine=InnoDB;
Query OK, 0 rows affected (9.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

##执行完之后，数据文件大小从14MB降低到10M。
# ls -lh /data2/mysql/test/user1.ibd 
-rw-r----- 1 mysql mysql 10M Nov 6 16:18 /data2/mysql/test/user.ibd
mysql> select table_schema,        
    ->table_name,ENGINE,        
    ->round(DATA_LENGTH/1024/1024+ INDEX_LENGTH/1024/1024) total_mb,TABLE_ROWS,        
    ->round(DATA_LENGTH/1024/1024) data_mb, 
    ->round(INDEX_LENGTH/1024/1024) index_mb, 
    ->round(DATA_FREE/1024/1024) free_mb, 
    ->round(DATA_FREE/DATA_LENGTH*100,2) free_ratio from information_schema.TABLES where  TABLE_SCHEMA= 'test' and TABLE_NAME= 'user';
+--------------+------------+--------+----------+------------+---------+----------+---------+------------+
| table_schema | table_name | ENGINE | total_mb | TABLE_ROWS | data_mb | index_mb | free_mb | free_ratio |
+--------------+------------+--------+----------+------------+---------+----------+---------+------------+
| test         | user      | InnoDB |        5 |      50000 |       5 |        0 |       2 |      44.29 |
+--------------+------------+--------+----------+------------+---------+----------+---------+------------+
1 row in set (0.00 sec)

delete对SQL的影响

未删除前的SQL执行情况

#插入100W数据
mysql> call insert_user_data(1000000);
Query OK, 0 rows affected (35.99 sec)

#添加相关索引
mysql> alter table user add index idx_name(name), add index idx_phone(phone);
Query OK, 0 rows affected (6.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

#表上索引统计信息
mysql> show index from user;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY   |            1 | id          | A         |      996757 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | idx_name  |            1 | name        | A         |      996757 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | idx_phone |            1 | phone       | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

#重置状态变量计数
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

#执行SQL语句
mysql> select id, age ,phone from user where name like 'lyn12%';
+--------+-----+-------------+
| id     | age | phone       |
+--------+-----+-------------+
|    124124 |   3 | 15240540354 |
|   1231 |  30 | 15240540354 |
|  12301 |  60 | 15240540354 |
.............................
| 129998 |  37 | 15240540354 |
| 129999 |  38 | 15240540354 |
| 130000 |  39 | 15240540354 |
+--------+-----+-------------+
11111 rows in set (0.03 sec)

mysql> explain select id, age ,phone from user where name like 'lyn12%';
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows  | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+
|  1 | SIMPLE      | user  | range | idx_name      | idx_name | 82      | NULL | 22226 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

#查看相关状态呢变量
mysql> select * from information_schema.session_status where variable_name in('Last_query_cost','Handler_read_next','Innodb_pages_read','Innodb_data_reads','Innodb_pages_read');
+-------------------+----------------+
| VARIABLE_NAME     | VARIABLE_VALUE |
+-------------------+----------------+
| HANDLER_READ_NEXT | 11111          |    #请求读的行数
| INNODB_DATA_READS | 7868409        |    #数据物理读的总数
| INNODB_PAGES_READ | 7855239        |    #逻辑读的总数
| LAST_QUERY_COST   | 10.499000      |    #SQL语句的成本COST，主要包括IO_COST和CPU_COST。
+-------------------+----------------+
4 rows in set (0.00 sec)

删除后的SQL执行情况

#删除50w数据
mysql> delete from user limit 500000;
Query OK, 500000 rows affected (3.70 sec)

#分析表统计信息
mysql> analyze table user;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.user | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (0.01 sec)

#重置状态变量计数
mysql> flush status;
Query OK, 0 rows affected (0.01 sec)

mysql> select id, age ,phone from user where name like 'lyn12%';
Empty set (0.05 sec)

mysql> explain select id, age ,phone from user where name like 'lyn12%';
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows  | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+
|  1 | SIMPLE      | user  | range | idx_name      | idx_name | 82      | NULL | 22226 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.session_status where variable_name in('Last_query_cost','Handler_read_next','Innodb_pages_read','Innodb_data_reads','Innodb_pages_read');
+-------------------+----------------+
| VARIABLE_NAME     | VARIABLE_VALUE |
+-------------------+----------------+
| HANDLER_READ_NEXT | 0              |
| INNODB_DATA_READS | 7868409        |
| INNODB_PAGES_READ | 7855239        |
| LAST_QUERY_COST   | 10.499000      |
+-------------------+----------------+
4 rows in set (0.00 sec)

结果统计分析

操作 COST 物理读次数 逻辑读次数 扫描行数 返回行数 执行时间
初始化插入100W 10.499000 7868409 7855239 22226 11111 30ms
100W随机删除50W 10.499000 7868409 7855239 22226 0 50ms

这也说明对普通的大表，想要通过delete数据来对表进行瘦身是不现实的，所以在任何时候不要用delete去删除数据，应该使用优雅的标记删除。

delete优化建议

控制业务账号权限

对于一个大的系统来说，需要根据业务特点去拆分子系统，每个子系统可以看做是一个service，例如美团APP，上面有很多服务，核心的服务有用户服务user-service，搜索服务search-service，商品product-service，位置服务location-service，价格服务price-service等。每个服务对应一个数据库，为该数据库创建单独账号，同时只授予DML权限且没有delete权限，同时禁止跨库访问。

#创建用户数据库并授权
create database mt_user charset utf8mb4;
grant USAGE, SELECT, INSERT, UPDATE ON mt_user.*  to 'w_user'@'%' identified by 't$W*g@gaHTGi123456';
flush privileges;

delete改为标记删除

在MySQL数据库建模规范中有4个公共字段，基本上每个表必须有的，同时在create_time列要创建索引，有两方面的好处：

  • 一些查询业务场景都会有一个默认的时间段，比如7天或者一个月，都是通过create_time去过滤，走索引扫描更快。

  • 一些核心的业务表需要以T +1的方式抽取数据仓库中，比如每天晚上00:30抽取前一天的数据，都是通过create_time过滤的。

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否逻辑删除：0：未删除，1：已删除',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'

#有了删除标记，业务接口的delete操作就可以转换为update
update user set is_deleted = 1 where user_id = 1213;

#查询的时候需要带上is_deleted过滤
select id, age ,phone from user where is_deleted = 0 and name like 'lyn12%';

数据归档方式

通用数据归档方法

#1. 创建归档表，一般在原表名后面添加_bak。
CREATE TABLE `ota_order_bak` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_id` varchar(255) DEFAULT NULL COMMENT '订单id',
  `ota_id` varchar(255) DEFAULT NULL COMMENT 'ota',
  `check_in_date` varchar(255) DEFAULT NULL COMMENT '入住日期',
  `check_out_date` varchar(255) DEFAULT NULL COMMENT '离店日期',
  `hotel_id` varchar(255) DEFAULT NULL COMMENT '酒店ID',
  `guest_name` varchar(255) DEFAULT NULL COMMENT '顾客',
  `purcharse_time` timestamp NULL DEFAULT NULL COMMENT '购买时间',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `create_user` varchar(255) DEFAULT NULL,
  `update_user` varchar(255) DEFAULT NULL,
  `status` int(4) DEFAULT '1' COMMENT '状态 ： 1 正常 ， 0 删除',
  `hotel_name` varchar(255) DEFAULT NULL,
  `price` decimal(10,0) DEFAULT NULL,
  `remark` longtext,
  PRIMARY KEY (`id`),
  KEY `IDX_order_id` (`order_id`) USING BTREE,
  KEY `hotel_name` (`hotel_name`) USING BTREE,
  KEY `ota_id` (`ota_id`) USING BTREE,
  KEY `IDX_purcharse_time` (`purcharse_time`) USING BTREE,
  KEY `IDX_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (to_days(create_time)) ( 
PARTITION p201808 VALUES LESS THAN (to_days('2018-09-01')), 
PARTITION p201809 VALUES LESS THAN (to_days('2018-10-01')), 
PARTITION p201810 VALUES LESS THAN (to_days('2018-11-01')), 
PARTITION p201811 VALUES LESS THAN (to_days('2018-12-01')), 
PARTITION p201812 VALUES LESS THAN (to_days('2019-01-01')), 
PARTITION p201901 VALUES LESS THAN (to_days('2019-02-01')), 
PARTITION p201902 VALUES LESS THAN (to_days('2019-03-01')), 
PARTITION p201903 VALUES LESS THAN (to_days('2019-04-01')), 
PARTITION p201904 VALUES LESS THAN (to_days('2019-05-01')), 
PARTITION p201905 VALUES LESS THAN (to_days('2019-06-01')), 
PARTITION p201906 VALUES LESS THAN (to_days('2019-07-01')), 
PARTITION p201907 VALUES LESS THAN (to_days('2019-08-01')), 
PARTITION p201908 VALUES LESS THAN (to_days('2019-09-01')), 
PARTITION p201909 VALUES LESS THAN (to_days('2019-10-01')), 
PARTITION p201910 VALUES LESS THAN (to_days('2019-11-01')), 
PARTITION p201911 VALUES LESS THAN (to_days('2019-12-01')), 
PARTITION p201912 VALUES LESS THAN (to_days('2020-01-01')));

#2. 插入原表中无效的数据（需要跟开发同学确认数据保留范围）
create table tbl_p201808 as select * from ota_order where create_time between '2018-08-01 00:00:00' and '2018-08-31 23:59:59';

#3. 跟归档表分区做分区交换
alter table ota_order_bak exchange partition p201808 with table tbl_p201808; 

#4. 删除原表中已经规范的数据
delete from ota_order where create_time between '2018-08-01 00:00:00' and '2018-08-31 23:59:59' limit 3000;

优化后的归档方式

#1. 创建中间表
CREATE TABLE `ota_order_2020` (........) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (to_days(create_time)) ( 
PARTITION p201808 VALUES LESS THAN (to_days('2018-09-01')), 
PARTITION p201809 VALUES LESS THAN (to_days('2018-10-01')), 
PARTITION p201810 VALUES LESS THAN (to_days('2018-11-01')), 
PARTITION p201811 VALUES LESS THAN (to_days('2018-12-01')), 
PARTITION p201812 VALUES LESS THAN (to_days('2019-01-01')), 
PARTITION p201901 VALUES LESS THAN (to_days('2019-02-01')), 
PARTITION p201902 VALUES LESS THAN (to_days('2019-03-01')), 
PARTITION p201903 VALUES LESS THAN (to_days('2019-04-01')), 
PARTITION p201904 VALUES LESS THAN (to_days('2019-05-01')), 
PARTITION p201905 VALUES LESS THAN (to_days('2019-06-01')), 
PARTITION p201906 VALUES LESS THAN (to_days('2019-07-01')), 
PARTITION p201907 VALUES LESS THAN (to_days('2019-08-01')), 
PARTITION p201908 VALUES LESS THAN (to_days('2019-09-01')), 
PARTITION p201909 VALUES LESS THAN (to_days('2019-10-01')), 
PARTITION p201910 VALUES LESS THAN (to_days('2019-11-01')), 
PARTITION p201911 VALUES LESS THAN (to_days('2019-12-01')), 
PARTITION p201912 VALUES LESS THAN (to_days('2020-01-01')));

#2. 插入原表中有效的数据，如果数据量在100W左右可以在业务低峰期直接插入，如果比较大，建议采用dataX来做，可以控制频率和大小，之前我这边用Go封装了dataX可以实现自动生成json文件，自定义大小去执行。
insert into ota_order_2020 select * from ota_order where create_time between '2020-08-01 00:00:00' and '2020-08-31 23:59:59';

#3. 表重命名
alter table ota_order rename to ota_order_bak;  
alter table ota_order_2020 rename to ota_order;
#4. 插入差异数据
insert into ota_order select * from ota_order_bak a where not exists (select 1 from ota_order b where a.id = b.id);
#5. ota_order_bak改造成分区表，如果表比较大不建议直接改造，可以先创建好分区表，通过dataX把导入进去即可。

#6. 后续的归档方法
#创建中间普遍表
create table ota_order_mid like ota_order;
#交换原表无效数据分区到普通表
alter table ota_order exchange partition p201808 with table ota_order_mid; 
##交换普通表数据到归档表的相应分区
alter table ota_order_bak exchange partition p201808 with table ota_order_mid;

这样原表和归档表都是按月的分区表，只需要创建一个中间普通表，在业务低峰期做两次分区交换，既可以删除无效数据，又能回收空，而且没有空间碎片，不会影响表上的索引及SQL的执行计划。

总结

通过从InnoDB存储空间分布，delete对性能的影响可以看到，delete物理删除既不能释放磁盘空间，而且会产生大量的碎片，导致索引频繁分裂，影响SQL执行计划的稳定性；

同时在碎片回收时，会耗用大量的CPU，磁盘空间，影响表上正常的DML操作。

在业务代码层面，应该做逻辑标记删除，避免物理删除；为了实现数据归档需求，可以用采用MySQL分区表特性来实现，都是DDL操作，没有碎片产生。

另外一个比较好的方案采用Clickhouse，对有生命周期的数据表可以使用Clickhouse存储，利用其TTL特性实现无效数据自动清理。

相关推荐：《mysql教程》

相关文章

mysql如何更新部分字段_mysql update set子句使用

mysql如何实现数据逻辑删除_mysql is_deleted标记应用

mysql触发器可以嵌套调用吗_mysql嵌套执行策略

mysql如何优化空间地理数据索引_mysql SPATIAL索引应用

mysql如何配置数据库审计插件_mysql audit_log安全合规

相关标签:

mysql

本站声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

上一篇：mysql如何在win10中配置环境变量 下一篇：Oracle和MySQL如何给表添加注释

作者最新文章

php7中的curl文件上传出现错误该怎么办

2021-08-13 09:20

MySQL如何获取当前时间和时间戳

2021-08-13 09:42

css如何为元素设置背景图像

2021-08-13 10:45

一招搞定css相对原点定位背景图片

2021-08-13 11:32

一招搞定css定位元素的背景图像

2021-08-13 11:52

如何使用css设置背景图的重复与否

2021-08-13 14:29

教你一招搞定css背景图的大小

2021-08-13 15:33

css如何定义网格线大小

2021-08-13 16:15

css如何设置指定网格的大小和位置

2021-08-13 17:35

分享五个好用的VueUse函数，一起用起来吧！

2021-08-13 17:39

热门AI工具

更多
DeepSeek
DeepSeek

幻方量化公司旗下的开源大模型平台

AI编程开发AI聊天问答
豆包大模型
豆包大模型

字节跳动自主研发的一系列大型语言模型

AI编程开发AI大模型
通义千问
通义千问

阿里巴巴推出的全能AI助手

AI编程开发Agent智能体
腾讯元宝
腾讯元宝

腾讯混元平台推出的AI助手

文档处理Excel 表格
文心一言
文心一言

文心一言是百度开发的AI聊天机器人，通过对话可以生成各种形式的内容。

AI编程开发AI文本写作
讯飞写作
讯飞写作

基于讯飞星火大模型的AI写作工具，可以快速生成新闻稿件、品宣文案、工作总结、心得体会等各种文文稿

AI文本写作中文写作
即梦AI
即梦AI

一站式AI创作平台，免费AI图片和视频生成。

图片拼接图画生成
ChatGPT
ChatGPT

最最强大的AI聊天机器人程序，ChatGPT不单是聊天机器人，还能进行撰写邮件、视频脚本、文案、翻译、代码等任务。

AI编程开发AI文本写作
智谱清言 - 免费全能的AI助手
智谱清言 - 免费全能的AI助手

智谱清言 - 免费全能的AI助手

AI编程开发Agent智能体

相关专题

更多
C# ASP.NET Core微服务架构与API网关实践
C# ASP.NET Core微服务架构与API网关实践

本专题围绕 C# 在现代后端架构中的微服务实践展开，系统讲解基于 ASP.NET Core 构建可扩展服务体系的核心方法。内容涵盖服务拆分策略、RESTful API 设计、服务间通信、API 网关统一入口管理以及服务治理机制。通过真实项目案例，帮助开发者掌握构建高可用微服务系统的关键技术，提高系统的可扩展性与维护效率。

16

2026.03.11

Go高并发任务调度与Goroutine池化实践
Go高并发任务调度与Goroutine池化实践

本专题围绕 Go 语言在高并发任务处理场景中的实践展开，系统讲解 Goroutine 调度模型、Channel 通信机制以及并发控制策略。内容包括任务队列设计、Goroutine 池化管理、资源限制控制以及并发任务的性能优化方法。通过实际案例演示，帮助开发者构建稳定高效的 Go 并发任务处理系统，提高系统在高负载环境下的处理能力与稳定性。

23

2026.03.10

Kotlin Android模块化架构与组件化开发实践
Kotlin Android模块化架构与组件化开发实践

本专题围绕 Kotlin 在 Android 应用开发中的架构实践展开，重点讲解模块化设计与组件化开发的实现思路。内容包括项目模块拆分策略、公共组件封装、依赖管理优化、路由通信机制以及大型项目的工程化管理方法。通过真实项目案例分析，帮助开发者构建结构清晰、易扩展且维护成本低的 Android 应用架构体系，提升团队协作效率与项目迭代速度。

75

2026.03.09

JavaScript浏览器渲染机制与前端性能优化实践
JavaScript浏览器渲染机制与前端性能优化实践

本专题围绕 JavaScript 在浏览器中的执行与渲染机制展开，系统讲解 DOM 构建、CSSOM 解析、重排与重绘原理，以及关键渲染路径优化方法。内容涵盖事件循环机制、异步任务调度、资源加载优化、代码拆分与懒加载等性能优化策略。通过真实前端项目案例，帮助开发者理解浏览器底层工作原理，并掌握提升网页加载速度与交互体验的实用技巧。

95

2026.03.06

Rust内存安全机制与所有权模型深度实践
Rust内存安全机制与所有权模型深度实践

本专题围绕 Rust 语言核心特性展开，深入讲解所有权机制、借用规则、生命周期管理以及智能指针等关键概念。通过系统级开发案例，分析内存安全保障原理与零成本抽象优势，并结合并发场景讲解 Send 与 Sync 特性实现机制。帮助开发者真正理解 Rust 的设计哲学，掌握在高性能与安全性并重场景中的工程实践能力。

218

2026.03.05

PHP高性能API设计与Laravel服务架构实践
PHP高性能API设计与Laravel服务架构实践

本专题围绕 PHP 在现代 Web 后端开发中的高性能实践展开，重点讲解基于 Laravel 框架构建可扩展 API 服务的核心方法。内容涵盖路由与中间件机制、服务容器与依赖注入、接口版本管理、缓存策略设计以及队列异步处理方案。同时结合高并发场景，深入分析性能瓶颈定位与优化思路，帮助开发者构建稳定、高效、易维护的 PHP 后端服务体系。

420

2026.03.04

AI安装教程大全
AI安装教程大全

2026最全AI工具安装教程专题：包含各版本AI绘图、AI视频、智能办公软件的本地化部署手册。全篇零基础友好，附带最新模型下载地址、一键安装脚本及常见报错修复方案。每日更新，收藏这一篇就够了，让AI安装不再报错！

168

2026.03.04

Swift iOS架构设计与MVVM模式实战
Swift iOS架构设计与MVVM模式实战

本专题聚焦 Swift 在 iOS 应用架构设计中的实践，系统讲解 MVVM 模式的核心思想、数据绑定机制、模块拆分策略以及组件化开发方法。内容涵盖网络层封装、状态管理、依赖注入与性能优化技巧。通过完整项目案例，帮助开发者构建结构清晰、可维护性强的 iOS 应用架构体系。

222

2026.03.03

C++高性能网络编程与Reactor模型实践
C++高性能网络编程与Reactor模型实践

本专题围绕 C++ 在高性能网络服务开发中的应用展开，深入讲解 Socket 编程、多路复用机制、Reactor 模型设计原理以及线程池协作策略。内容涵盖 epoll 实现机制、内存管理优化、连接管理策略与高并发场景下的性能调优方法。通过构建高并发网络服务器实战案例，帮助开发者掌握 C++ 在底层系统与网络通信领域的核心技术。

33

2026.03.03

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

相关下载

更多
B2S商城系统

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
MySQL 教程
MySQL 教程

共48课时 | 2.5万人学习

MySQL 初学入门（mosh老师）
MySQL 初学入门（mosh老师）

共3课时 | 0.3万人学习

简单聊聊mysql8与网络通信
简单聊聊mysql8与网络通信

共1课时 | 846人学习

最新文章

更多
mysql如何处理Table doesn't exist报错_mysql库名大小写敏感排查
mysql如何查看事务隔离级别_mysql select @@transaction_isolation
mysql中的条件函数：IF、CASE WHEN的使用
mysql高并发环境下权限配置要注意什么_mysql安全优化说明
mysql如何排查由于防火墙导致的连接中断_mysql网络连通性测试
mysql如何更新部分字段_mysql update set子句使用
mysql如何实现数据逻辑删除_mysql is_deleted标记应用
mysql触发器可以嵌套调用吗_mysql嵌套执行策略
mysql如何优化空间地理数据索引_mysql SPATIAL索引应用
mysql如何配置数据库审计插件_mysql audit_log安全合规
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网：公益在线php培训，帮助PHP学习者快速成长！
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号

  • PHP学习

  • 技术支持

  • 返回顶部