MySQL · 大表数据清理

发表于 | 更新于 | 分类于 | 阅读次数：

背景

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select table_name,table_rows,CONCAT(
TRUNCATE(SUM(data_length)/1024/1024/1024,2),'GB') AS data_size, CONCAT(
TRUNCATE(SUM(index_length)/1024/1024/1024,2),'GB') AS index_size from information_schema.tables where TABLE_SCHEMA = 'dzspzy' and table_name='t_hr_13plan_res_history';

+-------------------------+------------+-----------+------------+
| table_name              | table_rows | data_size | index_size |
+-------------------------+------------+-----------+------------+
| t_hr_13plan_res_history |  296397209 | 110.43GB  | 3.72GB     |
+-------------------------+------------+-----------+------------+

+-------------------------+------------+-----------+------------+
| table_name              | table_rows | data_size | index_size |
+-------------------------+------------+-----------+------------+
| t_hr_13plan_res_history |  269082432 | 102.44GB  | 3.45GB     |
+-------------------------+------------+-----------+------------+

+-------------------------+------------+-----------+------------+
| table_name              | table_rows | data_size | index_size |
+-------------------------+------------+-----------+------------+
| t_hr_13plan_res_history |  213501502 | 78.35GB   | 2.64GB     |
+-------------------------+------------+-----------+------------+

清理

  • 批量删除
1
pt-archiver --source u=sre,p='xx',S=/tmp/mysql-dzsp.sock,P=3306,D=dzspzy,t=t_hr_13plan_res_history --no-check-charset --progress 10000 --where "ID < 251061689" --limit 10000 --txn-size 1000 --bulk-delete --purge --statistics
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
show table status like 't_hr_13plan_res_history'\G
*************************** 1. row ***************************
           Name: t_hr_13plan_res_history
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 205639249
 Avg_row_length: 410
    Data_length: 84329660416
Max_data_length: 0
   Index_length: 2843754496
      Data_free: 35414605824
 Auto_increment: 590343627
    Create_time: 2019-03-27 13:18:10
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment:
  • 整理碎片
1
2
3
4
5
6
7
8
# 方法一
alter table t_hr_13plan_res_history engine = innodb

# 方法二
optimize table t_hr_13plan_res_history

# 方法三
pt-online-schema-change --alter="ENGINE=innodb" D=dzspzy,t=t_hr_13plan_res_history --execute