What does information_schema.TABLES.DATA_FREE mean in MySQL?
来源:互联网 发布:简历采集软件 编辑:程序博客网 时间:2024/05/17 05:50
1. DATA_FREE 是什么
The DATA_FREE column shows the free space in bytes for InnoDB tables.
DATA_FREE
是 InnoDB 表空间中没有使用的空间,每当从 InnoDB 表中删除了一行内容,该段空间就会被留空。而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大。
如下:
... ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Compact TABLE_ROWS: 45667473 AVG_ROW_LENGTH: 199 DATA_LENGTH: 9096413184MAX_DATA_LENGTH: 0 INDEX_LENGTH: 10359996416 DATA_FREE: 252824256512...
如上面所示,该表由于经常删除导致DATA_FREE
过大,已经超出表数据大小 10 多倍,表真正数据大小为:DATA_LENGTH(9096413184) + INDEX_LENGTH(10359996416)。
2. 如何取回DATA_FREE
的空间
有多种方法,可以根据不同的情况选择。
方法一
1. mysqldump 出所有数据库数据;2. 删除数据库数据,保留 schema;3. `/etc/my.cnf`设置参数`innodb_file_per_table=1`;4. 删除 ibdata1, ib_logfile0 and ib_logfile1 ;5. 重启 MySQL;6. 导入数据。
方法二
mysql> optimize table table_name;
方法三
删除旧表,创建新表;
ref:
http://stackoverflow.com/questions/9731853/what-does-information-schema-tables-data-free-mean-in-mysql
http://dba.stackexchange.com/questions/69765/how-to-reclaim-data-free-in-innodb-tables
http://dba.stackexchange.com/questions/16341/how-do-you-remove-fragmentation-from-innodb-tables
http://dev.mysql.com/doc/refman/5.5/en/tables-table.html
0 0
- What does information_schema.TABLES.DATA_FREE mean in MySQL?
- What does Using filesort mean in MySQL?
- What does "size" in int(size) of MySQL mean?
- What does (Aborted) mean in HttpWatch?
- In Javascript, what does this underscore mean?
- What does ^M character mean in Vim?
- What does “%.*s” mean in printf?
- what does boinc mean?
- what does "ERP" mean
- What does "V"mean
- What does "Vanilla" mean ?
- Discussion1: What does mandatory/required variable in a query mean?
- What does backend type static mean in Magento?
- What does the first letter "c" mean in "calloc"?
- what does mean "Units" in apple app sales report?
- What does the \0 symbol mean in a C string?
- What does the [Flags] Enum Attribute mean in C#?
- What does the distance attribute in DMatches mean?
- Android 5.0上动态布局层级覆盖问题
- Spring+Quartz实现定时任务的配置方法
- Android测试教程8--测试我们的Activity-第1篇
- Discuz X3.2云平台开通地址及方法
- Android 图片优化
- What does information_schema.TABLES.DATA_FREE mean in MySQL?
- Akka性能测试
- android资源目录之drawable
- Unity3d Linux 下面画面撕裂问题
- IOS图层Layer学习笔记(四)—— CAShapeLayer
- 从2-3-4树谈到Red-Black Tree(红黑树) http://www.cnblogs.com/guoyiqi/archive/2011/06/08/2129310.html
- Swift IOS中的常用操作(打开网页、发短信、打电话、发邮件)
- 安装 VS2012/2013 完成时报错“无法建立到信任根颁发机构的证书链”
- MatLab2012b/MatLab2013b 分类器大全(svm,knn,随机森林等)