中间件学习--MySql索引、事务、MVCC学习

在面试的时候碰到了用索引优化数据库的问题，完全懵逼，所以特意来看一下这段视频，并做了这篇笔记。

本篇笔记是参考MySQL索引失效原理_哔哩哔哩_bilibili 做的。

什么是mvcc？

Multi-Version Concurrency Control，主要是为了提高数据库的并发性能，避免同一个数据在不同事务之间的竞争，让数据库不用在读写时加锁，MVCC主要用于处理读请求，这个读指的是快照读也就是普通的select读。

快照读和当前读

快照读指的是普通的select读；
当前读指的是一种悲观锁的操作，它读取最新的数据库记录并且会对当前在读的数据加锁，防止其他事务修改数据。共享锁、排他锁、串行化都是当前读。

数据库acid的实现

原子性：通过undolog实现；

持久性：redolog

隔离性：通过加锁和mvcc实现，而加锁操作是对”写-写“实现隔离，通过加上表锁、行锁让数据库资源加锁，实现只能被一个事务使用加锁的数据库资源；而mvcc实现的是”读-写“的隔离，在常用的隔离级别”读已提交“和”可重复读“下，它的快照读都是通过mvcc实现的。

一致性：通过保证上面的三个特性来保证一致性。

事务隔离级别和问题

四种隔离级别对应解决三种问题：

脏读：由于读到未提交的数据，两次读可能读到不一样的数据，因为别的事务正在修改这条数据，这就是脏读。
不可重复读：两次读取到的数据不一样，但在两次读取之间这条数据被其他事务修改并提交了，就会导致两次数据不一样。
幻读：指的是读取到的数据量不一样，当事务不是独立执行时容易发生这个情况。比如两个事务同时向数据库插入编号为6的数据，两个人都以为自己插入的数据是6，插入完总量变成6，但实际上变成了7；又比如一个事务修改所有数据行，另一个事务添加一行数据，这时第一个事务执行完发现还有一行数据没被修改。

undolog日志和版本链

如上图，对于一条数据做多次修改，将张三改成了李四、王五、赵六，它们的id没变表示是在原来的数据上修改的，但是他们的事务id和回滚指针会在修改的过程中改变。而undolog就保存历史版本的信息，版本链则是由回滚指针和undolog链接起来得到的。

ReadView

ReadView其实就是一个保存事务ID的list列表。记录的是本事务执行时，MySQL还有哪些事务在执行，且还没有提交。在版本链中有很多条记录，readview就是通过一些规则让我们知道该选取那个记录。

它主要包含四个字段：

m_ids，当前有哪些事务正在执行，且还没有提交，这些事务的 id 就会存在这里，称为当前活跃的事务；
min_trx_id，是指 m_ids 里最小的值，就是活跃的读写事务中的最小的id值；
max_trx_id，是指下一个要生成的事务 id。下一个要生成的事务 id 肯定比现在所有事务的 id 都大；
creator_trx_id，表示生成readview事务的事务id。每开启一个事务都会生成一个 ReadView，而 creator_trx_id 就是这个开启的事务的 id。

readview的判断逻辑：

trx_id == creator_trx_id：可以访问这个版本，因为数据的事务id和创建这个数据的事务id一样，表示这个数据就是由这个书屋创建的，所以可以访问；
trx_id < min_trx_id：可以访问这个版本，因为数据的事务id比当前活跃的未提交事务id小，那就是已提交的数据，所以是可以访问的；
trx_id > max_trx_id：不可以访问这个版本，因为数据的事务id大于下一个事务的id，违规了。
min_trx_id <= trx_id <= max_trx_id：不可以访问这个版本，这里指的是要访问的trx_id在m_ids中，即在活跃事务id列表中的，表示未提交，所以不可读；当然，如果trx_id不在m_ids中，也是可以访问的，因为表示已提交的事务。

MVCC如何实现”读已提交“和”可重复读“

在读已提交隔离级别下，mvcc就是在执行每一条select语句时创建一个readview，根据上述判据判断是否可以读到某一个版本。

在可重复读隔离级别下，mvcc会根据事务创建readview，即多条select语句使用一个readview，那么在这个事务中读到的数据版本就不会改动了。

以一个例子来说明：

其中，张三是最早的版本，后续有一个事务修改了两次这个数据的值但是未提交，那么它们的事务id就改变了，显然越新的越大，注意这里王五的trx_id不是20，我们修改为60。然后根据判据：

