• 数据库 : 数据库(DataBase，简称 DB)就是信息的集合或者说数据库是由数据库管理系统管理的数据的集合。
• 数据库管理系统 : 数据库管理系统(Database Management System，简称 DBMS)是一种操纵和管理数据库的大型软件，通常用于建立、使用和维护数据库。
• 数据库系统 : 数据库系统(Data Base System，简称 DBS)通常由软件、数据库和数据管理员(DBA)组成。
• 数据库管理员 : 数据库管理员(Database Administrator，简称 DBA)负责全面管理和控制数据库系统。

2.数据库模型分类

3.关系模型基本组成

• 元组 ：元组（tuple）是关系型数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。
• 码 ：码就是能唯一标识实体的属性，对应表中的列。
• 候选码 ：若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何子集都不能再标识，则称该属性组为候选码。
                  例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码。
• 主键 : 主键也叫主码。主码是从候选码中选出来的。一个实体集中只能有一个主键，但可以有多个候选码。
• 外键 : 外键也叫外码。如果一个关系中的一个属性是另外一个关系中的主键则这个属性为外键。外键即“别人(别的关系)”的主键。
• 主属性：候选码中出现过的属性称为主属性。比如工人实体（工号，身份证号，姓名，性别，部门），显然工号和身份证号都能够唯一标识这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。
• 非主属性：不包含在任何一个候选码中的属性称为非主属性。比如在关系——学生（学号，姓名，年龄，性别，班级）中，主码是“学号”，那么其他的“姓名”、“年龄”、“性别”、“班级”就都可以称为非主属性。

（1）什么是级联？

• 性能问题
  每次对数据进行DELETE或UPDATE操作时，都必须考虑外键约束，数据库都会判断当前操作是否违反数据完整性，性能下降。
  比如假设有一张表名user_tb，这张表里有两个外键字段，指向另外两张表。那么每次更新user_tb表里的数据时，就必须在两个外键对应的表里查询是否有对应数据。如果交由程序控制，这种查询过程就可以控制在我们手里，可以省略一些不必要的查询过程。但是如果由数据库控制，则是必须要去这两张表里判断。
• 并发下的死锁问题
  在使用外键的情况下，每次修改数据都需要去另外一个表检查数据，需要获取额外的锁，因此如果是在高并发大流量事务场景，使用外键更容易造成死锁。
• 扩展性问题
  像触发器、外键这种依赖于数据库本身的特性，可扩展性差。同时在分库分表时，外键也不会生效。

• 函数依赖：在一张表中，如果在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 X 确定 Y，Y 函数依赖于 X，写作 X → Y。
• 部分函数依赖：比如学生信息表 R 中（学号，身份证号，姓名），（学号，身份证号）→ （姓名），但是（学号）→ （姓名）、（身份证号）→ （姓名），所以说姓名部分函数依赖于（学号，身份证号）；
• 完全函数依赖：在一个关系中，如果某个非主属性依赖于全部关键字，则称之为完全函数依赖。
                           比如学生基本信息表 R（学号，班级，姓名），假设不同的班级学号有相同的，班级内学号不能相同，所以学号+班级才能确定姓名，即（学号，班级）→ （姓名），但是（学号）→ (姓名)不成立，（班级）→ (姓名)也不成立，所以姓名完全函数依赖于（学号，班级）；
• 传递函数依赖 ： 如果X→ Y，Y→ Z，则称 Z 传递函数依赖于 X。传递函数依赖会导致数据冗余和异常。

（1）1NF(第一范式)

（2）2NF(第二范式)

        2NF 在 1NF 的基础之上(2NF也遵守1NF)，消除了非主属性对码的部分函数依赖。

（3）3NF(第三范式)

8.drop、delete 与 truncate 区别？

（1）功能不同

• drop(删除表)：drop table 表名，直接删除表。
• truncate (清空数据)：truncate table 表名，删除表中的所有数据，不会删除表。
• delete(删除数据)：delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句的话表示删除表中所有数据，和 truncate 的作用类似。

（2）属于不同的数据库语言

（3）执行速度不同

        一般来说：drop > truncate > delete

