精华内容
下载资源
问答
  • 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索引

    千次阅读 2016-09-24 11:39:44
    索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。 数据库索引 什么是索引 数据库索引好比是一本书前面的目录,能加快数据库的查询速度。 例如这样一个查询:select *...

    索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。

    数据库索引

    什么是索引

    数据库索引好比是一本书前面的目录,能加快数据库的查询速度。

    例如这样一个查询:select * from table1 where id=44。如果没有索引,必须遍历整个表,直到ID等于44的这一行被找到为止;有了索引之后(必须是在ID这一列上建立的索引),直接在索引里面找 44(也就是在ID这一列找),就可以得知这一行的位置,也就是找到了这一行。可见,索引是用来定位的。

    索引分为聚簇索引和非聚簇索引两种,聚簇索引 是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。

    概述

    建立索引的目的是加快对表中记录的查找或排序。

    为表设置索引要付出代价的:一是增加了数据库的存储空间,二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

    B树索引-Sql Server索引方式

    为什么要创建索引

    创建索引可以大大提高系统的性能。

    第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

    第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

    第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

    第四,在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

    第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

    也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?因为,增加索引也有许多不利的方面。

    第一,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。

    第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

    第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

    在哪建索引

    索引是建立在数据库表中的某些列的上面。在创建索引的时候,应该考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列上创建索引:

    在经常需要搜索的列上,可以加快搜索的速度;

    在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;

    在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;

    在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;

    在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

    同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点:

    第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

    第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

    第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少,不利于使用索引。

    第四,当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改操作远远多于检索操作时,不应该创建索引。


    此外,除了数据库索引之外,在LAMP结果如此流行的今天,数据库(尤其是MySQL)性能优化也是海量数据处理的一个热点。下面就结合自己的经验,聊一聊MySQL数据库优化的几个方面。

    首先,在数据库设计的时候,要能够充分的利用索引带来的性能提升,至于如何建立索引,建立什么样的索引,在哪些字段上建立索引,上面已经讲的很清楚了,这里不在赘述。另外就是设计数据库的原则就是尽可能少的进行数据库写操作(插入,更新,删除等),查询越简单越好。如下:

    数据库设计

    其次,配置缓存是必不可少的,配置缓存可以有效的降低数据库查询读取次数,从而缓解数据库服务器压力,达到优化的目的,一定程度上来讲,这算是一个“围魏救赵”的办法。可配置的缓存包括索引缓存(key_buffer),排序缓存(sort_buffer),查询缓存(query_buffer),表描述符缓存(table_cache),如下图:

    配置缓存

    第三,切表,切表也是一种比较流行的数据库优化方法。分表包括两种方式:横向分表和纵向分表,其中,纵向分表比较有使用意义,但是分表会造成查询的负担,因此在数据库设计之初,要想好:

    分表

    第四,日志分析,在数据库运行了较长一段时间以后,会积累大量的LOG日志,其实这里面的蕴涵的有用的信息量还是很大的。通过分析日志,可以找到系统性能的瓶颈,从而进一步寻找优化方案。

    性能分析

    以上讲的都是单机MySQL的性能优化的一些经验,但是随着信息大爆炸,单机的数据库服务器已经不能满足我们的需求,于是,多多节点,分布式数据库网络出现了,其一般的结构如下:

    分布式数据库结构

    这种分布式集群的技术关键就是“同步复制”。。。未完待续~~
    展开全文
  • 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索引建立原则和使用

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

    前言

    • SQL索引有两种,聚集索引和非聚集索引
    •  
    • 聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续
    •  
    • 字典的拼音查询法就是聚集索引,字典的部首查询就是一个非聚集索引.
    •  
    • 聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致
    •  
    • 聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

     

    建立索引的原则:

     

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

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

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

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

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

     

    6) 经常出现在关键字order bygroup bydistinct后面的字段,建立索引。

     

    如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

     

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

     

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

     

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

     

    9) 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5

    索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

     

    10) 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。

    对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。

    因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

     

    索引的不足之处

     

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

     

    使用索引时,有以下一些技巧和注意事项:

    索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的

    所以我们在数据库设计时不要让字段的默认值为NULL

    使用短索引

    对列进行索引,如果可能应该指定一个前缀长度。

    例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。

    短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作

    索引列排序

    MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的

    因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

    like语句操作

    一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。

    like “%aaa%” 不会使用索引,而like aaa%”可以使用索引。

    不要在列上进行运算

    select * from users where YEAR(adddate)<2007;

    将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成

    select * from users where adddate<‘2007-01-01’;

    不使用NOT IN和<>操作

     

    索引的建立时机

    一般来说,在WHEREJOIN中出现的列需要建立索引,但也不完全如此,

    因为MySQL只对<,<=,=,>,>=,BETWEENIN,以及某些时候的LIKE才会使用索引。

    因为在以通配符%和_开头作查询时,MySQL不会使用索引。

    索引的使用

    建立索引 

    create [UNIQUE|FULLTEXT] index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);

    示例:

    alter table table_name ADD INDEX [index_name] (index_col_name,...)

    CREATE INDEX paywayid_index ON pay_order_trade (paywayid)

    组合索引

    CREATE INDEX idx_example ON table1 (col1 ASC, col2 DESC, col3 ASC)

    示例:

    ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

    最左前缀:顾名思义,就是最左优先,

    上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。

    删除索引 

    DROP INDEX index_name ON tbl_name;

    alter table table_name drop index index_name;

    删除主键(索引)比较特别:

    alter table t_b drop primary key;

    查询索引(均可) 

    show index from table_name;

    show keys from table_name;

    desc table_Name;

    展开全文
  • 如何在 SQL 数据库优化 索引,SQL索引优化-技巧 出处:http://www.cr173.com/html/8688_all.html 在数据库存优化设计中往往会提到索引,这编文章就来详细的说明一下在 SQL SERVER 下面的建立索引的技巧和需要注意...
  • SQL索引的概念

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

    千次阅读 2018-10-11 09:56:08
    一、索引  索引是一种快速访问数据的途径,可提高数据库性能。索引使数据库程序无须对整个表进行扫描,...物理分类:区分索引、非分区索引、B树索引、正向索引、反向索引,位图索引。  (二)索引的缺点:  1...
  • SQL索引建立规则与优化

    千次阅读 2013-12-18 12:02:13
    SQL索引建立规则与优化 一.了解索引 实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非...
  • SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。  1.1 什么是索引?  SQL索引有两种,聚集索引和非聚集索引...
  • SQL索引排序

    2013-09-26 12:46:55
    只有聚集索引 SQL查询才会按照索引排序
  • SQL索引查找与索引扫描

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

    千次阅读 2018-07-27 15:16:16
    在聚集索引B树中,只有叶子节点实际存储数据, 而其他根节点和中间节点仅仅用于存放查找叶子节点的数据.每一个叶子节点为一页,每页是不可分割的. 而SQLServer向每个页内存储数据的最小单位是表的行(Row). 当叶子...
  • 今天在做SQL Tuning的时候遇到一个典型的example,立个Flag,梳理知识点,在这里也做个分享,就是我们在写SQL的时候如果对索引字段使用函数炒作,则导致该SQL不走索引扫描查询,导致SQL性能下降。 SQL如下: SELECT ...
  • SQL索引工作原理

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

    千次阅读 2020-02-23 20:36:10
    索引作为SQL优化查询的一种方法,它可以很好的帮助我们加快数据的查询速度,那为什么我们不去正确的使用它呢? 一、什么是索引索引是一种特殊的查询表,数据库搜索引擎可以使用它加速数据检索。它们也组织...
  • 图解SSIS自动维护SQL索引

    千次阅读 2008-11-28 09:57:00
    先前我写过一篇介绍sql索引碎片的例子,参考http://blog.csdn.net/jinjazz/archive/2008/06/25/2585493.aspx现在介绍一个实施的办法,在sql2005中可以用ssis来部署一个维护索引的作业包,并通过sqlserver代理来定期...
  • MS SQL 索引

    2010-12-15 15:04:00
    本文详细介绍MS SQL入门基础:创建索引  8.2.1 用CREATE INDEX 命令创建索引  CREATE INDEX 既可以创建一个可改变表的物理顺序的簇索引,也可以创建提高查询性能的非簇索引。其语法如下:  CREATE ...
  • 怎样才能充分利用SQL索引

    千次阅读 2009-01-23 13:00:00
    怎样才能充分利用SQL索引 背景:目前WEB的普及太快,很多网站都会因为大流量的数据而发生服务器习惯性死机,一个查询语句只能适用于一定的网络环境.没有优化的查询当遇上大数据量时就不适用了. 本文主旨:讨论什么情况...
  • Oracle PLSQL 里的索引记得是这样的:/*++index(index_name)*/ Select /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS Where SEX='M'; SQL SERVER T-SQL里却是...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 66,261
精华内容 26,504
关键字:

sql索引