MySQL进阶：MySQL基本架构图

参考：小林 coding、《MySQL 实战 45 讲》、《MySQL 技术内幕》、检索整理。

MySQL 基本架构图

Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器、查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在 Server 层实现。

存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB；从 MySQL 5.5 版本开始，InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的；不同的存储引擎支持的索引类型也不相同，比如 InnoDB 默认使用 B+ 树索引，在数据表中创建的主键索引和二级索引默认也是 B+ 树索引。

连接与会话

MySQL 基于 TCP 协议传输。

• SHOW PROCESSLIST;：查看当前有多少客户端连接。
• 最大空闲时长：SHOW VARIABLES LIKE 'wait_timeout';。空闲连接超过该时间会被连接器断开；默认约 8 小时（28800 秒）。
• 最大连接数：SHOW VARIABLES LIKE 'max_connections';。

查询缓存

查询缓存失效很频繁：只要对某表有更新，该表上的查询缓存会被清空。

• MySQL 8.0 起已移除查询缓存：MySQL 8.0: Retiring support for the query cache

解析器

解析器会做如下两件事情。

第一件事情，词法分析。MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。

第二件事情，语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

执行阶段

每条 SELECT 查询大致分为三阶段：

• prepare（预处理）
  • 检查表、字段是否存在；
  • 将 SELECT * 展开为具体列。
• optimize（优化）
  • 优化器确定执行计划（多索引时按代价选索引等）。
• execute（执行）

InnoDB 表空间与页结构

1、行（row）

数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。

2、页（page）

记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。

默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。

页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的，数据页的结构这里我就不讲细说了，之前文章有说过，感兴趣的可以去看这篇文章：换一个角度看 B+ 树(opens new window)

总之知道表中的记录存储在「数据页」里面就行。

3、区（extent）

我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。

B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。

解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。

那具体怎么解决呢？

在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

4、段（segment）

表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

索引段：存放 B + 树的非叶子节点的区的集合；
数据段：存放 B + 树的叶子节点的区的集合；
回滚段：存放的是回滚数据的区的集合；

varchar数据存储

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节（一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列。）

1
2
3
4

mysql-8.0.31 > CREATE TABLE test (
    -> `name` VARCHAR(65535)  NULL
    -> ) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

从报错信息就可以知道一行数据的最大字节数是 65535（不包含 TEXT、BLOBs 这种大对象类型），其中包含了 storage overhead。

问题来了，这个 storage overhead 是什么呢？其实就是「变长字段长度列表」和 「NULL 值列表」，也就是说一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去 storage overhead 占用的字节数。
「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。

若变长字段的长度小于255字节，就用1字节表示；若大于255字节，用2字节表示，最大不会不超过2字节，因为MySQL中VARCHAR类型的最大字节长度限制为65535。

这是因为我们存储字段类型为 varchar(n) 的数据时，其实分成了三个部分来存储：

• 真实数据
• 真实数据占用的字节数
• NULL 标识，如果不允许为NULL，这部分不需要，如果字段是允许为 NULL 的，会用 1 字节来表示「NULL 值列表」。

如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

InnoDB 的行格式

InnoDB 表的默认行格式由参数 innodb_default_row_format 定义，默认值为 DYNAMIC。

1
2
3
4
5
6

mysql-8.0.31 > show variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。

Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了。
由于 Redundant 不是一种紧凑的行格式，所以 MySQL 5.0 之后引入了 Compact 行记录存储方式，Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。

1
2
3
4
5
6
7

# 假如有三个字段 id,name,age其中name是变长类型（Varchar）
|id|name|age|
|1|wang|18|
|2|li|20|

磁盘里的存储为：
0x04 null值列表 数据头 1 wang 18 0x02 null值列表 数据头 2 li 20

Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer，我们来看下这三个字段是什么。

row_id
如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。

trx_id
事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。

roll_pointer
这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

文件查看

SHOW VARIABLES LIKE 'datadir';
我们每创建一个 database（数据库） 都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名的目录，然后保存表结构和表数据的文件都会存放在这个目录里。
文件类别：

• db.opt，用来存储当前数据库的默认字符集和字符校验规则。
• xx.frm ，表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
• xx.ibd，表数据会保存在这个文件。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。
  

