数据访问性能分析与优化
  摘要】随着数据量访问的增加，服务器的响应时间越来越长，特别是对于海量级数据的访问，数据库的性能直接影响了应用程序的处理效率，因此数据库的优化显得尤为重要。一般情况下，影响数据库性能的因素包括以下几个方面：数据库设计的合理性；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,18000000)向emp添加18000000条记录，数据的物理存储空间大约为625M，SQL语句执行效率如下：
        # Query_time: 9.187500 Rows_examined: 16000000
        select * from emp where empno=6000;
        语句的执行时间约为9.18m，扫描的行数为16000000，进行了全表扫描，花费的时间较长，语句的执行效率较低。为了提高数据的查询速度，在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 col1, col2类似，如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序，如下查询语句：
        Select  t1.dname,sum(t2.sal) as total_sal from dept t1,emp t2 where t1.deptno=t2.deptno group by t1.dname order by null。
        三、数据的水平分割与垂直分割
        对于数据达到上千万或上亿条的海量表，为了提高数据的访问效率，可以根据实际需要，将表进行水平分割。
        有些表记录数并不多，大约为2、3万条记录，但是字段却很长，表占用空间很大，检索表时需要执行大量I/O，严重降低了系统性能，这个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。
        四、数据读写分离
        随着一个网站的业务不断扩展，数据不断增加，数据库的压力也会越来越大，对数据库或者SQL的基本优化可能达不到最终的效果，我们可以采用读写分离的策略。
        主库master用来写入，slave1—slave3都用来做select，每个数据库分担的压力小了很多，但要实现这种方式，需要程序特别设计，写都操作master，读都操作slave，给程序开发带来了额外负担。也可以使用中间件来实现这个代理，对程序来读写哪些数据库是透明的。中间件可以采用Amoeba软件，它充当SQL路由功能，专注于分布式数据库代理层（Database Proxy）开发，位于与 Client、DB Server(s)之间,对客户端透明，具有负载均衡、高可用性、SQL过滤、读写分离、可路由相关的到目标数据库、可并发请求多台数据库合并结果。 通过Amoeba能够完成多数据源的高可用、负载均衡、的功能。
数据库优化sql语句【参考文献】
[1] 阳小华.分布式WWW信息收集技术[J].计算机工程与应用，2000，（5）．

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。