高性能Mysql学习日志之自增列、mysql本身的缺陷以及应对措施（六）

4.1.6选择标识列（自增列）

标识列，即可以不用手动的插入值，系统提供默认的序列值。为标识列选择适当的数据类型是十分重要的，正确的数据类型会他在查询和连表查询中的性能更加强大。

首先，要确保标识列和以该标识列作为外键的所有数据类型都要一致，否则不仅可能导致性能问题，还有可能导致难以发现的报错，这种错误通常很难以发现。

其次，尽量满足值的范围需求的同时，应该选择最小的数据类型。因为越小的数据类型在连表时的性能越高，而且性能差异是很大的。

再者，我们应该尽量使用整数类型作为标识列，这个最好的选择，因为他们很快，并且可以AUTO_INCREMENT(自增)。应该避免使用ENUM和SET类型，以及字符串类型。使用字符串类型会很消耗空间，并且比数字类型慢得多，尤其在查询时，最多会有六倍的性能下降。在使用随机的字符串作为标识列时（例如MD5()、UUID()），他会导致生成的值分布在很大的一个空间中，导致查询和插入都十分慢。因为插入时，插入的值会随机写到索引的各个不同位置，导致磁盘随机访问、产生索引碎片、导致页分裂等问题，在查询时，因为逻辑相邻的行随机分布在磁盘和内存的不同位置，查询起来非常缓慢。（如果非要使用UUID，则应该移除“-”符号；或者更好的做法，可以通过UNHEX()函数转换UUID为16位字节的数字，然后存储在一个BINARY(16)的列中。检索时，可以通过Mysql提供的HEX()来格式化为十六进制的UUID）。

4.1.7特殊类型数据

某些类型的数据并不直接和内置的类型一致，比如低于秒级精度的时间戳，或者一个IPV4的地址。（大多数人通常使用varchar(15)来存储IP地址，其实，IP地址实质是一个32位无符号整数，而不是字符串，我们可以使用Mysq提供的INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换）

4.2Mysql表设计中的陷阱

在mysql中，也有些性能问题是由于mysql的实现机制导致的，因此我们要避免这些错误导致的性能问题。

1）太多的列：mysql的存储引擎API工作时，会通过行缓冲的格式拷贝数据，然后将缓冲内容解码成各个列，从行缓冲中将编码过的列转换成行数据结构的操作代价是很高的，转换的代价依赖于列的数量。因此，当你列的数量很长时，会导致占用很高的CPU占用。

2）太多的关联：如果希望查询执行得快速且并发性好，则单个查询最好控制在12张表以内作关联。

3）注意防止过度使用枚举。尤其当你需要在你的枚举中增加一个类型时，都需要alter table操作，这个操作是一种阻塞的操作。

4）避免使用NULL值，但是也不要走极端：之前说过，避免使用NULL的各种好处，我们可以使用空字符串或者0等来替代NULL值。但是，当确实有未知值时也不要害怕使用它。例如使用-1代表一个未知整数，使用0000-00-00 00:00:00代替未知时间，这会使代码变得复杂很多。容易产生bug。因此有时候使用NULL会比它的代替方案更好。