SQL索引使用原则

其实对于非专业的数据库操作人员来讲，例如软件开发人员，在很大程度上都搞不清楚数据库索引的一些基本知识，有些是知其一不知其二，或者是知其然不知其所以然。造成这种情况的主要原因我觉的是行业原因，有很多公司都有自己的DBA团队，他们会帮助你优化SQL，开发人员即使不懂优化问题也不大，所以开发人员对这方面也就不会下太多功夫去了解SQL优化，但如果公司没有这样的DBA呢，就只能靠程序员自己了。 最近突然想起前一阵和一朋友的聊天，当时他问我的问题是一个非常普通的问题：说说SQL聚集索引和非聚集索引的区别。
    
        大家可能认为这个问题难度不大,认为太熟悉了，也许不会感兴趣，但你真能说清楚吗？其实要想说明白这两者的差别也不是三两句就说的清的，那天我也是觉的这问题太泛了，就随便说了其中的两个区别：

        第一：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个,这个跟没问题没差别，一般人都知道。
        第二：聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续，这个大家也都知道。

        上面的两点从大的方面讲都是讲的通的，后面我们继续探讨，举一个实际点的例子，一个学生表student，里面是学生号id,学生姓名，学生所在城市ID，学生成绩(总分)，
        问：如果想按姓名查询，如何做优化？
        答：在姓名字段上建立索引。
        问：建立什么类型的索引？
        答：建立非聚集索引。
        问：如果想按学生的学分范围来查找呢，例如查找得分在60-90的？
        答：在学分字段上创建聚集索引？
        问：为什么？
        答：一般有范围查询的需求，可以考虑在此字段上创建聚集索引。
        问：学分有重复性，在学分字段上创建聚集索引能行吗？
        ....沉思，不能创建吗？之前的项目好像真这样做过
        答：应该可以吧。
        问：聚集索引的约束是什么？
        答：唯一性啊？
        问：既然是唯一性，那么学分字段上还能创建聚集索引吗？
        ....再次沉思，应该可以啊，但索引的约束又怎么说呢？
        答：应该可以的，以前用过。
       
        我自认为是对数据库索引知识有一定研究的，但可能是有两年没实际接触SQL的原因，一时还真想不出具有说服力的解释，朋友们看到这能解答我的问题吗？
       
        其实上面的我们需要搞清楚以下几个问题：

        第一：聚集索引的约束是唯一性，是否要求字段也是唯一的呢？
        分析：如果认为是的朋友，可能是受系统默认设置的影响，一般我们指定一个表的主键，如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引,SQL会默认在此字段上创建一个聚集索引，而主键都是唯一的，所以理所当然的认为创建聚集索引的字段也需要唯一。
        结论：聚集索引可以创建在任何一列你想创建的字段上，这是从理论上讲，实际情况并不能随便指定，否则在性能上会是恶梦。
       
        第二：为什么聚集索引可以创建在任何一列上，如果此表没有主键约束，即有可能存在重复行数据呢？
        粗一看，这还真是和聚集索引的约束相背，但实际情况真可以创建聚集索引，分析其原因是：如果未使用 UNIQUE 属性创建聚集索引，数据库引擎将向表自动添加一个四字节 uniqueifier 列。必要时，数据库引擎 将向行自动添加一个 uniqueifier 值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。
            
        第三：是不是聚集索引就一定要比非聚集索引性能优呢？
        如果想查询学分在60-90之间的学生的学分以及姓名，在学分上创建聚集索引是否是最优的呢？
        答：否。既然只输出两列，我们可以在学分以及学生姓名上创建联合非聚集索引，此时的索引就形成了覆盖索引，即索引所存储的内容就是最终输出的数据，这种索引在比以学分为聚集索引做查询性能更好。
       
        第四：在数据库中通过什么描述聚集索引与非聚集索引的？
        索引是通过二叉树的形式进行描述的，我们可以这样区分聚集与非聚集索引的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。
       
        第五：在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢？
        有了上面第四点的认识，我们分析这个问题就有把握了，在有主键的表中插入数据行，由于有主键唯一性的约束，所以需要保证插入的数据没有重复。我们来比较下主键为聚集索引和非聚集索引的查找情况:聚集索引由于索引叶节点就是数据页，所以如果想检查主键的唯一性，需要遍历所有数据节点才行，但非聚集索引不同，由于非聚集索引上已经包含了主键值，所以查找主键唯一性，只需要遍历所有的索引页就行，这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。
       
        好了，讲这这些，不知道大家是否真的了解SQL的聚焦索引，我也是数据库新手(从使用时间上来讲也不算新了，哈哈)，不专业，有什么不对的地方，希望大家批评指正，下篇我会分析一些数据库访问索引的情况，有图的情况下，也许看的更加明白。
        

       文中修改：本文说的B树是指平衡树，由于本人对算法了解不够，所以不对之处望大家多多指正，谢谢两位朋友的指正。