• delete命令执行的时候会产生数据库的binlog日志，而日志记录是需要消耗时间的，但是也有个好处方便数据回滚恢复。
• truncate命令执行的时候不会产生数据库日志，因此比delete要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等。
• drop命令直接把表占用的空间全部释放掉。

        从上图可以看出， MySQL 主要由下面几部分构成：

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除）。
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。
• 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
• 插件式存储引擎 ： 主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

2.MySQL的执行流程？——深入理解MySQL内部结构

• 如何查看 MySQL 服务被多少个客户端连接了？
  可以执行 show processlist 命令进行查看。
  
  比如上图的显示结果，共有两个用户名为 root 的用户连接了 MySQL 服务，其中 id 为 6 的用户的 Command 列的状态为 Sleep ，这意味着该用户连接完 MySQL 服务就没有再执行过任何命令，也就是说这是一个空闲连接，并且空闲的时长是 736 秒（ Time 列）。
• 空闲连接会一直占用着吗？
  不会。MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。
  当然，我们自己也可以手动断开空闲连接：kill connection + id：
  
• MySQL 的连接数有限制吗？
  MySQL 服务支持的最大连接数由 max_connections 参数控制，比如下面的 MySQL 服务默认最大连接数是 151 个，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。
  
• 怎么解决长连接占用内存的问题？
  MySQL的连接有短连接和长连接两种，短连接一次连接只能执行一条sql，长连接可以执行多条sql(如下图)。但是使用长连接后可能会导致MySQL服务占用内存增多，因为 MySQL 在执行查询过程中临时使用内存管理连接对象，并且这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会导致 MySQL 服务的异常重启。
  
  有两种方法解决长连接占用内存的问题(本质是释放内存资源)。
  • 定期断开长连接。既然断开连接后就会释放连接占用的内存资源，那么我们可以定期断开长连接。
  • 客户端主动重置连接。MySQL 5.7 以后的版本可以执行 mysql_reset_connection()来重置连接，达到释放内存资源的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

• 如果缓存中有，就不必执行查询语句，直接取出缓存结果返回给客户端；
• 如果缓存未命中，就执行查询语句，将执行后的结果返回给客户端，并放入查询缓存中。

• 词法分析。根据输入的字符串构建出 SQL 语法树，方便后面模块获取 SQL 类型、表名、字段名、 where 条件等信息；
• 语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断 SQL 语句是否符合 MySQL 语法。

• 如果没有执行权限，会直接终止；
• 如果有执行权限，执行器就会根据表的引擎定义，从存储引擎读取记录，返回给客户端。

（5）★总结MySQL查询语句的执行流程

• 首先要与MySQL建立连接，由连接器验证用户名和密码；
• 成功建立连接后，客户端向 MySQL 服务发送查询语句；
• MySQL 会先去查询缓存中是否有结果：
  • 如果缓存中有，就不必执行查询语句，直接取出缓存结果返回给客户端；
  • 如果缓存未命中，就继续往下执行。
• 然后会经过解析器，对查询语句进行词法分析构建出语法树并检查语法是否符合要求；
• 判断要查询的表或字段是否存在，然后由优化器确定sql的执行方案，最后判断当前用户是否有执行权限：
  • 如果没有执行权限，会直接终止；
  • 如果有执行权限，执行器就会根据表的引擎定义，从存储引擎读取记录，返回给客户端。
• 至此，一条查询语句的执行结束。

（6）★MySQL更新语句的执行流程？

• 首先要与MySQL建立连接，由连接器验证用户名和密码；
• 建立连接后，客户端向 MySQL 服务发送更新语句；
• MySQL 会先去查询缓存中是否有要更新的这条记录：
  • 如果缓存未命中，就继续往下执行。
• 然后会经过分析器，对语句进行词法分析并检查语法是否符合要求；
• 然后由优化器确定sql的执行方案；
• 执行器调用存储引擎的接口，先判断要更新的数据在不在缓冲池中，如果在就直接返回给执行器更新；如果记录不在就将数据页从磁盘读到缓冲池，并记录相应的undo log(更新前记录，因为记录的是更新前的数据)。
• 然后开启事务，InnoDB 会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。
• 更新语句执行完以后，开始记录该语句对应的 binlog(此时未刷盘，在事务提交时才会刷盘)；
• 准备提交事务(两阶段提交)：
  • 将事务状态改为 prepare，然后将 redo log 刷新到硬盘；
  • 然后将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit；
