《浅谈SQL的优化方法(1)1》由会员分享，可在线阅读，更多相关《浅谈SQL的优化方法(1)1（6页珍藏版）》请在金锄头文库上搜索。
1、浅谈SQL的优化方法摘要：对于人数据量的数据库査询，SQL的优化是提高系统性能的重要手段。木文介绍了 SQL优化的原理和方法，结合SQL优化方法在海关风险平台的实际应川，探讨海关风险平 台系统在SQL优化方面的解决方法。关键词：SQL优化；关系型数据库；风险平台1前言决定DBMS的性能的凶索有两个：硕件和软件。使用频率高的CPU、使用多处理器、加 大内存容量、增加Cache.提高网络速度等这些都非常有效的硬件调优方式。不过对映件进 行调优对系统性能的提高是有限的，虽然有非常好的硬件条件但是如果编写的SQL质量非 常差的话，系统的性能也不会有明显的改善，而如果对SQL语句进行充分的优化，即使硬
2、件条件稍差，系统性能的变化也是非常惊人的。2、SQL调优的基本原则“二八原理”是一个普遍的真理，特别是在计算机的世界中表现得更加明显，那就是20% 的代码资源占川了 80%的总资源消耗。SQL也是一种代码，因此它也符合这个原理。在进 行SQL调优的时候应该把主要的精力放在这20%最消耗系统资源的SQL语句中，不要想把 所有的SQL语句都调到最优的状态。很多的DBMS都提供了非常好的用來分析系统中所有的SQL语句资源消耗的工具，借助这 些工具发现占用系统资源最多的SQL语句，然厉尝试对它们进行优化。优化后再次执行分 析、迭代这一过程，立到系统中没有明显的系统资源消耗异常的SQL语句为止。3、索引
3、索引是数据库调优最根本的优化方法，很多的优化手段都是围绕着索引展开的，可以说索 引是一切优化手段的“内功”，而所有的优化手段都是由索引衍生出来的“招式”而己。根据索引的顺序与数据库表的物理顺序是否相同，可以把索引分成两类：聚族索引，物理 表的顺序与索引的顺序相同；非聚族索引，数据库表的物理顺序与索引顺序不相同。由于索引需要占据一定的存储空间，而且索引还会降低数据插入、更新和删除的速度，所以 应该只创建必耍的索引，一般在检索时用的字段中创建索引。索引还会造成存储碎片的问题。当删除一条记录时将会导致対应索引中该记录的対应项为 空，由于索引是采川B树结构存储的，所以对于的索引项并不会被删除，经过一段
4、时I可的 增、删、改操作后，数据库中就会出现人量的存储碎片，这和磁盘碎片、内存碎片产生的原 理是类似的。如果发现索引中存在过多的存储碎片，要进行“碎片整理”了，最方便的手段 就是重建索引，重建索引是将之询创建的索引删除然后重新创建索引，可以首先用DROP INDEX语句删除索弓I，然后用ALTER TABLE语句重新创建索引。4、全表扫描和索引查找一般地，系统访问数据库中的数据，可以使用两种方法：全表扫描和索引查找。全表扫描, 就是指系统必须在数据表中逐条检索表中的的每条记录，以检查该记录是否肚配检索条件。 全表扌I描有可能会造成巨大的性能损失，当然也有可能不会影响性能，这取决于表中的数据 量
5、，如果表中有上千万条甚至上亿条的记录的话，全表扫描的速度会非常慢，而如果表中只 有儿条、儿十条记录的话全表扫描的性能消耗可以忽略不计了。当表中的数据量比较小的吋 候，使川全表扫描非常有川。但是随着表中的数据屋的增加，全表扫描会导致系统性能的严 重下降。如果表中有索引并H待匹配条件符合索引的要求，DBMS不会执行全表扫描，而 是育接到索引中杳找，这将大大加快检索的速度。DBMS中都有杳询优化器，它会根据分布的统计信息生成该杏询语句的优化执行规划，以 提高访问数据的效率为口标,确定是使川全表扫描还是索引查找。注意并不是表中存在索引， 在进行检索的时候就会使川到索引查找，如果使川不当，检索的过程仍然
6、会采川全表扫描， 这样索引就起不到作用了。5、优化的方法下面将列出一些常用的优化手段，并结合海关风险平台系统的数据表进行实际的应用说 明，在海关风险平台经常使用到衣ENTRY_HEAD(报关单衣头)、ENTRY_L1ST (报关单表 体)和ENTRY_WORDFLOW(工作流表)，ENTRY_HEAD衣中有记录三千多万条， ENTRY.LIST表中有记录一亿多条，ENTRY_WORDFLOW 表中有记录三亿多条，这三个 表保存着从海关总署分发到黄埔海关的报关单信息。三个表中的记录数都比较大。三个表都 在报关单字段ENTRY.ID上建有索引。5.1、创建必要的索引在经常需耍进行检索的字段上创建索
7、引，比如经常耍按照企业代码进行检索，那么 就应该衣企业代码字段上建立索引；如杲经常要按照报关单的申报时间进行检索，就应该在 申报时间字段上建立索引。如ENTRY_HEAD表在企业代码OWNER_CODE字段上就建立 了索引。创建索引会给检索带来冃人的性能提升，因此在发现检索速度过慢的时候应该首先 想到的就是创建索引。5.2、使用预编译查询程序通常根据用八的输入來动态执行SQL语句,这时应该尽量使用参数化的SQL, 这样不仅可以避免SQL注入漏洞攻击，最重要的是数据库会对这些参数化SQL执行预编译。 这样第一次执行的时候DBMS会为这个SQL语句进行查询优化并冃执行预编译，以后再执 行这个SQL
8、的时候就垃接使用预编译的结果，这样可以大人提高执行的速度。5.3、SELECT语句中避免使用*“SELECT*”比较简单，但是除非确实需要检索所有的列，否则将会检索出不需 要的列，这会增加网络的负载和服务器的资源消耗。如ENTRY_HEAD表中有儿十个字段， 如果只有检索出某个月申报的报关单号和申报企业名称，使用“SELECT*”将会返冋很多 使用不到的字段。即使需要检索所有的列，也不要使用“SELECT*”，因为这是非常低效的 方法，DBMS在解禅的过程中，会将*依次转换成所有的列名，这意味着将耗费更多的时间。5.4、调整WHERE子句中的连接顺序DBMS-般采用自下而上的顺序解析WHERE
9、子句，根据这个原理，表连接最好写 在其他WHERE条件之前，那样可以过滤最人数量记录。例如，在按商品号前4位相同(或者前6位、8位相同)计算该商品的关区平均箱重的 时候，要剔除同一报关氓里而有不同类型的商品的报关叽 为什么要剔除存在不同类商品的 报关单呢？举个简单的例子解释，如果一张报关也里而同时有钢铁和棉花两种商品，那得到 的钢铁和棉花的平均箱重肯定都是不正确的。SELECT A.ENTRY_ID,A.OWNER_CODE,A.NET_WT,B.QTY_1FROM ENTRY_HEAD AINNER JOIN ENTRY.LIST B ON A.ENTRYD=B.ENTRY_IDWHERE
10、( A.TRAF_MODE=4 OR TRAF_MODE=5,)AND 1=(SELECT COUNT(DISTINCT(LEFT(C.CODE_TS,4) FROM ENTRY_LIST C WHERE C.ENTRYD二A.ENTRYD)我们将子査询的条件放在最前面，比如下面的SQL语句的性能就比较好：SELECT A.ENTRY_ID,A.OWNER_CODE,A.NET_WT,B.QTY_1FROM ENTRY. HE AD AINNER JOIN ENTRY_L1ST B ON A.ENTRY_ID=B.ENTRYDWHERE 1=(SELECT COUNT(DISTINCT(LEF
11、T(C.CODE_TS,4) FROM ENTRY_LIST C WHERE C.ENTRY_ID=A.ENTRYD)AND (A.TRAF_MODE=4 ORTRAF_MODE=5,)使用下面的SQL语句来判断该报关单里面只有一类商品是不正确的，如果不小心就会 犯这样的错误：SELECT A.ENTRYD,LEFT(B.C0DE_TS,4) FROM ENTRY_HEAD AINNER JOIN ENTRY_LIST B ON A.ENTRYD=B.ENTRYDGROUP BY A.ENTRYEFT(B.CODE_TS,4)HAVING COUNT(*)=1举个简单的例子就知道上面的SQL是
12、错误的，同一报关单，在报关单表体ENTRY_LIST 里面有两条商品记录，它们的商品编号的前4位都不相同，但它们按照报关单号和商品编号 前4位相同归类后却满足上而的查询条件。如果WHERE子句中存在索引列，优化器会先杏找索引列，以过滤掉大量的记录，不 论索引是放在WHERE子句的最前面还是最后面。卞面的两个查询语句的执行都耗时不川 -秒钟。其中在ENTRY_LIST(报关单表体)表中ENTRYJD字段上建有索引，而QTY_1字 段上没有索引。SELECT * FROM ENTRY.LIST WHERE ENTRY_ID=,520465895458512500* AND QTY_1 = 1;SE
13、LECT * FROM ENTRY_LIST HERE QTY_1=1 AND ENTRY_ID=,520465895458512500*;5.5、使用表的别名当在SQL语句中有连接多个表时，请使用表的别名并把别名前缀置于每个列名上。 这样就可以减少解析的吋间并减少那些由列名歧义引起的语法错误。5.6避免在索引列上使用计算在WHERE子句中，如果索引列是计算或者函数的一部分，DBMS的优化器将不会 使川索引而是使川全表扫描。例如下血的SQL语句用于检索月薪的12倍大于25000元的员工：SELECT * FROM T_Employee WHERE FSalary* 1225000将上面的SQL
14、语旬改写成下面的将会使川索引查找：SELECT * FROM T_Employee WHERE FSalary25000/12下而是一个避免在索引列运算而提高系统性能的例子。在做海关风险平台的指标“陆运转 关核销分析”时， 要从 H2000.ENTRY_HEAD 、 H2000.ENTRY_LIST 和 H2000.EX_DECL_CUR_HEAD表中取数据进行分析。ENTRY.HEAD 与 ENTRY_LIST 表通过报关单号 ENTRY_1D 字段连接。表 EX_DECL_CUR_HEAD 与 ENTRY.HEAD 之间 的对应关系是 H2000.EX_DECL_CUR_HEAD.PRE_
15、NO( 16 位)后 13 位(去掉 PRE_NO 列前面的三个 0), 等J* H2000.ENTRY_HEAD.TRAF_NAME (14位)出掉前面的“ ”，其他的过滤条件是需 求中规定的，可参数传入。SELECT A.D_DATE,C.REL_TIME,C.CHECK_TIMEZ C.CHECK_MARK,A . ENTRYJD, A. I_E_FLAGZ SUBSTRING (A . TRAF_NAME, 2 , LEN (A . TRAF_NAME) ) AS 转关单号,A.I_E_FLAG,CASE A I_E_FLAG WHEN 1 I 1 THEN A I_E_PORT WH
16、EN 1 E1 THEN ADECL_PORT ENDAS转岀关f CASE A. I E FLAG WHEN 1 I 1 THEN A DECL PORT WHEN !E 1 THEN A. I E PORT ENDAS转入关,A.TRAF_NAME,A.TRADE_NAME,A.OWNER_NAME,A.PACK_NOZA.GROSS_WT,B.TRADE_TOTAL,B.REAL_RSV1z C.CHECK_OP_IDFROM RiskH2000.risk.ENTRY_HEAD AINNER JOIN RiskH2000.risk.ENTRY_LIST BON A.ENTRY_ID =B.ENTRY_IDIN
