mysql 碎片率_MySQL 5.6 整理表的碎片

QQ截图20200702141000.png

可以看到，当前表的碎片率超高了，50.6%。

有三种办法整理碎片

alter table table_name engine=innodb

optimize table

pt-online-schema-change

这三种操作都是先创建一个临时表复制完成后再删除旧表，所以在执行操作的过程中磁盘会先增大。

会锁表

optimize no_write_to_binlog table tbl_immsg_bigo_96;

MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可，可以写成定时任务来做。

OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。

在OPTIMIZE TABLE运行过程中，MySQL会锁定表。

默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。

pt-online-schema-change - ALTER tables 无需锁表。

[root@VM_100_3_centos ~]# pt-online-schema-change --charset=utf8 --no-version-check --host=10.10.10.10 --user=root --password=rootpass --alter "ENGINE=InnoDB" D=ipcis_cloud,t=fwk_biz_log --max-lag=300 --check-interval=5 --alter-foreign-keys-method=auto --execute

Cannot connect to A=utf8,P=20130,h=,p=...,u=root

No slaves found. See --recursion-method if host TENCENT64.site 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

No foreign keys reference `ipcis_cloud`.`fwk_biz_log`; ignoring --alter-foreign-keys-method.

Altering `ipcis_cloud`.`fwk_biz_log`...

Creating new table...

Created new table ipcis_cloud._fwk_biz_log_new OK.

Altering new table...

Altered `ipcis_cloud`.`_fwk_biz_log_new` OK.

2020-07-02T15:20:43 Creating triggers...

2020-07-02T15:20:43 Created triggers OK.

2020-07-02T15:20:43 Copying approximately 43827483 rows...

Copying `ipcis_cloud`.`fwk_biz_log`: 1% 26:11 remain

2020-07-02T15:21:28 Copied rows OK.

2020-07-02T15:21:28 Analyzing new table...

2020-07-02T15:21:28 Swapping tables...

2020-07-02T15:21:28 Swapped original and new tables OK.

2020-07-02T15:21:28 Dropping old table...

2020-07-02T15:21:50 Dropped old table `ipcis_cloud`.`_fwk_biz_log_old` OK.

2020-07-02T15:21:50 Dropping triggers...

2020-07-02T15:21:50 Dropped triggers OK.

Successfully altered `ipcis_cloud`.`fwk_biz_log`.

整理结果很明显，整理后碎片率0.3%。

QQ截图20200702152506.png

这里有几个参数需要介绍一下：

--dry-run

这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。

--execute

表明你已经阅读了文档，并且确认要 alter the table。你必须配置这个参数来 alter the table。如果你不配置，那么工具将只进行一些安全检查然后就退出了。这帮助确保你已经阅读了文档，并且了解如何使用该工具。如果你没有阅读这些文档，那么不会设置该参数。

--critical-load

每次chunk操作前后，会根据show global status统计指定的状态量的变化，默认是统计Thread_running。目的是为了安全，防止原始表上的触发器引起负载过高。这也是为了防止在线DDL对线上的影响。超过设置的阀值，就会终止操作，在线DDL就会中断。提示的异常如上报错信息。

--max-lag

type: time; default: 1s

lag 滞后偏移

暂停数据拷贝，直到所有replicas的lag值低于该值。在每个 data-copy query (each chunk)后，工具会通过Seconds_Behind_Master查询所有replica的 replication lag 。如果任何replica lag大于该值，那么工具会sleep --check-interval 秒，然后再次检查所有replica。如果你指定 --check-slave-lag，那么工具会检查那台server，而不是所有server。如果你想控制哪个提供工具的监控，配置DSN值--recursion-method。

工具会等待直到replicas停止lagging。如果任一replica停止，工具会一直处于等待状态直到该replica启动。 在所有replicas运行并且lagging不大的情况下，数据拷贝继续。

工具在等待的时候，会打印进程报告。如果replica停止了，会立即打印进程报告，然后在每个进程报告期间重复。

--check-interval

type: time; default: 1

Sleep time between checks for --max-lag.

--max-load

选项定义一个阀值，在每次chunk操作后，查看show global status状态值是否高于指定的阀值。该参数接受一个mysql status状态变量以及一个阀值，如果没有给定阀值，则定义一个阀值为为高于当前值的20%。注意这个参数不会像--critical-load终止操作，而只是暂停操作。当status值低于阀值时，则继续往下操作。是暂停还是终止操作这是--max-load和--critical-load的差别。

--charset

简写: -A; type: string

设置默认字符集。如果值为 utf8，设置 Perl’s binmode on STDOUT to utf8，传送 mysql_enable_utf8 参数到 DBD::mysql，然后在连接到MySQL后运行SET NAMES UTF8。其他的值也是在STDOUT设置 binmode，然后在连到MySQL后运行SET NAMES。

--check-replication-filters

检查复制中是否设置了过滤条件，如果设置了，程序将退出

--nocheck-replication-filters

不检查复制中是否设置了过滤条件

--set-vars

设置mysql的变量值

--check-slave-lag

检查主从延迟

--no-version-check

不检查版本，在阿里云服务器中一般加入此参数，否则会报错