几个缩减MySQL以节省磁盘空间的建议

我们在工作中时常会遇到一些客户的TPS\QPS都不太高,但磁盘占用非常大,一旦单实例空间太大，像内存、网络、CPU以及备份都将增加相应的开销。可能仅仅是由于空间不满足使得我们不得不进行扩容，下面的方法提供给大家参考。有则改之无则加勉。

1、表结构设计上

1)

字符集是否遵循了最小化原则?(能用latin的就不用gbk。能用gbk的就不用utf8)

2)

索引上是否有滥用？(根本不使用的字段建索引、不适合建索引的字段建索引、重复建索引或者不能很好的利用前缀索引等)

3)

冗余字段是否太多？(各表中不用的或者字段冗余太多)

4)

不正确的字段类型？(能用1个字节非要用几个字节，像枚举类、状态类比较常见)

5)

将较长的字段或者几个字段组合做为主键？（主键最好用mysql自增）

具体事例如下：

CREATE

TABLE

`class_meta`

(

`class_name`

varchar(128)

NOT

NULL

COMMENT

'类名',

`class_desc`

varchar(2048)

default

''

COMMENT

'类的描述',

`class_status`

char(20)

default

'test1'

COMMENT

'test1,test2',

PRIMARY

KEY

(`class_name`),

UNIQUE

KEY

`cm_cn_uk`

(`class_name`),

KEY

`cm_cd_ind`

(`class_desc`(767)),

KEY

`cm_cs_ind`

(`class_status`),

KEY

`cm_cdcn_ind`

(`class_desc`(767),`class_name`)

)

ENGINE=InnoDB

DEFAULT

CHARSET=latin1

COMMENT='meta信息'

通过上面的表结构能看到如下地方不合适

1、主键与唯一索引明显重复,索引cm_cd_ind与索引cm_cdcn_ind索引重复（这种情况经常出现，大家留意下）

2、cm_cs_ind如果两个状态分布均匀也明显不合适建索引

3、class_desc由于是描述性质的，也不合适建索引

4、最好以自增做为主键，可以减少整表的空间

5、class_status列明显可以用tinyint来存，可以省下19个字节

2、存储内容上

1)

是否将图片、视频、音乐等大数据存储在表中？(表里最好只保留路径而不是实际的文件内容)

3、数据保留上

1)是否有已过期而未删除的数据?(对于无效数据及时清理或者进行历史归档)

4、后期维护上

1)是否对经常删除的表进行维护(optimize

table)

建议:

1、在性能要求不高的case中(并发不太高)，可以考虑使用压缩表。一般压缩率在30%-70%之间，收益非常可观。

2、对于删除非常频繁的表要定期进行优化，使表中碎片减少。提高查询、写入的性能。

3、在表结构设计上，一定要发扬“斤斤计较”的精神，能用1个字节表示的坚决不用2个字节。

4、尽量减少大字段的使用。

ps:经常在跟开发评审表结构的时候，时常会被笑话说DBA太抠门，一点都不大气。当数据量小的时候可能大家不觉得，但当你的数据级到T或者P的时候，哪怕多省几个字节都是非常可观的，给大家简单算笔账，如果我们将一张5亿条记录的表，字段从100个字节降到60个字节(应该很容易做到吧)，那么不算上索引将节约大约18G的空间。

优化数据库的方法

1、选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

2、使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

DELETEFROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL *** 作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）..替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT*FROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，

---