# mysqlStudy

**Repository Path**: zhayan/mysql-study

## Basic Information

- **Project Name**: mysqlStudy
- **Description**: mysql学习记录
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2022-01-10
- **Last Updated**: 2022-01-20

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# mysqlStudy

## 架构原理

### 体系架构

#### 网络连接层

客户端连接器，提供与MYsql服务器连接建立支持。

#### 服务层

- 连接池（Connection Pool）

  负责存储和管理客户端与数据的连接，一个线程负责管理一个连接

- 系统管理和控制工具（Management Services & Utilies）
  备份恢复、安全管理、集群管理等


- SQL接口（Sql Interface）
  用于接受客户端发送的各种sql命令，并且返回用户的查询结果。DML/DDL/存储过程、识图、触发器等。


- 解析器（Parser）
  负责将请求的SQL解析生成一个“解析树”，返回根据mysql规则检查解析数是否合法


- 查询优化器（Optimizer）
  当“解析树”通过解析器语法检查后，将交由优化器将其转化为执行计划，然后与存储引擎交互。


- 缓存（Cache&Buffer）
  存储机制由一系列小缓存组成，如表缓存、记录缓存、权限缓存、引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据

#### 存储引擎层（Pluggable Storage Engines）

负责MYSQL中数据的存储与提取，与底层系统文件进行交互。插件模式，服务器中的查询执行引擎通过接口与存储引擎进行通讯，接口屏蔽了不同存储引擎之间的差异。

#### 系统文件层（File System）

负责将数据库的数据和日志存储在文件系统上，并完成与存储引擎的交互，是文件的物理存储层。包含：日志文件、新数据文件、配置文件、pid文件、socket文件等。

1. 日志文件：

   - 错误日志（error log）：

     ​默认开启，`show variables like '%log_error%';`

   - 通用查询日志（general query log）：
     记录一般查询语句，`show variables like '%general%';`


   - 二进制日志（binary log）：
     记录对mysql数据库执行的更改操作，并记录了语句的发生时间，执行时长；但是不记录select、show等不修改数据库的sql。主要用于数据库恢复和主从复制。
     `show variables like '%log_bin%'; //是否开启`

     `show variables like '%binlog%'; //参数查看`

     `show binary log; //查看日志文件`

   - 慢查询日志（Slow query log）

     记录所有执行时间超时的SQL，默认10秒。
     `show variables like '%slow_query%'; //是否开启`

     `show variables like '%long_query_time%'; //时长`

2. 配置文件：用于存储myssql富哦you的配置信息文件，my.cnf、my.ini等

3. 数据文件：

   - dp.opt：记录库默认使用的字符集和校验规则。
   - frm：存储与表相关的源数据信息，表结构的定义信息，每一张表有一个frm文件。
   - MYD文件：MyISAM存储引擎专用，存放MyISAM表的数据，每一张表有一个。MYD文件。
   - MYI文件：
   - ibd文件和IBDATA文件：存放InnoDB的数据文件，包括索引。ibd独享表空间文件，每张表一个；ibdata共享表空间文件，多表可共享一个.ibdata文件。
   - ibdata1文件：系统表空间数据文件，存储表源数据、Undo日志等。
   - ib_logfile0、ib_logfile1文件：Redo log日志文件。

4. pid文件
   pid文件是mysqld应用程序在Unix、Linux环境下的一个进程文件，存放自己的进程id。

5. socket文件
   Unix/Linux环境下，可以通过Unix Socket连接mysql。

### 运行机制

![执行过程](img/执行过程.png)

`show processlist; //查看用户正在运行的线程信息`
`show variables like '%query_cache%'; //查询缓存是否开启信息`

`show status like 'Qcahe%'; //查看缓存配置详情`

### 存储引擎

`show engines; //查看当前数据库支持的存储引擎`

