一、单表设计与优化：
（1）设计规范化表，消除数据冗余（以使用正确字段类型最明显）：
表设计三范式：
第一范式：字段的原子性约束
，要求字段具有原子性，不可再分割。
第二范式：记录的唯一性约束
，要求记录有唯一标识，每条记录需要一个字段来作为实体的唯一标识，即每列都要和主键相关
第三范式：字段冗余性约束。主键没有直接关系的数据列必须消除（再创建一个表来存放他们，外键除外）。
如果数据库设计达到完全的标准化，则把所有表通过关键词连接在一起，不会出现任何数据的复本。标准化的优点是避免了数据冗余，节省存储空间；也对数据的一致性提供了根本的保障，杜绝了数据不一致的现象，同时也提高了效率。
所有字段类型：
（一）整型数值：
（二）浮点数类型
（三）定点数类型
关于浮点数与定点数有点看法：
浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。
使用时我们要注意：
- 浮点数存在误差问题；
- 对货币等对精度敏感的数据，应该用定点数表示或存储；
- 编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
-
要注意浮点数中一些特殊值的处理。
（四）位类型
（五）日期时间类型
mysql中用now()写入当前时间。
（六）字符串类型：
针对常用的varchar，我们来思考几个问题：
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。
若定义的时候超过上述限制，则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我们有些看法建议：
BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的”空洞”，以后填入这些”空洞”的记录可能长度不同,为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理.
在不必要的时候避免检索大型的BLOB或TEXT值。
把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中 的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。
（三）时间类型：
1）尽量使用TIMESTAMP类型
因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。
2）根据实际需要选择能够满足应用的最小存储日期类型。
3）timestamp，日期类型中只有它能够和实际时区相对应。
（四）ENUM & SET：
对于状态字段，可以尝试使用 ENUM 来存放，因为可以极大的降低存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢？可以尝试使用SET类型，即使存在多种属性，同样可以游刃有余，同时还可以节省不小的存储空间。
（五）LOB类型：
强烈反对在数据库中存放 LOB 类型数据，虽然数据库提供了这样的功能，但这不是他所擅长的，我们更应该让合适的工具做他擅长的事情，才能将其发挥到极致
(2) 适当的冗余，增加计算列：
数据库设计的使用原则是：在数据冗余和处理速度之间找到合适的平衡点。
满足范式的表一定是规范化的表，但不一定是最佳的设计。很多情况下会为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。比如我们有一个表，产品名称，单价，库存量，总价值。这个表是不满足第三范式的，因为“总价值”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“总价值”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。
合理的冗余可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。
（3）索引的设计：
表优化的重要途径，比如百万级别的表没有索引，注定卡死。
（4）主键和外键的必要性（实际项目开发的重要取舍）
概述：
主键和外键的设计，在全局数据库的设计中，占有重要地位。因为主键是实体的抽象，主键与外键的配对，表示实体之间的连接。
主键：
标识某条记录。对于数据量大，查询频繁的数据库表，一定要有主键，主键可以增加效率、防止重复等优点。
主键一般选择总的长度小的键，小的键比较速度快，同时小的键可以使主键的B树结构的层次更少
主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般选择重复率低、单独或者组合查询可能性大的字段放在前面。
外键：
外键是最有效的一致性维护方法。
数据库的一致性要求，一次可以用外键、check约束、规则约束、触发器、客户端程序来实现。一般，离数据越近的方法效率越高。但是！！！要谨慎使用级联删除和级联更新，因为级联删除和级联更新有些突破了传统对外键的定义，使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。不过，从性能看级联删除和级联更新是比其他更高效的方法。
实际项目中主外键的取舍设计：（在性能和可扩展性之间寻求平衡）
边缘模块值得是小功能不常用需求很少再giant的模块；中心模块指的是关联的东西太多的模块，是很多表的主表；物理键指的是在表建立主外键关联；逻辑主外键指的是利用字段去实现逻辑主外键关联；热点模块指的是需求经常要改的模块。
大型系统：
1、针对性能要求不高、安全要求高的模块，推荐使用物理主外键关联；针对性能要求高、安全自己控制的模块，推荐不用物理外键。
2、针对中心模块的其他模块的联系，主键使用物理主外键
3、针对热点模块，必须使用逻辑主外键
4、针对边缘模块，推荐使用物理主外键
小系统
推荐还是使用外键
注意：
不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后个个应用通过这个层来访问数据库。
外键是有性能问题的，不能过分追求。
（5）存储过程、视图、函数的适当使用（这些是优化的方法，这几个后面会讲）：
很多人习惯将复杂操作都放在应用程序层，但如果你要优化数据访问性能，将SQL代码移植到数据库上(使用存储过程，视图，函数和触发器)也是一个很大的改进原因如下：
1）存储过程减少了网络传输、处理及存储的工作量，且经过编译和优化，执行速度快，易于维护，且表的结构改变时，不影响客户端的应用程序
2）使用存储过程，视图，函数有助于减少应用程序中SQL复制的弊端，因为现在只在一个地方集中处理SQL
3）使用数据库对象实现所有的TSQL有助于分析TSQL的性能问题，同时有助于你集中管理TSQL代码，更好的重构TSQL代码。
（6）传说中的‘三少原则’：
数据库的表越少越好
表的字段月少越好
字段中的组合主键、组合索引越少越好
这里的少是相对的，是减少数据冗余的重要设计理念而已。
实际上，我们为了减少单表查询压力，会把去分表，从而分发记录量，避免一个超级表的诞生。
（7）分隔你的表，减小表尺寸
如果发现某个表的记录太多，例如超过一千万跳，则要对该表进行水平分隔。水平分隔的做法是：以该表主键的某个值外界限，将该表的记录水平分隔为两个表
若果你若发现某个表的字段太多，例如超过八十个，则垂直分隔该表，将原来的一个表分隔为两个表。
（8）、字段设计原则
字段是数据库最基本的单位，其设计对性能的影响很大的。需要注意如下：
1）数据类型尽量用数字型，数字型的字段比字符型的快很多
2）在可以满足可预见的未来需求的前提下，数据类型尽量小，
3）尽量不要允许null，除非必要，可以用not null +default代替
NULL 类型比较特殊，SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外的存放空间。
4)少用text和image，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用
5）自增字段要慎用，不利于数据迁移。
二、基于单标设计的夺标设计原则：
（1）表关系：
一）一对一关系：
定义：
在这种关系中，关系表的每一边都只能存在一个记录。每个数据表中的关键字在对应的关系表中只能存在一个记录或者没有对应的记录。这种关系和一对配偶之间的关系非常相似——要么你已经结婚，你和你的配偶只能有一个配偶，要么你没有结婚没有配偶。大多数的一对一的关系都是某种商业规则约束的结果，而不是按照数据的自然属性来得到的。如果没有这些规则的约束，你通常可以把两个数据表合并进一个数据表，而且不会打破任何规范化的规则。
一对一关系又分为：一对一外键关联和一对一主键关联。
一对一主键关联：要求两个表的主键必须完全一致，通过两个表的主键建立关联关系。
可以看到下图，很明显的，班级编号作为主键的话，就是一个主键关联了。
一对一外键关联：
下面又很明显看到，以班主任ID作为外键关联起来的一个表。
二）一对多关系（多对一）：
定义：
主键数据表中只能含有一个记录，而在其关系表中这条记录可以与一个或者多个记录相关，也可以没有记录与之相关。这种关系类似于你和你的父母之间的关系。你只有一位母亲，但是你母亲可以有几个孩子。
三）多对多关系：
定义：
两个数据表里的每条记录都可以和另一个数据表里任意数量的记录（或者没有记录）相关。例如，如果你有多个兄弟姐妹，这对你的兄弟姐妹也是一样（有多个兄弟姐妹），多对多这种关系需要引入第三个数据表，这种数据表称为联系表或者连接表，因为关系型系统不能直接实现这种关系。
在RDBMS中，必须使用中间表来表示多对多的关系。中间表我们可以分成两种，一种是纯粹表示关系的中间表，一种是表示中间实体的中间表。
纯粹表示关系的中间表很简单，只需要两列：AID和BID，AID以外键关联到A表的主键，BID以外键关联到B表的主键，然后这两个列组成联合主键。这个中间表纯粹是表示多对多关系而存在，在业务上不会有对应的实体与之对应。比如前面提到的学生和课程的关系，如果我们只需要知道哪些学生上哪些课，哪些课有哪些学生选，不需要有更多的信息的情况下，我们就可以建立“学生课程”中间表，里面只有学生ID和课程ID两个字段。
中间实体是在纯粹的中间关系表的基础上，加上了更多的属性，从而形成了一个新的实体。比如上面提到的学生和课程的关系，如果我们需要记录学生选课的时间、学生选择这门课程后的考试成绩，那么我们就像建立一个“选课”实体，
该实体具有如下属性：
选课ID，主键
学生ID，与学生表做外键关联
课程ID，与课程表做外键关联
选课时间，DateTime类型
考试成绩，记录选修该课程后考试的最终成绩
注意：
一）外键与索引：
外键是一种约束，与索引的概念不一样，只是大多数情况下，我们建立外键时，都会在外键列上建立对应的索引。外键的存在会在每一次数据插入、修改时进行约束检查，如果不满足外键约束，则禁止数据的插入或修改。这必然带来一个问题，就是在数据量特别大的情况下，每一次约束检查必然导致性能的下降。索引其实也有类似的问题，索引如果键多了，那么在插入删除修改时也要去维护对应的索引，所以索引的存在也会导致数据操作变慢。
不过外键与索引的优点不同，外键只是保证数据的一致性，并不能给系统性能带来任何好处，所以由于外键导致的插入数据变慢会随着数据量的增长而越来越严重。而索引的目的性是为了索引数据更快，维护数据时导致的索引数据的变更，对性能的影响不会像外键那样随着数据量增长而变得验证（当然大数据量时的索引树维护回避小数据量的索引树维护更麻烦，但至少不会像外键那样）。
初语性能的考虑，如果我们的系统完全由我们开发的程序使用，而不需要提供数据库给其他应用系统写入数据，而且对性能要求较高，那么我们可以考虑在生产环境中不适用外键，只需要建立能够提高性能的索引。由于整个数据库的操作都是由我们开发的程序来完成的，所以我们程序可以再开发过程中做好各方面的一致性检查，保证操作的数据时满足外键约束的，而不需要真正的存在这样一个外键约束。具体做法为：我们在建立数据库时有多个脚本，包括创建表、初始化数据、创建索引、外键等，我们在开发和测试环境中，都把这些脚本运行了，使得开发测试环境中的数据库时完整的，经过大量测试保证营养程序能够维护数据之间的约束的情况下，那么我们在生产时，不需要运行创建外键这个脚本文件，只需要创建表、初始化数据、索引就可以了。
二）建立关系
在开始着手考虑建立关系表之间的关系之前，你可能需要对数据非常熟悉。只有在熟悉数据之后，关联会比你刚开始的时候更明显。你的数据库系统依赖于在两个数据表中找到的匹配值来建立关系。
进行匹配的值都是主键和外键的值。（关系模型不要求一个关系必须对应的使用一个主键来确定。你可以使用数据表中的任何备选关键字来建立关系，但是使用主键是大家都已经接受的标准。）主键（primary key）唯一的识别表中的每个记录。而外键（foreign key）只是简单的将一个数据表中的主键存放在另外一个数据表中。同样地，对于你来说也不需要做太多的工作——只是简单地将主键加到关系表中，并将其定义为外键。
（2）分表原则：
分表主要目的是为突破单节点数据库服务器的I/O能力限制，解决数据库扩展性问题。同时分库分表等思想也将引出以后的数据库集群、主从复制、读写分离方案。。。
为什么我们要分表分区？？
日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。
（一）表拆分方式：
1）垂直切分：
定义：
把主键和一些数据表的列放在一个表中，然后把主键和另一些数据表的列放在一个表中。
如果一个表的某些列常用，另一些不常用，则可以采用垂直拆分。
优点：可以使数据行变小，一个数据也就可以存放更多的数据，在查询时候可以减少I/O次数；可以最大化利用cache的目的。
缺点：主码(主键)出现冗余，需要管理冗余；查询所有数据时需要join操作（增加CPU开销），需要从业务上规避。
2）水平拆分（分表，分区）–按表中某一字段值的范围划分：
定义：
根据列的范围值进行合理切分，放在多个独立的表或分区中。
适用场景：
表很大，分隔后可以降低查询时候需要读取的数据和索引的页数，同时降低索引的层数，提高查询速度。
表中的数据时独立的，例如表中分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，而另一些数据不常用。
需要把数据放在多个存储介质上
需要把历史数据和当前数据拆分开
例子：
当伴随着某一个表的数据量越来越大，以至于不能承受的时候，就需要对它进行进一步的切分。一种选择是根据key 的范围来做切分，譬如ID 为 1-10000的放到表A上，ID 为10000~20000的放到表B。这样的扩展就是可预见的。另一种是根据某一字段值来划分，譬如根据用户名的首字母，如果是A-D，就属于表A，E-H就属于表B。这样做也存在不均衡性，当某个范围超出了单点所能承受的范围就需要继续切分。还有按日期切分等等。
可以使用Mrg_Myisam引擎实现水平分表
优点：
单表大小可控，天然水平扩展。降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，加快了查询速度。
缺点：
无法解决集中写入瓶颈的问题。同时，水平分割会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要union操作。在许多数据库应用中，这种复杂性会超过它带来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加两到三倍数据量，查询时也就增加读一个索引层的磁盘次数。
3）散列库表（基于hash算法的切分）：
定义：
表散列与水平分割相似，但没有水平分割那样的明显分割界限，采用Hash算法把数据分散到各个分表中, 这样IO更加均衡。一般采用mod来切分，一开始确定切分数据库的个数，通过hash取模来决定使用哪台。这种方法能够平均地来分配数据，但是伴随着数据量的增大，需要进行扩展的时候，这种方式无法做到在线扩容。每增加节点的时候，就需要对hash 算法重新运算。
我们会按照业务或者功能模块将数据库进行分离，不同的模块对应不同的数据库或者表，再按照一定的策略对某个页面或者功能进行更小的数据库散列，比如用户表，按照用户ID进行表散列，散列128张表，则应就能够低成本的提升系统的性能并且有很好的扩展性
优点：
数据分布均匀
缺点：
数据迁移的时候麻烦，不能按照机器性能分摊数据（可用一致性哈希解决这个问题）
（二）在了解完分表了，我们先来理解区分分区与分表吧。
分区：
定义：
分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。
分表定义：
分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。
mysql分表和分区有什么联系呢？：
1.都能提高mysql的性高，在高并发状态下都有一个良好的表现。
2.分表和分区不矛盾，可以相互配合
，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。
3.分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。
4.表分区相对于分表，操作方便，不需要创建子表。
（三）表拆分建议：（针对大系统）
其实这点没有明确的判断标准，比较依赖实际业务情况和经验判断。
一般MySQL单表1000W左右的数据是没有问题的
（前提是应用系统和数据库等层面设计和优化的比较好）。
1）对记录多的表进行拆分。（几百-上千万级别的表）
2）需要拆分的表分为动态表和相对静态表。动态表拆分到不同库，静态表存在于公共库。从公共库同步到分库。实现表的连接。
3）按照年、月、地域等来分割，或者根据时间范围、和很固定又清晰的字段值范围等，具有确定的分割标志来分割。