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 

温馨提醒,参考使用前最好先在本地mysql测试一下在用于生产环境

Last modification:February 20, 2024
如果觉得我的文章对你有用,请随意赞赏