mysql中如何优化表释放表空间

目录
  • mysql优化表释放表空间
  • mysql空间释放
    • 查询占用空间大小
    • 清理碎片
  • 总结

mysql优化表释放表空间

方法一：optimize table table_name

这是我们经常见到的方法，这里就不在过多介绍。

方法二：alter table table_name engine=engine_name

该方法通常用于切换表的引擎，例如MyISAM转为InnoDB，但是同样适用于释放表空间，只不过切换后的引擎和原来的engine相同罢了。

注意事项

1.这两种方式都适用于我们常用的表，myisam和innodb。

2.优化表的时候会锁表，数据表越大，耗时越长，因此不要在网站忙时进行表优化。

mysql空间释放

今天还原了一个数据库。发现数据量有数十GB，想着看看哪些表的数据量多，于是查询了下。数据还真不少。3000多万行，这儿一张表居然有近1400万数据。....惭愧。以前没做什么处理。

USE information_schema; # 使用数据库元数据;
SELECT table_name,table_rows FROM TABLES WHERE table_schema = 'tjpro' order by table_rows desc; #查询表名和表行数，数据库为tjpro 
SELECT sum(table_rows) FROM tables WHERE table_schema = 'tjpro'; #统计行数
SELECT table_name,table_rows FROM tables WHERE table_schema = 'tjpro' order by table_rows desc limit 100; #查询最多行数的表前100

使用delete删除mysql数据的时候，mysql并没有把数据文件删除，而是将数据文件的标识位删除，没有整理文件，因此不会彻底释放空间。被删除的数据将会被保存在一个链接清单中，当有新数据写入的时候，mysql会利用这些已删除的空间再写入。即，删除操作会带来一些数据碎片，正是这些碎片在占用硬盘空间。 -- 引自mysql

如何整理，清理这些空间呢，请往下看。

查询占用空间大小

这时候，我删除了数据最多的mis_system_data_remind_mas 表(测试库哟)之后。我再次查询该表占用的空间大小。

SELECT TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1048576, TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='dbname' AND TABLE_NAME='tablename（你的表名）';

默认是M为单位。第二栏。约为2.8个GB大小。

  • 然后清理一下。使用：OPTIMIZE TABLE

清理碎片

官方推荐使用 OPTIMIZE TABLE 命令来优化表，该命令会重新利用未使用的空间，并整理数据文件的碎片。

等待的时间有点长。等完成后，再次查询空间：

SELECT TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1048576, TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='dbname' AND TABLE_NAME='tablename（你的表名）';

可以看到，释放的空间约为2.8GB。

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。

赞 (0)

