☰

MyISAM和InnoDB如何利用HD空间？

tag-icon • tag-icon mysql disk-space-utilization innodb myisam
MyISAM和InnoDB如何利用HD空间？

我的 MySQL 服务器硬盘空间很快就用完了。我的大多数大型表都使用 InnoDB 引擎（不是出于任务关键性原因）。为了避免可怕的‘删除数据库以恢复 innodb 磁盘空间‘回应，我想更好地了解这两个引擎如何在磁盘上存储数据。

  1. 如果我使用 MyISAM，我是否能够通过删除行更轻松地恢复磁盘空间？除了删除/截断/清空查询之外，是否还需要运行其他命令？
  2. 假设#1 为真：将现有的 InnoDB 表转换为 MyISAM 并以此方式恢复空间是否可能/可行？

答案1

MySQL 对 DiskSpace 的使用是相当可预测的。

information_schema 可以快速显示两个存储引擎使用了多少空间。然而，使用 innodb_file_per_table 配置 InnoDB 效果更好。这样，您就可以精细管理单个 InnoDB 表的磁盘空间。如果您没有 innodb_file_per_table，ibdata1 将增长，并且永远不会缩小。

我写了一篇关于彻底清理 InnoDB 的精彩文章。

对于 MyISAM，您需要定期运行下列操作之一：

  • OPTIMIZE TABLE myisam-tablename；
  • ALTER TABLE myisam-tablename ENGINE=MyISAM; ANALYZE TABLE myisam-tablename；

这些将压缩 MyISAM，以便 MyISAM 表组件（.MYD 和 .MYI）中没有未使用的数据和索引页

您可以用以下查询手动监控磁盘空间使用情况：

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize;

这将告诉引擎存储引擎的数据和索引占用了多少空间。请注意查询末尾的子句：(SELECT 3 pw)。更改数字以生成不同单位的报告

(SELECT 0 pw) for Bytes
(SELECT 1 pw) for KiloBytes
(SELECT 2 pw) for MegaBytes
(SELECT 3 pw) for GigaBytes
(SELECT 4 pw) for TeraBytes
(SELECT 5 pw) for PetaBytes (Write me if you every get numbers this high)

更新时间：2012-05-26 22:29 EDT

说到InnoDB，如果你使用innodb_file_per_table，你可能会发现文件的文件大小.ibd和data_length + index_length的总和是有差别的。

对于 InnoDB 表mydb.mytable，您应该进行以下比较：

  • 通过运行获取文件大小ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print %5}'
  • 从信息模式的角度获取表的大小：SELECT data_length+index_length FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable';
  • 如果是filesize > (data_length+index_length) * 1.1，那么您应该像这样拖动表格：ALTER TABLE mydb.mytable ENGINE=InnoDB;

这将创建一个临时表，仅将实际数据页和索引页复制到临时表中，删除原始数据页，并将临时表重命名为 mydb.mytable。使用一个命令即可立即进行表压缩。请在非工作时间安排所有表压缩。

答案2

MyISAM 分配给磁盘的空间反映了实际使用的空间。如果您不使用外键、事务或任何其他 InnoDB 独有的功能，则可以轻松转换为 MyISAM。InnoDB 的写入速度更快，而 MyISAM 的读取速度更快。最后，我建议在随意更改存储引擎之前详细研究引擎之间的差异。

以下是我过去用于释放 InnoDB 表空间的过程：

从 mysql innodb 中删除大量数据

相关内容