选择优化的数据类型

更小的通常更好

一般情况下，应该尽量使用存储数据最小的数据单类型

尽量使用自带的数据类型

比如保存日期最好用 mysql 内有的日期类型而不是用字符串保存时间，除非有特殊需求。

尽量避免 NULL

MySQL 有些优化只会对 NOT NULL 列执行，当然这个优化性能有限，只是在设计的时候要注意到而已。

当然也有例外，InnoDB 在 bit 数据上使用稀疏矩阵，使用 NULL 能节省空间。

整数类型

整数类型可选 TINYINT，SMALLINT，MEDIUMENT，INT，BIGINT，分别占用 8，16，24，32，64 位存储空间。还可以选择无符号数，可以表示的数字范围翻倍。

MySQL 可以显式指定位数，但是这个在大多数情况下是没有意义的，因为比如说指定 INT(11) 也只是修改了终端显示的位数，不会改变 INT 是 32 位的这个事实。

实数类型

实数是带有小数的数字，FLOAT，DOUBLE 支持浮点数的近似计算。如果需要精确计算需要用到 DECIMAL，它可以指定精度，但是计算比较慢。

字符串类型

字符类型在 MySQL 4.0 版本以及以下指，VARCHAR(10)，指的是 10 字节，只能存放 3 个汉字。

在 MySQL 5.0 版本及以上，指的是 10 字符，能存放 10 个汉字。

VARCHAR

最多能存放 65532 个字符。

VARCHAR 用于存储可变长字符串，它比定长更节省空间，因为只用必要的空间。但是 VARCHAR 需要额外 1 到 2 字节记录长度。但是由于执行 update 操作需要重新计算长度，所以更新会更耗时。

CHAR

定长字符串，最多能存放 255 个字符，无论保存的字符串有多长，占用的硬盘大小都是固定大小。

如何选择合适的字符类型

如果字符串平均长度比最大长度小很多，列更新很少，或者使用了 UTF-8 这样复杂的字符集，这种情况下用 VARCHAR 是合适的。

当保存很短的字符串，或者所有的字符串都接近于一个长度的时候可以考虑用 CHAR。

VARCHAR 和 CHAR 还有一个重要的区别就是 CHAR 会把字符串末位空格截断而 VARCHAR 不会。

BLOB和TEXT

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

因为Memory引擎不支持BLOB和TEXT类型，所以，如果查询使用了BLOB或TEXT列并且需要使用隐式临时表，将不得不使用MyISAM磁盘临时表，即使只有几行数据也是如此。这会导致严重的性能开销。即使配置MySQL将临时表存储在内存块设备上（RAMDisk），依然需要许多昂贵的系统调用。

最好的解决方案是尽量避免使用BLOB和TEXT类型。

枚举类型

有时候一些固定的字符串可以用枚举类型来代替，比如说性别的男女。枚举排序的时候会按照枚举类型的数字值来进行排序，而不是对名字的字符顺序排序的。枚举的映射信息保存在表的 .frm 文件中。

枚举删除或者增加字符串必须使用 ALTER，这对大表是非常不友好的，使用只有确认不会修改这个字段了，才使用枚举类型。

日期和时间

DATETIME

这个类型能保存大范围的值，最大到 9999 年。它把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中。

与时区无关。

TIMESTAMP

时间戳，最大能表示到 2038 年。因为使用比较灵活，使用一般都推荐使用这个

位数据类型

在 MySql 5.0 前，BIT 和 TINYINT 是同一个类型，在 5.0 后，bit 就是另外一种类型了。BIT 列最大长度为 64 位。

MySql 把 bit 作为字符串类型，比如说当检索 ‘00111001’ 的时候，MySql 将检索出来的值转为数字然后返回 ASCII 码对应的字符串。

对于大部分应用，最好不要使用 BIT 类型。如果想要在一个 bit 的存储空间中保存布尔类型，可以使用 CHAR(1)。

选择 id

因为 id 需要读入内存进行查找，如果 id 长度非常长的话，检索会因为增加了磁盘 IO 而慢很多。所以最好要选择满足值的范围下最小的数据类型。

对于标识列来说，整数是最好的选择，因为它很快并且可以使用 AUTO_INCREMENT。

对于字符串类型来说，应该尽量避免用它来作索引，它们比整数慢，而且对于完全随机的字符串来说，他们会写入不同的地方，降低查询和插入的速度。

对于完全“随机”的字符串也需要多加注意，例如MD5()、SHA1()或者UUID()产生的字符串。

这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢：

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

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

UUID()生成的值与加密散列函数例如SHA1()生成的值有不同的特征：UUID值虽然分布也不均匀，但还是有一定顺序的。尽管如此，但还是不如递增的整数好用。

MySql设计中的陷阱

不同的数据库在设计 schema 时会有不同的问题。下面来介绍一下 Mysql 下有特有的错误。

太多的列

MySql 查询的时候会在服务层和存储引擎层之间拷贝数据，列数特别多而查询需要的结果只有一小部分，那么所付出的代价是非常高的。

太多的关联

关联查询的代价是非常高的，如果需要关联最好在 12 个表之内关联。

全能的枚举注意防止过度使用枚举（ENUM）。当需要在枚举列表更新时就要做一次ALTER TABLE操作。ALTER会锁表。

范式化的陷阱

范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在MySQL5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。

设计缓存表和汇总表

我们用术语“缓存表”来表示存储那些可以比较简单地从schema其他表获取（但是每次获取的速度比较慢）数据的表（例如，逻辑上冗余的数据）。而术语“汇总表”时，则保存的是使用GROUPBY语句聚合数据的表（例如，数据不是逻辑上冗余的）。

缓存表保存了衍生的冗余数据，避免了实时计算。比如保存了过去一个小时内的点击量，在一个小时后定时 COUNT 一次即可。

一个比较有用的技巧就是，缓存表和主表使用不同的引擎，比如主表使用 InnoDB，缓存表可以使用 MyISAM，这样可以全文检索，还占用了更小的空间。有时会把整个表导出 MySql，插入到专门搜索系统比如 Luncene 中。（是不是物化视图也可以当作一种缓存表？）

计数器表在更新的时候可能会碰到并发问题，这里有一个小技巧能增加计数的并发能力。比如说要记录一篇文章点击量，点击量可以分裂为多行，当需要统计的时候就将各行的数据累加起来即可。