- Innodb：支持事务，可提交、回滚、崩溃恢复；事务安全。5.5之后默认。
- MyISAM：不支持事务和外键，访问速度快。hibernate_sequence自增表就是。5.5版本之前默认。
- Memory：内存表，默认使用hash索引，访问速度快，但停机数据丢失。
- Archive：归档类型引擎，仅支持insert、select语句
- Csv：已csv文件进行数据存储，所有列必须强制指定not null，且不支持索引和分区，适合做数据交换的中间表
- BlackHole：黑洞，只进不出，数据不进行保存
- Federated：可以访问远端mysql数据库中的表。一个本地表，不保存数据，访问远端表数据
- MRG_MyISAM：一组MyISAM表的组合，表结构相同；Merge表本身没有数据，对Merge按操作可以对一组MyISAM表进行操作。

#### InnoDB和MyISAM

|        | InnoDB                          | MyISAM                                   |
| ------ | ------------------------------- | ---------------------------------------- |
| 事务和外键  | 支持，具有安全性和完整性。                   | 不支持；提供高速存储和检索                            |
| 锁机制    | 支持行级锁，锁定指定记录。基于索引来加锁实现。         | 表级锁，锁定整张表                                |
| 索引结构   | 聚集（聚簇）索引，索引和记录在一起存储。            | 使用非聚集（聚簇）索引，索引和记录分开。                     |
| 并发处理能力 | 读写阻塞与隔离级别有关，采用MVCC多版本并发控制来支持高并发 | 使用表锁，写操作并发率低；读之间并不阻塞，读写阻塞。               |
| 存储文件   | 两个文件：frm表结构、ibd数据文件，最大支持64TB    | 三个文件：frm表结构、myd表数据结构、myi索引文件。5.0开始默认限制256TB |
|        |                                 |                                          |

#### InnoDB存储结构

![存储结构图](img/innodb结构.png)

##### 内存结构

- Buffer Pool：缓冲池
  以Page也为单位，默认16K；底层使用链表管理Page；在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO操作，提升效率。

  - Page管理机制

    | page分类（状态） | 描述                                 |
    | ---------- | ---------------------------------- |
    | free page  | 空闲page，未被使用                        |
    | clean page | 被使用page，数据没有被修改过                   |
    | dirty page | 脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致 |

    | 链表结构链表结构   | 描述                                       |
    | ---------- | ---------------------------------------- |
    | free list  | 空闲缓冲区，管理free page                        |
    | flush list | 需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序。<br />脏页即存在flush链表，也在LRU链表中，但互不影响；LRU链表负责管理page的可用性和释放；flush链表负责管理脏页的刷盘操作 |
    | lru list   | 正在使用的缓冲区，管理clean/dirty page，缓冲区以midpoint为基点。前面链表称为new列表区，存放经常访问的数据，占63%；后面的链表称为old列表区，存放使用较少的数据，占37% |

  - 改进型LRU算法维护

    | LRU类型  | 描述                                       |
    | ------ | ---------------------------------------- |
    | 普通LRU  | 末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰              |
    | 改进型LRU | 链表分为new和old两部分。加入元素是从中间midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动；如果数据没有被访问，会逐步向old尾部移动，等待淘汰 |

    每当有新的page数据读取到buffer pool时，InnoDB引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。**如果空间均不足呢？？**

  - Buffer Pool配置参数
    建议：**将innodb_buffer_pool_size设置为总内存大小的60-80%，innodb_buffer_pool_intances可以设置为多个，避免缓存争夺。**

    | `show variables like '%innodb_page_size%'; //查看page页大小` |
    | ---------------------------------------- |
    | `show variables like '%innodb_old%'; //查看lru list中old列表参数` |
    | `show variables like '%innodb_buffer%'; //查看buffer pool参数` |

- Change Buffer写缓冲区CB
  在进行DML操作时，如果没有其响应的Page数据，并不会like将磁盘页加载到缓冲池，而是在CB中记录缓冲变更，等未来数据被读取时，再将数据合并恢复到BP中。
  占用Buffer Pool空间，默认占25%，最大允许占50%，`innodb_change_buffer_max_size`

  当更新一条数据时。该记录在Buffer Pool中存在，则直接在Buffer Pool修改，一次内存操作；如果不在BP中，直接在ChangeBuffer中进行一次内存操作，不再去磁盘查询，避免一次磁盘IO。当下次记录查询时，会进行磁盘读取，然后再从CB中读取信息合并，最终载入BufferPool中。

  **写缓冲区，仅适用于非唯一普通索引页：**

  - 如果再索引设置唯一性，在修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。会直接将记录查询到BufferPool中，然后再缓冲池修改，不会在ChangeBuffer操作。