• 至此，一条更新语句就执行完成。

• db.opt：用来存储当前数据库的默认字符集和字符校验规则；
• t_order.frm：保存 t_order 的表结构；
• t_order.ibd：保存 t_order 的表数据。这个文件也称为独占表空间文件。

• Redundant：很古老，现在基本没人用了；
• Compact：是一种紧凑的行格式，从 MySQL 5.1 版本之后，行格式默认设置成 Compact；
  compact  adj.紧凑的、紧密的。
• Dynamic 和 Compressed：都是紧凑的行格式，和 Compact 差不多，都是基于 Compact 改进一点东西。MySQL5.7 版本之后默认使用 Dynamic 行格式。

• 额外信息：记录的额外信息包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息。
  • 变长字段长度列表：我们知道 char 是定长的，varchar 是变长的，变长字段存储的数据长度是不固定的。所以在存储数据的时候，要把数据大小存到变长字段长度列表里，这样读取数据时才能根据这个「变长字段长度列表」去读取对应长度的数据。当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了。TEXT、BLOB 等其他变长字段也是这么实现的。
  • NULL值列表：Compact 行格式把值为 NULL 的数据存储到 NULL值列表中。当字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。
  • 记录头信息：记录头信息中包含的内容很多，比如当前记录的类型、下一条记录的位置等。
• 真实数据：记录的真实数据除了我们定义的字段，还有三个隐藏字段：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 个字节。

（4）★varchar(n) 中 n 最大取值为多少？

• 单字段
  一行记录最大只能存储 65535 字节的数据。那假设数据库表只有一个 varchar(n) 类型的列且字符集是 ascii，在这种情况下， varchar(n) 中 n 最大取值是 65535 吗？
  答案是不是的。我们说的一行数据的最大字节数 65535，其实是包含了变长字段长度列表和 NULL 值列表所占用的字节数。所以我们在算 varchar(n) 中 n 最大值时，需要减去变长字段长度列表和 NULL 值列表占用的字节数。
• 多字段
  如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

• Compact 行格式发生行溢出时，在记录真实数据只会保存的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。
• Compressed 和 Dynamic 采用完全的行溢出方式，记录真实数据不会存储该列的一部分数据，只用 20 字节存储指向溢出页的地址，实际的数据都存储在溢出页中。

（7）存储引擎和server的关系？

三、MySQL索引

        我们可以按照四个角度来分类索引：

• 按「数据结构」分类(底层实现)：B+树索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引、二级索引（辅助索引、非聚簇索引）。
  
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引(组合索引、复合索引)。

（1）按数据结构分类

• B+树 vs B树：
  • 存储相同数据量级别的情况下，B+树树高比B树低，磁盘I/O次数更少。
  • B+树叶子节点用双向链表串起来，适合范围查询，B树无法做到这点。
• B+树 vs二叉树：
  • 随着数据量的增加，二叉树会越来越高，磁盘I/O次数也会更多，B+树在千万级别的数据量下，高度依然维持在3~4层左右，也就是说一次数据查询操作只需要做3~4次的磁盘l/0操作就能查询到目标数据。
• B+树 vs Hash：
  • 虽然Hash的等值查询效率很高，但是无法做范围查询。

• 聚簇索引：聚簇索引是索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引，因为主键索引的B+树中叶子节点即存放主键索引又存放数据。
  • 优点：
    • 查询速度非常快 ：因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。因此相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
    • 对排序查找和范围查找优化 ：聚簇索引对于主键的排序查找和范围查找速度非常快。
  • 缺点：
    • 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
    • 更新代价大 ： 如果索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。
  • 在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为聚簇索引的索引键：
    • 如果有主键，默认使用主键作为聚簇索引的索引键（key）；
    • 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
      
    • 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）。
• 非聚簇索引：是索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。
  • 优点：更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，因为非聚簇索引的叶子节点不存放数据；
  • 缺点：
    • 依赖于有序的数据 ：跟聚簇索引一样，非聚簇索引也依赖于有序的数据；
    • 可能会二次查询(回表) ：这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