MySQL 日志系统

redo log（引擎层：重做日志）

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写。如果写满了则要擦除最开始记录的数据。

redo log 是 InnoDB引擎所特有的，所以我们如果再使用InnoDB引擎创建表时，如果数据库发生异常重启，之前提交的记录都不会丢失。 InnoDB正因为有了 redo log(重做日志)，才有了 crash-safe 的能力（即使mysql服务宕机，也不会丢失数据的能力）

写redo log的方式是顺序IO。更新操作是随机IO，随机IO相比顺序IO有一个寻址的过程，所以顺序写盘更快

关于：WAL（Write-Ahead Logging）

binlog（Server 层：归档日志）

两种日志的区别

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

例子

mysql> update T set c=c+1 where ID=2;

最后 commit 阶段，redo log 会写入 binlog 的文件名与位置信息，以保证 binlog 与 redo log 一致。

binlog 只记录逻辑操作，没有「是否完成」的状态；redo log 有状态，因此不能单靠 binlog 判断。一般在 redo log 处于 prepare 时再核对 binlog 是否存在；否则只需看 redo log 是否已 commit。完整事务在 binlog 结尾有固定格式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

通过实例演示如何利用binlog恢复数据：

    a.首先，看下当前binlog位置
        mysql> show master status;
        +------------------+----------+--------------+------------------+-------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
        +------------------+----------+--------------+------------------+-------------------+
        | mysql-bin.000008 |     1847 |              |                  |                   |
        +------------------+----------+--------------+------------------+-------------------+
    b.向表tb_person中插入两条记录：
        insert into tb_person  set name="person_1", address="beijing", sex="man", other="test-1";
        insert into tb_person  set name="person_2", address="beijing", sex="man", other="test-2";
    c.记录当前binlog位置：
        mysql> show master status;
        +------------------+----------+--------------+------------------+-------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
        +------------------+----------+--------------+------------------+-------------------+
        | mysql-bin.000008 |     2585 |              |                  |                   |
        +------------------+----------+--------------+------------------+-------------------+
    d.查询数据
        mysql> select *  from tb_person where name ="person_2" or name="person_1";
        +----+----------+---------+-----+--------+
        | id | name     | address | sex | other  |
        +----+----------+---------+-----+--------+
        |  6 | person_1 | beijing | man | test-1 |
        |  7 | person_2 | beijing | man | test-2 |
        +----+----------+---------+-----+--------+
    e.删除一条: delete from tb_person where name ="person_2";
        mysql> select *  from tb_person where name ="person_2" or name="person_1";
        +----+----------+---------+-----+--------+
        | id | name     | address | sex | other  |
        +----+----------+---------+-----+--------+
        |  6 | person_1 | beijing | man | test-1 |
        +----+----------+---------+-----+--------+
    f. binlog恢复（指定pos点恢复/部分恢复）
        mysqlbinlog   --start-position=1847  --stop-position=2585  mysql-bin.000008  > test.sql
        mysql> source /var/lib/mysql/3306/test.sql
    g. 数据恢复完成后的查询
        mysql> select *  from tb_person where name ="person_2" or name="person_1";
        +----+----------+---------+-----+--------+
        | id | name     | address | sex | other  |
        +----+----------+---------+-----+--------+
        |  6 | person_1 | beijing | man | test-1 |
        |  7 | person_2 | beijing | man | test-2 |
        +----+----------+---------+-----+--------+
    h. 小结
        恢复本质是让 MySQL 把 binlog 指定区间内的语句再执行一遍。

什么是 MVCC

MVCC（Multi-Version Concurrency Control，多版本并发控制）是数据库中常用的并发控制手段：通过维护行的多个版本来协调读写。

多版本并发控制（MVCC） 是通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

简单来说，多版本并发控制的思想就是保存数据的历史版本，通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。

快照读与当前读

• 当前读像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
• 像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

事务

事务的特性

• A (Atomicity) 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
• C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
  • 原子性和一致性的的侧重点不同：原子性关注状态，要么全部成功，要么全部失败，不存在部分成功的状态。而一致性关注数据的可见性，中间状态的数据对外部不可见，只有最初状态和最终状态的数据对外可见
