MySQL佛系笔记

MySQL佛系笔记

​ 之前为了找MySQL的资料在网上翻来翻去，感觉很麻烦，所以现在为了自己方便，借鉴了不少大神的文章，东拼西凑再加上自己的理解进行整合，主要是为了方便自己以后看。

MVCC的理解

​ MVCC，即多版本并发控制，是一种并发控制的方法。MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

​ MVCC的核心主要是，三个隐式字段，undo日志，Read View(读视图)。

三个隐式字段

​ MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。那么具体是怎么做的呢，就要先看看三个隐式字段了。

​ 每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段。

1. DB_TRX_ID
   6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
2. DB_ROLL_PTR
   7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
3. DB_ROW_ID
   6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

undo日志

undo log主要分为两种：

1. insert undo log ：顾名思义，插入的时候的日志，只在事务回滚时需要，并且在事务提交后可以被立即丢弃。
2. update undo log：更新和删除时都会产生这种日志，不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除。

实际情况：在数据进行操作时，无论插入、更新、删除，都会将数据存入undo log一份。具体是怎么样的，关键还得是上面说过的隐士字段，插入和删除，就是直接往undo log放入一条当前操作的数据备份，事务id和回滚指针可能是空的。更新操作，会将当前原数据放入undo log日志中，新数据其中的回滚指针会指向上一个版本的数据，事务id就是当前事务的id。这样最后就形成了一条链表，链首就是最新的旧记录，链尾就是最早的旧记录。

Read View(读视图)

​ Read View就是事务进行快照读操作的时候生产的读视图。

​ 首先说一下当前读和快照读，当前读就是进行增删改等操作时，读取的是数据的最新版本，读取时还要保证其他并发事务不能修改当前数据，会加排锁。快照读就是不加锁的select操作，即不加锁的非阻塞读。

​ 当我们某个事务执行快照读（select操作）的时候，会创建一个Read View，通过一个可见性算法，然后判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该数据的undo log里面的某个版本的数据。

​ 举个例子：当前某个事务有执行select操作读取数据的操作，而另外又有并发事务要更新数据，那么这个事务读取的数据到底该怎么获取，如果获取到了其他事务未提交的数据，那岂不是脏读了吗，所以需要一个可见性算法去处理。

​ 大致流程就是：将最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本。

​ Read View中有包含了3个重要的数据（首先明确一件事，事务id是递增的，事务id越大，表明该事务越新）：

1. trx_ids：一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID。
2. up_limit_id：记录trx_ids列表中事务ID最小的ID。
3. low_limit_id：ReadView生成时，系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1。

可见性算法：从undo log中挨个遍历数据，取出每条记录中的DB_TRX_ID（事务id）去判断。

1. 如果DB_TRX_ID小于up_limit_id，那没事了，直接能看到DB_TRX_ID所在的记录，它就是可见的最新记录（因为遍历是从链首开始的）。
2. 如果是大于等于up_limit_id，那么接下来和low_limit_id进行比较。如果大于等于则代表DB_TRX_ID 所在的记录在Read View生成后才出现的，那对当前事务肯定不可见，那就继续遍历下一个DB_TRX_ID。
3. 如果是小于low_limit_id，就判断DB_TRX_ID是否在trx_ids中，如果在，则代表Read View生成时，事务还在活跃，还没有提交，此事务修改的数据，当前事务是看不见的；如果不在，则说明，此事务在Read View生成之前就已经提交了，那么它修改的结果，当前事务是能看见的。

SQL优化

1. 使用group by 分组查询是——默认分组后，还会排序，可能会降低速度，在group by 后面增加 order by null 就可以禁止排序.

   explain select * from emp  group by deptno order by null;
   

2. select * from userId >= 101 和select * from userId > 100 哪个效率高?

   在工作中尽量不要使用>= 、 <=因为会做两次全表扫描，使用> 、 < 、 !=、<>

3. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

   select id from t where num is null
   

   最好不要给数据库留 NULL，尽可能的使用 NOT NULL 填充数据库。不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL 也包含在内），都是占用 100 个字符的空间的，如果是 varchar 这样的变长字段， null 不占用空间。

