1.为什么索引使用B+树而不使用其他的一些数据结构?如:二叉树,红黑色。hash算法在什么场景下适用索引。
1.二叉树:数据结构在数据递增时深度会逐渐增加,在一些情况下二叉树会变成链表。
2.红黑树:红黑色虽然是平衡二叉树。但在数据量太大时,会出现深度太深的情况。
mysql索引查询时,是按照每一层一次IO读取的方式进行的,当树的深度太深时,假如需要查询的数据在最后一行那么IO读取的次数会很大,性能会非常的低。
我们可以简单计算一下,假设数据库表的数据量为2千万条深度多少 ,我们假设深度为H
根据计算公式:2^0+2^1+2^2+.........+2^n=2千万
2千万=2^(H+1)-2
粗略计算一下大概 H=21。假设查询数据在叶子节点要IO 查询21次,所以性能非常差
3.所用 hash算法:hash算法做索引,会对每一条hash对应数据地址,查询是非常快的。但是使用hash做范围查找和联合索引的情况下索引就会完全失效,性能下降非常快。
4.使用B+树,B+树数据结构特点:
注:为什么不使用B树,因为B树结构特点(节点不重复,不冗余),每个节点都存放数据,导致节点中索引存放少,因为每个节点设置是16K大小。
有k个子树的中间节点包含有k个元素(B树中是k-1个元素),每个元素不保存数据,只用来索引,所有数据都保存在叶子节点。
所有的叶子结点中包含了全部元素的信息,及指向含这些元素记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。
所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素
5.索引使用B+树时的元素存放:
mysql默认设置的每个节点为16K,所以每次IO读盘为16K,当然可以自己设置。
计算每个节点存放元素个数,按照每个元素大概8B,指针大小大概4B。每个元素14B大小
每个节 点存放元素数:16k/14B =1170个。
叶子节点存放数据或者数据地址,大小为1k,叶子节点存放元素为16k/1k=16个
假设存放3层树,计算存放数据:1170(第一层节点)*1170(第二层节点)*16=2100万数据。
B+树三层即可存放这么多数据,按照Mysql数据库设计原则,当单表数据超过2千万即可分库分表了
所以查询一次数据只需要2次IO操作即可,第一层根数据可以常驻内存。性能是非常高的。
6:常用引擎myisam和InnoDB对比:
myisam :首先,myisam不支持事物操作
存在磁盘库中表内容:
.frm文件:存放表结构。myd文件:存放数据。myi文件存放索引。
B+树索引叶子节点存放数据的地址指针。
InoDB :首选,支持事物操作
存在磁盘库中表内容:
.frm文件:存放表结构。 .idb文件存放索引+数据。
B+树叶子节点存放行数据。
7.索引的创建:
注:必须建主键,否则的话mysql会自己建一个rowid。
因为B+树叶子节点是按照顺序从左到右顺序排列,所以主键最好用整型自增,这样B+索引就不会再进行重新排序了,节约性能。
在使用索引查找时,会进行比较大小,整型数据大小比较比String类型要快的多,String要转换asci码,逐位比较,
UUID占用空间比Inter,id大。
索引每个表都使用主键索引,能更有效查询数据。
8.关于回表:
注:非主键索引叶子节点存放的是主键
个人通俗理解,当定义一个非主键索引,根据这个索引去查询数据时,当查询字段有不包含索引的字段,那么查询会根据索引查到主键后再此根据主键索引去查询数据。
比如:
一张表中存在索引 name
select name from table name="xxxxx" 没有回表,因为查询字段就在此索引上
select name,age from table name="xxxxx" 存在回表
9.关于复合索引
复合索引结构会复合最左原则:
按照索引最左表一个字段进行B+树排序,因此如果条件查询时第一个条件不是最左边索引(现在查询分析器的优化后可以不在条件最前面,但是需要有),索引将会失效。
也同样适用按照左边原则进行索引搜索,否则部分失效
比如索引:(a,b,c)
如果按照条件查询 a=?and b=? and c=? 索引会完全生效 首选会根据a到B+树中找到相应节点,然后根据b找到相应节点,在找c
如果使用 b=? and c=? 会完全失效,因为刚开始就不知道怎么找,B+树是按照a排序。
如果适应 a=? and c=?会导致部分失效,只有a起作用
可参考https://blog.csdn.net/BThinker/article/details/89149660