Mysql相关一些记录和总结

关于mvcc相关文章

1、通俗易懂的mvcc讲解

https://www.cnblogs.com/jelly12345/p/14889331.html

在mysql中mvcc的应用知识在隔离级别是RC和RR这两个隔离级别的时候才会出现；但是这两个隔离级别产生的readview的时机不是一样的，RC隔离级别的时候，在同一个事务当中，第一个查询的sql语句执行的时候；而RR隔离级别的时候是在开启事务的时候创建readView;

但是需要注意的是：”begin是没有开始事务的，只有执行的第一个sql语句才开启事务”；而使用”start transaction with consistent snapshot“的时候是立马创建一个事务；

下面有个小题目，来看下最后的值是多少；

mysql> CREATE TABLE `t` ( `id` int(11) NOT NULL, `k` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;insert into t(id, k) values(1,1),(2,2);

执行sql流程图（隔离级别是RR）

先说答案：

事务A查询到的K=1；

事务B查询到k=3;

其实判断这个结果的诀窍在如下：

开始事务创建的readview的时机

根据这个条件我们就知道事务C是先执行的，执行完之后k=2，然后事务B开始执行，执行更新语句之后是3，然后再查询由于是自己查自己肯定是k=3;

而事务A中的k=1则是因为，当k=1的时候，事务已经开启并且创建了readview这个视图，所以不管外部怎么更新这里看到的其实都是k=1;

2、mysql如何保证数据不丢失？

其实在讨论不丢失的时候，首先要定义什么是数据不丢失。我的判断是这样的：

如果mysql数据库提交了事务，并且没有报任何异常的情况下，这个时候如果数据库发生了各种意外而造成的数据库丢失才是真的数据库丢失。

如果是一个应用调用了数据库接口，然后在调用的过程中数据库发生了异常，这个时候数据没有写入到数据库中，这个就不能成为数据丢失。

换句话说，只要应用程序成功提交了事务，那么这个时候的数据库发生丢失才能叫数据库数据丢失。不过这里也有特殊情况，就是在事务提交的过程中，数据库发生了意外，这个时候如果redo log和bin log都把对应的数据刷到了磁盘，那么数据也照样是可恢复的。

先说结论：

只有redo log和binlog保证都持久化到磁盘中，这样才能最大程度的保证mysql数据不丢失。配置参数如下：

只有在 sync_binlog 和 innodb_flush_log_at_trx_commit 都等于1的情况下，才能保证数据不丢失。 即

写 redo log 时，每次事务提交时，都将所有redo log fsync到磁盘

写 binlog 时，每次事务提交时，binlog 都会执行 fsync到磁盘。

redo log的名词解释

系统崩溃重启需要按照上述内容所记录的步骤重新更新数据页，所以上述内容也被称为重做日志，即redo log.

mysql各种日志的解释和作用

3、redo log 和binlog的解释

redo log和bin log都是使用的WAL（write ahead logging）技术，也就是说会先写日志再刷盘，这里的先写日志是指把日志写到内存中去，而内存有分为两部分(buffer和page cach)。

redo log的产生目的就是为了防止数据库突然发生异常而造成的数据丢失。

关于redo log的日志写入

下图是redo log的文件的数据结构，redo log的文件是一个环型结构（当然逻辑上是一个环型结构）;

write pos是记录当前的位置，checkpoint是要擦拭文件的位置，write pos和checkpoint之间的就是可以写入文件的内容。

redo log的写入流程是这样的：

当执行update语句的时候，引擎会把新数据写入到内存中，同时更新到redo log的内存日志当中，此时的redo log是出于prepare阶段，这里的prepare阶段意思是:”已经准备好了，随时可以进行日志提交”；

然后这个时候binlog的日志会写到内存中去，然后把进行提交，随后把redolog的日志状态给改成commit.

bin log

redo log是引擎端的日志，而bin log是server端的日志；

redo log和bin log的不同

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

参考文章：

redolog和binlog的写入流程

4、索引

索引其实就是为了提高查询效率的，但是尴尬的是，你在查询数据之前，总的需要先把数据塞进去吧。而且后续还伴随着数据的删除和插入等操作，这个时候索引考虑的就不仅仅是查询的问题了，还要结合写入来进行考虑，从而得到一个相对平衡的状态。

索引的分类：

hash（方便插入和等值查询；但是进行范围查询的时候就比较尴尬了）

有序列表（查询的时候，特别是进行连续的范围查询的时候是非常高效的，但是插入数据效率低下）

树（可以平衡插入和查询的效率；比如mysql的b+树底部的数据是通过唯一索引链接起来的，范围查询更有效）

其中的树还有N叉树，这样可以让树的层数稳定在一定层数，从而减少磁盘的寻址，从而加快了查询速度。比如b+树，甚至前2层的数据都可以放内存中，因为只有最后的叶子节点才存储数据，所以非叶子节点放内存占内存比较少，从而能够大大加快查询的效率。

索引的维护

假设图如下：

如果数据是插入到最后的，那么这样mysql无需过多的数据维护，只需要把指针指向最后的一个叶子节点就可以了。

但是如果数据插入到中间，那么就涉及到数据的移动，并且还可能涉及到页分裂;也就是需要再去申请一个页然后把数据给挪过去。页分裂除了会造成性能的下降，还会造成数据的利用率降低，因为mysql的存储是按照页来进行存储的。

既然有页分裂，那么自然也会有所谓的页合并，也就是删除数据的时候。

这就解释了为何要使用自增主值来做主键，因为这样插入的时候维护数据的成本是最低的。相反如果使用uuid这类没有规则的字符串作为主键，那么对mysql来说维护的工作量真的是太大了。

4.1普通索引查询过程

我们已经知道只要是非聚蔟索引，也就是普通的索引也会建立一个b+树，非叶子节点存的是普通索引的值（和主键一样，经过排序的），而叶子节点的内容是普通索引和主键的值。如图所示

假如我们来查询一个sql语句呢：

select * from T where k between 3 and 5;

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

查询的过程如下：

在 k 索引树上找到 k=3 的记录，取得 ID = 300；

再到 ID 索引树查到 ID=300 对应的 R3；

在 k 索引树取下一个值 k=5，取得 ID=500；再回到 ID 索引树查到 ID=500 对应的 R4；

在 k 索引树取下一个值 k=6，不满足条件，循环结束。

由此可见普通索引每次都要进行回表来进行查询，为什么呢？因为数据都是存在主b+树的叶子节点上。

如果想不回表查询呢？可以使用覆盖索引；使用的方式就是通过查询能够在普通索引的b+树上能够查到到自己想要的一切。例如：

select id,k from T where k between 3 and 5;

4.2最左前缀原则

在介绍最左前缀原则的时候，有没有想到一个问题，什么最左前缀原则能够使用到索引呢？先说结论：

那是因为普通的索引是按照顺序来进行排好序的，所以使用最左匹配的时候只要找到了第一个，那么就通过叶子节点往后挨个查询即可。

现在有意思的事情来了，如果是创建的联合索引那么这个时候，最左前缀是用在哪个字段上才能使用到索引呢？或者准确的说才能让查询的数据更高效呢？哈哈哈

其实只要走索引表，其实都是利用到了索引，但是索引的数据有可能也是有很多的，如果遍历所有的索引再回表，那么效率可能还不如直接全表扫描呢。

先说结论，如果是联合索引，那么最前缀用在联合索引的第一个字段上的时候效率是最高的。

联合索引是先根据第一个字段进行排序然后建立起来b+树，如果第一个字段的值相同那么就排序第二个字段的值，以此类推。比如下图：

同时也解释了联合索引为何是这样建立索引的，比如建立a,b,c这样的索引，那么会建立a,ab,abc这样的三个索引，而单独的b或者bc是不会走索引的；

ps:与sql的顺序无关，mysql会进行优化的。

现在有个小提问：如果有两个联合索引的值，我都想使用最左前缀原则，或者都想使用索引怎么办呢？

答：或许需要对两个值都建立索引。

4.3索引下推

原来没有索引下推的时候，联合索引匹配上值以后都会进行回表操作，而有了索引下推以后，那么会在联合索引的索引树中进行筛查一遍，从而减少了回表的次数。

select * from tuser where name like '张%' and age=10 and ismale=1;

上面的sql分别是没有索引下推和有索引下推的图片。

无索引下推

有索引下推

4.4索引的选择问题

如果有个表中的身份证字段，现在能够保证mysql插入的时候是不重复的，这个时候是选择普通索引还是唯一索引呢？这个就需要从两个方面来讨论这个问题：写入的时候对性能的影响；读的时候对性能的影响；

读

如果是唯一索引的话，因为是唯一的，所以mysql通过索引树找到索引后，然后再找到主键，返回即可；

如果是普通索引的话，那么就需要继续遍历，直到找到不是查询的值为止；

从这个角度来看，肯定是唯一索引会快，普通索引会慢；但是由于mysql读的时候不是按照一行一行的读的，而是整个页来进行读的，所以如果数据量少的话，其实两者差不多；但是理论上来说还是唯一索引会快点；

写

第一种情况，更新(插入和更新)的数据页就在内存当中，这个时候唯一索引只需要判断下是否有冲突，然后执行语句；普通索引则直接插入值；

第二种情况：更新(插入和更新)的数据页就不在内存当中，这个时候就需要从磁盘中读取数据然后再判断了，然后再判断。而普通索引则直接放到change buffer当中，语句执行完；

需要知道的是磁盘和内存交互的IO是数据库消耗成本极高的地方；所以当内存中没有对应的内存页，需要判断唯一索引的时候，有可能非常消耗资源；

小事故：

有个 DBA 的同学跟我反馈说，他负责的某个业务的库内存命中率突然从 99% 降低到了 75%，整个系统处于阻塞状态，更新语句全部堵住。而探究其原因后，我发现这个业务有大量插入数据的操作，而他在前一天把其中的某个普通索引改成了唯一索引。

其实从另一个角度来看，即使是唯一索引，如果有条件的话，最好使用一个相对有序递增的方式来创建，比如订单号这类的情况。

change buffer的合理使用

当写多读少的时候，可以使用change buffer来进行提高写的效率；当读和写差不多的时候,change buffer并不能给mysql代理性能上的优势；

change buffer只能用在普通索引上；如果一个字段普通索引和唯一索引都可以实现业务需求，那么还是建议使用普通索引，因为他们查询的效率差不多，但是写的时候的效率相差很大；

buffer pool其实一个mysql内存的总称，它包括 change buffer和page cache;