mysql B+树底层原理总结思考*

Update (2021-02-23)

回表：e.g. select A from .... where B ...: 其中B是索引；

显然如果A=B那么直接就返回结果了，一次查询得到结果，就不用回表
如果A < B: 那么也不用回表，B联合索引完全覆盖A, 也是一次查询得到结果
A > B: B索引不能完全覆盖A，e.g. SELECT * from XXX where id..., 显然A范围大于B, 那么就要拿到根据id作为索引的列，然后再查询：两次查询，需要回表。

Prelude

最近跟mysql打交道有点多，其实是跟着DBA。我来的时候用explain一看发现Extra里面有很多Using filesort就发现为什么数据量到了300w之后速度下降的那么快；然后就是锁的问题，因为InnoDB默认是REPEATABLE READ, 索引失效之后导致行锁变表锁慢了很多😂

不过因为数据量直奔2千w而去，估计上亿也就是几天之后的事情了而又对snowsql无感，估计要上hive了。

总结一下最近mysql的事情:

explain看出来的各列东西；
避免索引失效的做法；
order by和索引相同顺序；
mysqldumpslow慢查询日志
CREATE FUNCTION/PROCEDURE批量插入脚本
show profile
锁的四个问题: 丢脏重幻
行锁:
- 索引失效: 行锁变表锁
- 间隙锁: id断裂
锁住一行: begin; <SQL> for update; commit; show status;
mysql主从

搞了这么多之后，其实有点想知道底层是怎么搞的; 查了一下资料: B+树

Gastronomist: B+树

首先明白，索引是一种数据结构；可能用5种:

二叉树；
红黑树;
B树;
B+树;
hashtable;
里面存的都是KV键值对，K是索引，V是索引对应的磁盘真实数据地址。

Q: 为毛用B+树?

二叉树: 好理解: 如果数据单调: 退化为linked list; ❌
红黑树(RB树): 也叫自平衡二叉树；在这个网站自己试着插入7-8个节点, 看一下树的形状，就会明白RB树可能非常深，所以很多数据会查找很多次，慢；
B树和B+树一起说:

b.png

这是B树

b+.png

这是B+树

b树/b+树跟前面的二叉树/RB树首先最大的不同: 因为可能树深度很深啊，所以怎么降低深度: 每个节点多放一些数据呗: 所以b树/b+树每个节点不止放一个索引, 这样降低深度。不过看图也发现b树和b＋树的不同:

b树每个节点放的索引都带data(data存的索引所在行的磁盘地址); b+树不带；
b+树叶子节点才有data，并且节点之间有指针(其实是双向指针，这里只画了单向), 而且因为还是满足二叉树的结构，所以从左到右是增加的);
b+树，非叶子节点的索引，在叶子节点都有，是冗余索引。

首先，为什么b树每个节点放的索引都带data; b+树不带？很简单，因为这样b+树非叶子可以存更多索引，这样虽然可能直接找到索引不能瞬间拿出data还要往下，但是更大可能是，因为索引数量大大增加，查到真实数据的时间缩短了。

Q: 可能问，这都是为了降低树的深度，那我把所有数据都放在根节点不就行了？那么就一次磁盘IO全放在RAM，全在内存中操作最快呢。

