欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL表碎片整理

程序员文章站 2022-07-10 22:26:33
MySQL表碎片整理 1. 计算碎片大小 2. 整理碎片 2.1 使用alter table table_name engine = innodb命令进行整理。 2.2 使用pt-online-schema-change工具也能进行在线整理表结构,收集碎片等操作。 2.3 使用optimize ta ......

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.tablesdata_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