MySQL面试整理

MySQL

1、基础

什么是MySQL？

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。

• MySQL是关系型数据库，把数据存在二维表中，把数据存储在硬盘中，读取速度较慢；不受空间容量限制，性价比较高。
• 是由二维表以及表之间的关系所构成的一个数据集合。

关系型数据库与非关系型区别？🌟

• 关系型数据库：是由二维表以及表之间的关系所构成的一个数据集合。
• 非关系型数据库NOSQL：指非关系型的，数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定，常用于存储非结构化的数据。一般不确保遵照ACID标准的数据储存系统。
  • 主流的 NoSQL 数据库有 Redis、MongBD、Hbase、CouhDB 等

1. 储存格式：
   • 关系型数据库：是把数据存在二维表中，只支持基础类型。
   • 非关系型数据库：是将数据存在数据集中，可以存储的格式有key-value形式(Redis)、文档形式(MongoDb)、图片形式(Infinite Graph)等等，更为灵活。
2. 查询效率：关系型数据库存储于磁盘，非关系型数据库存储于缓存，效率比关系型数据库更高。
3. 预定义结构 VS 动态结构：
   • 关系型数据库：必须定义好表结构和字段后，才能够添加数据，例如定义表的主键、索引、外键等。表结构可以在定义之后更新，但是如果有比较大的结构变更，就会变的比较复杂。
   • 非关系型数据库：数据可以在任何时候任何地方添加。不需要预先定义
4. 扩展方式不同：（为了支持更多的并发量）
   • 关系型数据库：纵向扩展，通过提高计算机性能来提高处理能力。但是数据存储在关系表中，操作的性能瓶颈可能涉及很多个表，这都需要通过提高计算机性能来克服。虽然SQL数据库有很大扩展空间，但最终肯定会达到纵向扩展的上限。
   • 非关系型数据库：横向扩展的。因为非关系型数据存储天然就是分布式的，NoSQL数据库的扩展可以通过给资源池添加更多普通的数据库服务器(节点)来分担负载（通过集群来实现负载均衡）。
5. 对事务性的支持不同：
   • 关系型数据库：支持事务，能保证系统中事务的正确执行，也提供了事务的恢复、回滚、并发控制和死锁问题的解决。
   • 非关系型数据库：没有事务处理，无法保证数据的完整性和安全性。适合处理海量数据，但是不一定安全。
     （NoSQL数据库也可以使用事务操作，但稳定性方面没法和关系型数据库比较，所以它们真正闪亮的价值是在操作的扩展性和大数据量处理方面。）
6. 成本：非关系型数据库基本是开源的，关系型数据库需要购买。不需要像oracle花费大量的成本购买

• 非关系型数据库的类型：
  • 键值储存数据库
  • 列储存数据库
  • 文档型数据库
  • 图数据库

数据库三范式？🌟

1. 第一范式（1NF）：每一列都不可以再分；
   • 确保每一列的原子性。
2. 第二范式（2NF）：属性完全依赖于主键（不存在部分依赖）。满足第二范式（2NF）必须先满足第一范式（1NF）。
   • 可以保证每行都能被唯一地区分。（每行都有主键能进行区分）
3. 第三范式（3NF）：非主属性只依赖于主键，不依赖于其他非主属性（不存在传递依赖）。满足第三范式（ 3NF） 必须先满足第二范式（ 2NF）。
   • 简单理解：每一个表都不包含其他表已经包含的非主键信息。

注意：一般在建数据库的时候不会用到三大范式；设计数据库可能导致字段冗余，用来减少查库次数，一个系统的瓶颈一般都是数据库。数据库字段冗余最多就是浪费一些磁盘空间，但是提升了性能。

三范式带来的问题？

MYSQL支持哪些存储引擎？🌟

• 存储引擎是基于表的，而不是数据库。

1. MySQL支持多种存储引擎，比如InnoDB, MyISAM , Memory , Archive等。
2. MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB是 MySQL 的默认存储引擎。只有InnoDB支持事务。

MyISAM 和 InnoDB 的区别是什么？

• MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

1. InnoDB支持事务，MyISAM 不支持；
2. InnoDB支持数据库异常崩溃后的安全恢复；MyISAM不支持。
   • 使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log(重做日志) 记录在哪个数据页上做了哪些修改。
3. InnoDB是聚集索引，数据与索引存在一起；MyISAM是非聚集索引，数据与索引分开存储。
4. InnoDB支持行级锁和表级锁，默认行级锁；MyISAM支持表级锁。
   • 也就说，MyISAM 一锁就是锁住了整张表，这在并发写的情况下是多么滴憨憨啊！这也是为什么 InnoDB 在并发写的时候，性能更牛皮了！
5. InnoDB支持MVCC，而MyISAM不支持；
   • 这个对比有点废话，毕竟 MyISAM 连行级锁都不支持。MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能。
6. InnoDB支持外键、MyISAM 不支持；
   • 外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！

• MyISAM 和 InnoDB 如何选择？
  

SQL约束？

