数据库性能优化方案.docx

智欣仙子数据库性能优化方案随着计算机技术越来越广泛地应用于国民经济的各个领域，在计算机硬件不断微型化的同时，应用系统向着复杂化、大型化的方向发展。数据库的性能优化大体上可以分成数据库结构设计、查询语句优化、缓存查询结果。数据库结构设计在软件系统开发中，数据库设计应遵循必要的数据库范式理论，以减少冗余、保证数据的完整性与正确性。只有在合适的数据库产品上设计出合理的数据库模型，才能降低整个系统的编程和维护难度，提高系统的实际运行效率。虽然对于小项目或中等规模的项目，开发人员可以很容易地利用范式理论设计出一套符合要求的数据库，但对于一个包含大型数据库的软件项目，就必须有一套完整的设计原则与技巧。数据冗余数据应该尽可能少地冗余，这意味着重复数据应该减少到最少。比如说，一个部门雇员的电话不应该被存储在不同的表中， 因为这里的电话号码是雇员的一个属性。如果存在过多的冗余数据，这就意味着要占用了更多的物理空间，同时也对数据的维护和一致性检查带来了问题，当这个员工的电话号码变化时，冗余数据会导致对多个表的更新动作，如果有一个表不幸被忽略了，那么就可能导致数据的不一致性。添加索引对于大的数据库表，合理的索引能够提高整个数据库的操作效率。在索引设计中，索引字段应挑选重复值较少的字段；在对建有复合索引的字段进行检索时，应注意按照复合索引字段建立的顺序进行。例如，如果对一个万多条记录的流水表以日期和流水号为序建立复合索引，由于在该表中日期的重复值接近整个表的记录数，用流水号进行查询所用的时间接近秒；而如果以流水号为索引字段建立索引进行相同的查询，所用时间不到秒。因此在大型数据库设计中，只有进行合理的索引字段选择，才能有效提高整个数据库的操作效率。分类拆分对于经常使用的表（如某些参数表或代码对照表），由于其使用频率很高，要尽量减少表中的记录数量。例如，银行的户主账表原来设计成一张表，虽然可以方便程序的设计与维护，但经过分析发现，由于数据量太大，会影响数据的迅速定位。如果将户主账表分别设计为活期户主账、定期户主账及对公户主账等，则可以大大提高查询效率。行为拆分对用户的行为进行分析，那些是需要频繁操作的数据，那些是乘积数据，对这两种数据分别存放。比如，一个会员系统的会员基本属性有：会员ID、登录账号、登录密码、网络昵称、联系方式、联系地址等。我们设想一下，现有一个会员A要求登录（select），另外有一个会员B要求更新自己的联系方式（update），并且上面前面提到的所有属性都建立在一张表上面，由于数据库的锁机制，会员B必须等待会员A的验证通过之后才能执行自己修改请求；如果还有一个会员C也要求登录（select），那么会员B就需要等待会员A和会员C都通过验证之后才会执行自己的修改请求。这种情况我们就可以将基本属性分解成两个表：会员编号、登录账号、登录密码，会员编号、网络昵称、联系方式、联系地址。标签设计几乎每个交互式站点都提供搜索功能，许多CMS信息系统还设计了一个关键词标签用于对文章的检索。对于搜索语句大部分人都会想到和用到like语句，这是基本思路无可否非，但如果被检索的数据表存在上百万的数据，这样检索所花费的时间就实在不敢恭维了。那么怎么设计标签才比较合理呢！大家都知道用“=”号肯定比用“like”的性能更好，那么我们怎么结合“=”来检索标签呢。我的思路就是把标签单独建表，和文章进行关联。如标签表（tmp_tags）文章标示(id)标签(key)1234PHP1234MySql1235PHP文章表（tmp_article）文章唯一标示(id)其它属性12341235SQL：select a.* from tmp_article as a, tmp_tags as b where a.id=b.id and b.key=?优化查询语句避免select *通常大家在使用select语句时都会这样使用select * from tablename。但数据库服务器在解析时，如果碰到 *，则会先分析表的结构，然后把表的所有字段名再罗列出来。这就增加了分析的时间。我们试想一下，如果表中有30个字段500条记录，而我们只使用到了其中少部分结果，这是不是对服务器宽带资源造成了不比较的浪费？如果我们这样写 select top 10 字段列 from tablename 是不是就好多了。不要吝惜top道理和上面一样，Mysql数据库中使用limit关键词。from表名顺序SQLSERVER的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表driving table）将被最先处理，在FROM子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表，当SQLSERVER处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表（FROM子句中最后的那个表)并对记录进行排序；然后扫描第二个表（FROM子句中最后第二个表)；最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。例如: 表 TAB1有 16384 条记录，表 TAB2 有5条记录，选择TAB2作为基础表 (最好的方法)：select count(*) from TAB1 a, TAB2 bWhere条件组合避免使用is nullselect id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0避免对字段进行函数、表达式操作select id from t where num/2=100应改为：select id from t where num=100*2select id from t where substring(name，1，3)=abc-name以abc开头的idselect id from t where datediff(day，createdate，2005-11-30)=0-2005-11-30生成的id慎用in 、 not inselect id from t where num in(1，2，3)对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3where字句的连接SQLSERVER采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。例如：SELECT * FROM EMP EWHERE 25 50000AND JOB = MANAGER正确使用UNION和UNION ALLUNION将联合两个相类似的记录集，然后搜索重复的记录并排除；UNION ALL则不关心有没有重复记录。如果确定记录集中没有重复的记录或者程序不关心是否有重复记录，则用union all要比union更合理。缓存查询结果现在越来越多的人们关注缓存的使用，苹果浏览器的缓存如果不强制清除会一直保存，就算是服务器资源做了什么修改，客户端在没有清除缓存的情况下也是看不到服务器的改变。另外http头、Web框架等都以及集成了缓存，数据库也越来越多的完善dba