1. 记录存储结构

1.1 InnoDB的页

  • innoDB存储记录的时候会将数据划分为若干个页，以页作为硬盘和内存的交互基本单位。

  • InnoDB的页大小一般为16KB：一般情况下，刷盘和读取都是以页为单位。

    • 系统变量innodb_page_size默认值为16384也就是16K，
    • 在初始化设定数据目录后就不能改变了（mysqld —initialize）

      1.2 行格式

  • 四种格式：

    1. COMPACT：紧凑型
    2. REDUNDANT：冗余型
    3. DYNAMIC：动态型（MySQL5.7默认）
    4. COMPRESSED：精简型
  • 在创建表的时候除了CHARSET也可同样设置ROW_FORMAT，并且ROW_FORMAT可以通过Alter变更。

image.png
image.png



  • DYNAMIC和COMPRESSED: 这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字符串的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真 实数据处存储其他页面的地址。
  • COMPRESSED行格式会采用压缩算法对页面进行压缩

    2. 数据页结构

3. 表空间

1. SQL语句的执行过程

image.png

1.1 连接器

  • 连接器负责跟客户端建立连接、获取权限、维持和管理连接。

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到Command列为Sleep。
一个连接的默认超时时间是8小时，连接断开再次操作的话会报出“Lost connection to MySQL server during query”

1. 事务的特性ACID

  • 原子性： Atomicity

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。

  • 一致性：Consistency

数据库总是从一个一致性的状态转换到另一个一致性的状态。

  • 隔离性：Isolation

通常来说，一个事务所做的修改在最终提交前，对其它事务是不可见的。

  • 持久性：Durability

一旦事务提交，则所有修改就永久保存在数据库的磁盘中。
RELDOLOG https://www.yuque.com/imyiren/notes/gwhyb9#Nf0aF

2. 事务的隔离级别

  • 读未提交（read uncommitted）
  • 读已提交（read committed）
  • 可重复读（repeatable read）
  • 串行化（serializable ）
    • 隔离级别产生的三个问题：

    脏读（dirty read）： 不可重复读（non-repeatable read）： 幻读（phantom read）：

