数据访问性能分析与优化.docVIP

精品论文 参考文献 数据访问性能分析与优化 甘松灵 刘翠霞　甘立新/东北农业大学 【摘　要】随着数据量访问的增加，服务器的响应时间越来越长，特别是对于海量级数据的访问，数据库的性能直接影响了应用程序的处理效率，因此数据库的优化显得尤为重要。一般情况下，影响数据库性能的因素包括以下几个方面：数据库设计的合理性；SQL语句的执行效率；数据库参数的配置情况以及硬件资源和操作系统对数据库的影响。 【关键词】数据库；性能 一、数据库设计 数据库的设计一般要求满足第三范式，即属性具有原子性，不可分解；记录有唯一标识，实体具有唯一性；字段不存在冗余，任何字段不能由其他字段派生出来。但有时为了开发的需求，采用逆范式的标准，降低第三范式的要求，适当的保留冗余数据。 假设某网站为了统计相片的访问次数，所建立的数据模型如下： 相册集album（albumid 主键，albumname 相册集名称）； 相片photo（photoid 主键，albumid 外键，photoname 图片名称，photopath 图片存储路径，phototraffic 图片访问量）。 albumid为10001的相册集内的所有相片的访问总量的SQL语句如下： Select sum(t2.phototraffic) as visited_amout from album t1, photo t2 where t1.albumid=t2.albumid and t1.albumid=10001; 当存储的数据量较大时，表的扫描时间会越来越长，为了提高程序的运行效率，在album中适当的建立冗余子段albumtraffic，记录图片的访问总量，查询语句如下： Select alubmtraffic from album where albumid=10001; 采用逆范式的数据模型，避免了photo表的统计查询，提高了访问效率。 二、SQL语句分析与优化 SQL语句的优化一般通过show status命令来了解SQL语句的执行频率，定位于执行效率较低的SQL语句，然后采用explain工具分析语句的执行情况，确定执行效率低的原因，从而采取相应的优化措施。 建立以下数据测试环境： 部门表：dept(deptno INT，dname VARCHAR(20)，loc VARCHAR(13)) ； 员工表：emp(empno INT，ename VARCHAR(20)，job VARCHAR(9)，mgr INT，hiredate DATE，sal DECIMAL(7,2)，comm DECIMAL(7,2)，deptno INT)； 通过存储过程来创建海量数据表，调用call insert_emp(0向emp添记录，数据的物理存储空间大约为625M，SQL语句执行效率如下： # Query_time: 9.187500 Rows_examined: select * from emp where empno=6000; 语句的执行时间约为9.18m，扫描的行数进行了全表扫描，花费的时间较长，语句的执行效率较低。为了提高数据的查询速度，在empno上建立主索引，查询语句执行情况的分析如下： table: emp；type: const；possible_keys: PRIMARY；key: PRIMARY；key_len: 3；ref: const；rows: 1 表的扫描行数为一行，采用的索引类型为主索引，查询的速度在毫秒级以内，极大的提供了SQL语句的执行效率。 但是创建索引后，索引文件由1K增加到145M左右，增加了物理存储空间；另外DML语句的执行效率受到了影响，数据在增删改时，索引文件也需要相应的改变。因此查询条件频繁的字段可以建立索引，提高查询效率，更新频繁的字段不适合建立索引。对于大量数据的插入操作时，可以使用以下方法，提高系统的性能： alter table table_name disable keys; loading data; alter table table_name enable keys; 默认情况，系统对所有的group by col1,col2进行排序，这与在查询中指定order by col