Schema与数据类型优化的方法

共计 5091 个字符，预计需要花费 13 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章主要介绍 Schema 与数据类型优化的方法，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

schema 就是数据库对象的集合，这个集合包含了各种对象如：表、视图、存储过程、索引等。为了区分不同的集合，就需要给不同的集合起不同的名字，默认情况下一个用户对应一个集合，用户的 schema 名等于用户名，并作为该用户缺省 schema。所以 schema 集合看上去像用户名。

如果把 database 看作是一个仓库，仓库很多房间（schema），一个 schema 代表一个房间，table 可以看作是每个房间中的储物柜，user 是每个 schema 的主人，有操作数据库中每个房间的权利，就是说每个数据库映射的 user 有每个 schema（房间）的钥匙。SQL server 和 Oracle mysql 有别

4.1 选择优化的数据类型原则：

1、更小的通过更好，尽量使用可正确存储数据的最小的数据类型（占更少的磁盘 内存 CPU 缓存，处理时需要 CPU 周期更少：更快），但能罩得住数据，存不下就尴尬了

2、简单就好：简单类型（更少 CPU 周期），使用 MySQL 内建类型存时间，整型存 ip，整型较字符代价低（字符集和校对排序规则使字符较复杂）

3、尽量避免 null：最好指定为 not null

*）null 列使用更多的存储空间，mysql 里需要特殊处理

*）null 使索引、索引统计和值比较更复杂；可为 null 的列被索引时，每个索引记录需额外的字节

例外：InnoDB 使用单独位 bit 存储 null,so 对于稀疏数据（很多值为 null）有很好的空间效率，不适合 MyISAM

4.1.1 整数类型【参考】整数 whole number

tinyint(8 位存储空间)  smallint(16)  mediumint(24)   int(32)    bigint(64)

1、存储值的范围：，N 是存储空间的位数

2、unsigned：可选、不容许负值，可使正数的上限提高一倍：tinyint unsigned 0~255，tinyint-128~127

3、有无符号使用相同的存储空间，相同的性能

可为整型指定宽度，例如 INT(11)，对于大多数应用无意义，不会限制值的合法范围，只是规定了交互工具显示字符的个数，对于存储和计算，int（1）和 int（20）是相同的；

实数 real number：带小数

float 和 double，mysql 使用 duble 作为内部浮点计算的类型

decimal：存储精确的小数，mysql 服务器自身实现，decimal(18,9)18 位，9 位小数，9 个字节（前 4 后 4 点 1）

尽量只在对小数进行精确计算时才使用（额外的空间和计算开销），如财务数据

数据量大时，考虑使用 bigint 代替，将需要存储的货币单位据小数的位数乘以相应的倍数

浮点：

建议：只指定类型、不定精度（mysql），这些精度非标准，mysql 会悄选类型、或存时对值取舍

存储同样范围的值时，比 decimal 更少的空间，float4 字节存 double8 字节（更高精度范围）

4.1.3 字符串类型 varchar 和 char：

前提：innodb 和 myisam 引擎，最主要的字符串类型

磁盘存储：存储引擎存储的方式与在内存、磁盘上的不能不一样，所以 mysql 服务器从引擎取值需转格式

varchar：

1、存储可变字符串，比定长节省空间（仅使用必要的空间），但如果表使用 row_format=fixed，行会定长存储

2、需使用 1 / 2 额外字节记录字符串长度；1）列 max 长度 =255 字节，1 字节表示，否 2 字节，2）采用 latinl 字符集，varchar（10）列需 11 个字节的存储空间，varchar（1000）1002 字节，2 字节存储长度信息

3、节省存储空间，利于性能；但在 update 可能使行变得比原来更长、需做额外工作

合适的情况：

1）字符串列最大长度比平均长度大很多；2）列的更新少（不担心碎片）；3）使用 UTF- 8 字符串，每个字符均使用不同的字节数存储

char：

1、定长，据长度分配空间，删除 all 末尾空格；长度不够、空格填充

