面经：mysql数据库_假设系统中有两个表: 1)班级表 class(班级号 classid, 班内学生数 stucount

一. Mysql基础

1. 什么是关系型数据库？

关系型数据库（RDBMS，Relational Database Management System）就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。

2.什么是SQL？什么是Mysql？

SQL 是一种结构化查询语言(Structured Query Language)
MySQL 是一种关系型数据库，主要用于持久化存储我们的系统中的一些数据比如用户信息。

3.数据库三大范式

添加链接描述

• 第一范式：原子性
  属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式。
• 第二范式:主键相关
  确保表中的每列都和主键相关
• 第三范式：主键直接相关
  确保每列都和主键直接相关而不是间接相关
  

4. 主键和外键区别

主键(主码) ：主键用于唯一标识一个元组，不能有重复，不允许为空。一个表只能有一个主键。
外键(外码) ：外键用来和其他表建立联系用，外键是另一表的主键，外键是可以有重复的，可以是空值。一个表可以有多个外键。

5.Mysql常用的存储引擎有什么？它们有什么区别？

6. ER图

ER 图 全称是 Entity Relationship Diagram（实体联系图），提供了表示实体类型、属性和联系的方法。
实体 ：通常是现实世界的业务对象，当然使用一些逻辑对象也可以。比如对于一个校园管理系统，会涉及学生、教师、课程、班级等等实体。在 ER 图中，实体使用矩形框表示。(一个对象实例)
属性 ：即某个实体拥有的属性，属性用来描述组成实体的要素，对于产品设计来说可以理解为字段。在 ER 图中，属性使用椭圆形表示。 (对象的属性)
联系 ：即实体与实体之间的关系，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。例如，一个班级会有多个学生就是一种实体间的联系。

7. 触发器

触发器是与表有关的数据库对象，当触发器所在表上出现指定事件并满足定义条件的时候，将执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。触发器是一个特殊的存储过程，不同的是存储过程要用call来调用，而触发器不需要使用call，也不需要手工调用，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

• 例子：

假设系统中有两个表：
班级表 class(班级号 classID, 班内学生数 stuCount)
学生表 student(学号 stuID, 所属班级号 classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：
DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end$
DELIMITER ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

8. innodb下的数据文件类型

• 表结构文件：这些文件存储了数据库中表和其他对象的定义（例如索引、存储过程等）。表结构文件通常使用以 .frm为扩展名的文件。
• 数据文件：这些文件包含实际的数据行。每个表都有一个或多个数据文件，每个数据文件都可以包含多个数据页（即数据块）。数据文件通常使用以.ibd为扩展名的文件。
• 重做日志文件：重做日志文件记录了MySQL进行的所有更新操作。这些文件提供了故障恢复机制，可以在MySQL崩溃时帮助恢复所有尚未写入磁盘的更改。重做日志文件通常使用以.ib_logfile为前缀、以序号结尾的文件名（例如ib_logfile0、ib_logfile1等）来表示。

二. Mysql基础架构

1. 架构

• 大体来说，MySQL 可以分为 Server 层和存储引擎两部分。
• Server 层包括：连接器、查询缓存、分析器、优化器、执行器等，涵盖了 MySQL的大多数核心服务功能，以及所有的内置函数（如：日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如：存储过程、触发器、视图等等。
• 存储引擎层负责：数据的存储和提取。其架构是插件式的，支持 InnoDB、MyISAM 等多个存储引擎。从MySQL5.5.5版本开始默认的是InnoDB，但是在建表时可以通过 engine = MyISAM来指定存储引擎。不同存储引擎的表数据存取方式不同，支持的功能也不同。
• 从上图中可以看出，不同的存储引擎共用一个 Server 层，也就是从连接器到执行器的部分。
• 连接器的作用总结
  与客户端进行 TCP 三次握手建立连接；
  校验客户端的用户名和密码，如果用户名或密码不对，则会报错；
  如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限

2.一条sql查询语句的执行过程

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 执行 SQL：执行 SQL 共有三个阶段：
  预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
  优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
  执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

三. 索引

1.定义及优缺点

帮助Mysql高效获取数据的数据结构

• 优点
  加快数据检索速度；创建唯一性索引，保证数据库每一行数据的唯一性
• 缺点
  创建和维护索引需要时间；索引使用物理文件存储，消耗空间

2. 底层数据结构

2.1 Hash表

• Hash索引定位快：Hash索引指的就是Hash表，最大的优点就是能够在很短的时间内，根据Hash函数定位到数据所在的位置，这是B+树所不能比的。
• Hash冲突问题：知道HashMap或HashTable的同学，相信都知道它们最大的缺点就是Hash冲突了。不过对于数据库来说这还不算最大的缺点。
• Hash索引不支持顺序和范围查询(Hash索引不支持顺序和范围查询是它最大的缺点。

2.2 B树&B+树

• 两者区别
  B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体
• B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
  
• 数据库为什么用B+树，不用B树
• B树适合随机检索，B+树适合随机检索和顺序检索
• B+树空间利用率更高。因为B树每个节点都要存储键和值，B+树内部节点只存储键，减少I/O次数
• B+树的叶子节点都是连接在一起的，所以范围查找，顺序查找更加方便
• B+树性能更稳定，在B+树中，每次查询都是从根节点到叶子节点，而在B树中，要查询的值可能不再叶子节点，在内部节点就找到了
• hash结构与B+树区别
• 哈希索引不支持排序
• 哈希索引不支持范围查找
• 哈希索引不支持模糊查询及多列索引的最左前缀匹配
• 哈希表会存在哈希冲突，性能不稳定

2.3 为什么不用红黑树

在实际场景应用当中，MySQL表数据，一般情况下都是比较庞大、海量的。如果使用红黑树，树的高度会特别高，红黑树虽说查询效率很高。但是在海量数据的情况下，树的高度并不可控。如果我们要查询的数据，正好在树的叶子节点。那查询会非常慢。故而MySQL并没有采用红黑树来组织索引。
不管平衡二叉查找树还是红黑树，都会随着插入的元素增多，而导致树的高度变高，这就意味着磁盘 I/O 操作次数多，会影响整体数据查询的效率。

3. 索引类型总结

3.1 按照数据结构维度划分：

3.2 聚簇索引和非聚簇索引

• 聚簇索引（聚集索引）
  索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
  必须有，而且只有一个
  查询速度快，但更新代价大
• 非聚簇索引（非聚集索引）
  索引结构和数据分开存放的索引，**二级索引(辅助索引)**就属于非聚簇索引。
  可以存在多个
  更新代价比聚簇索引小，但可能有二次查询(回表)
• 二者对比(面试答这个就ok)
  聚集索引和非聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。 聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。
  聚集索引（Innodb）的叶节点就是数据节点，而非聚集索引(MyisAM)的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针。

3.3 按照应用维度划分(可以不看，避免乱)

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
普通索引：仅加速查询。
唯一索引：加速查询 + 列值唯一（可以有 NULL）。
覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

4. 主键索引与二级索引(辅助索引)

• 主键索引
  数据表的主键列使用的就是主键索引。
  一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
  在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
• 二级索引(辅助索引):叶子节点存主键！
  二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
  唯一索引，普通索引，前缀索引等索引属于二级索引。

5.覆盖索引和联合索引

• 覆盖索引
  如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”
  覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。
  第一个不需要回表，聚簇索引把数据和索引放在一起了。第二个语句就是覆盖索引，第三个需要回表查询。所以说要尽量避免使用select *
• 联合索引
  使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

6. 前缀索引

当字段类型为字符串时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

7. 最左前缀匹配原则

在使用联合索引，Mysql会根据联合索引的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、<）才会停止匹配。所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

8.非聚簇索引一定回表查询吗?

覆盖索引 不一定

9. 索引为什么会失效？

• 根本原因：
  全局扫描的效率高于建立索引
  索引涉及强制的类型转换
  索引上做相关的运算操作
• 具体表现：
  (1) 组合索引未使用最左前缀，例如组合索引（A，B），where B=b不会使用索引；
  (2) like未使用最左前缀，where A like ‘%China’；
  (3) 搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引 ；
  (4) or会使索引失效。如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）
  (5) 如果列类型是字符串，要使用引号。例如where A=‘China’，否则索引失效（会进行类型转换）；
  (6) 在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等；select sum(b) from table where b = 6;
  (7)在索引上进行计算,如select * from table where a+1=2;
  (8)索引字段上使用is null/is not null判断。如selct * from table where a is not null;
  (9)**select *** 尽量少用。 Select _ 在一些情况下是会走索引的 如果不走索引就是 where 查询范围过大 导致 MySQL 最优选择全表扫描了 并不是 Select _ 的问题(就是走全表扫描更快)
  文章里提到的原理,为什么不符合最左前缀就失效:b的有序是建立在a基础上的

10. 如何分析索引失效(Explain计划)

explain 执行计划来分析索引失效
资料1
资料2
资料3
explain每一列的含义

• id：选择标识符
  id 列的编号就是 select 的序列号，也可以理解为 SQL 执行顺序的标识，有几个 select 就有几个 id
• select_type: 表示查询的类型。
• table：输出结果集的表
• type：表示表的连接类型
  访问类型，即 MySQL 决定如何查找表中的行。
  依次从好到差：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，除了 all 之外，其他的 type 都可以使用到索引，除了 index_merge 之外，其他的 type 只可以用到一个索引。一般来说，得保证查询至少达到 range 级别，最好能达到 ref。
  range：索引范围扫描，常见于使用>,<,between ,in ,like等运算符的查询中。
  index：索引全表扫描，把索引树从头到尾扫一遍；
  all：遍历全表以找到匹配的行（Index 与 ALL 虽然都是读全表，但 index 是从索引中读取，而 ALL 是从硬盘读取）
• possible_keys：表示查询时，可能使用的索引
• key：表示实际使用的索引
  显示查询实际使用哪个索引来优化对该表的访问；
  select_type 为 index_merge 时，这里可能出现两个以上的索引，其他的 select_type 这里只会出现一个。
• key_len：索引字段的长度
• ref：列与索引的比较
• rows：扫描出的行数(估算的行数)
• Extra：执行情况的描述和说明
  如果是Using index，则说明查询是覆盖了索引的，不需要读取数据文件

11. 索引下推ICP

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。
添加链接描述

12. 联合索引是否失效问题

1.select * from T where a=x and b=y and c=z
2.select * from T where a=x and b>y and c=z
3.select * from T where c=z and a=x and b=y
4.select (a,b) from T where a=x and b>y
5.select count(*) from T where a=x
6.select count(*) from T where b=y
7.select count(*) form T
8.select * from T where a=x and c=z
1
2
3
4
5
6
7
8

1.a、b、c三个字段都可以走联合索引
2.a走索引，b因为在索引中有序，依旧可以走索引，c需要回表查询。
(但是在 mysql 5.6 版本后，c 字段虽然无法走联合索引，但是因为有索引下推的特性，c 字段在 inndob 层过滤完满足查询条件的记录后，才返回给server 层进行回表，相比没有索引下推，减少了回表的次数。)
3.查询条件的顺序不影响，优化器会优化，所以a、b、c三个字段都可以走联合索引
4.a和b都会走联合索引，查询是覆盖索引，不需要回表
5.a 可以走联合索引
6.只有b，无法使用联合索引，由于表存在联合索引，所以 count(*) 选择的扫描方式是扫描联合索引来统计个数，扫描的方式是type=index
7.由于表存在联合索引，所以 count(*) 选择的扫描方式是扫描联合索引来统计个数，扫描的方式是type=index
8.只有a列使用了索引，c列没有使用索引，因为中间跳过了b列
1
2
3
4
5
6
7
8
9

13. 如何创建索引？

• 什么时候需要索引：
  1.主键自动建立唯一索引。
  2.频繁作为where条件语句查询的字段
  3.关联字段需要建立索引，例如外键字段，student表中的classid, classes表中的schoolid 等
  4.排序字段可以建立索引
  5.分组字段可以建立索引，因为分组的前提是排序
  6.统计字段可以建立索引，例如count(),max()

• 什么时候不需要索引：
  (1) 对于具有许多重复值的列，添加索引的性能提升可能不明显。
  (2) 尽量避免在非常大的表上创建过多索引，因为这会影响插入和更新操作的性能。
  (3) 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的
  (4**)数据量小**：如果数据表中的数据量非常小，建立索引可能会增加查询时的开销，因为数据库引擎会在索引和实际数据之间进行切换，导致查询变慢。

• 语法

CREATE INDEX index_name ON table_name(column_name);  
CREATE INDEX index_product_no_name ON product(product_no, name);
1
2

14. 如何优化索引

前缀索引优化；
覆盖索引优化；
主键索引最好是自增的；
防止索引失效；

15. count(1) count(*) count(字段)

添加链接描述

count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。
该函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。
server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端。

count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。

16. B+树能存储多少数据？

添加链接描述
每个节点（每一页） 16kb大小
主键BigInt 8个byte，int 4个byte，指针设置6个byte； 非叶子节点
数据只存在叶子节点，假设1kb 叶子节点

    两层总数 = 非叶子节点(根) * 叶子节点。
    三层总数 = 非叶子节点(根) * 非叶子节点 * 叶子节点。
1
2

17. 联合索引建立原则

• 选择合适的列
  经常用于查询，分组，排序的列
• 确定索引顺序
  选择性高的放前面，选择性指的是列中不同值的数量与总行的比例

四、事务

1. 数据库事务的定义

数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执行成功,要么全部不执行 。

2. 事务四大特性ACID

原子性
包含事务的操作要么全部执行成功，要么全部失败回滚
一致性
事务在执行前后状态一致。例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
隔离性
一个事务所进行的修改在最终提交前，对其他事务是不可见的
持久性
数据一旦提交，其所作的修改将永久保存到数据库中

3. 数据库的并发一致性问题

• 脏读
  如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。
  
• 不可重复读
  事务2对数据多次读取，事务1在事务2读取数据这个过程中执行了更新操作并提交了，导致事务2多次读取到数据不一致
  
• 幻读
  在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。
  
• 丢失修改
  在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。(多个事务修改同一数据)
  

4. 数据库的隔离级别

• 未提交读
  最低级别，一个事务在提交前，它的修改对其他事务也是可见的。
• 提交读
  一个事务提交后，它的修改才能被其他事务看见
• 可重复读
  在同一个事务中多次读取到数据是一致的
• 串行化
  需要加锁实现，会强制事务串行执行.
  会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；
  
  Mysql的隔离级别默认是不可重复读
  可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；

5. 隔离级别是如何实现的

锁和MVCC(多版本并发控制)
锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。
串行化隔离级别是通过锁来实现的，提交读 和 可重复读 隔离级别是基于 MVCC 实现的

6. 如何避免幻读

7. MVCC多版本并发控制

• MVCC作用
  在不加锁的情况下，解决数据库读写冲突问题，并且解决脏读，幻读，不可重复读等问题，但不能解决丢失修改的问题。

• 底层实现
  MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

• 隐藏字段
  在内部，InnoDB 存储引擎为每行数据添加了三个 隐藏字段
  

• Read View（可见性）
  做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”
  活跃事务：启动了但还没提交的事务

可见性三种情况讨论：
如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
处于中间，如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

• Undo Log日志
  
  作用有2点：

1. 当事务回滚时用于将数据恢复到修改前的样子
2. 另一个作用是 MVCC ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读

• 可重复读下，Innodb的工作流程
  
  记住两个版本号去分析，创建版本号和查询版本号
• 比较RR可重复读和RC提交读的区别
  

8. 当前读和快照读有什么区别

**当前读 （一致性锁定读）**就是给行记录加 X 锁或 S 锁。
快照读（一致性非锁定读）就是单纯的 SELECT 语句
只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用一致性非锁定读
一般情况下select * from …where …是快照读，不会加锁，
而 for update,lock in share mode,update,delete都属于当前读

9. 生成快照的时机

• 在读已提交隔离级别下，快照是什么时候生成的？
  SQL语句开始执行的时候。
  若一个事务中存在多个select查询，每个select开始执行的时候都会生成readView
• 在可重复读隔离级别下，快照是什么时候生成的？
  事务开始的时候
  若一个事务中存在多个select查询，在事务开始的时候生成readView。所以多个select(如果查询条件相同)查询的结果是一样的。(原因)

10. 数据库创建视图read view的时机

读未提交：没有视图的概念，直接返回记录上的最新值；
读已提交：每个SQL语句执行时重新创建一个视图；
可重复读：事务启动时创建视图，整个事务期间查询操作都是使用这个视图
串行化：直接加锁避免并行访问；

11. 事务开启命令和创建read view的时机

• 在 MySQL 有两种开启事务的命令，分别是：
  第一种：begin/start transaction 命令；
  第二种：start transaction with consistent snapshot 命令；
• 这两种开启事务的命令，创建 read view 的时机是不同的：
  执行了 begin/start transaction 命令后，并不会创建 read view，只有在第一次执行 select 语句后， 才会创建 read view。
  执行了 start transaction with consistent snapshot 命令，就会马上创建 read view。

12. innodb如何避免不可重复读？

mysql的默认隔离级别是可重复读，可重复读隔离级别在开启事务后，执行第一个selete 语句的时候，会生成一个 Read View，后面整个事务期间的selete都在用这个 Read View，所以事务期间上读取的数据都是一致的，不会出现前后读取的数据不一致的问题，所以避免了不可重复读。

13. 四种事务隔离级别如何实现

对于读未提交隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
对于串行化隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
对于读提交和可重复读隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

五、锁

1. 锁的分类

1.1 全局锁、表级锁、行级锁

MyISAM默认是表级锁，InnoDB默认行级锁

• 全局锁
  
• 表级锁
  MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，主要分为三类：表锁；元数据锁；意向锁
• 行级锁
  MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。InnoDB有三种行锁：记录锁、间隙锁、临键锁
  

1.2 共享锁(读锁、S锁) 与 排他锁(写锁、X锁)

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类

• 共享锁
  一个事务对一个数据对象加了读锁，可以对这个数据对象进行读取操作，但不能进行更新操作。并且在加锁期间其他事务只能对这个数据对象加读锁，不能加写锁。
• 排他锁
  一个事务对一个数据对象加了排他锁，可以进行读取和更新操作，枷锁期间，其他事务不能对该数据对象进行加X锁或S锁
  排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。
  

2、三类表级锁：表锁；元数据锁；意向锁

• 表锁
  表锁就是对整个表进行上锁；表锁细分为读锁和写锁；读锁之间不互斥，写锁和写锁、写锁和读锁之间互斥；
• 元数据锁
  元数据锁就是针对表结构上的锁，简称MDL（Meta data lock）；它的目的就是为了保证数据访问的准确性，如果访问数据时，被别人修改了表结构，那么读到的数据就不准确了；
• 意向锁
  如果需要用到表锁的话，如何判断表中的记录没有行锁呢，一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东西来快速判断是否可以对某个表使用表锁。
  
  意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
  意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

3、三类行锁：记录锁、间隙锁、临键锁

• 记录锁
  属于单个行记录上的锁。
  

• 间隙锁
  Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。
  假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。
  
  间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

• 临键锁
  Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
  next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。
  

• 加锁的范围
  

4、如何避免死锁和锁冲突

死锁是指两个或两个以上进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象。

• 避免死锁和锁冲突
  
  
  低隔离级别！

5、乐观锁和悲观锁

6、锁与隔离事务级别的关系.

7. 死锁

7.1 如何排查死锁

查看线程情况 添加链接描述
在分析innodb中锁阻塞时，几种方法的对比情况：
（1）使用show processlist查看不靠谱；
（2）直接使用show engine innodb status查看，无法判断到问题的根因；
（3）使用mysqladmin debug查看，能看到所有产生锁的线程，但无法判断哪个才是根因；
（4）开启innodb_lock_monitor后，再使用show engine innodb status查看，能够找到锁阻塞的根因。

7.2 预防死锁

尽量避免大事务。锁的生命周期是从加锁到事务提交才会释放，所以事务越大，其持有的锁越多，更容易造成死锁。
合理设计索引。区分度较高的提到联合索引前面，使查询通过索引定位到更少的行，减少加锁范围。反例：“update … where name = ‘xxx’”, name 字段上没有索引，这个语句将会对全表加锁。
统一执行顺序。不仅是加锁访问不同表数据的顺序要一致，对同一个表的加锁访问也得一致。而且比较容易忽视。
对热点数据尽量放在事务后面，减少加锁时间，减少锁冲突，提高并发。

六、SQL语句的基础

1. 分类

数据库定义语言DDL：create、drop等对逻辑结构有操作的
数据操纵语言（DML）：CRUD
事务控制语言 (TCL)：用于管理数据库中的事务
数据控制语言DCL：主要是权限控制操作，包括grant、revoke

2. 约束

3. 子查询

一个查询的结果在另外一个查询中用

4. 连接查询

• 外连接
  左外链接：左表不符合的也会显示

右外链接

Mysql不支持全外连接

• 内连接：只显示符合条件的
  

• 交叉连接
  使用笛卡尔积

5. 主键一般用自增id还是UUID

自增ID，Mysql的InnoDB存储引擎中，主键索引是一种聚簇索引，主键索引的B+树的叶子节点按照顺序存储了主键值及数据，如果主键索引是自增ID，只需要将顺序往后排列即可，如果是UUID,ID是随机生成的，在插入数据时会造成大量的数据移动，产生大量的内存碎片，造成插入性能下降。

6. 分页查询

select * from table limit (start-1)*pageSize,pageSize;
1

使用mybatis-plus

    @Test
    public void testPage() {
        System.out.println("----- selectPage method test ------");
        //分页参数
        Page<User> page = Page.of(1,10);

        //queryWrapper组装查询where条件
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(User::getAge,13);
        userMapper.selectPage(page,queryWrapper);
        page.getRecords().forEach(System.out::println);
    }
1
2
3
4
5
6
7
8
9
10
11
12

7. char和varchar

char的长度是不可变的，而varchar的长度是可变的，也就是说，定义一个char[10]和varchar[10],如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个空格，而varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的。

8. 插入语句

9. On和Where区别

在内连接中，使用on或者where没有区别。
在外连接里，例如使用left join时：
on是在生成临时表时使用的条件，不管on的条件是否为真，都会返回左边表中的全部记录。
where条件是在临时表生成好后，再对临时表进行过滤的条件，条件不为真的记录就全部过滤掉，包括左边的表。

10. drop delete truncate的区别

七、三大日志

MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）。
MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
MySQL数据库的数据备份、主备、主主、主从都离不开bin log，需要依靠binlog来同步数据，保证数据一致性。

哪个层(除了undo),什么时候产生，有什么作用

1. bin log归档日志

binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。
作用：数据备份、主备、主主、主从 读写分离，保持数据一致性

2. redo log重做日志

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。
比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

2.0 总结

事务的持久性是通过 redo log 实现的。

我们修改某条记录，其实该记录并不是马上刷入磁盘的，而是将 Innodb 的 Buffer Pool 标记为脏页，等待后续的异步刷盘。

Buffer Pool 是提高了读写效率没错，但是问题来了，Buffer Pool 是基于内存的，而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失。

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改**以 redo log 的形式记录下来，**这个时候更新就算完成了。

后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

2.1 原理

• MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool中。
• 后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。
• 更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。
• 把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

2.2 刷盘

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：

• 0：设置为 0 的时候，表示每次事务提交时不进行刷盘操作1：
• 设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）
• 2：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache

2.3 日志文件组

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。

3. undo log回滚日志

我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

4. binlog和relog对比

• redo log是InnoDB引擎特有的，只记录该引擎中表的修改记录。binlog是MySQL的Server层实现的，会记录所有引擎对数据库的修改。
• redo log是物理日志，记录的是在具体某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑。
• redo log是循环写的，空间固定会用完；binlog是可以追加写入的，binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

5. 两阶段提交

在准备阶段，MySQL先将数据修改写入redo log，并将其标记为prepare状态，表示事务还未提交。然后将对应的SQL语句写入bin log。
在提交阶段，MySQL将redo log标记为commit状态，表示事务已经提交。然后根据sync_binlog参数的设置，决定是否将bin log刷入磁盘。

• 为什么需要两阶段提交
  如果只有 redo log 或者只有 binlog，那么事务就不需要两阶段提交。但是如果同时使用了 redo log 和 binlog，那么就需要保证这两种日志之间的一致性。否则，在数据库发生异常重启或者主从切换时，可能会出现数据不一致的情况。
  因为当数据库重启时，从库会基于bin log进行数据同步和恢复,如果主库binlog已经commit标记，而事务未提交，而从库根据Binlog同步后提交了，那么数据就存在不一致，说到底也就是保证数据的一致性。
  添加链接描述
  添加链接描述

八、数据库优化

1. 大表如何优化

限定数据范围
读写分离
垂直分表
水平分表
对单表进行优化：对表中的字段、索引、查询进SQL进行优化
添加缓存

2. 慢查询优化

慢查询日志

• 先分析 SQL 查询语句本身
  检查 SQL 代码的正确性以及语法，在查询语句中是否存在笛卡尔积、全表扫描、死循环等不合理的操作。 分析 SQL 是否可以优化，通过缩小查询范围、减少排序或聚合等操作来优化查询效率。
• 执行计划分析
  使用 explain 命令获取 SQL 执行计划，并查看 rows 等指标，确认是否存在全表扫描、索引失效、连接方式不当等问题。 根据执行计划和具体数据量，使用实际执行的时间预估执行时间，检查该 SQL是否会因数据量增大而导致性能下降。
• 数据库状态分析
  检查数据库状态信息，例如是否有锁等待、是否存在瓶颈，了解系统实际负载情况，例如 CPU 利用率、磁盘 I/O 等情况。
• 执行时间分析
  使用 MySQL 内置的性能分析工具（例如 pt-query-digest），统计 SQL 执行时间和调用次数等信息，进一步了解影响 SQL 性能的因素。
• 实际测试验证
  将分析后的优化方案应用到 SQL 中，通过实际测试数据中的执行效率等指标来验证是否能够解决该 SQL 的性能问题。 以上几个方面是排查慢 SQL 的常用方法，但具体方法还要根据具体情况而定。

3. 分库分表

3.1 什么是分库?分表?

分库 就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。
垂直分库 就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。
水平分库 是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。
分表 就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。
垂直分表 是对数据表列的拆分，把一张列比较多的表拆分为多张表。
水平分表 是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。

3.2 什么情况下需要分库分表

单表的数据达到千万级别以上，数据库读写速度比较缓慢。
数据库中的数据占用的空间越来越大，备份时间越来越长。
应用的并发量太大

3.3 常见的分片算法有哪些？

分片算法主要解决了数据被水平分片之后，数据究竟该存放在哪个表的问题。
哈希分片：求指定 key（比如 id） 的哈希，然后根据哈希值确定数据应被放置在哪个表中。哈希分片比较适合随机读写的场景，不太适合经常需要范围查询的场景。
范围分片：按照特性的范围区间（比如时间区间、ID 区间）来分配数据，比如 将 id 为 1~299999 的记录分到第一个库， 300000~599999 的分到第二个库。范围分片适合需要经常进行范围查找的场景，不太适合随机读写的场景（数据未被分散，容易出现热点数据的问题）。
地理位置分片：很多 NewSQL 数据库都支持地理位置分片算法，也就是根据地理位置（如城市、地域）来分配数据。

3.4 分库分表会带来什么问题呢？

• join 操作
  同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装，比如你在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据。(分两次查询实现)
• 事务问题
  同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。(分布式事务)
• 分布式 id
  分库之后， 数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。我们如何为不同的数据节点生成全局唯一主键呢？这个时候，我们就需要为我们的系统引入分布式 id 了。
• 跨节点的count,order by,group by以及聚合函数问题
  分别在各个节点上得到结果后在应用程序端进行合并。

3.5 分库分表后，数据怎么迁移呢？

停机迁移，写个脚本老库的数据写到新库中。比如你在凌晨 2 点，系统使用的人数非常少的时候，挂一个公告说系统要维护升级预计 1 小时。然后，你写一个脚本将老库的数据都同步到新库中。
双写方案: 我们对老库的更新操作（增删改），同时也要写入新库（双写）。如果操作的数据不存在于新库的话，需要插入到新库中。 这样就能保证，咱们新库里的数据是最新的。在迁移过程，双写只会让被更新操作过的老库中的数据同步到新库，我们还需要自己写脚本将老库中的数据和新库的数据做比对。如果新库中没有，那咱们就把数据插入到新库。如果新库有，旧库没有，就把新库对应的数据删除（冗余数据清理）。
想要在项目中实施双写还是比较麻烦的，很容易会出现问题。我们可以借助上面提到的数据库同步工具 Canal 做增量数据迁移（还是依赖 binlog，开发和维护成本较低）。

3.6 分库分表有没有什么比较推荐的方案？

ShardingSphere 项目

4. 实现读写分离,主从复制

4.1 如何实现读写分离

• 部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。
• 保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是我们常说的主从复制。
• 系统将写请求交给主数据库处理，读请求交给从数据库处理。
• 具体实现:通过引入第三方组件来帮助我们读写请求,推荐使用 sharding-jdbc

4.2 读写分离会带来什么问题？如何解决？

• 主从同步延迟
• 解决:强制将读请求路由到主库处理,既然你从库的数据过期了，那我就直接从主库读取嘛！这种方案虽然会增加主库的压力，但是，实现起来比较简单，也是我了解到的使用最多的一种方式。比如Sharding-JDBC 就是采用的这种方案。通过使用 Sharding-JDBC 的 HintManager分片键值管理器，我们可以强制使用主库。

4.3 主从复制原理是什么

添加链接描述

• 主库将数据库中数据的变化写入到 binlog
• 从库连接主库
• 从库会创建一个 I/O 线程向主库请求更新的 binlog
• 主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收
• 从库的 I/O 线程将接收的 binlog 写入到 relay log 中。
• 从库的 SQL 线程读取 relay log 同步数据本地（也就是再执行一遍 SQL)

4.4 主从复制的作用

• 第一个作用：读写分离。
  我们可以通过主从复制的方式来同步数据，然后通过读写分离提高数据库并发处理能力。主库，写；从库，读。
• 第2个作用就是数据备份。
  我们通过主从复制将主库上的数据复制到了从库上，相当于是一种热备份机制，也就是在主库正常运行的情况下进行的备份，不会影响到服务。
• 第3个作用是具有高可用性。
  数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正常运行。

5. 假如你所在的公司选择MySQL数据库作数据存储，一天五万条以上的增量，预计运维三年，你有哪些优化手段？

• 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
• 选择合适的表字段数据类型和存储引擎，适当的添加索引。
• MySQL库主从读写分离。
• 找规律分表，减少单表中的数据量提高查询速度。
• 添加缓存机制，比如Memcached，Apc等。
• 不经常改动的页面，生成静态页面。
• 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE。