mysql - Innodb 表有很多删除和插入操作 - 是否浪费了磁盘空间？

Question

我有一个大(500GB)innodb 表，我正在其中执行许多插入和删除操作。innodb_file_per_table 在我的 var 配置中设置。我知道它可能包含大量“浪费”的磁盘空间，由于 innodb 的性质，问题是预期有多少，以及是否有办法在我的特定表中找出该数字。据我了解，它重新使用了删除期间创建的未使用空间，因此如果我继续执行插入操作，在执行 re-claim procedure 时不会遇到任何重大差异？作为一条经验法则，磁盘空间浪费的上限和下限是多少，以及什么使用模式定义了它？

Answer 1

如果您启用了 innodb_file_per_table，我有一些好消息。除了对表进行碎片整理之外，您还可以做更多的事情。您可以缩小表格。为了了解您将回收多少空间，请按照以下步骤操作:

对于这些步骤，我们使用 InnoDB 表 mydb.mytable

GIVEN_DB=mydb
GIVEN_TB=mytable
MYSQL_CONN="-root -p...."
SQLSTMT="SELECT data_length+index_length FROM information_schema.tables WHERE"
SQLSTMT="${SQLSTMT} table_schema='${GIVEN_DB}' AND table_name='${GIVEN_TB}'"
IBDSIZE=`ls -l /var/lib/mysql/${GIVEN_DB}/${GIVEN_TB}.ibd | awk '{print $5}'`
TBLSIZE=`mysql ${MYSQL_CONN} --skip-column-names -Ae"${SQLSTMT}"`
(( FRGSIZE = IBDSIZE - TBLSIZE ))
FRAGPCT=`echo ${FRGSIZE}00/${IBDSIZE}|bc`
echo ${IBDSIZE} ${TBLSIZE} ${FRGSIZE} ${FRAGPCT}

这将打印 4 个数字

• IBDSIZE:从操作系统角度来看 InnoDB 单个表空间的大小
• TBLSIZE:INFORMATION_SCHEMA 数据库中 InnoDB 表的大小
• FRGSIZE:IBDSIZE 和 TBLSIZE 的差异(以字节为单位)
• FRAGPCT:IBDSIZE 和 TBLSIZE 的百分比差异

如果您不喜欢 FRAGPCT 的高度，可以通过三种方法来缩小 mydb.mytable:

选项 1:优化表 mydb.mytable;

选项 2:更改表 mydb.mytable ENGINE=InnoDB;

选项 3:分阶段运行压缩

CREATE TABLE mydb.mytabletmp LIKE mydb.mytable;
INSERT INTO mydb.mytabletmp SELECT * FROM mydb.mytable;
DROP TABLE mydb.mytable;
ALTER TABLE mydb.mytabletmp RENAME mydb.mytable;

尝试一下!!!