（3）按字段特性分类

• 主键索引：建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
• 唯一索引：建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
• 普通索引：建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。
• 前缀索引：前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
                    使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。
  

（4）按字段个数分类

• 单列索引：建立在单列上的索引称为单列索引，比如主键索引；
• 联合索引：通过将多个字段组合成一个索引，该索引就被称为联合索引。

• 在 MySQL 5.6 之前，只能从主键 id=2 开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
• 而 MySQL 5.6 引入的索引下推，可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

【tips】当查询语句的执行计划里，出现了 Extra 为 Using index condition，那么说明使用了索引下推的优化。

2.索引的优缺点及适用场景

• 优点：
  • 索引最大的有点就是可以提高查询速度；
• 缺点：
  • 创建索引需要占用物理空间(每⼀个节点都是⼀个 16KB 大小的页)；
  • 创建和维护索引需要耗费时间，当对表中的数据进行增删改时，如果数据有索引，索引也需要进行动态修改，会降低sql的执行效率。

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，还可以建立联合索引；
• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为建立索引后在 B+数中的记录都是排序好的。

3.索引优化

• 前缀索引优化；
• 覆盖索引优化；
• 主键索引最好是自增的；
• 索引最好设为NOT NULL；
• 防止索引失效。

（1）前缀索引优化

        前缀索引就是用某个字段中的前几个字符建立索引。

• order by 就无法使用前缀索引；
• 无法把前缀索引用作覆盖索引；

（2）覆盖索引优化

        覆盖索引可以避免回表的操作。

（3）主键索引最好是自增的

• 如果使用自增主键，那么每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这样做后，插入操作的效率非常高。
• 如果使用非自增主键，每次插入新数据时，可能需要移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。

• 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可以为 NULL 的列会使索引、索引统计和值比较都更复杂.比如进行索引统计时，count 会省略值为NULL 的行。
• NULL 值也会占用物理空间。(因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式中至少会用 1 字节空间存储 NULL 值列表)

（5）防止索引失效

1.事务基础

# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
SQL1,SQL2...
## 提交事务
COMMIT;

2.事务的特性

（1）原子性（Atomicity）——同完成同失败

（2）一致性（Consistency）——数据一致性

（3）★隔离性（Isolation）——互不干扰

（4）持久性（Durability）——永久修改

（5）InnoDB 引擎如何保证事务的四个特性？

• 持久性是通过 redo log （重做日志）来保证的
• 原子性是通过 undo log（回滚日志） 来保证的；
• 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
• 一致性则是通过持久性+原子性+隔离性来保证。(只有保证了事务的持久性、原子性、隔离性之后，才能保证一致性，也就是说 A、I、D 是手段，C 是目的！)

3.并发事务

（1）脏读（dirty read）——读到其他事务未提交的数据

• 不可重复读和幻读的区别？
  • 不可重复读侧重于记录的内容修改了，比如多次读取同一条记录，发现前后读到的记录内容不一致；而幻读侧重于记录的数量不一致，比如多次执行同一条查询语句，查到的记录数量不一致。
  • 幻读可以看做不可重复读的一种特殊情况，单独区分幻读主要是因为解决幻读和不可重复读的方案不一样。

• 读不提交 RU（read uncommitted）：最低的隔离级别。允许读取未提交事务的数据修改，可能会导致脏读、不可重复读和幻读，一般不使用；
• 读提交 RC（read committed）：读取的是并发事务已经提交的最新数据，可以阻止脏读，但是同一事务中前后两次相同的查询可能会读到不同的结果，所以可能导致不可重复读和幻读；
  
• 可重复读 RR（repeatable read）：指同一个事务中读到的数据，一直跟事务开始时读到的数据是一致的，解决了脏读和不可重复读，还是可能会幻读。是MySQL InnoDB 引擎的默认隔离级别；
• 串行化（serializable ）：最高的隔离级别。对记录加上读写锁，在并发事务对这条记录进行读写操作时，如果发生了读写冲突，后访问的事务必须等前一个事务执行完成，才能继续执行。可以防止脏读、不可重复读以及幻读。

