浅谈mysql高性能调优（一）

╰+攻爆jí腚メ 2022-10-12 04:14 94阅读 0赞

## mysql的问题介绍（一） ##

1.  mysql索引的实现原理和数据结构
2.  mysql索引设计的技巧
3.  mysql聚簇索引和非聚簇索引的区别
4.  mysql索引的中级调优方案
5.  mysql分布式集群的设计原则
6.  mysql如何实现高效率的读写分离和分库分表
7.  mysql事务隔离性的实现原理
8.  mysql的原子性和持久性是如何实现的
9.  当前读、快照读到底读的是什么
10. mysql的并发访问核心机制——MVCC
11. 共享锁、排他锁、意向锁、自增锁
12. mysql幻读的实现原理
13. 如何查看mysql中锁的等待情况

## 关于锁 ##

**锁的意义**：主要是解决事务的并发和同步问题，如果事务没有并发问题，则用不到锁。  
提到锁就要先谈事务，关于  
**事务的特性**：ACID（原子性、一致性、隔离性、持久性）  
**原子性**（Atomicity）：原子性是指一个事务中的操作，要么全部成功，要么全部失败，如果失败，就回滚到事务开始前的状态。

**一致性**（Consistency）：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。那转账举栗子，A账户和B账户之间相互转账，无论如何操作，A、B账户的总金额都必须是不变的。

**隔离性**（Isolation）：隔离性是当多个用户 并发的 访问数据库时，如果操作同一张表，数据库则为每一个用户都开启一个事务，且事务之间互不干扰，也就是说事务之间的并发是隔离的。再举个栗子，现有两个并发的事务T1和T2，T1要么在T2开始前执行，要么在T2结束后执行，如果T1先执行，那T2就在T1结束后在执行。关于数据的隔离性级别，将在后文讲到。

**持久性**（Durability）：持久性就是指如果事务一旦被提交，数据库中数据的改变就是永久性的，即使断电或者宕机的情况下，也不会丢失提交的事务操作。  
**事务的实现原理-MVCC**：通过日志实现，**锁是实现隔离性的原因**

## 数据库的隔离级别 ##

数据库的隔离级别主要是解决数据一致性的问题！！

1.  脏读：脏读是指一个事务在处理数据的过程中，读取到另一个为提交事务的数据。
2.  不可重复读：不可重复读是指对于数据库中的某个数据，一个事务范围内的多次查询却返回了不同的结果，这是由于在查询过程中，数据被另外一个事务修改并提交了。
3.  幻读：幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。  
    幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

> 四种隔离级别解决了上述问题

1.  读未提交（Read uncommitted）**没有事务控制，容易脏读**  
    此时，可能读取到不一致的数据，即“读脏 ”。这是并发最高，一致性最差的隔离级别。
2.  读已提交Read committed）**避免脏读**  
    可避免 脏读 的发生。在互联网大数据量，高并发量的场景下，几乎 不会使用 上述两种隔离级别。
3.  可重复读（Repeatable read）**解决不可重复读的问题**  
    MySql默认隔离级别。可避免 脏读 、不可重复读 的发生。
4.  串行化（Serializable ）**顺序执行**  
    可避免 脏读、不可重复读、幻读 的发生。

> **幻读又分为当前读和快照读**  
> 1、当前读：读取的是数据的最新版本【可以理解为 update,delete,insert,for update,lock inshare mode】  
> 2、快照读：读取的是历史数据【可以理解为select】  
> **注意：select又根据隔离级别的不同，读取的数据也可能不同**

## 关于MVCC ##

> Mysql要保存历史数据，牵扯出了一个概念，叫做MVCC；多版本并发控制；

