索引2

索引和全文索引：（索引的目的是提高性能）
－－－－－－－－－－－－－－－－－
    索引提供了一种基于一列或多列的值对表的数据行进行快速访问的方法。索引提供的是表为数据
    的逻辑顺序。规划合理的索引能够减少访问所需的时间，从而大大提高数据库的性能。
    
    －－－－－－－－－
    索引：索引为性能带来的好处却是有代价的。带索引的表在数据库中会占据更多的空间。另外，为了
          维护索引，对数据进行插入、更新、删除操作的命令所花费的时间会更长。在设计和创建索引
          时，应确保对性能的提高程序大于在存储空间和处理资源方面的代价。
    创建索引的原则： 
         * 该列频繁用于进行搜索
         × 该列用于对数据进行排序
         
         不要使用下面的列建立索引：
            * 列中仅包含几个不同的值
            × 表中仅包含几行。为小型表创建索引可能不太划算。
          
    创建索引：（只有表的所有者才能为表创建索引0）
        语法：
      create [unique] [clustered | nonclustered] index index_name
      on table_name (column_name[,column_name]....)
      [with
            [pad_index]   
            [[,]fillfactor=x]
            [[,]drop_existing]
      ]
      
      /*
         * unique 指定任意两行都不能具有相同的索引值。
         * [clustered][nonclustered] 是不同类型的索引。
         * pad_index指定索引的中间级中的各个页上的空白空间。pad_index选项仅在指定了fillfactor
           时才有用，这是因为pad_index需要使用fillfactor指定的百分比。
         * fillfactor指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比。
         * drop_existing删除任何相同名称的现有索引。
            */
            
     -----------------------
     索引类型：
        聚集索引：聚集索引确定表中数据的物理顺序。一个表只能有一个聚集索引，但该索引可以包含
                  多个列。
                  
                  每一个表都应有一个聚集索引，以加快数据检索过程。创建聚集索引时应注意以下几点： 
                     × 选择唯一值比例高的列。一般应选择主键列。
                     × 先创建聚集索引，再创建非聚集索引。
                     × 在创建聚集索引时使用fillfactor选项，以确保在插入新行时有足够的可用空间
                     × 在经常被使用联接或group by 子句的查询访问的列上创建聚集索引，一般来说，
                       这些是外键列。对order by 或group by 子句中指定的列进行索引，可以使sql
                       不必对数据进行排序，因为这些行已经排序，这样可以提高查询性能。
                     × 聚集索引不适用于频繁更改的列，因为这将导致整行移动（因为sql server必须）
                       按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务处理
                       系统中数据是容易丢失的。                        
                  －－－－－－－－－－－－
                  创建聚集索引：
                     create clustered 
                     index clindx_titleid on roysched(title_id)
                     
                     
                  
        非聚集索引：（指定表的逻辑顺序）
             一个表可以有多个非聚集索引，数据存储在一个位置，索引存储在另一个位置，索引中包含
             指向数据存储位置的指针。
             
             索引中的项按照索引键值的顺序存储，但表中信息顺序保持不变。非聚集索引中索引的逻辑
             顺序与各行在磁盘上的存储顺序即物理顺序并不匹配。
             
             创建非聚集索引：
                create nonclustered index nclindx_ordnum on sales(ord_num)
                
             在创建非聚集索引之前，应先了解数据是如何被访问的，可考虑将非聚集索引用于：
                × 包含大量重复值的列，如只有0或1,则大多数查询将不使用索引，因为此时表扫描
                  通常更有效。
                × 不返回大型结果集的查询
                × 返回精确匹配的查询的搜索条件（where 子句）中经常使用的列。
                × 经常需要联接和分组的决策支持系统应用程序。应联接和分组操作中使用的列上创建多个
                  非聚集索引，在任何外键列上创建一个聚集索引。
                × 在特定的查询中覆盖一个表中的所有列，这将完全消除对表或聚集索引的访问。
                
        填充因子：
            页拆分会降低性能并使表中的数据存储产生碎片：向一个已满的索引页添加某个新行时，sql
            把大约一半的行移到新页中以便为新行腾出空间，这种重组称为页拆分。
            
            使用sp_configure系统存储过程指定的服务器范围的默认填充因子，在大多数情况下都是最佳
            选择。
            
            指定一个不同于默认设置的填充因子会降低数据库的读取性能，而降低量与填充因子设置值成
            反比。例如，当填充因子的值为50%时，数据库的读取性能会降低两倍。
            
            填充因子只在创建索引时执行；索引创建后，发表中进行数据的添加、删除或更新时，不会保持
            填充因子。如果试图在数据页上保持额外的空间，则将有背于使用填充因子的本意，因为随着
            数据的输入，sql server必须在每个页上进行页拆分，以保持填充因子指定的空闲空间百分比。
            因此，如果表中的数据进行了较大的变动，添加了新数据，可以填充数据页的空闲空间。这种
            情况下，可以重新创建索引，重新指定填充因子，以重新分布数据。
            
            
       －－－－－－－－－－－－－－－－
       索引特性：
            将索引创建为唯一索引或组合索引可以进一步增强聚集索引和非聚集索引的功能。唯一索引不
            允许索引列中存在重复的值，组合索引则允许在创建索引时使用两列或更多的列。
            
            唯一索引：唯一索引可以确保索引列不包含重复的值。
                
                唯一索引示例：
         set nocount on
         use pubs
         --如果该表存在则删除
         if exists(select * from information_schema.tables 
                   where table_name='emp_pay')
             drop table emp_pay
         
         go
         use pubs
         --如果该索引存在则删除
         if exists(select name from sysindexes where name='employeeId_ind')
            drop index emp_pay.employeeID_ind
         go 
         use pubs
         go 
         --创建表
         create table emp_pay
         (
              employeeID int not null,
              base_pay money not null,
              commission decimal(2,2) not null
         )
         --插入数据
         insert emp_pay values(1,500, .10)
         insert emp_pay values(2,1000, .05)
         insert emp_pay values(3,800, .07)
         insert emp_pay values(5,1500, .03)
         insert emp_pay values(9,750, .06)
         go 
         set nocount off
         --
         create unique clustered index employeeID_ind
         on emp_pay(employeeID)
         go
         select * from emp_pay
         go  --查看索引信息
                  execute sp_helpindex emp_pay
                     
                注意：创建primary key 或unique约束会在表中指定的列上自动创建唯一索引
                
        －－－－－－－－－－－－－－－－－－－－
         组合索引：组合索引包含两个或更多为创建索引而组合在一起的列，最多可以组合16列
                   示例：
                      create unique clustered index upkcl_sales
                        on sales(stor_id,ord_num,title_id)
                
                创建组合索引的原则：
                   × 当需要频繁地将两个或多个列作为一个整体进行搜索时，可以创建组合索引。
                   × 创建组合索引时，先列出唯一性最好的列。
                   × 组合索引中列的顺序和数量会影响查询的性能。
         －－－－－－－－－－－－－－－－－－－－－－－－－－－－
         查看索引信息：execute sp_helpindex emp_pay
         
         -------------------------------------------------------
         使用索引：
               示例：select * from sales(index=nclindx_ordnum)
                     where ord_num='P3087a'
               注意：这样做将覆盖sql server作出的内部索引选择，确保使用指定的索引。但是
                     请注意，在多数情况下，查询优化器都能选择最佳方法来处理查询。
                     
                     
         －－－－－－－－－－－－－－－－－－－
         删除索引：
             示例：drop index table_name.index_name
                   drop index sales.nclindx_ordnum
             注意：drop index语句不适用于primary key 或unique约束创建的索引，drop index 
                  也不能用于删除系统表的索引。
         示例
         下例删除 authors 表内名为 au_id_ind 的索引。
         
         USE pubs
         IF EXISTS (SELECT name FROM sysindexes
                  WHERE name = 'au_id_ind')
            DROP INDEX authors.au_id_ind
         GO
         
      -----------------------------------
     全文搜索：全文索引必须在基表上定义，而不能在视图、系统表或临时表上定义。
            × 能唯一标识表中各行的列（主键或候选键），而且不允许null值。
            × 索引所覆盖的一个或多个字符串列。
            
            全文索引是用于执行两个transact-sql谓词的组件，以便根据全文检索条件对行进行
            测试；
               × contains
               * freetext
            transact-sql还包含两个返回符合全文条件的行集的函数：
               × containstable
               * freetexttable
           
     -------------------------
     使用全文索引：× contains 谓词 ，contains检索总是区分大小写。
             --简单的查询
             use northwind
             go
             select Description
             from categories
             where Description like '%bean curd%'
             go
             
             --或者，使用contains
             use northwind
             go 
             select Description
             from categories
             where contains(Description,'"bean curd"')   
                   
            × freetext谓词： 使用freetext搜索包含指定字符值的单词 
              --使用freetext搜索包含指定字符值的单词
              use Northwind 
              go 
              select CategoryName
              from Categories
              where freetext (Description,'sweetest candy bread and dry meat')
              go
              --在全文检索中使用变量
              use pubs
              go 
              declare @SearchWord varchar(30)
              set @SearchWord = 'Moon'
              select pr_info from pub_info 
              where freetext(pr_info,@SearchWord)
              
