索引:
1.所有出现字段的地方都可以用到索引,包括 select后,where后,group by后,order by 后。
2.数据结构为b+树,对比平衡树:b+树深度浅(一般不会大于4层)查询速度更快(索引一个根节一页,根节点上数据量(索引字段)越小,子结点就越多,树就越矮,io越低)
对比 b树:
- B树所有节点都带有数据,每一层可存放的索引少于B+树,导致需要更多层的中间节点。数据量大的的时候查询更慢。
- B+树所有数据在叶子节点,并且叶子节点的数据有指针指向下一条数据,所以当查询范围数据是,B+树更快。
3.分为聚集索引和非聚集索引
聚集索引
1)聚集索引:一张表只有一个聚集索引,sql server默认将主键设置为聚集索引
2)表的数据存放在聚集索引的叶子节点,因此说,数据的存放顺序和聚集索引的顺序一致。
非聚集索引
- 非聚集索引可以建多个
- 非聚集索引的叶子节点没有数据,它只存放索引和数据位置(数据在聚集索引中的位置),所以查找顺序,找出非聚集索引的叶子节点->去聚集索引走一遍。但如果语句全部处于非聚集索引包括的字段内,那么就不用再去查询聚集索引。
- (猜想)如果没有聚集索引,就存放数据在磁盘位置的指针
4.explan执行计划
主要类型:
- 表扫描:扫描全表,直接去数据页查找,花费最大,及其不建议
- 索引扫描:遍历索引,比表扫描快
- 索引查找:精确查找索引中的值,最快
- 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个字符,如果超出这个数,数据将溢出占用更多页,导致查询代价增加