MySQL学习 - 数据类型的选择

1. 相似的，数据类型间的，比较

1.1 原则上我们希望你这样去选择数据类型

• 够用的情况下，越小越好
• 使用简单的数据类型:       理论上MySQL内建类型处理起来更加方便，int会比varchar好处理
• 尽量避免使用NULL:          NULL的使用使得MySQL的优化比较难以执行下去
• 在你设计一张表的时候:   你最好先确定大的类型例如int, 然后再去确定小类型如int8

1.2 整数类型

• TINYINT / SMALLINT / ... / BIGINT 消耗的存储空间逐渐变大
• 在它们前面加上 UNSIGNED 标志能进一步将存储能力加大一倍
• 涉及整数计算 最好使用 64 位的 BIGINT
• INT(11) 这种写法大部分情况下都没什么意义
  • INT(11) 存储能力 = INT(1) 
  • INT(11) 计算能力 = INT(1)
  • 在Mysql一些客户端中，有时候INT(11)会只显示11字符

1.3 实数类型

• FLOAT[4] / DOUBLE[8] / DECIMAL(a,b) [ max(a,b) + 2 ] 
  • FLOAT / DOUBLE 是常见的单双精度数据，计算过程由CPU自己实现
  • DECIMAL类型的出现是为了存更加精确的小数。 假设你精确到了一定程度，那么CPU本身是没有实现这种东西的计算过程的。 
    • 因此在MYSQL-5.0以前实际上是由浮点数模拟的DECIMAL计算
      故而可能会损失一些精度
    • 5以后的版本MYSQL自己实现了DECIMAL计算
      但是就快而言，自然是CPU自身实现计算会更快
• MYSQL本身存在一种机制，即使你自身指定了精度，但是实际存储的过程中还是会做出一些取舍，可以说是自己在悄悄优化，所以建议只指定类型，不要指定精度

1.4 字符串类型

• VARCHAR(变长) - 存储的时候仅消耗一些必要的空间，但是会额外消耗1~2字节记录长度
  • 最好不要总是更新VARCHAR
    • 假设变长了，可能导致内存页内没有更多空间存储
    • 假设变短了，可能出现一个无法被利用的内存碎片
  • 字符串长度波动幅度非常大的时候用，能占到便宜
• CHAR(定长)
  • MD5这样的定长度值
  • 因为长度不会变，因此内存利用效率更高
• VARCHAR & CHAR 具体是如何存储的这要看存储引擎怎么决定
• 一些行为诸如TRIM是由MYSQL数据库决定的
• 如果真的按照你说的，VARCHAR只做必要的开销，那么VARCHAR(5) == VARCHAR(200)吗
  • 不是，用内存临时表排序的时候会带出很差的表现

1.5 枚举ENUM类型

• 假设现在有三种字符串A/B/C，在使用ENUM的情况下实际存储的时候会变成1/2/3。在对这一列进行排序的时候，也是按照1/2/3的方式进行排序的。
• 不好的地方:  
  • 排序的时候需要使用FILED( )来指定排序，否则就会按照1/2/3排序
  • 因为ENUM的candidate是固定的，因此但凡想要做出一些修改，最大的可能就是ALTER TABLE
  • 因为实际存储的时候是按照1/2/3存储的，因此转换存在一些开销，好在大部分时候这个映射表并不大，因此开销也不是很大
• 好的地方:
  • 假设某个属性成为了主键的一部分，面临一些做联表的可能，在联表的时候，做过ENUM映射的表，联的时候会比单纯的字符串快很多
  • 在使用 SHOW TABLE STATUS 展示表当前状态的时候使用ENUM会比使用字符串 data_length 更低

1.6 日期时间类型

• DATETIME: 日期+时间
• TIMESTAMP: UNIX时间，开销更小
  • 与时区相关，比如TS=0在美国就会比在英国晚5小时

1.7 标识符 / 索引

• 标识符有点像一个键，可以用来唯一的确定一列
• 最好的选择是使用整数类型作为标识符。 与之相对的是避免使用字符串作为标识符，因为它们会很消耗空间，大部分时候也挺慢
• 避免使用UUID - MD5 - SHA1等随机字符串作为索引，这些值会随意分布在很大空间内，从而导致INSERT SELECT 变慢
  • 所谓的 "随意分布" / "很大空间" 是指我们在索引生成以后，SHA1随机生成的新值很有可能会插入其中，变慢。 带来的其他影响则包含:  页的中间插入 -> 页分裂 / 磁盘随机写入行为
    
  • 完全随机字符串会导致 -> 内容写入到内存块完全随机的地方上，逻辑上相邻的行事实上相距非常远，从而导致SELECT变慢
  • 内存访问上有一种说法叫 "局部性原理"， 内存里总是有一小片很"热"，总是被访问，这种时候我们选择缓存这一小片，下次访问缓存就很有可能直接拿到想要的数据，整体是快的不行。   现在随机数来了，所有地方都是一样"热"，我缓存哪儿？我缓存哪儿对我都没有明显的好处

2. 设计一张表你最好这样 :

(接下来可能会反复补充设计表的一些东西)

2.1 设计表的时候尽量避免这些

• 包含有太多列
• 枚举映射表太长
• 过多使用NULL, 尽量避免使用null
  • 你可以使用一些符号代替 -1,0 都可以
  • 但是使用NULL也代表一定是坏事，如果你的表里有太多不可能发生的数字本身也不好

2.2 范式 & 反范式

• 使用范式
  • 好处: 
    • 如果没有范式这种东西，表内有太多冗余的信息，你不能说他们是错误信息，只是太多余，然而在你尝试更新一个字段的时候，哪怕你忘记更新其中一处，这些冗余信息就会变成错误信息
    • 使用范式的情况下更新属性通常更清爽也更快
    • 范式化的表通常更小，因此能直接把整表塞进内存，查询很快
  • 缺点: 
    • 查询全量信息的时候，面临联表，噩梦
• 使用反范式
  • 好处: 无需关联，快
• 总结
  • 实际上纯范式化的数据库 & 纯反范式化的数据库都只是理论上的说说，生产环境下我认为应该还是酌情，以及结合数据库测试的表现来说
    • 例如你现在有user + message 表， 其中有个字段叫做 account_name_chinese，按照范式这个字段只应该在user表里出现，但是message也会经常需要它，你可以在数据库的测试中的表现下，酌情使用
    • 但是像现在针对 这个字段的更新，你需要更新两张表，这也是你需要考量的点，你会不会经常更新它？ 你能接受一次更新两张表吗？ 
    • 避免这种问题，使用trigger解决这种麻烦事 ( 什么是 trigger )

2.3 该不该用视图

• 什么是视图:  
  • 定义: 一条预先保管好的查询语句，等你需要了在临时帮你查  完整定义
  • 为什么很少见到有人用视图
• 什么是物化视图: 预先计算好的表，会根据一些手段更新

转载于:https://juejin.im/post/5ccbb9276fb9a032196edde2