SqlServer思维导图

这是一篇关于SqlServer的思维导图，讲述了如何建立高性能索引、如何确认索引是否命中、复合索引的存储和命中原理。

Sql Server

• 如何建立高性能索引
  • 索引是什么？
    • • 索引是提高查询性能的一个重要工具，索引就是把查询语句所需要的少量数据添加到索引分页中，这样访问数据时只要访问少数索引的分页就可以。但是索引对于提高查询性能也不是万能的，也不是建立越多的索引就越好。索引建少了，用WHERE子句找数据效率低，不利于查找数据。索引建多了，不利于新增、修改和删除等操作，因为做这些操作时，SQL SERVER除了要更新数据表本身，还要连带地立即更新所有的相关索引，而且过多的索引也会浪费硬盘空间。因此要建得恰到好处
  • 索引的基本目的
    • 索引的基本目的是在大量数据中找寻少量数据
      • 你可以想像一下，若一本书有700页，就像数据表有700个数据页，而索引却有600个索引页，你会想用索引来查询书籍的内容吗？
    • 索引字段的值重复性越低越好
      • 假设
        • 假设书籍中如“的”“了”这些在文章中重复性极高的字，每页都有一大堆，你会先翻索引页某个位置有“的”，翻回该页读取了“的”之后，再索引看下一个“的”，结果是在先前同一页的不同位置，又翻回书籍原页查看下一个“的”。
      • 那么怎么理解索引是从大量数据中寻找少量数据呢？
        • • B-tree（多路搜索树，并不是二叉的）是一种常见的数据结构。使用B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。按照翻译，B 通常认为是Balance的简称。这个数据结构一般用于数据库的索引，综合效率较高。
  • 什么是索引？
    • 聚集索引（clustered index，也称聚类索引、簇集索引）
      • • 新华字典的正文本身就是一个聚集索引。比如，我们要查“按”字，就会很自然地翻开字典的前几页，因为“按”的拼音是“an”，而按照拼音排序的新华字典是以英文字母“a”开头并以“z”结尾的，那么“按”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明新华字典中没有这个字；同样的，如果查“招”字，那也会将新华字典翻到最后部分，因为“招”的拼音是“zhao”。也就是说，新华字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
    • 非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）
      • • 如果您碰到一个不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63 页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后 再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。
  • 什么场景下建立索引？
    • 创建索引的规则：
      • 1、表的主键、外键必须有索引； 2、数据量超过300的表应该有索引； 3、经常与其他表进行连接的表，在连接字段上应该建立索引； 4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 5、索引应该建在选择性高的字段上； 6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： A、正确选择复合索引中的主列字段，一般是选择性较好的字段； B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引； C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引； D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段； E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引； 8、频繁进行数据操作的表，不要建立太多的索引； 9、删除无用的索引，避免对执行计划造成负面影响；
    • 总结：
      • 一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。 因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也 会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性 能，特别是对频繁更新的表来说，负面影响更大
  • 索引的建立原则
    • 选择性
      • 选择性表示符合你查询条件的记录占总记录的百分比，也就是 选择性=符合查询条件的记录数量/总记录数量 这个值越小越好，越小代表选择越高，越适合采用索引
    • 数据密度
      • 数据密度是指键值惟一的记录条数分之一，也就是 数据密度=1/键值惟一的记录数量
        • • • 当数据密度越小，也就是惟一性越高时，代表该字段越适合建立索引，因为当总数据条数乘上该密度值，就是一般平均查询到的记录数字。
    • 数据分布
      • 数据分布代表多条数据记录组成的方式，与密度的概念有关。它代表数据记录是平均散布在一段范围内，还是集中在部分区块
    • 索引的有效性
  • 总结
    • • 最后再次重申一下，“水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。 　　所以说，我们要建立一个“适当”的索引体系，特别是对聚集索引的创建，更应精益求精，以使您的数据库能得到高性能的发挥。 　　因为非聚集索引需要在非聚集索引的B树中找到每一行的指针，再去其所在表上找数据，性能因此会大打折扣，有时甚至不如不加非聚集索引。
• 如何确认索引是否命中
  • Table Scan
    • 遍历整个表，查找所有匹配的记录行。这个操作将会一行一行的检查，当然，效率也是最差的。
      • 子主题
  • Index Scan
    • 根据索引，从表中过滤出来一部分记录，再查找所有匹配的记录行，显然比第一种方式的查找范围要小，因此比【Table Scan】要快。
  • Index Seek
    • 根据索引，定位（获取）记录的存放位置，然后取得记录，因此，比起前二种方式会更快。一般来说在执行计划中出现了Index Seek的步骤，我们就认为索引命中了
  • Clustered Index Scan
    • 和【Table Scan】一样。注意：不要以为这里有个Index，就认为不一样了。 其实它的意思是说：按聚集索引来逐行扫描每一行记录，因为记录就是按聚集索引来顺序存放的。 而【Table Scan】只是说：要扫描的表没有聚集索引而已，因此这二个操作本质上也是一样的。
  • Clustered Index Seek
    • 直接根据聚集索引获取记录，最快！
• 复合（组合）索引的存储和命中原理
  • 工作原理
    • 最左开始组合，只要包含第一列的查询都会用到该组合索引，相当于一个按照姓氏—>名字—>电话簿
  • 联合索引和普通索引区别
    • 单个索引
      • 一个一个起作用，也就是说有三个单个索引，哪个条件查询在前哪个起作用，其他不起作用。
    • 联合索引
      • 联合索引中列的顺序非常重要，从左原则。a,ab,ba,abc。Or 不会使用联合索引
  • 匹配原则
    • 经常用的列优先（最左匹配原则） 离散度高的列优先（离散度高原则） 宽度小的列优先（最少空间原则）
      • • 列的离散性计算：count（distinct col）/ count(col) 例如： id列一共9列都不重复 9/9 = 1 性别列一共9列只有（男或者女）两列 2/9 约等于0.2 离散性越高选择性越大
  • 结论
    • 联合索引比单个索引更适合，因为索引占用一定磁盘空间，也就说明有一定的开销，如果多个单个索引比较多，那么多资源的浪费也比较多，联合索引相当于对多个列建索引，并且只建一次，and条件下非常适合。