2020-11-18

太关注于所得的 结 果是否正确，而忽略了不同的实现方法之间可能存在的性能差异， 这种性能差异在大型的或是复杂的数据库环境中（如联机事 务处 理 OLTP 或决策支持系 统 DSS ）中表现得尤为 明 显 。

笔者在工作实践中 发现 ，不良的 SQL 往往来自于：

• 不恰当的索引设计
• 不充份的连接条件
• 不可优化的where 子句

测试环境主机： HP LH II---- 主 频 ： 330MHZ---- 内存： 128 兆 ----

操作系 统 ： Operserver5.0.4----

数据 库： Sybase11.0.3

 

一、不合理的索引 设计 ----

例：表 record 有 620000 行，在不同的索引下，下面几个 SQL 的运行情况：

1. 在 date 上建有一非个群集索引

select count(*) from record where date >'19991201' and date < '19991214'and amount >2000 (25秒)

select date ,sum(amount) from record group by date(55秒)

select count(*) from record where date >'19990901' and place in ('BJ','SH') (27秒)

---- 分析： ----

date 上有大量的重 复值 ，在非群集索引下，数据在物理上随机存放在数据 页 上，在范 围查 找 时 ，必 须执 行一次表 扫 描才能找到 这 一范 围 内的全部行。

2. 在 date 上的一个群集索引

select count(*) from record where date >'19991201' and date < '19991214' and amount >2000 （14秒）

select date,sum(amount) from record group by date（28秒）

select count(*) from record where date >'19990901' and place in ('BJ','SH')（14秒）

---- 分析： ---- 在群集索引下，数据在物理上按 顺 序在数据 页 上，重 复值 也排列在一起，因而在范 围查 找 时 ，可以先找到 这 个范 围 的起末点，且只在 这 个范 围 内 扫 描数据 页 ，避免了大范 围扫 描，提高了 查询 速度。

3. 在 place，date，amount 上的组合索引

select count(*) from record where date >'19991201' and date < '19991214' and amount >2000 （26秒）

select date,sum(amount) from record group by date（27秒）

select count(*) from record where date >'19990901' and place in ('BJ, 'SH')（< 1秒）

---- 分析：这 是一个不很合理的 组 合索引，因 为 它的前 导 列是 place ，第一和第二条 SQL 没有引用 place ，因此也没有利用上索引；第三个 SQL 使用了 place ，且引用的所有列都包含在 组 合索引中，形成了索引覆盖，所以它的速度是非常快的。

4. 在 date，place，amount 上的组合索引

select count(*) from record where date >'19991201' and date < '19991214' and amount >2000(< 1秒)

select date,sum(amount) from record group by date（11秒）

select count(*) from record where date >'19990901' and place in ('BJ','SH')（< 1秒）

---- 分析： ---- 这 是一个合理的 组 合索引。它将 date 作 为 前 导 列，使 每 个 SQL 都可以利用索引，并且在第一和第三个 SQL 中形成了索引覆盖，因而性能达到了最 优 。

总结 

缺省情况下建立的索引是非群集索引，但有 时 它并不是最佳的；合理的索引 设计 要建立在 对 各 种查询 的分析和 预测 上。

一般来 说 ：

• ① . 有大量重 复值 、且经常有范 围查询（ between, >,< ， >=,< = ）和 order by 、 group by 发 生的列，可考 虑 建立群集索引；
• ② . 经 常同 时 存取多列，且每 列都含有重 复值 可考 虑 建立 组 合索引；
• ③ . 组 合索引要尽量使 关键查询 形成索引覆盖，其前 导 列一定是使用最 频 繁的列。

二、不充份的连接条件：

例：表 card 有 7896 行，在 card_no 上有一个非聚集索引，表 account 有 191122 行，在 account_no 上有一个非聚集索引， 试看在不同的表 连 接条件下，两个 SQL 的 执 行情况：

select sum(a.amount) from account a,card b where a.card_no = b.card_no（20秒）

select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.account_no（< 1秒）

---- 分析： ---- 在第一个 连 接条件下，最佳 查询 方案是将 account 作外 层 表， card 作内 层 表，利用 card 上的索引，其 I/O 次数可由以下公式估算 为 ：

外 层 表 account 上的 22541 页 + （外 层 表 account 的 191122 行 * 内 层 表 card 上 对应 外 层 表第一行所要 查 找的 3 页 ） =595907 次 I/O

在第二个 连 接条件下，最佳 查询 方案是将 card 作外 层 表， account 作内 层 表，利用 account 上的索引，其 I/O 次数可由以下公式估算 为 ：外 层 表 card 上的 1944 页 + （外 层 表 card 的 7896 行 * 内 层 表 account 上 对应 外 层 表 每 一行所要 查 找的 4 页 ） = 33528 次 I/O

可 见 ，只有充份的 连 接条件，真正的最佳方案才会被 执 行。

总结

1. 多表操作在被 实际执 行前， 查询优 化器会根据 连 接条件，列出几 组 可能的 连 接方案并从中找出系 统开销 最小的最佳方案。 连 接条件要充份考 虑带 有索引的表、行数多的表；内外表的 选择 可由公式：外 层 表中的匹配行数 * 内 层 表中 每 一次 查 找的次数确定，乘 积 最小 为 最佳方案。

2. 查 看 执 行方案的方法 -- 用 set showplan on ，打 开 showplan 选项 ，就可以看到 连 接 顺 序、使用何 种 索引的信息；想看更 详细 的信息，需用 sa 角色 执 行 dbcc(3604,310,302) 。

 

三、不可 优 化的 where 子句

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

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

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

select * from record where convert(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 的性能 优 化是一个 复杂 的 过 程，上述 这 些只是在 应 用 层 次的一 种 体 现 ，深入研究 还 会 涉 及数据 库层 的 资 源配置、网 络层 的流量控制以及操作系 统层 的 总 体 设计 。

 

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/gprime/article/details/1687930