- Adaptive Hash Index自适应哈希索引
  用于优化对BP数据的查询。InnoDB存储引擎会监控对表索引的查找，如果观察到建立hash索引可以带来速度的提升，则会建立hash索引，所以称之为自适应。自动根据访问的频率和模式来为某些页建立hash索引

- Log Buffer日志缓冲区

  用于保存要写入磁盘上log文件（redo/undo）的数据，内容定期刷到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘IO。

  主要用于记录InnoDB引擎日志，在DML操作时会产生redo/undo日志。

  空间满了，会自动写入磁盘。`innodb_log_buffer_size`参数调大可以减少磁盘IO频率。

  `innodb_flush_log_at_trx_commit`控制日志刷新行为，默认为1。

  | 参数   | 描述                                       |
  | ---- | ---------------------------------------- |
  | 0    | 每隔1秒写日志文件和刷盘操作。写日志文件LogBuffer -> OS cache，刷盘OS cache -> 磁盘文件；最多丢失1秒的数据 |
  | 1    | 事务提交，立交写日志文件和刷盘，数据不丢失，但会频繁IO             |
  | 2    | 事务提交，立刻写日志文件，每隔1秒进行刷盘操作                  |

##### 磁盘结构

- Tablespaces表空间
  用于存储表结构和数据。

  | 表空间类别                           | 描述                                       |
  | ------------------------------- | ---------------------------------------- |
  | 系统表空间The System Tablespace      | 包括InnoDB数据字典，Doublewrite Buffer，Change Buffer，Undo Logs的存储区域。默认包含任何用户在系统表空间创建的表数据和索引数据。是一个共享的表空间，被多个表共享。<br />`innodb_data_file_path`，默认值ibdata:12M:autoextend，文件名ibdata1、12M、自动扩展 |
  | 独立表空间File-Per-Table Tablespaces | 单表表空间，创建于自己的数据文件中。当`innodb_file_per_table`选项开启时，表将被创建于表空间中。否则创建于系统表空间中。每个表文件表空间由一个`.ibd`数据文件代表，默认被创建于数据库目录中。支持动态和压缩行格式。 |
  | 通用表空间General Tablespaces        | 通过`create tablespace`创建共享表空间。可以创建mysql数据目录外的其他表空间，可以容纳多张表，且支持所有的行格式。 |
  | 撤销表空间Undo Tablespaces           | 由一个或多个包含undo日志文件组成。5.7版本之前Undo占用System Tablespace共享区，之后分离。<br />`innodb_undo_tablespaces`控制，默认0；0，表示使用系统表空间ibdata1；大于0表示使用undo表空间undo_001/undo_002等 |
  | 临时表空间Temporary Tablespaces      | 分为session/global temporary tablespace；前者存储用户创建的和磁盘内部的临时表；后者存储用户临时表的回滚段（rollback segments）。mysql服务器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。 |

- 数据字典InnoDB Data Dictionary
  由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据。元数据物理上位于InnoDB系统表空间中。历史原因，数据字典元数据一定程度上与frm文件信息重叠。

- 双写缓冲区Doublewrite Buffer
  一个存储区域。在BufferPool的page页刷新到磁盘真正位置前，会先在该缓冲区存储。如果page写入过程中出现操作系统、存储子系统、mysqld进程奔溃，InnoDB可以再奔溃恢复期从该缓冲区中找到页面的备份。

  `innodb_doublewrite=0 //禁用`
  开启时建议将`innodb_flush_method`设为O_DIRECT；控制innodb数据文件及redo log的打开、刷写模式。

  | fdatasync | 默认；先写入操作系统缓存，然后再调用fsync()函数去异步刷写数据文件与redo log的缓存信息。 |
  | --------- | ---------------------------------------- |
  | O_DSYNC   |                                          |
  | O_DIRECT  | 数据文件先写入操作系统会通知操作系统不要缓存数据，也不要用预读，直接从InnoDB buffer写到磁盘文件 |

