mysql怎么缩小 mysql 缩表

如何压缩Mysql数据库

压缩表从名字上来看，简单理解为压缩后的表，也就是把原始表根据一定的压缩算法按照一定的压缩比率压缩后生成的表。

成都创新互联是一家集网站建设,华容企业网站建设,华容品牌网站建设,网站定制,华容网站建设报价,网络营销,网络优化,华容网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。

1.1 压缩能力强的产品

表压缩后从磁盘占用上看要比原始表要小很多。如果你熟悉列式数据库，那对这个概念一定不陌生。比如，基于 PostgreSQL 的列式数据库 Greenplum；早期基于 MySQL 的列式数据库 inforbright；或者 Percona 的产品 tokudb 等，都是有压缩能力非常强的数据库产品。

1.2 为什么要用压缩表？

情景一：磁盘大小为 1T，不算其他的空间占用，只能存放 10 张 100G 大小的表。如果这些表以一定的比率压缩后，比如每张表从 100G 压缩到 10G，那同样的磁盘可以存放 100 张表，表的容量是原来的 10 倍。情景二：默认 MySQL 页大小 16K，而 OS 文件系统一般块大小为 4K，所以在 MySQL 在刷脏页的过程中，有一定的概率出现页没写全而导致数据坏掉的情形。比如 16K 的页写了 12K，剩下 4K 没写成功，导致 MySQL 页数据损坏。这个时候就算通过 Redo Log 也恢复不了，因为几乎有所有的关系数据库采用的 Redo Log 都记录了数据页的偏移量，此时就算通过 Redo Log 恢复后，数据也是错误的。所以 MySQL 在刷脏数据之前，会把这部分数据先写入共享表空间里的 DOUBLE WRITE BUFFER 区域来避免这种异常。此时如果 MySQL 采用压缩表，并且每张表页大小和磁盘块大小一致，比如也是 4K，那 DOUBLE WRITE BUFFER 就可以不需要，这部分开销就可以规避掉了。查看文件系统的块大小：

root@ytt-pc:/home/ytt#  tune2fs -l /dev/mapper/ytt--pc--vg-root  | grep -i 'block size'Block size:               4096

1.3 压缩表的优势

压缩表的优点非常明显，占用磁盘空间小！由于占用空间小，从磁盘置换到内存以及之后经过网络传输都非常节省资源。

简单来讲：节省磁盘 IO，减少网络 IO。

1.4 压缩表的缺陷

当然压缩表也有缺点，压缩表的写入（INSERT,UPDATE,DELETE）比普通表要消耗更多的 CPU 资源。

压缩表的写入涉及到解压数据，更新数据，再压缩数据，比普通表多了解压和再压缩两个步骤，压缩和解压缩需要消耗一定的 CPU 资源。所以需要选择一个比较优化的压缩算法。

1.5 MySQL 支持的压缩算法

这块是 MySQL 所有涉及到压缩的基础，不仅仅用于压缩表，也用于其它地方。比如客户端请求到 MySQL 服务端的数据压缩；主从之间的压缩传输；利用克隆插件来复制数据库操作的压缩传输等等。

从下面结果可以看到 MySQL 支持的压缩算法为 zlib 和 zstd，MySQL 默认压缩算法为 zlib，当然你也可以选择非 zlib 算法，比如 zstd。至于哪种压缩算法最优，暂时没办法简单量化，依赖表中的数据分布或者业务请求。

如何收缩Mysql的ibdata1文件

修改表引擎

1.对每个InnoDB表执行 ALTER TABLE table_name ENGINE=MyISAM;

2.停止Mysql服务；

3.移除InnoDB相关文件ibdata1等；

4.修改my.cnf中的参数，添加innodb_file_per_table；

在my.cnf中[mysqld]下设置

innodb_file_per_table=1

5.启动Mysql服务；

6.将刚才修改后的那些表改回InnoDB：ALTER TABLE table_name ENGINE=InnoDB；

导出InnoDB表

1.使用mysqldump命令导出所有的InnoDB表，例如: mysqldump –add-drop-table –extended-insert –disable-keys –quick ‘db_name’ –tables ‘tbl_name’ ‘db_name.tbl_name.sql’

2.删掉这些表：

◦SET FOREIGN_KEY_CHECKS=0;

◦DROP TABLE db_name.tbl_name;

◦DROP TABLE db_name1.tbl_name1;

◦–– DROP other tables here…

◦SET FOREIGN_KEY_CHECKS=1;

3.停止Mysql服务；

4.移除InnoDB相关文件ibdata1等；

5.修改my.cnf中的参数，添加innodb_file_per_table；

6.启动Mysql服务；

7.在Mysql Console下导入表：

◦SET FOREIGN_KEY_CHECKS=0;

◦SOURCE db_name.tbl_name.sql;

◦SOURCE db_name1.tbl_name1.sql;

◦–– SOURCE other files here…

◦SET FOREIGN_KEY_CHECKS=1;

导出整个数据库

这个是我常用的，虽然他和耗磁盘和时间，但是确实是最简便的：

1.导出所有的数据： /usr/bin/mysqldump ––extended-insert ––all-databases ––add-drop-database ––disable-keys ––flush-privileges ––quick ––routines ––triggers all-databases.sql

2.停止Mysql服务；

3.重命名mysql数据文件夹；

4.修改my.cnf中的参数，添加innodb_file_per_table；

5.mysql_install_db重新初始化mysqld；

6.开启Mysql服务;

7.进入Mysql Console执行：

◦SET FOREIGN_KEY_CHECKS=0;

◦SOURCE all-databases.sql;

◦SET FOREIGN_KEY_CHECKS=1;

8.重启数据库测试OK就领赏去吧。

如果因为断电或者直接关机导致idb文件出错，就需要重构这些文件

[mysqld]

加 innodb_force_recovery=1

我的Dz论坛提示mysql数据库超过50兆。 1.一般推荐多少兆？ 2.如何缩小它占用空间。

对于discuz论坛来说50M的数据库很小了，等你帖子有几十万了，数据库几个G都很正常，只要数据库够用就可以

别缩小占用空间，过度优化会有很多的错误的

如果数据库超过10G，可以考虑站库分离或者云数据库

mysql缩小字段长度

缩小一个字段的长度：altertable表名modifycolumn字段名类型。如：demo表里的test字段，原来长度是100个字符，现长度要改成10个字符altertabledemomodifycolumntestvarchar(10)。这样就可以设置完成了。

如何对Mysql 数据表压缩

633M -rw-rw---- 1 mysql mysql 632M Oct 25 17:51 url_comment_0.ibd 12K -rw-rw---- 1 mysql mysql 8.7K Oct 25 18:16 url_comment_0.frm 178M -rw-rw---- 1 mysql mysql 178M Oct 25 18:53 url_comment_0.MYD 99M -rw-rw---- 1 mysql mysql 98M Oct 25 18:53 url_comment_0.MYI结论:由上面数据可知innodb plugin能有效压缩innodb数据文件,近50%,另外相同的情况下使用MyISAM表也可较大的减少数据大小(178+99633M). 当然实际的压缩比例和表的结构等有关,如字段为varchar会有较大的压缩比,而int类型压缩率会低些~