登录 注册

数据库SQL优化

发布时间:Jun 12, 2019 10:30:17 AM 发布人:管理员


归属栏目: 数据库开发


外部链接: 


关键字: 


归属栏目: 数据库开发


文章来源: 


摘要: 

数据库SQL优化

开发数据库交互的应用时,好的数据库脚本优化对提升应用的访问效率、查询效率、系统运算效率有极大的好处,本文提出一些SQL脚本的优化方式,便于技术人员在开发项目时参考使用。

1:使用where子句过滤行

   使用where条件语句。

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

2:使用表连接而不是多个查询

   例:

      现有productsproduct_type表。查询出商品为苹果的基本信息及类型。

      ------BAD

      Select name,product _type_id

      From products

      Where name = '苹果'

      Select name 

      From product_type

      Where id = 1

      ------GOOD

      Select p.name,p.product_type_id,pt.name 

      From products p,product_type pt

      Where p.product_type_id = pt.id and p.name = '苹果'

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

      查询中必须选择连接顺序,将数量比较少的放在后面。

      比如:要连接3个相关表table1,table2,table3。假设table11000行,table2100

            行,table310行。首先应该将table2连接到table1上,接着是table2table3

3:执行连接时使用完全限定的列引用

   例:

      products ,product_type。多表链接查询所有数据。

      ------BAD

      Select p.name,pt.name,description,price

      From products p, product_type pt

      Where p.product_type_id = pt.id

      ------GOOD

      Select p.name,pt.name,p.description,p.price

      From products p,product_type pt

      Where p.product_type_id = pt.id

4:使用CASE表达式而不是多个查询

   例:

      查询products表中的价格小于2020500之间、大于500的数量。

      ------BAD

      Select count(id) countNum 

      From products 

      Where price < 20

      Select count(id) countNum 

      From products

      Where price between 20 and 500

      Select count(id) countNum

      From products

      Where price > 500

      ------GOOD

      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 products

5:索引(index)

   理解为一种特殊的目录。

   1、聚集索引

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

      例:

         汉语字典的正文本身就是一个聚集索引。

         汉语字典的排序是从“a”  “z”。如果查询,就会在“a”部分里找,如

         果找不到就没有这个字。

      适用于聚集索引的情况包括:

      1:含有大量非重复值的列。

      2:使用between , > , >= , < ,  <= 返回一个范围值的列。

      3:被连续访问的列。

      4:返回大型结果集的查询。

      5:经常被使用连接或group by 子句的查询访问的列。

   2、非聚集索引

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

      例:

         汉语字典。

         当你遇到一个不认识的字的时候,就不能用聚集索引来查询,这个时候需要用到

         “偏旁部首查到你要找的字,然后根据这个字后的页码直接翻到某页来找到你

         要找的字。

         这种目录纯粹是目录,正文纯粹是正文的排序方式称为非聚集索引

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

6:使用where而不是having

   Where:过滤行

   Having:过滤行组(先分组后过滤) 

   例:

      查询商品类型为 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_id

7:使用union all 而不是union

   Union all:获得2个查询检索到的所有行,包括重复行。

   Union:获得2个查询检索到的所有不重复行。

   例:

      ------BAD

      Select * from products

      Union

      Select * from pors

      ------GOOD

      Select * from products

      Union all 

      Select * from pros

8:使用exists而不是in

   In:检查一个值是否包含在列表中。

   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

      )

9:使用exists而不是distinct

   Distinct:禁止重复行的显示。

   Exists:检查是否存在。

   例:

      ------BAD

      Select distinct pr.id , pr.name

      From pros pr , purchases_test pu

      Where pr.id = pu.product_id

      ------GOOD

      Select o.id , o.name

      From pros o

      Where exists (

         Select 1 from purchases_test i

         Where i.product_id = o.id

      )

希望和大家共同讨论 祝工作愉快!

 

 


相关文章

返回