(注: 一次节点数据磁盘IO，想象的极限操作: 全放在根节点一次全放进RAM: 纯内存操作，这其实就是Redis搞的事情: mysql --> redis. 就像写轮眼到极限，就变成了轮回眼）

但是那是不可能的。

比如17亿条数据，一次放进RAM？也不是不行，主要是浪费。

实际上mysql已经做出了决定，一个非叶子节点的大小: show global status like 'Innodb_page_size';

pagesize.png

16384 B

看之前B+树的节点中的结构: index ptr index ptr ...这种结构，i.e. 每个index后面跟着一个指向下一行index的ptr; 而每个index是8 B, 每个ptr 6 B; 也就是每个小单元14 B; 那么一个节点中可以放多少个索引? 16384/14 = 1170个索引。

叶子节点的data大小比索引大得多，假设1KB, 那么每个叶子节点可以放16个数据单元，那么B+树的树身height = 3，3次就可以查1170*1170*16 = 21902400差不多2千1百万行数据; i.e. 2千w行数据，也就3次查询, 2次磁盘IO: 非常可以。

Q: MyISAM和InnoDB

数据存在磁盘上，存在哪里？ /var/lib/mysql:

varlibsql.png

Updated Part

Q：MyISAM是存在frm, MYD, MYI文件中，InnoDB没有MYD, MYI文件了，合并成ibd文件；底层有什么不同?

MyISAM: 索引是和数据分离的, i.e. 非聚集；叶子节点放的data是索引对应磁盘真实数据的地址, e.g. 0X5A, 找到地址还需要一次磁盘IO;
(Updated, 2020-07-29): InnoDB: 聚簇索引: data存了那一行除主键外的其它索引, i.e. 如果条件是类似where name = 'Lei'这样的，就要根据name先在“辅助键索引B＋树”中找主键，然后根据主键查找存在主键索引B＋树上的行数据，并不是“根据ptr地址指针去磁盘找数据”这个过程。

为什么InnoDB查找比MyISAM快? 理论上讲MyISAM从索引找到磁盘存储的真实数据需要一次地址hash寻址，而InnoDB经常“辅助键索引->主键索引”这样在多个B＋树之间通过辅助键索引叶子节点上的主键跳到主键索引树上查找行数据，明显比MyISAM通过地址一次hash寻址过程多。

InnoDB快的原因是：

因为行数据是放在主键索引B＋树上面的，所以数据和索引都是一次载入内存的，相比MyISAM的磁盘寻址，纯内存操作要快的多了。
另外就是辅助键索引树存放的是主键ID而不是地址，是因为可以省去数据移动时维护辅助键索引的开销，如果存的是地址，那辅助键索引B＋树本身要经常移动，维护很麻烦；而且InnoDB本身会默认创建主键，在内存操作下只是多一步从辅助键索引树查找ID到主键索引树，也还好。

附:
聚簇索引和非聚簇索引本质区别，mysql中Page的本质: link。

MyISAM, InnoDB, 都是存储引擎，是形容表的，不是库。

Q: InnoDB，表必须有主键，并推荐使用整型自增主键，为毛？

其实现在就很明白了，主键即索引，必须要用索引，否则全表扫描，非常低效。实际上，就算不建索引，mysql也会有个rowId默认创建的索引。

自增主键这个是老生常谈了，衍生的问题还有用 1. uuid, 2. DB集群自增, 3. redis集群生成, 4. Snowflake算法生成，这里就不展开了，前面三个都有缺陷，snowflake目前比较好，可以用到2039年9月7日，不过估计也会有缺陷。

Update: 上面并没有回答为什么需要AUTO_INCREMENT; 原因是这样的: 现在我们知道了一定要用INT, 那为什么要 AUTO_INCREMENT?: 因为用这个网站看出，如果不是自增，而是中间断裂插入的方式，可能因为插入中间数据导致节点数据满了，所以要分裂树🌳，还有可能要再平衡，所以效率变低了；如果插入的数据都是自增的，那么从叶子节点看过去，就是直接插入往这个双向链表正向直接加数据就行了，所以OK

Q: B+树快，但是hashtable更快，不管数据规模，都是一次，为毛不用?

select ... where id = 5;: 确实，hash作为索引数据结构更快；
select ... where id > 5;: hash, 有什么用?

i.e. 范围查询，hash, 慢的一批，等价于全表扫描，而B+树依旧坚挺：叶子节点找到值之后，因为是链表，所以顺着>或者<的方向，全部拿出来就行了。

附: Postgresql底层是btree:

psql.png

link: MySQL优化进阶