sql server高级知识梳理二:索引知识梳理

索引:

       1.所有出现字段的地方都可以用到索引，包括 select后，where后，group by后，order by 后。

2.数据结构为b+树，对比平衡树：b+树深度浅(一般不会大于4层)查询速度更快（索引一个根节一页，根节点上数据量（索引字段)越小，子结点就越多，树就越矮，io越低）

对比 b树：

1. B树所有节点都带有数据，每一层可存放的索引少于B+树，导致需要更多层的中间节点。数据量大的的时候查询更慢。
2.  B+树所有数据在叶子节点，并且叶子节点的数据有指针指向下一条数据，所以当查询范围数据是，B+树更快。

 

3.分为聚集索引和非聚集索引

聚集索引

1）聚集索引:一张表只有一个聚集索引，sql server默认将主键设置为聚集索引

2）表的数据存放在聚集索引的叶子节点，因此说，数据的存放顺序和聚集索引的顺序一致。

非聚集索引

1. 非聚集索引可以建多个
2. 非聚集索引的叶子节点没有数据，它只存放索引和数据位置(数据在聚集索引中的位置)，所以查找顺序，找出非聚集索引的叶子节点->去聚集索引走一遍。但如果语句全部处于非聚集索引包括的字段内，那么就不用再去查询聚集索引。
3. (猜想)如果没有聚集索引，就存放数据在磁盘位置的指针

4.explan执行计划

 主要类型:

1. 表扫描：扫描全表，直接去数据页查找，花费最大，及其不建议
2. 索引扫描:遍历索引，比表扫描快
3. 索引查找：精确查找索引中的值，最快
4. Key lookup: 部分字段使用了非聚集索引，但是还有字段未使用，需要再去聚集索引中查找

5.索引失效

       1）查询数据量实在太多（大于2%），，索引失效；需要分区分库分表

       2）(最常见)没有按复合索引的字段顺序使用复合索引，eg：index on(a,b,c)  select * from table where b =1,c=1 =>失效

3)使用了否定语句或者null判断：Eg !=, not in ,is null is not null

4）范围查询后无法接着用索引。Eg index on(a,b) select * from table where a>1 and b=1 =>失效

5）条件中使用了函数

6.索引的创建

1)最好索引中没有null值，会占用额外空间且重复可能有很多

2)尽量没有重复值，重复越多，查找出来的数据量就越多

3)尽量使用数字类型字段和字符数少的字段

7.行数据量控制

一行的数据一般都是存在一页,一页8K，包括页头，数据行和其他开销，数据行占用大约8000个字符，如果超出这个数，数据将溢出占用更多页，导致查询代价增加