知识笔记--sql高级3.B/B+树索引
1. 评价条件
一般使用磁盘I/O次数评价索引结构的优劣
2. B树分析
- 对于B树,检索一次最多需要访问h(树的高度)个结点。数据库系统的设计者巧妙利用了磁盘预读原理,将一个结点的大小设为等于一个页,这样每个结点只需要一次I/O就可以完全载入。
为了达到这个目的,在实际实现B-Tree还需要使用如下技巧:
每次新建节点时,直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,就实现了一个node只需一次I/O。
- B树中一次检索最多需要h-1次I/O(根节点常驻内存中)。
一般实际应用中,出度d是非常大的数字,通常超过100,因此h非常小(通常不超过3) - 综上所述,用B-Tree作为索引结构效率是非常高的。
3. B+树适合外存索引的原因
原因和内节点出度d有关。d越大索引的性能越好,而出度的上限取决于节点内key和data的大小:
由于B+树内结点去掉了data域,因此可以拥有更大的出度,拥有更好的性能。
B+树的索引id为什么设置为自增有益于索引维护?
可以避免分裂和页合并
4. MyISAM索引实现(使用B+树作为索引结构)
叶节点的data域存放的是数据记录的地址。
这里设表一共有三列,假设我们以Col1为主键,则上图是一个MyISAM表的主索引示意。
可以看出MyISM的索引文件仅仅保存数据记录的地址。
在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:
同样也是一颗B+Tree,data域保存数据记录的地址。当搜索时,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。
5. InnoDB索引实现
虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。
1. 区别一:InnoDB的数据文件本身就是索引文件。
从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。
而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。
这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
上图是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。
因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
2. 区别二:InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。例如下图为定义在Col3上的一个辅助索引
这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。
用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。
INNODB的二级索引与主键索引有很大的不同。InnoDB的二级索引的叶子包含主键值,而不是行指针(row pointers),这减小了移动数据或者数据页面分裂时维护二级索引的开销,因为InnoDB不需要更新索引的行指针。
6. 聚簇索引和非聚簇索引的区别
聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。
1. InnoDB按聚簇索引的形式存储数据,MyISAM按照非聚簇索引的形式存储数据
2. 回表
3.哪些情况需要创建索引?
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段,外键关系建立索引
- 单键/组合索引的选择问题
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
4.哪些情况不需要创建索引?
- 表记录太少
- 经常增删改的表
原因:提高了查询速度,同时却会降低更新表的速度。 如对表进行增、删和改时,mysql不仅要保存数据,还要保存一下索引文件。 - where条件用不到的字段不创建索引
- 数据重复且分布平均的表字段不创建索引
7. 索引性能分析
mysql常见瓶颈:
1.CPU---sql中对大量数据进行比较、关联、排序分组
2.IO---(1)实例内存满足不了缓存数据或排序等需要导致产生了大量物理IO (2)查询执行效率低,扫描过多数据行。
3.锁---(1)不适宜的锁的设置,导致线程阻塞,性能下降 (2)死锁,线程之间交叉调用资源,导致死锁,程序卡住。
4.服务器硬件的性能瓶颈:top,free,iostat,vmstat来查看系统的性能状态
本文来自博客园,作者:jsqup,转载请注明原文链接:https://www.cnblogs.com/jsqup/p/16083911.html