1. NOT NULL 非空约束：某列不能存储NULL值。
2. UNIQUE 唯一约束：保证某列的值不重复。
3. PRIMARY KEY 主键约束：是NOT NULL 和UNIQUE的结合。保证某列有唯一标识。
4. FOREIGN KEY 外键约束：保证一个表中的数据 匹配 另一个表中的值 的参照完整性。
5. DEFAULT 默认约束：规定数据的默认值。
6. CHECK 检查约束：保证列中的值符合指定条件。

添加、删除约束的 SQL语句

非空约束：
## 创建表时添加非空约束：
<字段名> <数据类型> NOT NULL;
## 修改表时添加非空约束
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NOT NULL;
## 删除非空约束
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NULL;

唯一约束：
## 创建表时添加唯一约束：
<字段名> <数据类型> UNIQUE
## 修改表时添加唯一约束：
ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
## 删除唯一约束：
ALTER TABLE <表名> DROP INDEX <唯一约束名>;

🌟 查询：

MySQL执行查询的过程？

1. 客户端通过TCP连接 发送 连接请求到MySQL连接器，连接器会对该请求进行权限验证；
2. 查询缓存。（当判断缓存是否命中时，MySQL不会进行解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息）
3. 没有命中缓存的话，SQL语句就会经过分析器，分析器其实就是先看SQL语句要干嘛，再检查SQL语句语法是否正确。
4. 然后经过优化器。是否使用索引，生成执行计划。
5. 执行器执行语句，将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。在执行语句之前会先判断是否有权限，如果没有权限就会报错。
   

sql格式

select [all | distinct] <目标列表达式> [,<目标列表达式>]...
from <表名或视图名> [,<表名或视图名>...]｜(<select语句>)[AS]<别名>
where <条件表达式>
group by <列名> having <条件表达式>
order by <列名> [ASC|DESC];
limit [m],n;

在查询过程中执行顺序：FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT。

分页查询 limit？🌟

• limit：select * from 表名 limit [m],n;
  • m：[m]为可选，就是指跳过m条；
  • n：就是显示多少条记录。指从第m+1条记录开始，取n条记录。
• 例子：
  • 要查student表的前5条，对应的SQL语句就是：select * from stu limit 5;。
  • 要查Student表的第3-6条对应的SQL语句就是：select * from student limit 2,4;。
• 分页查询，对应的SQL语句是：select * from 表 limit (curPage-1)*pageSize ,pageSize;
  • 例如查Student表的第3页，每页显示10条，对应的SQL语句是：
    select * from Student limit (3-1)*10 , 10;

having和where区别、聚合函数？

1. • having是在分组后对数据进行过滤；
   • where是在分组前对数据进行过滤；
2. • having后面可以使用聚合函数；
   • where后面不可以使用聚合；

• SQL聚合函数：
  • AVG()：计算平均值。
  • COUNT()：统计数量。
  • MAX()：返回集合中的最大值。
  • MIN()：返回集合中的最小值。
  • SUM()：求和。

SQL聚合函数

in 和 exists 区别？🌟

1. • in：是把外表和内表作hash连接，先查询内表，再把内表结果与外表匹配。
   • exists：是对外表作loop循环，每次loop循环再对内表进行查询；如果内层查询结果非空，则外层的where条件就为真true，返回当前循环到的这条记录；如果内存循环查询结果为空，就where条件就为false，当前循环到的这条记录就会被丢弃。（带有exists的子查询只产生逻辑真true和逻辑假false。）
     • 即 如果A表有n条记录，那么exists查询就是将这n条记录逐条取出，然后判断n遍exists条件。
2. in查询的子条件返回结果必须只有一个字段，例子如下；exists没有这个限制。
   select * from user where user_id in (select id from B);
3. 大表连小表(子查询表小)用in；小连大(外表小，子查询表大)用exists。

• in查询相当于多个or条件的叠加。这个比较好理解，比如下面的查询：

select * from user where user_id in (1, 2, 3);

等效于

select * from user where user_id = 1 or user_id = 2 or user_id = 3;

• 总结：in查询就是先将子查询条件的记录全都查出来，假设结果集为B，共有m条记录，然后再将子查询条件的结果集分解成m个，再进行m次查询。

delete、truncate、drop 区别？🌟

1. 属于不同的数据库语言、提交方式、回滚：
   • delete：属于DML语句（数据库操作语言）；事务提交之后才能生效，可以回滚，可以触发触发器；
   • truncate和drop：属于DDL语句（数据库定义语言）；会隐式提交，执行后会自动提交，操作是立即生效的，不能回滚，不会触发触发器。。
2. 删除内容：
   • delete：根据where条件删除表中符合条件的数据行；如果不指定where语句，就和truncate一样删除所有表中所有数据，表结构也还在。
   • truncate：一次性删除表中所有数据（通过释放存储表数据所用的页来删除数据），表结构还在。
     • 再插入数据时，自增id又从1开始，在清空表中数据的时候使用。
   • drop：是删除整个表结构和数据。在删除表时使用。
     • 但是被依赖的约束、触发器、索引、依赖于该表的存储过程、函数将被保留，但其状态会变为invalid。