－－－－－－－－－－－－－－－－－－－－－－－－
作业：1、在titles表的title_id用pub_id列上创建一个名为nclindx_titlepub的非聚集索引，请确保索引
         页留有20%的空白空间，并且删除具有相同名称的现有索引。
         
     
        
      2、titles表的notes 列上已启用了全文索引。找出其注释中含有"technology" 和“bestseller”best
         这两个词语的title.
      use pubs 
      if exists(select [name] from sysindexes where [name]='nclindx_titlepub')
         drop index titles.nclindx_titlepub
      go
      create nonclustered index nclindx_titlepub
      on titles(title_id,pub_id)
      with fillfactor = 80
      go
      ------------------使用全文索引
      use pubs
      go 
      select * from titles 
      where freetext(notes,'technology bestseller')     
     
--------------------------------------
索引与全文索引上机练习：
     create nonclustered index RegionID_index
     on Territories(RegionID)
     
     go 
     select * from Territories
     go 
     -----------------使用指定的索引进行查询
     select * from Territories(index = RegionID_index)    
     
    
  -----------------------------------
   创建全文索引：
       可以在无结构文本上建立全文索引，以便能够在文本中搜索特定的条目。可以使用企业管理器中
       的全文索引向导创建全文索引。创建后，就可以对创建了索引的表发出全文查询。
       use pubs 
       if exists(select [name] from sysindexes where [name]='nclindx_titlepub')
          drop index titles.nclindx_titlepub
       go
       create nonclustered index nclindx_titlepub
       on titles(title_id,pub_id)
       with fillfactor = 80
       go
       ------------------使用全文索引
       ------------------未建立全文索引
       use pubs
       go 
       select * from titles 
       where freetext(notes,'latest')
       where freetext(notes,'technology and bestseller')
       
       --另一种方式 ，如果是短语则用双引号括起来。
       use pubs
       go 
       select * from titles 
       where contains(notes,'"The latest"')
       -------------------------------------------------------
       create nonclustered index RegionID_index
       on Territories(RegionID)
       
       go 
       select * from Territories
       go 
       -----------------使用指定的索引进行查询
       select * from Territories(index = RegionID_index)
       
       -----------------使用全文索引注意不同的使用方式
       use Northwind
       go
       select ProductName,QuantityPerUnit 
       from Products
       where contains(QuantityPerUnit,'"bottles" or "jars"')
       -------此两种方式输出内容相同，只不过使用方法不同而已
       go
       select ProductName,QuantityPerUnit
       from Products
       where freetext(QuantityPerUnit,'bottles or jars')       
        －－－－－－－－－－－－－
        上机部分作业：
               一、 使用企业管理器创建全文索引并填充
               二、使用查询分析器创建聚集索引
           use pubs
           go
           if exists(select name from sysindexes where name='stor_id_ind')
              drop index discounts.stor_id_ind
           go
           create clustered index stor_id_ind 
           on discounts(stor_id)                 
           
          三、创建非聚集索引，并设置填充因子
           use pubs
           go
           if exists(select name from sysindexes where name='emp_id_nonind')
               drop index employee.emp_id_nonind
           go
           create nonclustered index emp_id_nonind
           on employee(emp_id,pub_id)
           with fillfactor=75
           
       四、查看sales表中的全部索引
           execute sp_helpindex employee
           
       五、使用全文索引
           use pubs
           go
           select * from titles 
           where contains(notes,'"recipes" or "electronic"')
           
           -----或者
           use pubs
           go
           select * from titles 
           where contains(notes,'recipes or electronic')
           
    作业：1、
             use Northwind
         go
         if exists(select name from sysindexes where name='County_nonindex')
             drop index Suppliers.County_nonindex
         go
         create nonclustered index County_nonidex
         on Suppliers(Country,city)
         with fillfactor = 50
     
          2、使用指定的索引进行查询
         select * from Suppliers(index = County_nonidex) 
         where Country= 'France'