- 重做日志Redo Log

  用于崩溃恢复期间更正不完整事务写入的数据。mysql以循环方式写入重做日志文件，记录InnoDB中所有对Buffer pool修改的日志。当出现实例故障，导致数据未能更新到数据文件，则数据库重启时需undo，重新把数据更新到数据文件。读写事务执行时都会产生redo log。默认情况下，以ib_logfile0、ib_logfile1物理文件表示。

- 撤销日志Undo Logs
  是在事务开始前保存的被修改数据的备份，用于例外情况的回滚事务。撤销日志属于逻辑日志，根据每行记录进行记录。存在于系统表空间、撤销表空间和临时表空间中。

#### InnoDB线程模型

![](img/innodb线程模型.png)

| 类型                  | 描述                                       |
| ------------------- | ---------------------------------------- |
| IO Thread           | AIO，提高性能。<br />write thread：负责写，将缓存脏页刷新到磁盘，4个<br />read thread：负责读取，将数据从磁盘加载到缓存page页，4个<br />insert buffer thread：负责将写缓存内容刷新到磁盘，1个<br />log thread：负责将日志缓冲区内容刷新到磁盘，1个 |
| Purge thread        | 事务提交后，回收已经分配的不再需要的undo页。<br />`show variables like '%innodb_purge_threads%';` |
| Page cleaner thread | 将脏数据刷新到磁盘，刷盘后redo log可以被覆盖，所以即可以同步数据，又能redo log循环使用。会调用write thread处理。<br />`show variables like '%innodb_page_cleaners%';` |
| Master Thread       | mysql主线程，负责调度其他各线程，优先级最高。作用是将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。包括：脏页的刷新、undo页回收、redo日志刷新、合并写缓冲等。<br />内部两个主处理，分别每隔1秒、10秒处理。<br />每隔一秒的操作：<br />- 刷新日志缓冲区到磁盘<br />- 合并写缓冲区数据，根据IO读写压力来决定是否操作<br />- 刷新脏页数据到磁盘，达到75%才操作。`innodb_max_dirty_pages_pct`和`innodb_io_capacity`<br />每隔10秒的操作：<br />- 刷新脏页数据到磁盘<br />- 合并写缓冲区数据<br />- 刷新日志缓冲区<br />- 删除无用的undo页 |



#### InnoDB数据文件

##### 文件存储结构

![](img/innodb文件存储结构.png)

##### 文件存储格式

`show table status;`

##### File文件格式

- Antelope：5.6及以前，提供行格式：COMPACT/REDUNDANT
- Barracuda：5.7开始，支持之前的行格式并提供新的：COMPRESSED/DYNAMIC

##### Row行格式

每个表的数据分成若干页来存储，每个页中采用B树结构存储。如果某些字段信息过长，无法存储在B树节点中，这时被单独分配空间，称为溢出页。

`ALTER TABLE [table_name] ROW_FORMAT=格式类型;`

| 行格式        | 描述   |
| ---------- | ---- |
| REDUNDANT  |      |
| COMPACT    |      |
| DYNAMIC    |      |
| COMPRESSED |      |

#### Undo Log

撤销或取消，以撤销操作为目的，返回指定某个状态的操作。

- 在事务开始前产生；事务提交时，不会立马删除，innodb将undo log放入删除列表中，后面通过后台线程purge thread进行回收。
- 逻辑日志：如执行delete，undolog中记录一个insert；执行update，undolog中记录一个反向的update。
- 采用段的方式管理和记录。rollback segment回滚段，内部包含1024个undo log segment。`show variables like'%innodb_undo%';`

作用：

- 实现事务原子性：事务执行失败则rollback，mysql利用undo log恢复到事务开始之前的状态
- 实现多版本并发控制MVCC：事务未提交前，undo log保存了未提交之前的数据版本，可以提供给其他并发的事务进行快照读。

#### Redo Log

