[toc]
选择数据类型的原则：#
- 越小越好。 如果后续因为业务需求要alter增大范围， 会很耗时。
- 简单数据类型， 比如用整数而不是字符串去存ip。
- 尽量NOT NULL。原因:索引列如果存在null， 会需要一个额外的字节，? ?索引统计也变复杂了。
整数类型：#
- mediumint是24位的， 其他分别是8、16、32、64对应tiny、small、int、big
- 支持unsigned属性
- int(11)不代表用11位存储， 限定只是在客户端工具中只显示11位字符。
实数类型（带小数点的类型）#
- 有时候不一定是为了小数才选实数。 比如选decima而不是bigint是因为decimal可以存储比bigint还要大的数字
- float和double就是标准的16、32浮点运算
- decimal存储精确的小数和精确计算（精确计算只在mysql5.0之后）
- 浮点比decimal要的存储空间更小， decimal(18.9）会用掉9个字节（4小数点前+4小数点后+1小数点本身）
- 只有精确计算才用到decimal， 如果数据太多，为了节省空间， 可以改成用bigint（只要把小数点部分乘10的倍数，去掉小数点即可），毕竟decimal比较耗空间。
字符串类型#
varchar#
- 存储可变长字符串。（例外： 用ROW_FORMAT=FIXED创建的话，varchar的空间会固定）
- 可变长的概念：
char是固定长度的，例如你定义了12，插入了"aa"，后面它会给你补10个空。如果是varchar，那么"aa"就是"aa"不会给你补 - varchar(N)代表的N是最大长度
- 需要1-2个额外字节， 来保存字符串长度
varchar(n）需要的最大存储空间长度= n + (n<=255?1:2) - 2种情况不适合用varchar
- 最大长度比平均长度要大
- 列的更新很少
- varchar(5)相比于varchar(200)的优势： mysql会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或者操作时会特别糟糕？？ 不懂
char#
- char是定长的，因此总是会预留足够的空间
- char存储时，会删除字符串末尾的空格。 即’ abc ‘存入后，会变成’ abc’
- char相比varchar的优势： 字符串很短时， 需要的空间比varchar少(varchar需要额外空间存储长度)
binary 和varbinary#
- 存储的是二进制字符串（字节码，0x77那种）
- binary 采用\0而不是空格来填充
- 二进制字节码比较的速度比字符快
BLOB和TEXT#
- 为了存储超大的数据而设计的字符串类型
- mysql把他们当作独立的对象处理，专门使用外部的存储区域来存储， 内部存储指针。
- BLOB和TEXT的区别： 一个是二进制，一个带有字符集规则
- mysql对这2个类型做排序时， 只对前max_sort_length字节做排序
- 因此不能将这2个类型的字段做索引
枚举enum#
create table enum_test(e ENUM(‘a’,‘b’,‘c’) NOT NULL)
- 实际上存储的不是’a’这个字符串，而是1\2\3 这类数字
- 按照定义顺序分配数字
- 排序时也按照实际数字排序，而不是枚举的字典序
- 枚举作为主键也优于字符串， 毕竟她本质是数字。但是比数字会差一点。
日期和时间类型#
DATETIME#
- 从1001到9999年， 精度为秒
- 与时区无关
- 使用8个字节存储
TIMESTAMP#
- 从1970.1.1至今的秒数
- 只使用4个字节
- 显示依赖时区，服务端、客户端都需要配置时区，然后timeStamp的展示就会不同。
- TIMESTAMP默认为NOT NULL
- 插入时会自动设置这个列的值为“当前时间”
如果想存毫秒怎么办？ 可以使用bigint来存储毫秒级时间戳。
位类型#
通常用于存储acl权限
bit（n)#
- mysql把bit当作字符串而不是数字类型
- 存储00111001并且检索时， 得到的ASCII为57的字符串（即’9’)。 在数字上下文场景却是数字57
- 因为这个特性，慎用bit
set#
- 如果字段内容就是一堆true或者false的位，可以放到set类型种
- 替代方式： 用tinyint类型来替代， 就是比太好理解。
主键的选择#
- 整数是坠好的
- enum和set是糟糕的选择。
- 字符串类型也尽量避免。空间大，检索慢
- 避免随机生成的主键字符串，原因:
- 插入值会随机写到索引的不同位置
- select语句会变慢
- 随机值导致缓存失效( 局部性原理gg）
因此最好不要用string类型的UUID， 而是转为数字。
schema设计时的陷阱#
- 太长的列且列中有变长的字段， 可能会导致转换成行数据结构时要消耗很大的CPU。（从行缓冲种将编码过的列转成行数据的缘故）
- 表的关联表太多会有问题。 建议查询最好在12个表内做关联。
- 防止过度使用枚举。 枚举的缺点:
① 可能会有人搞一个enum(‘-1’,‘0’,‘1’,‘2’)这种误导人的枚举
② 每次要新增枚举必须alter table，会阻塞表 - set中的元素如果每次只能出现一个， 应该改成枚举。
- mysql会在索引中存储null，但是oracle不会。
- 尽量不要用null，而是用空字符串、默认值替代，除非没有可以用的默认值，宁愿用null去引起调用方注意
范式的考量#
范式设计： 每个数据一般只会出现一次，没有冗余或者重复数据
反范式： 与范式相反。
范式的优点：
- 更新操作快，因为重复的记录少
- 表更小，可以更好地放到内存里
- 很少需要做distinct、groupby， 因此一般都是1对1的关系
缺点：
查询时经常需要关联，当关联后的另外一个表需要做条件判断，可能会消耗一定性能
反范式的优缺点和范式正好相反。
查询的缓存#
- 有时候会弄一个叫缓存表或者汇总表的东西， 避免每次查询所有记录来得到一个区间的结果
- 计数器表，如果表里只有1行，可能导致并发效率低。 可以弄100行，然后大家更新时随机更新。 统计结果时直接sum即可
alter table性能问题#
- mysql中， alter table的原理一般是 用新的结构创建一个新表，然后把数据导入到新表中。
- 有一些操作可以只修改表的.frm文件来达到修改结构的作用。
比如 alter column修改默认值， 移除列的auto_increment属性， 或者修改enum、set的常量 （可以通过弄一个新表，然后只修改新表的属性，接着进入数据库后端直接替换tablename.frm文件
分区的限制#
MySQL分区的限制
-
一个表最多只能有1024个分区。
-
MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
-
如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。
-
分区表中无法使用外键约束。
-
MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。