运行OPTIMIZE TABLE对表进行碎片整理以获得更好的性能

我阅读了与OPTIMIZE TABLE相关的文章，需要进一步澄清。

我针对我的MariaDBv10.6.7运行了一个mysqltuner.pl，在那里我得到了一些建议，其中之一就是运行优化表。

Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `DB`.`TableA`; -- can free 426 MB
Total freed space after theses OPTIMIZE TABLE : 426 Mb

问题：

是否可以在InnoDB表中运行OPTIMIZE TABLE"TableA"以获得更好的性能(据我所知，它会清除磁盘中未使用的空间，但会提高性能吗)
由于我正在使用InnoDB，它说"；表不支持优化，改为"重新创建+分析"；。我需要运行Alter Table吗。。。OPTIMIZE而不是OPTIMIZETABLE(猜测两者都有关联)
即使在我按照建议运行OPTIMIZE表之后，我仍然看到表426Mb并没有完全释放(它被减少到384MB)。我们不能腾出全尺寸的吗

> select * from information_schema.TABLES where TABLE_NAME = "TableA"G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: DB
TABLE_NAME: TableA
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 1600474
AVG_ROW_LENGTH: 207
DATA_LENGTH: 332136448
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 446693376 (426MB)
AUTO_INCREMENT: NULL
CREATE_TIME: 2022-08-09 16:01:05
UPDATE_TIME: 2022-08-09 16:04:47
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS: partitioned
TABLE_COMMENT:
1 row in set (0.01 sec)
ERROR: No query specified

> optimize table TableA;
+-----------+----------+----------+--------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                           |
+-----------+----------+----------+--------------------------------------------------------------------+
| DB.TableA | optimize | note     | Table does not support optimize, doing recreate + analyze instead  |
| DB.TableA | optimize | status   | OK                                                                 |
+-----------+----------+----------+--------------------------------------------------------------------+
2 rows in set (8.25 sec)
127.0.0.1:3307> select * from information_schema.TABLES where TABLE_NAME = "TableA"G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: DB
TABLE_NAME: TableA
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 1600474
AVG_ROW_LENGTH: 193
DATA_LENGTH: 310116352
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 402653184 (384MB)
AUTO_INCREMENT: NULL
CREATE_TIME: 2022-08-09 16:47:00
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS: partitioned
TABLE_COMMENT:
1 row in set (0.27 sec)

使用与mysqltuner.pl中相同的逻辑来查找可用大小。不确定查询背后的逻辑。

SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME),cast(DATA_FREE as signed) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema', 'mysql') AND DATA_LENGTH/1024/1024>100 AND cast(DATA_FREE as signed)*100/(DATA_LENGTH+INDEX_LENGTH+cast(DATA_FREE as signed)) > 10 AND NOT ENGINE='MEMORY' $not_innodb

更新1:

根据请求，添加了表A 的输出

