【Mysql系列文章(四)】InnoDb数据类型解析及建表规约

郝琳
247 阅读10分钟

序

合理的表设计是SQL优化的基础，所以在做SQL优化之前先了解下建表的一些基本原则。

字段类型解析

整数

  1. 支持非负属性UNSIGNED，大致可以使正数的上限提高一倍。
  2. 整数类型的宽度对大多应用没有意义，例如int(10)和int(1)对于mysql存储和计算是没有区别的，只是当指定了zerofiil，返回结果会在值前面填充0返回
  3. 整数的运算速度快且没有精度问题，在某些时候可以通过适当的转换作为存储浮点数的替代方案
  4. IPV4本质上是32位无符号整数，利用INET_ATON将IP转化为数字，INET_NTOA将数字转化为IP。使用BigInt替代字符串存储IP地址，可以进行对应范围查找。

实数

  1. FLOAT、DOUBLE在运算时有精度问题，但是由于因为CPU原生支持浮点数运算，速度比DECIMAL更快，大多时候占用空间更小。
  2. DECIMAL的高精度计算由Mysql服务器完成，相比于浮点运算速度更慢，但精度更高，大多数时候占用空间更大

字符串

VARCHAR(变长字符串)

  1. 当列的最大长度小于等于255时，需要额外1字节，否则需要额外2字节记录字符串的长度。
  2. 会保留原数据末尾的空格。
  3. 由于节省了空间，所以对性能有帮助，但由于行是变长的，当UPDATE为更长的字符串时可能导致InnoDb当前页没有足够的存储空间而发生页分裂。

CHAR(定长字符串)

  1. 不保留原数据末尾的空格。
  2. 因为已经预留足够的存储空间，变更时发生页分裂的概率减小。

注意：字符串长度定义的是字符数而不是字节数。就是说char(10)同样存储10个字符，10个汉字和10个英文字母占用的存储空间是不同的。所以理论上不管VARCHAR还是CHAR更新后都可能导致需要更大的存储空间而发生页分裂。

慷慨是不明智的：能用varchar(5)则不用varchar(200)存储5个字符，因为更长的列将耗费更大的内存，mysql会分配固定大小的内存块来保存内补值，特别当使用临时表时。只分配需要的空间。

Text Blog类型

  1. Mysql只对该类型的列的前max_sort_length字节排序而不是整个字符串
  2. 由于memery不支持blog和text, 如何查询了text列，并且使用临时表，就必须使用myisam磁盘临时抱，将带来严重的性能开销

Binary

  1. Mysql使用\0填充binary,而不是空格，在查询时不会去掉填充值
  2. Mysql把Bit当做字符串类型，而不是数字。在数字运算场景中，结果是位字符串转化成的数字，尽量避免使用位数据类型。

Enum(枚举类型)

  1. 注意事项
    1. mysql内部使用数字存储枚举类型，例如: enum('a', 'b', 'c') 内部存储是整数 1,2,3
    2. 所以用枚举型做数学运算或者排序可能会得到"意外"的结果，其内部都是使用数字来做对应的操作而不是表面显示的字符串。
    3. 使用enum和varchar/char做关联速度会较慢，因为比较时，需要先转化枚举类型为字符串。
    4. 枚举字符串列表是固定的，每次增减字符串都要执行alter table，如果不是在列表末尾进行变更，还需要重建整个表，非常麻烦且耗时。
  2. 优势
    1. 由于枚举类型存储的整数，通过一个映射关系转化为字符串，所以非常节约存储空间。

日期类型

DATETIME

  1. 存储的是格式为YYYYMMDDHHMMSS的整数
  2. 存储范围更大，1001年-9999年，精确到秒
  3. 占用空间更大，8字节
  4. 与时区无关

TIMESTAMP

  1. 存储的是从1970.01.01 00:00:00(格里尼治标准时间)以来的秒数，与UNIX时间戳相同
  2. 存储范围较小，1970年-2038年，精确到秒
  3. 占用空间较小，4字节
  4. 受时区影响
  5. 默认not null

