MySQL支持多种索引,B+树索引、哈希索引、全文索引等,这里仅讨论B+树索引。
1. 索引本质
高效获取数据的数据结构
2. 索引作用
提高数据的查询速度
实现:B树或者B+树
优点:
1)唯一性索引,保证表中每一行数据的唯一性
2)加快数据的检索速度(最主要原因)
3)加速表和表之间的连接(参考完整性)
4)减少查询中分组和排序的时间
5)优化隐藏器,提高系统性能
缺点:
1)创建和维护索引耗费时间,随数据量增加而增加
2)索引本身占存储空间
3)插入和修改数据时要花费较多的时间(索引也需要动态维护)
二叉排序树的查找与树的深度n有关,降低深度n就能提高查找效率,有了多叉树的概念。结合平衡二叉树的思想,就有平衡多叉树结构,然后利用平衡多路查找算法,提高搜索效率。
在哪些列上适合建索引:
(1)经常需要搜索的列 (2)作为主键的列(唯一性) (3)经常用在连接的列上(外键) (4)经常需要根据范围进行搜索的列上(索引已排序) (5)经常需要排序的列上(利用索引排序) (6)经常使用where子句的列上,加快条件的判断速度
3. B树(B-树)平衡多路查找树
特性:类似平衡二叉树
1)根节点为空,或者有一个记录,两个孩子
2)每个节点有key和其他数据data(包含指向孩子的指针)
3)d是树的宽度,非叶子节点有[d/2,d-1]条记录,key值按大小排列,有[d/2+1,d]个孩子
4)第(n-1)个节点值<n个子树任一key<第n个节点值
5)叶子节点位于同一层,具有相同的深度
平衡二叉树是二叉排序树吗????
查找流程:根节点开始二分查找,找到返回节点数据,否则对相应区间(左或者右)继续二分查找。
根节点i>待查找元素 对第i个子树二分查找
根节点i<待查找元素 对第(i+1)个子树二分查找
直到找到或者找到空指针
时间复杂度:O(lgdn) 度为d,索引为n 效率比较高
MySQL中的索引采用B+树实现
B+树是B树的变种,但有一些区别:
1)每个节点的指针上限为2d而不是2d+1
2)非叶子节点不存储data,只存储key
3)叶子节点不存储指针
因为叶子节点和非叶子节点的区别,不是所有的节点拥有相同的域。但是B树的每个节点域和上限一致,即申请同等大小的空间。
带有顺序访问指针的B+树:在叶子节点增加一个指向相邻叶子节点的指针,目的:提高区间访问的性能。
4. 索引分类
A. 唯一索引:不允许其中任何两行具有相同索引值的索引
B. 主键索引:定义主键将自动创建主键索引(唯一索引的特例),要求主键中的每个值都唯一。
C. 聚集索引:表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。
D. 非聚集索引:表中行的物理顺序与键值的逻辑(索引)顺序不相同。
怎么评价索引结构的优劣?
磁盘I/O次数!!!
B-Tree中一次检索最多需要(h-1)次I/O(根节点常驻内存),渐进复杂度为O(h)=O(logdN) ,d非常大,h非常小
红黑树的I/O渐进复杂度也为O(h),h非常大,效率明显比B-Tree差很多
5. 索引中B树和B+树的区别
1) B树中每个节点包含了键值和键值对应的数据对象存放地址指针,所以成功搜索一个对象不用到达叶节点。
搜索方法:取根结点,在根结点所包含的关键字K1,…,kj查找给定的关键字(顺序查找或二分查找法),若找到等于给定值的关键字,则查找成功;否则,一定可以确定要查的关键字在某个Ki或Ki+1之间,于是取Pi所指的下一层索引节点块继续查找,直到找到,或指针Pi为空时查找失败。
2)B+树非叶节点中存放的关键码并不指示数据对象的地址指针,非叶节点只是索引部分。所有的叶节点在同一层上,包含了全部关键码和相应数据对象的存放地址指针,且叶节点按关键码从小到大顺序链接。
B+树有2个头指针,一个是树的根节点,一个是最小关键码的叶节点。
搜索方法:
一、按叶节点自己拉起的链表顺序搜索
二、从根节点开始搜索,和B树类似,不过如果非叶节点的关键码等于给定值,搜索并不停止,而是继续沿右指针,一直查到叶节点上的关键码。无论搜索是否成功,都将走完树的所有层。
不同之处:
(1) B树中同一键值不会出现多次,并且它有可能出现在叶结点,也有可能出现在非叶结点中。而B+树的键一定会出现在叶结点中,并且有可能在非叶结点中重复出现,以维持B+树的平衡。
(2)B树键位置不定,且在整个树结构中只出现一次,虽然可以节省存储空间,但在插入、删除操作复杂度明显增加。所以用B+树比较好。
(3)B树的查询效率与键在树中的位置有关,最大时间复杂度与B+树相同(在叶结点的时候),最小时间复杂度为1(在根结点的时候)。而B+树的时间复杂度是固定的。
6. 为什么索引不用B树?
结构上的区别:B+树在非叶子结点不保存数据,只在叶子结点保存。而B树在叶子结点和非叶子结点都会保存数据,导致查询,插入,删除时会增加磁盘IO的次数,性能不如B+树。