MySql优化之路（一）--- 字段的设计和类型选择

一张表的设计的好坏会直接影响到后期的性能问题。一个优秀的程序员应该提前考虑表的设计。

字段设计原则

1.更小的通常更好
一般情况下，应该尽量使用可以正确存储数据的最小数据类型。因为更小的数据类型占用更少的磁盘、内存、和CPU缓存。

2.简单就好
简单的数据类型操作可以减小CPU的使用率。例如整型比字符操作代价更低，因为字符集的排序规则比整型更加复杂。尽量选用mysql的内建类型来存储日期和时间（data,time,datatime）而不是用字符串。

3.尽量避免NULL
null是列的默认属性，通常情况下最好指定列not null，除非业务中需要存储null值。
因为有null值的列使索引、索引统计和值比较都更复杂，如果在这列上建立索引，每个索引记录都需要一个额外的字节。

mysql支持的数据类型

1.整数类型

类型	存储空间（单位：位）
tinyint	8
smallint	16
mediumint	24
int	32
bingint	64

它们取值范围是：
$$-2^{n-1}～2^{n-1}-1$$
n表示存储空间的位数

整数类型有可选的unsigned属性，表示不允许负值。
可以使正数上限提高一倍 如：tinyint的范围是 -128-127 而 tinyint unsigned 是 0～255

2.实数类型

类型	存储空间（单位：位）
float	4
double	8
declmal

float和double用于存储小数类型的数据，但是容易丢失精度。
declmal用于存储精确的小数 如：declaml(18,9) 小数两边将各存9个数字，一共使用9字节：
小数点前的数字使用4字节，小数点后数字使用4字节，小数点占用一个字节。

declmal类型最多允许存65个数字。

3.字符串类型

类型	存储空间（单位：位）
char	可指定长度
varchar	自动增长
blob
text

varchar用于存储可变长的字符串，它比定长类型更节省空间。varcher需要1到2个字节记录字符串的长度：如果字符串的最大长度<=255使用1个字节，否则使用两个字节。
char适合存贮很短的字符串，或者所有值都接近统一长度的的字符串，比如密码的MD5值。
char会自动截断字符串后面的空格，varchar不会。
blob和text都是为了存储很大的数据而设计的。
mysql把每个blob和text当作一个单独的对象处理。当值变的太大时，InnoDB会使用专门的“外部”存储区域来进行存储。此时每个值在行内需要1～4个字节存储一个指针，指向外部存储区域的值。

4.日期和时间类型

类型	存储空间（单位：位）	日期格式
data	4	YYYY-MM-DD
time	3	HH:MM:SS
datatime	8	YYYY-MM-DD HH:MM:SS
timestamp	4	YYYY-MM-DD HH:MM:SS

datatime能保存1001～9999年，精度为秒
timestamp保存了1970～2038年，精度为秒

5.位数据类型
bit ：bit列的最大长度是64位，使用2进制存储。

6.枚举
枚举类型有时候可以代替字符串进行存储。可以很好地进行数据分类。
在建表地时候注意排好字符串的优先级。
数据库会将枚举类型的数据映射到一个整数上
看个例子吧：

CREATE TABLE enum_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    col ENUM('red', 'blue', 'black') NOT NULL
)default charset=utf8;
INSERT INTO enum_test(title, col)
VALUES('红鲤鱼1号', 'red');

INSERT INTO enum_test(title, col)
VALUES('红鲤鱼2号', 'red');

INSERT INTO enum_test(title, col)
VALUES('红鲤鱼3号', 1);

INSERT INTO enum_test(title, col)
VALUES('蓝鲤鱼1号', 'blue');

INSERT INTO enum_test(title, col)
VALUES('蓝鲤鱼2号', 'blue');

INSERT INTO enum_test(title, col)
VALUES('蓝鲤鱼3号', 2);

INSERT INTO enum_test(title, col)
VALUES('黑鲤鱼2号', 'black');

select * from enum_test;

select priority+0 from enum_test;

从查询结果可以看出：enum实际上是以整数类型存储的，所以也可与根据enum进行排序分组等操作。

[参考资料]《高性能MySql(第3版）》