数据库设计与优化数据库设计与优化

一、设计技巧 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+提供了线程池插件，可以使用线程池中的少量线程服务大量连接。