《高效率SQL的基础知识》由会员分享，可在线阅读，更多相关《高效率SQL的基础知识（12页珍藏版）》请在金锄头文库上搜索。
1、高效率SQL的基础知识一个高效率的数据库系统是从两个方面来评价的：响应时间和吞吐量。在应用系统幵发阶段，由于幵发库上的数据比较少，在SQL语句的编写上感觉不出各种写法的性能差异，在将应用系统提交实际应用后，随着数据库中数据 的增加，系统的响应速度就会成为最需要解决的主要问题之一。缩短系统的响应时间，增加操作的并发度，可以提高系统的吞吐量。要缩短系统的响应时间，就需要可以高效率执行的SQL语句。高效SQL语句的基本原则,是充分合理的利用索引，避免表扫描。理解索引大多数情况下，数据库使用索引来检索表，优化器根据用户定义的索引来提高执行性能。但是，如果在 SQL语句的where子句中写的SQL代码不
2、合理，就会 造成优化器忽略索引而采用全表扫描，而这种SQL语句就是所谓的劣质SQL语句 在编写SQL语句时需要了解优化器根据何种原则来使用索引，这将有助于写出高 性能的SQL语句。IS NULL 与 IS NOT NULL以NULL值做条件时，将无法使用包含 NULL值的列上的索引。即使索引有多 列这样的情况下，只要这些列中有一列含有 null ，该列就会从索引中排除。也就 是说如果某列存在空值，在使用 NULL值做条件时，即使对该列建索引也不会提高性能列的联接本节应该阐述列被包含到表达式中导致不能使用索引。对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。例：假定有一个职
3、工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAM和LAST_NAME现在要查询一个叫比尔.克林顿（Bill Cliton ）的 职工。下面是一个采用联接查询的SQL语句，select * from employsswhere first_ name|last_ name =Bill Clito n;改进方法：select * from employeewhere first_name = Bill and last_name = Cliton 带通配符（%的like语句select * from employee where last_ name like %cl
4、it on %;由于通配符（%在搜寻词首出现，所以数据库将不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有数，通配符如此使用会降低查询速度。当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：select * from employee where last_ name like c%;Order by 语句ORDER B语句决定了数据库如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。需要仔
5、细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。NOTn ot （status 二VALID）和（statusoVALID）哪个效率更高salary3000 和 salary3000 和 salaryv = 2999 or salary=3001（假设是整数）哪一个效率更高在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and （与）、or （或）以及not （非）。NOT可用来对任何逻辑运算符号 取
6、反。例：. where n ot （status 二VALID）要使用NOT则应在取反的短语前面加上括号， 并在短语前面加上 NOT运算符。 NOT运算符包含在另外一个逻辑运算符中， 这就是不等于（）运算符。换句话说， 即使不在查询 where子句中显式地加入 NOT词，NOT仍在运算符中，见下例：. where status INVALID;再例：select * from employee where salary3000; 解决方法，不使用 NOT：select * from employee where salary3000; 这两种查询的结果一样，但是第二种查询方案会比第一种查询方案
7、更快些。第二种查询会对 salary 列使用索引，而第一种查询则不会使用索引。IN 和 EXISTS将一列和一系列值相比较，常用的方法是在 where 子句中使用子查询。在 where 子句中可以使用两种格式的子查询。第一种格式是使用 IN 操作符：. where column in(select * from . where .);第二种格式是使用 EXIST 操作符：. where exists (select X from .where .); 采用第二种格式要比第一种格式的效率高。第二种格式中，子查询以select X幵始。运用EXISTS子句不管子查询从 表中抽取什么数据它只查看 w
8、here 子句。这样优化器就不必遍历整个表而仅根据 索引就可完成工作(这里假定在 where 语句中使用的列存在索引) 。使用EXIST,数据库系统会首先检查主查询，然后运行子查询直到它找到第一 个匹配项。使用 IN 子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了 索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕， 存放在临时表中以后再执行主查询。所以使用EXISTS通常比使用IN查询速度快。应尽可能使用NOT EXISTS来代替NOT IN,尽管二者都使用了 NOT(不能使用索引而降低速度），但NOT EXISTS要比NOT IN查询效率高不充份的连接条件
9、例：表 card 有 7896 行，在 card_no 上有一个非聚集索引，表 account 有 191122 行，在account_no上有一个非聚集索引，在不同的表连接条件下， 两个SQL的执 行情况：select sum from account a,card bwhere = （20 秒）将SQL改为：select sum from account a,card bwhere =and =（ 1 秒）在第一个连接条件下，最佳查询方案是将 account 作外层表， card 作内层表，利用 card 上的索引，其 I/O 次数可由以下公式估算为：外层表 account 上的 2254
10、1 页+（外层表 account 的 191122 行* 内层表 card 上对应外层表第一行所要查找的 3页） =595907次 I/O在第二个连接条件下，最佳查询方案是将 card 作外层表， account 作内层表，利 用 account 上的索引，其 I/O 次数可由以下公式估算为：外层表 card 上的 1944 页+（外层表 card 的 7896 行*内层表 account 上对应外层 表每一行所要查找的 4页） = 33528 次 I/O 只有充份的连接条件，真正的最佳方案才会被执行。多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方 案并从中找出系统开销
11、最小的最佳方案。 连接条件要充份考虑带有索引的表、行数多的表;内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。不可优化的where子句下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢select * from record wheresubstri ng（card_no,1,4）=5378（13秒）select * from record whereamou nt/30 1000 （ 11 秒）select * from record where分析：where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得 到，那么就可以被 SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：select * from record where card_ no like5378% （ 1 秒）select * from record where amount 1000*30 （ 1 秒）select * from record where date= 1999/12/01（(25 秒)(55 秒)select coun t(*) fr
