读薄《高性能MySql》(二)Scheme与数据优化

读薄《高性能MySQL》(一)MySQL基本知识
读薄《高性能MySQL》(二)Scheme与数据优化
读薄《高性能MySQL》(三)索引优化
读薄《高性能MySQL》(四)查询性能优化

选择更优的数据类型

当我们设计数据类型的时候应该选择最优的数据类型，因为好的数据类型会使数据库性能提升很多，特别是在使用 ORM 的时候要尤其小心，因为需求的复杂性，ORM 基本上没什么可能会生成最优的类型。

接下来介绍一些通用的数据类型结构。

更小的通常更好

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

尽量使用自带的数据类型

比如保存日期最好用 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 不会。

枚举类型

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

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

MySql设计中的陷阱

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

太多的列

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

太多的关联

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

设计缓存表和汇总表

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

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

计数器表在更新的时候可能会碰到并发问题，这里有一个小技巧能增加计数的并发能力。

比如说要记录一篇文章点击量，点击量可以分裂为多行，当需要统计的时候就将各行的数据累加起来即可。