索引

转自http://secret330.blog.163.com/blog/static/119558358201042843744614/?fromdm&fromSearch&isFromSearchEngine=yes

设计，创建和维护索引
1  索引的功能：
              1 提过数据库的访问速度
              2 确保数据的唯一性


2 索引的概念:
             2.1  那些数据访问操作可以利于索引提高处理速度？？
                     1 查询操作中的where语句的数据提取
                     2 查询操作中的order by 语句的数据排序
                     3 group by 语句的数据分组
                     4 连接表
                     5 提高更新和删除数据记录的处理速度
             
2.2  SQL server 如何判断是否要使用索引？？
                    1 先检查是否存在根据适当字段创建的索引，确认该索引是否有助于本次数据访问操作
                    2 SQL server会从根页开始，沿着索引树状结构寻找查询所要求的数据记录
                     3 将符合查询条件的数据记录提取出来


3 索引的设计原则（需要了解一些数据库的物理存储结构的知识）
              3.1  应该只为下列字段创建索引：
                     1 经常被用来搜索数据记录的字段
                 Ps：覆盖查询所提高的执行速度最显著
               什么是覆盖查询：
                       如果你为多个字段的组合创建一个索引，而某个查询只引用该索引的组合键中的某些字段，则该查询就是覆盖查询
原因：覆盖查询能将访问速度提高到最高点,，主要是因为查询所要提取的所有数据均位于索引本身的索引页中，不需要引用表所在的数据页，因此能有效的降低整体磁盘的输入输出操作而提高访问速度。
                     2 primary key 约束所定义的作为主键的字段（该索引是自动创建的）
                     3 应用unique 约束的字段（该索引是自动创建的）
                     4 foreign key 约束所定义的作为外键的字段
                     5 在查询中用来连接表的字段
                     6 经常用来作为排序基准的字段
                     Ps：除上所述字段外的所有字段都不应该为塔创建索引
SQL SERVER 不允许text,  ntetx,  image, varchar(max),  nvarchar(max),  varbinary(max)与xml这七种LOB大型对象数据类型的字段指定为键的字段


              3.2  为什么不能随意选择某个字段创建索引？？
                     1 索引会占用磁盘空间，创建不必要的索引只会形成浪费
                     2 索引必须与表中的数据记录确实对应才能够发挥作用
                     3 索引创建后需要维护，这些都需要时间和资源
                     4 尽量不要为数据内容重复率很高的字段创建索引，它所带来的效益很少
                     5 数据记录越多，索引提高数据访问的效率的幅度就越明显
4 索引类型
              1 分类：按存储结构分：聚集索引  ，  非聚集索引
                        按数据唯一性：唯一索引  ，  非唯一索引
                 按键列个数分：单列索引  ，  多列索引
              用来创建索引的字段称为键列，而字段在索引中的数据称为键值
              2 分别介绍：
                2.1 聚集索引与非聚集索引
                      适合使用聚集索引的情况：
                            1 如果某字段所包含的有差别的数据的数目有限，则非常适合为该字段创建聚集索引
                            2 使用betwwen，>,<,>=与<=等运算符返回介于特定范围的数据记录的查询
                            3 按照特定次序访问的字段
                            4 返回大量结果集的查询
                            5 经常用于查询的连接条件或group by语句的字段
                            6 使用聚集索引搜索为一键列的速度一非常快
                           
               2.2 创建聚集索引要注意：                         
                            1聚集索引可以是唯一索引或非唯一索引
                            2每个表最多只能有一个聚集索引
                            3 聚集索引的大小 平均是表大小的5%
4 虽然SQL SERVER 允许为单一字段或多个字段的组合创建聚集索引，但是聚集索引的键列的数目越少越好
                            5 如果某个字段的内容经常变动，则非常不适合未该字段创建聚集索引
6 覆盖查询不适合使用聚集索引，因为搜索键中的字段数目越多，键列中的数据被修改的几率就越大，而这会导致额外的I/O


2.3 非聚集索引
              适合使用非聚集索引的情况：
              1 如果某个字段所包含的有差别的数据的数目非常多，则非常适合为该字段创建非聚集索引
              2 返回的结果集数据量不大的查询
3 如果某个字段经常用于查询的搜索条件（where语句）并要求其内容要符合特定的值，则非常适合为该字段创建非聚集索引
4 不常添加或修改数据且包含大量数据表
5 覆盖查询
2.4 创建非聚集索引要注意：
              1 每个表最多能有249个非聚集索引
              2 当针对表执行下列操作时，SQL SERVER 会自动重建表中所有现存的非聚集索引：
                            ~ 将表的聚集索引删除
                            ~ 为表创建一个聚集索引
                            ~ 更改聚集索引的键列
3 由于创建聚集索引会使表中所有现存的非聚集索引重建，因此，请在闯将任何非聚集索引前，先创建聚集索引
4 非聚集索引可以是唯一索引或非唯一索引
5 当创建unique约束是，SQL SERVER 会自动创建唯一的非聚集索引




2.5 唯一索引和非唯一索引
1 唯一索引或非唯一索引都能提高数据查询的速度，但是唯一索引能够进一步确保数据的唯一性
2 你可以为某一个字段和多个字段的组合创建唯一索引，但是该字段或多个字段的组合的值必须能够唯一识别表的每条数据记录，（多个字段组合情况下：个别字段中所存储的数据是允许重复的，但字段组合后的值必须是唯一的）     
3 null值也会视为是重复的
4 是否忽略重复的键值


2.6 单列索引和多列索引
    创建多列索引的情况：
       1 当两个或两个以上的字段组合在一起为最佳的搜索键值是，非常适合为这些字段的组合创建一个多列索引
       2 当查询所引用的字段均是索引的键列时，应该为这些字段的组合创建一个多列索引（覆盖查询最典型）