• 读未提交：因为可以读到未提交事务修改的数据，所以是直接读取最新的数据；
• 串行化：通过加读写锁的方式来避免并行访问；
• 「读提交」和「可重复读」：基于MVCC(多版本并发控制)，通过 Read View 来实现的。它们的区别在于创建 Read View 的时机不同：
  • 「读提交」是在每个 select 都会生成一个新的 Read View，所以在事务期间多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
  • 「可重复读」是在「启动事务时」生成一个 Read View，然后整个事务期间都用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

• 每个事务都有一个严格递增的事务id，当事务对记录进行修改时，会产生一条undo记录，这条undo记录里除了保存字段的数据外，还保存了修改记录的事务id(trx_id)和上一个版本的记录地址(roll_pointer)，通过roll_pointer可以获得每一个版本的记录。
• 写操作会生成undo记录，形成版本链，而读操作只需要根据一定的规则来读取某个版本的数据。这个规则就是可见性判断规则(上面的3）)。

• 针对快照读（普通 select 语句），是通过 MVCC 的方式解决了幻读，并不会对记录进行加锁。
  ——实现方式是开始事务后（执行 begin 语句后），在执行第一个查询语句后，会创建一个 Read View，后续的查询语句都用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，也查不到新记录，所以就很好了避免幻读问题。
• 针对当前读（select ... for update 等语句），是通过临键锁（记录锁+间隙锁）的方式解决了幻读。
  ——因为当执行 select ... for update 语句的时候，会加上临键锁，如果有其他事务在锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就避免了幻读问题。

（4）可重复读能完全解决幻读吗？

五、MySQL锁

• 加全局锁：

  flush tables with read lock
  

  执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：
  • 对数据的增删改操作，比如 insert、delete、update等语句；
  • 对表结构的更改操作，比如 alter table、drop table 等语句；
  • 更新类事务的提交操作。
• 释放全局锁：

  unlock tables

  当会话断开全局锁会被自动释放。

（2）表级锁（table-level locking）

• 表锁；
• 元数据锁（meta data lock，MDL）；
• 意向锁；
• AUTO-INC 锁(自增锁，了解即可)。

• 如果想对表 t_student 加表锁，可以使用下面的命令：

  //表级别的共享锁，也就是读锁；
  lock tables t_student read;
  //表级别的独占锁，也就是写锁；
  lock tables t_stuent write;
  

  表锁除了会限制其他线程的读写操作外，也会限制本线程接下来的读写操作。
  

• 解锁：

  unlock tables

  会话退出会自动释放所有表锁。
  

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

• 当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。
• 反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

• 如果要对数据表的某行记录加「共享锁」，需要先在数据表上加一个「意向共享锁」；同理要给某行记录加「独占锁」之前，需要先在数据表上加一个「意向独占锁」。
• 这样在对某行记录加独占锁时，直接看该表有没有意向独占锁，如果有意向独占锁，说明该表已经有记录被加了独占锁了，就不用逐行遍历去看加没加独占锁了。

（3）行级锁（row-level locking）

//对读取的记录加共享锁
select ... lock in share mode;
//对读取的记录加独占锁
select ... for update;

• 记录锁（Record Lock）：给一条行记录上锁，所以也称为行锁；
• 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身；
• 临键锁（Next-Key Lock）：行锁+间隙锁，锁定一个范围，包含记录本身。主要目的是为了解决幻读问题。记录锁只能锁住已经存在的记录，为了避免插入新记录，所以还需要间隙锁。

mysql > begin;
mysql > select * from t_test where id = 1 for update;

//对读取的记录加共享锁(S型锁)
select ... lock in share mode;
//对读取的记录加独占锁(X型锁)
select ... for update;

3.MySQL加行级锁的原则？

• 当查询的记录是「存在」的，即在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「行锁」。
  因为记录存在，要避免幻读，只需要加行锁来阻塞对这条记录的更新和删除操作即可。
• 当查询的记录是「不存在」的，在索引树找到第一条大于该记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。
  因为记录不存在，就没有索引，而行锁是加在索引上的，所以只需要间隙锁即可。

（2）非唯一索引等值查询