4. in 和 not in 也要慎用，否则会导致全表扫描，如：

   select id from t where num in(1,2,3)
   

   对于连续的数值，能用 between 就不要用 in 了：

   select id from t where num between 1 and 3
   

   很多时候用 exists 代替 in 是一个好的选择：

   select num from a where num in(select num from b)
   

   用下面的语句替换：

   select num from a where exists(select 1 from b where num=a.num)
   

5. 全模糊查询会导致全表检索

   select id from t where name like ‘%abc%’
   

   如果只是右模糊查询则不会全表检索

   select id from t where name like ‘abc%’
   

   更多优化方案可查看：sql优化

   想要调优，可以通过expain去分析sql预计，常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

   explain select * from table where table.id = 1;
   

MySQL的三种日志

1. undo log：顾名思义，Undo Log的字面意思就是撤销操作的日志，指的是使MySQL中的数据回到某个状态。另外，在MVCC中的地位上面也说过了。对于MySQL实现事务来说，起着至关重要的作用。
   在MySQL数据库中，事务开始之前，MySQL会将待修改的记录保存到Undo Log中，如果数据库崩溃或者事务需要回滚时，MySQL可以通过利用Undo Log日志，将数据库中的数据回滚到之前的状态。
2. redo log：顾名思义，Redo Log的字面意思就是重做日志，指的是在数据库出现意外情况时能够对重新执行某种操作。在MySQL中，事务中修改的任何数据，都会将最新的数据写入Redo Log中进行备份，Redo Log 能够实现事务的持久性。
   在MySQL中，随着事务操作的执行，就会产生Redo Log日志，在事务提交时会产生Redo Log并将其写入Redo Buffer，Redo Buffer也并不是随着事务的提交就会被立刻写入到磁盘中，而是等事务操作的脏页写入到磁盘之后，Redo Log的使命也就完成了，此时，Redo Log日志占用的空间可以重新利用，会被后续产生的Redo Log日志覆盖。
3. bin log：Binlog记录所有MySQL数据库表结构变更以及表数据修改的二进制日志，不会记录select和show这类查询操作的日志。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。开启Binlog日志有以下两个最重要的使用场景。
   • 数据恢复：通过mysqlbinlog等工具来恢复数据。
   • 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。

MySQL事务的特性

1. mysql中的事务有4大特性：原子性、一致性、隔离性、持久性。

   • 原子性 (Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。
   • 一致性 (Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。
   • 隔离性 (Isolation)：事务的执行不受其他事务的干扰，当数据库被多个客户端并发访问时，隔离它们的操作，防止出现：脏读、幻读、不可重复读。
   • 持久性 (Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失。

2. 针对脏读（一个事务读取到另一个事务未提交的数据，比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读），不可重复读（事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了），幻读（一个事务多次查询整表数据，由于其他事务新增（删除）记录造成多次查询的记录条数不同（事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了）），提出四大隔离级别。

   • read uncommitted——不作任何隔离，具有脏读、不可重复读、幻读问题
   • read committed——可防止脏读，不能防止不可重复读和幻读问题
   • repeatable read——可以防止脏读、不可重复读，不能防止幻读问题（mysql默认是这个隔离级别）
   • serializable——数据库运行在串行化，上述问题都可以防止，只是性能非常低

MySQL索引失效

1. 如果条件中有or，即使其中有条件带索引也不会使用（这也是为什么尽量少用or的原因）要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2. 对于多列索引，不是使用的第一部分，则不会使用索引
3. like查询以%开头，假如有这样一列code的值为’AAA’,‘AAB’,‘BAA’,‘BAB’ ,如果where code like '%AB’条件，由于前面是模糊的，所以不能利用索引的顺序，必须一个个去找，看是否满足条件。这样会导致全索引扫描或者全表扫描。如果是这样的条件where code like 'A % '，就可以查找CODE中A开头的CODE的位置，当碰到B开头的数据时，就可以停止查找了，因为后面的数据一定不满足要求。这样就可以利用索引了。
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引