索引是帮助MYsql高效获取数据的排好序的数据结构
为什么不用红黑树:高度不可控,数据量大的时候查找效率降低。(大量的磁盘查找)
索引的节点是放在磁盘上的,查找一次是和磁盘做了一次I/O交互,效率很低。树越高,效率越低。
优化方法:索引节点分配大一点,横向分配更多空间,分配更多的索引元素。横向索引越多,树越低。这也就是b树。
B tree
1:叶节点具有相同的深度,叶节点的指针为空
2:所有索引元素不重复
3:节点中的数据索引从左到右递增
4:data就是索引所在行的磁盘文件地址
b+ Tree结构
1:非叶子节点不储存数据,只存储索引(冗余),可以放更多的索引
2:叶子节点包含所以索引字段
3:叶子节点用指针连接,提高区间访问性能
4:叶子节点包含了整张表的所有索引元素,因为data挪到了叶子节点(减少了磁盘查找,比b树好的原因之一)
5:把每页的第一个索引元素提上去,作为分叶子节点,做成树结构
6.节点内部依次递增
7.叶子节点两端维护了两个指针,指向相邻叶子节点地址,这个特点支持了范围查找,hash就不行。
e.g.假如查找大于20的数据,找到根节点,然后顺藤摸瓜,把后面的数据全输出。b树就不行
b+树查找流程:
把根节点的所有元素load到内存里面(RAM),然后折半查找,内存中查找效率比磁盘高很多。
找到区间,根据区间指针找到子节点,再把子节点所有元素load到内存中。重复此操作到目标叶子节点具体数据为止。
思考:为什么不把所有索引放到根节点?
查找一个数据把所有索引加载到内存里费事费力。
mysql为此折中,把一页大小默认设置为16kb
b+树高度可控吗?
mysql 默认一页大小是16kb
如果按bigInt8B+6B的指针算,一页就是16kb/14b≈1170个索引。
叶子节点除外,因为包含了索引所在行的磁盘文件地址,也有可能是索引所在行的其他列数据。
假如叶子节点为1kb
那么假设b+树高度为三,他可以存储1170*1170*16=219,024,000个元素
生产中若根节点和其子节点常驻内存(咱们系统内存存了哪几个?),那么每次查找就只用做一次I/O交互
MYISAM 和 INNODB 都是形容表的
myisam表对应三个文件,
frm(frame):存储表结构
MYD(data):表数据 data
MYI(index):表索引 Index
MYI 中索引就是用b+树存储的。
模拟一下读数据过程:
先看查找字段是不是索引字段,是的话先从MYI b+树上快速定位,定位到数据所在行的磁盘文件地址,然后用这个地址去MYD文件中定位数据。
这就是非聚集索引,索引文件和数据文件是分离的。
innodb
innodb表对应两个文件
frm(frame):表结构
IBD(index,data)数据和索引,就是下图
表数据文件本身就是按b+树组织的一个索引结构文件
叶节点包含了完整的数据结构,也就是聚集索引。
叶节点存储的是索引所在行的其他列数据。
为什么建议innoDB表必须键主键,并且建议用整形和自增主键?
IBD文件必须要有一个b+树去组织和维护数据,如果没有主键就没建立不了b+树索引。如果不规定主键,就会自动找一列唯一的数据作为主键,没有这种列的话就会自动建立一列隐藏列rowID作为主键。所以不建立主键很浪费mysql资源。
整形是方便数据比较大小,如果用字符串还得转换成ASCII码进行比较。线上存储ssd节省空间,整形小。
不自增:为了维护从左到右有序性,数据插入到已经满了的节点中,会导致节点分离,效率降低。