3. 删除速度：一般而言，drop > truncate > delete
   • delete：执行的时候会产生数据库的binlog日志，记录日志是需要消耗时间的，所以速度慢一些。
   • truncate：执行时不会产生数据库日志，所以比delete快一些。（除此之外，还会把表的自增id重置从1开始，将索引页恢复到初始大小）
   • drop：会把表占用的空间全部释放掉。
4. 使用场景：
   • （1）如果想删除表，用drop；
   • （2）如果想保留表但是清空表中的数据，而且和事务无关，用truncate即可；
   • （3）如果和事务有关，或者想触发trigger，还是用delete；

• delete：

 删除表中全部数据:
delete from 表名
 按条件删除:
delete from 表名 where 条件

• truncate：

删除表中所有数据且不可恢复:
truncate from 表名

• drop：

- 删除 表:
drop table 表名
- 删除数据库:
drop database 数据库名

DML、DDL、DCL、DQL区别？

1. DML：数据库操作语言，是对表中的数据进行操作。包括新增insert、删除delete、更新update、查询select。

SELECT 列名称 FROM 表名称
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
insert into 表名(字段1,字段2,字段3,...) values (值1,值2,值3,...)
DELETE FROM 表名称 WHERE 列名称 = 值

3. DDL：数据库定义语言，主要是用于定义数据库、表、字段等数据库内部对象。包括create、修改Alter、删除drop。

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

ALTER TABLE table_name
ALTER COLUMN column_name datatype

DROP TABLE 表名称
DROP DATABASE 数据库名称

3. DCL：数据控制语言，用来创建和管理数据库用户，控制数据库的访问权限。
   grant：授予权限给用户
   revoke：撤销某用户的权限

创建用户：
create user '用户名'@'主机名' identified by '密码';
查询权限：
SHOW GRANTS FOR '用户名'@'主机名';
授予权限:
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限:
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

5. DQL：数据查询语言，主要用于查询数据库中表的数据。

select 
		字段名
from
		表名列表
where
		条件列表
group by
		分组字段列表
having
		分组后条件列表
order by
		排序字段列表
limit
		分页参数

内连接、外连接？🌟

链接: 解释一下MySQL中内连接，外连接等的区别

• MySQL查询可以分为内连接、外连接。
  • 内连接（inner join）：是组合两个表中的记录，只返回符合连接条件的记录，也就是返回两个表的交集。
  • 外连接（outer join）：不仅返回符合连接条件的记录，也会返回不相符的记录，对于不相符的值为NULL。外连接也可以分为左外连接、右外连接与全外连接。
    • 左外连接（left join）：保留 左表的全部记录，和右表符合连接条件的记录。
    • 右外连接（right join）：保留右表的全部记录，和左表符合连接条件的行记录。
    • 全外连接：左外连接 union 右外连接，Mysql 中暂不支持。

1.内连接就是取交集的部分。
2.左连接就是左表全部的数据加上交集的数据。
3.右连接就是右表全部的数据加上交集的数据。
4.交叉连接就是全都要！

字段、键：

varchar 和 char 区别？

1. char长度不可变，varchar长度可变。
   • 比如：定义一个char[10]和varchar[10]，如果存进去‘abcd’，char所占长度依然是10，除字符’abcd’外，用6个空格填充。但varchar长度变成了4。去数据的时候，char类型要用trim()去掉多余的空格，二varchar类型不需要。
2. char的存取速度 比 varchar快得多。因为长度固定，方便程序的存储与查找；（但是付出了空间代价）
   • 所以如果确定某个字段的值的长度，可以用char，否则应尽量使用varchar。
     • 例如 存储md5加密后的密码应该使用char。

money 用什么类型修饰？