-*****************************************************************************************

 

上篇文章我粗略的总结了些SQL聚集索引与非聚集索引的区别，但看起来好像不太清晰，这篇我通过索引原理来再一次分析下。
     

      说明：下面所指的B树是指平衡树。
      索引是为检索而存在的,就是说索引并不是一个表必须的。表索引由多个页面组成，这些页面一起组成了一个树形结构,即我们通常说的B树， 首先来看下表索引的组成部分：
  
      根极节点,root,它指向另外两个页，把一个表的记录从逻辑上分成非叶级节点Non-Leaf Level(枝)，它指向了更加小的叶级节点Leaf Level(叶)。 根节点、非叶级节点和叶级节点都位于索引页中，统称为索引叶节点，属于索引页的范筹。这些“枝”、“叶”最终指向数据页Page。根级节点和叶级节点之间的叶又叫数据中间页。根节点对应了sysindexes表的Root字段，记载了非叶级节点的物理位置（即指针）；非叶级节点位于根节点和叶节点之间，记载了指向叶级节点的指针；而叶级节点则最终指向数据页，这就是最后的B树。   
  
      数据库是怎样访问表数据的：
  
     第一：没有创建任何索引的表。
     这种表我们称为堆表，因为所有的数据页都是无序的，杂乱无章的，在查询数据时，需要一条一条记录查询，有时第一条记录就能找到，最坏的情况是在最后一条记录中查找到,但是千万不要认为SQL此时查找到数据后会当成结果立即返回，SQL即使查找到了记录，也会将所有数据遍历一次，这能从最终的执行计划中得知，就是平时说的表扫描，对于没有索引的表也能查询，就是效率会特别低，如果数据量稍大的话。
  
     问题：SQL是如何得知表没有索引呢？
    SQL在接到查询请求的时候，会分析sysindexes表中索引标志符(INDID: Index ID)的字段的值，如果该值为0，表示这是一张数据表而不是索引表，SQL就会使用sysindexes表的另一个字段FirstIAM值中找到该表的IAM 页链也就是所有数据页集合。至于什么是IAM,大家可以网上搜索下。
  
     第二：访问创建有非聚集索引的表。
     非聚集索引可以建多个,形成B树结构，叶级节点不包含数据页，只包含索引行。如果表中只有非聚集索引，则每个索引行包含了非聚集索引键值以及行定位符（ROW ID,RID），他们指向具有该键值的数据行。RID由文件ID、页编号和在页中行的编号组成。当 INDID的值在2-250之间时，说明表中存在非聚集索引页。SQL调用ROOT字段的值指向非聚集索引B树的ROOT，查找与被查询最相近的值，根据这个值找到在非叶级节点中的页号，在叶级节点相应的页面中找到该值的RID，最后根据这个RID在Heap中定位所在的页和行并返回到查询端。
   上篇文章的cityid上建立了非聚集索引，执行Select * From student Where cityid=’0101’时，查询过程是：
   1:在sysindexes表查询INDID值为2,说明有非聚集索引；
   2:从根出发，在非叶级节点中定位最接近0101的值(枝节点)，查到其位于叶级页面的第n页；
   3:在叶级页面的第n页下搜寻0101的RID，其RID显示为N∶i∶j，表示cityid字段中名为0101的记录位于堆的第i页的第j行，N代表文件的ID值。
   4:在堆的第 i页第j行将该记录返回给客户端。

   下图可做参考：
                                 
  

    第三：访问创建有聚集索引的表。
    聚集索引中，数据所在的数据页是叶级，索引数据所在的索引页是非叶级。原理和上述非聚集索引的查询差不多，由于记录是按聚集索引键值进行排序，即聚集索引的索引键值也就是具体的数据页。这种情况比起非聚集索引要简单很多,因为比非聚集索引少了一层节点查询。
   上篇文章的username字段上建立了聚集索引，此时执行Select* From student Where username=’1’时，查询过程是：
   1:在sysindexes表查询INDID值为1，说明表中建立了聚集索；
   2:从根出发，在非叶级节点中定位最接近1的值(枝节点)，再查到其位于叶级页面的第n页；
   3:在叶级页面第n页下搜寻值为1的条目，而这一条目就是数据记录本身；
   4:将该记录返回客户端。
   下图可做参考：

                           

    第四：怎样访问既有聚集索引、又有非聚集索引的数据表：
    username字段上建立了聚集索引，cityid上建立了非聚集索引，当执行Select * From student Where cityid=’0101’时，查询过程是：
    1:在sysindexes表查询INDID值为2,说明有非聚集索引；
    2:从根出发，在cityid的非聚集索引的非叶级节点中定位最接近0101的条目；
    3:从上面条目下的叶级页面中查到0101的逻辑位置，是聚集索引的指针；
    4:根据指针所指示位置，进入位于username的聚集索引中的叶级页面中找到0101数据记录；
    5:将该记录返回客户端。
  
    通过上面数据库访问索引的原理，我们就很容易解释聚集索引与非聚集索引的区别了，原理都一样，关键看什么场合应用什么索引了,下一篇我来总结一些不同场合最适合采用什么样的索引，不对之外多多指点。

    注：此篇文章的图以及部分文字均来自网上。

 

 

 

 -*******************************************************************************************