重做恢复，以恢复操作为目的。事务中修改的任何数据，将最新的数据备份存储。

- 随着事务操作的执行，就会生成redo log，在事务提交时会将尝试的redo log写入log buffer，并不随事务提交立刻写入磁盘文件。等事务操作的脏页写入磁盘后，占用的空间可以重重（覆盖写入）


- 为了实现事务的持久性：防止在发生故障的时间点，尚有脏页未写入表的IBD文件中，在重启mysql服务的时候根据Redolog进行重做，达到持久化。
- 文件内容以顺序循环的方式写入文件，写满时回溯到第一个文件，进行覆盖写。

相关配置：每个innodb引擎至少有一个重做日志文件组group，每个文件组至少有2个重做日志文件，`ib_logfile0/ib_logfile1`，查看`show variables like '%innodb_log%';`。

redo buffer持久化策略：`innodb_flush_log_at_trx_commit`。

| 参数   | 描述                                       |
| ---- | ---------------------------------------- |
| 0    | 每秒提交Redo buffer -> os cache -> flush cache to disk，可能丢失一秒内的事务数据。master线程每1秒操作。 |
| 1    | 默认值。每次事务提交执行Redo buffer -> os cache -> flush cache todisk，最安全性能最差。 |
| 2    | 事务每次提交执行Redo buffrt -> os cache，然后master每隔1秒执行os cache -> flush to disk。一般选该值。 |

#### Binlog

binary log二进制日志，属于mysql server自己的日志。

- 记录所有数据库表结构变更以及表数据修改的二进制日志，不包括select、show等类似操作。以事件形式记录，包括执行的消耗时间。

- 使用场景：主从复制、数据恢复。

- 文件名：默认`主机名_binlog-序列号`，如oak_binlog-000001

  | 记录模式                                 | 描述                                       |
  | ------------------------------------ | ---------------------------------------- |
  | ROW-row based replication            | 日志中记录每一行数据被修改的情况，然后再slave端对相同的数据进行修改。    |
  | STATMENT-statement based replication | 每一条被修改数据的sql都记录到master的Binlog中，slave复制的时候sql进程会解析成和原理master端执行过相同的sql再次执行。简称sql语句复制。 |
  | MIXED-mixed-based replication        | 混合模式。 一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式 |

binlog对应不同的事件：query event、row event、xid event。

写入机制：

文件操作：



## 索引原理

### 索引类型

| 索引类型 | 描述   |
| ---- | ---- |
| 普通索引 |      |
| 唯一索引 |      |
| 主键索引 |      |
| 复合索引 |      |
| 全文索引 |      |

### 索引原理

- 二分查找法
- hash结构
- B+树
  - 聚簇索引
  - 二级（辅助）索引
  - 非聚簇索引
- B-树

### 索引分析与优化

#### explain

- select_type：查询的类型
  - SIMPLE：查询语句不包含子查询或union
  - PRIMARY：此查询是最外层的查询
  - UNIOM：此查询是UNION的第二个或后续查询
  - DEPENDENT UNION
  - UNION RESULT
  - SUBQUERY:
  - DEPENDENT SUBQUERY:
- type：存储引擎查询数据时采用的方法
  - ALL：全表扫描，性能最差
  - index：基于索引的全表扫描，先扫描索引再全表扫描
  - range：索引范围查询。使用>、>=、<、<=、in等
  - ref：使用唯一索引进行单值查询
  - eq_ref：一般为多表join查询，前面表的每一个记录，都只能匹配后面表的一行结果
  - const：使用主键或唯一索引做等值查询，常量查询
  - NULL：不用访问表，速度最快
- possible_keys：可能用到的索引
- key：真正使用到的索引名称
- rows：估算结果需要扫描的记录行数，越少效率越高
- key_len：传使用索引的字节数量。可以判断是否全部使用了组合索引
- Extra
  - Using where：查询需要通过索引回表查询数据
  - Using index：通过索引就能查询到数据
  - Using filesort：查询出来的结果需要额外排序，数据量小在内存，大则在磁盘中，可进行优化
  - Using temprorary：查询使用了临时表，一般出现与去重、分组等操作
  - index condition pushdown