• 一般是使用Decimal和Numric来修饰，金钱这种需要很精确的数据。
  • 例如 money DECIMAL(5,2) 其中5(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。表示能被存储在money列中的值的范围是从-999.99 到999.99。
• Decimal和Numric值是作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度。一个字符用于值的每一位、小数点(如果scale>0)和“-”符号(对于负值)。如果scale是0‘DECIMAL 和NUMERIC值不包含小数点或小数部分。
• Note：计算机是二进制的，计算机在表示一个数字的时候宽度有限，无法准确地表示所有小数。而浮点数是由整数部分和小数部分组成的，也就意味着，计算机无法准确表示浮点数。

超键、候选键、主键、外键分别是？

1. 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。（表中的每行就是一个元组）
2. 候选键：是最小的超键，即没有冗余元素的超键。
   • 候选键是超键的子集，主键是候选键的子集
3. 主键：是指一个列或多列的组合，其值能唯一地标识表中的每一行。主键的取值不能为Null。（候选键里面找一个作为主键）
4. 外键：一个表中存在 另一个表的主键，就称这个属性集是外键。

• 例子：比如一个小范围的所有人，没有重名的，考虑以下属性
  • 身份证 姓名 性别 年龄
  • 1）超键：
    身份证唯一，所以是一个超键
    姓名唯一，所以是一个超键
    （姓名，性别）唯一，所以是一个超键
    （姓名，性别，年龄）唯一，所以是一个超键
    –>可以看出，超键的组合是唯一的，但可能不是最小唯一的
  • 2）候选键：
    身份证唯一，而且没有多余属性，所以是一个候选键
    姓名唯一，而且没有多余属性，所以是一个候选键
    –>可以看出，候选键是没有多余属性的超键
  • 3）主键：
    考虑输入查询方便性，可以选择 身份证 为主键
    也可以 考虑习惯 选择 姓名 为主键
    –>主键是选中的一个候选键

存储过程？优缺点？

1. 存储过程是一组为了完成特定功能的SQL语句集，编译一次后就存储在数据库中，再次调用时，不用再次编译，需要使用的时候直接调用，不需要再重复写SQL语句了。
2. 优点：
   • 效率高，编译一次后就存储在数据库，再次调用时，不用再编译直接执行。
   • 复用性高，往往是针对某一特定功能编写的，当需要再次完成此功能时，可以再次调用。
   • 安全性较高，完成某个特定功能的存储过程一般只有特定用户可以使用，具有使用身份限制，更安全。
3. 缺点：
   • 存储过程的管理不方便；
   • 复用性没有写在服务层好。

• 创建语法： begin、end用于表示sql语句的开始和结束。

create procedure 存储过程名(参数列表)
begin
//....存储过程体
end;

参数列表主要有三种形式：
	IN : 需要调用者传入
	OUT : 作为返回值
	INOUT : 调用者传入参数，对应的可以有返回值

• 例子：在score表里查找出指定成绩对应的学生学号

CREATE PROCEDURE score2(num int)
BEGIN
select 学号 from score
where 成绩=num;
end;

触发器作用、触发器的使用场景？

• 触发器是一种特殊的存储过程，一般存储过程通过存储过程名可以直接调用而执行，但触发器不需要用EXEC命令调用，而是在某个指定的事件执行时激活。当用户对表中的数据进行UPDATE、INSERT或DELETE操作时自动触发执行。

1. 触发器是一段代码，比如发生增删改操作时，会自动触发执行这段代码。
   • 比如：当数据库中的表发生增删改操作时，对应的触发器就会自动执行对应的PL/SQL语句块。
2. 作用：用于保证数据完整性，记录表的修改来审计表的相关信息。
   • DML触发器：当数据库服务器中发生数据操作语言事件时执行的存储过程，分为：After触发器和instead of触发器
   • DDL触发器：特殊的触发器，在响应数据定义语言（DDL）语句时触发，一般用于数据库中执行管理任务。
     • DDL触发器是响应create、after、或drop开头的语句而激活
       触发器用处还是很多的，比如校内网、开心网、Facebook，你发一个日志，自动通知好友，其实就是在增加日志时做一个后触发，再向通知表中写入条目。因为触发器效率高。
3. 使用场景：
   • 可以通过数据库中的相关表实现级联更改。
   • 实时监控某张表中的某个字段的更改而需要做出相应的处理。
   • 例如可以生成某些业务的编号。
   • 注意不要滥用，否则会造成数据库及应用程序的维护困难。

• 创建触发器：

CREATE TRIGGER 触发器名称
ON {表名 | 视图名}
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] 
  [ DELETE ] [ , ]
  [UPDATE ] }
AS 
  SQL 语句

2、事务？

• 事务（Transaction）是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。
• 事务是逻辑上的一组操作，操作要么全都执行，要么全部不执行。
• 事务具有四个基本特征，分别是：原子性、一致性、隔离性、持久性，简称ACID。+ 详细四大特性
  

1.1 事务的四大特性？🌟

1. 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）：执行事务前后，数据保持一致。例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
3. 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性（Durabilily）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

• Note：只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

为什么要有数据库事务？

事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

事务的提出主要是为了解决并发情况下保持数据一致性的问题。

并发事务带来了哪些问题?

多个事务并发运行，可能会操作同一批数据来完成各自的事务。

1. 丢失修改（Lost to modify）：事务A读取了某数据，事务B也读取了这个数据，事务A修改了这个数据后，事务B也对这个数据进行了修改。这时事务A的修改结果就会丢失。
2. 脏读（Dirty read）：事务B读取了事务A还没有提交的数据。（你都还没提交，我就读到了你刚操作的数据，万一你回滚了怎么办，你说这脏不脏。）
   （事务A正在访问数据并进行修改，还没有提交到数据库，这时候事务B也访问了这个数据，但是这个数据是还没有提交的数据。）
3. 不可重复读（Unrepeatable read）：通俗地讲，在一个事务范围内，多次查询某个数据，却得到不同的结果。
   • 事务A内多次读取某个数据，在事务没有完成之前，事务B也访问这个数据并修改，由于事务B的修改导致事务A中多次读取的数据不一样，称为不可重复读。
4. 幻读（Phantom read）：事务A读取了若干条数据，事务B插入或删除的方式修改了事务A的结果集，并提交。事务A就会发现多了或少了若干条数据，像发生了幻觉一样，称为幻读。

• 不可重复读和幻读区别：
  • 不可重复读：同样的条件下，读取过的数据，当我们再次读取时值发生了变化。
  • 幻读：同样的条件下，第1次和第2次读出来的记录数不一样。

事务隔离级别（SQL 标准）?🌟

