MySQL概要——《深究MySQL》

ゝ一纸荒年。 2022-06-05 12:25 196阅读 0赞

# 1. MySQL架构 #

1.  MySQL的存储引擎架构将**查询处理**等系统任务和**数据的存或取**相分离。这种分离设计使得可根据不同的需求选择不同的存储方式。
2.  存储引擎不会解析SQL，InnoDB例外，它会解析外键定义，因为MySQL服务器本身没有实现该功能。
3.  MySQL会解析查询并创建内部数据结构（解析树），然后对其进行重写查询、决定表的读取顺序、选择合适的索引等优化。

# 2. 锁 #

1.  读锁（也叫共享锁），互不干扰。只有没有写锁时，其他用户才能获得读锁。
2.  写锁（也叫排他锁），会阻塞其他的读锁和写锁。
3.  锁的各种操作，包括获得锁、检查锁是否已经删除、释放锁等都会增加系统开销。
4.  锁策略：在锁的开销和数据安全性之间寻求平衡。
5.  每种MySQL存储引擎都能自定义锁策略和锁粒度。
6.  表锁：会销定整张表，它是MySQL中最基本的锁策略，也是开销最小的锁策略。
7.  行级锁：支持最大程度的并发处理（开锁也大），它只在存储引擎层实现，而在MySQL服务层中没有实现。
8.  死锁：两个或者多个事务都在请求锁定对方占用的资源的现象。
9.  **解决死锁办法：部分全部回滚其中一个事务**
10. 死锁示例：当两个事务同时执行完第一句SQL，紧接着执行第二句SQL时就会发现资源被对方占用
    
    > 事务1：  
    > start transaction  
    > update user set age = 20 where userId=3;  
    > update user set age = 20 where userId=4;
    > 
    > 事务2：  
    > start transaction  
    > update user set age = 20 where userId=4;  
    > update user set age = 20 where userId=3;
11. 锁只有在事务commit（提交）或rollback（回滚）的时候才会释放，且所有的锁都在同一时刻释放。

# 3. 事务 #

## 3.1 事务的几个特性 ##

1.  原子性（atomicity）: 一个事务是一个最小的逻辑执行单元
2.  一致性（consistency）: 数据库数据在逻辑上要保持一致。如一个用户转出了200，那另一个用户必须增加200元。
3.  隔离性（isolation）: 一个事务的修改在最终提交之前，其他事务是不可见的。即不能影其他事务。
4.  持久性（durablility）: 事务最终提交之后，修改就要被永远保存在数据库中，而不会像回滚一样能撤销。

## 3.2 隔离级别 ##

1.  **read uncommitted**（读未提交）：一个事务可以读到另一个事务未提交的数据。会有“脏读”、“不可重复读”、“幻读”的问题。
2.  **read committed**（读提交：大多数据库默认级别）: 一个事务只能读到另一个事务提交后的修改。会有“不可重复读”、“幻读”的问题。
3.  **repeatable read**（可重复读:**MySQL默认级别**）：在一个事务中多次读取同一个记录时，结果一样。  
    【这是因为该事务锁住了这条数据记录，其他事务不能作任何修改（但可读），所以多次读取同一记录时结果一样】  
    会有“幻读”的问题。**幻读**指的是某个事务两次读取**某范围内**的数据时，数据不一致。
4.  **serializable**：事务会串行执行。此时会在读取的每一行数据都加锁，就会可能导致超时和争锁的问题。

低的隔离级别可以执行更高的并发，系统开销也相对较低。

## 3.3 MySQL中的事务 ##

1.  MySQL中的两种事务型存储引擎：InnoDB、NDB Cluster
2.  DDL(create alter drop等)、DCL（grant,deny,revoke等与用户角色权限相关操作）会强制自动提交事务，**DML**（select、update、insert、delete等）需要手动提交事务

## 3.4 事务日志 ##

1.  作用：提高事务的效率
2.  原理： 存储引擎在修改表的数据时，只会修改其内存拷贝，然后把该**修改操作**持久到硬盘上的事务日志中，而不用每次都将修改的**数据本身**持久到磁盘上。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。此方式称之为，**预写式日志**，修改数据需要写两次磁盘。
3.  快的原因：事务日志采用的是追加的方式，写事务日志的操作是磁盘上的**顺序I/O**，而不像随机I/O要在磁盘上多个地方移动磁头。
4.  另一个优点：如果数据的修改已经纪录到事务日志中，但数据本身还没有写回磁盘，如果此时系统崩溃，存储引擎在重启时，能够自动恢复这部分修改的数据
5.  开启事务日志：待查？？？？？

# 4. 多版本并发控制（MVCC: multi-version concurrency control） #

1.  MVCC定义：多版并发控制系统。可认为是行级锁的一个变种，它能够避免更多情况下的加锁操作。
2.  作用：避免一些加锁操作，提升并发性能。
3.  实现：通过在每行记录的后面保存行的**创建时间**和**过期时间或删除时间**（它们是隐藏的），这两个时间实际都是系统的版本号。**每开始一个新的事务，版本号都会自动增加**。
4.  **具体原理**  
    4.1） **select**：innoBD查询时会检查以下**两个条件**：一个是数据行的版本号早于当前事务的版本号；另一个是行的删除版本号，要么没有，要么大于当前事务的版本号。
    
    4.2）**insert/delete**：innoDB将当前的系统版本号作为新插入(删除)的数据行的版本号。
    
    4.3）**update**：先新插入一行数据，并将当前系统版本号作为行的版本号，同时将当前系统版本号作为原来行的删除版本号。
5.  **注意**：MVCC只在read committed和repeatable read两个隔离级别下工作。