Mysql数据库设计

对于数据库的设计是非常的重要的，而笔者在构建一个简单的数据表的时候就遇到了一定的问题，所以在此记录一些数据库设计的笔记

大致流程

需求分析 -> 逻辑设计 -> 物理设计 -> 维护优化

---

简介

• 维护优化主要包括新的需求进行建表，索引优化，大表的拆分

• 需求分析是很关键的，主要是要了解系统要存储的数据，存储特点，数据生命周期,实体与实体之间的关系，关联关系分析好，实体的属性，主键分析，实体的特性(增长比较快的，要考虑分库分表)

• BC范式要求如果这个表是复合关键字，那么复合关键字之间也不能存在函数依赖关系

• 逻辑设计通常包含ER图的设计，范式规范

• 物理设计除了数据库管理系统的讨论，还有命名规范，选择合适的字段类型，以及反范式

• MyISAM不支持事务，在读写频繁的时候不要用，最好还是使用Innodb，行级锁,Archive主要应用于日志记录，用的空间相对少

• Ndb一般是在MySQL集群时用

• DBMS系统对表名的大小写是敏感的，这个要特别的注意

• 尽量不要使用缩写，容易引起歧义

数据类型的选择

• 当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或者二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。（比如对于char(10) varchar(20) 一般会优先选择char,char(10)比varchar(20)所占的空间小）

char(M) M字节，1<=M<=255
varchar(M) L+1字节，在此 L<=M 和 1<=M<=255
相当于在不越界的情况下，varchar总是比char大一个字节空间

以上原则的主要参考依据

1. 在对数据进行比较(查询条件，JOIN条件以及排序)操作时，同样的数据，字符往往比数字处理要慢。2. 在数据库中，数据处理以页为单位，列的长度越小，利于性能提升(针对于I/O优化)。

• 如果列中要存储的数据长度差不多是一致的，比如身份证，电话这种相对固定的数字，一般用char()

• 如果列中的最大数据长度是小于50个字节的，那么一般也考虑用char,如果这个列很少用，则基于节省空间和减少I/O的考虑，还是可以使用varchar

• 一般大于50个字节就不宜用char了（25个汉字或者50个字母）

decimal && float

• decimal 用于存储精确的数据，而float只能用于存储非精确数据。所以精确数据只能选择用decimal

• 由于float 的存储空间开销一般比decimal小，所以非精确数据优先选择float类型（精确到7位小数只需要4个字节，而精确到15位小数只需要8字节）

---

• int 只能存储到2038-1-19 11:14:07 即是2^32 ，对于不怎么查询的字段可以用int ，但是比较常用到的还是用datetime，比如订单时间等等

• 外键约束最好不要使用，但是相关联的列上一定要建立索引

• 避免使用触发器

• 一定不要使用预留字段，并没有什么卵用

反范式化

反范式化是为了性能和读取效率的考虑而适当的对第三范式的要求进行违反，而允许存在少量的数据冗余，也就是用空间换时间

不可能每张表中都进行反范式化，一定要适度，还是要看情况，DBA和后端人员的交流

维护优化

1. 维护数据字典
2. 维护索引
3. 维护表结构
4. 在适当的时候对表进行水平拆分或者垂直拆分

维护数据字典

数据库本身主要是通过备注字段来进行维护 –COMMENT

维护索引

如何选择合适的列来建立索引？

1.出现在WHERE从句GPOUP BY 从句 ORDER BY 从句中的列

2.索引中不要包括太长的数据类型

原则

• 索引量一定要适当
• 定期要维护索引碎片
• 在sql语句中不要使用强制索引关键字

适合在数据库中进行的操作

1. 批量操作（逐条操作适合在存储过程中进行）
2. 不要使用Select * 这样的查询
3. 控制使用用户自定义的函数
4. 不要使用数据库中的全文索引

表的拆分

垂直拆分把列拆分开。

• 把经常一起查询的列放在一起，把大字段放到另一个表中
• text,blob等大字段拆分出到附加表

水平拆分可以使用hash key来拆分（解决宽度问题）

        笔记主要来源于慕课网数据库那些事                                            -by vampirebitter