1. 第一种隔离级别 READ-UNCOMMITTED(读未提交) ： 最低的隔离级别，允许读取尚未提交的数据。
   • 解决了丢失修改；但是可能会导致脏读、不可重复读和幻读。
   • 以操作同一行数据为前提，未提交的写事务，不允许其他写事务同时进行操作，仅允许读此行数据；此隔离级别可以通过“排他写锁”实现。
2. 第二种隔离级别Read committed(读已提交)： 允许读取并发事务已经提交的数据。未提交的写事务，不允许其他读事务和写事务，这样就避免了脏读。
   • 解决了修饰修改和脏读的问题；但是幻读或不可重复读仍有可能发生。
   • 读已提交会锁定当前正在读取的行的数据(写操作加写锁，读操作加读锁)；此隔离级别可以通过“瞬间共享读锁”和“排他写锁”实现。
3. 第三种隔离级别：Repeatable read(可重复读)：Mysql默认的隔离级别，同一个事务中，多次查询的某个数据结果是一样的，除非数据是被事务自己修改。
   • 解决了丢失修改、脏读和不可重复读；但幻读仍有可能发生。
   • 读事务会禁止其他写事务(允许读事务)；未提交的写事务 禁止其他读写事务。可重复读会锁定读取到的所有行直到事务结束，其他事务的更新操作只能等到事务结束后才能进行；此隔离级别可以通过“共享读锁”和“排他写锁”实现。
4. 第四种隔离级别：Serializable(可串行化)：最高的隔离级别，完全服从ACID的级别。所有事务依次执行，不能并发执行，这样事务之间不会产生干扰。
   • 解决了丢失修改、脏读、不可重复读以及幻读问题。
   • 对表进行加锁，可以有效避免脏读、不可重复读、幻读问题，但是效率较低，通常会用其他并发级别加上相应的并发锁机制来取代它。
     

MySQL 的隔离级别是基于锁实现的吗？

• MySQL的隔离级别是通过 锁 和 MVCC机制共同实现的。
• Serializable可串行化隔离级别是通过锁来实现的；
• 除 Serializable可串行化，其他隔离级别都是通过MVCC机制实现的。
  • 不过，SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

隔离级别与锁的关系？

1. 在 READ-UNCOMMITTED(读未提交)级别下 ： 读取数据不需要加共享锁，这样就不会和被修改的数据上的排他锁冲突。
2. 第二种隔离级别Read committed(读已提交)： 读操作需要加共享锁，但是在语句执行完以后释放共享锁。
3. 第三种隔离级别：Repeatable read(可重复读)：读操作需要加共享锁，但是在事务提交之前不释放共享锁，必须等待事务执行完毕之后释放共享锁。
4. 第四种隔离级别：Serializable(可串行化)：最高的隔离级别，这个级别锁定整个范围的键，并一直持有锁，直到事务完成。

MySQL 的默认隔离级别是什么?

事务的实现原理？

• 事务是基于**重做日志文件(redo log)和回滚日志(undo log)**实现的。
  1. 每提交一个事务必须先将该事务的所有日志写入到 重做日志文件 进行持久化，数据库就可以通过重做日志来保证事务的原子性和持久性。
  2. 有修改事务时，会产生undo log，如果需要回滚，则根据undo log的反向语句进行逻辑操作，比如insert一条记录就delete一条记录。undo log主要实现数据库的一致性。

什么是当前读和快照读？当前读和快照读区别？

1. 当前读：读的是当前时刻已提交的数据，获取的是最新的数据。快照读：读的是快照生成时候的数据，获取的可能是老的数据。
2. 当前读是加了锁的，加的是一种悲观锁；快照读没有加锁。
   

什么是MVCC？

1. MVCC（Multi-Version Concurrency Control）是多版本并发控制。一般用于数据库中对数据库的并发访问。
2. MVCC机制的作用其实就是避免同一个数据在不同事务之间的竞争，提高系统的并发性能。
3. Mysql中的innoDB中就是使用这种方法来提高读写事务控制的、他大大提高了读写事务的并发性能。
   • 原因是：MVCC是一种不采用锁来控制事物的方式，是一种非堵塞、同时还可以解决脏读、不可重复读和幻读等事务隔离问题，但不能解决更新丢失问题。

• 小结一下
  总之MVCC就是一些大牛、不满意读写使用锁去实现读写冲突问题、而提出的解决方案，一般在数据库中会使用MVCC加锁的方式解决读写、和写写冲突的问题。

MVCC的实现原理？

MVCC的实现原理是依靠记录中的3个隐含字段、undo log日志、Read View来实现的。

1. 隐含字段：
   • 1）ROW ID：自增ID，如果没有设置主键，自动生成一个聚簇索引。大小为6bt。
   • 2）事务ID：记录最后一次修改该记录的事务ID。
   • 3）回滚指针：指向这条记录的上一个版本。
2. undo log日志 分为两种：
   • 1）insert undo log：事务进行插入操作时产生、在事务回滚时需要，提交事务后可以被立即丢弃。
   • 2）update undo log：进行update、delete时产生的undo log，在回滚事务、快照读时需要。所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除。
3. Read View(读视图)：
   • Read View可以理解为数据库中某一个时刻所有未提交事务的快照。当创建ReadView时，可以知道这个时间点上未提交事务的所有信息。

3、锁

