MySQL基础复习

Posted on Edited on In

MySQL基础复习(一)

1、一条SQL查询语句的执行

MySQL的基本架构如下：

MySQL的逻辑架构图

模块介绍：

  1. Server层：
    1. 连接器：管理连接，权限验证；尽量使用长连接，但全部使用长连接后有时会导致OOM而MySQL重启，解决方法是5.7以上版本执行mysql_reset_connection 来重新初始化连接资源。
    2. 缓存：每次更新语句都会清空缓存，弊大于利，所以8.0版本以后此缓存功能已删除。
    3. 分析器：分析SQL语句，比如判断select关键字、字段等。
    4. 优化器：分析语句的执行方案，决定使用什么索引等。
    5. 执行器：先检查是否有该表的权限，接着调用引擎接口，一行行扫描是否符合条件，符合则将此行数据存入内存中。
  2. 存储引擎：
    1. 分InnoDB、MyISM等等，现在默认为InnoDB，因为其支持事务、行锁、外键等等特性。

2、一条SQL更新语句的执行

三种日志：

  1. redo log：InnoDB引擎独有，以物理记录的方式。SQL更新语句会先记录在redo log，然后更新内存，这样这条语句就执行完成了。接着等到引擎空闲的时候才会将操作记录更新到磁盘中。大小固定，循环的方式存储在磁盘上，一组四个文件，每个文件大小4GB，满了后就会先擦去(执行)语句。作用是保证数据库发生异常重启后，之前提交的记录不会丢失(crash-safe)。
  2. bin log：Server层中存储，以逻辑记录的方式，并没有内存限制，记录写满后会继续往下一页写。作用是备份数据(归档)，用来恢复数据(从库到主库)，或者扩容，没有crash- safe能力。
  3. undo log：MVCC(多版本并发控制)使用此日志，用来记录行数据更新日志，用来事务记录。

SQL更新语句的执行，以select * from T where id = 1这条语句为例：

  1. Server层一路到执行器，接着执行器通过引擎接口找到id=1这一行数据。如果有索引，则用搜索树内部结构找到。如果内存中有，则直接返回，否则先从磁盘中找到数据，刷到内存后，再从内存中取出。
  2. 执行更新语句，写入内存中。
  3. redo log记录，状态为prepared状态。
  4. 记录到bin log中。
  5. 更新redo log为commit状态。

img

为什么redo log和bin log要两阶段提交：

​ 尽量保证两者的一致性，分以下三种情况：

1. prepared阶段成功，写binlog时失败，此时发现没有commit，回滚，两个日志同样逻辑相同。
1. commit失败，但满足prepared成功和bin log也有记录，自动commit。

数据库备份恢复：

​ DBA一般能够恢复”半个月内”数据库的任何时刻，从而实现数据库恢复。实现方法是通过定时备份数据(从库)和bin log，假如要恢复到昨天12点的状态，找到距离这个时间之前最近的备份数据(比如可能昨天0点)，然后配合bin log(0点到12点的记录)，从而进行恢复。

推荐资料：链接

3、事务隔离

什么是事务？

事务的四大特性：ACID(原子性、持续性、隔离性和持久性)。

事务可能就出现的问题：脏读、不可重复读、幻读。后两者其实都一样，只是针对的语句类型不同，不可重复读是针对查询语句，幻读是针对删除插入语句。

事务的四种隔离级别：

  1. 读未提交(read uncommitted)：会出现以上三种问题。
  2. 读提交(read committed)：解决脏读。事务提交，其所做变更才会被其他事务看到。
  3. 可重复读(repeatable read)：进一步解决不可重复读。事务在执行中看的数据与其刚开始启动时的数据一样。MySQL默认隔离级别。
  4. 串行化(serializable)：进一步解决幻读。加读写锁，性能消耗大。

事务隔离的实现：

​ undo log记录事务的变更日志。在不需要的时候才删除，系统判断当没有事务再需要用到这些回滚日志时，回滚日志会被删除。

tips：

​ 长事务指的是一个流程过长的事务。尽量不要使用长事务。长事务意味着系统里面会存在很老的事务视图，而且这个事务提交之前，其回滚记录都必须保留，这就会导致大量占用存储空间。另外，长事务也会占用锁资源。

事务的启动方式：

1. 显式启动事务语句， begin 、start transaction、commit、rollback。
1. set autocommit=0。

推荐资料：链接

4、索引

索引的内部结构有哈希表、有序数组和多叉搜索树的形式。

InnoDB为了减少磁盘IO次数，使用的是**N叉搜索树(B+树)**的形式。

索引的分类：

​ 主键索引、唯一索引、联合索引和普通索引。

​ 主键索引：非叶子结点存储的是主键，叶子结点存储的是页(数据的存储单位，具体的说是行数据)。

​ 普通索引：非叶子结点存储的是索引条件，叶子结点存储的是主键，然后会去主键索引树进行回表查询。

​ 尽量使用主键索引，并且主键字段不要太长，这样占用空间较小。

覆盖索引：
不需要回表查询的二级(普通)索引。应用条件是普通索引树已经有我们需要的字段了，比如select后的字段已经在普通索引树上。