2、存储空间上更有效率，char(1)来存储只有 Y N 的值 1 个字节，varchar2 字节，还有一个记录长度

适合的情况：

1）适合存储很短的字符串；2）或 all 值接近同一个长度；3）经常变更的数据，存储不易碎片

对应空格、存储：

char 类型存储时末尾空格被删；数据如何存储取决于存储引擎，Memory 引擎只支持定长的行（最大长度分配空间）

binary，varbinary：存储二进制字符串，字节码，长度不够、\0 来凑（不是空格）检索时不会去

慷慨不是明智的：varchar（5）和 varchar（100）存储‘hell’空间开销一样，长的列消耗更多内存

blob 和 text：大数据

分别用二进制和字符方式存储，分别属于两组不同的数据类型：字符类型：tinytext、smalltext、text、mediumtext、longtext，对应的二进制类型是 tinyblob、smallblob、blob、mediumblob、longblob，两类仅有的不同：blob 类型存储的是二进制，无排序规则或字符集，text 有字符串 排序规则；

MySQL 会把每个 blob 和 text 当做独立的对象处理，存储引擎存储时会做特殊处理，当值太大，innoDB 使用专门的外部存储区域进行存储，此时每个值在行内需要 1~4 个字节存储一个指针，然后在外部存储实际的值；

mysql 对他们的列排序：只对每列前 max_sort_length 字节排序；且不能将列全部长度的字符串进行索引，也不能使用这些索引消除排序；

如果 explain 执行计划的 extra 包含 using temporary：这个查询使用了隐式临时表

使用 enum 代替字符串类型

定义时指定取值范围，对 1～255 个成员的枚举需要 1 个字节存储；对于 256～65535 个成员，需要 2 个字节存储。最多可以有 65535 个成员，ENUM 类型只能从成员中选择一个；和 set 相似

可把不重复的固定的字符串存储成一个预定义的集合，mysql 在存储枚举时会据列表值的数量压缩到 1 / 2 字节中，在内部会将每个值在列表中的位置保存为整数（从 1 开始，必须进行查找才能转换为字符串，开销、列表小 可控），且在表的.frm 文件中保持“数字 - 字符串”映射关系的“查找表”；

将一个数字存储到一个 ENUM 中，数字被当作为一个索引值，并且存储的值是该索引值所对应的枚举成员：在一个 ENUM 字符串中存储数字是不明智的，因为它可能会打乱思维；ENUM 值依照列规格说明中的列表顺序进行排序。(ENUM 值依照它们的索引号排序。)举例来说，对于 ENUM(a , b) a 排在 b 后，但是对于 ENUM(b , a)，b 却排在 a 之前。空字符串排在非空字符串前，NULL 值排在其它所有的枚举值前。为了防止意想不到的结果，建议依照字母的顺序定义 ENUM 列表。也可以通过使用 GROUP BY CONCAT(col) 来确定该以字母顺序排序而不是以索引值。【源】

排序时安装创建表时的顺序排序的（应该是）；枚举最不好的地方：字符串列表是固定的，添加删除字符串须使用 alter  table；在‘查找表’时采用整数主键避免基于字符串的值进行关联；

4.1.4 日期和时间 datetime：大范围的值 1001 9999 s YYYYMMDDHHMMSS 与时区无关 8 字节

默认，以可排序、无歧义的格式显示 datetime：2008-01-02 22:33:44

timestamp：1970 2038，1970 1 1 以来的秒数，时区 4 字节

from_unixtime 将 unix 时间戳转日期，unix_timestamp 将日期转 unix 时间戳

插入时没有指定第一个 timestamp 列的值，设置为当前时间，插入记录时，默认更新第一个 timestamp 列的值，timestamp 类为 not null，尽量使用 timestamp（空间效率高）；

可以使用 bigint 类型存储微妙级别的时间戳，或 double 存秒之后的小数部分，或使用 MariaDB 代替 MySQL；

4.1.5 位 bit：mysql5.0

前与 tinyint 同义词，新特性