前两篇文章我总结了一些SQL数据库索引的问题，这篇主要来分析下索引的优缼点，以及如何正确使用索引。
  
    索引的优点：这个显而易见，正确的索引会大大提高数据查询，对结果进行排序、分组的操作效率。
    索引的缺点:优点显而易见，同样缺点也是显而易见:
    1：创建索引需要额外的磁盘空间，索引最大一般为表大小的1.2倍左右。
    2：在表数据修改时，例如增加，删除，更新，都需要维护索引表，这是需要系统开销的。
    3：不合理的索引设计非但不能利于系统，反而会使系统性能下降。例如我们在一个创建有非聚集索引的列上做范围查询，此列的索引不会起到任何的优化效果，反而由于数据的修改而需要维护索引表,从而影响了对数据修改的性能。
  

     实际例子：还是拿前两篇文章的学生表来讲吧，要查询成绩在50分以上的学生信息select * from student where score>50。学生表包含了100000行记录，而且学分是随机生成的,这样从数据量以及数据分布上都有一定的保障。

     第一种情况：学生表有索引。
      1：存在聚集索引，但聚集索引不在学分上,这里只分析学分不是聚集索引的情况。
          (1):学分上没有索引。此时SQL会通过聚集索引来查找数据，这点估计大家都会知道。
         （2):学分上有索引。这种情况，SQL会使用上学分上的索引吗？这个问题估计不是每个人都能回答正确的。既然学分上有索引，而where中又有此列，理应使用了索引，但实际情况并没有使用索引。因为出现了范围查找，如果一个索引一个索引的比较，在性能上比起直接按聚集索引查找全部数据后再过滤来的差。那学分上的索引什么时候  SQL会优先考虑呢？当score指定为一个具体值时，就能使用学分索引查找了。从下图的SQL执行计划可以得知。

              

      2:不存在聚集索引。
          (1):在学分上没有索引，其它字段有索引，这种情况就会出现表扫描。
          (2):在学分上有索引，是否会按照学分上的索引进行查找呢？由于上面的表数据量也不少，一般会认为SQL不会采用表扫描，因为会查找全部记录，但实际情况表明SQL对于范围查询也行采用表扫描而不是按学生索引查询。我们也可以强制SQL按学分查询，于是有下面的SQL执行计划比较，我们可以清楚的看出，强制使用学分做为索引查询比表搜索的性能要差很多。

             

    第二种情况：学生表没有索引。这个情况没有分析的价值。

    
   什么字段不适合创建索引？
   1:不经常使用的列，这种索引带来缺点远大于带来的优点。
   2:逻辑性的字段，例如性别字段等等,匹配的记录太多，和表扫描比起来不相上下。
   3:字段内容特别大的字段，例如text等，这会大大增大索引所占用的空间以及索引更新时的速度。
  
   我们说SQL在维护索引时要消耗系统资源，那么SQL维护索引时究竟消耗了什么资源？会产生哪些问题？究竟怎样才能优化字段的索引？

   第一：当数据页达到了8K(数据页最大为8K) 容量，如此时发生插入或更新数据的操作，将导致页的分裂。
   1、聚集索引的情况下：聚集索引将被插入和更新的行指向特定的页，该页由聚集索引关键字决定；
   2、只有堆的情况下：有空间就可以插入新的行，对行数据的更新需要更多的空间，如果大于了当前页的可用空间，行就被移到新的页中，且在原位置留下一个转发指针，指向被移动的新行，如果具有转发指针的行又被移动了，那么原来的指针将重新指向新的位置；
   3、堆中有非聚集索引，尽管插入和更新操作，不会发生页分裂，但非聚集索引上仍然产生页分裂。
   总结：无论有无索引，很多数据将保留在老页面，其它将放入新页面，并且新页面可能被分配到任何可用的页，频繁页分裂，表会产生大量数据碎片，直接造成I/O 效率下降。
  
   引出问题：为什么数据库对于varchar最大值设置为8000,而不是10000呢？
   答：是由于数据页大小最大为8K。
  
   第二：针对上述索引可能造成的页分页的解决方案，填充因子。
   创建索引时，可以为索引指定一个填充因子，在索引的每个叶级页面上保留一定百分比的空间，将来数据可以进行扩充和减少页分裂。值从0到100的百分比数值，100 时表示将数据页填满。不对数据进行更改时(例如只读表中)才用此设置，实用价值不大。值越小则数据页上的空闲空间越大，可以减少在索引增长过程中进行页分裂，但需要占用更多的硬盘空间。填充因子也不能设置过小，过小会影响SQL的读取性能，因为填充因子造成数据页的增多。一般我们公司设置的填充因子是80。
  
   索引是否是一尘不变的？
   随着业务的变化，数据的变化，会发生有些索引的用处可能发生变化，例如：
   1:原来主要靠用户名搜索记录，现在业务更改为按用户所在城市搜索等等，此时我们需要即时变更表索引以适应新业务的变化,即数据和使用模式发生了大幅度变化。
   2:系统上线前不合理的索引，随着数据的增加，缺点越来越明显，此时需要调整索引。
   3:随着数据的增加，产生了越来越多的页分裂，导致索引性能越来越低。
  
   上面的几种情况，我们就需要选择重建索引来彻底解决问题。
  
   总结索引使用原则：
   1:不要索引数据量不大的表，对于小表来讲，表扫描的成本并不高。
   2:不要设置过多的索引，在没有聚集索引的表中，最大可以设置249个非聚集索引，过多的索引首先会带来更大的磁盘空间，而且在数据发生修改时，对索引的维护是特别消耗性能的。
   3:合理应用复合索引，有某些情况下可以考虑创建包含所有输出列的覆盖索引。
   4:对经常使用范围查询的字段，可能考虑聚集索引。
   5:避免对不常用的列，逻辑性列，大字段列创建索引。

 

   有说的不对的地方，欢迎大家指正。