mysql表碎片的查询自己回收

江南才子

　　在MySQL中，我们经常会使用VARCHAR、TEXT、BLOB等可变长度的文本数据类型。不过，当我们使用这些数据类型之后，我们就不得不做一些额外的工作——MySQL数据表碎片整理。
每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。
　　当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用。
　　

1.或者查看某个表所占空间，以及碎片大小。
select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where TABLE_SCHEMA='test';

或者
select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where data_free !=0;
　　
+------------+--------+------------+--------+-----------+
| table_name | engine | table_rows | length | DATA_FREE |
+------------+--------+------------+--------+-----------+
| curs    | InnoDB |       0 |  16384 |       0 |
| t       | InnoDB |       10 |  32768 |       0 |
| t1       | InnoDB |       9 |  32768 |       0 |
| tn       | InnoDB |       7 |  16384 |       0 |
+------------+--------+------------+--------+-----------+

table_name 表的名称
engine ：表的存储引擎
table_rows  表里存在的行数
data_length 表的大小（表数据+索引大小）
DATA_FREE ：表碎片的大小
以上单位都是byte字节

整理碎片：
整理碎片过程会锁边，尽量放在业务低峰期做操作

1、myisam存储引擎回收碎片
optimize table aaa_safe,aaa_user,t_platform_user,t_user;
2、innodb存储引擎回收碎片
alter table t engine=innodb;
：
1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE运行过程中，MySQL会锁定表。
4.默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE或
　　脚本回收innodb表碎片
　　#!/bin/bash
　　DB=test
　　USER=root
　　PASSWD=root123
　　HOST=192.168.2.202
　　MYSQL_BIN=/usr/local/mysql/bin
　　D_ENGINE=InnoDB
　　$MYSQL_BIN/mysql -h$HOST -u$USER -p$PASSWD $DB -e "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='"$DB"' "';" | grep -v "TABLE_NAME" >tables.txt
　　for t_name in  `cat tables.txt`
　　do
　　echo "Starting table $t_name......"
　　sleep 1
　　 $MYSQL_BIN/mysql -h$HOST -u$USER -p$PASSWD $DB -e "alter table $t_name engine='"$D_ENGINE"'"
　　 if [ $? -eq 0 ]
　　then
　　 echo "shrink table $t_name ended." >>con_table.log
　　sleep 1
　　else
　　echo "shrink failed!" >> con_table.log
　　fi
　　done

　　

[MySQL] mysql表碎片的查询自己回收

相关帖子

MySQL Server 层和存储引擎层是怎么交互数据的？

MySQL让人又爱又恨的多表查询

MySQL存储过程图文实例讲解

MySQL由浅入深掌握连接查询

MySQL数据库触发器从小白到精通

一篇带给你MySQL索引知识详解

MySQL视图和索引专篇精讲

阿里面试MySQL死锁问题的处理

mysql中drop、truncate与delete的区别详析

风冷散热器怎么看好坏？

江南才子 LV4