Mysql索引、锁、事务、mvcc、分库分表总结

梦里梦外; 2023-09-27 10:31 127阅读 0赞

#### 1.mysql执行计划 ####

可以使用explain+sql语句来模拟优化器执行sql查询语句，从而知道mysql是如何处理sql语句的。

官网地址：[MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format][MySQL _ MySQL 8.0 Reference Manual _ 8.8.2 EXPLAIN Output Format]

(1)执行计划中包含的信息

**EXPLAIN Output Columns**

<table> 
 <thead> 
  <tr> 
   <th>Column</th> 
   <th>JSON Name</th> 
   <th>Meaning</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_id" title="id" rel="nofollow">id</a></th> 
   <td><code>select_id</code></td> 
   <td>The&nbsp;<code>SELECT</code>&nbsp;identifier</td> 
  </tr> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_select_type" title="select_type" rel="nofollow">select_type</a></th> 
   <td>None</td> 
   <td>The&nbsp;<code>SELECT</code>&nbsp;type</td> 
  </tr> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_table" title="table" rel="nofollow">table</a></th> 
   <td><code>table_name</code></td> 
   <td>The table for the output row</td> 
  </tr> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_partitions" title="partitions" rel="nofollow">partitions</a></th> 
   <td><code>partitions</code></td> 
   <td>The matching partitions</td> 
  </tr> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_type" title="type" rel="nofollow">type</a></th> 
   <td><code>access_type</code></td> 
   <td>The join type</td> 
  </tr> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_possible_keys" title="possible_keys" rel="nofollow">possible_keys</a></th> 
   <td><code>possible_keys</code></td> 
   <td>The possible indexes to choose</td> 
  </tr> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_key" title="key" rel="nofollow">key</a></th> 
   <td><code>key</code></td> 
   <td>The index actually chosen</td> 
  </tr> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_key_len" title="key_len" rel="nofollow">key_len</a></th> 
   <td><code>key_length</code></td> 
   <td>The length of the chosen key</td> 
  </tr> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_ref" title="ref" rel="nofollow">ref</a></th> 
   <td><code>ref</code></td> 
   <td>The columns compared to the index</td> 
  </tr> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_rows" title="rows" rel="nofollow">rows</a></th> 
   <td><code>rows</code></td> 
   <td>Estimate of rows to be examined</td> 
  </tr> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_filtered" title="filtered" rel="nofollow">filtered</a></th> 
   <td><code>filtered</code></td> 
   <td>Percentage of rows filtered by table condition</td> 
  </tr> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_extra" title="Extra" rel="nofollow">Extra</a></th> 
   <td>None</td> 
   <td>Additional information</td> 
  </tr> 
 </tbody> 
</table>

id：select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序。

select\_type：用来分辨查询的类型，是普通查询还是联合查询还是子查询。

table：对应行正在访问哪一张表，表名或者别名，可能是临时表或者union合并结果集。

type：显示的是访问类型，访问类型表示以何种方式访问我们的数据，最容易想到的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次为：

system->const->eq\_ref->ref->fulltext->ref\_or\_null->index\_merge->unique\_subquery->index\_subquery->range->index->All

一般情况下，得保证查询至少达到range级别，最好能达到ref。

possible\_keys：显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

key\_len：表示索引中使用的字节数，可以通过key\_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

ref：显示索引的哪一列被使用了。

rows：根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应sql找了多少数据。

extra：包含额外的信息。

#### 2.B+Tree结构 ####

（1）B+Tree每个节点可以包含更多的节点，做这个的原因有两个，第一个原因是为了降低树的高度，第二个是将数据范围变为多个区间，区间越多，数据检索越快。

（2）非叶子节点存储key，叶子节点存储key和数据。

（3）叶子节点两两指针相互连接（符合磁盘的预读属性），顺序查询性能更高。

#### 3.聚簇索引、非聚簇索引 ####

（1）数据跟索引存储在一起的叫聚簇索引，没有存储在一起的叫非聚簇索引

（2）innodb存储引擎在进行数据插入的时候，数据必须要跟某一个索引列存储在一起，这个索引列可以是主键，如果没有主键，选择唯一键，如果没有唯一键，选择6字节的rowid来进行存储。

（3）数据必定是跟某一个索引绑定在一起的，绑定数据的索引叫做聚簇索引，其它索引的叶子节点中存储的数据不再是整行的记录，而是聚簇索引的ID值。