> SHOW TABLE STATUS WHERE name LIKE "TableA"G;
*************************** 1. row ***************************
Name: TableA
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1875385
Avg_row_length: 3
Data_length: 5685248
Max_data_length: 0
Index_length: 0
Data_free: 1991245824
Auto_increment: NULL
Create_time: 2022-10-25 10:53:40
Update_time: 2022-10-25 11:34:32
Check_time: NULL
Collation: utf8mb3_general_ci
Checksum: NULL
Create_options: partitioned
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0.002 sec)
> Show create table TableA;
| TableA | CREATE TABLE `TableA` (
`Col1` mediumint(8) unsigned NOT NULL,
`Col2` tinyint(4) NOT NULL,
`Col3` tinyint(4) NOT NULL,
`Col4` tinyint(4) NOT NULL,
`Col5` tinyint(4) NOT NULL,
`Col6` smallint(4) NOT NULL,
`timestamp` int(11) NOT NULL,
`Col7` bigint(20) DEFAULT NULL,
`Col8` bigint(20) DEFAULT NULL,
`Col9` tinyint(4) DEFAULT NULL,
:::
`Col40` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`Col1` ,`Col2` ,`Col3` ,`Col4` ,`Col5` ,`Col6`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
PARTITION BY RANGE (`timestamp`)
(PARTITION `p2022_10_11_02_00_00` VALUES LESS THAN (1665437400) ENGINE = InnoDB,
PARTITION `p2022_10_11_03_00_00` VALUES LESS THAN (1665441000) ENGINE = InnoDB,
PARTITION `p2022_10_11_04_00_00` VALUES LESS THAN (1665444600) ENGINE = InnoDB,
....
PARTITION `p2022_10_25_12_00_00` VALUES LESS THAN (1666683000) ENGINE = InnoDB)
//partitioned by timestamp. Partitioned more than 360

简短回答："永远不要"在InnoDB表上使用OPTIMIZE TABLE。(这主要是浪费时间。即使你看到了进步，也会很快被吞噬。)

为什么你没有存款？每个CCD_ 2预先分配4MB-7MB的"；免费的"；空间"建议"没有注意到这一点；所以你没有存款。

如果您曾经需要DELETE很多行，让我们谈谈。通常有更好的方法来做到这一点，而且它们不会导致做OPTIMIZE的诱惑。

这张桌子大约有70个分区？只有160万排？你从PARTITIONing中得到什么好处吗？(我对此表示怀疑。)让我们看看主要的疑问；我将带您了解一些更好的索引和无分区。

根据问题中的信息，数据库中的所有内容都很好。

是的，可以在生产表上使用OPTIMIZE。它及其InnoDB时代的替代品使用在线数据定义语言语句。
当您对InnoDB表说OPTIMIZE TABLE时，服务器会做正确的事情。
像sqltuner.pl这样的调优工具提供了估计，而不是关于节省空间之类的硬数字。
在许多情况下，很难衡量OPTIMIZE TABLE带来的性能改进。具有FULLTEXT索引的繁忙表是个例外。

一些背景。

当应用程序更改表(使用INSERT、UPDATE和DELETE)时，有时会在表的数据结构中留下未使用的空间。一个明显的例子是：更新一行，将较长的VARCHAR()值更改为较短的值。

FULLTEXT索引在更改时也会留下空白。

诊断程序。和information_schema.TABLES.DATA_FREE，估计重新组织(优化)表后的可用空间。与InnoDB等较新的存储引擎相比，传统MyISAM存储引擎的这些估计更准确。因此，您的表reorg没有产生工具估计的那么多可用空间也就不足为奇了。

一些参考文献：

MariaDB碎片整理InnoDB表空间
MySQL Optimize Table语句

使用OPTIMIZE TABLE对InnoDB表进行碎片整理，这可能会减小大小，尤其是在大批量删除等重大数据更改之后。

但在实践中，它不会显著提高性能。性能不是使用OPTIMIZE TABLE的原因。

关于"；表不支持优化"；因为它不像OPTIMIZE TABLE在旧的MyISAM存储引擎上那样做。对于InnoDB，它确实支持该语句，而且它确实做了一些有用的事情：将行复制到新的表空间，并在此过程中重建二级索引。它不支持优化的信息具有误导性。

对于InnoDB，OPTIMIZE TABLE <name>执行与ALTER TABLE <name> FORCE或ALTER TABLE <name> ENGINE=InnoDB相同的工作。ALTER TABLE <name> OPTIMIZE没有语法(指定的分区除外)。

至于你神秘的高"；自由数据"；，我想知道您是否将此表存储在共享系统表空间中。也就是说，配置选项innodb_file_per_table被禁用，这导致表一起存储在系统表空间中，该表空间通常在数据目录中的文件ibdata1中。

如果表在系统表空间中，那么OPTIMIZE table不会将表复制到新的表空间，它只是将数据移动到系统表空间的另一个区域，并且不会恢复任何可用空间。此外；data_ free"；是针对整个表空间报告的，而不仅仅是针对相应的表。"；data_ free"；优化该表空间中的表之后，可能不会有太大的变化。

您可以通过以下方式检查表占用的表空间：

SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE NAME = '<schemaname>.<tablename>';

系统表空间的空间id为0。如果它不是0，那么表就在它自己独立的表空间中。

我希望人们不要使用mysqltuner。

众所周知，mysqltuner提供的建议不可靠。我见过一些案例，它的建议表明作者对MySQL内部的了解不够，无法提供建议。我认为mysqltuner导致了很多不必要的工作。

以下是一些过去的例子，我试图纠正那些被mysqltuner报告弄糊涂的人的误解：

InnoDB写入日志效率太高，超过100%(1953.15%)
小型数据库的MySQL tmp_table_size
Mysql每线程内存，导致线程内存使用的变量

相关内容

最新更新

热门标签：