MySQL索引与事务深入解析-优快云博客

本文链接：https://blog.youkuaiyun.com/programmer_czh/article/details/106178485

mysql

1. B树与B+树

1.1 B树性质

M阶B树满足下列条件：
1.定义任意非叶子结点最多只有M个儿子；且M>2；
2.根结点的儿子数为[2, M]；
3.除根结点以外的非叶子结点的儿子数为[M/2, M]；
4.每个结点存放至少M/2（取上整）-1和至多M-1个关键字；（至少2个关键字,根节点至少一个关键字）；
5.非叶子结点的关键字个数=指向儿子的指针个数-1；
6.非叶子结点的关键字：K[1], K[2], …, K[m-1]，m<M+1；且K[i]< K[i+1] ；
7.非叶子结点的指针：P[1], P[2], …, P[m]；其中P[1]指向关键字小于K[1]的子树，P[m]指向关键字大于K[m-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8.所有叶子结点位于同一层；

1.2 B+树性质

B+树的性质(与B树不相同的部分)
1.非叶子节点的子树指针与关键字个数相同；
2.非叶子节点的子树指针p[i],指向关键字值属于[k[i],k[i+1]]的子树.(B树是开区间,也就是说B树不允许关键字重复,B+树允许重复)；
3.为所有叶子节点增加一个链指针；
4.所有关键字都在叶子节点出现(稠密索引). (且链表中的关键字恰好是有序的)；
5.非叶子节点相当于是叶子节点的索引(稀疏索引),叶子节点相当于是存储(关键字)数据的数据层；
6.更适合于文件系统；

2. mysql使用B+树作为索引的数据结构优势

1.非叶子节点只存索引并且可以存储更多的关键字，这样B+树相对B树更矮，IO读写次数更少；
2.关键字查找都要从跟节点到叶子节点，查询效率更加稳定；
3.为所有叶子节点增加一个链指针，支持范围查找，定位到叶子节点后，不用再从根节点出发，直接可以从叶子节点，横向跨越子树进行统计，对于数据库的扫描，只用扫描叶子即可，非叶子节点不用管。

3. 稠密索引与稀疏索引

3.1 稠密索引

叶子节点保存的不仅仅是键值，还保存了位于同一行的其他列的信息。稠密索引决定了表的物理排列顺序，一个表只能有一个稠密索引。

3.2 稀疏索引

叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键位信息以及主键。

4. NyISAM与InnoDB

4.1 MyISAM

MyISAM默认用的是表级锁，不支持行级锁，不管是主键索引、唯一键索引、还是普通索引，其索引均属于稀疏索引，稀疏索引的叶子节点均指向行数据的物理地址，即表数据和索引是分开存储的。
适合场景
1）频繁执行count全表的语句，InnoDB每次执行需要全表扫描，而MyISAM保存了一个变量记录了表的行数；
2）对数据增删改的频率不高，查询非常频繁；
3）不支持事务的场景。

4.2 InnoDB

InnoDB默认是行级锁，也支持表级锁，有且仅有一个稠密索引（一般为主键），非主键索即为稀疏索引的叶子节点存储的是相关键位和对于的主键值（并不存储行数据的物理地址），故非主键索引的查找包含了两次查找，一次是非主键索引查找到主键值，然后通过主键值查找对应的行数据，InnoDB的主键索引和对于的数据是保存在同一个文件中的，所以在加载索引的时候，也会将数据加载到内存中。
InnoDB在sql没有用的索引的时候使用的是表级锁，在用的索引时是行级锁，行级锁事先的开销远大于表级锁，所以使用行级锁性能会有所损失。
适合场景
1）对数据增删改的频率不高；
2）要求可靠性比较高，支持事务。
数据库文件，.frm是存储表的数据结构的文件，InnoDB中.ibd是存索引和表数据的文件，MyISAM中.MYI是存索引的文件，.MYD是存表数据的文件。

5. 锁

5.1 悲观锁

先取锁，再对数据进行操作的保守策略没，会产生额外的开销，增加产生死锁的概率，数据库的事务就是悲观锁方式。

5.2 乐观锁

乐观的认为不会造成数据冲突，在数据提交的时候才会检查数据是否冲突，乐观锁一般不用数据提供的锁机制，而是使用版本号或是时间戳，减少死锁的问题。

6. 事务隔离

事务隔离机制，isolation的级别为read uncommitted（可读取未提交后的值），就有可能出现，两个事务分别对同一条数据的某个值（500）进行操作，事务一在执行+200操作后，因为某种原因回滚了, 事务二在事务一+200操作后且回滚之前读取了值为700，然后进行-300，此时就出现了所谓的脏读。isolation的级别改为read committed（读取提交后的值，Oracle默认级别）。
不可重复读，事务二对数据的某个值进行+200操作，在+200操作之前事务一读取到的值为500，事务二+200操作后并提交结果，事务一在事务二提交前再去读，结果变为700，会导致同一事务，读取同一值结果不同的情况，将事务隔离级别再调大一级，也就是Mysql默认隔离级别（repeatable read）可避免。
幻读，事务一对表中数据进行了当前读（共享读锁lock in share mode），此时事务二对表进行新增或删除操作，事务一然后对当前读得数据进行批量修改操作，会发现多了一行或少了一行数据更新，出现幻读，将事务隔离级别设置为最高的隔离级别（serializable–所有的sql执行都会加上锁）即可。
但在mysql中的InnoDB的隔离级别repeatable read