optimize table

 

 

> optimize table endpoint \G;
*************************** 1. row ***************************
   Table: falcon_global.endpoint
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: falcon_global.endpoint
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (6.37 sec)

ERROR: 
No query specified

Thu Aug 27 10:48:08 2020


> alter table endpoint engine='InnoDB';   
Query OK, 0 rows affected (6.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Aug 27 10:49:21 2020

 

optimize table  apple;执行后，他会重建一个相同的表，ibd文件大小差不多，原本apple.ibd文件就有2.1T了，该系统总共只有2.6T，显然装不下临时文件了

[work@xxxxx apple]$ ll -h
total 2.1T
-rw-r----- 1 work work   67 Aug 28  2019 db.opt
-rw-r----- 1 work work 8.8K Sep  1  2019 apple.frm
-rw-r----- 1 work work 2.1T Jun  4 09:55 apple.ibd
-rw-r----- 1 work work 8.8K Jun  4 09:54 #sql-2cf54_e65805.frm
-rw-r----- 1 work work 1.3G Jun  4 09:55 #sql-ib46-1552102934.ibd

 

同样的，alter table endpoint engine='InnoDB';  也会新建一个临时表，还是无法解决因为大表的存在而存储空间有限的问题

 

这里mysql给的提示是

Note>> Table does not support optimize, doing recreate + analyze instead 
Status>> OK
也就是说 optimize table 对于innodb来说，无法作为a single operation，实际的操作是：

ALTER TABLE test.foo ENGINE=InnoDB;
ANALYZE TABLE test.foo;

   MySQL5.7已经推荐对于InnoDB的table使用 alter table table_name engine=innodb;语句的方式来进行表碎片优化。

 

使用optimize table table.name;

出现Table does not support optimize, doing recreate + analyze instead

#########

Everytime you do optimize MySQL, by using mysqlcheck -A -o or using ./mysql_optimize from here.
You may see the output

Table does not support optimize, doing recreate + analyze instead.

It is because the table that you are using is InnoDB.

You can optimize the InnoDB tables by using this.

#########
1，

ALTER TABLE table_name ENGINE='InnoDB';

This will create a copy of the original table, and drop the original table, and replace to the original place.

Although this is safe, but I suggest you do backup and test first before doing this.

原来如此，大致意思是说innodb的数据库不支持optimize，可以用
ALTER TABLE table.name ENGINE='InnoDB';
该方法会对旧表以复制的方式新建一个新表，然后删除旧表。虽然这个过程是安全的，但是在进行操作时还是先进行备份为好

还有一种方式：

You can make OPTIMIZE TABLE work on other storage engines by starting mysqld with the --skip-new or --safe-mode option. In this case, OPTIMIZE TABLE is just mapped toALTER TABLE.

2，

上面是说要求我们在启动的时候指定--skip-new或者--safe-mode选项来支持optimize功能

 

良策：

01： 将binlog文件格式从rows改为mixed

 

 

 

 

 

#########################################