建表规约

  1. 数据类型越小越好 在满足需求的前提下，越小的类型占用空间越小，间接减少磁盘I/O次数。 通常来说，越小的类型运算速度越快。 如果不允许负值，请勾选非负。

  2. 数据类型越简单越好 越简单的类型，比较或运算速度越快。例如：整型优于字符串。

  3. 使用Decimal存储小数 在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。

  4. 如果存储的字符串长度几乎相等，使用char定长字符串类型

  5. 如果字符串字段过长，超过2000，则考虑使用text类型并且独立一张表来存储该字段，使用主键关联，避免影响其他字段索引效率。

  6. 联合查询列选择

    1. 要选择相同类型的字段，类型之间要精准匹配，包括unsigned，字符集等
    2. 尽量选择整型作为标识列，速度更快。

  7. 控制列的数量 Mysql的存储引擎API通过行缓冲格式在引擎层和服务器层之间拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据机构的操作代价非常高，转换的代价依赖于列的数量。所以当一个表的列很多，但是查询的时候可能只会用到少部分列时，这时转换的代价过高，会影响性能。

  8. 适当的冗余字段减少联表，提高查询效率是可行的，但要考虑以下几点

    • 数据一致性
    • 不是频繁更新的字段
    • 不是 varchar 超长字段，更不能是 text 字段

  9. 单表行数超过 1000 万或者单表容量超过 2GB考虑分库分表

  10. 所有字段不允许Allow Null, 必须有默认值。

    NULL在Mysql中是一个特殊的值，在某些场景下可能导致不可预期的结果，例如：

    1. count(字段) 不会计算值为null的行
    2. 对null做算术运算的结果都是null
    3. 不能使用=,<,>这样的运算符
    4. 需要更多的存储空间
    5. NPE问题

  11. 不建议使用外键，外键概念在应用层解决

    1. 外键更新同时触发关联表更新，即级联更新，在分布式，高并发集群中，级联更新是强阻塞，存在数据库更新风暴的风险。
    2. 而且外键影响数据库的插入速度，也影响程序的可阅读行，增加维护的难度

  12. 不建议使用存储过程，存储过程不利于维护，难以调试和扩展，没有移植性

  13. 不建议使用MYSQL分区表

  14. 注意字符集的选择，尽量选择相同的字符集编码，否则查询时可能出现意料之外的情况（例如联表查询时）。同时如果联表操作关联字段的字符集或者排序方式不同，会导致不能使用索引。字符集的继承规则 数据库服务器配置 >> 库 >> 表 >> 列，生效规则（就近原则） 列 > 表 > 库）

附录

各数据类型大小及取值范围

分类 类型 大小（字节） 范围（有符号） 范围（无符号） 说明
整型 TINYINT 1 (-128,127) (0,255)
SMALLINT 2 (-32768, 32767) (0,63535)
MEDIUMINT 3 (-8 388 608, 8 388 607) (0, 16777215)
INT 4 (-2147483648, 2147483647) (0, 4294967295)
BIGINT 8 (-9223372036854775808, 9223372036854775807) (0,18446744073709551615)
小数 FLOAT 4 (-3.402 823 466 E+38, 1.175 494 351 E- 38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0, (1.175 494 351 E-38, 3.402 823 466 E+38) 单精度浮点数
DOUBLE 8 (1.797 693 134 862 315 7 E+308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4E-308,1.7976931348623157E+308) 0, (2.225 073 858 507 201 4 E- 308, 1.797 693 134 862 315 7 E+308) 双精度浮点数
DECIMAL DECIMAL(M,D) ，如果M>D，为 M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 DECIMAL最适合保存准确度要求高，而且用于计算的数据
日期时间类型 DATE 3 1000-01-01 到 9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59' 到 '838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901 到 2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00 到 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值，与时间无关
TIMESTAMP 4 1970-01-01 00:00:00 到 2038-01-19 03:14:07 YYMMDD hhmmss 存储的是UTC时间戳，与时区有关
字符串 CHAR 0-255 0-255 0-255 定长字符串，char(n)当插入的字符串实际长度不足n时， 插入空格进行补充保存。在进行检索时，尾部的空格会被 去掉。
VARCHAR 0-65535 0-65535 0-65535 变长字符串，varchar(n)中的n代表最大列长度，插入的 字符串实际长度不足n时不会补充空格。
TINYTEXT 0-255 0-255 0-255 短文本字符串
TEXT 0-65535 0-65535 0-65535 较长文本字符串
MEDIUMTEXT 0-16777215 0-16777215 0-16777215 长文本字符串
LONGTEXT 0-4294967295 0-4294967295 0-4294967295 极大文本字符串
二进制字符串 TINYBLOB 0-255 0-255 0-255 不超过 255 个字符的二进制数据
BLOG 0-65535 0-65535 0-65535 较长文本的二进制数据
MEDIUMBLOG 0-16777215 0-16777215 0-16777215 长文本的二进制数据
LONGBLOG 0-4294967295 0-4294967295 0-4294967295 极大的二进制数据

系列文章

上一篇：【Mysql系列文章(三)】Mysql Explain详解

avatar
文章
阅读
粉丝