• I(Isolation)隔离性：一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。举个例子，张三给李四转账100元。事务要做的是从张三账户上减掉100元，李四账户上加上100元。一致性的含义是其他事务要么看到张三还没有给李四转账的状态，要么张三已经成功转账给李四的状态，而对于张三少了100元，李四还没加上100元这个中间状态是不可见的。
  • 转账过程中可能存在的状态：
    1. 张三未扣减、李四未收到
    2. 张三已扣减、李四未收到（中间状态）
    3. 张三已扣减，李四已收到
  • 数据库事务的隔离级别有4种，由低到高分别为
    • READ-UNCOMMITTED(读未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
    • READ-COMMITTED(读已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
    • REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
    • SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
• D (Durability) 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

事务与隔离问题

脏读：

一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。

不可重复读：

在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据并修改数据。那么，在第一个事务的两次读数据之间。由于另一个事务的修改，那么第一个事务两次读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他买单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

那怎么解决可能的不可重复读问题？Repeatable read ！

幻读：

当某个事务在读取某个范围的记录的时候，另外一个事务又在该范围插入了新的记录，当前事务再次读取这个范围的记录

程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读

那怎么解决幻读问题？Serializable！

数据库事务的隔离级别有4种，由低到高分别为

• READ-UNCOMMITTED(读未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

• 持久性是通过 redo log （重做日志）来保证的；
• 原子性是通过 undo log（回滚日志） 来保证的；
• 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
• 一致性则是通过持久性+原子性+隔离性来保证；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

mysql> #开启事务
    -> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+------+-----+
| Id | Name | Age |
+----+------+-----+
|  1 | 李华 |  23 |
|  2 | 李强 |  22 |
|  3 | 刘明 |  26 |
|  4 | 杰克 |  21 |
+----+------+-----+
4 rows in set (0.06 sec)

mysql> #插入操作
    -> insert into student values(5,'Jimmy',20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-------+-----+
| Id | Name  | Age |
+----+-------+-----+
|  1 | 李华  |  23 |
|  2 | 李强  |  22 |
|  3 | 刘明  |  26 |
|  4 | 杰克  |  21 |
|  5 | Jimmy |  20 |
+----+-------+-----+
5 rows in set (0.06 sec)

mysql> #回滚操作
    -> rollback;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from student;
+----+------+-----+
| Id | Name | Age |
+----+------+-----+
|  1 | 李华 |  23 |
|  2 | 李强 |  22 |
|  3 | 刘明 |  26 |
|  4 | 杰克 |  21 |
+----+------+-----+
4 rows in set (0.06 sec)

mysql> insert into student values(5,'Jimmy',20);
Query OK, 1 row affected (0.08 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+-------+-----+
| Id | Name  | Age |
+----+-------+-----+
|  1 | 李华  |  23 |
|  2 | 李强  |  22 |
|  3 | 刘明  |  26 |
|  4 | 杰克  |  21 |
|  5 | Jimmy |  20 |
+----+-------+-----+
5 rows in set (0.06 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+-------+-----+
| Id | Name  | Age |
+----+-------+-----+
|  1 | 李华  |  23 |
|  2 | 李强  |  22 |
|  3 | 刘明  |  26 |
|  4 | 杰克  |  21 |
|  5 | Jimmy |  20 |
+----+-------+-----+
5 rows in set (0.05 sec)

当提交事务的时候才会真正操作并写入到数据库中，如果选择的不是提交而是回滚，就不会将事务中定义的操作运用到数据库中。

MySQL 索引的选择

索引持久化在磁盘上：查一行往往要先读索引页再读数据页，磁盘 I/O 次数越多，耗时越大。

哈希表

哈希索引做区间查询往往很慢，它是无序的。

哈希表这种结构适用于只有等值查询的场景

有序数组

假设我们现在用数组来存储索引
如果仅仅看查询效率，有序数组就是最好的数据结构了（数组已排序时用二分法查询最快，时间复杂度是 O(log(N))）。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高

二叉查找树

二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点

二叉查找树由于存在退化成链表(就是形成一个单支的树)的可能性，会使得查询操作的时间复杂度从 O(logn)降低为 O(n)。

而且会随着插入的元素越多，树的高度也变高，意味着需要磁盘 IO 操作的次数就越多，这样导致查询性能严重下降，再加上不能范围查询，所以不适合作为数据库的索引结构。

平衡二叉查找树（AVL 树）

在二叉查找树的基础上增加了一些条件约束：每个节点的左子树和右子树的高度差不能超过 1。

但是不管平衡二叉查找树还是红黑树，都会随着插入的元素增多，而导致树的高度变高，这就意味着磁盘 I/O 操作次数多，会影响整体数据查询的效率。

N 叉树

当树的节点越多的时候，并且树的分叉数 M 越大的时候，M 叉树的高度会远小于二叉树的高度。

B树

自平衡二叉树虽然能保持查询操作的时间复杂度在O(logn)，但是因为它本质上是一个二叉树，每个节点只能有 2 个子节点，那么当节点个数越多的时候，树的高度也会相应变高，这样就会增加磁盘的 I/O 次数，从而影响数据查询的效率。

为了解决降低树的高度的问题，后面就出来了 B 树，它不再限制一个节点就只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度。

B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，所以 B 树就是一个多叉树。

而如果同样的节点数量在平衡二叉树的场景下，树的高度就会很高，意味着磁盘 I/O 操作会更多。所以，B 树在数据查询中比平衡二叉树效率要高。

但是 B 树的每个节点都包含数据（索引+记录），而用户的记录数据的大小很有可能远远超过了索引数据，这就需要花费更多的磁盘 I/O 操作次数来读到「有用的索引数据」

B+ 树

B+ 树与 B 树差异的点，主要是以下这几点：

叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；

所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；

非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。

非叶子节点中有多少个子节点，就有多少个索引；

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

InnoDB 的索引模型

在Innodb中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。Innodb使用的B+树索引类型。每一个索引在InnoDB里面对应一棵B+树。
Innodb 根据索引类型不同，分为聚集和二级索引。他们区别在于，聚集索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点，而二级索引的叶子节点存放的是主键值，而不是实际数据。

• 索引类型
  • 主键索引（聚簇索引），值存的是整行内容
  • 非主键索引（二级索引），值存的是主键内容
• B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数

索引的实现由存储引擎来决定，InnoDB使用B+树（N叉树，比如1200叉树），把整颗树的高度维持在很小的范围内，同时在内存里缓存前面若干层的节点，可以极大地降低访问磁盘的次数，提高读的效率。

B+树的插入可能会引起数据页的分裂，删除可能会引起数据页的合并，二者都是比较重的IO消耗，所以比较好的方式是顺序插入数据，这也是我们一般使用自增主键的原因之一

【基于主键索引和普通索引的查询有什么区别】

1
2
3
4
5
6
7
8
9

mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;

insert into T values(100,1),(200,2),(300,3),(500,5),(600,6),(700,7);

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

（1）如果语句为select _ from T where ID=500, 主键索引，只需要搜索ID这个B+树
（2）如果语句为select _ from T where k = 5 , 普通索引，先查询k这个B+树，然后得到id的值，再搜索ID这个B+树，这个过程叫做回表。

总结：
1、覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少 IO、避免读取整行数据
2、最左前缀：联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
3、联合索引：根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。
4、索引下推：like ‘hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表次数，提升检索速度

InnoDB 删除部分行后，空间往往以页内可复用空洞等形式存在，不会自动收缩 .ibd；需要 OPTIMIZE TABLE、ALTER TABLE ... ENGINE=InnoDB 或 重建表 等方式整理碎片（视版本与场景而定）。

锁

锁机制用于管理对共享资源的并发访问。
分类：

• 全局锁：flush tables with read lock当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
• 表级锁：表锁的语法是 lock tables … read/write
  • 表锁
  • 元数据锁
  • 意向锁
    • 意向锁的目的是为了快速判断表里是否有记录被加锁
  • AUTO-INC 锁
    • 在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的
• 行级锁：InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。
  • Record Lock，记录锁，也就是仅仅把一条记录锁上；
    • 共享锁（Share Lock），是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。SELECT ... LOCK IN SHARE MODE;
      • 读读共享，读写互斥
    • 排他锁（Exclusive Lock），如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。SELECT ... FOR UPDATE;
      • 写写互斥、读写互斥
  • Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
  • Next-Key Lock（临键锁）：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

全局锁

加上全局锁，意味着整个数据库都是只读状态。

那么如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

但是，如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。
因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL（Flush tables with read lock） 命令了。

表锁

1
2
3
4
5

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_student write;

lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象

表级的锁MDL（metadata lock)

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
我们不需要显式使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
• 对一张表做结构变更操作的时候，加的是 MDL 写锁；

注意：MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

行级锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

不同隔离级别下，行级锁的种类是不同的。

• 在读已提交隔离级别下，行级锁的种类只有记录锁，也就是仅仅把一条记录锁上。

• 在可重复读隔离级别下，行级锁的种类除了有记录锁，还有间隙锁（目的是为了避免幻读）

记录锁

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

间隙锁

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。
假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生

临键锁

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改和删除 id = 5 这条记录。
next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。
加锁机制

乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题

乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式

悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

死锁

死锁产生：

• 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
• 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
• 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

MyISAM避免死锁：

• 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

InnoDB避免死锁：

• 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元组（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
• 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
• 通过SELECT … LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
• 改变事务隔离级别

如果出现死锁，可以用 show engine innodb status;命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

普通索引和唯一索引

关于change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

查看：

1

show variables like '%change_buffer%';

innodb_change_buffer_max_size：表示change buffer在buffer pool中的最大占比，默认25%，最大50%

innodb_change_buffering：表示索引列merge对象，all表示对IDU索引列都起作用，都进行merge，如果只想对insert索引列进行merge，就把all改为inserts。

字符串字段创建索引

直接创建完整索引，这样可能比较占用空间；

1. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
2. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
3. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

删除数据

1：为啥删除了表的一半数据，表文件大小没变化？

因为delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也可以认为是一种逻辑删除，所以物理空间没有实际释放，只是标记为可复用，表文件的大小当然是不变的啦！

2：表的数据信息存在哪里？

表数据信息可能较小也可能巨大无比，它可以存储在共享表空间里，也可以单独存储在一个以.ibd为后缀的文件里，由参数innodb_file_per_table来控制，老师建议总是作为一个单独的文件来存储，这样非常容易管理，并且在不需要的时候，使用drop table命令也能直接把对应的文件删除，如果存储在共享空间之中即使表删除了空间也不会释放。

3：表的结构信息存在哪里？

首先，表结构定义占有的存储空间比较小，在MySQL8.0之前，表结构的定义信息存在以.frm为后缀的文件里，在MySQL8.0之后，则允许把表结构的定义信息存在系统数据表之中。

系统数据表，主要用于存储MySQL的系统数据，比如：数据字典、undo log(默认)等文件

4：如何才能删除表数据后，表文件大小就变小？

重建表，消除表因为进行大量的增删改操作而产生的空洞，使用如下命令：

1：alter table t engine=InnoDB

2：optimize table t( 等于 recreate+analyze)。

3：truncate table t（相当于 drop + create）

5：空洞是啥？咋产生的？

空洞就是那些被标记可复用但是还没被使用的存储空间。

使用delete命令删除数据会产生空洞，标记为可复用

插入新的数据可能引起页分裂，也可能产生空洞

修改操作，有时是一种先删后插的动作也可能产生空洞

CAP 理论

CAP 理论是分布式系统里常用的分析框架。

Consistency（一致性）：“all nodes see the same data at the same time”，即更新操作成功并返回客户端后，所有节点在同一时间的数据完全一致，这就是分布式的一致性。一致性的问题在并发系统中不可避免，对于客户端来说，一致性指的是并发访问时更新过的数据如何获取的问题。从服务端来看，则是更新如何复制分布到整个系统，以保证数据最终一致。

Availability（可用性）：可用性指 “Reads and writes always succeed”，即服务一直可用，而且是正常响应时间。好的可用性主要是指系统能够很好的为用户服务，不出现用户操作失败或者访问超时等用户体验不好的情况。

Partition Tolerance（分区容错性）：即分布式系统在遇到某节点或网络分区故障的时候，仍然能够对外提供满足一致性和可用性的服务。分区容错性要求能够使应用虽然是一个分布式系统，而看上去却好像是在一个可以运转正常的整体。比如现在的分布式系统中有某一个或者几个机器宕掉了，其他剩下的机器还能够正常运转满足系统需求，对于用户而言并没有什么体验上的影响。

CAP三个特性只能满足其中两个

CA without P：如果不要求 P（不允许分区），则 C（强一致性）和 A（可用性）是可以保证的。但放弃 P 的同时也就意味着放弃了系统的扩展性，也就是分布式节点受限，没办法部署子节点，这是违背分布式系统设计的初衷的。

CP without A：如果不要求 A（可用），相当于每个请求都需要在服务器之间保持强一致，而 P（分区）会导致同步时间无限延长（也就是等待数据同步完才能正常访问服务），一旦发生网络故障或者消息丢失等情况，就要牺牲用户的体验，等待所有数据全部一致了之后再让用户访问系统。设计成 CP 的系统其实不少，最典型的就是分布式数据库，如 Redis、HBase 等。对于这些分布式数据库来说，数据的一致性是最基本的要求，因为如果连这个标准都达不到，那么直接采用关系型数据库就好，没必要再浪费资源来部署分布式数据库。

AP without C：要高可用并允许分区，则需放弃一致性。一旦分区发生，节点之间可能会失去联系，为了高可用，每个节点只能用本地数据提供服务，而这样会导致全局数据的不一致性。典型的应用就如某米的抢购手机场景，可能前几秒你浏览商品的时候页面提示是有库存的，当你选择完商品准备下单的时候，系统提示你下单失败，商品已售完。这其实就是先在 A（可用性）方面保证系统可以正常的服务，然后在数据的一致性方面做了些牺牲，虽然多少会影响一些用户体验，但也不至于造成用户购物流程的严重阻塞。

BASE 理论

分布式场景里常谈「弱一致性」或「最终一致性」；单机 MySQL 事务则更接近强一致性语义。

BASE是Basically Available（基本可用）、Soft state（软状态）和Eventually consistent（最终一致性）三个短语的缩写。BASE理论是对CAP中一致性和可用性权衡的结果，其来源于对大规模互联网系统分布式实践的总结， 是基于CAP定理逐步演化而来的。BASE理论的核心思想是：即使无法做到强一致性，但每个应用都可以根据自身业务特点，采用适当的方式来使系统达到最终一致性。接下来看一下BASE中的三要素：

1、基本可用

基本可用是指分布式系统在出现不可预知故障的时候，允许损失部分可用性—-注意，这绝不等价于系统不可用。比如：

（1）响应时间上的损失。正常情况下，一个在线搜索引擎需要在0.5秒之内返回给用户相应的查询结果，但由于出现故障，查询结果的响应时间增加了1~2秒

（2）系统功能上的损失：正常情况下，在一个电子商务网站上进行购物的时候，消费者几乎能够顺利完成每一笔订单，但是在一些节日大促购物高峰的时候，由于消费者的购物行为激增，为了保护购物系统的稳定性，部分消费者可能会被引导到一个降级页面

2、软状态

软状态指允许系统中的数据存在中间状态，并认为该中间状态的存在不会影响系统的整体可用性，即允许系统在不同节点的数据副本之间进行数据同步的过程存在延时

3、最终一致性

最终一致性强调的是所有的数据副本，在经过一段时间的同步之后，最终都能够达到一个一致的状态。因此，最终一致性的本质是需要系统保证最终数据能够达到一致，而不需要实时保证系统数据的强一致性。总的来说，BASE理论面向的是大型高可用可扩展的分布式系统，和传统的事务 ACID 特性是相反的，它完全不同于ACID的强一致性模型，而是通过牺牲强一致性来获得可用性，并允许数据在一段时间内是不一致的，但最终达到一致状态。但同时，在实际的分布式场景中，不同业务单元和组件对数据一致性的要求是不同的，因此在具体的分布式系统架构设计过程中，ACID特性和BASE理论往往又会结合在一起。

常见分布式事务解决方案

• 两阶段提交（2PC, Two-phase Commit）
• TCC 补偿模式
• 基于本地消息表实现最终一致性
• 最大努力通知
• 基于可靠消息最终一致性方案

延伸阅读：分布式事务常见方案整理（知乎专栏）