1
、 用程序中,保证在实现功能的基础上,尽量减少对数据库的访问次数;通过搜索参数,尽量减少对表的访问行数,最小化结果集,从而减轻网络负担;能够分开的操作尽量分开处理,提高每次的响应速度;在数据窗口使用SQL时,尽量把使用的索引放在选择的首列;算法的结构尽量简单;在查询时,不要过多地使用通配符如SELECT
*
FROM
T1语句,要用到几列就选择几列如:
SELECT
COL1,COL2
FROM
T1;在可能的情况下尽量限制尽量结果集行数如:
SELECT
TOP
300
COL1,COL2,COL3
FROM
T1,因为某些情况下用户是不需要那么多的数据的。不要在应用中使用数据库游标,游标是非常有用的工具,但比使用常规的、面向集的SQL语句需要更大的开销;按照特定顺序提取数据的查找。
2
、 避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:
SELECT
name
FROM
employee
WHERE
salary >
60000
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。
3
、 尽量避免在WHERE子句中对字段进行函数或表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
SELECT
*
FROM
T1
WHERE
F1
/
2
=
100
应改为:
SELECT
*
FROM
T1
WHERE
F1
=
100
*
2
SELECT
*
FROM
RECORD
WHERE
SUBSTRING
(CARD_NO,
1
,
4
)
=
’
5378
’
应改为:
SELECT
*
FROM
RECORD
WHERE
CARD_NO
LIKE
‘
5378
%
’
SELECT
member_number, first_name, last_name
FROM
members
WHERE
DATEDIFF
(yy,datofbirth,
GETDATE
())
>
21
应改为:
SELECT
member_number, first_name, last_name
FROM
members
WHERE
dateofbirth
<
DATEADD
(yy,
-
21
,
GETDATE
())
即:任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。
4
、 避免使用
!=
或<>、
IS
NULL或IS
NOT
NULL
、
IN
,
NOT
IN等这样的操作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如:
SELECT
id
FROM
employee
WHERE
id
!=
'
B%
'
优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
5
、 尽量使用数字型字段,一部分开发人员和数据库管理人员喜欢把包含数值信息的字段
设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
6
、 合理使用EXISTS,
NOT
EXISTS子句。如下所示:
1
.
SELECT
SUM
(T1.C1)
FROM
T1
WHERE
(
(
SELECT
COUNT
(
*
)
FROM
T2
WHERE
T2.C2
=
T1.C2
>
0
)
2
.
SELECT
SUM
(T1.C1)
FROM
T1WHERE
EXISTS
(
SELECT
*
FROM
T2
WHERE
T2.C2
=
T1.C2)
两者产生相同的结果,但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。
如果你想校验表里是否存在某条纪录,不要用count(
*
)那样效率很低,而且浪费服务器资源。可以用EXISTS代替。如:
IF
(
SELECT
COUNT
(
*
)
FROM
table_name
WHERE
column_name
=
'
xxx
'
)
可以写成:
IF
EXISTS
(
SELECT
*
FROM
table_name
WHERE
column_name
=
'
xxx
'
)
经常需要写一个T_SQL语句比较一个父结果集和子结果集,从而找到是否存在在父结果集中有而在子结果集中没有的记录,如:
1
.
SELECT
a.hdr_key
FROM
hdr_tbl a
--
-- tbl a 表示tbl用别名a代替
WHERE
NOT
EXISTS
(
SELECT
*
FROM
dtl_tbl b
WHERE
a.hdr_key
=
b.hdr_key)
2
.
SELECT
a.hdr_key
FROM
hdr_tbl a
LEFT
JOIN
dtl_tbl b
ON
a.hdr_key
=
b.hdr_key
WHERE
b.hdr_key
IS
NULL
3
.
SELECT
hdr_key
FROM
hdr_tbl
WHERE
hdr_key
NOT
IN
(
SELECT
hdr_key
FROM
dtl_tbl)
三种写法都可以得到同样正确的结果,但是效率依次降低。
7
、 尽量避免在索引过的字符数据中,使用非打头字母搜索。这也使得引擎无法利用索引。
见如下例子:
SELECT
*
FROM
T1
WHERE
NAME
LIKE
‘
%
L
%
’
SELECT
*
FROM
T1
WHERE
SUBSTING(NAME,
2
,
1
)
=
’L’
SELECT
*
FROM
T1
WHERE
NAME
LIKE
‘L
%
’
即使NAME字段建有索引,前两个查询依然无法利用索引完成加快操作,引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。
8
、 分利用连接条件,在某种情况下,两个表之间可能不只一个的连接条件,这时在
WHERE
子句中将连接条件完整的写上,有可能大大提高查询速度。
例:
SELECT
SUM
(A.AMOUNT)
FROM
ACCOUNT A,CARD B
WHERE
A.CARD_NO
=
B.CARD_NO
SELECT
SUM
(A.AMOUNT)
FROM
ACCOUNT A,CARD B
WHERE
A.CARD_NO
=
B.CARD_NO
AND
A.ACCOUNT_NO
=
B.ACCOUNT_NO
第二句将比第一句执行快得多。
9
、 消除对大型表行数据的顺序存取
尽管在所有的检查列上都有索引,但某些形式的WHERE子句强迫优化器使用顺序存取。如:
SELECT
*
FROM
orders
WHERE
(customer_num
=
104
AND
order_num
>
1001
)
OR
order_num
=
1008
解决办法可以使用并集来避免顺序存取:
SELECT
*
FROM
orders
WHERE
customer_num
=
104
AND
order_num
>
1001
UNION
SELECT
*
FROM
orders
WHERE
order_num
=
1008
这样就能利用索引路径处理查询。
10
、 避免困难的正规表达式
LIKE关键字支持通配符匹配,技术上叫正规表达式。但这种匹配特别耗费时间。例如:
SELECT
*
FROM
customer
WHERE
zipcode
LIKE
“98_ _ _”
即使在zipcode字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。如
果把语句改为SELECT *
FROM
customer
WHERE
zipcode
>
“
98000
”,在执行查询
时就会利用索引来查询,显然会大大提高速度。
11
、 使用视图加速查询
把表的一个子集进行排序并创建视图,有时能加速查询。它有助于避免多重排序
操作,而且在其他方面还能简化优化器的工作。例如:
SELECT
cust.name,rcvbles.balance,……other columns
FROM
cust,rcvbles
WHERE
cust.customer_id
=
rcvlbes.customer_id
AND
rcvblls.balance
>
0
AND
cust.postcode
>
“
98000
”
ORDER
BY
cust.name
如果这个查询要被执行多次而不止一次,可以把所有未付款的客户找出来放在一个
视图中,并按客户的名字进行排序:
CREATE
VIEW
DBO.V_CUST_RCVLBES
AS
SELECT
cust.name,rcvbles.balance,……other columns
FROM
cust,rcvbles
WHERE
cust.customer_id
=
rcvlbes.customer_id
AND
rcvblls.balance
>
0
ORDER
BY
cust.name
然后以下面的方式在视图中查询:
SELECT
*
FROM
V_CUST_RCVLBES
WHERE
postcode
>
“
98000
”
视图中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘
I
/
O,所以查询工作量可以得到大幅减少。
12
、 能够用BETWEEN的就不要用IN
SELECT
*
FROM
T1
WHERE
ID
IN
(
10
,
11
,
12
,
13
,
14
)
改成:
SELECT
*
FROM
T1
WHERE
ID
BETWEEN
10
AND
14
因为IN会使系统无法使用索引,而只能直接搜索表中的数据。
13
、 DISTINCT的就不用GROUP
BY
SELECT
OrderID
FROM
Details
WHERE
UnitPrice
>
10
GROUP
BY
OrderID
可改为:
SELECT
DISTINCT
OrderID
FROM
Details
WHERE
UnitPrice
>
10
14
、 部分利用索引
1
.
SELECT
employeeID, firstname, lastname
FROM
names
WHERE
dept
=
'
prod
'
or
city
=
'
Orlando
'
or
division
=
'
food
'
2
.
SELECT
employeeID, firstname, lastname
FROM
names
WHERE
dept
=
'
prod
'
UNION
ALL
SELECT
employeeID, firstname, lastname
FROM
names
WHERE
city
=
'
Orlando
'
UNION
ALL
SELECT
employeeID, firstname, lastname
FROM
names
WHERE
division
=
'
food
'
如果dept 列建有索引则查询2可以部分利用索引,查询1则不能。
15
、 能用UNION ALL就不要用UNION
UNION
ALL不执行SELECT DISTINCT函数,这样就会减少很多不必要的资源
16
、 不要写一些不做任何事的查询
如:
SELECT
COL1
FROM
T1
WHERE
1
=
0
SELECT
COL1
FROM
T1
WHERE
COL1
=
1
AND
COL1
=
2
这类死码不会返回任何结果集,但是会消耗系统资源。
17
、 尽量不要用SELECT INTO语句。
SELECT
INOT 语句会导致表锁定,阻止其他用户访问该表。
18
、 必要时强制查询优化器使用某个索引
SELECT
*
FROM
T1
WHERE
nextprocess
=
1
AND
processid
IN
(
8
,
32
,
45
)
改成:
SELECT
*
FROM
T1 (
INDEX
=
IX_ProcessID)
WHERE
nextprocess
=
1
AND
processid
IN
(
8
,
32
,
45
)
则查询优化器将会强行利用索引IX_ProcessID 执行查询。
19
、 虽然UPDATE、DELETE语句的写法基本固定,但是还是对UPDATE语句给点建议:
a) 尽量不要修改主键字段。
b) 当修改VARCHAR型字段时,尽量使用相同长度内容的值代替。
c) 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。
d) 避免UPDATE将要复制到其他数据库的列。
e) 避免UPDATE建有很多索引的列。
f) 避免UPDATE在WHERE子句条件中的列。
查询优化的一些技巧
最新推荐文章于 2021-07-19 17:02:20 发布