mysql表碎片空间计算_MySQL表碎片整理

最新推荐文章于 2022-08-02 01:11:22 发布

nfs king

最新推荐文章于 2022-08-02 01:11:22 发布

阅读量319

点赞数

文章标签： mysql表碎片空间计算

本文链接：https://blog.csdn.net/weixin_28812943/article/details/113464709

版权

MySQL表碎片整理

计算碎片大小

整理碎片

整理表碎片shell脚本

1. 计算碎片大小

要整理碎片，首先要了解碎片的计算方法。

可以通过show table [from|in db_name] status like '%table_name%'命令查看：

mysql> show table from employees status like 't1'\G

*************************** 1. row ***************************

Name: t1

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 1176484

Avg_row_length: 86

Data_length: 101842944

Max_data_length: 0

Index_length: 0

Data_free: 39845888

Auto_increment: NULL

Create_time: 2018-08-28 13:40:19

Update_time: 2018-08-28 13:50:43

Check_time: NULL

Collation: utf8mb4_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

碎片大小 = 数据总大小 - 实际表空间文件大小

数据总大小 = Data_length + Data_length = 101842944

实际表空间文件大小 = rows * Avg_row_length = 1176484 * 86 = 101177624

碎片大小 = (101842944 - 101177624) / 1024 /1024 = 0.63MB

通过information_schema.tables的DATA_FREE列查看表有没有碎片：

SELECT t.TABLE_SCHEMA,

t.TABLE_NAME,

t.TABLE_ROWS,

t.DATA_LENGTH,

t.INDEX_LENGTH,

concat(round(t.DATA_FREE / 1024 / 1024, 2), 'M') AS datafree

FROM information_schema.tables t

WHERE t.TABLE_SCHEMA = 'employees'

+--------------+--------------+------------+-------------+--------------+----------+

+--------------+--------------+------------+-------------+--------------+----------+

| employees | departments | 9 | 16384 | 16384 | 0.00M |

| employees | dept_emp | 331143 | 12075008 | 11567104 | 0.00M |

| employees | dept_manager | 24 | 16384 | 32768 | 0.00M |

| employees | employees | 299335 | 15220736 | 0 | 0.00M |

| employees | salaries | 2838426 | 100270080 | 36241408 | 5.00M |

| employees | t1 | 1191784 | 48824320 | 17317888 | 5.00M |

| employees | titles | 442902 | 20512768 | 11059200 | 0.00M |

| employees | ttt | 2 | 16384 | 0 | 0.00M |

+--------------+--------------+------------+-------------+--------------+----------+

8 rows in set (0.00 sec)

2. 整理碎片

2.1 使用alter table table_name engine = innodb命令进行整理。

root@localhost [employees] 14:27:01> alter table t1 engine=innodb;

Query OK, 0 rows affected (5.69 sec)

Records: 0 Duplicates: 0 Warnings: 0

root@localhost [employees] 14:27:15> show table status like 't1'\G

*************************** 1. row ***************************

Name: t1

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 1191062

Avg_row_length: 48

Data_length: 57229312

Max_data_length: 0

Index_length: 0

Data_free: 2097152

Auto_increment: NULL

Create_time: 2018-08-28 14:27:15

Update_time: NULL

Check_time: NULL

Collation: utf8mb4_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

2.2 使用pt-online-schema-change工具也能进行在线整理表结构，收集碎片等操作。

[root@mysqldb1 14:29:29 /root]

# pt-online-schema-change --alter="ENGINE=innodb" D=employees,t=t1 --execute

Cannot chunk the original table `employees`.`t1`: There is no good index and the table is oversized. at /opt/percona-toolkit-3.0.11/bin/pt-online-schema-change line 5852.

需要表上有主键或唯一索引才能执行

[root@mysqldb1 14:31:16 /root]

# pt-online-schema-change --alter='engine=innodb' D=employees,t=salaries --execute