当前m_ids有：20（李四）、60（王五）；min_trx_id：20；max_trx_id：61（比王五还要大1）；creator_trx_id：10（张三）

	trx_id == creator_trx_id（10）	trx_id < min_trx_id（20）	trx_id > max_trx_id（60）	trx_id 在m_ids（20，60）中？
王五60	否	否	否	是
李四20	否	否	否	是
张三10	是（读的到）	是	否	否

所以读到张三这个版本

快照读和当前读如何解决幻读

按上述所说，快照读会在select执行时生成一个readview，那后续数据的变化不会再影响这个readview，所以读到的数据量和数据信息不再改变了；

当前读通过加间隙锁的方式解决，首先让数据id形如1、3、5、7这样间隙递增，然后读的时候把要读的一段加锁，这样如果同时有一个事务想要插入id=4的数据就不能成功，以此方式解决了幻读。

Mysql数据库索引的底层数据结构

结论：使用了b+树这个数据结构，b+树的叶子节点是双向链表，非叶子节点的每个节点可以存放两个数据。

使用b+树的好处是：降低了树的高度，又是一颗查找树，可以使用二分查找快速查到数据，同时避免了大于、小于这类查询时的回退查找的问题，可以直接将双向链表的后续或者向前的全部一次性返回。（如果是b树、平衡二叉树要返回上一个节点，磁头反转是一个很昂贵的操作，特别慢）

三种树的动画演示可以查看：AVL Tree Visualzation (usfca.edu)

B-Tree Visualization (usfca.edu)

B+ Tree Visualization (usfca.edu)

三种树的数据结构可看：二叉树，平衡二叉树，红黑树，B-树、B+树、B*树的区别_红黑树是平衡二叉树吗_道法—自然的博客-CSDN博客

索引Index和索引失效

索引是在你存储的数据之外，额外保存一些路标（一般是B+树），以减少检索数据的时间。所以索引是主数据衍生的附加结构。

常用的索引有：

单列索引：只包含一个字段的索引叫做单列索引；
复合索引：包含两个或以上字段的索引叫做复合索引；
唯一索引：是在表上一个或者多个字段组合建立的索引，这个（或这几个）字段的值组合起来在表中不可以重复。一张表可以建立任意多个唯一索引，但一般只建立一个。主键可以作为一种唯一索引。
聚簇索引：聚簇索引的叶子节点就是数据节点 innodb；
非聚簇索引：非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针；

组合索引会先按照第一个字段排序，在第一个字段相同的情况下又按照第二个字段排序，以此类推。所以当查询语句掠过了第一个字段时，索引就失效了。同理出现模糊查询时也容易导致索引失效，因为都是略过了第一个字段，比如用通配符：‘%aa’ 就导致索引失效了。

Innodb和MyISAM的区别：

简要概括为一下几点：

外键：Innodb支持，MyISAM不支持；

索引：Innodb是聚簇索引，索引指向数据，MyISAM是非聚簇索引，索引指向地址；

锁粒度：Innodb是行锁，MyISAM是表锁；

如何利用索引优化数据库

首先需要建立索引，以视频中的数据库为例，包含的字段如下表。

创建数据库的代码如下：

create table `test_user`{
    -> `id` int(11) not null AUTO_INCREMENT comment '主键id',
    -> `user_id` varchar(36) not null comment '用户id',
    -> `user_name` varchar(30) not null comment '用户名称',
    -> `phone` varchar(20) not null comment '手机号码',
    -> `lan_id` int(9) not null comment '本地网',
    -> `region_id` int(9) not null comment '区域',
    -> `create_time` datetime not null comment '创建时间',
    -> primary key (`id`)
    -> }engine=InnoDB AUTO_INCREMENT=5000 DEFAULT CHARSET=utf8mb4;

我们可以创建组合索引：

alter table test_user add index idx_phone_lan_region(phone,lan_id,region_id);

这样查询的速度会快非常多，当然我们可以创建多个索引，只要符合索引规范即可。

以我们自己项目中的数据库为例，这是一个数据量也达到百万级的数据库，只是并发非常低可以忽略不计：

我们的优化思路是，除了主键id外，可以将ip、warnstyle、warnlog构成索引，这是因为只有这三个可以唯一确定一条记录，报警时间也有很多重复的。

也可以添加更多的索引，如warnstyle、warntime、warnlog构成索引，以应对条件查询限定报警类型的情况。