如何压缩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。至于哪种压缩算法最优，暂时没办法简单量化，依赖表中的数据分布或者业务请求。

什么是存储引擎？

关系数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于Excel的电子表格的形式。有的表简单，有的表复杂，有的表根本不用来存储任何长期的数据，有的表读取时非常快，但是插入数据时去很差；而我们在实际开发过程中，就可能需要各种各样的表，不同的表，就意味着存储不同类型的数据，数据的处理上也会存在着差异，那么。对于MySQL来说，它提供了很多种类型的存储引擎，我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。这篇博文将总结和分析各个引擎的特点，以及适用场合，并不会纠结于更深层次的东西。我的学习方法是先学会用，懂得怎么用，再去知道到底是如何能用的。下面就对MySQL支持的存储引擎进行简单的介绍。

MyISAM

在mysql客户端中，使用以下命令可以查看MySQL支持的引擎。

复制代码代码如下:

show engines

MyISAM表是独立于 *** 作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表明。例如，我建立了一个MyISAM引擎的tb_Demo表，那么就会生成以下三个文件：

1.tb_demo.frm，存储表定义；

2.tb_demo.MYD，存储数据；

3.tb_demo.MYI，存储索引。

MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

1.选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。

2.插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

InnoDB

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户 *** 作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的MySQL 5.6.13版，InnoDB就是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择：

1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。

2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。

3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。

4.外键约束。MySQL支持外键的存储引擎只有InnoDB。

5.支持自动增加列AUTO_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

MEMORY

使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。

一般在以下几种情况下使用Memory存储引擎：

1.目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。

2.如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。

3.存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等 *** 作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在=和<>的 *** 作符中，不适合在<或> *** 作符中，也同样不适合用在order by子句中。

可以在表创建时利用USING子句指定要使用的版本。例如：

复制代码代码如下:

create table users

(

id smallint unsigned not null auto_increment,

username varchar(15) not null,

pwd varchar(15) not null,

index using hash (username),

primary key (id)

)engine=memory

上述代码创建了一个表，在username字段上使用了HASH散列索引。下面的代码就创建一个表，使用BTREE索引。

复制代码代码如下:

create table users

(

id smallint unsigned not null auto_increment,

username varchar(15) not null,

pwd varchar(15) not null,

index using btree (username),

primary key (id)

)engine=memory

MERGE

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除 *** 作，这些 *** 作实际上是对内部的MyISAM表进行 *** 作。Merge存储引擎的使用场景。

对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

ARCHIVE

Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

存储引擎的一些问题

1.如何查看服务器有哪些存储引擎可以使用？

为确定你的MySQL服务器可以用哪些存储引擎，执行如下命令：

复制代码代码如下:

show engines

这个命令就能搞定了。

2.如何选择合适的存储引擎？

（1）选择标准可以分为：

（2）是否需要支持事务；

（3）是否需要使用热备；

（4）崩溃恢复：能否接受崩溃；

（5）是否需要外键支持；

然后按照标准，选择对应的存储引擎即可。

---