浅谈数据库优化

作为一名后端开发人员,数据库优化是迟早要面临的问题。数据库优化对性能的提升有时是十倍甚至上百倍的量级。下面我就自己的了解对数据库优化进行一下总结分析。

如何构造一个合适的数据库结构

一个合适的数据库结构对整个项目都是至关重要的,建表时首要的就是要遵循范式要求,一般至少要满足第三范式。对于各个范式的含义,下面我做一下简单的回忆。

1NF

第一范式,简单来说就是列不可分,每一列的值都是唯一的,而不是一个集合。而所谓的不可分是一个相对的概念,比如同样的一个列’time’在不同的情况下的要求是不一样的,有的时候要求一个精确的时间到秒,而有的时候要求年份就可以了。所以我们不能用年份的标准去衡量精确到秒,而说其不满足第一范式。

2NF

第二范式,简单来说就是消除了非主属性对码的部分函数依赖。为什么要消除这种依赖呢?首先我举一个例子。

1
SNo     CNo     SName     CName    Credit

上面关系的primary key 是(SNo, CNo)共同组成的,其他属性都依赖于主关键字,但是SName并非完全依赖于主码,还依赖于主属性SNo,这就导致了数据冗余、更新异常、插入异常、删除异常。

3NF

第三范式,简单来说就是消除了非主属性对码的传递函数依赖。我还是举一个例子

1
SNo     DNo     DName

主键是SNo,而属性DName对主键是传递函数依赖,DNo依赖于SNo,并且DName依赖于DNo,这样DName就传递函数依赖于主键SNo。
不满足3NF的数据结构,可以将表拆分,使其满足3NF,这样跟合理一些。

如何构造一个合适查询语句

想达到相同的结果,有多种不同的查询策略。比如连接和查询顺序的不同,可能使查询效率有很大的差别。
在关系代数运算表达式中的一个很简单的原则,即先做选择后做连接,这种顺序上的不同,会使执行效率有戏剧性的改变。
在数据库查询过程中,系统也会相应的根据数据库结构构造出一个能最小化查询执行代价的查询计划。系统对查询所做的优化一方面出现在关系代数级,即系统尝试找出一个与给定的表达式等价但执行起来更为有效的表达式。另一方面是对于处理查询选择一个详细的策略,比如统计信息、选择可用的特定索引。
虽然数据库可以为用户选择一个合适高效的查询策略,但是也不能把所有的优化工作交给数据库管理系统来做,系统的优化是有一定局限的,况且系统这种优化本身也是一种消耗。

如何创建一个合适的索引

数据库索引是对数据库表中一个或多个列的值进行排序的结构。例如这样一个查询

1
select * from table1 where id =10000;

如果没有索引,必须要遍历整个表,直到id等于10000的这一行被找到为止,有了索引(必须是id这一列上建立的索引),即可在索引中查找。由于索引是经过某种算法优化过的,因而查找的次数要少得多。可见索引是用来定位的。
通常情况下建立索引是加快查询速度的有效手段。但索引不是万能的,靠索引不一定能实现对查询效率的提高。因此在建立索引时恰到好处比较关键。
选择索引存取方法的一般规则:

  1. 如果一个(或一组)属性经常在查询条件中出现,则要考虑在这个(或这组)属性上建立索引(或组合索引);
  2. 如果一个属性经常作为最大值和最小值等聚集函数的参数,则考虑在这个属性上建立索引;
  3. 如果一个(或一组)属性经常在连接操作的连接条件中出现则考虑这个(或这组)属性上建立索引(或组合索引。

关系上定义的索引数过多会带来较多的额外开销

  1. 维护索引的开销;
  2. 查找索引的开销。

数据库索引包括聚簇索引和非聚簇索引。为提高某个属性(或属性组)的查询速度,把这个或者这些属性(成为聚簇码)上具有相同值的元组几种存放在连续的物理块成为聚簇。
聚簇的用途:

  1. 大大提高了按聚簇码查询的效率;
  2. 节省存储空间。

聚簇的局限性:

  1. 建立聚簇只能提高某些特定应用的性能;
  2. 建立与维护聚簇开销很大。

HASH存取方法的选择

当一个关系满足下列条件时,可以选择HASH存取方法:

  1. 改关系的属性主要出现在等值连接条件中或者主要出现在相等比较选择条件中;
  2. 该关系的大小预知,而且不变或者该关系大小可变但所选用的DBMS提供了动态的HASH存取方法。