一、设计技巧
1. 原始单据与实体之间的关系
可以是一对一、一对多、多对多的关系。在一般情况下,它们是一对一的关系:即一张原始单据对应且只对应一个实体。在特殊情况下,它们可能是一对多或多对一的关系,即一张原始单证对应多个实体,或多张原始单证对应一个实体。这里的实体可以理解为基本表。明确这种对应关系后,对我们设计录入界面大有好处。
〖例〗:一份员工履历资料,在人力资源信息系统中,就对应三个基本表:员工基本情况表、社会关系表、工作简历表。这就是“一张原始单证对应多个实体”的典型例子。
2. 主键与外键
一般而言,一个实体不能既无主键又无外键。在E—R图中,处于叶子部位的实体,可以定义主键,也可以不定义主键(因为它无子孙),但必须要有外键(因为它有父亲)。
主键与外键的设计,在全局数据库的设计中,占有重要地位。当全局数据库的设计完成以后,有个美国数据库设计专家说:“键,到处都是键,除了键之外,什么也没有”,这就是他的数据库设计经验之谈,也反映了他对信息系统核心(数据模型)的高度抽象思想。因为:主键是实体的高度抽象,主键与外键的配对,表示实体之间的连接。
3. 基本表的性质
基本表与中间表、临时表不同,因为它具有如下四个特性:
(1)原子性。基本表中的字段是不可再分解的。
(2)原始性。基本表中的记录是原始数据(基础数据)的记录。
(3)演绎性。由基本表与代码表中的数据,可以派生出所有的输出数据。
(4)稳定性。基本表的结构是相对稳定的,表中的记录是要长期保存的。
理解基本表的性质后,在设计数据库时,就能将基本表与中间表、临时表区分开来。
4. 中间表、报表和临时表
中间表是存放统计数据的表,它是为数据仓库、输出报表或查询结果而设计的,有时它没有主键与外键(数据仓库除外)。临时表是程序员个人设计的,存放临时记录,为个人所用。基表和中间表由DBA维护,临时表由程序员自己用程序自动维护。
5. 范式标准
基本表及其字段之间的关系,应尽量满足第三范式。但是,满足第三范式的数据库设计,往往不是最好的设计。为了提高数据库的运行效率,常常需要降低范式标准:适当增加冗余,达到以空间换时间的目的。
〖例〗:有一张存放商品的基本表,如图:
商品编号
商品名称
商品单价
商品数量
金额
1
苹果
2
100
200
2
梨
3
50
150
“金额”这个字段的存在,表明该表的设计不满足第三范式,因为“金额”可以由“单价”乘以“数量”得到,说明“金额”是冗余字段。但是,增加“金额”这个冗余字段,可以提高查询统计的速度,这就是以空间换时间的作法。
在Rose2002中,规定列有两种类型:数据列和计算列。“金额”这样的列被称为“计算列”,而“单价”和“数量”这样的列被称为“数据列”。
6. 通俗地理解三个范式
通俗地理解三个范式,对于数据库设计大有好处。在数据库设计中,为了更好地应用三个范式,就必须通俗地理解三个范式(通俗地理解是够用的理解,并不是最科学最准确的理解):
第一范式:1NF是对属性的原子性约束,要求属性具有原子性,不可再分解;
第二范式:2NF是对记录的惟一性约束,要求记录有惟一标识(主键),即实体的惟一性;
第三范式:3NF是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。
没有冗余的数据库设计可以做到。但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保留冗余数据。具体做法是:在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段,允许冗余。
7. 正确认识数据冗余
主键与外键在多表中的重复出现,不属于数据冗余,这个概念必须清楚,事实上有许多人还不清楚。非键字段的重复出现,才是数据冗余!而且是一种低级冗余,即重复性的冗余。高级冗余不是字段的重复出现,而是字段的派生出现。
〖例〗:商品中的“单价、数量、金额”三个字段,“金额”就是由“单价”乘以“数量”派生出来的,它就是冗余,而且是一种高级冗余。冗余的目的是为了提高处理速度。只有低级冗余才会增加数据的不一致性,因为同一数据,可能从不同时间、地点、角色上多次录入。因此,我们提倡高级冗余(派生性冗余),反对低级冗余(重复性冗余)。
8. 要善于识别与正确处理多对多的关系
若两个实体之间存在多对多的关系,则应消除这种关系。消除的办法是,在两者之间增加第三个实体。这样,原来一个多对多的关系,现在变为两个一对多的关系。要将原来两个实体的属性合理地分配到三个实体中去。这里的第三个实体,实质上是一个较复杂的关系,它对应一张基本表。一般来讲,数据库设计工具不能识别多对多的关系,但能处理多对多的关系。
〖例〗:在“图书馆信息系统”中,“图书”是一个实体,“读者”也是一个实体。这两个实体之间的关系,是一个典型的多对多关系:一本图书在不同时间可以被多个读者借阅,一个读者又可以借多本图书。为此,要在二者之间增加第三个实体,该实体取名为“借还书”,它的属性为:借还时间、借还标志(0表示借书,1表示还书),另外,它还应该有两个外键(“图书”的主键,“读者”的主键),使它能与“图书”和“读者”连接。
9. E--R图没有标准答案
信息系统的E--R图没有标准答案,因为它的设计与画法不是惟一的,只要它覆盖了系统需求的业务范围和功能内容,就是可行的。反之要修改E--R图。尽管它没有惟一的标准答案,并不意味着可以随意设计。好的E—R图的标准是:结构清晰、关联简洁、实体个数适中、属性分配合理、没有低级冗余。
10. 视图技术在数据库设计中很有用
与基本表、代码表、中间表不同,视图是一种虚表,它依赖数据源的实表而存在。视图是供程序员使用数据库的一个窗口,是基表数据综合的一种形式,是数据处理的一种方法,是用户数据保密的一种手段。为了进行复杂处理、提高运算速度和节省存储空间,视图的定义深度一般不得超过三层。若三层视图仍不够用,则应在视图上定义临时表,在临时表上再定义视图。这样反复交迭定义,视图的深度就不受限制了。
对于某些与国家政治、经济、技术、军事和安全利益有关的信息系统,视图的作用更加重要。这些系统的基本表完成物理设计之后,立即在基本表上建立第一层视图,这层视图的列数和结构,与基本表的列数和结构是完全相同。并且规定,所有的程序员,一律只准在视图上操作。只有数据库管理员,带着多个人员共同掌握的“安全钥匙”,才能直接在基本表上操作。请读者想想:这是为什么?
11. 完整性约束表现在三个方面
域的完整性:用Check来实现约束,在数据库设计工具中,对字段的取值范围进行定义时,有一个Check按钮,通过它定义字段的值城。参照完整性:用PK、FK、表级触发器来实现。用户定义完整性:它是一些业务规则,用存储过程和触发器来实现。
12. 防止数据库设计打补丁的方法是“三少原则”
(1) 一个数据库中表的个数越少越好。只有表的个数少了,才能说明系统的E--R图少而精,去掉了重复的多余的实体,形成了对客观世界的高度抽象,进行了系统的数据集成,防止了打补丁式的设计;
(2) 一个表中组合主键的字段个数越少越好。因为主键的作用,一是建主键索引,二是做为子表的外键,所以组合主键的字段个数少了,不仅节省了运行时间,而且节省了索引存储空间;
(3) 一个表中的字段个数越少越好。只有字段的个数少了,才能说明在系统中不存在数据重复,且很少有数据冗余,更重要的是督促读者学会“列变行”,这样就防止了将子表中的字段拉入到主表中去,在主表中留下许多空余的字段。所谓“列变行”,就是将主表中的一部分内容拉出去,另外单独建一个子表。这个方法很简单,有的人就是不习惯、不采纳、不执行。
数据库设计的实用原则是:在数据冗余和处理速度之间找到合适的平衡点。“三少”是一个整体概念,综合观点,不能孤立某一个原则。该原则是相对的,不是绝对的。“三多”原则肯定是错误的。试想:若覆盖系统同样的功能,一百个实体(共一千个属性)的E--R图,肯定比二百个实体(共二千个属性)的E--R图,要好得多。
提倡“三少”原则,是叫读者学会利用数据库设计技术进行系统的数据集
13. 主键的取值方法
PK是供程序员使用的表间连接工具,可以是一无物理意义的数字串,由程序自动加1来实现。也可以是有物理意义的字段名或字段名的组合。不过前者比后者好。当PK是字段名的组合时,建议字段的个数不要太多,多了不但索引占用空间大,而且速度也慢(不建议作为联合主键的字段过多)。建议主键使用自增主键,对于自增索引性能会更好(《高性能Mysql》书中给出的建议,详细可度娘B+树索引优化关键字)。
14. 联合主键也许不是最好的选择(适合极少数场景)
设计表时,碰上联合主键,我们可以考虑不使用联合主键(我们设置联合主键的目的是防止重复插入数据),而是增加一个字段(例如:uk)并设置为唯一索引,当业务代码插入记录时,我们可以将原本的联合主键字段通过固定算法生成一个唯一值(例如: uk=hash(name+age+sex+phone) ),将唯一值赋给uk即可,此时插入数据即可保证数据不重复,同时查询操作时只需再计算一次uk即可查询唯一记录。
15. 日期时间类型字段设计方法
设计表时,遇到需要精确到时分秒的时间类型字段,建议采用时间戳类型存储,因为时间戳不区分时区,可以很容易的转换到对应时区的时间。(例如:跨时区表同步)
如果只需要保存到日期而不关心时分秒以及时区等条件,建议使用数字类型存储(例如:RQ字段:20190331),建立索引后,方便以后的查询统计。(例如:按月分组统计:group by SUBSTR(RQ, 1, 6) 按年分组统计:group by SUBSTR(RQ, 1, 4) )
16. 遇到并发修改单条记录的表设计方法
设计表时,如果表记录可能涉及到并发修改一条记录,建议增加一个version字段,业务代码中也使用version字段作为where条件之一来更新记录。
17. 常更新的记录,添加一个更新时间字段
设计表时,如果记录可能会被修改,那么建议添加更新时间字段(例如:updateTime),类型建议采用时间戳(timestamp)存储,对于solr增量更新索引有利,对于某些表同步场景也有用处。
18. oracle中,索引生效必须保证入参字段值类型与索引字段类型一致,否则无法利用索引
错误范例
正确用法
oracle中,对字段建立索引后,查询时使用索引需要注意,入参字段值必须与字段类型一致,否则oracle无法利用索引快速查询。
19. oracle中,group by 如果没有索引,会进行全表扫描,导致性能低下
索引区间扫描
group by非索引字段全表扫描
优化方式:
通过筛选过后,再进行分组,也就是说尽量少用having,可以让他扫描较少数据。
建立索引
oracle group by 如果只有一个字段参加分组,可以建立bitmap index在该字段上,(注意只有bitmapindex才有用,其他索引都无效)然后查询出字段只能使用count() 聚合函数(使用其他聚合函数都要使效率大大下降),这样可以使用索引;
如果必须包含其他聚合函数,那么请把使用其他聚合函数的字段和分组字段建立组合索引,可以大大提高效率;
如果有根据2个以上字段分组,那么把需要查询出来的字段全部组成一个组合索引,同样group by可以使用索引;
2个以上索引分组,如果查询出来的字段比分组字段多一个,然后多出来的查询字段为使用count()聚合函数,oracle同样会使用索引;
在试验过程统计,group by 使用索引比group by不使用索引可以提高平均15倍左右的效率。
20. 复合索引的优点和生效条件
概念:单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上;用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引);复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引;同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引;设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效。
使用:创建索引create index idx1 on table1(col1,col2,col3) ,查询select * from table1 where col1= A and col2= B and col3 = C 这时候查询优化器,不在扫描表了,而是直接的从索引中拿数据,因为索引中有这些数据,这叫覆盖式查询,这样的查询速度非常快。
注意事项:
对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高:select * from table1 where col1=A AND col2=B AND col3=D 如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引
何时使用复合索引:根据where条件建索引是极其重要的一个原则。注意不要过多用索引,否则对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中
复合索引会替代单一索引么:如果索引满足窄索引的情况下可以建立复合索引,这样可以节约空间和时间
备注:对一张表来说,如果有一个复合索引 on (col1,col2),就没有必要同时建立一个单索引 on col1 。如果查询条件需要,可以在已有单索引 on col1的情况下,添加复合索引on (col1,col2),对于效率有一定的提高。同时建立多字段(包含5、6个字段)的复合索引没有特别多的好处,相对而言,建立多个窄字段(仅包含一个,或顶多2个字段)的索引可以达到更好的效率和灵活性
复合索引where条件生效组合
21. oracle不走索引的7种常见情况
1. 没有 WHERE 子句
2. 使用 IS NULL 和 IS NOT NULL
SELECT ... FROM emp WHERE comm IS NULL; comm 列的索引会失效
3. WHERE 子句中使用函数
如果没有使用基于函数的索引,那么 where 子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。例如:
select * from staff where trunc(birthdate) = '01-MAY-82';
但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。
select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);
注意:对于 MIN, MAX 函数,Oracle 仍然使用索引。
4. 使用 LIKE ‘%T’ 进行模糊查询
5. WHERE 子句中使用不等于操作
不等于操作包括:<>, !=, NOT colum >= ?, NOT colum <= ?
对于这个限制条件可以通过 OR 替代,例如: colum <> 0 ===> colum>0 OR colum<0
6. 等于和范围索引不会被合并使用
SELECT emp_id, emp_m, salary_q ... FROM emp WHERE job='manager' AND deptno>10
job 和 deptno 都是非唯一索引,这种条件下 oracle 不会合并索引,它只会使用第一个索引。
7. 比较不匹配数据类型
dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。
select * from dept where dept_id = 900198;
这是因为 oracle 会自动把 where 子句转换成 to_number(dept_id)=900198,相当于使用函数,这样就限制了索引的使用。正确写法如下:
select * from dept where dept_id = '900198';
22. 分页查询order by排序时,不要使用单一字段排序,使用排序字段+唯一字段(主键)方式保证分页正常
如果order by的排序字段不是唯一字段,在分页操作时,如果排序字段值相同时,会出现分页数据相同或错乱的现象,因此添加一个唯一字段作为副排序字段就很有必要了。
23.
二、数据库服务优化
1. mysql5.5+提供了线程池插件,可以使用线程池中的少量线程服务大量连接。
线程池概论
Java中的连接池
2.
三、参考文章