高性能MySql进化论(三):ID(标示符)的选择_MySQL

高性能MySql进化论(三):ID(标示符)的选择

在设计数据库表结构的时候，通常情况下每张表结构都有一个字段作为ID，因为 ID会被用来做查询，JOIN,FK等操作，所以ID设计的好坏对性能的影响很大。

在为ID选择合适的类型的时候不仅需要考虑这种类型在数据库中存储所占用的空间，还需要考虑该类型在计算或者是值比较时的特性，例如BIT类型存储的时候是二进制的形式，而在数字计算的上下文时，会被转换成对应的十进制形式。

对ID进行JOIN操作或者是被用来作为其他表的FK时，涉及的字段类型要保持完全的一致，即使类型都是整数,Unsigned/Signed这样的微小细节也要保持一致，否则在性能方面可能会有意想不到的性能问题，这种问题往往又是难以被发现的。如果使用的Storage Engine 是InnoDB，则可以有效的防止关联字段类型不匹配的问题，如果类型不匹配则会抛出 异常“ERROR 1005 (HY000): Can’t createtable”(VARCHAR(10)和VARCHAR(20)会被认为类型是完全相同的)。

创建ID时，要遵守“尽量最小类型原则”(在满足以后需求变化的情况下，要选择占用存储空间最小的类型),例如 有一张存储全国省份的表，对于这张种需求而言，TINYINT比INT更适合作为ID的类型，虽然选用TINYINT只是节省了3个BYTE, 但是在进行JOIN操作或者是作为FK时，性能可能会有很大的提升。

实际的应用开发中，ID的类型往往是多样的，例如INT,VARCHAR是比较常用的两种类型，但是有些情况下也可以使用ENUM作为ID的类型，下面的内容分别对这几种类型进行描述

整数类型

因为整数类型是一种简单的类型，对比于字符串它的处理更简单速度更快，而且它还支持AUTO_INCREMENT特性，所以一般情况下它是作为ID的首选类型

字符串

因为MySQL处理字符串要比整数消耗更多的存储/内存空间，而且速度也相对较慢，所以应该尽量避免使用字符串作为ID的类型。

在无法避免字符串作为ID的类型时，往往会采用“随机值”的策略来生成ID的值，常用的方法包括UUID，MD5, SHA1.在这种情况下需要额外注意，因为随机生成的ID值可能会带来以下三个问题：

(1) 在执行INSERT语句时意味着索引值会被任意写到索引表的不同位置，这种对于磁盘的随机访问行为，将会导致插入操作的变慢，

(2) 在执行SELECT操作时，因为逻辑相关的索引也被会被任意写到索引表的不同位置，也会导致查询的变慢。

(3) 对于数据库提供的CACHE机制而言，这种随机值的策略产生的影响也是比较大的。因为CACHE往往是基于“热区”的原理来实现的，比如说索引表中有100个索引，你执行了多次的查询的结果都是索引表中的20-30这部分的索引，那么 MySQL Serve为了提高查询效率会把20-30这部分索引的值放到CACHE中，如果你又有一些查询的结果是在30-40 之间的，那么Server会对CACHE进行更新。如果是基于随机的索引，意味着“热区”的区间将会很大，这将导致CACHE的命中率受到影响，而且也会导致CACHE的不断刷新。

如果存储UUID 值，则应该移除“-”符号；或者更好的做法是，用UNHEX() 函数转换UUID 值为16 字节的数字，并且存储在一个BINARY(16) 列中。检索时可以通过HEX()函数来格式化为十六进制格式。

枚举

对于ID来说，EMUM 和SET 类型通常是一个糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题的。ENUM 和SET 列适合存储固定信息，例如有序的状态、产品类型、人的性别。没有特殊需求，应该放弃这个选择