优化
这东西,就我个人而言,应该是在设计没有什么毛病的基础上进行的。对一个项目,如果数据库设计这一块有很大问题,这导致后面扩展需求的时候很难复用之前的数据库,而业务耦合性又很高,优化起来是一件相当痛苦的事情! 如果在设计的时候就把该考虑的设计好,你可能会给后面的优化工作做了相当好的铺垫。 关于数据库的设计,我来从范式
、反范式
、主键
、字符集
、存储引擎
等方面总结一下。
三范式
每一列都是不可分割的原子数据项,确保数据表中每列(字段)的原子性。
比如,如下t_user表:
id | 姓名 | 部门 | |
---|---|---|---|
部门名称 | 部门领导 | ||
1 | 赵云 | 蜀汉 | 刘备 |
2 | 张辽 | 曹魏 | 曹操 |
3 | 甘宁 | 孙吴 | 孙权 |
像这个就不属于第一范式,因为部门
字段可以分割成部门名称
和部门领导
两个字段,分割后:
id | 姓名 | 部门名称 | 部门领导 |
---|---|---|---|
1 | 赵云 | 蜀汉 | 刘备 |
2 | 张辽 | 曹魏 | 曹操 |
3 | 甘宁 | 孙吴 | 孙权 |
这样就符合第一范式了。
在第一范式的基础上,不存在仅依赖于关键一部分的属性(不能存在部分依赖于主键)。 如果一个关系满足第一范式,并且除了主键之外的其他列,都依赖于该主键,则满足第二范式。
举个栗子,一个用户只有一种角色,而一个角色对应多个用户。
这种情况可以按如下方式建立数据表关系,使其满足第二范式。
user表
id | user_name | dept_name | role_id |
---|---|---|---|
1 | 赵云 | 蜀汉 | 1 |
2 | 张辽 | 曹魏 | 2 |
role表
id | role_name |
---|---|
1 | 保镖 |
2 | 前将军 |
属性不传递依赖于其他非主属性,非主键必须直接依赖于主键而不能传递依赖。
再来看一下这个表:
id | user_name | dept_name |
---|---|---|
1 | 赵云 | 蜀汉 |
2 | 张辽 | 曹魏 |
其实,这里有一个字段部门领导
是依赖于部门的
,所以它不符合第三范式。
可以再拆(加)一个表:
dept_name | dept_leader |
---|---|
蜀汉 | 刘备 |
曹魏 | 曹操 |
这样就符合第三范式了。
反范式
顾名思义,不遵照范式规则,就是反范式。 没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保留冗余数据。所以就有了反范式。
优点
缺点
join
优点
缺点
在实际工作中,很少能做到严格意义上的范式和反范式,一般需要混合使用。
例如,在一个网站实例中,这个网站,允许用户发送消息,并且一些用户是付费用户。现在想查看付费用户最近的10条信息。 在user表和message表中都存储用户类型(account_type)而不用完全的反范式化。这避免了完全反范式化的插入和删除问题,因为即使没有消息的时候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大,有利于高效地获取数据。
如果需要显示每个用户发了多少消息(类似论坛的),可以每次执行一个昂贵的自查询来计算并显示它;也可以在user表中建一个num_messages列,每当用户发新消息时更新这个值。
与业务无关的,无意义的数字序列。 对MySQL数据库,一般情况下我们都给每个表一个id字段,把它设为主键,自增,这个就是
代理主键
,也是常用的。
事物属性中的自然唯一标识。 比如,存储的人员的身份证信息,能唯一标识一条记录,那么可以把它作为主键。 但是,身份证号这个字段一般会和业务有牵扯,耦合性高。
代理主键不与业务耦合,易于维护。 一个大多数表,最好是全部表,通用的键策略能够减少需要编写的源码数量,减少系统的总体拥有成本。比如专门写一个生成主键的东西用来生成主键,比如uuid。。。
字符集直接决定了数据在MySQL中的存储编码方式,由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异,所以通过使用合适的字符集,可以帮助我们尽可能减少数据量,进而减少IO操作次数。
存储引擎
代表数据文件的组织形式,所以存储引擎的选择对于数据的存储查询等都有影响,后面我会在索引优化、查询优化的总结中不断提到存储引擎
,这里只针对MySQL的存储引擎做一个简单的对比。
- | MyISAM | InnoDB |
---|---|---|
索引类型 | 非聚簇索引 | 聚簇索引 |
支持事务 | 否 | 是 |
支持表锁 | 是 | 是 |
支持行锁 | 否 | 是 |
支持外键 | 否 | 是 |
支持全文索引 | 是 | 是,5.6后支持 |
适合的操作类型 | 大量select | 大量insert,delete,update |
看了这个表,我选InnoDB。。。
被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。 这样的场景由于每次Join仅仅只是为了取得某个小字段的值,Join到的记录又大,会造成大量不必要的 IO,完全可以通过空间换取时间的方式来优化。 不过,冗余的同时需要确保数据的一致性不会遭到破坏,确保更新的同时冗余字段也被更新。
当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候,如果我们大部分访问这张表的时候都不需要这个字段,我们就该义无反顾的将其拆分到另外的独立表中,以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加,既减少物理 IO 次数,也能大大提高内存中的缓存命中率。 还有适当的分库分表,我们可以
垂直拆分
---把不同业务用到的库表放在不同的服务器;也可以水平拆分
---比如按照月份把数据拆分,或者按照地市数据拆分到不同的服务器。