什么是锁？

• 锁是数据库系统区别文件系统的一个关键特性；

为什么要加锁？锁的作用？

• 当多个用户 并发存取数据时，会出现多个事务同时存取同一数据的情况。如果对并发操作不加控制，就会导致存取不正确的数据，破坏数据库的一致性。
• 锁机制用于管理对共享资源的并发访问，保证数据的完整性和一致性。

乐观锁、悲观锁？怎么实现？

思路：乐观锁、悲观锁定义 + 实现方式
数据库管理系统(DBMS)中的并发控制的任务是 确保多个事务同时存取数据库中的同一数据时，不破坏事务的隔离性、统一性和数据库的统一性。乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段。

• 乐观锁：

  • 乐观锁总是假设最好的情况，每次去拿数据时认为别人不会同时修改数据，因此乐观锁不会上锁。但是在更新时会判断在此期间，别人是否修改了数据，如果别人修改了数据则放弃操作，否则执行操作。（但是在修改数据时会把事务锁起来，通过version的方式进行锁定）
  • 实现方式：可以使用版本号机制或CSA算法实现。

• 悲观锁：

  • 悲观锁总是假设最坏的情况，每次去拿数据时都认为别人会修改数据，所以每次拿数据时都会上锁，直到提交事务。
  • 实现方式：实现方式是加锁。

• 加分回答：

  • 两种锁的使用场景
    • 乐观锁： 适用于写较少的情况下(冲突很少发生的情况)，可以节省锁开销，加大了整个系统的吞吐量。
    • 悲观锁： 适用于写较多的情况，可以减少并发。

按照锁的粒度分数据库锁有哪些？

在关系型数据库中，可以按照锁的粒度把数据库锁分为：行级锁、表级锁和页级锁。
+详见下一题。

行级锁和表级锁区别？

1. 行级锁：
   • MySQL 中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。
   • 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高。
   • 加锁的开销也最大，加锁慢，会出现死锁。
2. 表级锁：
   • MySQL 中锁定粒度最大的一种锁，对当前操作的整张表加锁。
   • 实现简单，加锁开销较少，加锁快，不会出现死锁。
   • 其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。

MyISAM和InnoDB存储引擎使用的锁？

1. MyISAM仅支持表级锁，一锁就锁住整张表。在并发写的情况下性能很差。
2. InnoDB不仅支持表级锁，也支持行级锁，默认行级锁。

从锁的类别上分 MySQL有哪些锁？

从锁的类别上分有 共享锁 和 排他锁。
+详见下面

共享锁和排他锁？

行级锁 和 表级锁都存在 共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）。

1. 共享锁（S锁）：又称为读锁。事务T对数据A加上S共享锁，则事务T可以读数据A但不能修改，其他事务只能再对数据A加S共享锁，不能加X排他锁，直到事务T释放A上的锁为止。
2. 排他锁（X锁）：又称为写锁。事务T对数据A加上X排他锁，则只允许事务T读取和修改数据A，其他事务不能再对数据A加任何类型的锁，直到事务T释放A上的排他锁为止。
   

意向锁作用？

前言：在存在行锁和表锁的情况下，一个事务想对某个表加X锁时，需要先检查是否有其他事务对这个表加了锁或对这个表的某一行加了锁，对表的每一行都进行检测一次这是非常低效率的，为了解决这种问题，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁。

书上：意向锁的含义是：如果对一个节点加意向锁，说明该节点的下层节点正在被加锁。（对任一节点加锁时，必须先对它的上层节点加意向锁）

• 意向锁是表级锁，共有两种：
  1. 意向共享锁（IS锁）：事务有意向对表中某些行加共享锁S锁。
  2. 意向排他锁（IX锁）：事务有意向对某些行加排他锁X锁。
• 意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

InnoDB 有哪几类行锁？

• MySQL InnoDB 支持三种行锁定方式：
  1. 记录锁（Record Lock） ：也被称为记录锁，属于单个行记录上的锁。
  2. 间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。
  3. 临键锁（Next-key Lock） ：Record Lock+Gap Lock，锁定一个范围，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

InnoDB 引擎的行锁是怎么实现的？

INnoDB的行锁是通过给索引上的索引项加锁实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录进行加锁。

什么是死锁？怎么解决？

1. 两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而出现恶性循环的现象，事务永远不能结束，形成死锁。
2. 预防死锁的方法：
   • 1）一次封锁法：事务一次将所有要使用的数据 全部加锁。（降低了并发度）
   • 2）顺序封锁法：尽量规定一个封锁顺序，所有事务按照顺序封锁。
   • 3）第三种方法：对于非常容易产生死锁的业务部分，可以尝试升级锁定粒度，通过表级锁定来减少死锁产生的概率。

如果业务处理不好可以用分布式事务锁或者使用乐观锁

用SQL模拟一个死锁？

链接: link

4、索引

什么是索引？有什么作用？

索引是一种用于快速检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。
索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

索引的优缺点？

• 优点 ：
  • 1）使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
  • 2）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 缺点 ：
  • 1）创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 2）索引需要使用物理文件存储，也会耗费一定空间。
• 但是，使用索引一定能提高查询性能吗?
  • 大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

