数据库设计优化
物理设计:
- 物理设计要做什么?
-
- 选择合适的数据库管理系统。
- 考虑因素:成本,业务场景,开发语言,功能,操作系统等。
数据库类型 | 成本 | 开发语言 | 支持系统 | 业务场景 |
Oracle |
商业型 | php,java,python等 | windows/liunx系统 | 企业级 |
SQLServer | 商业型 | .NET,C#等 | 只支持windows系统 | 企业级 |
MySQL | 开源型 | php,java,python等 | windows/liunx系统 | 中小型 |
PgSQL | 开源型 | php,java,python等 | windows/liunx系统 | 中小型 |
2.定义数据库,表及字段,要符合命名规范。
-
- 选择存储引擎:这里以MySQL为例
存储引擎 | 索引 | 事务 | 锁粒度 | 主要应用 | 忌用 |
MyISAM | 支持 | 不支持 | 支持并发插入的表级锁 | select,insert高负载 | 读写并用 |
InnoDB | 支持 | 支持 | 支持MVCC的行级锁 | 事务处理 | 无 |
MEMORY | 支持 | 不支持 | 表锁 | 中间计算,静态数据 | 大型数据集,持久性存储 |
Archive | 不支持 | 不支持 | 行级锁 | 日志记录,聚合分析,只支持select,insert操作 | 随机读取,删除 |
Ndb cluster | 支持 | 支持 | 行级锁 | 高可用集群 | 典型引用 |
-
- 表及字段命名规范
- 可读性:命名可读性强
- 举例:列名nickname和nick_name相比,后者更加清晰明了,看起来更加舒服一点。
- 表意性:见名知意
- 举例:列名col1和user_name相比,后者更加具有直观性,可以让我们一眼就知道当前列名所代表的意思和可能的数据类型
- 敏感性:不能与数据库专有字段命名冲突
- 举例:MySQL中 有user表,所以我们自己创建用户表时,尽量不使用user命名,可以加一个前缀比如数据库缩写_user。
- 可读性:命名可读性强
- 表及字段命名规范
3.根据选择的数据库管理系统选择合适的数据类型
-
- 常用数据类型及占用空间
列类型 | 存储空间 |
TINYINT | 1个字节 |
SMALLINT | 2个字节 |
MEDIUMINY | 3个字节 |
INT | 4个字节 |
BINGINT | 8个字节 |
DATE | 3个字节 |
DATETIME | 8个字节 |
TIMESTAMP | 4个字节 |
CHAR(M) | M字节,1<= M <=255 |
VARCHAR(M) | L+1字节,在此 L < = M 和 1 <=M <= 255 |
FLOAT | 4字节 |
DOUBLE | 8字节 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 |
-
- 字段类型的选择的原则
- 当一个列可以选择多种数据类型的时候,优先考虑数字类型,其次是日期类型和二进制类型,最后是字符类型。
- 对于相同的数据类型,应考虑占用空间较小的数据类型。
-
- 字段类型的选择的原则依据
- 在对数据进行比较(查询条件,JOIN条件及排序)操作时,同样的数据,字符处理往往比数字处理效率要低,因为字符要参考数据字典进行比较,数字就不需要。
- 在数据库中,数据处理以页为单位,列的长度越小,一页中存储的数据就越多,加载相同的数据时的页数就相对较小,速度会更快。
-
- 如何具体选择字段类型?
- char和varchar该如何选择?
- char用于数据长度差不多是一致的,基本都在一个小区间内波动或者列中最大数据长度小于50字节。
- varchar用于数据长度变化较大,不能预知其具体长度的数据。
- decimal和float该如何选择?
- decimal用于存储精确数据,精度最高,但是占用空间很大。
- float占用空间比decimal小,适用于非精确数据,但会丢失数据精度。
- 时间类型如何存储?
- 使用int:int 是从 1970 年开始累加的,但是 int 支持的范围是 1901-12-13 到 2038-01-19 03:14:07,如果需要更大的范围需要设置为 bigInt。但是这个时间不包含毫秒,如果需要毫秒,还需要定义为浮点数。
- 使用timestamp:记录经常变化的更新 / 创建 / 发布 / 日志时间 / 购买时间 / 登录时间 / 注册时间等,并且是近来的时间,够用,时区自动处理,比如说做海外购或者业务可能拓展到海外。
- 使用datetime:记录固定时间如服务器执行计划任务时间 / 健身锻炼计划时间等,在任何时区都是需要一个固定的时间要做某个事情。
-
- 数据库设计的其他注意事项
- 如何选择主键?
- 区分业务主键和数据库主键:
- 业务主键:用于标识业务数据,进行表与表之间的关联。
- 数据库主键:为了优化数据存储和查找。若没有设置数据库主键,则InnoDB引擎会自动生成6个字节的隐含主键。
- 考虑主键是否要自动顺序增长:部分数据库是按照主键的顺序逻辑存储的。
- 主键的字段类型所占用的空间要尽可能小:对于使用聚集索引方式存储的表,每个索引都会附加上主键信息。
- 区分业务主键和数据库主键:
- 避免使用外键(避免使用数据库来提供外键约束功能):限于互联网项目
- 在高并发业务中,使用外键约束会降低数据导入的效率,增加维护成本。
- 建议使用逻辑外键,事实上在数据库中并没有设置外键约束,但在项目上都认为这是外键。由程序来维护外键约束,而不是数据库服务器本身来实现该功能。
- 相关联的列要建立索引,增加查找效率。
- 该怎么创建表就怎么创建表,只是没有了FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)该条外键约束命令。
例如:使用数据库创建外键约束:
CREATE TABLE `m_user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `m_order` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `total_price` decimal(10,2) NOT NULL DEFAULT '0.00', `user_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), CONSTRAINT `for_indx_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
不使用数据库创建外键约束:
CREATE TABLE `m_user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `m_order` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `total_price` decimal(10,2) NOT NULL DEFAULT '0.00', `user_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.避免使用触发器
-
- 触发器是一个隐藏的存储过程,因为它不需要参数,不需要显示调用,往往在你不知情的情况下已经做了很多操作,无形中增加了系统的复杂性。
- 涉及到复杂的逻辑的时候,触发器的嵌套是避免不了的,如果再涉及几个存储过程,再加上事务等等,很容易出现死锁现象。
- 存储过程的致命伤在于移植性,存储过程不能跨库移植,在后期系统升级维护时难度加大。
4.谨慎使用预留字段
-
- 无法准确的知道预留字段的类型。
- 无法知道预留字段中所存储的内容。
- 后期维护预留字段的成本高。
建议:
- “按需设计”,在经过详细有效的分析之后,在数据表中只放置必要的字段,而不要留出大量的备用字段。
- 如果数量很少,而且信息的性质与原表密切相关,那么就可以直接在原表上动态增加字段,并将相关的数据更新进去
- 如果数量较大,或者并非是原表对象至关重要的属性,那么就可以新增一个表,然后通过键值连接起来。
4.反范式化设计。
-
- 什么是反范式化?
- 适当的违反的范式的要求,允许少量的数据冗余,用空间换取时间。
- 优点:增加查询效率。
- 什么是反范式化?
以上仅为本人学习过程中的知识总结,若有错误,还请诸位不吝赐教。
posted on 2019-04-26 13:24 与落霞齐飞12138 阅读(822) 评论(0) 编辑 收藏 举报