SQL：力学笃行篇

不晓得列位看官，能否有过想测验考试设想表的设法呢？数据间纷乱复杂的关系，又该若何下手呢？满足什么样的设想准绳，才能够合适准确、完整、分歧且平安的要求呢？大概，小采风想和列位一路进修一下表的设想规范。

有位同窗，专职于后台产物司理，担任产物具体的营业逻辑，有幸有过一次深聊。营业逻辑，需要准确的框架和答应范畴的波动。人算不如天年，充实考虑产物的存储需求、数据处置需求、数据的平安需乞降完整性要求，这是文档要求，我们这些小白能做的，莫过于极力合适文档需求，往往不是一蹴而就的。

什么样的表设想，是足够合理呢？合适三大范式设想准绳的表，至多是准确完整的，可是往往不是足够高效的，需要进行必然的反范式化设想。1 第一范式（不再具体引见）字段只要单一属性且不成朋分，由根基数据类型构成，必需是简单的二维表；2 第二范式不克不及答应非主键列对部门主键具有依赖关系；3 第三范式既不部门依赖于营业主键，也不传送依赖于营业主键；

看完三大范式，不要急着骂小采风。这不就是天书吗？完全没有法子去理解，我们仍是看一看具体的例子，测验考试搞清晰这些苦涩的范式吧！

简单一看，这个表的设想，是没有问题的。可是，若是有一天高数从6个学分变成8个学分了，我们还需要一步步更改表中每行记实，完成高数学分的点窜吗？问题的素质是，非主键course_credit列对复合主键列（stu_id,course_name）中的course_name具有部门依赖，即高数学分依赖于高数课程，违反第二范式会发生插入、更新和删除非常，该当将其更改为两张表，具体如下：

这张表的问题在于，从学号能够晓得学院名称，从学院名称能够晓得学院德律风，素质是，school_tel部门依赖于stu_id，即学院德律风部门依赖于学院学号，违反第三范式，该当将其拆分成两张表，具体如下：

小我认为，比拟于前面三点，这点是最为主要，像是终极大boss一样，需求阐发会具有缝隙，逻辑设想会有不足之处，物理设想会有bug，可是维护优化环节，需要更多的经验和更详尽的考量。近日，领会了些数据库架构中的内容，主从拓扑，主从切换，MMM和MHA等学问，主库的读写分手，从库的读负载平衡，主从切换时的异步延迟，从库IO线程读取binlog日记的分歧，binlog日记bindump号令的堵塞等，面临如许汗青浩劫题，小采风只能知难而进了，留给看官中的您，让世界更夸姣了。

本文似乎有点头重脚轻，说是力学笃行篇，却并没有一点干货呢？看官们不要焦急，下面让我们从现实出发，看看现实场景中，一个营业的表是若何设想的（不再进行具体的表建立等），尾随套路的程序，一步步登上封顶。

//用户消息表：用户名为主键 用户名，暗码，手机号，姓名，出华诞期，在线 以用户名为主键，满足第二范式设想； 2 不具有部门依赖和传送依赖关系，合适第三范式设想

//商品消息表：（商品名称，分类名称）结合主键 商品名称，分类名称，出书社名称，图书价钱， 图书描述，作者 //具体阐发 1 图书描述对商品名称具有依赖关系； 2 仅有分类名称，没有分类描述，完整性不足；

//商品消息表：商品名称为主键 商品名称，出书社名称，图书价钱，图书描述，作者 //分类消息表：分类名称为主键 分类名称，分类描述 //商品分类关系表：（商品名称，分类名称）结合主键 商品名称，分类名称

//订单表：订单编号为主键 订单编号，下单用户，下单日期，订单金额， 订单商品分类，订单商品名，订单商品单价， 订单商品数量，领取金额 //具体阐发 1、订单商品分类，订单商品名，订单商品单价曾经 具有于商品及分类表中，数据严峻冗余； 2、订单金额能够有订单计较而来；

以上设想，当然不是最合理的，不外根基满足三大范式的要求，根基实现准确性、完整性和分歧性，可是如许的文档式设想，能实现高效查询吗？

//查询某位用户的订单总金额 select 下单用户名，sum（d.商品价钱*b.商品数量） from 订单表 a join 订单商品联系关系表 b on a.订单编号=b.订单编号 join 商品分类关系表 c on c.商品名称=b.商品名称 and c.分类名称=b.分类名称 join 商品消息表 d on d.商品名称=c.商品名称 group by 下单用户名 //具体阐发 一条简单的查询，需要联系关系四张表，如许会严峻影响查询效率

//1 在订单商品联系关系表中添加商品价钱字段， 削减联系关系商品消息表 订单编号，订单商品分类，订单商品名， 商品数量，商品价钱 //2 去除商品分类消息表， 间接基于商品消息表和分类消息表查询

//查询某位用户的订单总金额 select 下单用户名，sum(b.订单商品名*b.商品数量) from 订单表 a join 订单商品联系关系表 b on a.订单编号=b.订单编号 group by 下单用户名

世界老是这般公允，反范式化通过添加冗余，提高查询效率，可是同时添加表布局的冗余和数据维护非常的难度，所以，恰当情境下自在恰当衡量。

存储引擎选择分歧的场景需要分歧的存储引擎选择，次要是以下几个方面：事务支撑，表锁行锁，读写机能，索引优化，主键外键

字段数据类型选择1 选择准绳优先考虑数字类型，其次是日期或二进制类型，最初是字符类型；不异级此外数据类型，优先选择占空间小的数据类型；2 主键选择主键应尽可能小，由于主键索引中包含非主键属性；主键该当是挨次增加的，提高插入和删除效率；3 VARCHAR和CHAR类型：以字符为单元，不是以字节为单元VARCHAR：字符串列的最大长度比平均长度大，阐扬变长字符特征；字符串序列少被更新，削减存储碎片；CHAR：字符串序列存储长度近似的值；适合存储短字符串；适合存储更新频度比力高的字符串；4 整数类型和实数类型整数类型的位数，由现实场景选择；float和double类型的数据精度问题，在财政类使用中需要留意；5 日期类型：保举利用date字符串存储8字节，datetime存储4字节，int存储4字节，date类型3字节；date类型有良多日期时间函数能够利用；

纸上得来终觉浅，觉知此事要躬行。今天舍友加入了阿里两头件手艺的引见，主讲人一句话，若是说淘宝手艺是喜马拉雅山脉 ，那么两头件手艺就是珠穆朗玛峰。在mysql的架构中，两头件用于洪水猛兽般的高并发请求时的读写分手和读负载平衡，这是手艺造福于社会的终极聪慧，向工程师看齐。很久没有如许的长文了，四个字，与君同业。