为标识列（identifier column）选择合适的数据类型非常重要。一般来说更有可能用标识列与其他值进行比较（例如，在关联操作中），或者通过标识列找其他列。标识列也可能在另外的表中作为外键使用，所以为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型（在相关的表中使用相同的数据类型是个好主意，因为这些列很可能在关联中使用）。当选择标识列的类型时，不仅仅需要考虑存储类型，还需要考虑MySQL对这种类型怎么执行计算和比较。例如，MySQL在内部使用整数存储ENUM和SET类型，然后在做比较操作时转换为字符串。
一旦选定了一种类型，要确保在所有关联表中都使用同样的类型。类型之间需要精确匹配，包括像UNSIGNED这样的属性。混用不同数据类型可能导致性能问题，即使没有性能影响，在比较操作时隐式类型转换也可能导致很难发现的错误。这种错误可能会很久以后才突然出现，那时候可能都已经忘记是在比较不同的数据类型。在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。例如有一个state_id列存储美国各州的名字，就不需要几千或几百万个值，所以不需要使用INT。TINYINT足够存储，而且比INT少了3个字节。如果用这个值作为其他表的外键，3个字节可能导致很大的性能差异。下面是一些小技巧。

整数类型

整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREAMENT。

ENUM和SET类型

对于标识列来说，ENUM和SET类型通常是一个糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题的。ENUM和SET列适合存储固定信息，例如有序的状态、产品类型、人的性别。
举个例子，如果使用枚举字段来定义产品类型，也许会设计一张以这个枚举字段为主键的查找表（可以在查找表中增加一些列来保存描述性质的文本，这样就能够生成一个术语表，或者为网站的下拉菜单提供有意义的标签）。这时，使用枚举类型作为标识列是可行的，但是大部分情况下都要避免这么做。

字符串类型

如果可能，应该避免使用字符串类型作为标识列，因为他们很消耗空间，并且通常比数字类型慢。尤其是在MyISAM表里使用字符串作为标识列时要特别小心。MyISAM默认对字符串使用压缩索引，这会导致查询慢很多。在我们的测试中，我们注意到最多有6倍的性能下降。
对于完全“随机”的字符串也需要多加注意，例如MD5()、SHA1()或者UUID（）产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢：

  • 因为插入值会随机地写到索引的不同位置，所以使得INSERT语句更慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。
  • SELECT语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
  • 随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问局部性原理失效。如果这个数据集都一样的“热”，那么缓存任何一部分特定数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中。

如果存储UUID值，则应该移除“-”符号；或者更好的做法是，用UNHEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY(16)列中。检索时可以通过HEX()函数来格式化为十六进制格式。
UUID()生成的值与加密散列函数例如SHA1()生成的值不同的特征：UUID值虽然分布也不均匀，但还是有一定的顺序的。尽管如此，但还是不如递增的整数好用。

笔者注：实际开发中，通常使用整数类型的id，并使用AUTO_INCREAMENT自增长。

作者：一任天然

来源：CSDN
原文：https://blog.csdn.net/yirentianran/article/details/79347019
版权声明：本文为博主原创文章，转载请附上博文链接！