浅谈如何优化sql语句提高数据库系统效率

浅谈如何优化 SQL 语句提高数据库系统效率陈庆 1（1.扬州市中医院,江苏省扬州市，225009）摘要：对于大型的企业或部门来说，每天都需要处理大量的数据业务，数据 库系统的设计变的异常复杂，而数据库的性能的好坏直接影响到各项业务能否顺利进行。然而数据库优化涉及到许多方面，如 ：良好的系统和数据库设计；优质的 SQL 语句编写；合适的数据表索引设计等，甚至包括各种硬件因素，如：网络性能；服务器的性能；操作系统的性能等。如果只想通过升级硬件系统来提升性能将会付出很大的经济代价，通 过优化 SQL 语句的编写提升数据库性能会是一个不错的选择。关键词：数据库设计;数据库系统;数据库性能;SQL 语句;优化1 引言SQL（Structured Query Language)结构化查询语言，是一种高级的非过程化编程语言，是沟通数据库服务器和客户端的重要工具，它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有在不同数据库系统上使用相同的 SQL 语言作为数据输入与管理的接口。它以记录集合作为操作对象，所有 SQL 语句接受集合作为输入，返回集合作为输出，这种集合特性允许一条 SQL 语句的输出作为另一条 SQL 语句的输入，所以 SQL 语句可以嵌套，这使它具有极大的灵活性和强大的功能，在多数情况下，在其他语言中需要一大段程序实现的功能只需要一个 SQL 语句就可以达到目的，这也意味着用SQL 语言可以写出非常复杂的语句。然而 SQL 语句的不同写法却会对数据库系统运行带来完全不同的效果，本文就如何优化 SQL 语句提升系统性能作一深入探讨。2 优化 SQL 语句的好处对于大部分基于数据库的应用程序来说，多数都是 C/S 或 B/S 架构，其与数据库的联系是通过客户端嵌入的 SQL 语句或调用数据库上的过程实现的。所以 SQL 语句质量的好坏会影响整个系统，因此优化 SQL 语句有以下几个好处：一是提高系统的运行效率，减少数据库死锁的风险；二是降低系统对硬件资源的消耗，节约投资；三是强化系统源代码的可读性，降低程序员修改程序的劳动强度。3 合理使用数据库索引索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。创 建 索 引 可 以 大 大 提 高 系 统 的 性 能 。 第 一 ， 通 过 创 建 唯 一 性 索 引 ， 可 以 保 证 数 据 库表 中 每 一 行 数 据 的 唯 一 性 。 第 二 ， 可 以 大 大 加 快 数 据 的 检 索 速 度 ， 这 也 是 创 建 索 引 的 最主 要 的 原 因 。 第 三 ， 可 以 加 速 表 和 表 之 间 的 连 接 ， 特 别 是 在 实 现 数 据 的 参 考 完 整 性 方 面特 别 有 意 义 。 第 四 ， 在 使 用 分 组 和 排 序 子 句 进 行 数 据 检 索 时 ， 同 样 可 以 显 著 减 少 查 询 中分 组 和 排 序 的 时 间 。 第 五 ， 通 过 使 用 索 引 ， 可 以 在 查 询 的 过 程 中 ， 使 用 优 化 器 ， 提 高 系统 的 性 能 。然 而 增 加 索 引 也 有 许 多 不 利 的 方 面 。 第 一 ， 创 建 索 引 和 维 护 索 引 要 耗 费 时 间 ， 这 种时 间 随 着 数 据 量 的 增 加 而 增 加 。 第 二 ， 索 引 需 要 占 物 理 空 间 ， 除 了 数 据 表 占 数 据 空 间 之外 ， 每 一 个 索 引 还 要 占 一 定 的 物 理 空 间 ， 如 果 要 建 立 聚 簇 索 引 ， 那 么 需 要 的 空 间 就 会 更大 。 第 三 ， 当 对 表 中 的 数 据 进 行 增 加 、 删 除 和 修 改 的 时 候 ， 索 引 也 要 动 态 的 维 护 ， 这 样就 降 低 了 数 据 的 维 护 速 度 。因此在写 SQL 语句时要合理使用数据库索引，这会让你的数据库运行效果事半功倍。4 优化 SQL 语句的方法4.1 合理的使用各种操作符首先要注意操作符的使用方法，虽然有时候不同的操作符实现的功能是一样的，但执行的效率却相差很多，这对于数据库的初学者是常常容易忽视的。4.1.1 IN（NOT IN）与 EXISTS（NOT EXISTS）操作符的区别我们在调用数据的时候会将一列和一系列值相比较，最简单的办法就是在 WHERE 子句中使用子查询，而在 WHERE 子句中有两种方式的子查询。下面以 zy_dxsfls，zy_brzlls 两个表为例，如下所示：第一种方式使用 IN 操作符，Select a.blh,a.sfks,a.sfrq,a.yzm,a.zfje,a.xzysFrom zy_dxsfls awhere a.blh in (select b.blh from zy_brzlls b where b.csrq= 1970-1-1)；第二种方式使用 EXIST 操作符，Select a.blh,a.sfks,a.sfrq,a.yzm,a.zfje,a.xzys From zy_dxsfls awhere exists (select 1 from zy_brzlls b where b.blh=a.blh and b.csrq=1970-1-1)。虽然两种方法得出的结果是一样的，但是使用的时候却是有区别的。IN 操作符适合于zy_brzlls 表大而 zy_dxsfls 表小的情况，而 EXISTS 操作符适合于 zy_brzlls 表小而zy_dxsfls 表大的情况。因此使用的时候应视情况而定，不能盲目使用，NOT IN 操作符和NOT EXISTS 操作符也是一样的。4.1.2 巧用比较条件（、=、2 与A=3 的效果就有很大的区别了，因为 A2 时数据库会先找出为 2 的记录再进行比较，而A=3 时数据库则直接找到等于 3 的记录，所以选择好的比较条件会提高数据库的执行效率。4.1.3 尽量少用 UNION 操作符UNION 操作符是用来合并查询结果的，最常见的是过程表与历史表的合并。在执行该操作时数据库先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致数据库系统效率下降。不过可以采用 UNION ALL 操作符替代 UNION，因为 UNION ALL 操作只是简单的将两个结果合并后就返回。4.1.4 变相使用 IS NULL（IS NOT NULL）操作符- 3 -NULL 值即空值的意思，根据数据库的特性，是不能使用包含 NULL 值的字段作为索引的，即使对该字段建立了索引，只要某行上面有 NULL 值，该字段就不能使用索引。但是我们可以使用变相方法来表示 NULL 值，如将某行上的 NULL 值用 0 表示，这样 WHERE 条件语句“a is null”就可以改成“a=0”来实现；另外还 可以设置字段不允许为空，而用一个缺省值代替空值，如一个时间类型的字段，可以将默认时间设为“1900-01-01”来表示空值。4.1.5 合理使用 LIKE 操作符LIKE 操作符是可以使用通配符查询的语句，里面的通配符组合可以达到任意多个，但是如果用得不好则会产生性能上的问题，如 yzm like %阿莫西林% 这种查询不会使用数据库索引，而 yzm like 阿莫西林%则会使用数据库索引。用一个实例测试，如表 1 所示：SQL 语句 数据库用时Select top 10000 blh,sfks,sfrq,yzm,zfje,xzysFrom zy_dxsflswhere yzm like 阿莫西林%356msSelect top 10000 blh,sfks,sfrq,yzm,zfje,xzysFrom zy_dxsflswhere yzm like %阿莫西林%1420ms表14.1.6 避免在 WHERE 子句中做函数计算这样做会使数据库在表的每行上进行运算，从而导致该列的索引不能被使用而触发全表扫描。我们可以从下列对比中看出效果，如表 2 所示：SQL 语句 数据库用时Select * From zy_brzlls where year(csrq)、 !、!=1000；Select id From Emp Where sal=1000 and empno=1000”条件在记录集内比率为 99%，而“empno=1000 的记录 M 条，接着处理条件 empno=1000，同样只要在第一次查询的结果集中进行查询即可，得出 B 条，所以总共查询了A+B 条记录。可以看出数据库扫描的数据量 A+B=10000Order by a.blh；如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序。SQL 语句可以改为：Select a.BLH,b.SFKS,b.SFRQ,b.YZM,b.ZFJE,Into zy_brzl_zyFrom zy_brzl a,zy_dxsf bWhere a.blh=b.blh and a.zt=在院Order by a.blh；然后以下面的方式在临时表中查询:Select * From zy_brzl_zy Where zfje =10000；由于临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘的 I/O 操作，所以查询工作量可以得到大幅减少。但是需要注意临时表创建后不会反映主表的修改，在主表中数据频繁修改的情况下，不要丢失数据。5 应用实例以作者所在单位为例，原本使用的是 IBM3650 服务器，100 多个客户端，应用程序比较单一，整个业务系统运行还算平稳。可是仅仅过了 2 年多的时间，单位业务量便出现急剧增长，应用程序不断扩充，现在已经拥有了 300 个客户端，而系统的运行也出现了问题，客户端的应用程序会不时的出现“卡机”现象，数据库还会出现“死锁”的情况。经过深入研究发现，每次出现问题都是一些特定的 SQL 语句造成的，后来经过大规模的优化 SQL语句和数据库索引使问题得到了圆满的解决，避免了硬件资源在未到使用寿命之前就被更换掉的命运，从而为单位节约了大量人力和物力，提高了经济效益。6 总结对于拥有几百甚至上千台客户端的企业或部门来说，每天都有大量的并发业务数据，如果应用程序中的 SQL 语句写的不好，就需要消耗更多的硬件资源来满足系统的运行，从而导致企业或部门花费更多的人力和财力去提升系统硬件。因此优秀的 SQL 语句将为整个业务系统的执行效率带来很大的提高，减少数据库死锁的风险，降低数据库维护成本，为大型的企业或部