No slaves found. See --recursion-method if host mysqldb1 has slaves.

Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

Operation, tries, wait:

analyze_table, 10, 1

copy_rows, 10, 0.25

create_triggers, 10, 1

drop_triggers, 10, 1

swap_tables, 10, 1

update_foreign_keys, 10, 1

Altering `employees`.`salaries`...

Creating new table...

Created new table employees._salaries_new OK.

Altering new table...

Altered `employees`.`_salaries_new` OK.

2018-08-28T14:37:01 Creating triggers...

2018-08-28T14:37:01 Created triggers OK.

2018-08-28T14:37:01 Copying approximately 2838426 rows...

Copying `employees`.`salaries`: 74% 00:10 remain

2018-08-28T14:37:41 Copied rows OK.

2018-08-28T14:37:41 Analyzing new table...

2018-08-28T14:37:42 Swapping tables...

2018-08-28T14:37:42 Swapped original and new tables OK.

2018-08-28T14:37:42 Dropping old table...

2018-08-28T14:37:42 Dropped old table `employees`.`_salaries_old` OK.

2018-08-28T14:37:42 Dropping triggers...

2018-08-28T14:37:42 Dropped triggers OK.

Successfully altered `employees`.`salaries`.

2.3 使用optimize table命令，整理碎片。

运行OPTIMIZE TABLE， InnoDB创建一个新的.ibd具有临时名称的文件，只使用存储的实际数据所需的空间。优化完成后，InnoDB删除旧.ibd文件并将其替换为新文件。如果先前的.ibd文件显着增长但实际数据仅占其大小的一部分，则运行OPTIMIZE TABLE可以回收未使用的空间。

mysql>optimize table account;

+--------------+----------+----------+-------------------------------------------------------------------+

+--------------+----------+----------+-------------------------------------------------------------------+

+--------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (0.09 sec)

3. 整理表碎片shell脚本

#!/bin/sh

socket=/tmp/mysql3306.sock

time=`date +"%Y-%m-%d"`

SQL="select concat(d.TABLE_SCHEMA,'.',d.TABLE_NAME) from information_schema.TABLES d where d.TABLE_SCHEMA = 'employees'"

optimize_table_name=$(/usr/local/mysql/bin/mysql -S $socket -e "$SQL"|grep -v "TABLE_NAME")

echo "Begin Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>/tmp/optimize_table_$time.log

for table_list in $optimize_table_name

echo `date +"%Y-%m-%d %H:%M:%S"` "alter table $table_list engine=innodb ...">>/tmp/optimize_table_$time.log

/usr/local/mysql/bin/mysql -S $socket -e "alter table $table_list engine=innoDB"

done

echo "End Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>>/tmp/optimize_table_$time.log

输出内容

# cat optimize_table_2018-08-30.log

Begin Optimize Table at: 2018-08-30 08:43:21

2018-08-30 08:43:21 alter table employees.departments engine=innodb ...

2018-08-30 08:43:21 alter table employees.dept_emp engine=innodb ...

2018-08-30 08:43:27 alter table employees.dept_manager engine=innodb ...

2018-08-30 08:43:27 alter table employees.employees engine=innodb ...

2018-08-30 08:43:32 alter table employees.salaries engine=innodb ...

2018-08-30 08:44:02 alter table employees.t1 engine=innodb ...

2018-08-30 08:44:17 alter table employees.titles engine=innodb ...

2018-08-30 08:44:28 alter table employees.ttt engine=innodb ...

End Optimize Table at: 2018-08-30 08:44:28

nfs king

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql表碎片空间计算_MySQL表碎片整理

MySQL表碎片整理计算碎片大小整理碎片整理表碎片shell脚本1. 计算碎片大小要整理碎片，首先要了解碎片的计算方法。可以通过show table [from|in db_name] status like '%table_name%'命令查看：mysql> show table from employees status like 't1'\G**********************...
复制链接

扫一扫