数据类型
简单的原则:
1)更小的通常最好
why:更小的数据类型会占用更小的磁盘,内存和CPU缓存,会产生更小的索引,处理时CPU周期更少。
2)简单就好
整数好于字符串。
why:整型比字符操作代价更低,因为字符集的排序规则使字符比较比整型比较更复杂。
eg:使用MySQL内建类型(内部为整型)存储时间;使用整型存储IP地址。
3)尽量避免NULL值
如何存储NULL值,索引如何处理NULL值?
选择数据类型时,先选择大类型,再选择具体类型。
1、整数类型
TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
可选属性:UNSIGNED
2、实数类型
FLOAT、DOUBLE、DECIMAL
比较:FLOAT和DOUBLE使用CPU的原生浮点运算,运算更快,但是计算不精确。DECIMAL支持更大的范围,精度高。
怎么避免浮点存储运算的不精确和DECIMAL精确计算代价高的问题?使用BIGINT
3、字符串类型
VARCHAR与CHAR比较:
1)VARCHAR在以下情况是适合的:字符串列的最大长度比平均长度大得多;列的更新少,碎片不是问题;使用了UTF-8等复杂字符集,每个字符使用不同的字节数。
2)CHAR在以下情况是适合的:很短的字符串;所有的值都接近同一个长度;经常变更的数据。
3)存储CHAR值时,MySQL服务器层会删除所有的末尾空格,所以取出时会发现空格不在了。VARCHAR不会存在这种问题。
4)用VARCHAR(5)和VARCHAR(200)存'hello'时空间开销一致,使用更短的列有什么优势?可变字符串在临时表和排序时可能导致悲观的按最大长度分配内存。
BINARY和VARBINARY:
1)用于二进制数据,存储的是字节码而不是字符
2)MySQL填充BINARY采用的是\0而不是空格,在检索时不会去掉填充值。
BLOB和TEXT
1)用于存储很大的数据,分别采用二进制和字符方式存储。
2)MySQL对BLOB和TEXT列进行排序时怎么样的?
3)MEMORY不支持BLOB,TEXT类型。所以查询使用了BLOB或TEXT列并且需要使用隐式临时表时,将不得不使用MyISAM磁盘临时表。怎么办?避免使用这些类型,没法避免的话用SUBSTRING(column,length)
ENUM
1)可用于代替常用的字符串类型,原理是在表文件.frm中保存"数字-字符串“的映射关系。
2)优点?压缩字节;列转换为枚举后,关联更快;字符串主键转换为枚举后,InnoDB会使非主键索引更小。
3)缺点?添加或删除枚举值时必须使用ALTER TABLE。
4)CHAR/VARCHAR与枚举进行关联时的性能?关联时尽量使用相同类型的列。
4、日期和时间类型
DATETIME:8个字节,不依赖时区,范围为1001-9999年。
TIMESTAMP:4个字节,会根据时区变化,范围为1970-2038年。尽量使用。
5、位数据类型
BIT :避免使用
SET:小心使用
6、主键的数据类型
整数类型:最好
ENUM和SET类型:通常是糟糕的选择。
字符串类型:避免,消耗空间,慢。“随机”字符串。
表设计陷阱
1)太多的列。为什么?主要是因为转换代价,存储引擎层提供行缓存给服务器层,服务器曾需要将编码过的列转换成行数据结构,对于MyISAM的变长行和InnoDB的行总是要转换的。
2)太多的关联。
3)过多使用枚举。因为修改枚举列表时,会做一次ALTER TABLE操作。
4)列类型默认可为NULL,应尽可能的避免为NULL,用空格,0,特殊值能代替吗?
范式和反范式
范式:每个事实数据会出现并且只出现一次
反范式:信息是冗余的。
范式的优缺点?
1)优:更新比反范式快。很少需要DISTINCT或GROUP BY。只需修改更少的数据。表很小能放在内存中,执行更快。
2)缺:需要关联
反范式的优缺点?
1)优:避免关联。
2)缺:
应该根据实际情况,混用范式和反范式。
缓存表和汇总表
缓存表:存储那些可以比较简单地从其他表获取(但是每次获取的速度比较慢)数据的表,逻辑上冗余。
汇总表:使用GROUP BY语句聚合数据的表,逻辑上不冗余。
加快ALTER TABLE操作的速度
修改表大部分情况下,会锁住表并且重建整张表。怎么改进?一是在备库修改,再切换为主库;二是“影子拷贝”。