死磕数据库调优-记在数据库设计中对数据库性能优化的思考

一、根据业务需求选取最合适的字段属性

主流的关系型数据库（如MySQL，Oracle，SQLServer等）都支持大量数据的存取，但在我们的项目中如果不考虑实际业务需求来设计表的字段属性，那么就很可能会造成大量多余的数据存储空间，所以我们在设计数据库表的字段属性时，我们可以将表中字段的宽度设得尽可能小。

在数据库中，字符型的数据是最多的，可以占到整个数据库的80%以上。为此正确处理字符型的数据，对于提高数据库的性能有很大的作用。

例如，我们用char(11)来存储手机号码，char是固定长度的，在不足11位的情况下会在后面补齐空格，而varchar是变长的，11只是最大值，当你存储的字符小于11时，按实际长度存储。通常情况下手机号码的长度是固定在11位的，所以不需要使用varchar(11)来存储，因为在char比varchar效率稍高一些；同样的，我们用char(6)来定义邮政编码这个字段，如果将其设置为char(255)，显然给数据库增加了不必要的空间，甚至使用varchar这种类型也是多余的。

所以如果某个字段的长度总是近似的，如一般在90个到100个字符之间，甚至是相同的长度，此时比较适合采用char字符类型。比较典型的应用就是MD5哈希值。当利用MD5哈希值来存储用户密码时，就非常使用采用char字符类型。因为其长度是相同的。另外，像用来存储用户的身份证号码等等，一般也建议使用char类型的数据。

从碎片角度进行考虑。使用CHAR字符型时，由于存储空间都是一次性分配的。为此某个字段的内容，其都是存储在一起的。单从这个角度来讲，其不存在碎片的困扰。而可变长度的字符数据类型，其存储的长度是可变的。当其更改前后数据长度不一致时，就不可避免的会出现碎片的问题。故使用可变长度的字符型数据时，我们就要时不时的对碎片进行整理。如执行数据库导出导入作业，来消除碎片。

另外varchar在位数相同的情况下会比char多占用一个存储位置。比如char(1)与varchar(1)这两个定义，虽然这两个都只能够用来保存单个的字符，但是varchar要比char多占用一个存储位置。这主要是因为使用varchar数据类型时，会多用1个字节用来存储长度信息。这个管理上的开销char字符类型是没有的。

那既然varchar是变长的，那么varchar(100)和varchar(200)有没有什么区别呢？当然有区别了，虽然它们用来存储90个字符数据的时候其存储空间是相同的。但是对于内存的消耗是不同的。对于varchar数据类型来说，硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的，但是对于内存来说，它是使用固定大小的内存块来保存值，也就是说varchar(200)是使用200个字符空间来保存值。显然，这对于排序或者临时表(这些内容都需要通过内存来实现)作业会产生比较大的不利影响。所以说在内存的操作方式中，varchar是按照最长的方式在内存中进行操作的。比如说要进行排序的时候，varcahr(100)是按照100这个长度来进行的。

我们一般会用tinyint(1)来描述性别而不会直接用char，也不会用int(1)。int类型占4个字节，tinyint占1个字节。int(1)和int(4) 从本身长度还是存储方式上都是一样的，区别就是显示的长度不同，如果列制定了zerofill 就会用0填充显示，int(4)指定后就会显示为0002。tinyint(1)和tinyint(4)没什么区别，存123都能存的下，而如果tinyint(3) zerofill 的话，插入值 12，会存储012，zerofill自动左边补零，它会限制显示长度。

常见的数值类型

常见的字符串类型

tinyint VS enum VS set

enum是一个字符串对象，其值通常选自一个允许值列表中，该列表在表创建时的列规格说明中被明确地列举。

在下列某些情况下，值也可以是空串("") 或 NULL：

如果将一个无效值插入一个 ENUM (即，一个不在允许值列表中的字符串)，空字符串将作为一个特殊的错误值被插入。事实上，这个字符串有别于一个"普通的"空字符串，因为这个字符串有个数字索引值为 0。稍后有更详细描述。

如果一个 ENUM 被声明为 NULL，NULL 也是该列的一个合法值，并且该列的缺省值也将为 NULL 。如果一个 ENUM 被声明为 NOT NULL，该列的缺省值将是该列表所允许值的第一个成员。

每个枚举值均有一个索引值：

在列说明中列表值所允许的成员值被从 1 开始编号。

空字符串错误值的索引值为 0。这就意味着，你可以使用下面所示的 SELECT 语句找出被赋于无效 ENUM值的记录行。

mysql> SELECT * FROM tbl_name WHERE enum_col=0;
NULL 值的索引值为 NULL。

指定为 ENUM("one", "two", "three") 的一个列，可以有下面所显示的任一值。每个值的索引值也如下所示：

枚举最大可以有 65535 个成员值

从上面的enum分析可以看出，enum适合存储表单界面中的“单选值”，下面我们来看看set数据类型。

set是一种多选字符串数据类型，适合存储表单界面的“多选值”。设定set的时候，同样需要给定“固定的几个选项”；存储的时候，可以存储其中的若干个值。

设定set的格式：set("param1","param2","param3",...)
set的每个选项值也对应一个数字，依次是1，2，4，8，16...，最多有64个选项
使用的时候，可以使用set选项的字符串本身（多个选项用逗号分隔），也可以使用多个选项的数字之和（比如：1+2+4=7）

15=1+2+4+8 <=> ‘music,read,swimming,footbal’

7=1+2+4<=> ‘music,read,swimming’

了解了set后，以后我们在做权限管理这一块功能的时候就不需要单建一张权限表，然后在角色表里加一个权限的字段，现在我们直接用set就可以很方便实现一个角色对应多种权限。

那么对于enum和tinyint我们到底用哪一个呢？

个人推荐尽量使用tinyint来代替enum，我们可以用tinyint(1)来代替enum表示性别字段，enum对php这种弱类型来说简直是灾难，而且如果项目以后要做不同数据库的迁移，enum可能会出现一些问题，enum的移植性不如tinyint，维护起来比较麻烦。

参考文献：

MySQL数据库中CHAR与VARCHAR之争：http://tech.it168.com/a2011/0426/1183/000001183173.shtml

MySQL 数据类型：http://www.runoob.com/mysql/mysql-data-types.html