索引的底层数据结构

• 常见的MySQL主要有两种结构：Hash索引和B+ 树索引，我们使用的是InnoDB引擎，默认的是B+树。

1）Hash索引❓

• Hash索引底层是哈希表，可以通过键(key)快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O(1)）。
• 为何能够通过 key 快速取出 value呢？
  • 原因在于 哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。

hash = hashfunc(key)
index = hash % array_size

哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描

• 优缺点：
  • 优点：可以通过键(key)快速取出对应的值(value)，所以等值查询时很快。
  • 缺点：
    • 存在hash冲突问题：不同的key可能得到的index相同。出现hash冲突时必须遍历链表中所有行，逐行比较，找符合条件的行。
    • hash索引不支持区间查询：hash索引虽然可以快速定位，但是没有顺序，对于区间查询，需要全表扫描。

hash冲突问题？

• 哈希算法存在 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。
• 比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

2）B+树

InnoDB存储引擎默认索引是：B+树索引

1. B+树：是多路平衡查找树；
2. 叶子结点存放key和data，其他内节点只存放key；叶子节点有 链指针 指向相邻的叶子结点（叶子结点间有指针相互连接）。
   • 因为所有数据都在叶子结点，任何查找都是从根节点到叶子节点，每个数据的查询效率基本相同，所以查询时间复杂度固定为 O(log n)。

• 在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+树作为索引结构 ，但是，两者的实现方式不太一样。
• B+树简化图：
  
• B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。
• • 目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+树 作为索引结构。
  • 在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+树作为索引结构 ，但是，两者的实现方式不太一样。

平衡二叉树

是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

B树

• B 树也称 B-树,全称为 多路平衡查找树。
• 每个节点都存储key和data，所有节点组成这棵树，并且叶子结点指针为null。
  B-树简化图：
  

B树和B+树异同？

1. B 树的所有节点既存放键(key) 也存放 数据(data)； B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
2. B 树的叶子节点都是独立的：B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
3. B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了； B+树查询效率稳定，因为B+树内节点不存储数据，它所有数据都在叶结点，任何查找都是从根节点到叶子节点，每个数据的查询效率基本相同，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。）

B+树优点 缺点？为什么索引用B+树？🌟

1. 优点：
   • 查询效率稳定，因为B+树内节点不存储数据，它所有数据都在叶结点，任何查找都是从根节点到叶子节点，每个数据的查询效率基本相同，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。
   • B+树支持范围查询，因为B+树的叶子结点有 链指针 指向相邻的叶子结点，顺序遍历即可。
   • B+树IO次数更少，因为它的非叶结点不存放数据，所以能存放更多目录项记录，结构更加矮胖，可以很快定位到数据页。
     • B+树 叶子结点存放key和data，其他内节点只存放key；叶子节点有 链指针 指向相邻的叶子结点（叶子结点间有指针相互连接）。
2. 缺点：
   • 1）平衡树增删效率不高，
   • 2）存储比较大，要构造节点，存储一些结点与结点之间额外的信息。
     ( 1）、2）都是树的通病)

为什么默认B+树索引，不是Hash索引？与Hash索引比较有优缺点？

1. Hash索引底层是哈希表，是以key-value存储数据的结构，数据之间没有顺序关系，对于区间查询，无法之间通过索引查询，需要全表扫描。所以，hash索引只适合等值查询的场景。
2. B+树是多路平衡查找树，叶子结点存放key和data，并且叶子节点有 链指针 指向相邻的叶子结点，区间查询时不需要做全表扫描。所以通过将B+树用于数据库索引。

B+ 树索引和Hash索引区别？

1. hash索引适合等值查询，等值查询时效率很高，但是无法进行范围查询 ；
2. hash索引存在hash碰撞问题。
3. 查询效率稳定，因为B+树内节点不存储数据，它所有数据都在叶结点，任何查找都是从根节点到叶子节点，每个数据的查询效率基本相同，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。
4. B+树支持范围查询，因为B+树的叶子结点有 链指针 指向相邻的叶子结点，顺序遍历即可。

聚集索引、非聚集索引

链接: mysql索引面试题

什么是聚集索引、非聚集索引？

链接: 什么是聚集索引、非聚集索引？

• 聚集索引：是索引结构和数据一起存放的索引。主键索引属于聚集索引。
  

在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

聚集索引的优缺点：

• 优点：
  • 聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。
• 缺点：
  • 依赖于有序的数据 ：因为 B+树是多路平衡查找树，如果索引的数据是无序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  • 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚集索引：

• 非聚集索引：是索引结构和数据分开存放的索引。

非聚集索引的优缺点：

• 优点：
  • 更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的。
• 缺点：
  • 依赖于有序的数据。
  • 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

聚集索引和非聚集索引区别？

1. 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。
2. 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。

mysql索引类型：

主键索引(Primary Key)

数据表的主键列使用的就是主键索引。

• 主键索引：是一种特殊的唯一索引，索引列的值必须唯一，且不允许为空，并且一个表最多只能有一个主键。
• 主键索引属于聚集索引。聚集索引：是索引结构和数据一起存放的索引。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

主键索引的作用？

