【MySQL数据库】第四章解读：Schema与数据类型优化（上）

前言：

高性能的基石：良好的逻辑、物理设计，根据系统要执行的查询语句设计schema

本章关注MySQL数据库设计，介绍mysql数据库设计与其他关系型数据库管理系统的区别

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）有很好的空间效率，不适合MyISAM4.1.1整数类型【参考】整数whole numbertinyint(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：存储二进制字符串，字节码，长度不够、来凑（不是空格）检索时不会去

慷慨不是明智的：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) 来确定该以字母顺序排序而不是以索引值。【源】

排序时安装创建表时的顺序排序的（应该是）；枚举最不好的地方：字符串列表是固定的，添加删除字符串须使用altertable；在‘查找表’时采用整数主键避免基于字符串的值进行关联；4.1.4日期和时间datetime：大范围的值 1001 9999 s YYYYMMDDHHMMSS 与时区无关 8字节默认，以可排序、无歧义的格式显示datetime：2008-01-02 22:33:44timestamp：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特殊类型数据：空相关文章：

【MySQL数据库】第三章解读：服务器性能剖析（上）

【MySQL数据库】第三章解读：服务器性能剖析 （下）以上就是【MySQL数据库】第四章解读：Schema与数据类型优化（上）的详细内容，更多请关注小潘博客其它相关文章！