一行数据可能有多个历史版本；

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>name</td> 
   <td>age</td> 
   <td>隐藏id</td> 
   <td>事务id</td> 
   <td> <p style="margin-left:.0001pt;text-align:justify;">回滚指针</p> </td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>innoDB插入数据的时候，必须要跟某个索引行绑定在一起，一般是主键、唯一键或者是6字节的rowid，这个索引有一个选择的顺序</td> 
   <td>每次要进行数据并发操作的时候， <p style="margin-left:.0001pt;text-align:justify;">要开启不同的事务，事务的版本号是递增的，会有一个事务id，表示最近的修改或更新的单行记录的事务id</p> </td> 
   <td> <p style="margin-left:.0001pt;text-align:justify;">指向了上一个版本的历史数据，表示当前数据更改的一个范围</p> </td> 
  </tr> 
 </tbody> 
</table>

历史数据保存的地方：undolog，它除了可以完成MVCC之外，还可以实现原子性；

Purge线程清楚历史数据，当事务正确提交后触发该线程；

> 当我有多个历史版本的数据，选择哪一个历史版本呢？

没有保存时间信息，但是保存了事务的id，事务的id是递增的。

## Read view 读视图； ##

当进行快照读的时候，会生成一个事务的id列表，来保存不同的信息。通过这些信息来做可见性判断；

**List:**列表保存生成readview的时候，活跃的id

**Up\_limit\_id：**当前活跃的id的最小值

**Lower\_limit\_id：**尚未分配的下一个事务的id；

> 那么如何判断读取的是数据是不是正确的呢？需要用到readview

>  1、首先比较DB\_TRX\_ID<up\_limit\_id；如果小于，则当前事务能看到DB\_TRX\_ID所在的记录；如果大于或等于则进入下一个判断；
> 
> 2、接下来判断DB\_TRX\_ID >= low\_limit\_id；如果大于等于则代表DB\_TRX\_ID所在的记录在ReadView生成后出现的，那么对于当前事务肯定不可见；如果小于，则进入下一个判断；
> 
> 3、判断DB\_TRX\_ID是否在活跃事务中，如果在则代表readview生成时刻，这个事务还是活跃的状态，还没有commit，修改的数据，当前的事务也是看不到。如果不在，则说明这个事务在Readview生成之前就开始commit，那么修改的结果是能够看到的！！

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>2</td> 
   <td>3</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>事务开始</td> 
   <td>事务开始</td> 
   <td>事务开始</td> 
   <td>事务开始</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>update commit</td> 
  </tr> 
  <tr> 
   <td>...</td> 
   <td>快照读</td> 
   <td>...</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

当事务2在执行快照读的时候，能否读取到事务4修改的数据；【没有定义隔离级别】

**undolog中有几个历史版本？ 1个！！**

read view

list: 123 是活跃事务，4是非活跃事务，因为它已经提交了；

up\_limit\_id ： 最小值是1

lower\_limit\_id : 下一个是5；

进行的数据查找了：

DB\_TRX\_ID:4

带入上述公式，看看能否正确查看结果？

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA_size_16_color_FFFFFF_t_70][]

锁：用来控制不同的事务并行执行的时候能够操作对应的数据行；

innoDB时行锁，锁的粒度越小，并发执行度越高；

间隙锁：【行锁】

临键锁：【行锁】

记录锁：【行锁】

行锁又分为共享锁和排他锁；和不同的隔离级别有关的；

innoDB里边加锁是给索引加锁，不是数据；

间隙锁和临键锁存在的意义是啥？解决幻读的问题；

分情况：

RR没有主键和索引

RR有主键无索引

RR有主键有索引

RC.....

## 排查具体的锁情况： ##

Set autocommit = 0;把自动提交关闭；

Begin;

Show engin innodb status;

Set global innodb\_satus\_souput\_locks = 1;

Commit;

Begin;

![20210703104119568.png][]

意向锁：【表锁】

自增锁：【表锁】

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA_size_16_color_FFFFFF_t_70]: /images/20221005/a7177822db7f47b19b7444694d554da1.png
[20210703104119568.png]: /images/20221005/3d2fe246db5c490e86b4227abea6b0c9.png