1. 保证实体的完整性;
2. 加快数据的检索速度
3. 添加新记录时，数据库会自动检查新记录的主键值，不允许该值与表中其他记录的主键值重复。
4. 数据库会自动按主键值的顺序来显示记录。如果没有设定主键，就按输入的顺序来显示记录。

二级索引(辅助索引)

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

1. 唯一索引(Unique Key) ：唯一索引也是一种约束。索引列的值必须唯一，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2. 复合索引：一个索引可以包含多个列，多个列共同构成一个复合索引。
3. 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。（Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引）。
4. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
5. 前缀索引(Prefix) ：选择索引列的最左n个字符来建立索引。这样就大大节约了索引空间，进而提高索引效率。缺点就是：MySQL无法使用前缀索引做ORDER BY 、GROUP BY 和覆盖扫描。（前缀索引只适用于字符串类型的数据）

索引的优缺点？

• 优点 ：
  • 1）使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
  • 2）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 缺点 ：
  • 1）创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 2）索引需要使用物理文件存储，也会耗费一定空间。
• 但是，使用索引一定能提高查询性能吗?
  • 大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

索引越多越好吗?

• 不，索引要尽可能少。
  • 比如我们向表中插入一个记录，实际上是先将记录插入到聚簇索引对应的B+树，再插入到每个二级索引对应的B+树，–> 索引越多B+树就越多，插入时要进行的IO操作就越多，会严重影响性能
    另一方面，MySQL优化器会耗费更多的时间计算不同索引方案成本

什么样的字段适合创建索引?

1. 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
2. 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
3. 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
4. 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
5. 被频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

什么情况下会索引失效?🌟

1. 以%开头的like语句；
2. 查询语句的条件类型与数据表字段的类型不匹配；
3. 使用联合索引时，不满足最左匹配原则。

索引SQL

建立索引SQL：

create [unique] [cluster] index <索引名> 
on <表名>(<列名>[<次序>][, <列名>[<次序>]] ...);

例如：Student 表按学号升序建唯一索引。

create unique index Stusno on Student(sno);

修改索引名 SQL：

alter index <旧索引名> rename to <新索引名>

删除索引SQL：

drop index <索引名>

5、日志

MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志 binlog（归档日志）和事务日志redo log（重做日志）和 undo log（回滚日志）。

事务的实现原理？

• 事务是基于**重做日志文件(redo log)和回滚日志(undo log)**实现的。
  1. 每提交一个事务必须先将该事务的所有日志写入到 重做日志文件 进行持久化，数据库就可以通过重做日志来保证事务的原子性和持久性。
  2. 有修改事务时，会产生undo log，如果需要回滚，则根据undo log的反向语句进行逻辑操作，比如insert一条记录就delete一条记录。undo log主要实现数据库的一致性。

redo log重做日志

1. redo log（重做日志）是InnoDB存储引擎独有的，记录在哪个数据页上做了哪些修改。
2. 保证事务的持久性。redo log是循环写的，后面的内容会覆盖以前的内容, 它用于保证事务的持久性。
   • 它让MySQL拥有了崩溃恢复能力。比如 ：MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。（需要binlog和redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。）
     
3. MySQL 中数据是以页为单位，查询一条记录，会从硬盘加载一页的数据出来(加载出来的数据叫数据页)，放到 Buffer Pool 缓冲池中。后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。
   更新表数据时：
   • 1）若 Buffer Pool 缓冲池中存在要更新的数据，则直接在 Buffer Pool 缓冲池中更新。
   • 2） redo log buffer重做日志缓存中记录在哪个数据页上做了哪些修改。
   • 3）再将记录写到redo log file中（刷盘）。
   • 这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。
     
4. 刷盘时机（刷新到磁盘中）：
   InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：
   • 0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作；
   • 1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）；
   • 2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache；

binlog（归档日志）

1. 不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志，记录SQL语句。
2. 可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。
   

两阶段提交

1. 在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以它们的写入时机不一样。可能会出现逻辑不一致问题。
2. 为了解决两份日志之间的逻辑不一致问题，InnoDB存储引擎使用两阶段提交方案。
   • 原理：是将redo log的写入拆成了两个prepare阶段和commit阶段。
   • 写入binlog时发生异常不会有影响：因为MySQL根据redo log日志恢复数据时，如果发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。（如果能找到对应的binlog日志，MySQL会认为是完整的，就会提交事务恢复数据。）
     

若redo log与binlog两份日志之间的逻辑不一致，会出现什么问题？

• 以update语句为例，假设id=2的记录，字段c值是0，把字段c值更新成1，SQL语句为update T set c=1 where id=2。
• 假设写完redo log日志后，binlog日志没写完就发生了异常。
• 因此，之后用binlog日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为redo log日志恢复，这一行c值是1，最终数据不一致。
  

undo log （回滚日志）

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

小结

1. MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
2. MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

6、优化

MySQL 性能优化？

链接: MySQL高性能优化规范建议总结

8、题

1. 编写一个SQL查询语句，查找Docs表中所有重复的文件名的数据记录，只保留一个id最大的唯一文件。
   

select * from Doc as a 
where not exists
( select 1 from Doc as b where b.filename=a.filename and b.id>a.id)