Mysql 磁盘满了？检查是否为碎片导致

友儿

December 30, 2021

917 views

No comments

3447 words

MYSQL

mysql碎片化是什么?

表的存储会出现碎片化，每当删除了一行内容，该段空间就会变为被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。
当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片。
当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。
定期清除碎片有什么好处？
降低访问表时的IO,提高mysql性能,释放表空间降低磁盘空间使用率。
怎么解决mysql碎片(data_free字段即为碎片)?

如何查看某个表的data_free?

-- 第一种换算成MB查看(针对单一表来说)
      SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size,
      CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size,
      CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free,
      CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size
      FROM information_schema.tables WHERE TABLE_NAME = '表名';
-- 直接字节(k)查看(针对单一表来说)
      SHOW TABLE STATUS LIKE '表名';
-- 查看一个数据库连接所有已经产生碎片的表
      SELECT table_schema db, table_name, data_free, engine     
      FROM information_schema.tables 
      WHERE table_schema NOT IN ('information_schema', 'mysql')  AND data_free > 0;
-- MyISAM表清除表碎片
      OPTIMIZE TABLE 表名
-- InnoDB表清除表碎片
      ALTER TABLE 表名 engine=InnoDB

如果要清楚的碎片表已经删除？怎么清楚其碎片？

#使用命令查看mysql安装的位置和配置文件所在的地方
[root@wewerrert ~]# ps -ef|grep mysql
root     1510501       1  0 Dec23 ?        00:00:00 /bin/sh /www/server/mysql/bin/mysqld_safe --datadir=/www/server/data --pid-file=/www/server/data/iZ8vbg5lal7crqjflxzizyZ.pid
mysql    1511162 1510501  0 Dec23 ?        00:05:58 /www/server/mysql/bin/mysqld --basedir=/www/server/mysql --datadir=/www/server/data --plugin-dir=/www/server/mysql/lib/plugin --user=mysql --log-error=iZ8vbg5lal7crqjflxzizyZ.err --open-files-limit=65535 --pid-file=/www/server/data/iZ8vbg5lal7crqjflxzizyZ.pid --socket=/tmp/mysql.sock --port=3306
#关闭mysql
  service mysql stop
#删除datadir目录下的ibdata1、ib_logfile0 ib_logfile1这些文件
  rm -rf  ibdata1 ib_logfile0  ib_logfile1
#移动mysql的启动参数
 mv /etc/my.cnf ./abc
#重新启动mysql 发现磁盘空间释放了
 service mysql start
#备份数据库
#还原数据

进行清楚是需要注意些什么？

MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可(我们现在是每月凌晨4点清理mysql所有实例下的表碎片)。
在OPTIMIZE TABLE运行过程中，MySQL会锁定表。因此，这个操作一定要在网站访问量较少的时间段进行。Engine不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引。
OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作.所以把 Optimize 命令放在程序中是不妥当的,不管设置的命中率多低,当访问量增大的时候,整体命中率也会上升,这样肯定会对程序的运行效率造成很大影响.比较好的方式就是做个shell,定期检查mysql中information_schema.TABLES字段,查看 DATA_FREE 字段,大于0话,就表示有碎片。

下面分享一段使用shell脚本定时清楚mysql碎片的方法

#!/bin/sh  
mysql_user=root   
mysql_pass=123123  
time_log=/opt/time 
databases=/opt/databases  
/usr/bin/mysql -u$mysql_user -p$mysql_pass -e "show databases" | grep -v "Database" > /opt/databases  
sed -i "s/information_schema//" $databases  
sed -i "s/mysql//" $databases  
sed -i "s/test//" $databases  
databases1=$(cat /opt/databases)  
for i in $databases1  
do  
echo "database $i starting" 
tables=$(/usr/bin/mysql $i -u$mysql_user -p$mysql_pass -e "show tables" | grep -v "Tables" > /opt/$i)  
tablelist=$(cat /opt/$i)  
echo "optimize database $i starting" >> $time_log  
echo "$i start at $(date  +[%Y/%m/%d/%H:%M:%S])" >> $time_log  
for list in $tablelist  
do  
echo $list  
/usr/bin/mysql $i -u$mysql_user -p$mysql_pass -e "optimize table $list" 
done  
echo "$i end   at $(date  +[%Y/%m/%d/%H:%M:%S])" >> $time_log  
echo >> $time_log  
done