相关推荐

  • 浅谈MySQL表空间回收的正确姿势

    浅谈MySQL表空间回收的正确姿势

    目录 前置说明 问题重现 删除数据原理 数据的复用 哪些操作会造成数据空洞 如何收缩表空间 小结 不知道大家有没有遇到这样的一种情况,线上业务在MySQL表上做增删改查操作,随着时间的推移,表里面的数据越来越多,表数据文件越来越大,数据库占用的空间自然也逐渐增长 为了缩小磁盘上表数据文件占用的空间,我们在最大的一张业务表中用delete命令删除了一半儿的旧数据,删除之后,磁盘上表数据文件并没有缩小,即使删除整张表的数据,文件依然没有变小,这是为什么呢? 本文将详细的分析上述问题,并给出正确回收表

  • Mysql空间清理的几种具体方法

    Mysql空间清理的几种具体方法

    目录 前言 1.查看文件磁盘占用 1.1 查看磁盘空间占用 1.2 查看目录空间占用 2.Binlog日志清理 2.1.定时自动清理Binlog日志 2.2 手动删除Binlog日志 2.3.Slow日志清理 2.4.Error日志清理 3.表清理 3.1.查看表占空间和记录数 3.2 常规表数据清理 前言 在Mysql环境下,常常由于数据磁盘满而导致Mysql故障.下面整理了如何在Mysql环境下做好Mysql的空间清理. 1.查看文件磁盘占用 1.1 查看磁盘空间占用 [root@mysql

  • MySQL数据库表空间回收的解决

    目录 1. MySQL表空间回收 2. MySQL表空间设置 3. MySQL删除数据流程 4. MySQL数据页空洞问题 1. MySQL表空间回收 我们经常会发现一个问题,就是把表数据删除以后发现,数据文件大小并没有变化,这就是标题中所说的MySQL数据库表空间回收问题. 这里,我们还是针对MySQL中应用最广泛的InnoDB引擎展开讨论.一个InnoDB表包含两部分,即:表结构定义和数据.在MySQL 8.0版本以前,表结构是存在以.frm为后缀的文件里.而MySQL 8.0版本,则已经允

  • mysql中如何优化表释放表空间

    目录 mysql优化表释放表空间 mysql空间释放 查询占用空间大小 清理碎片 总结 mysql优化表释放表空间 方法一:optimize table table_name 这是我们经常见到的方法,这里就不在过多介绍. 方法二:alter table table_name engine=engine_name 该方法通常用于切换表的引擎,例如MyISAM转为InnoDB,但是同样适用于释放表空间,只不过切换后的引擎和原来的engine相同罢了. 注意事项 1.这两种方式都适用于我们常用的表,m

  • Mysql中 show table status 获取表信息的方法

    Mysql中 show table status 获取表信息的方法

    使用方法 mysql>show table status; mysql>show table status like 'esf_seller_history'\G; mysql>show table status like 'esf_%'\G; 样例: mysql>show table status like 'esf_seller_history'\G; 1.Name 表名称 2.Engine: 表的存储引擎 3.Version: 版本 4.Row_format 行格式.对于My

  • mysql中各种常见join连表查询实例总结

    mysql中各种常见join连表查询实例总结

    本文实例讲述了mysql中各种常见join连表查询.分享给大家供大家参考,具体如下: 通常我们需要连接多个表查询数据,以获取想要的结果. 一.连接可以分为三类: (1) 内连接:join,inner join (2) 外连接:left join,left outer join,right join,right outer join,union,union all (3) 交叉连接:cross join 二.准备需要演示的表: CREATE TABLE `a` ( `id` int(11) uns

  • MySQL delete删除数据后释放磁盘空间的操作方法

    目录 一.删除表|清空数据表 二.删除表数据 三.优化表空间 当 MySQL 数据库服务删除部分数据后:有些情况下这些数据占用的存储空间会释放掉,有些情况这些存储空间则不会释放.以下是对这种情况的简单说明: 一.删除表|清空数据表 当执行删除数据表或者清空数据表的全部数据操作时,都会释放掉相应的磁盘存储空间. drop table table_name; truncate table table_name; 在MyISAM和InnoDB(innodb_file_per_table)存储引擎中,数

  • MySQL中数据库优化的常见sql语句总结

    MySQL中数据库优化的常见sql语句总结

    目录 1.SHOW ENGINES 2.SHOW PROCESSLIST 3.SHOW STATUS LIKE 'InnoDB_row_lock%' 4.SHOW ENGINE INNODB STATUS 5.SHOW INDEXS 6.ALTER TABLE xx ENGINE = INNODB 7.ANALYZE TABLE 1.SHOW ENGINES 查看执行引擎以及默认引擎. 2.SHOW PROCESSLIST SHOW PROCESSLIST查看当前数据库连接的使用情况,以及各种状

  • MySQL中如何优化order by语句

    order by 查询语句使用也是非常频繁,有时候数据量大了会发现排序查询很慢,本文就介绍一下 MySQL 是如何进行排序的,以及如何利用其原理来优化 order by 语句. 建立一张表: CREATE TABLE `cc4` ( `id` INT(11) NOT NULL, `user_name` VARCHAR(16) NOT NULL, `job` VARCHAR(16) NOT NULL, `company` VARCHAR(16) DEFAULT NULL, PRIMARY KEY

  • 深入了解MySQL中索引优化器的工作原理

    深入了解MySQL中索引优化器的工作原理

    目录 本文导读 一.MySQL 优化器是如何选择索引的 1.MySQL数据库组成 2.MySQL数据库成本计算 二.MySQL查询成本 三.SELECT 执行过程 总结 本文导读 本文将解读MySQL数据库查询优化器(CBO)的工作原理.简单介绍了MySQL Server的组成,MySQL优化器选择索引额原理以及SQL成本分析,最后通过 select 查询总结整个查询过程. 一.MySQL 优化器是如何选择索引的 下面我们来看这张表,SUB_ODR_ID字段创建了相关的 2 个索引,根据我们前面

  • MYSQL中binlog优化的一些思考汇总

    问题 问题1:如何解决事务提交时flush redo log带来的性能损失 WAL是实现事务持久性(D)的一个常用技术,基本原理是将事务的修改记录redo log.redo log顺序追加写入.事务提交时,只需要保证事务的redo log落盘即可,通过redo log的顺序写代替页面的随机写提升数据库系统的性能.但是,该方案必须要求每个事务提交时都将其生成的redo log进行一次刷盘,效率不高. 问题2:binlog和引擎层事务提交的顺序问题 对于单个事务而言,日志写入顺序是先redo log

  • MySQL中show命令方法得到表列及整个库的详细信息(精品珍藏)

    show databases;show tables from db_name; show columns from table_name from db_name;show index from talbe_name [from db_name]; show status;show variables; show [full] processlist;show table status [from db_name]; show grants for user; 除了status,process

  • MySQL中如何在原有的表中增加一列

    目录 MySQL在原有的表中增加一列 MySQL table中增加列的SQL语句 1.增加列 2.示例 总结 MySQL在原有的表中增加一列 表已经建好了,但我们需要再增加一列,使用代码: 注意:如果不加after,那新加的列就在表的最后一列,after是让新加的列在哪一列的后面. alter table emp_bonus add column ename varchar(20) not null after empno; 结果如图: MySQL table中增加列的SQL语句 下面是 Mys

随机推荐