精华内容
下载资源
问答
  • SQL索引的创建和使用
    千次阅读
    2021-05-05 13:47:10

    建立索引的原则:

    1) 定义主键的数据列一定要建立索引。
    
    2) 定义有外键的数据列一定要建立索引。
    
    3) 对于经常查询的数据列最好建立索引。
    
    4) 对于需要在指定范围内的快速或频繁查询的数据列;
    
    5) 经常用在WHERE子句中的数据列。
    
    6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。
    
        如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。
    
    7) 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
    
    8) 对于定义为text、image和bit的数据类型的列不要建立索引。
    
    9) 对于经常存取的列避免建立索引
    
    10) 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。
    
         索引虽说提高了访问速度,但太多索引会影响数据的更新操作。
    
    11) 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。
    
          对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。
    

    索引的不足之处:

    虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
     
    建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。
     
    索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
    

    使用CREATE 语句创建索引:

    CREATE INDEX index_name ON table_name(column_name,column_name) include(score)
    

    普通索引

    CREATE UNIQUE INDEX index_name ON table_name (column_name) ;
    

    非空索引

    CREATE PRIMARY KEY INDEX index_name ON table_name (column_name) ;
    

    主键索引

    使用ALTER TABLE语句创建索引

    alter table table_name add index index_name (column_list) ;
    alter table table_name add unique (column_list) ;
    alter table table_name add primary key (column_list) ;
    

    删除索引

    drop index index_name on table_name ;
    alter table table_name drop index index_name ;
    alter table table_name drop primary key ;
    

    创建索引

    alter table tbl_name add primary key (column_list):
    

    该语句添加一个主键,这意味着索引值必须是唯一的,且不能为 null。

    alter table tbl_name add unique index_name (column_list):
    

    这条语句创建索引的值必须是唯一的(除了 null 外,null 可能会出现多次)。

    alter table tbl_name add index index_name (column_list):
    

    添加普通索引,索引值可出现多次。

    alter table tbl_name add fulltext index_name (column_list):
    

    该语句指定了索引为 fulltext ,用于全文索引。

    删除索引

    drop index [indexname] on mytable;
    

    修改

    alter mytable add index [indexname] on(username(length))
    

    查询
    使用 show index 命令来列出表中的相关的索引信息。可以通过添加 \g 来格式化输出信息。
    show index from table_name \g

    更多相关内容
  • SQL索引详解

    万次阅读 多人点赞 2018-03-08 00:25:13
    SQL索引详解转自:http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.htmlSQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开...

    SQL索引详解

    转自:http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.html

    SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。

      1.1 什么是索引?

      SQL索引有两种,聚集索引和非聚集索引,索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间 

    下面举两个简单的例子:

    图书馆的例子:一个图书馆那么多书,怎么管理呢?建立一个字母开头的目录,例如:a开头的书,在第一排,b开头的在第二排,这样在找什么书就好说了,这个就是一个聚集索引,可是很多人借书找某某作者的,不知道书名怎么办?图书管理员在写一个目录,某某作者的书分别在第几排,第几排,这就是一个非聚集索引

    字典的例子:字典前面的目录,可以按照拼音和部首去查询,我们想查询一个字,只需要根据拼音或者部首去查询,就可以快速的定位到这个汉字了,这个就是索引的好处,拼音查询法就是聚集索引,部首查询就是一个非聚集索引.

        看了上面的例子,下面的一句话大家就很容易理解了:聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。就像字段,聚集索引是连续的,a后面肯定是b,非聚集索引就不连续了,就像图书馆的某个作者的书,有可能在第1个货架上和第10个货架上。还有一个小知识点就是:聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

     

       1.2 索引的存储机制

        首先,无索引的表,查询时,是按照顺序存续的方法扫描每个记录来查找符合条件的记录,这样效率十分低下,举个例子,如果我们将字典的汉字随即打乱,没有前面的按照拼音或者部首查询,那么我们想找一个字,按照顺序的方式去一页页的找,这样效率有多底,大家可以想象。

           聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致,其实理解起来非常简单,还是举字典的例子:如果按照拼音查询,那么都是从a-z的,是具有连续性的,a后面就是b,b后面就是c, 聚集索引就是这样的,他是和表的物理排列顺序是一样的,例如有id为聚集索引,那么1后面肯定是2,2后面肯定是3,所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是,可能按照偏房查询的时候,根据偏旁‘弓’字旁,索引出两个汉字,张和弘,但是这两个其实一个在100页,一个在1000页,(这里只是举个例子),他们的索引顺序和数据库表的排列顺序是不一样的,这个样的就是非聚集索引。

          原理明白了,那他们是怎么存储的呢?在这里简单的说一下,聚集索引就是在数据库被开辟一个物理空间存放他的排列的值,例如1-100,所以当插入数据时,他会重新排列整个整个物理空间,而非聚集索引其实可以看作是一个含有聚集索引的表,他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针,其实就有点和堆栈差不多的感觉了

     

      1.3 什么情况下设置索引 

    动作描述

    使用聚集索引 

     使用非聚集索引

     外键列

     应

     应

     主键列

     应

     应

     列经常被分组排序(order by)

     应

     应

     返回某范围内的数据

     应

     不应

     小数目的不同值

     应

     不应

     大数目的不同值

     不应

     应

     频繁更新的列

    不应 

     应

     频繁修改索引列

     不应

     应

     一个或极少不同值

     不应

     不应

     

    建立索引的原则:

    1) 定义主键的数据列一定要建立索引。

    2) 定义有外键的数据列一定要建立索引。

    3) 对于经常查询的数据列最好建立索引。

    4) 对于需要在指定范围内的快速或频繁查询的数据列;

    5) 经常用在WHERE子句中的数据列。

    6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

    7) 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

    8) 对于定义为text、image和bit的数据类型的列不要建立索引。

    9) 对于经常存取的列避免建立索引 

    9) 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

    10) 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

     

      1.4 如何创建索引

      1.41 创建索引的语法:

    CREATE [UNIQUE][CLUSTERED | NONCLUSTERED]  INDEX  index_name  

    ON {table_name | view_name} [WITH [index_property [,....n]]

    说明:

    UNIQUE: 建立唯一索引。

    CLUSTERED: 建立聚集索引。

    NONCLUSTERED: 建立非聚集索引。

    Index_property: 索引属性。

     UNIQUE索引既可以采用聚集索引结构,也可以采用非聚集索引的结构,如果不指明采用的索引结构,则SQL Server系统默认为采用非聚集索引结构。

    1.42 删除索引语法:

    DROP INDEX table_name.index_name[,table_name.index_name]

    说明:table_name: 索引所在的表名称。

    index_name : 要删除的索引名称。

    1.43 显示索引信息:

    使用系统存储过程:sp_helpindex 查看指定表的索引信息。

    执行代码如下:

    Exec sp_helpindex book1;

     

      1.5 索引使用次数、索引效率、占用CPU检测、索引缺失

      当我们明白了什么是索引,什么时间创建索引以后,我们就会想,我们创建的索引到底效率执行的怎么样?好不好?我们创建的对不对?

      首先我们来认识一下DMV,DMV (dynamic management view)动态管理视图和函数返回特定于实现的内部状态数据。推出SQL Server 2005时,微软介绍了许多被称为dmvs的系统视图,让您可以探测SQL Server 的健康状况,诊断问题,或查看SQL Server实例的运行信息。统计数据是在SQL Server运行的时候开始收集的,并且在SQL Server每次启动的时候,统计数据将会被重置。当你删除或者重新创建其组件时,某些dmv的统计数据也可以被重置,例如存储过程和表,而其它的dmv信息在运行dbcc命令时也可以被重置。

      当你使用一个dmv时,你需要紧记SQL Server收集这些信息有多长时间了,以确定这些从dmv返回的数据到底有多少可用性。如果SQL Server只运行了很短的一段时间,你可能不想去使用一些dmv统计数据,因为他们并不是一个能够代表SQL Server实例可能遇到的真实工作负载的样本。另一方面,SQL Server只能维持一定量的信息,有些信息在进行SQL Server性能管理活动的时候可能丢失,所以如果SQL Server已经运行了相当长的一段时间,一些统计数据就有可能已被覆盖。

      因此,任何时候你使用dmv,当你查看从SQL Server 2005的dmvs返回的相关资料时,请务必将以上的观点装在脑海中。只有当你确信从dmvs获得的信息是准确和完整的,你才能变更数据库或者应用程序代码。

    下面就看一下dmv到底能带给我们那些好的功能呢?

    1.51 :索引使用次数

    我们下看一下下面两种查询方式返回的结果(这两种查询的查询用途一致)

    ①----

    declare @dbid int

    select @dbid = db_id()

    select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id

                , user_seeks, user_scans, user_lookups, user_updates

    from sys.dm_db_index_usage_stats s,

                sys.indexes i

    where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1

    and i.object_id = s.object_id

    and i.index_id = s.index_id

    order by (user_seeks + user_scans + user_lookups + user_updates) asc

    返回查询结果

     

     

    ②:使用多的索引排在前面

    SELECT  objects.name ,

            databases.name ,

            indexes.name ,

            user_seeks ,

            user_scans ,

            user_lookups ,

            partition_stats.row_count

    FROM    sys.dm_db_index_usage_stats stats

            LEFT JOIN sys.objects objects ON stats.object_id = objects.object_id

            LEFT JOIN sys.databases databases ON databases.database_id = stats.database_id

            LEFT JOIN sys.indexes indexes ON indexes.index_id = stats.index_id

                                             AND stats.object_id = indexes.object_id

            LEFT  JOIN sys.dm_db_partition_stats partition_stats ON stats.object_id = partition_stats.object_id

                                                                  AND indexes.index_id = partition_stats.index_id

    WHERE   1 = 1

    --AND databases.database_id = 7

            AND objects.name IS NOT NULL

            AND indexes.name IS NOT NULL

            AND user_scans>0

    ORDER BY user_scans DESC ,

            stats.object_id ,

            indexes.index_id

    返回查询结果

     

     

    user_seeks : 通过用户查询执行的搜索次数。 
     个人理解: 此统计索引搜索的次数

    user_scans: 通过用户查询执行的扫描次数。 
      个人理解:此统计表扫描的次数,无索引配合
    user_lookups: 通过用户查询执行的查找次数。 
     个人理解:用户通过索引查找,在使用RID或聚集索引查找数据的次数,对于堆表或聚集表数据而言和索引配合使用次数
    user_updates:  通过用户查询执行的更新次数。 
      个人理解:索引或表的更新次数

    我们可以清晰的看到,那些索引用的多,那些索引没用过,大家可以根据查询出来的东西去分析自己的数据索引和表

    1.52 :索引提高了多少性能

    新建了索引到底增加了多少数据的效率呢?到底提高了多少性能呢?运行如下SQL可以返回连接缺失索引动态管理视图,发现最有用的索引和创建索引的方法: 

    SELECT  

    avg_user_impact AS average_improvement_percentage,  

    avg_total_user_cost AS average_cost_of_query_without_missing_index,  

    'CREATE INDEX ix_' + [statement] +  

    ISNULL(equality_columns, '_') + 

    ISNULL(inequality_columns, '_') + ' ON ' + [statement] +  

    ' (' + ISNULL(equality_columns, ' ') +  

    ISNULL(inequality_columns, ' ') + ')' +  

    ISNULL(' INCLUDE (' + included_columns + ')', '')  

    AS create_missing_index_command 

    FROM sys.dm_db_missing_index_details a INNER JOIN  

    sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle 

    INNER JOIN sys.dm_db_missing_index_group_stats c ON  

    b.index_group_handle = c.group_handle 

    WHERE avg_user_impact > = 40

     

    返回结果

     

     

    虽然用户能够修改性能提高的百分比,但以上查询返回所有能够将性能提高40%或更高的索引。你可以清晰的看到每个索引提高的性能和效率了

    1.53 :最占用CPU、执行时间最长命令

    这个和索引无关,但是还是在这里提出来,因为他也属于DMV带给我们的功能吗,他可以让你轻松查询出,那些sql语句占用你的cpu最高

     

    SELECT TOP 100 execution_count,

               total_logical_reads /execution_count AS [Avg Logical Reads],

               total_elapsed_time /execution_count AS [Avg Elapsed Time],

                    db_name(st.dbid) as [database name],

               object_name(st.dbid) as [object name],

               object_name(st.objectid) as [object name 1],

               SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, 

               ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) 

                 / 2) + 1) AS statement_text

      FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

     WHERE execution_count > 100

     ORDER BY 1 DESC;

     

    返回结果:

     

     

    执行时间最长的命令

    SELECT TOP 10 COALESCE(DB_NAME(st.dbid),

    DB_NAME(CAST(pa.value as int))+'*',

    'Resource') AS DBNAME,

    SUBSTRING(text,

    -- starting value for substring

            CASE WHEN statement_start_offset = 0

    OR statement_start_offset IS NULL

    THEN 1

    ELSE statement_start_offset/2 + 1 END,

    -- ending value for substring

            CASE WHEN statement_end_offset = 0

    OR statement_end_offset = -1

    OR statement_end_offset IS NULL

    THEN LEN(text)

    ELSE statement_end_offset/2 END -

    CASE WHEN statement_start_offset = 0

    OR statement_start_offset IS NULL

    THEN 1

    ELSE statement_start_offset/2  END + 1

    )  AS TSQL,

    total_logical_reads/execution_count AS AVG_LOGICAL_READS

    FROM sys.dm_exec_query_stats

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa

    WHERE attribute = 'dbid'

    ORDER BY AVG_LOGICAL_READS DESC ;

     

     

    看到了吗?直接可以定位到你的sql语句,优化去吧。还等什么呢?

    1.54:缺失索引

    缺失索引就是帮你查找你的数据库缺少什么索引,告诉你那些字段需要加上索引,这样你就可以根据提示添加你数据库缺少的索引了

    SELECT TOP 10

    [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)

    , avg_user_impact

    , TableName = statement

    , [EqualityUsage] = equality_columns

    , [InequalityUsage] = inequality_columns

    , [Include Cloumns] = included_columns

    FROM    sys.dm_db_missing_index_groups g

    INNER JOIN sys.dm_db_missing_index_group_stats s

    ON s.group_handle = g.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details d

    ON d.index_handle = g.index_handle

    ORDER BY [Total Cost] DESC;

    查询结果如下:

     

     

     

      1.6  适当创建索引覆盖

      假设你在Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外键列(ProductID)上创建了一个索引,假设ProductID列是一个高选中性列,那么任何在where子句中使用索引列(ProductID)的select查询都会更快,如果在外键上没有创建索引,将会发生全部扫描,但还有办法可以进一步提升查询性能。

      假设Sales表有10,000行记录,下面的SQL语句选中400行(总行数的4%): 

    SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID = 112

      我们来看看这条SQL语句在SQL执行引擎中是如何执行的:

      1)Sales表在ProductID列上有一个非聚集索引,因此它查找非聚集索引树找出ProductID=112的记录;

      2)包含ProductID = 112记录的索引页也包括所有的聚集索引键(所有的主键键值,即SalesID);

      3)针对每一个主键(这里是400),SQL Server引擎查找聚集索引树找出真实的行在对应页面中的位置;

      SQL Server引擎从对应的行查找SalesDate和SalesPersonID列的值。

      在上面的步骤中,对ProductID = 112的每个主键记录(这里是400),SQL Server引擎要搜索400次聚集索引树以检索查询中指定的其它列(SalesDate,SalesPersonID)。

      如果非聚集索引页中包括了聚集索引键和其它两列(SalesDate,,SalesPersonID)的值,SQL Server引擎可能不会执行上面的第3和4步,直接从非聚集索引树查找ProductID列速度还会快一些,直接从索引页读取这三列的数值。

      幸运的是,有一种方法实现了这个功能,它被称为“覆盖索引”,在表列上创建覆盖索引时,需要指定哪些额外的列值需要和聚集索引键值(主键)一起存储在索引页中。下面是在Sales 表ProductID列上创建覆盖索引的例子: 

    CREATE INDEX NCLIX_Sales_ProductID--Index name

      ON dbo.Sales(ProductID)--Column on which index is to be created
      INCLUDE(SalesDate, SalesPersonID)--Additional column values to include

      应该在那些select查询中常使用到的列上创建覆盖索引,但覆盖索引中包括过多的列也不行,因为覆盖索引列的值是存储在内存中的,这样会消耗过多内存,引发性能下降。

      

      1.7 索引碎片

    在数据库性能优化一:数据库自身优化一文中已经讲到了这个问题,再次就不做过多的重复地址:http://www.cnblogs.com/AK2012/archive/2012/12/25/2012-1228.html

     

      1.8 索引实战(摘抄)

    之所以这章摘抄,是因为下面这个文章已经写的太好了,估计我写出来也无法比这个好了,所以就摘抄了

    人们在使用SQL时往往会陷入一个误区,即太关注于所得的结果是否正确,而忽略了不同的实现方法之间可能存在的性能差异,这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。

    笔者在工作实践中发现,不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。

    在对它们进行适当的优化后,其运行速度有了明显地提高!

    下面我将从这三个方面分别进行总结:

    为了更直观地说明问题,所有实例中的SQL运行时间均经过测试,不超过1秒的均表示为(< 1秒)。----

    测试环境: 主机: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中形成了索引覆盖,因而性能达到了最优。

    ---- 5.总结:----

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

    一般来说:

    ①.有大量重复值、且经常有范围查询(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 showplanon,打开showplan选项,就可以看到连接顺序、使用何种索引的信息;想看更详细的信息,需用sa角色执行dbcc(3604,310,302)。

     

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

    1.7索引实战是摘抄网友的文章,引用地址:http://blog.csdn.net/gprime/article/details/1687930

    SQL索引详解

    转自:http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.html

    SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。

      1.1 什么是索引?

      SQL索引有两种,聚集索引和非聚集索引,索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间 

    下面举两个简单的例子:

    图书馆的例子:一个图书馆那么多书,怎么管理呢?建立一个字母开头的目录,例如:a开头的书,在第一排,b开头的在第二排,这样在找什么书就好说了,这个就是一个聚集索引,可是很多人借书找某某作者的,不知道书名怎么办?图书管理员在写一个目录,某某作者的书分别在第几排,第几排,这就是一个非聚集索引

    字典的例子:字典前面的目录,可以按照拼音和部首去查询,我们想查询一个字,只需要根据拼音或者部首去查询,就可以快速的定位到这个汉字了,这个就是索引的好处,拼音查询法就是聚集索引,部首查询就是一个非聚集索引.

        看了上面的例子,下面的一句话大家就很容易理解了:聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。就像字段,聚集索引是连续的,a后面肯定是b,非聚集索引就不连续了,就像图书馆的某个作者的书,有可能在第1个货架上和第10个货架上。还有一个小知识点就是:聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

     

       1.2 索引的存储机制

        首先,无索引的表,查询时,是按照顺序存续的方法扫描每个记录来查找符合条件的记录,这样效率十分低下,举个例子,如果我们将字典的汉字随即打乱,没有前面的按照拼音或者部首查询,那么我们想找一个字,按照顺序的方式去一页页的找,这样效率有多底,大家可以想象。

           聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致,其实理解起来非常简单,还是举字典的例子:如果按照拼音查询,那么都是从a-z的,是具有连续性的,a后面就是b,b后面就是c, 聚集索引就是这样的,他是和表的物理排列顺序是一样的,例如有id为聚集索引,那么1后面肯定是2,2后面肯定是3,所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是,可能按照偏房查询的时候,根据偏旁‘弓’字旁,索引出两个汉字,张和弘,但是这两个其实一个在100页,一个在1000页,(这里只是举个例子),他们的索引顺序和数据库表的排列顺序是不一样的,这个样的就是非聚集索引。

          原理明白了,那他们是怎么存储的呢?在这里简单的说一下,聚集索引就是在数据库被开辟一个物理空间存放他的排列的值,例如1-100,所以当插入数据时,他会重新排列整个整个物理空间,而非聚集索引其实可以看作是一个含有聚集索引的表,他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针,其实就有点和堆栈差不多的感觉了

     

      1.3 什么情况下设置索引 

    动作描述

    使用聚集索引 

     使用非聚集索引

     外键列

     应

     应

     主键列

     应

     应

     列经常被分组排序(order by)

     应

     应

     返回某范围内的数据

     应

     不应

     小数目的不同值

     应

     不应

     大数目的不同值

     不应

     应

     频繁更新的列

    不应 

     应

     频繁修改索引列

     不应

     应

     一个或极少不同值

     不应

     不应

     

    建立索引的原则:

    1) 定义主键的数据列一定要建立索引。

    2) 定义有外键的数据列一定要建立索引。

    3) 对于经常查询的数据列最好建立索引。

    4) 对于需要在指定范围内的快速或频繁查询的数据列;

    5) 经常用在WHERE子句中的数据列。

    6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

    7) 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

    8) 对于定义为text、image和bit的数据类型的列不要建立索引。

    9) 对于经常存取的列避免建立索引 

    9) 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

    10) 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

     

      1.4 如何创建索引

      1.41 创建索引的语法:

    CREATE [UNIQUE][CLUSTERED | NONCLUSTERED]  INDEX  index_name  

    ON {table_name | view_name} [WITH [index_property [,....n]]

    说明:

    UNIQUE: 建立唯一索引。

    CLUSTERED: 建立聚集索引。

    NONCLUSTERED: 建立非聚集索引。

    Index_property: 索引属性。

     UNIQUE索引既可以采用聚集索引结构,也可以采用非聚集索引的结构,如果不指明采用的索引结构,则SQL Server系统默认为采用非聚集索引结构。

    1.42 删除索引语法:

    DROP INDEX table_name.index_name[,table_name.index_name]

    说明:table_name: 索引所在的表名称。

    index_name : 要删除的索引名称。

    1.43 显示索引信息:

    使用系统存储过程:sp_helpindex 查看指定表的索引信息。

    执行代码如下:

    Exec sp_helpindex book1;

     

      1.5 索引使用次数、索引效率、占用CPU检测、索引缺失

      当我们明白了什么是索引,什么时间创建索引以后,我们就会想,我们创建的索引到底效率执行的怎么样?好不好?我们创建的对不对?

      首先我们来认识一下DMV,DMV (dynamic management view)动态管理视图和函数返回特定于实现的内部状态数据。推出SQL Server 2005时,微软介绍了许多被称为dmvs的系统视图,让您可以探测SQL Server 的健康状况,诊断问题,或查看SQL Server实例的运行信息。统计数据是在SQL Server运行的时候开始收集的,并且在SQL Server每次启动的时候,统计数据将会被重置。当你删除或者重新创建其组件时,某些dmv的统计数据也可以被重置,例如存储过程和表,而其它的dmv信息在运行dbcc命令时也可以被重置。

      当你使用一个dmv时,你需要紧记SQL Server收集这些信息有多长时间了,以确定这些从dmv返回的数据到底有多少可用性。如果SQL Server只运行了很短的一段时间,你可能不想去使用一些dmv统计数据,因为他们并不是一个能够代表SQL Server实例可能遇到的真实工作负载的样本。另一方面,SQL Server只能维持一定量的信息,有些信息在进行SQL Server性能管理活动的时候可能丢失,所以如果SQL Server已经运行了相当长的一段时间,一些统计数据就有可能已被覆盖。

      因此,任何时候你使用dmv,当你查看从SQL Server 2005的dmvs返回的相关资料时,请务必将以上的观点装在脑海中。只有当你确信从dmvs获得的信息是准确和完整的,你才能变更数据库或者应用程序代码。

    下面就看一下dmv到底能带给我们那些好的功能呢?

    1.51 :索引使用次数

    我们下看一下下面两种查询方式返回的结果(这两种查询的查询用途一致)

    ①----

    declare @dbid int

    select @dbid = db_id()

    select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id

                , user_seeks, user_scans, user_lookups, user_updates

    from sys.dm_db_index_usage_stats s,

                sys.indexes i

    where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1

    and i.object_id = s.object_id

    and i.index_id = s.index_id

    order by (user_seeks + user_scans + user_lookups + user_updates) asc

    返回查询结果

     

     

    ②:使用多的索引排在前面

    SELECT  objects.name ,

            databases.name ,

            indexes.name ,

            user_seeks ,

            user_scans ,

            user_lookups ,

            partition_stats.row_count

    FROM    sys.dm_db_index_usage_stats stats

            LEFT JOIN sys.objects objects ON stats.object_id = objects.object_id

            LEFT JOIN sys.databases databases ON databases.database_id = stats.database_id

            LEFT JOIN sys.indexes indexes ON indexes.index_id = stats.index_id

                                             AND stats.object_id = indexes.object_id

            LEFT  JOIN sys.dm_db_partition_stats partition_stats ON stats.object_id = partition_stats.object_id

                                                                  AND indexes.index_id = partition_stats.index_id

    WHERE   1 = 1

    --AND databases.database_id = 7

            AND objects.name IS NOT NULL

            AND indexes.name IS NOT NULL

            AND user_scans>0

    ORDER BY user_scans DESC ,

            stats.object_id ,

            indexes.index_id

    返回查询结果

     

     

    user_seeks : 通过用户查询执行的搜索次数。 
     个人理解: 此统计索引搜索的次数

    user_scans: 通过用户查询执行的扫描次数。 
      个人理解:此统计表扫描的次数,无索引配合
    user_lookups: 通过用户查询执行的查找次数。 
     个人理解:用户通过索引查找,在使用RID或聚集索引查找数据的次数,对于堆表或聚集表数据而言和索引配合使用次数
    user_updates:  通过用户查询执行的更新次数。 
      个人理解:索引或表的更新次数

    我们可以清晰的看到,那些索引用的多,那些索引没用过,大家可以根据查询出来的东西去分析自己的数据索引和表

    1.52 :索引提高了多少性能

    新建了索引到底增加了多少数据的效率呢?到底提高了多少性能呢?运行如下SQL可以返回连接缺失索引动态管理视图,发现最有用的索引和创建索引的方法: 

    SELECT  

    avg_user_impact AS average_improvement_percentage,  

    avg_total_user_cost AS average_cost_of_query_without_missing_index,  

    'CREATE INDEX ix_' + [statement] +  

    ISNULL(equality_columns, '_') + 

    ISNULL(inequality_columns, '_') + ' ON ' + [statement] +  

    ' (' + ISNULL(equality_columns, ' ') +  

    ISNULL(inequality_columns, ' ') + ')' +  

    ISNULL(' INCLUDE (' + included_columns + ')', '')  

    AS create_missing_index_command 

    FROM sys.dm_db_missing_index_details a INNER JOIN  

    sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle 

    INNER JOIN sys.dm_db_missing_index_group_stats c ON  

    b.index_group_handle = c.group_handle 

    WHERE avg_user_impact > = 40

     

    返回结果

     

     

    虽然用户能够修改性能提高的百分比,但以上查询返回所有能够将性能提高40%或更高的索引。你可以清晰的看到每个索引提高的性能和效率了

    1.53 :最占用CPU、执行时间最长命令

    这个和索引无关,但是还是在这里提出来,因为他也属于DMV带给我们的功能吗,他可以让你轻松查询出,那些sql语句占用你的cpu最高

     

    SELECT TOP 100 execution_count,

               total_logical_reads /execution_count AS [Avg Logical Reads],

               total_elapsed_time /execution_count AS [Avg Elapsed Time],

                    db_name(st.dbid) as [database name],

               object_name(st.dbid) as [object name],

               object_name(st.objectid) as [object name 1],

               SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, 

               ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) 

                 / 2) + 1) AS statement_text

      FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

     WHERE execution_count > 100

     ORDER BY 1 DESC;

     

    返回结果:

     

     

    执行时间最长的命令

    SELECT TOP 10 COALESCE(DB_NAME(st.dbid),

    DB_NAME(CAST(pa.value as int))+'*',

    'Resource') AS DBNAME,

    SUBSTRING(text,

    -- starting value for substring

            CASE WHEN statement_start_offset = 0

    OR statement_start_offset IS NULL

    THEN 1

    ELSE statement_start_offset/2 + 1 END,

    -- ending value for substring

            CASE WHEN statement_end_offset = 0

    OR statement_end_offset = -1

    OR statement_end_offset IS NULL

    THEN LEN(text)

    ELSE statement_end_offset/2 END -

    CASE WHEN statement_start_offset = 0

    OR statement_start_offset IS NULL

    THEN 1

    ELSE statement_start_offset/2  END + 1

    )  AS TSQL,

    total_logical_reads/execution_count AS AVG_LOGICAL_READS

    FROM sys.dm_exec_query_stats

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa

    WHERE attribute = 'dbid'

    ORDER BY AVG_LOGICAL_READS DESC ;

     

     

    看到了吗?直接可以定位到你的sql语句,优化去吧。还等什么呢?

    1.54:缺失索引

    缺失索引就是帮你查找你的数据库缺少什么索引,告诉你那些字段需要加上索引,这样你就可以根据提示添加你数据库缺少的索引了

    SELECT TOP 10

    [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)

    , avg_user_impact

    , TableName = statement

    , [EqualityUsage] = equality_columns

    , [InequalityUsage] = inequality_columns

    , [Include Cloumns] = included_columns

    FROM    sys.dm_db_missing_index_groups g

    INNER JOIN sys.dm_db_missing_index_group_stats s

    ON s.group_handle = g.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details d

    ON d.index_handle = g.index_handle

    ORDER BY [Total Cost] DESC;

    查询结果如下:

     

     

     

      1.6  适当创建索引覆盖

      假设你在Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外键列(ProductID)上创建了一个索引,假设ProductID列是一个高选中性列,那么任何在where子句中使用索引列(ProductID)的select查询都会更快,如果在外键上没有创建索引,将会发生全部扫描,但还有办法可以进一步提升查询性能。

      假设Sales表有10,000行记录,下面的SQL语句选中400行(总行数的4%): 

    SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID = 112

      我们来看看这条SQL语句在SQL执行引擎中是如何执行的:

      1)Sales表在ProductID列上有一个非聚集索引,因此它查找非聚集索引树找出ProductID=112的记录;

      2)包含ProductID = 112记录的索引页也包括所有的聚集索引键(所有的主键键值,即SalesID);

      3)针对每一个主键(这里是400),SQL Server引擎查找聚集索引树找出真实的行在对应页面中的位置;

      SQL Server引擎从对应的行查找SalesDate和SalesPersonID列的值。

      在上面的步骤中,对ProductID = 112的每个主键记录(这里是400),SQL Server引擎要搜索400次聚集索引树以检索查询中指定的其它列(SalesDate,SalesPersonID)。

      如果非聚集索引页中包括了聚集索引键和其它两列(SalesDate,,SalesPersonID)的值,SQL Server引擎可能不会执行上面的第3和4步,直接从非聚集索引树查找ProductID列速度还会快一些,直接从索引页读取这三列的数值。

      幸运的是,有一种方法实现了这个功能,它被称为“覆盖索引”,在表列上创建覆盖索引时,需要指定哪些额外的列值需要和聚集索引键值(主键)一起存储在索引页中。下面是在Sales 表ProductID列上创建覆盖索引的例子: 

    CREATE INDEX NCLIX_Sales_ProductID--Index name

      ON dbo.Sales(ProductID)--Column on which index is to be created
      INCLUDE(SalesDate, SalesPersonID)--Additional column values to include

      应该在那些select查询中常使用到的列上创建覆盖索引,但覆盖索引中包括过多的列也不行,因为覆盖索引列的值是存储在内存中的,这样会消耗过多内存,引发性能下降。

      

      1.7 索引碎片

    在数据库性能优化一:数据库自身优化一文中已经讲到了这个问题,再次就不做过多的重复地址:http://www.cnblogs.com/AK2012/archive/2012/12/25/2012-1228.html

     

      1.8 索引实战(摘抄)

    之所以这章摘抄,是因为下面这个文章已经写的太好了,估计我写出来也无法比这个好了,所以就摘抄了

    人们在使用SQL时往往会陷入一个误区,即太关注于所得的结果是否正确,而忽略了不同的实现方法之间可能存在的性能差异,这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。

    笔者在工作实践中发现,不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。

    在对它们进行适当的优化后,其运行速度有了明显地提高!

    下面我将从这三个方面分别进行总结:

    为了更直观地说明问题,所有实例中的SQL运行时间均经过测试,不超过1秒的均表示为(< 1秒)。----

    测试环境: 主机: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中形成了索引覆盖,因而性能达到了最优。

    ---- 5.总结:----

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

    一般来说:

    ①.有大量重复值、且经常有范围查询(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 showplanon,打开showplan选项,就可以看到连接顺序、使用何种索引的信息;想看更详细的信息,需用sa角色执行dbcc(3604,310,302)。

     

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

    1.7索引实战是摘抄网友的文章,引用地址:http://blog.csdn.net/gprime/article/details/1687930

    SQL索引详解

    转自:http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.html

    SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。

      1.1 什么是索引?

      SQL索引有两种,聚集索引和非聚集索引,索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间 

    下面举两个简单的例子:

    图书馆的例子:一个图书馆那么多书,怎么管理呢?建立一个字母开头的目录,例如:a开头的书,在第一排,b开头的在第二排,这样在找什么书就好说了,这个就是一个聚集索引,可是很多人借书找某某作者的,不知道书名怎么办?图书管理员在写一个目录,某某作者的书分别在第几排,第几排,这就是一个非聚集索引

    字典的例子:字典前面的目录,可以按照拼音和部首去查询,我们想查询一个字,只需要根据拼音或者部首去查询,就可以快速的定位到这个汉字了,这个就是索引的好处,拼音查询法就是聚集索引,部首查询就是一个非聚集索引.

        看了上面的例子,下面的一句话大家就很容易理解了:聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。就像字段,聚集索引是连续的,a后面肯定是b,非聚集索引就不连续了,就像图书馆的某个作者的书,有可能在第1个货架上和第10个货架上。还有一个小知识点就是:聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

     

       1.2 索引的存储机制

        首先,无索引的表,查询时,是按照顺序存续的方法扫描每个记录来查找符合条件的记录,这样效率十分低下,举个例子,如果我们将字典的汉字随即打乱,没有前面的按照拼音或者部首查询,那么我们想找一个字,按照顺序的方式去一页页的找,这样效率有多底,大家可以想象。

           聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致,其实理解起来非常简单,还是举字典的例子:如果按照拼音查询,那么都是从a-z的,是具有连续性的,a后面就是b,b后面就是c, 聚集索引就是这样的,他是和表的物理排列顺序是一样的,例如有id为聚集索引,那么1后面肯定是2,2后面肯定是3,所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是,可能按照偏房查询的时候,根据偏旁‘弓’字旁,索引出两个汉字,张和弘,但是这两个其实一个在100页,一个在1000页,(这里只是举个例子),他们的索引顺序和数据库表的排列顺序是不一样的,这个样的就是非聚集索引。

          原理明白了,那他们是怎么存储的呢?在这里简单的说一下,聚集索引就是在数据库被开辟一个物理空间存放他的排列的值,例如1-100,所以当插入数据时,他会重新排列整个整个物理空间,而非聚集索引其实可以看作是一个含有聚集索引的表,他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针,其实就有点和堆栈差不多的感觉了

     

      1.3 什么情况下设置索引 

    动作描述

    使用聚集索引 

     使用非聚集索引

     外键列

     应

     应

     主键列

     应

     应

     列经常被分组排序(order by)

     应

     应

     返回某范围内的数据

     应

     不应

     小数目的不同值

     应

     不应

     大数目的不同值

     不应

     应

     频繁更新的列

    不应 

     应

     频繁修改索引列

     不应

     应

     一个或极少不同值

     不应

     不应

     

    建立索引的原则:

    1) 定义主键的数据列一定要建立索引。

    2) 定义有外键的数据列一定要建立索引。

    3) 对于经常查询的数据列最好建立索引。

    4) 对于需要在指定范围内的快速或频繁查询的数据列;

    5) 经常用在WHERE子句中的数据列。

    6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

    7) 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

    8) 对于定义为text、image和bit的数据类型的列不要建立索引。

    9) 对于经常存取的列避免建立索引 

    9) 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

    10) 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

     

      1.4 如何创建索引

      1.41 创建索引的语法:

    CREATE [UNIQUE][CLUSTERED | NONCLUSTERED]  INDEX  index_name  

    ON {table_name | view_name} [WITH [index_property [,....n]]

    说明:

    UNIQUE: 建立唯一索引。

    CLUSTERED: 建立聚集索引。

    NONCLUSTERED: 建立非聚集索引。

    Index_property: 索引属性。

     UNIQUE索引既可以采用聚集索引结构,也可以采用非聚集索引的结构,如果不指明采用的索引结构,则SQL Server系统默认为采用非聚集索引结构。

    1.42 删除索引语法:

    DROP INDEX table_name.index_name[,table_name.index_name]

    说明:table_name: 索引所在的表名称。

    index_name : 要删除的索引名称。

    1.43 显示索引信息:

    使用系统存储过程:sp_helpindex 查看指定表的索引信息。

    执行代码如下:

    Exec sp_helpindex book1;

     

      1.5 索引使用次数、索引效率、占用CPU检测、索引缺失

      当我们明白了什么是索引,什么时间创建索引以后,我们就会想,我们创建的索引到底效率执行的怎么样?好不好?我们创建的对不对?

      首先我们来认识一下DMV,DMV (dynamic management view)动态管理视图和函数返回特定于实现的内部状态数据。推出SQL Server 2005时,微软介绍了许多被称为dmvs的系统视图,让您可以探测SQL Server 的健康状况,诊断问题,或查看SQL Server实例的运行信息。统计数据是在SQL Server运行的时候开始收集的,并且在SQL Server每次启动的时候,统计数据将会被重置。当你删除或者重新创建其组件时,某些dmv的统计数据也可以被重置,例如存储过程和表,而其它的dmv信息在运行dbcc命令时也可以被重置。

      当你使用一个dmv时,你需要紧记SQL Server收集这些信息有多长时间了,以确定这些从dmv返回的数据到底有多少可用性。如果SQL Server只运行了很短的一段时间,你可能不想去使用一些dmv统计数据,因为他们并不是一个能够代表SQL Server实例可能遇到的真实工作负载的样本。另一方面,SQL Server只能维持一定量的信息,有些信息在进行SQL Server性能管理活动的时候可能丢失,所以如果SQL Server已经运行了相当长的一段时间,一些统计数据就有可能已被覆盖。

      因此,任何时候你使用dmv,当你查看从SQL Server 2005的dmvs返回的相关资料时,请务必将以上的观点装在脑海中。只有当你确信从dmvs获得的信息是准确和完整的,你才能变更数据库或者应用程序代码。

    下面就看一下dmv到底能带给我们那些好的功能呢?

    1.51 :索引使用次数

    我们下看一下下面两种查询方式返回的结果(这两种查询的查询用途一致)

    ①----

    declare @dbid int

    select @dbid = db_id()

    select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id

                , user_seeks, user_scans, user_lookups, user_updates

    from sys.dm_db_index_usage_stats s,

                sys.indexes i

    where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1

    and i.object_id = s.object_id

    and i.index_id = s.index_id

    order by (user_seeks + user_scans + user_lookups + user_updates) asc

    返回查询结果

     

     

    ②:使用多的索引排在前面

    SELECT  objects.name ,

            databases.name ,

            indexes.name ,

            user_seeks ,

            user_scans ,

            user_lookups ,

            partition_stats.row_count

    FROM    sys.dm_db_index_usage_stats stats

            LEFT JOIN sys.objects objects ON stats.object_id = objects.object_id

            LEFT JOIN sys.databases databases ON databases.database_id = stats.database_id

            LEFT JOIN sys.indexes indexes ON indexes.index_id = stats.index_id

                                             AND stats.object_id = indexes.object_id

            LEFT  JOIN sys.dm_db_partition_stats partition_stats ON stats.object_id = partition_stats.object_id

                                                                  AND indexes.index_id = partition_stats.index_id

    WHERE   1 = 1

    --AND databases.database_id = 7

            AND objects.name IS NOT NULL

            AND indexes.name IS NOT NULL

            AND user_scans>0

    ORDER BY user_scans DESC ,

            stats.object_id ,

            indexes.index_id

    返回查询结果

     

     

    user_seeks : 通过用户查询执行的搜索次数。 
     个人理解: 此统计索引搜索的次数

    user_scans: 通过用户查询执行的扫描次数。 
      个人理解:此统计表扫描的次数,无索引配合
    user_lookups: 通过用户查询执行的查找次数。 
     个人理解:用户通过索引查找,在使用RID或聚集索引查找数据的次数,对于堆表或聚集表数据而言和索引配合使用次数
    user_updates:  通过用户查询执行的更新次数。 
      个人理解:索引或表的更新次数

    我们可以清晰的看到,那些索引用的多,那些索引没用过,大家可以根据查询出来的东西去分析自己的数据索引和表

    1.52 :索引提高了多少性能

    新建了索引到底增加了多少数据的效率呢?到底提高了多少性能呢?运行如下SQL可以返回连接缺失索引动态管理视图,发现最有用的索引和创建索引的方法: 

    SELECT  

    avg_user_impact AS average_improvement_percentage,  

    avg_total_user_cost AS average_cost_of_query_without_missing_index,  

    'CREATE INDEX ix_' + [statement] +  

    ISNULL(equality_columns, '_') + 

    ISNULL(inequality_columns, '_') + ' ON ' + [statement] +  

    ' (' + ISNULL(equality_columns, ' ') +  

    ISNULL(inequality_columns, ' ') + ')' +  

    ISNULL(' INCLUDE (' + included_columns + ')', '')  

    AS create_missing_index_command 

    FROM sys.dm_db_missing_index_details a INNER JOIN  

    sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle 

    INNER JOIN sys.dm_db_missing_index_group_stats c ON  

    b.index_group_handle = c.group_handle 

    WHERE avg_user_impact > = 40

     

    返回结果

     

     

    虽然用户能够修改性能提高的百分比,但以上查询返回所有能够将性能提高40%或更高的索引。你可以清晰的看到每个索引提高的性能和效率了

    1.53 :最占用CPU、执行时间最长命令

    这个和索引无关,但是还是在这里提出来,因为他也属于DMV带给我们的功能吗,他可以让你轻松查询出,那些sql语句占用你的cpu最高

     

    SELECT TOP 100 execution_count,

               total_logical_reads /execution_count AS [Avg Logical Reads],

               total_elapsed_time /execution_count AS [Avg Elapsed Time],

                    db_name(st.dbid) as [database name],

               object_name(st.dbid) as [object name],

               object_name(st.objectid) as [object name 1],

               SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, 

               ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) 

                 / 2) + 1) AS statement_text

      FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

     WHERE execution_count > 100

     ORDER BY 1 DESC;

     

    返回结果:

     

     

    执行时间最长的命令

    SELECT TOP 10 COALESCE(DB_NAME(st.dbid),

    DB_NAME(CAST(pa.value as int))+'*',

    'Resource') AS DBNAME,

    SUBSTRING(text,

    -- starting value for substring

            CASE WHEN statement_start_offset = 0

    OR statement_start_offset IS NULL

    THEN 1

    ELSE statement_start_offset/2 + 1 END,

    -- ending value for substring

            CASE WHEN statement_end_offset = 0

    OR statement_end_offset = -1

    OR statement_end_offset IS NULL

    THEN LEN(text)

    ELSE statement_end_offset/2 END -

    CASE WHEN statement_start_offset = 0

    OR statement_start_offset IS NULL

    THEN 1

    ELSE statement_start_offset/2  END + 1

    )  AS TSQL,

    total_logical_reads/execution_count AS AVG_LOGICAL_READS

    FROM sys.dm_exec_query_stats

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa

    WHERE attribute = 'dbid'

    ORDER BY AVG_LOGICAL_READS DESC ;

     

     

    看到了吗?直接可以定位到你的sql语句,优化去吧。还等什么呢?

    1.54:缺失索引

    缺失索引就是帮你查找你的数据库缺少什么索引,告诉你那些字段需要加上索引,这样你就可以根据提示添加你数据库缺少的索引了

    SELECT TOP 10

    [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)

    , avg_user_impact

    , TableName = statement

    , [EqualityUsage] = equality_columns

    , [InequalityUsage] = inequality_columns

    , [Include Cloumns] = included_columns

    FROM    sys.dm_db_missing_index_groups g

    INNER JOIN sys.dm_db_missing_index_group_stats s

    ON s.group_handle = g.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details d

    ON d.index_handle = g.index_handle

    ORDER BY [Total Cost] DESC;

    查询结果如下:

     

     

     

      1.6  适当创建索引覆盖

      假设你在Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外键列(ProductID)上创建了一个索引,假设ProductID列是一个高选中性列,那么任何在where子句中使用索引列(ProductID)的select查询都会更快,如果在外键上没有创建索引,将会发生全部扫描,但还有办法可以进一步提升查询性能。

      假设Sales表有10,000行记录,下面的SQL语句选中400行(总行数的4%): 

    SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID = 112

      我们来看看这条SQL语句在SQL执行引擎中是如何执行的:

      1)Sales表在ProductID列上有一个非聚集索引,因此它查找非聚集索引树找出ProductID=112的记录;

      2)包含ProductID = 112记录的索引页也包括所有的聚集索引键(所有的主键键值,即SalesID);

      3)针对每一个主键(这里是400),SQL Server引擎查找聚集索引树找出真实的行在对应页面中的位置;

      SQL Server引擎从对应的行查找SalesDate和SalesPersonID列的值。

      在上面的步骤中,对ProductID = 112的每个主键记录(这里是400),SQL Server引擎要搜索400次聚集索引树以检索查询中指定的其它列(SalesDate,SalesPersonID)。

      如果非聚集索引页中包括了聚集索引键和其它两列(SalesDate,,SalesPersonID)的值,SQL Server引擎可能不会执行上面的第3和4步,直接从非聚集索引树查找ProductID列速度还会快一些,直接从索引页读取这三列的数值。

      幸运的是,有一种方法实现了这个功能,它被称为“覆盖索引”,在表列上创建覆盖索引时,需要指定哪些额外的列值需要和聚集索引键值(主键)一起存储在索引页中。下面是在Sales 表ProductID列上创建覆盖索引的例子: 

    CREATE INDEX NCLIX_Sales_ProductID--Index name

      ON dbo.Sales(ProductID)--Column on which index is to be created
      INCLUDE(SalesDate, SalesPersonID)--Additional column values to include

      应该在那些select查询中常使用到的列上创建覆盖索引,但覆盖索引中包括过多的列也不行,因为覆盖索引列的值是存储在内存中的,这样会消耗过多内存,引发性能下降。

      

      1.7 索引碎片

    在数据库性能优化一:数据库自身优化一文中已经讲到了这个问题,再次就不做过多的重复地址:http://www.cnblogs.com/AK2012/archive/2012/12/25/2012-1228.html

     

      1.8 索引实战(摘抄)

    之所以这章摘抄,是因为下面这个文章已经写的太好了,估计我写出来也无法比这个好了,所以就摘抄了

    人们在使用SQL时往往会陷入一个误区,即太关注于所得的结果是否正确,而忽略了不同的实现方法之间可能存在的性能差异,这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。

    笔者在工作实践中发现,不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。

    在对它们进行适当的优化后,其运行速度有了明显地提高!

    下面我将从这三个方面分别进行总结:

    为了更直观地说明问题,所有实例中的SQL运行时间均经过测试,不超过1秒的均表示为(< 1秒)。----

    测试环境: 主机: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中形成了索引覆盖,因而性能达到了最优。

    ---- 5.总结:----

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

    一般来说:

    ①.有大量重复值、且经常有范围查询(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 showplanon,打开showplan选项,就可以看到连接顺序、使用何种索引的信息;想看更详细的信息,需用sa角色执行dbcc(3604,310,302)。

     

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

    1.7索引实战是摘抄网友的文章,引用地址:http://blog.csdn.net/gprime/article/details/1687930

    SQL索引详解

    转自:http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.html

    SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。

      1.1 什么是索引?

      SQL索引有两种,聚集索引和非聚集索引,索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间 

    下面举两个简单的例子:

    图书馆的例子:一个图书馆那么多书,怎么管理呢?建立一个字母开头的目录,例如:a开头的书,在第一排,b开头的在第二排,这样在找什么书就好说了,这个就是一个聚集索引,可是很多人借书找某某作者的,不知道书名怎么办?图书管理员在写一个目录,某某作者的书分别在第几排,第几排,这就是一个非聚集索引

    字典的例子:字典前面的目录,可以按照拼音和部首去查询,我们想查询一个字,只需要根据拼音或者部首去查询,就可以快速的定位到这个汉字了,这个就是索引的好处,拼音查询法就是聚集索引,部首查询就是一个非聚集索引.

        看了上面的例子,下面的一句话大家就很容易理解了:聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。就像字段,聚集索引是连续的,a后面肯定是b,非聚集索引就不连续了,就像图书馆的某个作者的书,有可能在第1个货架上和第10个货架上。还有一个小知识点就是:聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

     

       1.2 索引的存储机制

        首先,无索引的表,查询时,是按照顺序存续的方法扫描每个记录来查找符合条件的记录,这样效率十分低下,举个例子,如果我们将字典的汉字随即打乱,没有前面的按照拼音或者部首查询,那么我们想找一个字,按照顺序的方式去一页页的找,这样效率有多底,大家可以想象。

           聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致,其实理解起来非常简单,还是举字典的例子:如果按照拼音查询,那么都是从a-z的,是具有连续性的,a后面就是b,b后面就是c, 聚集索引就是这样的,他是和表的物理排列顺序是一样的,例如有id为聚集索引,那么1后面肯定是2,2后面肯定是3,所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是,可能按照偏房查询的时候,根据偏旁‘弓’字旁,索引出两个汉字,张和弘,但是这两个其实一个在100页,一个在1000页,(这里只是举个例子),他们的索引顺序和数据库表的排列顺序是不一样的,这个样的就是非聚集索引。

          原理明白了,那他们是怎么存储的呢?在这里简单的说一下,聚集索引就是在数据库被开辟一个物理空间存放他的排列的值,例如1-100,所以当插入数据时,他会重新排列整个整个物理空间,而非聚集索引其实可以看作是一个含有聚集索引的表,他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针,其实就有点和堆栈差不多的感觉了

     

      1.3 什么情况下设置索引 

    动作描述

    使用聚集索引 

     使用非聚集索引

     外键列

     应

     应

     主键列

     应

     应

     列经常被分组排序(order by)

     应

     应

     返回某范围内的数据

     应

     不应

     小数目的不同值

     应

     不应

     大数目的不同值

     不应

     应

     频繁更新的列

    不应 

     应

     频繁修改索引列

     不应

     应

     一个或极少不同值

     不应

     不应

     

    建立索引的原则:

    1) 定义主键的数据列一定要建立索引。

    2) 定义有外键的数据列一定要建立索引。

    3) 对于经常查询的数据列最好建立索引。

    4) 对于需要在指定范围内的快速或频繁查询的数据列;

    5) 经常用在WHERE子句中的数据列。

    6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

    7) 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

    8) 对于定义为text、image和bit的数据类型的列不要建立索引。

    9) 对于经常存取的列避免建立索引 

    9) 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

    10) 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

     

      1.4 如何创建索引

      1.41 创建索引的语法:

    CREATE [UNIQUE][CLUSTERED | NONCLUSTERED]  INDEX  index_name  

    ON {table_name | view_name} [WITH [index_property [,....n]]

    说明:

    UNIQUE: 建立唯一索引。

    CLUSTERED: 建立聚集索引。

    NONCLUSTERED: 建立非聚集索引。

    Index_property: 索引属性。

     UNIQUE索引既可以采用聚集索引结构,也可以采用非聚集索引的结构,如果不指明采用的索引结构,则SQL Server系统默认为采用非聚集索引结构。

    1.42 删除索引语法:

    DROP INDEX table_name.index_name[,table_name.index_name]

    说明:table_name: 索引所在的表名称。

    index_name : 要删除的索引名称。

    1.43 显示索引信息:

    使用系统存储过程:sp_helpindex 查看指定表的索引信息。

    执行代码如下:

    Exec sp_helpindex book1;

     

      1.5 索引使用次数、索引效率、占用CPU检测、索引缺失

      当我们明白了什么是索引,什么时间创建索引以后,我们就会想,我们创建的索引到底效率执行的怎么样?好不好?我们创建的对不对?

      首先我们来认识一下DMV,DMV (dynamic management view)动态管理视图和函数返回特定于实现的内部状态数据。推出SQL Server 2005时,微软介绍了许多被称为dmvs的系统视图,让您可以探测SQL Server 的健康状况,诊断问题,或查看SQL Server实例的运行信息。统计数据是在SQL Server运行的时候开始收集的,并且在SQL Server每次启动的时候,统计数据将会被重置。当你删除或者重新创建其组件时,某些dmv的统计数据也可以被重置,例如存储过程和表,而其它的dmv信息在运行dbcc命令时也可以被重置。

      当你使用一个dmv时,你需要紧记SQL Server收集这些信息有多长时间了,以确定这些从dmv返回的数据到底有多少可用性。如果SQL Server只运行了很短的一段时间,你可能不想去使用一些dmv统计数据,因为他们并不是一个能够代表SQL Server实例可能遇到的真实工作负载的样本。另一方面,SQL Server只能维持一定量的信息,有些信息在进行SQL Server性能管理活动的时候可能丢失,所以如果SQL Server已经运行了相当长的一段时间,一些统计数据就有可能已被覆盖。

      因此,任何时候你使用dmv,当你查看从SQL Server 2005的dmvs返回的相关资料时,请务必将以上的观点装在脑海中。只有当你确信从dmvs获得的信息是准确和完整的,你才能变更数据库或者应用程序代码。

    下面就看一下dmv到底能带给我们那些好的功能呢?

    1.51 :索引使用次数

    我们下看一下下面两种查询方式返回的结果(这两种查询的查询用途一致)

    ①----

    declare @dbid int

    select @dbid = db_id()

    select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id

                , user_seeks, user_scans, user_lookups, user_updates

    from sys.dm_db_index_usage_stats s,

                sys.indexes i

    where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1

    and i.object_id = s.object_id

    and i.index_id = s.index_id

    order by (user_seeks + user_scans + user_lookups + user_updates) asc

    返回查询结果

     

     

    ②:使用多的索引排在前面

    SELECT  objects.name ,

            databases.name ,

            indexes.name ,

            user_seeks ,

            user_scans ,

            user_lookups ,

            partition_stats.row_count

    FROM    sys.dm_db_index_usage_stats stats

            LEFT JOIN sys.objects objects ON stats.object_id = objects.object_id

            LEFT JOIN sys.databases databases ON databases.database_id = stats.database_id

            LEFT JOIN sys.indexes indexes ON indexes.index_id = stats.index_id

                                             AND stats.object_id = indexes.object_id

            LEFT  JOIN sys.dm_db_partition_stats partition_stats ON stats.object_id = partition_stats.object_id

                                                                  AND indexes.index_id = partition_stats.index_id

    WHERE   1 = 1

    --AND databases.database_id = 7

            AND objects.name IS NOT NULL

            AND indexes.name IS NOT NULL

            AND user_scans>0

    ORDER BY user_scans DESC ,

            stats.object_id ,

            indexes.index_id

    返回查询结果

     

     

    user_seeks : 通过用户查询执行的搜索次数。 
     个人理解: 此统计索引搜索的次数

    user_scans: 通过用户查询执行的扫描次数。 
      个人理解:此统计表扫描的次数,无索引配合
    user_lookups: 通过用户查询执行的查找次数。 
     个人理解:用户通过索引查找,在使用RID或聚集索引查找数据的次数,对于堆表或聚集表数据而言和索引配合使用次数
    user_updates:  通过用户查询执行的更新次数。 
      个人理解:索引或表的更新次数

    我们可以清晰的看到,那些索引用的多,那些索引没用过,大家可以根据查询出来的东西去分析自己的数据索引和表

    1.52 :索引提高了多少性能

    新建了索引到底增加了多少数据的效率呢?到底提高了多少性能呢?运行如下SQL可以返回连接缺失索引动态管理视图,发现最有用的索引和创建索引的方法: 

    SELECT  

    avg_user_impact AS average_improvement_percentage,  

    avg_total_user_cost AS average_cost_of_query_without_missing_index,  

    'CREATE INDEX ix_' + [statement] +  

    ISNULL(equality_columns, '_') + 

    ISNULL(inequality_columns, '_') + ' ON ' + [statement] +  

    ' (' + ISNULL(equality_columns, ' ') +  

    ISNULL(inequality_columns, ' ') + ')' +  

    ISNULL(' INCLUDE (' + included_columns + ')', '')  

    AS create_missing_index_command 

    FROM sys.dm_db_missing_index_details a INNER JOIN  

    sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle 

    INNER JOIN sys.dm_db_missing_index_group_stats c ON  

    b.index_group_handle = c.group_handle 

    WHERE avg_user_impact > = 40

     

    返回结果

     

     

    虽然用户能够修改性能提高的百分比,但以上查询返回所有能够将性能提高40%或更高的索引。你可以清晰的看到每个索引提高的性能和效率了

    1.53 :最占用CPU、执行时间最长命令

    这个和索引无关,但是还是在这里提出来,因为他也属于DMV带给我们的功能吗,他可以让你轻松查询出,那些sql语句占用你的cpu最高

     

    SELECT TOP 100 execution_count,

               total_logical_reads /execution_count AS [Avg Logical Reads],

               total_elapsed_time /execution_count AS [Avg Elapsed Time],

                    db_name(st.dbid) as [database name],

               object_name(st.dbid) as [object name],

               object_name(st.objectid) as [object name 1],

               SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, 

               ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) 

                 / 2) + 1) AS statement_text

      FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

     WHERE execution_count > 100

     ORDER BY 1 DESC;

     

    返回结果:

     

     

    执行时间最长的命令

    SELECT TOP 10 COALESCE(DB_NAME(st.dbid),

    DB_NAME(CAST(pa.value as int))+'*',

    'Resource') AS DBNAME,

    SUBSTRING(text,

    -- starting value for substring

            CASE WHEN statement_start_offset = 0

    OR statement_start_offset IS NULL

    THEN 1

    ELSE statement_start_offset/2 + 1 END,

    -- ending value for substring

            CASE WHEN statement_end_offset = 0

    OR statement_end_offset = -1

    OR statement_end_offset IS NULL

    THEN LEN(text)

    ELSE statement_end_offset/2 END -

    CASE WHEN statement_start_offset = 0

    OR statement_start_offset IS NULL

    THEN 1

    ELSE statement_start_offset/2  END + 1

    )  AS TSQL,

    total_logical_reads/execution_count AS AVG_LOGICAL_READS

    FROM sys.dm_exec_query_stats

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa

    WHERE attribute = 'dbid'

    ORDER BY AVG_LOGICAL_READS DESC ;

     

     

    看到了吗?直接可以定位到你的sql语句,优化去吧。还等什么呢?

    1.54:缺失索引

    缺失索引就是帮你查找你的数据库缺少什么索引,告诉你那些字段需要加上索引,这样你就可以根据提示添加你数据库缺少的索引了

    SELECT TOP 10

    [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)

    , avg_user_impact

    , TableName = statement

    , [EqualityUsage] = equality_columns

    , [InequalityUsage] = inequality_columns

    , [Include Cloumns] = included_columns

    FROM    sys.dm_db_missing_index_groups g

    INNER JOIN sys.dm_db_missing_index_group_stats s

    ON s.group_handle = g.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details d

    ON d.index_handle = g.index_handle

    ORDER BY [Total Cost] DESC;

    查询结果如下:

     

     

     

      1.6  适当创建索引覆盖

      假设你在Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外键列(ProductID)上创建了一个索引,假设ProductID列是一个高选中性列,那么任何在where子句中使用索引列(ProductID)的select查询都会更快,如果在外键上没有创建索引,将会发生全部扫描,但还有办法可以进一步提升查询性能。

      假设Sales表有10,000行记录,下面的SQL语句选中400行(总行数的4%): 

    SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID = 112

      我们来看看这条SQL语句在SQL执行引擎中是如何执行的:

      1)Sales表在ProductID列上有一个非聚集索引,因此它查找非聚集索引树找出ProductID=112的记录;

      2)包含ProductID = 112记录的索引页也包括所有的聚集索引键(所有的主键键值,即SalesID);

      3)针对每一个主键(这里是400),SQL Server引擎查找聚集索引树找出真实的行在对应页面中的位置;

      SQL Server引擎从对应的行查找SalesDate和SalesPersonID列的值。

      在上面的步骤中,对ProductID = 112的每个主键记录(这里是400),SQL Server引擎要搜索400次聚集索引树以检索查询中指定的其它列(SalesDate,SalesPersonID)。

      如果非聚集索引页中包括了聚集索引键和其它两列(SalesDate,,SalesPersonID)的值,SQL Server引擎可能不会执行上面的第3和4步,直接从非聚集索引树查找ProductID列速度还会快一些,直接从索引页读取这三列的数值。

      幸运的是,有一种方法实现了这个功能,它被称为“覆盖索引”,在表列上创建覆盖索引时,需要指定哪些额外的列值需要和聚集索引键值(主键)一起存储在索引页中。下面是在Sales 表ProductID列上创建覆盖索引的例子: 

    CREATE INDEX NCLIX_Sales_ProductID--Index name

      ON dbo.Sales(ProductID)--Column on which index is to be created
      INCLUDE(SalesDate, SalesPersonID)--Additional column values to include

      应该在那些select查询中常使用到的列上创建覆盖索引,但覆盖索引中包括过多的列也不行,因为覆盖索引列的值是存储在内存中的,这样会消耗过多内存,引发性能下降。

      

      1.7 索引碎片

    在数据库性能优化一:数据库自身优化一文中已经讲到了这个问题,再次就不做过多的重复地址:http://www.cnblogs.com/AK2012/archive/2012/12/25/2012-1228.html

     

      1.8 索引实战(摘抄)

    之所以这章摘抄,是因为下面这个文章已经写的太好了,估计我写出来也无法比这个好了,所以就摘抄了

    人们在使用SQL时往往会陷入一个误区,即太关注于所得的结果是否正确,而忽略了不同的实现方法之间可能存在的性能差异,这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。

    笔者在工作实践中发现,不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。

    在对它们进行适当的优化后,其运行速度有了明显地提高!

    下面我将从这三个方面分别进行总结:

    为了更直观地说明问题,所有实例中的SQL运行时间均经过测试,不超过1秒的均表示为(< 1秒)。----

    测试环境: 主机: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中形成了索引覆盖,因而性能达到了最优。

    ---- 5.总结:----

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

    一般来说:

    ①.有大量重复值、且经常有范围查询(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 showplanon,打开showplan选项,就可以看到连接顺序、使用何种索引的信息;想看更详细的信息,需用sa角色执行dbcc(3604,310,302)。

     

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

    1.7索引实战是摘抄网友的文章,引用地址:http://blog.csdn.net/gprime/article/details/1687930

    展开全文
  • SQL索引

    千次阅读 2016-04-06 11:47:53
    SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。  1.1 什么是索引?  SQL索引有两种,聚集索引和非聚集索引,索引...
    
      SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。

    1.1 什么是索引?

      SQL索引有两种,聚集索引和非聚集索引,索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间 

    下面举两个简单的例子:

    图书馆的例子:一个图书馆那么多书,怎么管理呢?建立一个字母开头的目录,例如:a开头的书,在第一排,b开头的在第二排,这样在找什么书就好说了,这个就是一个聚集索引,可是很多人借书找某某作者的,不知道书名怎么办?图书管理员在写一个目录,某某作者的书分别在第几排,第几排,这就是一个非聚集索引

    字典的例子:字典前面的目录,可以按照拼音和部首去查询,我们想查询一个字,只需要根据拼音或者部首去查询,就可以快速的定位到这个汉字了,这个就是索引的好处,拼音查询法就是聚集索引,部首查询就是一个非聚集索引.

        看了上面的例子,下面的一句话大家就很容易理解了:聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。就像字段,聚集索引是连续的,a后面肯定是b,非聚集索引就不连续了,就像图书馆的某个作者的书,有可能在第1个货架上和第10个货架上。还有一个小知识点就是:聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

     

    1.2 索引的存储机制

        首先,无索引的表,查询时,是按照顺序存续的方法扫描每个记录来查找符合条件的记录,这样效率十分低下,举个例子,如果我们将字典的汉字随即打乱,没有前面的按照拼音或者部首查询,那么我们想找一个字,按照顺序的方式去一页页的找,这样效率有多底,大家可以想象。

           聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致,其实理解起来非常简单,还是举字典的例子:如果按照拼音查询,那么都是从a-z的,是具有连续性的,a后面就是b,b后面就是c, 聚集索引就是这样的,他是和表的物理排列顺序是一样的,例如有id为聚集索引,那么1后面肯定是2,2后面肯定是3,所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是,可能按照偏房查询的时候,根据偏旁‘弓’字旁,索引出两个汉字,张和弘,但是这两个其实一个在100页,一个在1000页,(这里只是举个例子),他们的索引顺序和数据库表的排列顺序是不一样的,这个样的就是非聚集索引。

          原理明白了,那他们是怎么存储的呢?在这里简单的说一下,聚集索引就是在数据库被开辟一个物理空间存放他的排列的值,例如1-100,所以当插入数据时,他会重新排列整个整个物理空间,而非聚集索引其实可以看作是一个含有聚集索引的表,他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针,其实就有点和堆栈差不多的感觉了

     

    1.3 什么情况下设置索引


    动作描述

    使用聚集索引 

     使用非聚集索引

     外键列

     应

     应

     主键列

     应

     应

     列经常被分组排序(order by)

     应

     应

     返回某范围内的数据

     应

     不应

     小数目的不同值

     应

     不应

     大数目的不同值

     不应

     应

     频繁更新的列

    不应 

     应

     频繁修改索引列

     不应

     应

     一个或极少不同值

     不应

     不应

     

    建立索引的原则:

    1) 定义主键的数据列一定要建立索引。

    2) 定义有外键的数据列一定要建立索引。

    3) 对于经常查询的数据列最好建立索引。

    4) 对于需要在指定范围内的快速或频繁查询的数据列;

    5) 经常用在WHERE子句中的数据列。

    6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

    7) 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

    8) 对于定义为textimagebit的数据类型的列不要建立索引。

    9) 对于经常存取的列避免建立索引 

    9) 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

    10) 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

     

    1.4 如何创建索引

      1.41 创建索引的语法:

    CREATE [UNIQUE][CLUSTERED | NONCLUSTERED]  INDEX  index_name  
    
    ON {table_name | view_name} [WITH [index_property [,....n]]

    说明:

    UNIQUE: 建立唯一索引。

    CLUSTERED: 建立聚集索引。

    NONCLUSTERED: 建立非聚集索引。

    Index_property: 索引属性。

     UNIQUE索引既可以采用聚集索引结构,也可以采用非聚集索引的结构,如果不指明采用的索引结构,则SQL Server系统默认为采用非聚集索引结构。

      1.42 删除索引语法:

    DROP INDEX table_name.index_name[,table_name.index_name]

    说明:table_name: 索引所在的表名称。

    index_name : 要删除的索引名称。

      1.43 显示索引信息:

    使用系统存储过程:sp_helpindex 查看指定表的索引信息。

    执行代码如下:

    Exec sp_helpindex book1;
    
    

    1.5 索引使用次数、索引效率、占用CPU检测、索引缺失


      当我们明白了什么是索引,什么时间创建索引以后,我们就会想,我们创建的索引到底效率执行的怎么样?好不好?我们创建的对不对?

      首先我们来认识一下DMV,DMV (dynamic management view)动态管理视图和函数返回特定于实现的内部状态数据。推出SQL Server 2005时,微软介绍了许多被称为dmvs的系统视图,让您可以探测SQL Server 的健康状况,诊断问题,或查看SQL Server实例的运行信息。统计数据是在SQL Server运行的时候开始收集的,并且在SQL Server每次启动的时候,统计数据将会被重置。当你删除或者重新创建其组件时,某些dmv的统计数据也可以被重置,例如存储过程和表,而其它的dmv信息在运行dbcc命令时也可以被重置。

      当你使用一个dmv时,你需要紧记SQL Server收集这些信息有多长时间了,以确定这些从dmv返回的数据到底有多少可用性。如果SQL Server只运行了很短的一段时间,你可能不想去使用一些dmv统计数据,因为他们并不是一个能够代表SQL Server实例可能遇到的真实工作负载的样本。另一方面,SQL Server只能维持一定量的信息,有些信息在进行SQL Server性能管理活动的时候可能丢失,所以如果SQL Server已经运行了相当长的一段时间,一些统计数据就有可能已被覆盖。

      因此,任何时候你使用dmv,当你查看从SQL Server 2005dmvs返回的相关资料时,请务必将以上的观点装在脑海中。只有当你确信从dmvs获得的信息是准确和完整的,你才能变更数据库或者应用程序代码。

    下面就看一下dmv到底能带给我们那些好的功能呢?

      1.51 索引使用次数

    我们下看一下下面两种查询方式返回的结果(这两种查询的查询用途一致)

    ----

    declare @dbid int
    
    select @dbid = db_id()
    
    select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id
    
                , user_seeks, user_scans, user_lookups, user_updates
    
    from sys.dm_db_index_usage_stats s,
    
                sys.indexes i
    
    where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1
    
    and i.object_id = s.object_id
    
    and i.index_id = s.index_id
    
    order by (user_seeks + user_scans + user_lookups + user_updates) asc


    返回查询结果

     

     

    ②:使用多的索引排在前面

    SELECT  objects.name ,
    
            databases.name ,
    
            indexes.name ,
    
            user_seeks ,
    
            user_scans ,
    
            user_lookups ,
    
            partition_stats.row_count
    
    FROM    sys.dm_db_index_usage_stats stats
    
            LEFT JOIN sys.objects objects ON stats.object_id = objects.object_id
    
            LEFT JOIN sys.databases databases ON databases.database_id = stats.database_id
    
            LEFT JOIN sys.indexes indexes ON indexes.index_id = stats.index_id
    
                                             AND stats.object_id = indexes.object_id
    
            LEFT  JOIN sys.dm_db_partition_stats partition_stats ON stats.object_id = partition_stats.object_id
    
                                                                  AND indexes.index_id = partition_stats.index_id
    
    WHERE   1 = 1
    
    --AND databases.database_id = 7
    
            AND objects.name IS NOT NULL
    
            AND indexes.name IS NOT NULL
    
            AND user_scans>0
    
    ORDER BY user_scans DESC ,
    
            stats.object_id ,
    
            indexes.index_id


    返回查询结果

     

     

    user_seeks : 通过用户查询执行的搜索次数。 
     个人理解: 此统计索引搜索的次数


    我们可以清晰的看到,那些索引用的多,那些索引没用过,大家可以根据查询出来的东西去分析自己的数据索引和表

    1.52 索引提高了多少性能


    新建了索引到底增加了多少数据的效率呢?到底提高了多少性能呢?运行如下SQL可以返回连接缺失索引动态管理视图,发现最有用的索引和创建索引的方法:


    SELECT  
    
    avg_user_impact AS average_improvement_percentage,  
    
    avg_total_user_cost AS average_cost_of_query_without_missing_index,  
    
    'CREATE INDEX ix_' + [statement] +  
    
    ISNULL(equality_columns, '_') + 
    
    ISNULL(inequality_columns, '_') + ' ON ' + [statement] +  
    
    ' (' + ISNULL(equality_columns, ' ') +  
    
    ISNULL(inequality_columns, ' ') + ')' +  
    
    ISNULL(' INCLUDE (' + included_columns + ')', '')  
    
    AS create_missing_index_command 
    
    FROM sys.dm_db_missing_index_details a INNER JOIN  
    
    sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle 
    
    INNER JOIN sys.dm_db_missing_index_group_stats c ON  
    
    b.index_group_handle = c.group_handle 
    
    WHERE avg_user_impact > = 40
    
     


    返回结果

     

     

    虽然用户能够修改性能提高的百分比,但以上查询返回所有能够将性能提高40%或更高的索引。你可以清晰的看到每个索引提高的性能和效率了

    1.53 :最占用CPU、执行时间最长命令

    这个和索引无关,但是还是在这里提出来,因为他也属于DMV带给我们的功能吗,他可以让你轻松查询出,那些sql语句占用你的cpu最高

     

    SELECT TOP 100 execution_count,
    
               total_logical_reads /execution_count AS [Avg Logical Reads],
    
               total_elapsed_time /execution_count AS [Avg Elapsed Time],
    
                    db_name(st.dbid) as [database name],
    
               object_name(st.dbid) as [object name],
    
               object_name(st.objectid) as [object name 1],
    
               SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, 
    
               ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) 
    
                 / 2) + 1) AS statement_text
    
      FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    
     WHERE execution_count > 100
    
     ORDER BY 1 DESC;


     

    返回结果:

     

     

    执行时间最长的命令

    SELECT TOP 10 COALESCE(DB_NAME(st.dbid),
    
    DB_NAME(CAST(pa.value as int))+'*',
    
    'Resource') AS DBNAME,
    
    SUBSTRING(text,
    
    -- starting value for substring
    
            CASE WHEN statement_start_offset = 0
    
    OR statement_start_offset IS NULL
    
    THEN 1
    
    ELSE statement_start_offset/2 + 1 END,
    
    -- ending value for substring
    
            CASE WHEN statement_end_offset = 0
    
    OR statement_end_offset = -1
    
    OR statement_end_offset IS NULL
    
    THEN LEN(text)
    
    ELSE statement_end_offset/2 END -
    
    CASE WHEN statement_start_offset = 0
    
    OR statement_start_offset IS NULL
    
    THEN 1
    
    ELSE statement_start_offset/2  END + 1
    
    )  AS TSQL,
    
    total_logical_reads/execution_count AS AVG_LOGICAL_READS
    
    FROM sys.dm_exec_query_stats
    
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
    
    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
    
    WHERE attribute = 'dbid'
    
    ORDER BY AVG_LOGICAL_READS DESC ;


     

     

    看到了吗?直接可以定位到你的sql语句,优化去吧。还等什么呢?

    1.54:缺失索引

    缺失索引就是帮你查找你的数据库缺少什么索引,告诉你那些字段需要加上索引,这样你就可以根据提示添加你数据库缺少的索引了

    SELECT TOP 10
    
    [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
    
    , avg_user_impact
    
    , TableName = statement
    
    , [EqualityUsage] = equality_columns
    
    , [InequalityUsage] = inequality_columns
    
    , [Include Cloumns] = included_columns
    
    FROM    sys.dm_db_missing_index_groups g
    
    INNER JOIN sys.dm_db_missing_index_group_stats s
    
    ON s.group_handle = g.index_group_handle
    
    INNER JOIN sys.dm_db_missing_index_details d
    
    ON d.index_handle = g.index_handle
    
    ORDER BY [Total Cost] DESC;


    查询结果如下:

     

     

     

    1.6  适当创建索引覆盖

      假设你在Sales(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外键列(ProductID)上创建了一个索引,假设ProductID列是一个高选中性列,那么任何在where子句中使用索引列(ProductID)select查询都会更快,如果在外键上没有创建索引,将会发生全部扫描,但还有办法可以进一步提升查询性能。

      假设Sales表有10,000行记录,下面的SQL语句选中400(总行数的4%): 

    SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID = 112

      我们来看看这条SQL语句在SQL执行引擎中是如何执行的:

      1)Sales表在ProductID列上有一个非聚集索引,因此它查找非聚集索引树找出ProductID=112的记录;

      2)包含ProductID = 112记录的索引页也包括所有的聚集索引键(所有的主键键值,即SalesID);

      3)针对每一个主键(这里是400)SQL Server引擎查找聚集索引树找出真实的行在对应页面中的位置;

      SQL Server引擎从对应的行查找SalesDateSalesPersonID列的值。

      在上面的步骤中,对ProductID = 112的每个主键记录(这里是400)SQL Server引擎要搜索400次聚集索引树以检索查询中指定的其它列(SalesDateSalesPersonID)

      如果非聚集索引页中包括了聚集索引键和其它两列(SalesDate,SalesPersonID)的值,SQL Server引擎可能不会执行上面的第34步,直接从非聚集索引树查找ProductID列速度还会快一些,直接从索引页读取这三列的数值。

      幸运的是,有一种方法实现了这个功能,它被称为覆盖索引,在表列上创建覆盖索引时,需要指定哪些额外的列值需要和聚集索引键值(主键)一起存储在索引页中。下面是在Sales ProductID列上创建覆盖索引的例子: 

        CREATE INDEX NCLIX_Sales_ProductID--Index name
      ON dbo.Sales(ProductID)--Column on which index is to be created
      INCLUDE(SalesDate, SalesPersonID)--Additional column values to include

      应该在那些select查询中常使用到的列上创建覆盖索引,但覆盖索引中包括过多的列也不行,因为覆盖索引列的值是存储在内存中的,这样会消耗过多内存,引发性能下降。

      

    1.7 索引碎片


    在数据库性能优化一:数据库自身优化一文中已经讲到了这个问题,再次就不做过多的重复地址:http://www.cnblogs.com/AK2012/archive/2012/12/25/2012-1228.html

     

    1.8 索引实战(摘抄)


    之所以这章摘抄,是因为下面这个文章已经写的太好了,估计我写出来也无法比这个好了,所以就摘抄了

    人们在使用SQL时往往会陷入一个误区,即太关注于所得的结果是否正确,而忽略了不同的实现方法之间可能存在的性能差异,这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。

    笔者在工作实践中发现,不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。

    在对它们进行适当的优化后,其运行速度有了明显地提高!

    下面我将从这三个方面分别进行总结:

    为了更直观地说明问题,所有实例中的SQL运行时间均经过测试,不超过1秒的均表示为(< 1秒)。----

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

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

    数据库:Sybase11.0.3

     

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

    例:表record620000行,试看在不同的索引下,下面几个 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中形成了索引覆盖,因而性能达到了最优。


    ---- 5.总结:----

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

    一般来说:

    ①.有大量重复值、且经常有范围查询(between, >,< >=,< =)和order bygroup by发生的列,可考虑建立群集索引;

    ②.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;

    ③.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。

     

    二、不充份的连接条件:

    例:表card7896行,在card_no上有一个非聚集索引,表account191122行,在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+(外层表account191122*内层表card上对应外层表第一行所要查找的3页)=595907I/O

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

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

    总结:

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

    2.查看执行方案的方法-- set showplanon,打开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.例:表stuff200000行,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数据库性能的影响。

    实践证明,表的行数越多,工作表的性能就越差,当stuff620000行时,执行时间竟达到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)
    begin
    select @a=count(*) from stuff where id_no='0'
    select @b=count(*) from stuff where id_no='1'
    end
    select @c=@a+@b
    select @d=convert(char(10),@c)
    print @d


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

     

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


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

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

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

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


    展开全文
  • SQL索引工作原理

    千次阅读 2019-04-26 22:17:06
    SQL 当一个新表被创建之时,系统将在磁盘中分配一段以8K为单位的连续空间,当字段的值从内存写入磁盘时,就在这一既定空间随机保存,当一个8K用完的时候, SQLS指针会自动分配一个8K的空间。这里,每个8K空间被称为...

     

    SQL 当一个新表被创建之时,系统将在磁盘中分配一段以8K为单位的连续空间,当字段的值从内存写入磁盘时,就在这一既定空间随机保存,当一个8K用完的时候, SQLS指针会自动分配一个8K的空间。这里,每个8K空间被称为一个数据页(Page),又名页面或数据页面,并分配从0-7的页号,每个文件的第0页记录引导信息,叫文件头(File header);每8个数据页(64K)的组合形成扩展区(Extent),称为扩展。全部数据页的组合形成堆(Heap)。

    SQLS 规定行不能跨越数据页,所以,每行记录的最大数据量只能为8K。这就是char和varchar这两种字符串类型容量要限制在8K以内的原因,存储超过 8K的数据应使用text类型,实际上,text类型的字段值不能直接录入和保存,它只是存储一个指针,指向由若干8K的文本数据页所组成的扩展区,真正的数据正是放在这些数据页中。

    页面有空间页面和数据页面之分。当一个扩展区的8个数据页中既包含了空间页面又包括了数据或索引页面时,称为混合扩展(Mixed Extent),每张表都以混合扩展开始;反之,称为一致扩展专门保存数据及索引信息。表被创建之时,SQLS在混合扩展中为其分配至少一个数据页面,随着数据量的增长,SQLS可即时在混合扩展中分配出7个页面,当数据超过8个页面时,则从一致扩展中分配数据页面。

    空间页面专门负责数据空间的分配和管理,包括:PFS页面(Page free space):记录一个页面是否已分配、位于混合扩展还是一致扩展以及页面上还有多少可用空间等信息;GAM页面(Global allocation map)和SGAM页面(Secodary global allocation map):用来记录空闲的扩展或含有空闲页面的混合扩展的位置。SQLS综合利用这三种类型的页面文件

    在必要时为数据表创建新空间;数据页或索引页则专门保存数据及索引信息,SQLS使用4种类型的数据页面来管理表或索引:它们是IAM页、数据页、文本/图像页和索引页。

    在WINDOWS 中,我们对文件执行的每一步操作,在磁盘上的物理位置只有系统(system)才知道;SQL SERVER沿袭了这种工作方式,在插入数据的过程中,不但每个字段值在数据页面中的保存位置是随机的,而且每个数据页面在“堆”中的排列位置也只有系统(system)才知道。这是为什么呢?众所周知,OS 之所以能管理DISK,是因为在系统启动时首先加载了文件分配表:FAT(File Allocation Table),正是由它管理文件系统并记录对文件的一切操作,系统才得以正常运行;同理,作为管理系统级的SQL

    SERVER,也有这样一张类似FAT的表存在,它就是索引分布映像页:IAM(Index Allocation Map)。

    IAM的存在,使SQLS对数据表的物理管理有了可能。

    IAM 页从混合扩展中分配,记录了8个初始页面的位置和该扩展区的位置,每个IAM页面能管理512,000个数据页面,如

    果数据量太大,SQLS也可以增加更多的IAM页,可以位于文件的任何位置。第一个IAM页被称为FirstIAM,其中记录了以

    后的IAM页的位置。

    数据页和文本/图像页互反,前者保存非文本/图像类型的数据,因为它们都不超过8K的容量,后者则只保存超过8K容

    量的文本或图像类型数据。而索引页顾名思义,保存的是与索引结构相关的数据信息。了解页面的问题有助我们下

    一步准确理解SQLS维护索引的方式,如页拆分、填充因子等。

     

    二、索引的基本概念

    什么是索引呢?索引是一种特殊类型的数据库对象,它与表有着密切的联系。

    索引是为检索而存在的。如一些书籍的末尾就专门附有索引,指明了某个关键字在正文中的出现的页码位置,方便我们查找,但大多数的书籍只有目录,目录不是索引,只是书中内容的排序,并不提供真正的检索功能。可见建立索引要单独占用空间;索引也并不是必须要建立的,它们只是为更好、更快的检索和定位关键字而存在。

    再进一步说,我们要在图书馆中查阅图书,该怎么办呢?图书馆的前台有很多叫做索引卡片柜的小柜子,里面分了若干的类别供我们检索图书,比如你可以用书名的笔画顺序或者拼音顺序作为查找的依据,你还可以从作者名的笔画顺序或拼音顺序去查询想要的图书,反正有许多检索方式,但有一点很明白,书库中的书并没有按照这些卡片柜中的顺序排列——虽然理论上可以这样做,事实上,所有图书的脊背上都人工的粘贴了一个特定的编号①,它们是以这个顺序在排列。索引卡片中并没有指明这本书摆放在书库中的第几个书架的第几本,仅仅指明了这个特定的编号。管理员则根据这一编号将请求的图书返回到读者手中。这是很形象的例子,以下的讲解将会反复用到它。

    SQLS 在安装完成之后,安装程序会自动创建master、model、tempdb等几个特殊的系统数据库,其中master是SQLS的

    主数据库,用于保存和管理其它系统数据库、用户数据库以及SQLS的系统信息,它在SQLS中的地位与WINDOWS下的注册表相当。

    master中有一个名为sysindexes的系统表,专门管理索引。SQLS查询数据表的操作都必须用到它,毫无疑义,它是本文主角之一。查看一张表的索引属性,可以在查询分析器中使用以下命令:select * from sysindexes where id=object_id(‘tablename’);而要查看表的索引所占空间的大小,可以使用系统存储过程命令:sp_spaceused tablename,其中参数tablename为被索引的表名。

     

    三、平衡树

    如果你通过书后的索引知道了一个关键字所在的页码,你有可能通过随机的翻寻,最终到达正确的页码。但更科学更快捷的方法是:首先把书翻到大概二分之一的位置,如果要找的页码比该页的页码小,就把书向前翻到四分之一处,否则,就把书向后翻到四分之三的地方,依此类推,把书页续分成更小的部分,直至正确的页码。这叫“两分法”,微软在官方教程MOC里另有一种说法:叫B树(B-Tree,Balance Tree),即平衡树。

    一个表索引由若干页面组成,这些页面构成了一个树形结构。B 树由“根”(root)开始,称为根级节点,它通过指向另外两个页,把一个表的记录从逻辑上分成两个部分:“枝”—--非叶级节点(Non-Leaf Level);而非叶级节点又分别指向更小的部分:“叶”——叶级节点(Leaf Level)。根节点、非叶级节点和叶级节点都位于索引页中,统称为索引节点,属于索引页的范筹。这些“枝”、“叶”最终指向了具体的数据页(Page)。在根级节点和叶级节点之间的叶又叫数据中间页。

    “根”(root)对应了sysindexes表的Root字段,其中记载了非叶级节点的物理位置(即指针);非叶级节点位于根

    节点和叶节点之间,记载了指向叶级节点的指针;而叶级节点则最终指向数据页。这就是“平衡树”。

     

    四、聚集索引和非聚集索引

    从形式上而言,索引分为聚集索引(Clustered Indexes)和非聚集索引(NonClustered Indexes)。

    聚集索引相当于书籍脊背上那个特定的编号。如果对一张表建立了聚集索引,其索引页中就包含着建立索引的列的值(下称索引键值),那么表中的记录将按照该索引键值进行排序。比如,我们如果在“姓名”这一字段上建立了聚集索引,则表中的记录将按照姓名进行排列;如果建立了聚集索引的列是数值类型的,那么记录将按照该键值的数值大小来进行排列。

    非聚集索引用于指定数据的逻辑顺序,也就是说,表中的数据并没有按照索引键值指定的顺序排列,而仍然按照插入记录时的顺序存放。其索引页中包含着索引键值和它所指向该行记录在数据页中的物理位置,叫做行定位符(RID:Row ID)。好似书后面的的索引表,索引表中的顺序与实际的页码顺序也是不一致的。而且一本书也许有多个索引。比如主题索引和作者索引。

    SQL Server在默认的情况下建立的索引是非聚集索引,由于非聚集索引不对表中的数据进行重组,而只是存储索引键

    值并用一个指针指向数据所在的页面。一个表如果没有聚集索引时,理论上可以建立249个非聚集索引。每个非聚集索引提供访问数据的不同排序顺序。

     

    五、数据是怎样被访问的

    若能真正理解了以上索引的基础知识,那么再回头来看索引的工作原理就简单和轻松多了。

    (一)SQLS怎样访问没有建立任何索引数据表:

    Heap 译成汉语叫做“堆”,其本义暗含杂乱无章、无序的意思,前面提到数据值被写进数据页时,由于每一行记录之间并没地有特定的排列顺序,所以行与行的顺序就是随机无序的,当然表中的数据页也就是无序的了,而表中所有数据页就形成了“堆”,可以说,一张没有索引的数据表,就像一个只有书柜而没有索引卡片柜的图书馆,书库里面塞满了一堆乱七八糟的图书。当读者对管理员提交查询请求后,管理员就一头钻进书库,对照查找内容从头开始一架一柜的逐本查找,运气好的话,在第一个书架的第一本书就找到了,运气不好的话,要到最后一个书架的最后一本书才找到。

    SQLS 在接到查询请求的时候,首先会分析sysindexes表中一个叫做索引标志符(INDID: Index ID)的字段的值,如果该值为0,表示这是一张数据表而不是索引表,SQLS就会使用sysindexes表的另一个字段——也就是在前面提到过的FirstIAM值中找到该表的IAM页链——也就是所有数据页集合。这就是对一个没有建立索引的数据表进行数据查找的方式,是不是很没效率?对于没有索引的表,对于一“堆”这样的记录,SQLS也只能这样做,而且更没劲的是,即使在第一行就找到了被查询的记录,SQLS仍然要从头到尾的将表扫描一次。这种查询称为“遍历”,又叫“表扫描”。

    可见没有建立索引的数据表照样可以运行,不过这种方法对于小规模的表来说没有什么太大的问题,但要查询海量的数据效率就太低了。

    (二)SQLS怎样访问建立了非聚集索引的数据表:

    如前所述,非聚集索引可以建多个,具有B树结构,其叶级节点不包含数据页,只包含索引行。假定一个表中只有非聚集索引,则每个索引行包含了非聚集索引键值以及行定位符(ROW ID,RID),他们指向具有该键值的数据行。每一个RID由文件ID、页编号和在页中行的编号组成。当INDID 的值在2-250之间时,意味着表中存在非聚集索引页。此时,SQLS调用ROOT字段的值指向非聚集索引B树的ROOT,在其中查找与被查询最相近的值,根据这个值找到在非叶级节点中的页号,然后顺藤摸瓜,在叶级节点相应的页面中找到该值的RID,最后根据这个RID在Heap中定位所在的页和行并返回到查询端。

    例如:假定在Lastname上建立了非聚集索引,则执行Select * From Member Where Lastname=’Ota’时,查询过程是:

    ①SQLS 查询INDID值为2;②立即从根出发,在非叶级节点中定位最接近Ota的值“Martin”,并查到其位于叶级页面的第61页;③仅在叶级页面的第61 页的Martin下搜寻Ota的RID,其RID显示为N∶706∶4,表示Lastname字段中名为Ota的记录位于堆的第707页的第4行,N表示文件的ID值,与数据无关;④根据上述信息,SQLS立马在堆的第 707页第4行将该记录“揪”出来并显示于前台(客户端)。视表的数据量大小,整个查询过程费时从百分之几毫秒到数毫秒不等。

    在谈到索引基本概念的时候,我们就提到了这种方式:

    图书馆的前台有很多索引卡片柜,里面分了若干的类别,诸如按照书名笔画或拼音顺序、作者笔画或拼音顺序等等,但不同之处有二:① 索引卡片上记录了每本书摆放的具体位置——位于某柜某架的第几本——而不是“特殊编号”;② 书脊上并没有那个“特殊编号”。管理员在索引柜中查到所需图书的具体位置(RID)后,根据RID直接在书库中的具体位置将书提出来。(可以想象成数是杂乱无章的摆放)

    显然,这种查询方式效率很高,但资源占用极大,因为书库中书的位置随时在发生变化,必然要求管理员花费额外的精力和时间随时做好索引更新。

    (三)SQLS怎样访问建立了聚集索引的数据表:

    在聚集索引中,数据所在的数据页是叶级,索引数据所在的索引页是非叶级。

    查询原理和上述对非聚集索引的查询相似,但由于记录是按照聚集索引中索引键值进行排序,换句话说,聚集索引的索引键值也就是具体的数据页。这就好比书库中的书就是按照书名的拼音在排序,而且也只按照这一种排序方式建立相应的索引卡片,于是查询起来要比上述只建立非聚集索引的方式要简单得多。仍以上面的查询为例:假定在Lastname字段上建立了聚集索引,则执行Select * From Member Where Lastname=’Ota’时,查询过程是:①SQLS查询INDID值为1,这是在系统中只建立了聚集索引的标志;②立即从根出发,在非叶级节点中定位最接近Ota的值“Martin”,并查到其位于叶级页面的第120页;③在位于叶级页面第120页的Martin下搜寻到Ota条目,而这一条目已是数据记录本身;④将该记录返回客户端。

    这一次的效率比第二种方法更高,以致于看起来更美,然而它最大的优点也恰好是它最大的缺点——由于同一张表

    中同时只能按照一种顺序排列,所以在任何一种数据表中的聚集索引只能建立一个;并且建立聚集索引需要至少相

    当于源表120%的附加空间,以存放源表的副本和索引中间页!

    难道鱼和熊掌就不能兼顾了吗?办法是有的。

    (四)SQLS怎样访问既有聚集索引、又有非聚集索引的数据表:

    如果我们在建立非聚集索引之前先建立了聚集索引的话,那么非聚集索引就可以使用聚集索引的关键字进行检索,

    就像在图书馆中,前台卡片柜中的可以有不同类别的图书索引卡,然而每张卡片上都载明了那个特殊编号——并不

    是书籍存放的具体位置。这样在最大程度上既照顾了数据检索的快捷性,又使索引的日常维护变得更加可行,这是

    最为科学的检索方法。

    也就是说,在只建立了非聚集索引的情况下,每个叶级节点指明了记录的行定位符(RID);而在既有聚集索引又有

    非聚集索引的情况下,每个叶级节点所指向的是该聚集索引的索引键值,即数据记录本身。

    假设聚集索引建立在Lastname上,而非聚集索引建立在Firstname上,当执行Select * From Member Where Firstname=’

    Mike’时,查询过程是:①SQLS查询INDID值为2;②立即从根出发,在Firstname的非聚集索引的非叶级节点中定位最

    接近Mike的值“Jose”条目;③从Jose条目下的叶级页面中查到Mike逻辑位置——不是RID而是聚集索引的指针;④

    根据这一指针所指示位置,直接进入位于Lastname的聚集索引中的叶级页面中到达Mike数据记录本身;⑤将该记录返

    回客户端。

    这就完全和我们在“索引的基本概念”中讲到的现实场景完全一样了,当数据发生更新的时候,SQLS 只负责对聚集

    索引的健值驾以维护,而不必考虑非聚集索引,只要我们在ID类的字段上建立聚集索引,而在其它经常需要查询的

    字段上建立非聚集索引,通过这种科学的、有针对性的在一张表上分别建立聚集索引和非聚集索引的方法,我们既

    享受了索引带来的灵活与快捷,又相对规避了维护索引所导致的大量的额外资源消耗。

     

    六、索引的优点和不足

    索引有一些先天不足:1:建立索引,系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。2:更新数据的时

    候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和索引的一致性——这就如同图书馆要有专门的

    位置来摆放索引柜,并且每当库存图书发生变化时都需要有人将索引卡片重整以保持索引与库存的一致。

    当然建立索引的优点也是显而易见的:在海量数据的情况下,如果合理的建立了索引,则会大大加强SQLS执行查询

    、对结果进行排序、分组的操作效率。

    实践表明,不恰当的索引不但于事无补,反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时

    比没有索引花费更多的系统时间。比如在如下字段建立索引应该是不恰当的:1、很少或从不引用的字段;2、逻辑

    型的字段,如男或女(是或否)等。

    综上所述,提高查询效率是以消耗一定的系统资源为代价的,索引不能盲目的建立,必须要有统筹的规划,一定要

    在“加快查询速度”与“降低修改速度”之间做好平衡,有得必有失,此消则彼长。这是考验一个DBA是否优秀的很

    重要的指标。

    上期,我们就索引的基本概念和数据查询原理作了详细阐述,知道了建立索引时一定要在“加快查询速度”与“降

    低修改速度”之间做好平衡,有得必有失,此消则彼长。那么,SQLS维护索引时究竟怎样消耗资源?应该从哪些方

    面对索引进行管理与优化?以下从六个方面来回答这些问题:

    一.页分裂

    微软MOC教导我们:当一个数据页达到了8K容量,如果此时发生插入或更新数据的操作,将导致页的分裂(又名页拆

    分):

    1.有聚集索引的情况下:聚集索引将被插入和更新的行指向特定的页,该页由聚集索引关键字决定;

    2.只有堆的情况下:只要有空间就可以插入新的行,但是如果我们对行数据的更新需要更多的空间,以致大于当前

    页的可用空间,行就被移到新的页中,并且在原位置留下一个转发指针,指向被移动的新行,如果具有转发指针的

    行又被移动了,那么原来的指针将重新指向新的位置;

    3.如果堆中有非聚集索引,那么尽管插入和更新操作在堆中不会发生页分裂,但是在非聚集索引上仍然产生页分裂

    无论有无索引,大约一半的数据将保留在老页面,而另一半将放入新页面,并且新页面可能被分配到任何可用的页

    。所以,频繁页分裂,后果很严重,将使物理表产生大量数据碎片,导致直接造成I/O效率的急剧下降,最后,不得

    不停止SQLS的运行并重建索引。

    二.填充因子

    然而在“混沌之初”,就可以在一定程度上避免不愉快出现,在创建索引时,可以为这个索引指定一个填充因子,

    以便在索引的每个叶级页面上保留一定百分比的空间,将来数据可以进行扩充和减少页分裂。填充因子是从0到100

    的百分比数值,设为100时表示将数据页填满,只有当不会对数据进行更改时(例如只读表中)才用此设置。值越小则

    数据页上的空闲空间越大,这样可以减少在索引增长过程中进行页分裂的需要,但这一操作需要占用更多的硬盘空

    间。

    填充因子只在创建索引时执行,索引创建以后,当表中进行数据的添加、删除或更新时,是不会保持填充因子的,

    如果想在数据页上保持额外的空间,则有悖于使用填充因子的本意,因为随着数据的输入,SQLS必须在每个页上进

    行页拆分,以保持填充因子指定的空闲空间。因此,只有在表中的数据进行了较大的变动,才可以填充数据页的空

    闲空间。这时,可以从容的重建索引,重新指定填充因子,重新分布数据。

    反之,填充因子指定不当,就会降低数据库的读取性能,其降低量与填充因子设置值成反比。例如,当填充因子的

    值为50时,数据库的读取性能会降低两倍。所以,只有在表中根据现有数据创建新索引,并且可以预见将来会对这

    些数据进行哪些更改时,设置填充因子才有意义。

    三.两道数学题

    假定数据库设计没有问题,那么是否像上篇分析的那样,当你建立了众多的索引,在查询工作中SQLS就只能按照“

    最高指示”用索引处理每一个提交的查询呢?答案是否定的。

    “数据是怎样被访问的”一文中提到的四种索引方案只是一种静态的、标准的和理论上的分析比较。实际上,SQLS

    几乎完全是“自主”的决定是否使用索引或使用哪一个索引

    这是怎么回事呢?

    让我们先来算一道题:如果某表的一条记录在磁盘上占用1000字节(1K)的话,我们对其中10字节的一个字段建立索引

    ,那么该记录对应的索引大小只有10 字节(0.01K)。上篇说过,SQLS的最小空间分配单元是“页(Page)”,一个页

    面在磁盘上占用8K空间,所以一页只能存储8条“记录”,但可以存储800条“索引”。现在我们要从一个有8000条

    记录的表中检索符合某个条件的记录(有Where子句),如果没有索引的话,我们需要遍历8000条 ×1000字节/8K字节

    =1000个页面才能够找到结果。如果在检索字段上有上述索引的话,那么我们可以在8000条×10字节/8K字节=10个页

    面中就检索到满足条件的索引块,然后根据索引块上的指针逐一找到结果数据块,这样I/O访问量肯定要少得多。

    然而有时用索引比不用索引还快。

    同上,如果要无条件检索全部记录(不用Where子句),不用索引的话,需要访问8000条×1000字节/8K字节=1000个页面

    ;而使用索引的话,首先检索索引,访问8000条×10字节/8K字节=10个页面得到索引检索结果,再根据索引检索结果

    去对应数据页面,由于是检索全部数据,所以需要再访问 8000条×1000字节/8K字节=1000个页面将全部数据读取出来

    ,一共访问了1010个页面,这显然不如不用索引快。

    SQLS内部有一套完整的数据索引优化技术,在上述情况下,SQLS会自动使用表扫描的方式检索数据而不会使用任何

    索引。那么SQLS是怎么知道什么时候用索引,什么时候不用索引的呢?因为SQLS除了维护数据信息外,还维护着数

    据统计信息。

    四.统计信息

    打开企业管理器,单击“Database”节点,右击Northwind数据库→单击“属性”→选择“Options”选项卡,观察

    “Settings”下的各项复选项,你发现了什么?

    从Settings中我们可以看到,在数据库中,SQLS将默认的自动创建和更新统计信息,这些统计信息包括数据密度和分

    布信息,正是它们帮助SQLS确定最佳的查询策略:建立查询计划和是否使用索引以及使用什么样的索引。

    在创建索引时,SQLS会创建分布数据页来存放有关索引的两种统计信息:分布表和密度表。查询优化器使用这些统

    计信息估算使用该索引进行查询的成本(Cost),并在此基础上判断该索引对某个特定查询是否有用。

    随着表中的数据发生变化,SQLS自动定期更新这些统计信息。采样是在各个数据页上随机进行。从磁盘读取一个数

    据页后,该数据页上的所有行都被用来更新统计信息。统计信息更新的频率取决于字段或索引中的数据量以及数据

    更改量。比如,对于有一万条记录的表,当1000个索引键值发生改变时,该表的统计信息便可能需要更新,因为

    1000 个值在该表中占了10%,这是一个很大的比例。而对于有1千万条记录的表来说,1000个索引值发生更改的意义

    则可以忽略不计,因此统计信息就不会自动更新。

    五.索引的人工维护

    上面讲到,某些不合适的索引将影响到SQLS的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个

    程度时将会影响到索引的使用。这时需要用户自己来维护索引。

    随着数据行的插入、删除和数据页的分裂,有些索引页可能只包含几页数据,另外应用在执行大量I/O的时候,重建

    非聚聚集索引可以维护I/O的效率。重建索引实质上是重新组织B树。需要重建索引的情况有:

    1.数据和使用模式大幅度变化;

    2.排序的顺序发生改变;

    3.要进行大量插入操作或已经完成;

    4.使用I/O查询的磁盘读次数比预料的要多;

    5.由于大量数据修改,使得数据页和索引页没有充分使用而导致空间的使用超出估算;

    6.dbcc检查出索引有问题。

    六.索引的使用原则

    接近尾声的时候,让我们再从另一个角度认识索引的两个重要属性----惟一性索引和复合性索引。

    惟一性索引保证在索引列中的全部数据是惟一的,不会包含冗余数据。如果表中已经有一个主键约束或者惟一性约

    束,那么当创建表或者修改表时,SQLS自动创建一个惟一性索引。但出于必须保证惟一性,那么应该创建主键约束

    或者惟一性键约束,而不是创建一个惟一性索引。 复合索引就是一个索引创建在两个列或者多个列上。在搜索时,当两个或者多个列作为一个关键值时,最好在这些

    列上创建复合索引。当创建复合索引时,应该考虑这些规则:最多可以把16个列合并成一个单独的复合索引,构成

    复合索引的列的总长度不能超过900字节;在复合索引中,所有的列必须来自同一个表中,不能跨表建立复合列;在

    复合索引中,列的排列顺序是非常重要的,原则上,应该首先定义最惟一的列,例如在(COL1,COL2)上的索引与

    在(COL2, COL1)上的索引是不相同的,因为两个索引的列的顺序不同;为了使查询优化器使用复合索引,查询语

    句中的WHERE子句必须参考复合索引中第一个列。

    综上所述,我们总结了如下索引使用原则:

    1.逻辑主键使用惟一的成组索引,对系统键(作为存储过程)采用惟一的非成组索引,对任何外键列采用非成组索

    引。考虑数据库的空间有多大,表如何进行访问,还有这些访问是否主要用作读写;

    2.不要索引memo/note 字段,不要索引大型字段(有很多字符),这样作会让索引占用太多的存储空间;

    3.不要索引常用的小型表;

    4.一般不要为小型数据表设置过多的索引,如果经常有插入和删除操作就更不要设置索引,因为SQLS对插入和删除

    操作提供的索引维护可能比扫描表空间消耗的时间更多。

    查询是一个物理过程,表面上是SQLS在东跑西跑,其实真正大部分压马路的工作是由磁盘输入输出系统(I/O)完成,全

    表扫描需要从磁盘上读表的每一个数据页,如果有索引指向数据值,则I/O读几次磁盘就可以了。但是,在随时发生

    的增、删、改操作中,索引的存在会大大增加工作量,因此,合理的索引设计是建立在对各种查询的分析和预测上

    的,只有正确地使索引与程序结合起来,才能产生最佳的优化方案。

    SQLS是一个很复杂的系统,让索引以及查询背后的东西真相大白,可以帮助我们更为深刻的了解我们的系统。一句

    话,索引就像盐,少则无味多则咸。

     

     

       DBCC DBREINDEX重建索引提高SQL Server性能  

    大多数SQL Server表需要索引来提高数据的访问速度,如果没有索引,SQL Server 要进行表格扫描读取表中的每一个记录才能找到索要的数据。索引可以分为簇索引和非簇索引,簇索引通过重排表中的数据来提高数据的访问速度,而非簇索引则通过维护表中的数据指针来提高数据的索引。  

    1. 索引的体系结构  

    为什么要不断的维护表的索引?首先,简单介绍一下索引的体系结构。SQL Server在硬盘中用8KB页面在数据库文件内存放数据。缺省情况下这些页面及其包含的数据是无组织的。为了使混乱变为有序,就要生成索引。生成索引后,就有了索引页和数据页,数据页保存用户写入的数据信息。索引页存放用于检索列的数据值清单(关键字)和索引表中该值所在纪录的地址指针。索引分为簇索引和非簇索引,簇索引实质上是将表中的数据排序,就好像是字典的索引目录。非簇索引不对数据排序,它只保存了数据的指针地址。向一个带簇索引的表中插入数据,当数据页达到100%时,由于页面没有空间插入新的的纪录,这时就会发生分页,SQL Server 将大约一半的数据从满页中移到空页中,从而生成两个半的满页。这样就有大量的数据空间。簇索引是双向链表,在每一页的头部保存了前一页、后一页地址以及分页后数据移动的地址,由于新页可能在数据库文件中的任何地方,因此页面的链接不一定指向磁盘的下一个物理页,链接可能指向了另一个区域,这就形成了分块,从而减慢了系统的速度。对于带簇索引和非簇索引的表来说,非簇索引的关键字是指向簇索引的,而不是指向数据页的本身。  

    为了克服数据分块带来的负面影响,需要重构表的索引,这是非常费时的,因此只能在需要时进行。可以通过DBCC SHOWCONTIG来确定是否需要重构表的索引。  

    2. DBCC SHOWCONTIG用法  

    下面举例来说明DBCC SHOWCONTIG和DBCC REDBINDEX的使用方法。以应用程序中的Employee数据表作为例子,在 SQL Server的Query analyzer输入命令:  

    use database_name  

        declare @table_id int  

        set @table_id=object_id('Employee')  

        dbcc showcontig(@table_id)  

    输出结果:  

    DBCC SHOWCONTIG scanning 'Employee' table...  

        Table: 'Employee' (1195151303); index ID: 1, database ID: 53  

        TABLE level scan performed.  

    - Pages Scanned................................: 179  

    - Extents Scanned..............................: 24  

    - Extent Switches..............................: 24  

    - Avg. Pages per Extent........................: 7.5  

    - Scan Density [Best Count:Actual Count].......: 92.00% [23:25]  

    - Logical Scan Fragmentation ..................: 0.56%  

    - Extent Scan Fragmentation ...................: 12.50%  

    - Avg. Bytes Free per Page.....................: 552.3  

    - Avg. Page Density (full).....................: 93.18%  

        DBCC execution completed. If DBCC printed error messages, contact your system administrator.  

    通过分析这些结果可以知道该表的索引是否需要重构。如下描述了每一行的意义:  

    信息                                           描述  

    Pages Scanned                    表或索引中的长页数  

    Extents Scanned                 表或索引中的长区页数  

    Extent Switches                  DBCC遍历页时从一个区域到另一个区域的次数  

    Avg. Pages per Extent         相关区域中的页数  

    Scan Density[Best Count:Actual Count]          

        Best Count是连续链接时的理想区域改变数,Actual Count是实际区域改变数,Scan Density为100%表示没有分块。  

    Logical Scan Fragmentation   扫描索引页中失序页的百分比  

    Extent Scan Fragmentation    不实际相邻和包含链路中所有链接页的区域数  

    Avg. Bytes Free per Page       扫描页面中平均自由字节数  

    Avg. Page Density (full)         平均页密度,表示页有多满  

    从上面命令的执行结果可以看的出来,Best count为23 而Actual Count为25这表明orders表有分块需要重构表索引。下面通过DBCC DBREINDEX来重构表的簇索引。  

    3. DBCC DBREINDEX 用法  

    重建指定数据库中表的一个或多个索引。  

    语法  

    DBCC DBREINDEX  

            (    [ 'database.owner.table_name'      

                    [ , index_name  

                        [ , fillfactor ]  

                    ]   

                ]   

            )       

    参数  

        'database.owner.table_name'  

    是要重建其指定的索引的表名。数据库、所有者和表名必须符合标识符的规则。有关更多信息,请参见使用标识符。如果提供 database 或 owner 部分,则必须使用单引号 (') 将整个 database.owner.table_name 括起来。如果只指定 table_name,则不需要单引号。  

    index_name  

    是要重建的索引名。索引名必须符合标识符的规则。如果未指定 index_name 或指定为 ' ',就要对表的所有索引进行重建。  

    fillfactor  

    是创建索引时每个索引页上要用于存储数据的空间百分比。fillfactor 替换起始填充因子以作为索引或任何其它重建的非聚集索引(因为已重建聚集索引)的新默认值。如果 fillfactor 为 0,DBCC DBREINDEX 在创建索引时将使用指定的起始 fillfactor。  

    同样在Query Analyzer中输入命令:  

    dbcc dbreindex('database_name.dbo.Employee','',90)  

    然后再用DBCC SHOWCONTIG查看重构索引后的结果:  

    DBCC SHOWCONTIG scanning 'Employee' table...  

        Table: 'Employee' (1195151303); index ID: 1, database ID: 53  

        TABLE level scan performed.  

    - Pages Scanned................................: 178  

    - Extents Scanned..............................: 23  

    - Extent Switches..............................: 22  

    - Avg. Pages per Extent........................: 7.7  

    - Scan Density [Best Count:Actual Count].......: 100.00% [23:23]  

    - Logical Scan Fragmentation ..................: 0.00%  

    - Extent Scan Fragmentation ...................: 0.00%  

    - Avg. Bytes Free per Page.....................: 509.5  

    - Avg. Page Density (full).....................: 93.70%  

        DBCC execution completed. If DBCC printed error messages, contact your system administrator.  

    通过结果我们可以看到Scan Denity为100%。  

    1.合理使用索引  

          索引是数据库中重要的数据结构,它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处,其使用原则如下:   

    ●在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引。   

    ●在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。   

    ●在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。   

    ●如果待排序的列有多个,可以在这些列上建立复合索引(compound index)。   

    ●使用系统工具。如Informix数据库有一个tbcheck工具,可以在可疑的索引上进行检查。在一些数据库服务器上,索引可能失效或者因为频繁操作而使得读取效率降低,如果一个使用索引的查询不明不白地慢下来,可以试着用tbcheck工具检查索引的完整性,必要时进行修复。另外,当数据库表更新大量数据后,删除并重建索引可以提高查询速度。   

             

          (1)在下面两条select语句中:  

             select * from table1  where  field1<=10000 and field1>=0;  

             select * from table1  where  field1>=0 and field1<=10000;  

             如果数据表中的数据field1都>=0,则第一条select语句要比第二条select语句效率高的多,因为第二条select语句的第一个条件耗费了大量的系统资源。  

       第一个原则:在where子句中应把最具限制性的条件放在最前面。  

       

          (2)在下面的select语句中:  

             select * from tab  where  a=… and b=… and c=…;  

            若有索引index(a,b,c),则where子句中字段的顺序应和索引中字段顺序一致。  

       第二个原则:where子句中字段的顺序应和索引中字段顺序一致。  

       

    以下假设在field1上有唯一索引I1,在field2上有非唯一索引I2。  

    (3) select field3,field4 from tb where field1='sdf'        快  

        select * from tb where field1='sdf'      慢,  

          因为后者在索引扫描后要多一步ROWID表访问。  

       

          (4) select field3,field4 from tb where field1>='sdf'        快  

    select field3,field4 from tb where field1>'sdf'        慢  

          因为前者可以迅速定位索引。  

       

          (5) select field3,field4 from tb where field2 like 'R%'    快  

        select field3,field4 from tb where field2 like '%R'    慢,  

        因为后者不使用索引。  

       

          (6) 使用函数如:  

    select field3,field4 from tb where upper(field2)='RMN'不使用索引。  

          如果一个表有两万条记录,建议不使用函数;如果一个表有五万条以上记录,严格禁止使用函数!两万条记录以下没有限制。  

       

          (7) 空值不在索引中存储,所以  

        select field3,field4 from tb where field2 is[not] null不使用索引。  

       

          (8) 不等式如  

        select field3,field4 from tb where field2!='TOM'不使用索引。  

        相似地,  

        select field3,field4 from tb where field2 not in('M','P')不使用索引。  

       

          (9) 多列索引,只有当查询中索引首列被用于条件时,索引才能被使用。  

       

          (10)  MAX,MIN等函数,如  

    Select max(field2) from tb使用索引。所以,如果需要对字段取max,min,sum等,应该加索引。  

          一次只使用一个聚集函数,如:  

    select “min”=min(field1), “max”=max(field1)  from tb        

    不如:select “min”=(select min(field1) from tb) , “max”=(select max(field1) from tb)      

             

          (11) 重复值过多的索引不会被查询优化器使用。而且因为建了索引,修改该字段值时还要修改索引,所以更新该字段的操作比没有索引更慢。  

       

          (12) 索引值过大(如在一个char(40)的字段上建索引),会造成大量的I/O开销(甚至会超过表扫描的I/O开销)。因此,尽量使用整数索引。 Sp_estspace可以计算表和索引的开销。  

       

          (13) 对于多列索引,order by的顺序必须和索引的字段顺序一致。  

       

          (14) 在sybase中,如果order by的字段组成一个簇索引,那么无须做order by。记录的排列顺序是与簇索引一致的。

    展开全文
  • SqlServer索引碎片整理脚本,提据库查询效率,很有用。
  • SQL索引建立原则和使用

    万次阅读 多人点赞 2017-12-01 18:52:21
    SQL索引有两种,聚集索引和非聚集索引 聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续 字典的拼音查询法就是聚集索引,字典的部首查询就是一个非聚集索引. 聚集索引和非...
  • SqlServer重建索引

    2018-06-13 17:47:38
    用于SqlServer的索引重建,全语句实现,可根据实际情况进行部分关键表的索引重建。
  • sql索引语法

    千次阅读 2018-10-11 09:56:08
    一、索引  索引是一种快速访问数据的途径,可提高数据库性能。索引使数据库程序无须对整个表进行扫描,...物理分类:区分索引、非分区索引、B树索引、正向索引、反向索引,位图索引。  (二)索引的缺点:  1...
  • 资源名称:SQL Server 视图及索引的创建及使用内容简介: 本文档主要讲述的是SQL Server 视图及索引的创建及使用;目的是通过企业管理器和Transact_SQL语句对视图进行创建、修改和删除通过企业管理器。希望本文档会给...
  • SQL索引的概念

    万次阅读 多人点赞 2019-05-13 15:52:31
    MySQL索引的概念 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构,通俗来...user表有3个字段(id、name、age),存储8条记录,当我们查找age为20的记录时,sql语句 select * from user ...
  • SQL索引失效的几种情况

    千次阅读 2021-01-16 16:09:17
    1、 没有正确使用符合索引 例如我们根据user表中的A,B,C创建一个符合索引 create index on user(A,B,C) 实际上mysql是为我们创建了三个索引 A AB ABC 假如你从B 或者C 开始查 就没有用到索引 select B from user ...
  • 日期范围查询的SQL索引

    千次阅读 2021-01-19 20:14:54
    几天来,我一直在努力提高我的数据库的性能,并且有些问题我仍然对SQL Server数据库中的索引感到困惑.我会尽量提供尽可能丰富的信息.我的数据库目前包含大约10万行并且将继续增长,因此我正在努力寻找一种方法来使其更...
  • sql索引失效的几种情况

    千次阅读 2020-06-16 21:15:14
    索引什么时候不会生效,以下集中情况会导致索引失效: 1.条件中用or,即使其中有条件带索引,也不会使用索引查询(这就是查询尽量不要用or的原因,用in吧); 注意:使用or,又想索引生效,只能将or条件中的每个...
  • 查询的时间是加了默认的betree索引的。在between同一天的时候,索引有效: 当between不在同一天时,索引无效: 而无论是否在同一天,用>和<时,索引都是无效的。
  • 【数据库】HIVE SQL索引及其使用

    千次阅读 2019-04-16 21:55:58
    最近在用一张8亿数据量表作为主表去关联一个千万量级的表时遇到一个问题,JOB运行的特别慢,而且...由于对于索引不是特别了解,查了各种资料,这里做一个总结,加深对索引的理解。 HIVE中如何创建索引? 第一步 ...
  • SQL修复简单数据库索引错误,SQL修复简单数据库索引错误
  • 今天在做SQL Tuning的时候遇到一个典型的example,立个Flag,梳理知识点,在这里也做个分享,就是我们在写SQL的时候如果对索引字段使用函数炒作,则导致该SQL不走索引扫描查询,导致SQL性能下降。 SQL如下: SELECT ...
  • SQL 索引的作用(真的是超详细)

    万次阅读 2017-12-08 21:43:53
    当在SQLServer上创建索引时,可指定是按升序还是降序存储键。 2、建立索引 使用 CREATE INDEX 语句可以为表创建索引。 语法格式: CREATE[ UNIQUE ] /* 指定索引是否唯一* /  [ ...
  • 哪些情况下sql索引会失效

    千次阅读 2017-10-22 15:36:34
    索引什么时候不会生效,以下集中情况会导致索引失效: 1.条件中用or,即使其中有条件带索引,也不会使用索引查询(这就是查询尽量不要用or的原因,用in吧) 注意:使用or,又想索引生效,只能将or条件中的每个...
  • SQL索引查找与索引扫描

    千次阅读 2017-11-20 23:34:07
    本文导读:虽然都是通过索引取到相应数据,但是两者在过程上还是有区别的,索引扫描与表扫描差不多,都是把索引从开始扫描到结束,而索引查找就不一样了,会根据你查询的字符,定位到索引的局部位置,然后再开始查找...
  • SQL索引创建原则、创建与删除示例

    千次阅读 2017-06-03 09:44:21
    一、索引创建原则: 表的主键、外键必须有索引; 数据量超过300的表应该有索引; 经常与其他表进行连接的表,在连接字段上应该建立索引; 经常出现在Where子句中的字段,特别是大表的字段,应该建立索引索引应该...
  • sql索引的创建、修改、删除、查看

    万次阅读 2018-06-29 10:31:01
    ALTER TABLE索引创建 索引表名:table_name 索引名称:index_name 1.PRIMARY KEY(主键索引) mysql&amp;gt;ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.UNIQUE(唯一索引) mysql&amp...
  • 压缩包中是2分PDF文档,分别对sqlserver索引进行了介绍和对索引的优化
  • 十分钟弄懂SQL 索引及优化

    千次阅读 2019-10-18 11:06:48
    索引概念和作用 索引是一种使记录有序化的技术,它可以指定按某列/某几列预先排序,从而大大提高查询速度(类似于汉语词典中按照拼音或者笔画查找)。 索引的主要作用是加快数据查找速度,提高数据库的性能。 ...
  • SQL索引查看

    千次阅读 2021-11-18 13:56:03
    SQL索引查看 select * from user_indexes where table_name=upper('表名'); 表索引查看详情 select * from user_ind_columns where index_name=('index_name');

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 690,829
精华内容 276,331
关键字:

sql索引

友情链接: STM32F103C8T6例程.zip