3 当某个字段的大小超过900个子节时，应该在使用create index表达式创建索引时，加上include参数将这个字段加入键值
创建多列索引需要注意：
1 你最多可以为16个字段的组合创建一个多列索引，而且这些字段的总长度不能超过900个字节
2 多列索引的哥哥字段必须来自同一个表
3 在定义多列索引时，识别度高的字段或是能返回较低百分比的数据记录的字段应该放在前面
4 查询的where语句务必引用多列索引的第一个字段，才能让查询优化器（query optimizer）使用该多列索引
5 既能提高查询速度又能减少表的索引数目是使用多列索引的最高境界


2.7 填充因子
    1当针对有聚集索引或非聚集索引的表执行insert或update表达式时，可以通过设置填充因子来优化其执行效率
      2 SQL SERVER 的索引是以平衡树的形式来实施的，即采用B-Tree结构：
        索引只有一个根页（Root Page），SQL SERVER以根页作为其遍历索引的起始点。在索引树中，所有位于叶级之上的索引级（包括跟页在内）称为非叶级（Non-Leaf Level）。叶级（Leaf-Level）则是索引结构的最底层，包含能对应数据页中的数据记录的键值，或是包含完整数据记录的数据页。
    3“页拆分”（page split）-----会拖慢执行的效率
        可以在创建索引时，通过指定一个填充因子来要求在索引的每个叶级页留出特定的百分比的可用空间，以便存储将来添加的键值或数据记录。


2.8 创建索引
1  在SQL Server Managenebt Studio中为表创建索引
2  使用表达式create index
        create [unique] [clustered] [nonclustered]  唯一索引  聚集索引  非聚集索引
           index index_name  指定索引的名称
               on [datebase_name.[schema_name]. | schema_name]
                      table_or_view_name         //表，视图或
                      (colum [asc | desc] [,…n])      键列及其排序方式，默认asc        
               [include(column_name[,…n])]   是否要在非聚集索引叶级中加入非键列
               [with
                      (pad_index={on | off }) 设置填充因子时开启on，默认为off
                      | fillfactor = fillfactor  指定填充因子
                      | sort_in_tempdb ={ on | off }  是否要将排序结果存储在tempdb
                      | ignore_dup_key ={ on | off }  希望唯一索引是否忽略重复的键值
                      | statistics_norecompute ={ on | off }  是否重新计算索引统计信息
                      | drop_existing ={ on | off }  以表达式的方式启用索引
                      | online ={ on | off }  创建索引时，是否可以访问表
                      | allow_row_locks ={ on | off }  是否允许行锁定
                      | allow_page_locks={ on | off }  是否允许页锁定
                      | maxdop=max_degree_of­_parallelism}  指定使用多个cpu
                      [ ,…n ] ) ]
               [ on { filegroup | “default”}] [ ; ]  存储索引得文件组


2.9 禁用索引
    当SQL Server在安装Service Pack是会自动禁用索引。除非遇到下列情况，否则很少手动禁用索引：
       1 出现代码为823或824的磁盘I/O错误
       解决：先禁用索引，然后使用dbcc checkdb表达式修复页面上的磁盘错误
当sql server 返回某个超过1000条数据记录，出现代码824的磁盘错误，这些页将标示成疑似损毁，但事实上这些页是好的，所以要先禁用索引，然后删除或修改suspect_page表的记录
       2 重新创建非聚集索引：磁盘空间问题（理解）
         禁用索引：alter index
              alter index index_name
              on [ datebase_name.[ schema_name] . | schema_name. ]
                table_or_view_name
              disable
           [ ; ]  
       3 禁用索引要注意：
              1 任何类型的索引都可以禁用
              2 你必须对禁用的表或视图拥有alter权限
3 如果禁用的是唯一索引，primary key约束，unique约束与所有引用该索引字段的foreign key约束都会随之禁用
4 如果禁用的是聚集索引，与该聚集索引相关联的非聚集索引以及foreign key约束也会随之禁用
  alter index ix_电话 on 客户 disable；
   select name as 索引名称，is_disabled as 禁用索引
       from sys.indexes where name = ‘ix_电话’；
   alter index  ix_电话 on 客户 rebuild;
  disable ， rebuild  禁用，启用索引


2.10  删除索引
        若不再需要使用某个索引，或是发现设计错误，应该立即将它删除以避免占用磁盘空间并拖慢执行效率
        删除方式：
                     1 可视化操作
                     2 表达式操作
                     drop index
                            {
                            Index_name
                            on [ datebase_name. [ schema_name ]. | schema_name. ]
                                   table_or_view_name }
                            [ , … n] [ ; ]
                     e.g:  drop index
                               ix_电话 on 客户 ，
                               ix_姓名雇用日期电话号码 on 人事；
                            如果要删除一个聚集索引，还可以加上如下所示语法:
                            With ( {
                                   maxdop = max_degree_of_parallelism
                                   | online = { on | off }
                                   | move to { filegroup_name | “default”}  }
                              [ , … n ] )
              删除索引要注意：
              1 删除一个索引会腾出它原先在数据库中所占用的空间，腾出的空间可被数据库中的任何对象使用
2 删除一个聚集索引会花费较长的时间，因为当表的聚集索引被删除，该表的所有非聚集索引都必须重建一次
3 你不能直接删除primary key或uinque约束的索引，如果你尝试这样做，将会出现错误（预期相关的索引会自动删除）
4 只有对表或视图拥有alter权限才可以删除表或视图的索引
5 当你删除一个表，该表的所有索引会自动删除
6 drop index表达式不适用与系统表