#### 分析类型

| 类型     | 描述                                       |
| ------ | ---------------------------------------- |
| 回表查询   | 辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低 通过索引查询主键值，然后再去聚簇索引查询记录信息 |
| 覆盖索引   | 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。 将被查询的字段，建立到组合索引 |
| 最左前缀原则 | 最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效 |
| LIKE查询 | MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。 |
| Null查询 | 可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为NUL |
|        |                                          |

#### 索引与排序



### 查询优化

#### 慢查询定位

开启慢查询日志

`show variables like 'slow_query_log%';`

```
set global slow_query_log = ON;
set global slow_query_log_file = '***.log';
set global log_queries_not_using_indexes = ON; // 记录没有使用索引的查询
set long_query_time = 10; // 单位秒
```

查看工具mysqldumpslow

```
// mysql bin目录下执行-windows需要perl额外工具
perl mysqldumpslow.pl --help

perl mysqldumpslow.pl -t 5 -s at path[slow.log]
	-t N，top n，返回前面多少条数据
	-s，order顺序， al平均锁定时间
				  ar平均返回记录时间
				  at平均查询时间（默认）
				  c计数
				  l锁定时间
				  r返回记录
				  t查询时间
	 -g，正则匹配模式，大小写不敏感
```

#### 慢查询优化

- 是否慢查询：sql执行时间 > long_query_time
- 是否应用了索引：explain，key是否为null
- 应用索引是否row减少（索引过滤性）：扫描少则快，若row不变则索引无效

提高索引过滤性：

- 索引字段差异要大：
- 增加虚拟列：`alter table [table_name] add first_name varchar(2) generated always as (left(name, 1), add index(first_name, age));`

总结：

- 全表扫描：explain分析中type属性为all
- 全索引扫描：explain分析中type属性index
- 索引过滤性不好：靠索引字段选型、数据量和状态、表设计
- 频繁回表查询：尽量少用select *，尽量保证索引覆盖

#### 分页查询优化

`select * from [table_name] limit [offset],[rows]`

- 偏移量offset
- 返回记录行数rows

优化方案：

- 利用索引覆盖，select尽量取想要的值
- 利用子查询优化：让子查询走索引（覆盖），主查询尽量走索引


## 事务与锁

### ACID特性

### 事务控制

| 事务并发  | 描述                                       |
| ----- | ---------------------------------------- |
| 更新失败  | 多个事务更新同一行记录产生丢失现象。<br />- 回滚覆盖：一个事务回滚，把其他已提交事务覆盖。<br />- 提交覆盖：一个事务提交，把其他已提交事务覆盖 |
| 脏读    | 一个事务读取到了另一个事务修改但未提交的数据                   |
| 幻读    | 一个事务中多次按相同条件查询，结果不一致。后续结果或多或少            |
| 不可重复读 | 一个事务中多次读取同一行记录，前后读取不一致                   |

#### MVCC

multi version concurrency control多版本并发控制。

- 读读并行
- 读写并行
- 写读并行
- ~~写写并行无法支持~~：可以使用乐/悲观锁

读操作：

- 快照读Snapshot Read
  读取记录的快照版本，不用加锁
- 当前读Current Read
  读取记录的最新版本，并且当前读返回的记录都会加锁，保证其他事务不再并发修改这条记录。`select ... for update` `lock in share mode` `insert/delete/update`

行记录的隐藏列：

- db_row_id：行id，用于生成默认的聚集索引
- db_trx_id：事务id，最后一个对数据操作的事务ID
- db_roll_pt：回滚指针，执行该记录的Undo log信息。

### 隔离级别

#### 类型

| 隔离级别 | 回滚覆盖 | 脏读   | 不可重复读 | 提交覆盖 | 幻读   |
| ---- | ---- | ---- | ----- | ---- | ---- |
| 读未提交 | X    | 可能发生 | 可能发生  | 可能发生 | 可能发生 |
| 读已提交 | X    | X    | 可能发生  | 可能发生 | 可能发生 |
| 可重复度 | X    | X    | X     | X    | 可能发生 |
| 串行化  | X    | X    | X     | X    | X    |