最左前缀原则：
B+树可以利用索引的“最左前缀”，来定位记录。索引项是按照索引定义里面出现的字段顺序排序的。比如当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了，这样就少维护了一个索引，即索引的复用。

(索引失效)

索引下推：

​ 在联合索引中，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

​

5、全局锁和表锁

全局锁：

​ 对整个数据库实例加锁，命令是 Flush tables with read lock (FTWRL)，使得整个数据库变成只读状态，其他增删改、定义语句被阻塞。一般用作全库备份时的操作，避免数据库不一致。

​

表级锁：

​ 分为表锁和元数据锁（meta data lock，MDL)。

​ 表锁：分读锁、写锁。写是排他锁，写锁意味着其他线程不能读也不能写。读锁是共享锁，加上后其他锁只能读不能写，本线程也不能写。InnoDB一般使用行锁，表锁影响较大。

​ MDL：不需要显式使用，在访问一个表的时候会被自动加上。同样是读写锁。系统默认添加。所有对表的增删改查操作都需要先申请 MDL 读锁。该锁直到事务提交才释放。

​ 给表加字段时会扫描全表，会使用MDL锁，可能会造成阻塞，即出现命令队列中一个语句占用锁时间过长，导致后面的语句被block，这个时候客户端如果有频繁重试的逻辑就会导致不停的和数据库建立连接，把连接池打满导致库不可用，使得数据库崩溃。

如何安全地给小表加字段：

​ 在 alter table 语句里面设定等待时间，过时则重试。

6、行锁

行锁是在引擎层由各个引擎自己实现的，InnoDB 是支持行锁。

行锁都是在 commit 的时候才释放的，即两阶段锁协议。

所以如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁和死锁检测：

出现条件：循环资源依赖。

解决策略：

  1. 进入等待，直到超时
  2. 死锁检测，发现死锁后主动回滚死锁链条中的某一个事务(正常情况下使用该策略)

死锁检测有弊端：检测时会扫描其他占用资源的所有线程。比如100个线程同时更新一行，那么会扫描100*100次，消耗大量的 CPU 资源，事务执行少。

限制死锁：只能尽量控制，一是客户端进行并发控制，但仍可能会出现，比如几个客户端各自限制同时最多三个线程，但汇总到数据库服务端仍会有大量线程；二是改中间件或者MySQL源码(?)；三是考虑通过将一行改成逻辑上的多行来减少锁冲突，使得命中同一行的可能性降低了。

7、MVCC、行锁、快照

MVCC事务举例图

​ 以上图的事务场景举例，我们假设事务C的update语句本身就是一个事务，语句完成的时候会自动提交。然后事务B更新后查询，事务A在这之后，只读。

​ 结论是事务B查的k的值是3，事务A查的k的值是1。

视图：

​ 我们说的视图目前有两种概念，一种是view，虚拟表；另一种是事务开启时生成的一致性视图，InnoDB使用MVCC创建而成，即 consistent read view，用于支持 RC（读提交）和 RR（可重复读）隔离级别的实现。

快照：

​ 之前说的MVCC会为每个事务生成快照，这个快照肯定不是真正的表数据，不然太大了，那它到底是什么呢？

​ 首先每行数据可能当下都位于多个事务中，那么每个事务启动时(可能在begin/start transaction时启动，也可能在执行语句时启动，PR前者，RC后者)都会被InnoDB事务系统生成一个唯一的transaction ID(按申请顺序严格递增)，且每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id，所以说每行数据此时此刻可能有多个row trx_id。

​ 根据这个ID，MVCC结合undo log，能够”溯源”，找到以前提交的版本，从而实现”快照”，或者说”视图”的概念。

​ 每个事务开启时，都会有对应的视图数组，表示的是它通过MVCC找到的、它自己承认的、已提交事务的row trx_id集合，即快照。

img

​ 每个事务读取的数据，绿色的表示之前已提交的事务，红色部分表示的是将来要启动的事务，黄色部分表示一些是在它视图数组里的已提交的事务，另一部分表示不在它视图数组里的未提交的事务。事务能看到的只是绿色和黄色中已提交部分的。

​ 回到之前的事务举例，显然对于隔离级别PR，事务A启动时已生成快照，根据一致性读，查出的k还未改变，所以当然是1。

​ 此外对于事务B，因为其是更新语句，因为要保证其他事务的更新对他有影响，所以有这条规则：更新数据都是先读后写的，并且只能读当前的值，称为“当前读”（current read）。读的是当前事务的数据，所以一开始读的时候k就为2了。除了 update 语句外，select 语句如果加锁，也是当前读。

​ 假设事务C的commit在事务B的更新语句之后，会因为行锁的原因，事务B必须等待事务C的commit。事务 B 是当前读，必须要读最新版本，而且必须加锁。

​ **可重复读的核心就是一致性读(consistent read)**；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

​ 读提交与PR的区别是，每一个语句执行前会重新算出一个新的视图。这样按照例子，事务A读的是k的值是2，事务B是3。

8、扩展：2pc和3pc

2pc(两阶段提交)，3pc(三阶段提交)。

推荐资料：链接