• 唯一索引在满足一些条件的时候，索引的 next-key lock 退化为间隙锁或者记录锁。
• 非唯一索引范围查询，索引的 next-key lock 不会退化。

4.MySQL中的死锁问题

• 设置事务等待锁的超时时间：
  • 当一个事务的等待时间超过设定的时间后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。
  • 在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值是 50 秒。
  • 当发生超时时会出现以下提示(1205)：
    
• 开启主动死锁检测：
  • 开启主动死锁检测后，如果发生了死锁，会主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。
  • 将参数 innodb_deadlock_detect 设置为 on，表示开启主动死锁检测，默认就是开启的。
  • 当检测到死锁后，就会出现下面这个提示：
    

        undo log 的两大用途：

• 实现事务回滚，保障事务的原子性。
          事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
• 是实现 MVCC关键因素之一。
          MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读的时候，会根据事务的 Read View ，顺着 undo log 的版本链找到满足其可见性的记录。

2.buffer pool 缓冲池

（2）buffer pool 的大小

（3）★buffer pool 中的缓存内容

        

（4）查询一条记录，就只需要缓冲一条记录吗？

（5）如何提高缓存命中率？

（6）★预读失效

        ——要避免预读失效导致的命中率降低的问题，可以让预读页停留在 Buffer Pool 里的时间尽可能短，让真正被访问的页才移动到 LRU 链表的头部。所以 MySQL 改进了 LRU 算法，将 LRU 划分成 2 个区域：old 区域 和 young 区域。young 区域在 LRU 链表的前半部分，old 区域则是在后半部分。这时，预读页只需要加到 old 区域的头部，当真正访问的时候，才将页插入 young 区域的头部。这样一来如果预读页一直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数据了。
        

（7）★buffer pool污染

3.redo log 重做日志

（1）为什么有了buffer pool还需要redo log来恢复数据？

• redo log 记录了事务「完成后」的数据状态，记录的是更新之后的数据；
• undo log 记录了事务「开始前」的数据状态，记录的是更新之前的数据。

（4）redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？(WAL如何将随机写变成顺序写？)

• MySQL 正常关闭时，会对redo log进行刷盘；
• 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会进行刷盘；
• InnoDB 的后台线程每隔 1 秒，会进行刷盘；
• 还有一个由 innodb_flush_log_at_trx_commit 参数控制的事务提交时的三种刷盘策略：
  • 0 ：innodb_flush_log_at_trx_commit 设置为 0 的时候，表示每次事务提交时不进行刷盘操作，还是将 redo log 留在 redo log buffer 中；
  • 1 ：设置为 1 的时候，表示每次事务提交时(prepare阶段结束)都将进行刷盘操作(默认操作)；
  • 2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache。
    Page Cache 是专门用来缓存文件数据的，所以将 redo log文件写入page cache意味着写入到了操作系统的文件缓存，而不是写入到磁盘了。
    
    

（6）innodb_flush_log_at_trx_commit 为 0 和 2 的时候，什么时候将 redo log 写入磁盘？

• 针对参数 0 ：会把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到 Page Cache，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，MySQL 的崩溃会导致上一秒钟所有事务数据的丢失；
• 针对参数 2 ：调用 fsync()，将缓存在Page Cache 里的 redo log 持久化到磁盘。所以参数为 2 的策略，比 0 更安全，因为 MySQL 的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
  
  

（7）0、1、2三个参数的应用场景？

        要根据三个参数的安全性和写入性能分析应用场景：

• 数据安全性：参数 1 > 参数 2 > 参数 0
• 写入性能：参数 0 > 参数 2> 参数 1

（8）redo log满了怎么办？

• 用 write pos 表示当前记录写到的位置；
• 用 checkpoint 表示当前要擦除的位置

• write pos ～ checkpoint 之间的部分（图中的红色部分），用来记录新的更新操作；
• check point ～ write pos 之间的部分（图中蓝色部分），是等待刷盘的脏页数据。

• 1）适用对象不同(实现不同)：
  • binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
  • redo log 是 Innodb 存储引擎实现的日志，只有InnoDB才有redo log。
