数据库表设计

数据库异常

• 由于数据库设计不合理，造成在增删改时可能出现问题
• 插入异常：想要插入的值依赖于其他无关紧要的值。如果在无关紧要的值不插入，想要插入的值无法进行插入操作
• 更新异常：只想修改某一个数据，却不得不修改多行数据
• 删除异常：删除某一数据的时候，不得不同时删除其他无关紧要的数据

基本概念

• 码：表中可以唯一确定一个元组的某个属性（或者属性组）
  • 候选码：如果存在多个码，所有的码都叫候选码
  • 主码：从候选码中选出一个
• 全码：如果一个码包含了所有的属性，这个码就是全码。
• 主属性：一个属性只要在任何一个候选码中出现过，这个属性就是主属性。
• 非主属性：与上面相反，没有在任何候选码中出现过，这个属性就是非主属性。

范式

• 第一范式：强调的是列的原子性，即列不能够再分成其他几列
  • eg：电话包括家庭电话、公司电话。这时就需要拆分成家庭电话和公司电话两列
• 第二范式：一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。（检查主键和非主键的关系）
  • eg：比如业务主键是学号和课程号，学分只对课程号依赖
  • 可能会导致插入异常、删除异常、更新异常
• 第三范式：在第二范式的基础上，另外非主键列必须直接依赖于主键，不能存在传递依赖。（检查非主键和非主键的关系）
  • eg：（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）=》学号、所在院校、院校地址传递依赖
• BC范式：主属性不部分依赖于主码。候选主键中的字段部分依赖于主键，这样说可能有点不太严谨

范式：https://blog.youkuaiyun.com/xuchenhuics/article/details/48649795
https://blog.youkuaiyun.com/Fly_as_tadpole/article/details/82503601

好处

• 减少数据冗余、减少维护异常

坏处

• 查询时需要连接的表很多，多次使用join
• 子表的数据变化可能会影响到链表查询（比如订单生成后，其价格不想因修改商品信息再改变）
• 所以有时可以使用反范式的思想，用空间换时间提高效率

冷热数据分离

• 将冷热数据进行分离，减小表的宽度

字段设计

• 优先选择符合需求的最小数据类型。eg：INET_ATON(“255.255.255.255”)=4294967295
• vachar(n)：n代表的是字符个数，如果utf8存储中文，需要占用765字节。
  • 在磁盘中存储虽然是有多长占用多少空间，但是在读取到内存中为了保证效率，都是按照设置的长度来预留空间
• TEXT和BLOB建议放到单独的表中
  • 这两种类型只能用前缀索引，索引不管怎样都需要在表中二次查询
• 尽量所有列都加上not null。对null的判断需要做特殊处理。如果可以为null，还需要多一个字段来进行标记
• 日期时间尽量用日期类型存储。
  • 这样可以使用到日期函数进行比较，同时日期类型相比字符串占用空间更少
• 金额类的数据一定要存放在decimal类型中，这是一个精准的浮点型，不会丢失精度，占用空间由定义的宽度决定，能存放比bigint大的数据

sql开发规范

• 尽量使用预编译语句进行数据操作，这样不用重复进行sql编译，可以复用之前的执行计划，提高处理效率。同时只用传参数值，降低网络传输的带宽
• 重复利用索引。避免使用双%无法使用索引。一个sql 只能利用复合索引中的一列来进行范围查询。
• 不要使用select * 。这样会消耗更多的网络和io，无法使用覆盖索引。
• 使用left join或者not exist来优化not in，子查询无法使用索引；子查询可能会使用到临时表；浪费io
• 不要关联太多的join。会消耗很多的内存，可能会使用到临时表
• 用in来代替or，in可以有效使用索引
• 禁止使用order by rand()来获取随机行，这样会吧所有符合的数据读取到内存中，会利用临时表和文件来进行排序，消耗io。推荐使用程序获取随机值，再用随机值在数据库中获取相应的行

order by rand(): https://blog.youkuaiyun.com/fdipzone/article/details/51541729

• where中禁止对列进行函数转换。eg:where date(createtime)=‘20160908’，这样将无法使用索引。改用where createtime>=“20160908” and create time<“20160909”？
• 能使用union all的地方不要使用union。union会把数据放到临时表中在去重
• 将一个大sql拆分成多个sql并发处理，提高效率
• 大表的写操作，建议分批进行。一次性太多数据可能会造成长时间的锁表，严重的主从延迟，应该避免大事务