Schema的优化和索引 - 选择最佳的数据类型 - 主键的选择

对于主键，选择一个好的数据类型尤为关键。你可能经常需要用这些列和其他做比较以及用这些列查找其他的列。你可能也把它们作为另一些表的外键。因此当你选择主键的数据类型时，应该保持相关表主键类型一致。

 

当选择主键的数据类型，你不仅要考虑存储类型，也要考虑MySQL操作和比较这些类型的表现情况。比如，MySQL内部存储ENUM和SET是作为整型的，但是当在字符串环境下作比较的时候，MySQL会把它们转为字符串。

 

一旦你选择了一个类型，要确定相关表都要使用这个类型。这个类型一定要精确，包括了它的属性，如UNSIGNED。混合不同的数据类型，会引起性能问题。即使不会，在类型比较的时候，也会出现很难发现的错误。在忘记比较不同的数据类型之后，这些错误往往会发生。

 

根据你需要值的范围，选择最小的数据类型的范围。并且要为以后留一些主键增长的空间。一个例子，你使用state_id来存储美国州的名称，你不需要上千或百万的值。因此你不需要使用INT。一个TINYINT就足够了并且它大小为3字节。如果你使用它作为其他表的外键。三字节就能发挥大的作用了。

 

Integer类型

Integer通常来说是主键类型的最佳选择。因为它很快并且可以自增。

 

ENUM 以及SET

通常来说，虽然它们对于表中包含状态或者类型值比较有用，但对于主键并不是一个好的选择。ENUM和SET比较适合存储一些如订单的状态，产品的类型，或者人的性别。

 

如果你使用了ENUM定义了一个产品的类型，你可能要根据唯一的ENUM字段来查找（你可能在这表中添加了产品的描述以及等等产品类型的相关信息）。这种情况下你可能使用ENUM作为主键，但是大多数情况要避免使用。

 

String类型

如果可以，要尽可能避免使用String做为主键。它会浪费许多空间以及处理起来要慢于Integer。当使用MyISAM的表时，使用String要尤为小心。默认的情况下，MyISAM会对String类型的索引进行压缩，这样会使查找变慢。

 

你也要小心使用随机的String.如MD5(),SHA1(),UUID().随机生成的String散列在大的空间中，这会降低插入和一些查询语句的速度。

 

• 降低了INSERT语句，因为在索引中这插入的值会存入随机的位置。这回造成页的分割，随机硬盘访问，造成聚集索引碎片。
• 降低SELECT速度，因为相邻的行被分散在硬盘和内存中。
• 随机值会导致缓存能力的下降。因为它们消除了本地的引用。本地的引用是缓存的工作方式。如果所有的数据都已经“预热”了，把任意数据放到缓存中没有任何的优势并且，如果工作数据集合没有在内存中，缓存就会有很多刷新和查找丢失的现象。

 

如果使用的UUID值，要去掉破折号或者使用UNHEX( )把UUID转为16字节的数并且，把它们存在BINARY(16)的列。你可使用HEX来获得16进制的值。

 

UUID生成的值和其他哈希函数如SHA1()生成的值有所不同。UUID的值分布不均并且稍微有点连续。但是它还是没有整型好。