- 读未提交
  Read Uncommitted。可能读取到其他事务中未提交的数据，**导致脏读**

- 读已提交
  Read Committed。只能读取到其他事务中已提交的数据；**解决脏读**；但可能一个事务中多次查询结果不一致，**导致不可重复读**

- 可重复读（mysql默认级别）

  Repeatable Read。**解决不可重复读**；**可能导致幻读**，即读取到额外的数据行。

- 串行化
  强制事务排序，**解决幻读**；可能导致大量超时和锁竞争，效率最低

#### 控制

`show variables like 'tx_isolation';`

`select @@tx_isolation`

```
// 设置
set tx_isolation='READ-UNCOMMITTED';
set tx_isolation='READ-COMMITTED';
set tx_isolation='REPEATABLE-READ';
set tx_isolation='SERIALIZABLE';
```

### 锁机制

#### 锁分类

|        | 行锁   | 表锁   | 页锁   |
| ------ | ---- | ---- | ---- |
| MyISAM |      | √    |      |
| BDB    |      | √    | √    |
| InnoDB | √    | √    |      |



| 操作类型    | 描述                                       |
| ------- | ---------------------------------------- |
| S锁-读锁   | 共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。<br /> 事务A对记录添加了S锁，可以对记录进行读操作，不能做修改，其他事务可以对该记录追加S锁，但是不能追加X锁，需要追加X锁，需要等记录的S锁全部释放。 |
| X锁-写锁   | 排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁。<br /> 事务A对记录添加了X锁，可以对记录进行读和修改操作，其他事务不能对记录做读和修改操作。 |
| IS锁、IX锁 | 意向读锁、意向写锁，属于表级锁，S和X主要针对行级锁。在对表记录添加S或X锁之前，会先对表添加IS或IX锁 |

|      | 描述                                       |
| ---- | ---------------------------------------- |
| 乐观锁  | 一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息 |
| 悲观锁  | 在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴 |

#### 锁原理

InnoDB行锁是通过对索引数据页上的记录加锁实现的

| 算法             | 描述                                       |
| -------------- | ---------------------------------------- |
| RecordLock锁    | 锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）             |
| GapLock锁       | 间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）  |
| Next-key Lock锁 | 记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持） |

#### 悲观锁

#### 乐观锁

#### 死锁与解决方案



## 集群架构

### 架构设计

- 可用性-冗余
  双主+主从模式
  - 站点高可用，冗余站点
  - 服务高可用，冗余服务
  - 数据高可用，冗余数据
- 扩展性
  - 提高读性能：加从库，分库分表
  - 提高写性能：分库分表
- 一致性
  - 不适应从库
  - 增加访问路由层

### 主从模式

用途

- 实时灾备，用于故障切换-高可用
- 读写分离，提高查询服务-读扩展
- 数据备份，避免影响业务-高可用

部署条件

- 从库服务器能联通主库
- 主库开启binlog日志（设置log-bin参数）
- 主从server-id不同

#### 实现原理

![](img/mysql主从复制.png)

三个步骤：

1. 主库将数据库变更的操作记录到binlog日志文件中
2. 从库读取主库的binlog文件，写入到从库的relay log中继日志中
3. 从库读取中继日志信息，在从库中进行replay，更新从库数据信息

涉及线程：

1. master服务器对数据库更改操作记录在binlog中，binlog dump thread街道写入请求后，读取binlog信息推送给slave的I/O thread。
2. slave的I/O thread将读取到的binlog信息写入到本地relay log中
3. slave的sql thread检测到relay log的变更请求，解析relay log中内容在从库上执行

主从复制存在的问题：

- 主库宕机，数据可能丢失 -> 半同步复制
- 从库只有一个SQL Thread，主库写压力大，复制很可能延时 -> 并行复制

半同步复制：

- 传统主从复制
- 半同步
- 增强半同步

#### 并行复制

5.6版本后追加并行复制功能，enhanced multi-threaded slave-MTS

### 双主模式

### 分库分表