（4）innodb中既有聚餐索引也有非聚簇索引，myisam中只有非聚餐索引；innodb生成的文件包含.frm(表结构框架信息）、.ibd(索引和数据）；myisam中包含.frm(表结构和框架信息）、.MYI(索引）、.MYD(数据）。

#### 4.索引相关其它概念 ####

（1）回表

某个表字段：id、name、age；id主键，name配置索引；select \* from table where name='zhangsaan';sql执行过程：先根据name索引B+树匹配到对应的叶子节点，查询到对应行记录的id值，再根据id去B+树中检索整行记录，这个过程就称之为回表，要尽量避免回表操作。

（2）索引覆盖

某个表字段：id、name、age；id主键，name配置索引；select id,name from table where name='zhangsaan';sql执行过程：先根据name索引B+树匹配到对应的叶子节点，能获取到id的属性值，索引的叶子节点中包含了查询的所有列，此时不需要进行回表，这个过程叫做索引覆盖。

（3）最左匹配

创建索引的时候，可以选择多个字段共同组成索引，此时叫做组合索引或者联合索引，查询的时候需要遵循最左原则；例一name+age作为组合索引，此时索引中name是有序的，age是无序的。查询的时候where后面跟的条件顺序name、age走索引；age、name也走索引，mysql有优化器；只有name也是走索引的；只有age不走索引。

（4）索引下推

select \* from table where name='zhangsan' and age=12;

没有索引下推之前：先根据name从存储引擎中拉取数据到server层，再在server层中进行age数据的过滤。

有索引下推之后：根据name和age两个条件从存储引擎中拉取数据到server层。

#### 5.MVCC多版本并发控制 ####

（1）当前读、快照读

当前读：读取的是数据的最新版本，总是读取到最新的数据。执行的语句包含：

> select ... lock in share mode
> 
> select ...for update
> 
> update
> 
> delete
> 
> insert

快照读：读取的是历史版本的记录。执行的语句包含

> select...

（2）事务隔离级别

①读未提交

②读已提交RC

③可重复读RR（mysql默认隔离级别）

④串行化

![ec881769ecfa4471883111819d059372.png][]

能否读取到B事务修改的记录？

RC：可以读取到最新的结果值记录

RR：不可以读取到最新的结果值记录

（3）MVCC实现，包含的部分

①第一部分：隐藏字段

数据的每一行记录都会包含三个用户不可见的字段：

DB\_TRX\_ID：创建或者最后一次修改改记录的事务id；

DB\_ROW\_ID：隐藏主键id；

DB\_ROLL\_PTR：回滚指针，指向的是回滚日志undolog。

![fa8739d1e8c849858908c0085941d8ad.png][]

②第二部分：undolog回滚日志

当不同的事务对同一条记录进行修改时，该记录的undolog会形成一个线性表，即链表，链首是最新的历史记录，链尾是最早的历史记录。

![8aed8d3795364f739b29dcb42905a5d3.png][]

③第三部分：readview事务在进行快照读的时候产生的读视图，包含：

trx\_list：系统活跃的事务id集合；

up\_limit\_id：活跃事务id集合中最小的id；

low\_limit\_id：系统尚未分配的下一个事务id。

（4）可见性分析算法

①首先比较DB\_TRX\_ID<up\_limit\_id，若果小于，则当前事务能看到DB\_TRX\_ID所在的记录，如果大于进入下一个判断；

②判断DB\_TRX\_ID>=low\_limit\_id，若是大于等于，则代表当前DB\_TRX\_ID所在的记录，在Read view生成之后才产生，所以当前事务不可见，如果小于则进行下一个判断；

③判断DB\_TRX\_ID是否在活跃事务集合trx\_list中，若果在，则表示在生成read view的时候，此事务还没有commit提交，则此事务修改的东西，当前事务是看不到的；若是不在，则表示在生成read view的时候，此事务已经commit，则修改的结果是能看到的。

![57cb02d14fe44954817ead61480c6e01.png][]

上图的这种方式，经过可见性算法，可以得出结果，可以看到修改后的记录。

（5）不同的隔离级别生成快照的时机不同

RC：每次在进行快照读的时候都会生成新的快照，所以其它事务提交的记录，在当前事务中是可以读取到的；

RR：只有在第一次进行快照读的时候才会生成readview，之后的读操作都会使用第一次的readview，其它事务对某条记录进行了修改，记录的DB\_TRX\_ID为修改的事务id，此修改的事务id在当前事务的快照中还是活跃状态，所以读取不到。

（5）ACID保障

![13078799a511438eb48618482713cd33.png][]

为了保证数据的一致性，可以先将数据通过顺序读写的方式写到日志文件中，然后再将数据写入到对应的磁盘文件中，只要日志文件保存成功，数据就不会丢失，可以根据日志来进行数据的恢复。

（6）binlog、redolog日志

binlog：mysql自带的日志文件；

redolog：innodb插件引擎带的日志文件，支持磁盘顺序写日志，性能大大提供。

因为两种日志属于不同的组件，所以为了保证数据的一致性，要保证binlog和redolog一致，所以有了二阶段提交的概念。

（7）二级段提交redolog

![66a5b0802f2d4281bab51f6b7ad639c8.png][]

（8）同一个事务里面产生幻读的情况

当第一次执行select...时，会生成当前的快照记录，然后执行insert、update、delete、select ...for update语句后，此时的readview会失效，再执行select...时，会进行当前读，读取到最新的readview。

![b8574a02cdd24535a723bb6065bd3555.png][]

（9）长事务的影响

并发情况下，数据库连接池容易被撑爆；

锁定太多的数据，造成大量的阻塞和锁超时；

执行时间长，容易造成主从延迟；

回滚所需要的时间比较长；

undo log膨胀；

容易导致死锁。

#### 6.锁相关 ####

（1）从是否独占分为：读锁、写锁、意向锁

读锁（共享锁，S锁）：select ... lock in share mode;读锁是共享的，多个事务可以同时读取同一个资源，但不允许其他事务修改。

写锁（排它锁，X锁）;select ... for update;写锁是排他的，会阻塞其他的写锁和读锁，update、insert、delete都会加写锁。

意向锁（相当于表层面的全局变量）：分为意向共享锁、意向排他锁，当事务对某行加锁时，会在粒度更高的表锁上也加上意向共享锁或者意向排他锁，这样在其它事务需要获取表锁的时候，只有获取表的意向锁即可得到是否能获取到表锁；若是当前表为意向排他锁，则其它事务来获取表锁时，不用再去逐行判断是否有加排他锁，大大提升了获取表锁的效率。

（2）从锁粒度分为：表锁、行锁、记录锁、间隙锁、临键锁

表锁：上锁锁住的是整个表，当下一个事务访问该表时，必须等前一个事务释放了锁后才能进行对表的访问；特点：粒度大，加锁简单，容易冲突。

行锁：上锁锁住的是某一行或者多行记录，其它事务访问同一张表时，只有被锁住的记录不能访问，其它的记录可正常访问；特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高。

记录锁：记录锁也属于行锁的一种，只不过记录锁的范围只是表中的一行记录。

间隙锁：间隙锁是行锁的一种，间隙锁是在事务加锁后锁住的是记录的某一个区间，当表的相邻id之间出现空隙则会形成一个区间，遵循左开右闭原则，在innodb的可`重复读隔离级别`下，解决`幻读问题`产生的。

临键锁：临键锁也是行锁的一种，并且它是innodb的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住。

（3）从性能分为：悲观锁、乐观锁

悲观锁：就是对数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，别人会修改数据，所以在整个数据处理过程中，需要将数据锁定。悲观锁的实现，通常依靠数据库提供的锁机制实现，比如mysql的排他锁，select ... for update来实现悲观锁。

乐观锁：对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测。如果发现冲突了，则返回错误信息给用户，让用户自己决定如何操作。乐观锁不会刻意使用数据库本身的锁机制，而是依据数据本身来保证数据的准确性。乐观锁的实现：

①CAS（Compare and Swap）：比较和替换，在进行数据修改的时候，先比较之前获取的值跟当前的值是否一样，一样则刻意执行更新，不一样怎会导致cas失败，失败之后，进入一个无限循环while（true），再次获取值，接着执行cas操作，此种自旋的方式，会浪费一定的服务器性能

②版本号控制：数据表中加上一个版本号version字段，表示数据被修改的次数，当数据被修改时，version值会+1。当线程A要修改数据时，在读取数据的同时也会读取到version值，在提交更新时，若是刚才读取到的version值与当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

之所以会使用版本号控制是因为，cas的方式会存在ABA 问题，即某个记录的值，两个线程都加载到工作空间后，经过某个线程更新为新的值，然后再把值更新还原回去，而第二个线程进行更新时，比较值时发现还是等于一开始的值，虽然值没有变，但是已经不是最开始的版本了；使用版本号控制即可解决此问题。

#### 7.分库分表 ####

数据库和表的拆分都可以分为垂直拆分、水平拆分。

（1）垂直拆分

![81dbc321344244bc96d3ab2ad98b27cd.png][]

垂直分表示意图

![10e62f08aa1a4e8fbb56665863964d2f.png][]

![2b63ccc9c2db41f09c37414717a5749b.png][]

特点：

①每个库（表）的结构都不一样；

②垂直拆分的每个库（表）的数据都（至少有一列）一样；

③每个库（表）的并集是全量数据。

优点：

①拆分后业务清晰（专库专用按业务拆分）；

②数据维护简单，按业务不同放到不同的机器上。

缺点：

①如果单表的数据量大，写读压力大；

②部分业务无法关联join，因为需要关联的表放在不同的库下（ip不同），只能通过程序接口去调用，提高了开发复杂度。

（2）水平拆分

![7f54a422653849e9b40195def61ddfbf.png][]

水平分表示意图

![a339744bfb104adaa2e84e9d9ae09c74.png][]

特点：

①每个库（表）的结构都一样；

②每个库（表）的数据都不一样；

③每个库（表）的并集是全量数据。

优点：

①单库（表）的数据保持在一定的量（减少），有助于性能提高；

②提供了系统的稳定性和负载能力，不同的记录存在不同的库（表），若是有部分服务器挂了，但是正常的服务器还是能提供能力的；

③拆分的表结构相同，程序改造较少。

缺点：

①数据的库容很有难度，维护量大；

②拆分规则很难抽象出来；

③分片事务的一致性问题，部分业务无法关联join，只能通过程序接口去调用；例如用户订单这个功能，可以按用户id来分表，方便用户查询我的订单，但是若是想看某个商品的销售去向，则需要遍历所有的订单表来获取值。

（3）分库分表带来的问题

①分布式事务，acid

②跨库join查询

③分布式全局唯一ID

④开发成本高，对程序员要求高

[MySQL _ MySQL 8.0 Reference Manual _ 8.8.2 EXPLAIN Output Format]: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
[ec881769ecfa4471883111819d059372.png]: https://img-blog.csdnimg.cn/ec881769ecfa4471883111819d059372.png
[fa8739d1e8c849858908c0085941d8ad.png]: https://img-blog.csdnimg.cn/fa8739d1e8c849858908c0085941d8ad.png
[8aed8d3795364f739b29dcb42905a5d3.png]: https://img-blog.csdnimg.cn/8aed8d3795364f739b29dcb42905a5d3.png
[57cb02d14fe44954817ead61480c6e01.png]: https://img-blog.csdnimg.cn/57cb02d14fe44954817ead61480c6e01.png
[13078799a511438eb48618482713cd33.png]: https://img-blog.csdnimg.cn/13078799a511438eb48618482713cd33.png
[66a5b0802f2d4281bab51f6b7ad639c8.png]: https://img-blog.csdnimg.cn/66a5b0802f2d4281bab51f6b7ad639c8.png
[b8574a02cdd24535a723bb6065bd3555.png]: https://img-blog.csdnimg.cn/b8574a02cdd24535a723bb6065bd3555.png
[81dbc321344244bc96d3ab2ad98b27cd.png]: https://img-blog.csdnimg.cn/81dbc321344244bc96d3ab2ad98b27cd.png
[10e62f08aa1a4e8fbb56665863964d2f.png]: https://img-blog.csdnimg.cn/10e62f08aa1a4e8fbb56665863964d2f.png
[2b63ccc9c2db41f09c37414717a5749b.png]: https://img-blog.csdnimg.cn/2b63ccc9c2db41f09c37414717a5749b.png
[7f54a422653849e9b40195def61ddfbf.png]: https://img-blog.csdnimg.cn/7f54a422653849e9b40195def61ddfbf.png
[a339744bfb104adaa2e84e9d9ae09c74.png]: https://img-blog.csdnimg.cn/a339744bfb104adaa2e84e9d9ae09c74.png