如果我是一个innodb表，这个查询执行了很多次（一段时间内每秒执行一次，附加了少量数据），并且 innodb_files_per_table = 0 这会导致存储问题吗？blob本身可能是2-3mb，但是我丢失了30gb的数据使用量，我想知道这段代码是不是有什么原因，或者我是否应该找其他地方。
sql语句
UPDATE phppos_app_files
SET file_data = CONCAT(file_data, "NEW LINE OF DATA")
WHERE file_id = 10;架构：
mysql> show create table phppos_app_files;
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_app_files | CREATE TABLE `phppos_app_files` (
`file_id` int(10) NOT NULL AUTO_INCREMENT,
`file_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`file_data` longblob NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`expires` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`file_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7577 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>blog列中存储的数据是2-3mb的日志文本；只是纯文本。但我要问的问题是，频繁的更新查询是否会导致存储问题，从而导致数据库出现严重问题。我只想确保它只存储最少的信息量，如果与concat更新可以导致任何巨大的磁盘问题。
3条答案
按热度按时间epfja78i1#
在存储和访问blob时，频繁地更新小数据块可能会由于碎片而浪费大量空间。可能需要考虑以下优化：
存储前压缩，如本文档中所述（可能与您的用例相关，因为blob存储文本）
定期运行优化表命令，以重新组织物理存储。正如bill karwin所评论的
innodb_file_per_table禁用此选项不会收缩表空间。但是，它将回收由于碎片而丢失的空间，并将其标记为表空间中可用的空间：因此它将在再次扩展表空间之前重新调整任何空扩展数据块的用途。z18hc3ub2#
如果频繁地更新大型blob，它确实可能会暂时占用大量存储空间，并使表空间支离破碎。
更新会将原始数据页复制到“undo log”中，而“undo log”只是表空间中的一个页面区域。innodb保留这些复制的页面，以防回滚事务并必须还原原始数据。提交更新后，不再需要undo日志中的副本，innodb将逐渐清理。但是如果你的更新像你说的那样频繁，那么你在undo日志中积累垃圾的速度可能比innodb清除垃圾的速度还要快。
在任何情况下，扩展存储以获取更多的undo log内容都会增加表空间，即使在innodb清理之后，表空间也不会缩小。它将永远保持它的高水位线大小。
为了缓解这种情况，mysql 5.6引入了一个选项，允许将undo日志存储在系统表空间之外，mysql 5.7引入了一个特性，在清理完undo日志之后截断它们。阅读https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html 有关这些功能的详细信息。
如果您对innodb文件的内部布局感兴趣，我建议您尝试一下开源工具jeremy cole写道：https://github.com/jeremycole/innodb_ruby/wiki
您可以转储ibdata1表空间中内容的各种摘要。许多文件可能未使用，但仍占用空间。
下面是一个来自本地mysql沙盒的示例。我只有一张table
innondb_file_per_table=0（所有其他表都在中心表空间之外）。我运行了这个命令：这是它创建的插图。图例（不包括在内）显示我的一个表的数据和索引在3008到3392页之间。其他的东西主要是innodb系统数据结构（黑色）、系统表、插入缓冲区（深黄色）和未占用页面（灰色）。
0g0grzrc3#
您可以使用下面的查询来检查表的大小。通过这种方式，您可以看到哪个表正在浪费您的存储空间。
实际上，使用的空间总量保持不变。
如果你使用
innodb_files_per_table=0您将只有一个表空间，而且由于您的数据变化很快，因此表空间可能会在时间上变得支离破碎。如果以后决定删除或截断表，表空间大小将保持不变（不会缩小），因此无法从删除操作中恢复空间。