• 2）文件格式不同：
  • binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，可以通过binlog_format参数指定，区别如下：
    • STATEMENT：每一条更新类的 SQL 语句原文都会被记录到 binlog 中，相当于记录了逻辑操作（所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端可以根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
    • ROW：记录行数据最终被修改成什么样了。不会出现动态函数的问题，但缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，会使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
    • MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；
  • redo log 是物理日志，记录的是在某个数据页做了什么修改。
• 3）写入方式不同：
  • binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量日志。
  • redo log 是循环写，日志空间大小是固定，全部写满就从头开始。
    所以如果数据库数据全部删除了的话，要用binlog而不是redo log来恢复所有数据。因为 redo log 会边写边擦除日志的，它只记录了未刷盘的数据；而 binlog 保存了所有数据变更的情况(全量日志)。
    
• 4）用途不同：
  • binlog 用于备份恢复、主从复制；
  • redo log 用于断电等故障恢复。
• 5）持久化时机不同：
  • redo log 可以在事务没提交之前持久化到磁盘；
  • binlog 必须在事务提交之后，才可以持久化到磁盘。

（2）MySQL如何实现主从复制？

        MySQL 集群的主从复制过程可以成为 3 个阶段：

• 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
  • 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
• 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
  • 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log（中继日志）里；
• 回放 Binlog：回放 binlog，并更新存储引擎中的数据。
• 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog ，更新存储引擎中的数据，最终实现主从的数据一致性。

【为什么要对MySQL进行主从复制？】

        

【从库是不是越多越好？】

• 异步复制（默认模型）：主库提交事务的线程并不会等待从库复制完成，就会给客户端返回结果。
  • 这种模式下一旦主库宕机，数据就会发生丢失。
• 同步复制：主库提交事务的线程要等待所有从库的复制完成后，才会给客户端返回结果。
  • 这种模式下缺点比较多，一是性能很差，因为要等待复制到所有从库(节点)才能给客户端返回响应；二是可用性也很差，主库和所有从库只要任何一个数据库出了问题，都会影响业务。
• 半同步复制：这是MySQL 5.7 才有的，介于同步和异步复制之间，事务线程不用等待所有的从库复制完成，只要一部分复制完成就可以给客户端返回响应结果，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。
  • 这种方式兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

（3）binlog 什么时候刷盘？

• 图中的 write指的是把日志写入到 binlog 文件，但是并没有把数据持久化到磁盘，因为数据还缓存在文件系统的 page cache 里。write 的写入速度比较快的，因为不涉及磁盘 I/O。
• 图中的 fsync才是将数据持久化到磁盘的操作，这里就会涉及磁盘 I/O，所以频繁的 fsync 会导致磁盘的 I/O 升高。

• sync_binlog = 0（默认）：表示每次提交事务都只 write，不 fsync，由操作系统决定何时刷盘；
  • MySQL默认的是 sync_binlog = 0，这时候的性能是最好的，但是风险也是最大的：因为一旦主机发生异常重启，还没持久化的数据就会丢失。
• sync_binlog = 1 ：表示每次提交事务 write 后，马上 fsync；
  • 当 sync_binlog = 1 的时候，是最安全但是性能最差的。因为即使主机发生异常重启，最多只会丢失一个事务的 binlog，但是频繁的磁盘 I/O 对性能影响太大。
• sync_binlog =N(N>1)：表示每次提交事务都 write，但累积 N 个事务后才 fsync。
  • 如果能接受少量事务的 binlog 丢失，为了提高写入的性能，一般会将 sync_binlog 设置为 100~1000 中的某个数值。

5.两阶段提交

• prepare 阶段：将 XID（内部 XA 事务的 ID） 和记录的修改写入 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用）；
• 写入binlog：把 XID 和更新操作写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用）；
• commit 阶段：调用引擎的提交事务接口，将 redo log 状态设置为 commit（此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功）。

• redo log 成功持久化，binlog 未持久化：
  • 如果将 redo log 持久化到磁盘之后， MySQL 突然宕机了，而 binlog 还没来得及写入磁盘，这时 MySQL 重启后，通过 redo log 能将 name 字段恢复到更新后的值"lisi"，但是 binlog 里面没有记录这条更新语句，所以从库的这一行记录的 name 字段仍是原来的"zhangsan"，导致与主库数据不一致。
