目录
常见问题
➢为什么要使用索引
为了避免全表扫描,提高查询数据的效率。
数据库存储的最小单位是块或页,由多行记录组成的,例如一个表就是多个块或者多个页。
全表扫描就是把这些块或页都加载进来,然后每个块或者页轮询,如果数据量较大,效率就会变得很慢。
就像一本字典从第一页开始,一页一页的查找数据,效率很低。
而索引就相当于通过字典的拼音、部首等进行一层又一层有条理的查询。
而这些部首,拼音等就相当于索引。
➢什么样的信息能成为索引
主键、唯一键以及普通键等
只要是能让数据具备一定区分性字段,都能成为索引。
➢索引的数据结构
二叉查找树
查询效率O(logn)
缺陷:可能退化为链表,查找复杂度为O(n)
B树
➢根节点至少包括两个孩子
➢树中每个节点最多含有m个孩子( m>=2 )
➢除根节点和叶节点外,其他每个节点至少有ceil(m/2)个孩子
➢所有叶子节点都位于同一层
B+树(MySQL)
➢非叶子节点的子树指针与关键字个数相同
➢非叶子节点的子树指针P[i] ,指向关键字值[K[i], K[i+ 1])的子树
➢非叶子节点仅用来索引,数据都保存在叶子节点中
➢所有叶子节点均有一个链指针指向下一个叶子结点
B+树适合做索引的缘由:
1. B+树的磁盘读写代价更低(程序运行,往往最耗时的操作就是IO,如果IO的次数越少,那么运行也就越快,代价也就越低,B+树内部结构不存放数据,只存放索引,因此其内部节点比B树更小)
2. B+树的查询效率更加稳定,数据存放在叶子节点中,也就意味着每次查询都需要经过从根节点到叶节点的查询路径,时间复杂度味为O(logn),比较稳定
3. B+树更有利于对数据库的扫描(因为数据只存放在叶子节点,而且有顺序,所以更好的查询数据范围)
哈希
Hash缺点
1、只满足=,IN不能范围查询
2、不能利用部分索引键做查询
3、不能被用来避免数据的排序(因为hash索引中存放的是hash运算之后的值,hash值大小关系不一定和运算前的键值完全一[不同key可能相同hash(key)],数据库无法利用索引的数据来避免任何排序运算)
4、不能避免表扫描([不同key可能相同hash(key)],也就是说索引可能对应多个记录。这些记录仍然需要进行扫描)
5、遇到大量Hash值相等的情况后性能不一定比B-Tree索引高(极端情况所有key都对应同一个hash(key)就变成线性的了)
➢密集索引和稀疏索引的区别
密集索引:每一条行记录都对应索引叶结点的一个索引项(关键字—指针)
稀疏索引:只有部分行记录对应索引项
聚集还是非聚集指的是B+Tree 的叶结点存储数据还是指针
MyISAM索引和数据分离,使用的是非聚集索引
InnoDB数据文件就是索引文件,主键索引就是聚集索引
衍生出来的问题,以mysql为例
➢如何定位并优化慢查询Sq|
大致思路:
1、根据慢日志定位慢查询SQL
2、使用explain分析SQL(主要看type、extra)
3、修改SQL或者尽量让SQL去走索引
➢联合索引的最左匹配原则的成因
联合索引
(a,b)当做索引时,如果同时查询where a='' and b='' 或者 where a='' 都会使用这个索引,但是只查where b='' 不会用这个索引。
原则:一直向右匹配直到遇到范围查询。
原因:索引保存是按照联合索引的顺序来,先排a再排b
➢索引是建立得越多越好吗
1、数据量小的表不需要建立索引,建立会增加额外的索引开销(好比只有两页的宣传手册,谁还会去建立目录?)
2、数据变更需要维护索引,因此更多的索意味着更多的维护成本
3、更多的索引意味着也需要更多的空间