MySQL--表的设计与优化（单表、多表）
⽂章结构：（1）单表设计与优化；（2）基于单表设计的多表设计原则（含表拆分原则）；（均以实际⽣产开发环境下的环境为基准）
⽂章⽬录：
（1）单表设计与优化
设计规范化表，消除数据冗余（以使⽤正确字段类型最明显）
前三范式
所有字段类型
- 所有字段类型罗列
- 针对常⽤的varchar，我们来思考⼏个问题
- 给出⼏个类型选取建议
适当的冗余，增加计算列：（实际开发中必须思考的点）
索引的设计
主键和外键的必要性（实际项⽬开发的重要取舍）
存储过程、视图、函数的适当使⽤（这些是优化的⽅法，这⼏个后⾯会讲）
传说中的‘三少原则’
分割你的表，减⼩表尺⼨
字段设计原则
（2）基于单表设计的多表设计原则
表关系
⼀对⼀关系
⼀对多关系（多对⼀）
多对多关系
注意
- 外键与索引
- 建⽴关系
分表原则：（涉及分区分表问题探究，以后的篇章再补充实例）
表拆分⽅式
- 垂直切分
- ⽔平拆分（分表，分区）–按表中某⼀字段值的范围划分
- 散列库表（基于hash算法的切分）
在了解完分表了，我们先来理解区分分区与分表吧
表拆分建议：（针对⼤系统）
⼀、单表设计与优化：
（1）设计规范化表，消除数据冗余（以使⽤正确字段类型最明显）：
数据库范式是确保数据库结构合理，满⾜各种查询需要、避免数据库操作异常的数据库设计⽅式。满⾜范式要求的表，称为规范化表，范式产⽣于20世纪70年代初，⼀般表设计满⾜前三范式就可以，在这⾥简单介绍⼀下前三范式。
第⼀范式（1NF）⽆重复的列
所谓第⼀范式（1NF）是指在关系模型中，对域添加的⼀个规范要求，所有的域都应该是原⼦性的，即数据库表的每⼀列都是不可分割的原⼦数据项，⽽不能是集合，数组，记录等⾮原⼦数据项。
第⼆范式（2NF）属性
在1NF的基础上，⾮码属性必须完全依赖于码[在1NF基础上消除⾮主属性对主码的部分函数依赖]
第三范式（3NF）属性
在1NF基础上，任何⾮主属性不依赖于其它⾮主属性[在2NF基础上消除传递依赖。
通俗点讲：
第⼀范式：属性(字段)的原⼦性约束，要求属性具有原⼦性，不可再分割；
第⼆范式：记录的惟⼀性约束，要求记录有惟⼀标识，每条记录需要有⼀个属性来做为实体的唯⼀标识，即每列都要和主键相关。
第三范式：属性(字段)冗余性的约束，即任何字段不能由其他字段派⽣出来，在通俗点就是：主键没有直接关系的数据列必须消除(消除的办法就是再创建⼀个表来存放他们，当然外键除外)。即：确保每列都和主键列直接相关,⽽不是间接相关。
如果数据库设计达到了完全的标准化，则把所有的表通过关键字连接在⼀起时，不会出现任何数据的复本(repetition)。标准化的优点是明显的，它避免了数据冗余，⾃然就节省了空间，也对数据的⼀致性(consistency)提供了根本的保障，杜绝了数据不⼀致的现象，同时也提⾼了效率。
尤其是正确字段类型的选择：（先列出所有字段类型再写建议）
所有字段类型：
（⼀）整型数值：
整数类型字节数最⼩值 ~ 最⼤值
tinyint1-128~127 或 0-255
smallint2-32768~32767 或 0~65535
mediumint3-8388608~8388607 或 0~1677215
int4-2147483648~2147483647 或 0~4294967295
bigint8-9223372036854775808~9223372036854775807 或 0~18446744073709551615
（⼆）浮点数类型
浮点数类型字节数最⼩值 ~ 最⼤值
double4±1.175494351E-38 ~ ± 3.402823466E+38
double8±2.2250738585072014E-308 ~ ±1.7976931348623157E+308
（三）定点数类型
定点数类型字节数最⼩值 ~ 最⼤值
dec(m,d)m+2最⼤取值范围与double相同，给定decimal的有效值取值范围由m和d决定
关于浮点数与定点数有点看法：
浮点数相对于定点数的优点是在长度⼀定的情况下，浮点数能够表⽰更⼤的数据范围；它的缺点是会引起精度问题。
使⽤时我们要注意：
1. 浮点数存在误差问题；
2. 对货币等对精度敏感的数据，应该⽤定点数表⽰或存储；
3. 编程中，如果⽤到浮点数，要特别注意误差问题，并尽量避免做浮点数⽐较；
4. 要注意浮点数中⼀些特殊值的处理。
（四）位类型
位类型字节数最⼩值 ~ 最⼤值
bit(m)1~8bit(1) ~ bit(64)
（五）⽇期时间类型
时间⽇期类型字节数最⼩值 ~ 最⼤值
date41000-01-01 ~ 9999-12-31
datetime81000-01-01 00:00:00 ~ 9999-12-31 23:59:59 timestamp419700101080001 ~ 2038年某个时刻
time3-838:59:59 ~ 838:59:59
year11901 ~ 2155
mysql中⽤now()写⼊当前时间。
（六）字符串类型：
字符串类型字节数取值范围
char(m)m m为0 ~ 255之间的整数
varchar(m)值长度+1m为0~65535之间的整数
tinytext值长度+2允许长度0~255字节
text值长度+2允许长度0~65535字节
mediumtext值长度+3允许长度0~167772150字节
longtext值长度+3允许长度0~4294967295字节
binary(m)m允许0~m个字节定长的字符串
varbinary(m)值长度+1允许0~m个字节变长的字符串
tinyblob值长度+1允许长度0~255字节
blob值长度+2允许长度0~65535字节
mediumblob值长度+3允许长度0~167772150字节
longblob值长度+4允许长度0~4294967295字节
enum1或21~255个成员需要1个字节存；255~65535个成员，2个字节存set1/2/3/4/8类似enum,set⼀次可以选取多个成员，⽽enum只能⼀个
针对常⽤的varchar，我们来思考⼏个问题：
mysql创建表数据类型
1）varchar的长度？
MySQL的⽂档，其中对varchar字段类型这样描述：varchar(m) 变长字符串。m 表⽰最⼤列长度。m的范围是0
到65,535。(VARCHAR的最⼤实际长度由最长的⾏的⼤⼩和使⽤的字符集确定，最⼤有效长度是65,532字
节）。
mysql varchar(50) 不管中⽂还是英⽂都是存50个的，但是⼀个表中所有varchar字段的总长度跟编码有关，如
果是utf-8，那么⼤概65535/3，如果是gbk，那么⼤概65535/2.
2）存储限制？编码长度限制？⾏长度限制？超出了，会变成怎样？
针对第⼀个问题：varchar 字段是将实际内容单独存储在聚簇索引之外，实际存储从第⼆个字节开始，接着要⽤
1到2个字节表⽰实际长度（长度超过255时需要2个字节），因此最⼤长度不能超过65535。
针对第⼆个问题：字符类型若为gbk，每个字符最多占2个字节。字符类型若为utf8，每个字符最多占3个字节。
针对第三个问题：导致实际应⽤中varchar长度限制的是⼀个⾏定义的长度。 MySQL要求⼀个⾏的定义长度不能
超过65535。若定义的表长度超过这个值，则提⽰
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some colum
针对第四个问题：若定义的时候超过上述限制，则varchar字段会被强⾏转为text类型，并产⽣warning。
3）与char的对⽐：
CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以
达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进⾏⼤⼩写转换。CHAR
存储定长数据很⽅便，CHAR字段上的索引效率级⾼，⽐如定义 char(10)，那么不论你存储的数据是否达到了10
个字节，都要占去10个字节的空间，不⾜的⾃动⽤空格填充。
CHAR和VARCHAR最⼤的不同就是⼀个是固定长度，⼀个是可变长度。由于是可变长度，因此实际存储的时候
是实际字符串再加上⼀个记录字符串长度的字节(如果超过255则需要两个字节)。如果分配给CHAR或
VARCHAR列的值超过列的最⼤长度，则对值进⾏裁剪以使其适合。如果被裁掉的字符不是空格，则会产⽣⼀
条警告。如果裁剪⾮空格字符，则会造成错误(⽽不是警告)并通过使⽤严格SQL模式禁⽤值的插⼊。
4）char、varchar与text的建议：
TEXT只能储存纯⽂本⽂件。
效率来说基本是char>varchar>text，但是如果使⽤的是Innodb引擎的话，推荐使⽤varchar代替char
char和varchar可以有默认值，text不能指定默认值
以下给出⼏个类型选取建议
（⼀）数字类型：
1）不到不要使⽤DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。
2）固定精度的⼩数，也不建议使⽤DECIMAL，建议乘以固定倍数转换成整数存储，可以⼤⼤节省存储空间，且
不会带来任何附加维护成本。
3）对于整数的存储，在数据量较⼤的情况下，建议区分开 TINYINT / INT / BIGINT 的选择，因为三者所占⽤的
存储空间也有很⼤的差别，能确定不会使⽤负数的字段，建议添加unsigned定义。当然，如果数据量较⼩的数据
库，也可以不⽤严格区分三个整数类型。
4）对于整型数值，mysql⽀持在类型名称后⾯的⼩括号内指定显⽰宽度，例如int(5)表⽰当数值宽度⼩于5位时候
在数值前⾯填满宽度，⼀般配合zerofill属性使⽤。如果⼀个列指定为zerofill，则MySQL⾃动为该列添加unsigned
属性。
5）在数据量较⼤时、建议把实数类型转为整数类型。原因很简单：1. 浮点不精确；2.定点计算代价昂贵。例如：要存放财务数据精确到万分之⼀、则可以把所有⾦额乘以⼀百万、然后存在BIGINT下。
（⼆）字符类型：
1）尽量不要使⽤ TEXT 数据类型，其处理⽅式决定了他的性能要低于char或者是varchar类型的处理。定长字段，建议使⽤ CHAR 类型，不定长字段尽量使⽤ VARCHAR，且仅仅设定适当的最⼤长度，⽽不是⾮常随意的给⼀个很⼤的最⼤长度限定，因为不同的长度范围，MySQL也会有不⼀样的存储处理。
2）char会删除字符串尾部的空格，varchar不会，varchar向前补1-2字节；char定长。binary类似于char,binary 只能保存⼆进制字符串。
char是固定长度，所以它的处理速度⽐varchar快得多，但缺点是浪费存储空间，不能在⾏尾保存空格。在MySQL中，MyISAM建议使⽤固定长度代替可变长度列；InnoDB建议使⽤varchar类型，因为在InnoDB中，内部⾏存储格式没有区分固定长度和可变长度。
3）enum类型忽略⼤⼩写。
4）text与blob区别：blob保存⼆进制数据；text保存字符数据，有字符集。text和blob不能有默认值。
应⽤：text与blob主要区别是text⽤来保存字符数据（如⽂章，⽇记等），blob⽤来保存⼆进制数据（如照⽚等）。blob与text在执⾏了⼤量删除操作时候，有性能问题（产⽣⼤量的“空洞“），为提⾼性能建议定期optimize table 对这类表进⾏碎⽚整理。
关于text与blob我们有些看法建议：
1. BLOB和TEXT值也会引起⾃⼰的⼀些问题，特别是执⾏了⼤量的删除或更新操作的时候。删除这种值会在数据表中留下很⼤的”空洞”，以后填⼊这些”空洞”的记录可能长度不同，为了提⾼性能，建议定期使⽤ OPTIMIZE TABLE 功能对这类表进⾏碎⽚整理.
2. 在不必要的时候避免检索⼤型的BLOB或TEXT值。
3. 把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第⼆张数据表中，可以让你把原数据表中的数据列转换为固定长度的数据⾏格式，那么它就是有意义的。这会减少主表中的碎⽚，使你得到固定长度数据⾏的性能优势。它还使你在主数据表上运⾏ SELECT *查询的时候不会通过⽹络传输⼤量的BLOB或TEXT值。
（三）时间类型：
1）尽量使⽤TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的⼀半。对于只需要精确到某⼀天的数据类型，建议使⽤DATE类型，因为他的存储空间只需要3个字节，⽐TIMESTAMP还少。不建议通过INT类型类存储⼀个unix timestamp 的值，因为这太不直观，会给维护带来不必要的⿇烦，同时还不会带来任何好处。
2）根据实际需要选择能够满⾜应⽤的最⼩存储⽇期类型。
3）timestamp，⽇期类型中只有它能够和实际时区相对应。
（四）ENUM & SET：
对于状态字段，可以尝试使⽤ ENUM 来存放，因为可以极⼤的降低存储空间，⽽且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢？可以尝试使⽤SET类型，即使存在多种属性，同样可以游刃有余，同时还可以节省不⼩的存储空间。
（五）LOB类型：
强烈反对在数据库中存放 LOB 类型数据，虽然数据库提供了这样的功能，但这不是他所擅长的，我们更应该让合适的⼯具做他擅长的事情，才能将其发挥到极致。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。