MySQL表碎片整理
mysql表碎片整理
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'
+--------------+--------------+------------+-------------+--------------+----------+
| table_schema | table_name | table_rows | data_length | index_length | datafree |
+--------------+--------------+------------+-------------+--------------+----------+
| 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; +--------------+----------+----------+-------------------------------------------------------------------+ | table | op | msg_type | msg_text | +--------------+----------+----------+-------------------------------------------------------------------+ | test.account | optimize | note | table does not support optimize, doing recreate + analyze instead | | test.account | optimize | status | ok | +--------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.09 sec)
3.整理表碎片shell脚本
# cat optimize_table.sh
#!/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
do
echo "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-29.log
begin optimize table at: 2018-08-29 22:56:17
alter table employees.departments engine=innodb ...
alter table employees.dept_emp engine=innodb ...
alter table employees.dept_manager engine=innodb ...
alter table employees.employees engine=innodb ...
alter table employees.salaries engine=innodb ...
alter table employees.t1 engine=innodb ...
alter table employees.titles engine=innodb ...
alter table employees.ttt engine=innodb ...
end optimize table at: 2018-08-29 22:56:51