数据库-SQL优化

SQL数据库优化

数据库

-cmquan

iSoftStone Technologies Ltd.2013-7-13

SQL数据库优化

SQL优化

1. 使用WHERE子句过滤行 2. 使用表连接而不是多个查询 3. 执行连接时使用完全限定的列引用 4. 使用CASE表达式而不是多个查询 5. 索引 6. 使用WHERE而不是HAVING 7. 使用UNION ALL而不是UNION 8. 使用EXISTS而不是IN 9. 使用EXISTS而不是DISTINCT

2

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

1. 使用WHERE子句过滤行 使用where条件语句。

Where子句中应避免使用函数，否则会增加执行时间。

3

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

2. 使用表连接而不是多个查询 例： 现有products,product_type表。查询出商品为“苹果”的基本 信息及类型。--BAD select name, product_type_id from products where name = '苹果‘ select name from product_type where id = 1

--GOOD select , p.product_type_id, from products p, product_type pt where p.product_type_id = pt.id and = '苹果'

4

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

执行一个查询通常比执行两个查询效率高。

查询中必须选择连接顺序，将数据量比较少的放在后边。 比如：要连接3个相关表table1,table2,table3。假设table1有1000 行，table2有100行，table3有10行。首先应将table2连接到table1上 ，接着是table2和table3。

5

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

3. 执行连接时使用完全限定的列引用 例：products,product_type。多表链接查询所有数据。

--BAD select , , description, price from products p, product_type pt where p.product_type_id = pt.id

--GOOD select , , p.description, p.price from products p, product_type pt where p.product_type_id = pt.id

6

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

4. 使用CASE表达式而不是多个查询 例： 查询products表中的价格小于20、20到500之间、大于500的数量

。--BAD select count(id) countNum from products where price < 20 --GOOD select count(id) countNum from products where price between 20 and 500 select count(id) countNum from products where price > 500

select count(case when price < 20 then 1 else null end) low, count(case when price between 20 and 500 then 1 else null end) med, count(case when price > 500 then 1 else null end) high from products7

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

5. 索引(index) 理解为一种特殊的目录。

1、聚集索引 2、非聚集索引

8

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

聚集索引： 确定表中数据的物理顺序。

例： 汉语字典的正文本身就是一个聚集索引。 汉语字典的排序是从“a”到“z”。如果查询“安”,就会在 “a”部分里找，如果找不到就属于没有这个字。

9

©2005 iSoftStone Technologies Ltd. A

ll rights reserved.

SQL数据库优化

SQL优化

聚集索引：

适用于聚集索引的情况包括： 1、含有大量非重复值的列。 2、使用BETWEEN,>,>=,<或<=返回一个范围值的列 3、被连续访问的列 4、返回大型结果集的查询 5、经常被使用连接或GROUP BY子句的查询访问的列

10

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

非聚集索引： 该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

例： 汉语字典。 当你遇到一个不认识的字的时候，就不能用聚集索引来查询，这 时候需要用到“偏旁部首”查到您要找的字，然后根据这个字后的 页码直接翻到某页来找到你要找的字。 这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集 索引”。

11

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

一个表或视图中最多有250个非聚集排序或有249个非聚集排序一个 聚集排序。

12

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

6. 使用WHERE而不是HAVINGwhere:过滤行 having:过滤行组。(先分组后过滤)

例：查询商品类型为1和2的平均价格(products)。--BAD select product_type_id, avg(price) price_avg from products group by product_type_id having product_type_id in (1, 2) --GOOD select product_type_id, avg(price) price_avg from products where product_type_id in (1, 2) group by product_type_id13

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

7. 使用UNION ALL而不是UNIONunion all:获得2个查询检索到的所有行，包括重复行。 union:获得2个查询检索到的所有不重复行。

例：--BAD select * from products union select * from pros

--GOOD select * from products union all select * from pros

14

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

8. 使用EXISTS而不是INin:检查一个值是否包含在列表中。 exists:检查是否存在。

例：--BAD select * from pros where id in ( select product_id from purchases_test ) --GOOD select o.* from pros o where exists ( select 1 from purchases_test i where i.product_id = o.id )

15

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

SQL优化

9. 使用EXISTS而不是DISTINCTdistinct:禁止重复行的显示。 exists:检查是否存在。

例：--BAD select distinct pr.id, from pros pr, purchases_test pu where pr.id = pu.product_id --GOOD select o.id, from pros o where exists( select 1 from purchases_test i where i.product_id = o.id )

16

©2005 iSoftStone Technologies Ltd. All rights reserved.

SQL数据库优化

谢谢!

iSoftStone Technologies Ltd.2013-7-13