数据库索引漫谈（整理）2

三、不可 优 化的 where 子句

1. 例：下列 SQL 条件 语 句中的列都建有恰当的索引，但 执 行速度却非常慢：

select * from record wheresubstring(card_no,1,4)='5378'(13秒)

select * from record whereamount/30< 1000（11秒）

select * from record whereconvert(char(10),date,112)='19991201'（10秒）

分析：

where 子句中 对 列的任何操作 结 果都是在 SQL 运行 时 逐列 计 算得到的，因此它不得不 进 行表搜索，而没有使用 该 列上面的索引；

如果 这 些 结 果在 查询编译时 就能得到，那 么 就可以被 SQL 优 化器 优 化，使用索引，避免表搜索，因此将 SQL 重写成下面 这样 ：

select * from record where card_no like'5378%'（< 1秒）

select * from record where amount< 1000*30（< 1秒）

select * from record where date= '1999/12/01'（< 1秒）

你会 发现 SQL 明 显 快起来！

2. 例：表 stuff 有 200000 行， id_no 上有非群集索引， 请 看下面 这 个 SQL ：

select count(*) from stuff where id_no in('0','1') （ 23 秒）

分析： ---- where 条件中的 'in' 在 逻辑 上相当于 'or' ，所以 语 法分析器会将 in ('0','1') 转 化 为 id_no ='0' or id_no='1' 来 执 行。

我 们 期望它会根据 每 个 or 子句分 别查 找，再将 结 果相加， 这样 可以利用 id_no 上的索引；

但 实际 上（根据 showplan ） , 它却采用了 "OR 策略 " ，即先取出 满 足 每 个 or 子句的行，存入 临时 数据 库 的工作表中，再建立唯一索引以去掉重 复 行，最后从 这 个 临时 表中 计 算 结 果。因此， 实际过 程没有利用 id_no 上索引，并且完成 时间还 要受 tempdb 数据 库 性能的影响。

实 践 证 明，表的行数越多，工作表的性能就越差，当 stuff 有 620000 行 时 ， 执 行 时间 竟达到 220 秒！ 还 不如将 or 子句分 开 ：

select count(*) from stuff where id_no='0'select count(*) from stuff where id_no='1'

得到两个 结 果，再作一次加法合算。因 为每 句都使用了索引， 执 行 时间 只有 3 秒，在 620000 行下， 时间 也只有 4 秒。

或者，用更好的方法，写一个 简单 的存 储过 程：

create proc count_stuff asdeclare @a intdeclare @b intdeclare @c intdeclare @d char(10)beginselect @a=count(*) from stuff where id_no='0'select @b=count(*) from stuff where id_no='1'endselect @c=@a+@bselect @d=convert(char(10),@c)print @d

直接算出 结 果， 执 行 时间 同上面一 样 快！

---- 总结 ： ---- 可 见 ，所 谓优 化即 where 子句利用了索引，不可 优 化即 发 生了表 扫 描或 额 外 开销 。

1. 任何 对 列的操作都将 导 致表 扫 描，它包括数据 库 函数、 计 算表达式等等， 查询时 要尽可能将操作移至等号右 边 。

2.in 、 or 子句常会使用工作表，使索引失效；如果不 产 生大量重 复值 ，可以考 虑 把子句拆 开 ；拆 开 的子句中 应该 包含索引。

3. 要善于使用存 储过 程，它使 SQL 变 得更加灵活和高效。

从以上 这 些例子可以看出， SQL 优 化的 实质 就是在 结 果正确的前提下，用 优 化器可以 识别 的 语 句，充份利用索引，减少表 扫 描的 I/O 次数，尽量避免表搜索的 发 生。其 实 SQL 的性能 优 化是一个 复杂 的 过 程，上述 这 些只是在 应 用 层 次的一 种 体 现 ，深入研究 还 会 涉 及数据 库层 的 资 源配置、网 络层 的流量控制以及操作系 统层 的 总 体 设计 。