bit（1）单个位的字段，bit（2）2 个位，最大长度 64 个位

  行为因存储引擎而异，MyISAM 打包存储 all 的 BIT 列（17 个单独的 bit 列只需要 17 个位存储，myisam3 字节 ok），其他引擎 Memory 和 innoDB 为每 bit 列使用足够存储的最小整数类型来存放，不节省存储空间；

   mysql 把 bit 当做字符串类型，检索 bit（1）值、结果是包含二进制 0 / 1 的字符串，数字上下文的场景检索，将字符串转成数字，大部分应用，best 避免使用；

set

创建表时，就指定 SET 类型的取值范围：属性名 SET(值 1 , 值 2 , 值 3 …, 值 n)，“值 n”参数表示列表中的第 n 个值，这些值末尾的空格将会被系统直接删除，字段元素顺序 系统自动按照定义时的顺序显示 重复 只存一次。

其基本形式与 ENUM 类型一样。SET 类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET 类型的值最多只能是有 64 个元素构成的组合，根据成员的不同，存储上也有所不同：【参考，同 enum】

1～8 成员的集合，占 1 个字节。9～16 成员的集合，占 2 个字节。17～24 成员的集合，占 3 个字节。25～32 成员的集合，占 4 个字节。33～64 成员的集合，占 8 个字节。

需要保持很多 true、false 值，可考虑合并这些列到 set 类型，在 mysql 内部以一系列打包的位的集合来表示的（有效利用存储空间）且 mysql 有 find_in_set、field 函数，方便在查询中使用；

缺点：改变列的定义代价高，需要 alter table，无法再 set 上通索引查找

在整数列按位操作：

代替 set 的方式：使用整数包装一系列的位：可把 8 个位包装到 tinyint 中，且按位操作来使用，为位定义名称常量来简化这个工作，但是这样查询语句较难写且难理解

4.1.6 选择标识符 identifier 标识列：自增长列【源】

1）可不用手动插入值，系统提供默认序列值;2)不要求和主键搭配 ; 3)要求是 unique key；

4）一个表最多一个；5）类型只能是数值；5）可通过 set auto_increment_increment=3;

选择标识列类型时

考虑存储类型、mysql 对这种类型怎么执行计算和比较，确定后确保在 all 关联表中使用 same 类型，类型间要精确匹配；

技巧：

1、整数类型：整数通常最好的选择，很快且可使用 auto_increment

2、enum 和 set 类型，存储固定信息

3、字符串：避免，耗空间较数字慢，myisam 表特别小心（默认对字符串压缩使用、查询慢）

1）完全“随机”字符串 MD5/SHA1/UUID 函数生成的新值 会任意分布在很大的空间内，导致 insert 及部分的 select 变慢：插入值随机的写到索引的不同位置，insert 变慢(页分裂 磁盘随机访问 聚簇索引碎片）；select 变慢、逻辑上相邻的行分布在磁盘和内存不同的地方；随机值导致缓存对 all 类型的查询语句效果都变差（使缓存赖以工作的访问局部性原理失效）

聚簇索引，实际存储的循序结构与数据存储的物理结构一致，通常来说物理顺序结构只有一种，一个表的聚簇索引也只能有一个，通常默认都是主键，设置了主键，系统默认就为你加上了聚簇索引；【源】

非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；

2）存储 uuid，移除 - 符号，或者用 unhex 转换 uuid 值为 16 字节的数字，且存储在 binary（16）列中，检索时通过 hex 函数格式化为 16 进制格式;

UUID 生成的值与加密散列函数 (sha1) 生成的值不同特征：uuid 分布不均匀，有一定顺序，不如递增整数

当心自动生成的 schema：

严重性能问题，很大的 varchar、关联列不同的类型；

orm 会存储任意类型的数据到任意类型的后端数据存储中，并没有设计使用更优的类型存储，有时为每个对象每个属性使用单独行，设置使用基于时间戳的版本控制，导致单个属性会有多个版本存在；权衡

4.1.7 特殊类型数据：空

以上是“Schema 与数据类型优化的方法”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！

向 AI 问一下细节