• binlog 成功持久化，redo log 未持久化：
  • 如果将 binlog 持久化到磁盘之后， MySQL 突然宕机了，而 redo log 还没来得及写入磁盘。由于redo log没有持久化，所以此时 MySQL 重启后这个事务相当于无效了，所以这行数据的 name 字段还是原来的"zhangsan"，而 binlog 里面记录了这条更新语句，在从库中这一行记录的 name 字段就是更新后的"lisi"，也会导致与主库数据不一致。

• 如果 binlog 中没有 XID，说明 redo log 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。(时刻A)
• 如果 binlog 中有 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。(时刻B)

• 磁盘 I/O 次数高：
      binlog 和 redo log 在内存中都有对应的缓存空间，binlog 会缓存在 binlog cache，redo log 会缓存在 redo log buffer，它们持久化到磁盘的时机分别由 sync_binlog 和 innodb_flush_log_at_trx_commit 这两个参数控制。一般我们为了避免日志丢失，会将这两个参数设置为 1。这种 “双1” 配置，导致每个事务提交都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘。
• 锁竞争激烈：两阶段提交在单事务中一定能保证两个日志的数据一致，但在多事务情况下，由于不能保证提交的顺序一致，所以无法保证二者的数据一致性。因此，在两阶段提交的基础上，还需要加一个锁来保证提交的原子性，从而保证多事务情况下，两个日志的提交顺序一致。        

• flush 阶段：多个事务按进入的顺序将 binlog 从 cache 写入文件（不刷盘）；
• sync 阶段：对 binlog 文件做 fsync 操作（多个事务的 binlog 合并一次刷盘）；
• commit 阶段：各个事务按顺序进行提交。

• 在 MySQL 5.7 之前，每个事务各自执行 prepare 阶段，也就是各自将 redo log 刷盘，这样就没办法对 redo log 进行组提交。
• 在 MySQL 5.7 中， prepare 阶段不再让事务各自执行 redo log 刷盘操作，而是推迟到组提交的 flush 阶段，也就是说 prepare 阶段融合在了 flush 阶段。通过延迟写 redo log 的方式，为 redolog 做了一次组写入。

（8）MySQL 磁盘 I/O 很高，有什么优化的方法？

        事务在提交时需要将 binlog 和 redo log 持久化到磁盘(涉及到磁盘I/O)，如果出现磁盘 I/O 很高的现象，我们可以通过控制以下参数，来降低磁盘 I/O 的频率：

• 设置组提交的两个参数： binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，延迟 binlog 刷盘的时机，从而减少 binlog 的刷盘次数。
          【缺点】这个方法可能会增加语句的响应时间，但即使 MySQL 进程中途挂了，也没有丢失数据的风险，因为 binlog 早被写入到 page cache 了，只要系统没有宕机，缓存在 page cache(系统文件缓存) 里的 binlog 就会被持久化到磁盘。
• 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）：表示每次提交事务都 write，但累积 N 个事务后才 fsync，相当于延迟了 binlog 刷盘的时机。
          【缺点】主机掉电时会丢 N 个事务的 binlog 日志。
• 将 innodb_flush_log_at_trx_commit 设置为 ：表示每次事务提交时，都只是把缓存在 redo log buffer 里的 redo log 写到 Page Cache，然后由操作系统控制持久化到磁盘的时机。
          【缺点】主机掉电的时候会丢数据。

• 是否支持行级锁：MyISAM 最小的锁粒度是表级锁，而 InnoDB 还支持行级锁。也就是说 MyISAM 一锁就锁住了整张表，所以 InnoDB 在并发写情况下的性能比 MyISAM 好很多；
• 是否支持事务：MyISAM 不支持事务，而InnoDB 支持事务；
• 数据恢复：InnoDB 支持故障恢复，当数据库崩了，可以依靠redo log实现数据恢复；而 MyISAM 不支持数据恢复；
• 索引的实现：虽然 MyISAM 和 InnoDB 都用 B+树作为索引结构，但是实现方式不太一样：在 InnoDB 中，索引和数据都放在叶子节点上；而在 MyISAM 中，索引和数据是分离的。