b+tree数据结构、limit、order by、 group by、where 分析

b+tree数据结构、limit、order by、 group by、where 分析
思路： 从b+tree索引文件结构和InnoDB之聚簇索引、辅助索引文件出发，直到InnoDB的具体执行方案

b+tree 、 聚簇索引、 辅助索引 - 我之前已经说过

b+tree.png

InnoDB聚簇索引数据文件.png

b+tree和limit

用户表tb_user 对age添加辅助索引

select * from tb_user where age>=18 limit 10000, 10

1、 在索引文件中查找age>=18的节点； 然后向后遍历9999条记录， 再取出后面的10条记录；

2、 遍历9999条记录需要遍历N个叶子节点， 执行N次I/O操作， 很耗时， 所以用limit做分页当数据量小的时候因为读取的数据量小， 执行的I/O操作也少， 总耗时可以接收，但是一旦offset过了1000或者10000这样的量级别， 则耗时1s或3s以上， 则不能接收；

3、 不用limit做分页；
tb_user表 用id做为主键索引， id是递增的；

select * from tb_user where id>=10000 and id <=10009

可以不用遍历叶子节点， 走索引直接查询。但是限制了只用主键索引字段id作为范围查询、却不能有其他的字段的where条件。

InnoDB的b+tree索引数据文件、聚簇索引文件、 辅助索引文件、 覆盖索引

辅助索引字段查询， 1、 走对应的辅助索引文件， 辅助索引叶子节点data域保存聚簇索引字段值； 2、聚簇索引字段值再到聚簇索引文件中查找记录；

为了加快速度， 如果不走2， 只走1， 则需要目标查询字段就是 索引字段， 直接在辅助索引文件中就能查出来；

b+tree和 order by , group by

因为b+tree叶子节点都是有序且叶子节点间用顺序访问指针连接， 所以聚簇索引文件和辅助索引文件本身就是有序的； 所以order by， group by 最优情况下就是走同一个索引， 同一个索引文件； 因为索引文件本来就有序， 所以无需在内存中再排序，直接在索引文件叶子节点读取的就是分组、排序好的节点。

where, group by, order by 最优的情况就是用单个索引文件， 可以是单个索引字段， 也可以是复合索引字段。