2.1 读未提交

  • 一个事务还没提交时，它做的变更就能被别的事务看到。
  • 实现：“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；

  • 问题：脏读、不可重复读、幻读

    2.2 读已提交

  • 一个事务提交之后，它做的变更才会被其他事务看到。

  • 实现：“读已提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的

  • 问题：不可重复读、幻读

    2.3 可重复读 （MySQL默认 ）

  • 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

  • 实现：使用视图实现，视图是在事务启动时创建的，整个事务存在期间都用这个视图。

  • 问题：幻读

    2.4 串行化

  • 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

  • 实现：“串行化”隔离级别下直接用加锁的方式来避免并行访问。 （锁是部分锁，不是表锁）
  • 问题：它会强制事务串行执行，避免了脏读、不可重复读、幻读的问题，但是牺牲了并发性。

    2.5 问题解读

  1. 脏读
  • 事务A 读到了事务B修改了但是还未提交的数据
  1. 不可重复读
  • 事务A 开始读了一次数据，事务B开始修改了一个数据提交后，然后事务A又读了一次，发现和上次读取的数据不一样。
  1. 幻读

  • 事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据 称为幻读

    2.6 隔离级别的案例

    image.png

  • 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。

  • 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
  • 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
  • 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

    2.7 事务相关设置

  1. 查询事务隔离级别：

    1. # 当前会话：
    2. select @@tx_isolation;
    3. show variables like 'transaction_isolation';
    4. # 系统级别：
    5. select @@global.tx_isolation;

  2. 设置事务隔离级别

    1. # 注意 这样设置当前会话不会生效，只有下一个会话才会生效。 可重复读
    2. set transaction isolation level REPEATABLE READ;
    3. # 设置当前会话的事务隔离级别 ： 读已提交
    4. set transaction isolation level read committed;
    5. # 设置同的事务隔离级别： 读未提交
    6. set transaction isolation level read uncommitted;

  3. 启动提交回滚事务 ```sql

    开始事务

    begin start transaction

提交事务

commit

回滚事务

rollback

  2. 4. 关闭默认自动提交
  3. ```sql
  4. set autocommit = 0;
  • 该命令会把这个线程的自动提交关掉。这样只要执行一个select语句，事务就启动，并不会自动提交，直到主动执行commit或rollback或断开连接。

  • 在autocommit=1的情况下用begin显式启动事务，如果执行commit则提交事务。

    3. MVCC 多版本并发控制

  • MVCC(Mutil-Version Concurrency Control)，就是多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

  • 在MySQL的InnoDB引擎中就是指在读已提交(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程。

    3.1 MVCC在行中的隐藏列

  1. DATA_TRX_ID
    • 记录最近更新这条行记录的 事务 ID ，大小为 6 个字节
  2. DATA_ROLL_PTR
    • 表示指向该行回滚段 （rollback segment） 的指针，大小为 7 个字节， InnoDB 便是通过这个指针找到之前版本的数据。该行记录上所有旧版本，在 undo 中都通过链表的形式组织。
  3. DB_ROW_ID
    • 行标识（隐藏单调自增 ID ），大小为 6 字节，如果表没有主键， InnoDB 会自动生成一个隐藏主键，因此会出现这个列。另外，每条记录的头信息（ record header ）里都有一个专门的 bit （ deleted_flag ）来表示当前记录是否已经被删除。

4. MySQL中的各种锁

4.1 全局锁：

  • 全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

    4.2 行锁 (InnoDB引擎支持)

  • 行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

  • 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

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

  • 注意

    • 行锁是通过索引结构来实现的，如果所在列没有索引，update一条数据都会锁表

    • 4.3 表锁

1. 索引概念知识

1.1 索引的作用

  • 索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

    1.2 索引的结构

  • 哈希表、有序数组、搜索树、跳表、LSM树

    1.2.1 哈希表

  • 哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

  • 缺点：

    • 在哈希函数在计算位置时，可能出现重复的情况
    • 只适合等值查询，范围查询效率非常低，需要遍历完成数据

      1.2.2 有序数组

  • 有序数组在等值查询和范围查询场景中的性能就都非常优秀。

  • 缺点：插入一个记录就必须得挪动后面所有的记录，成本太高。

  • 适用于：有序数组索引只适用于静态数据，不经常变的。

    1.2.3 二叉搜索树

  • 二叉搜索树的特点是：父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值。

  • 查询一个值的时间复杂度是 O(log(N))。为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。
  • 实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

    硬盘的最小存储单位是扇区，硬盘本身没有block的概念 文件系统若按照一个扇区一个扇区读数据速度太慢，所以有了block（块）的概念，是一个块一个块读取的，block才是文件存取的最小单位。扇区是对硬盘而言，块是对文件系统而言。

1.2.4 跳表

1.2.5 LSM 树

2. MySQL中的索引：InnoDB

  • 在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。
  • Memory: MEMORY存储引擎支持HASH和BTREE索引。你可以通过添加一个如下所示的USING子句为给定的索引指定一个或另一个
  • InnoDB：B+树，聚簇索引叶子节点存储数据，非聚簇索引叶子节点存储主键ID;
  • MyISAM：B+树，和InnoDB不同的是，无论是主键还是非主键索引都是叶子节点存储数据在硬盘上的位置（innode(硬盘数据区的编号)）

image.png

下面主要是InnoDB。

2.1 InnoDB的索引的结构

  • InnoDB中默认使用主键作为默认的聚簇索引，如果不设置主键，innodb会给默认创建一个Rowid做主键。
  • InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。
  • 首先Innodb是聚集存储的，每条记录的数据以主键进行聚集存储，通过主键进行索引。如果没有定义主键，系统默认使用6个字符作为主键进行聚簇存储。而辅助索引（secondary indexes）中的每条记录包含主键（primary key）和索引字段。
  • 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

image.png

  • 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

image.png

2.2 InnoDB的索引的使用

InnoDB的索引包括：主键索引、普通索引、联合索引

2.2.1 查询

  • 在使用主键查询时：只需要从主键索引的树获取一次即可拿到数据
  • 在使用非主键索引时，需要做两次获取，第一次去查非主键索引树，找到主键，然后再去主键索引树去找一次。
    • 回表：从非主键索引回到主键索引树搜索的过程，我们称为回表
  • 覆盖索引： 如果执行的语句是select id from tableA where idx_a = ?，这时只需要查 id 的值，而 ID 的值已经在 idx_a 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。
    • 覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
    • 只会存在两种情况：一种是主键，一种是联合索引字段。
  • 最左前缀原则：不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。
    • 如何选择联合索引的顺序
    • 如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
    • 如果（A,B）这样的索引不满足需求，需要B的单独索引，往往需要考虑占用空间大小，A大还是B大，遵从大在前原则，占用空间小的字段单独建立索引所需要的空间小。
  • 索引下推（MySQL5.6+）： 索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

image.png
select * from people where name like ‘张%’ and age = 10; 的索引下推

2.2.2 修改数据

  • 插入数据，如果当前数据页有空间，就会直接插入，但是如果当前数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能会受影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
  • 删除数据也会有问题：当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

  • 当索引页面的“page-full”百分比低于50％时，InnoDB会尝试将索引页与相邻页合并。 如果两个页面都接近50％已满，则在合并页面后很快就会发生页面拆分。 如果频繁发生此合并拆分行为，则可能会对性能产生负面影响。 为避免频繁的合并拆分，您可以降低MERGE_THRESHOLD值，以便InnoDB以较低的“page-full”百分比尝试页面合并。 以较低页面满百分比合并页面会在索引页面中留出更多空间，并有助于减少合并拆分行为。

    2.2.3 主键索引的选择

  • 如果你业务内有唯一且有序的字段，可以作为主键索引，你要怎么设置主键索引？比如身份证？

  • 先说结论：
    • 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
    • 每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
    • 从性能和存储空间方面考量，自增主键往往是更合理的选择。
  • 有没有什么场景适合用业务字段直接做主键的呢？
    • 只有一个索引；
    • 该索引必须是唯一索引。

由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

2.2.4 如何重建索引

  • 非主键索引 ```sql

alter table T drop index k; alter table T add index(k);

  2. - **主键索引重建**
  3. ```sql
  4. alter table T engine=InnoDB;

2.2.5 索引失效

  • 使用函数 如: where month(update_time) = 7;
  • 字段做计算：如：where age + 1 = 20;

https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

  • 隐适转换：如：where trace_id = 1234; 注意：trace_id 是varchar(32)
    • 字符编码转换 如：where a.trace_id = a.trace_id; 前表是utf8，后表是utf8mb4;
    • 字符串和数字做比较的话，是作为浮点数的比较进行的。
    • 按数据长度增加的方向，字符串编码转换的例子：a表的条件会加上 using utf8mb4
      • 常见错误：int -> long，decimal_a = int_b,
      • 日期和时间戳：在和常量比较时，会把常量转成时间戳。
  • 除开最左前缀外情况的，通配符匹配
  • 使用OR
  • 使用IN：两种情况：一个是IN(集合)的集合过大，一个是IN的所属字段的区分度过小，约30%。

2.2.6 索引优化

  • 做碎片整理，索引重建：alter table t engine=innodb
  • 条件覆盖 主键索引、组合索引
  • 使用组合索引的最左前缀、索引覆盖、索引下推来优化索引查询的。
  • 使用区分度大的字段做索引。
  • 组合索引通过最左前缀排序后，可以减少索引的数量。

MySQL中的各种日志

  • 重做日志（redo log）🪵
  • 回滚日志（undo log）
  • 二进制日志（binlog）
  • 错误日志（errorlog）
  • 慢查询日志（slow query log）
  • 一般查询日志（general log）

  • 中继日志（relay log）

    Binlog 二进制日志

    它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

  • InnoDB而言，必须要提交了事务才会记录binlog。

    两个应用场景

  • MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的

  • 数据恢复：通过使用 mysqlbinlog工具来使恢复数据

三种保存格式

Statement

每一条会修改数据的sql都会记录在binlog中
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能。
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外mysql的复制，像一些特定函数的功能，slave与master要保持一致会有很多相关问题。

Row

5.1.5版本的MySQL才开始支持 row level 的复制,它不记录sql语句上下文相关信息，仅保存哪条记录被修改。
优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.
缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

注：将二进制日志格式设置为ROW时，有些更改仍然使用基于语句的格式，包括所有DDL语句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。

Mixed

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。 在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

RedoLog 重做日志

  • 用于确保确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

Undolog 重做日志

  • 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读