选择优化的数据类型

  1. 更小的通常更好
    1. 占用更少的磁盘、内存和CPU缓存。处理时需要的CPU周期也更少。
  2. 简单就好
    1. 应该用MySQL内建的时间类型而非字符串来存储日期和时间。
    2. 应该用整型存储IP地址。
  3. 尽量避免null
    1. 最好指定列为NOT NULL，除非真的需要存储NULL值。
    2. 可为NULL的列使得索引、索引统计和值比较都更复杂。
    3. 可为NULL的列会使用更多的存储空间，在MySQL里需要特殊处理。
    4. 可为NUll的列被索引时，每个索引需要一个额外的字节。
    5. 如果计划在列上建索引，应该尽量避免设计成可为NULL的列。

      整数类型

      TINYINT/SMAILLINT/MEDIUMINT/INT/BIGINT，分别使用8/16/24/32/64位存储空间。它们可以存储的值的范围从-2（N-1）到2（N-1）-1，其中N是存储空间的位数。

MySQL可以为整数类型指定宽度，例如INT(11)，它不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

整数类型可以选择UNSIGNED属性，表示不允许负值。这可以使正数的范围提高一倍。例如TINYINT UNSIGNED 可以存储 0~255，而TINYINT 的存储范围是-128~127。

实数类型

  • 浮点类型：
    • FLOAT
    • DOUBLE

  • 精确类型

    • DECIMAL。DECIMAL只是一种存储格式，在计算中DECIMAL会转换成DOUBLE类型。

  • CPU直接支持原生浮点计算，所以浮点类型计算更快。

  • CPU不支持对DECIMAL的直接计算，所以MySQL5.0以上的版本中，MySQL服务器自身实现了DECIMAL的高精度计算。
  • 浮点类型在存储相同范围的数据时，通常比DECIMAL使用更少的空间。
  • 尽量只在对小数进行精确计算的时候，才使用DECIMAL，因为DECIMAL需要更多的存储空间和计算资源。

字符串类型

varchar

vharchar类型用于存储可变长字符串。比定长类型更节省空间。

  • MySQL的表如果使用ROW_FORMAT=FIXED创建的话，那么它的每一个行都会使用定长存储，这会很浪费空间。
  • varchar需要额外的1个或者2个字节记录字符串的长度注1。
  • 缺点：因为行是变长的，如果在update的时候，可能导致行的长度比原来长，这就可能导致MySQL需要做一些额外的工作。例如InnoDB需要分裂页，从而使得行可以放进页内。
  • 适用场景：字符串列的最大长度比平均长度大得多；列的更新很少，碎片不是问题；使用了像UTF-8这样的复杂字符集，每个字符都使用不同的字节数进行存储。

  • InnoDB可以把过长的VARCHAR存储为BLOB。

    char

    char类型是定长的。

  • 存储char值时，MySQL会删除所有的末尾空格；

  • 适用场景：
    • 存储很短的字符串；
    • 所有值的长度都接近；
    • 对于经常变更的数据，用char比varchar好，因为char不容易产生碎片；
    • 对于非常短的列，例如char(1)需要一个字节，varchar(1)需要2个字节，因为有1个额外的字节存储长度。

注1：字符串的长度定义是字符数。而不是字节数。

BLOB

TEXT

ENUM

日期和时间类型

DATETIME

  • 范围：1001年~9999年；
  • 精度为秒；
  • 存储到格式为YYYYMMDDHHMMSS的整数中；
  • 与时区无关；

  • 使用8个字节的存储空间；

    TIMESTAMP

  • 范围：1970~1-1 午夜以来的秒数；1970~2038，范围比DATETIME小很多；

  • 使用4个字节存储空间；
  • TIMESTAMP显示的值依赖于时区；
  • 插入时如果没有指定第一个TIMESTAMP列的值，MySQL会设置这个列的值为当前时间；
  • MySQL会默认更新第一个TIMESTAMP列的值（除非update语句中指定了值）；

建议尽量使用TIMESTAMP，因为他的存储空间效率更高。

位数据类型

BIT

使用BIT列可以在一列中存储一个或者多个true/false值；
BIT(1)定义一个包含1个位的字段，BIT(2)存储2个位；以此类推，BIT列的最大长度是64个位。

建议：避免使用BIT类型，因为MySQL把BIT当做字符串类型，而不是数字类型。在不同的场景中，查询结果容易让人误解。

SET

存储很多true/false值，可以考虑合并这些列到一个SET数据类型。