MySQL面试笔记

1-数据库基础

1.1-数据库三范式

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

1.2-MySQL 数据库引擎有哪些

存储引擎 Storage engine：MySQL 中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

常用的存储引擎有以下：

• Innodb 引擎：Innodb 引擎提供了对数据库 ACID 事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。

• MyIASM 引擎(原本 Mysql 的默认引擎)：不提供事务的支持，也不支持行级锁和外键。

• MEMORY 引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

1.3-说说 Innodb 和 MyISAM 的区别

	MyISAM	Innodb
存储结构	每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	每张表被存放在两个文件：frm-表格定义、数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
哈希索引	不支持	支持
全文索引	支持	不支持
锁支持	表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
SELECT	MyISAM 更优
INSERT、UPDATE、DELETE		InnoDB 更优
select count(*)	myisam 更快，因为 myisam 内部维护了一个计数器，可以直接调取。
聚簇索引	MyISAM 索引是非聚簇索引	InnoDB 索引是聚簇索引，

1.4-MySQL 中一条 SQL 语句是如何执行的

比如下面这条 SQL 语句：

select name from t_user where id = 1

• 取得链接，使用使用到 MySQL 中的连接器。

• 查询缓存，key 为 SQL 语句，value 为查询结果，如果查到就直接返回。不建议使用次缓存，在 MySQL 8.0 版本已经将查询缓存删除，也就是说 MySQL 8.0 版本后不存在此功能。

• 分析器，分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错误在此阶段。

• 优化器，是在表里有多个索引的时候，决定使用哪个索引；或者一个语句中存在多表关联的时候（join），决定各个表的连接顺序。

• 执行器，通过分析器让 SQL 知道你要干啥，通过优化器知道该怎么做，于是开始执行语句。执行语句的时候还要判断是否具备此权限，没有权限就直接返回提示没有权限的错误；有权限则打开表，根据表的引擎定义，去使用这个引擎提供的接口，获取这个表的第一行，判断 id 是都等于 1。如果是，直接返回；如果不是继续调用引擎接口去下一行，重复相同的判断，直到取到这个表的最后一行，最后返回。

2-索引

2.1-什么是索引，索引的基本原理

• 索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。

• 索引的原理很简单，就是把无序的数据变成有序的查询

  • 把创建了索引的列的内容进行排序

  • 对排序结果生成倒排表

  • 在倒排表内容上拼上数据地址链

  • 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。而且索引是一个文件，它是要占据物理空间的。

MySQL 索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个 key 的全部值的信息了。

2.2-索引的优缺点

• 索引的优点

  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因；

  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能；

• 索引的缺点

  • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；

  • 空间方面：索引需要占物理空间；

2.3-MySQL 有哪几种索引类型

1 、从存储结构上来划分：B-Tree 索引（B-Tree 或 B+Tree 索引），Hash 索引，full-index 全文索引，R-

Tree 索引。这里所描述的是索引存储时保存的形式。

2 、从应用层次来分：普通索引，唯一索引，复合索引。

• 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引。

• 唯一索引：索引列的值必须唯一，但允许有空值。

• 复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。

• 聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB 的聚簇索引其实就是在同一个结构中保存了 B-Tree 索引(技术上来说是B+Tree)和数据行。

• 非聚簇索引： 不是聚簇索引，就是非聚簇索引。

2.4-说一说索引的底层实现

Hash 索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

B-Tree 索引（MySQL 使用 B+Tree）

B-Tree 能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

B+Tree 索引

是 B-Tree 的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比 B-Tree 来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下 B+Tree 效率更高。

B+tree 性质：

• n 棵子 tree 的节点包含n个关键字，不用来保存数据而是保存数据的索引。

• 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

• 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

• B+ 树中，数据对象的插入和删除仅在叶节点上进行。

• B+ 树有 2 个头指针，一个是树的根节点，一个是最小关键码的叶节点。

2.5-为什么索引结构默认使用 B+Tree，而不是 B-Tree，Hash,二叉树，红黑树？

B-tree： 从两个方面来回答

• B+ 树的磁盘读写代价更低：B+ 树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对 IO 读写次数就降低了；

• 由于 B+ 树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是 B 树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历顺序来扫，所以 B+ 树更加适合在区间查询的情况，所以通常 B+ 树用于数据库索引；

Hash：

• 虽然可以快速定位，但是没有顺序，IO复杂度高；

• 基于Hash 表实现，只有 Memory 存储引擎显式支持哈希索引 ；

• 适合等值查询，如=、in()、<=>，不支持范围查询 ；

• 因为不是按照索引值顺序存储的，就不能像 B+Tree 索引一样利用索引完成排序 ；

• Hash 索引在查询等值时非常快 ；

• 因为 Hash 索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找 ；

• 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题 ；

二叉树： 树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且 IO 代价高。

红黑树： 树的高度随着数据量增加而增加，IO 代价高。

2.6-讲一讲聚簇索引与非聚簇索引？

在 InnoDB 里，索引 B+ Tree 的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。而索引 B+ Tree 的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

聚簇索引与非聚簇索引的区别：

• 非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）；

• 对于 InnoDB 来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。第一次索引一般是顺序 IO，回表的操作属于随机 IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描 ；

• 通常情况下， 主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可；

• 注意：MyISAM 无论主键索引还是二级索引都是非聚簇索引，而 InnoDB 的主键索引是聚簇索引，二 级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引；

2.7-非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行 select score from student where score > 90 的查询时，在索引的叶子节点上，已经包含了score 信息，不会再次进行回表查询。

2.8-联合索引是什么？为什么需要注意联合索引中的顺序？

MySQL 可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

具体原因为:

MySQL 使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照 name 排序，如果 name 相同，则按照 age 排序，如果 age 的值也相等，则按照 school 进行排序。

当进行查询时，此时索引仅仅按照 name 严格有序，因此必须首先使用 name 字段进行等值查询，之后对于匹配到的列而言，其按照 age 字段严格有序，此时可以使用 age 字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

2.9-讲一讲 MySQL 的最左前缀原则?

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 andc > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。

2.10-讲一讲前缀索引？

因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果。但是，我们需要注意，order by不支持前缀索引 。

流程是：

先计算完整列的选择性 :select count(distinct col_1)/count(1) from table_1

再计算不同前缀长度的选择性 :select count(distinct left(col_1,4))/count(1) from table_1

找到最优长度之后，创建前缀索引 :create index idx_front on table_1 (col_1(4))

2.11-了解索引下推吗？

MySQL 5.6引入了索引下推优化。默认开启，使用 SET optimizer_switch =‘index_condition_pushdown=off’;可以将其关闭。

• 有了索引下推优化，可以在减少回表次数；

• 在InnoDB中只针对二级索引有效；

官方文档中给的例子和解释如下：

在 people_table中有一个二级索引(zipcode，lastname，address)，查询是 SELECT * FROM peopleWHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

• 如果没有使用索引下推技术，则 MySQL 会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到 MySQL服务端，然后 MySQL 服务端基于 lastname LIKE ‘%etrunia%’ and address LIKE‘%Main Street%’来判断数据是否符合条件；

• 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据 lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接 reject 掉；

2.12-怎么查看 MySQL 语句有没有用到索引？

通过 explain，如以下例子：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986- 06 - 26';

• id：在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的id ，如 explain select * from s1 where id = (select id from s1 where name = 'egon1');第一个 select 的 id 是 1 ，第二个 select 的 id 是2 。有时候会出现两个select，但是 id 却都是 1 ，这是因为优化器把子查询变成了连接查询 ；

• select_type：select 关键字对应的那个查询的类型，如 SIMPLE,PRIMARY,SUBQUERY,DEPENDENT,SNION ；

• table：每个查询对应的表名 ；

• type：type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是全表扫描 还是 索引扫描 等。如 const (主键索引或者唯一二级索引进行等值匹配的情况下),ref(普通的二级索引列与常量进行等值匹配),index(扫描全表索引的覆盖索引) 。通常来说, 不同的 type 类型的性能关系如下:ALL < index < range ~ index_merge < ref < eq_ref < const < system ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快；

• possible_key：查询中可能用到的索引 ( 可以把用不到的删掉，降低优化器的优化时间 ) ；

• key：此字段是 MySQL 在当前查询时所真正使用到的索引；

• filtered：查询器预测满足下一次查询条件的百分比 ；

• rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好；

• extra：表示额外信息，如Using where,Start temporary,End temporary,Using temporary等；

2.13- 为什么官方建议使用自增长主键作为索引？

结合 B+Tree 的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

2.14- 建索引的原则有哪些？

1 、最左前缀匹配原则，非常重要的原则，mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2 、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。

3 、尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1 ，而一些状态、性别字段可能在大数据面前区分度就是 0 ，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均 1 条扫描 10 条记录。

4 、索引列不能参与计算，保持列“干净”，比如 from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time =unix_timestamp(’2014-05-29’)。

5 、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

2.15-如何创建索引？

创建索引有三种方式。

1 、 在执行 CREATE TABLE 时创建索引

CREATE TABLE user_index2 (

id INT auto_increment PRIMARY KEY,

first_name VARCHAR ( 16 ),

last_name VARCHAR ( 16 ),

id_card VARCHAR ( 18 ),

information text,

KEY name (first_name, last_name),

FULLTEXT KEY (information),

UNIQUE KEY (id_card)

);

2 、 使用 ALTER TABLE 命令去增加索引。

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE 用来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引。

其中 table_name 是要增加索引的表名，column_list 指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名 index_name 可自己命名，缺省时，MySQL 将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

3 、 使用 CREATE INDEX 命令创建。

ALTER TABLE table_name ADD INDEX table_name (column_list);

2.16-创建索引时需要注意什么？

• 非空字段：应该指定列为NOT NULL，除非你想存储 NULL。在 mysql 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用 0 、一个特殊的值或者一个空串代替空值；

• 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过 count() 函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；

• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次 IO 操作获取的数据越大效率越高；

3-事务

3.1-什么是数据库事务及其 ACID 属性

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

关系型数据库需要遵循 ACID 规则，具体内容如下：

• 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

• 一致性: 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；

• 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

• 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

3.2-并发事务带来哪些问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）:当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

• 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

• 幻读（Phantom read）:幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别： 不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

3.3-事务隔离级别，MySQL 默认的级别是？

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL 默认采用的 REPEATABLE_READ 隔离级别 Oracle 默认采用的 READ_COMMITTED 隔离级别。

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是 MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容):，但是你要知道的是 InnoDB 存储引擎默认使用 REPEATABLE-READ （可重读）并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE( 可串行化 ) 隔离级别。

3.4-事务的实现原理

事务是基于重做日志文件(redo log)和回滚日志(undo log)实现的。

每提交一个事务必须先将该事务的所有日志写入到重做日志文件进行持久化，数据库就可以通过重做日志来保证事务的原子性和持久性。

每当有修改事务时，还会产生 undo log，如果需要回滚，则根据 undo log 的反向语句进行逻辑操作，比如 insert 一条记录就 delete 一条记录。undo log 主要实现数据库的一致性。

3.5- MySQL 事务日志介绍下？

innodb 事务日志包括 redo log 和 undo log。

undo log 指事务开始之前，在操作任何数据之前，首先将需操作的数据备份到一个地方。redo log 指事务中操作的任何数据，将最新的数据备份到一个地方。

事务日志的目的：实例或者介质失败，事务日志文件就能派上用场。

redo log

redo log 不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 redo 中。具体的落盘策略可以进行配置 。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 MySQL 服务的时候，根据 redo log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。RedoLog 是为了实现事务的持久性而出现的产物。

undo log

undo log 用来回滚行记录到某个版本。事务未提交之前，Undo 保存了未提交之前的版本数据，Undo中的数据可作为数据旧版本快照供其他并发事务进行快照读。是为了实现事务的原子性而出现的产物,在MySQL innodb 存储引擎中用来实现多版本并发控制。

3.6- 什么是 MySQL 的 binlog ？

MySQL的 binlog 是记录所有数据库表结构变更（例如 CREATE、ALTER TABLE）以及表数据修改（INSERT、UPDATE、DELETE）的二进制日志。binlog 不会记录 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看 MySQL 执行过的所有语句。

MySQL binlog 以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

binlog 有三种格式，各有优缺点：

• statement ： 基于 SQL 语句的模式，某些语句和函数如 UUID, LOAD DATA INFILE 等在复制过程可能导致数据不一致甚至出错；

• row ： 基于行的模式，记录的是行的变化，很安全。但是 binlog 会比其他两种模式大很多，在一些大表中清除大量数据时在 binlog 中会生成很多条语句，可能导致从库延迟变大；

• mixed ： 混合模式，根据语句来选用是 statement 还是 row 模式；

3.7-在事务中可以混合使用存储引擎吗？

尽量不要在同一个事务中使用多种存储引擎，MySQL 服务器层不管理事务，事务是由下层的存储引擎实现的。

如果在事务中混合使用了事务型和非事务型的表（例如 InnoDB 和 MyISAM 表）,在正常提交的情况下不会有什么问题。

但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。所以，为每张表选择合适的存储引擎非常重要。

3.8-MySQL 中是如何实现事务隔离的?

读未提交和串行化基本上是不需要考虑的隔离级别，前者不加锁限制，后者相当于单线程执行，效率太差。

MySQL 在可重复读级别解决了幻读问题，是通过行锁和间隙锁的组合 Next-Key 锁实现的。

详细原理看这篇文章：https://haicoder.net/note/MySQL-interview/MySQL-interview-MySQL-trans-level.html

3.9-大事务影响

• 并发情况下，数据库连接池容易被撑爆

• 锁定太多的数据，造成大量的阻塞和锁超时

• 执行时间长，容易造成主从延迟

• 回滚所需要的时间比较长

• undo log 膨胀

• 容易导致死锁

3.10-事务优化

• 将查询等数据准备操作放到事务外

• 事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久

• 事务中避免一次性处理太多数据，可以拆分成多个事务分次处理

• 更新等涉及加锁的操作尽可能放在事务靠后的位置

• 能异步处理的尽量异步处理

• 应用侧(业务代码)保证数据一致性，非事务执行

4-锁

4.1-为什么需要加锁？

当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

保证多用户环境下保证数据库完整性和一致性。

4.2-按照锁的粒度分析，数据库上的锁有哪些？

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁( INNODB 引擎)、表级锁( MYISAM 引擎)和页级锁( BDB 引擎 )。

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

• MyISAM 采用表级锁(table-level locking)。

• InnoDB 支持行级锁(row-level locking)和表级锁，默认为行级锁

行级锁，表级锁和页级锁对比

• 行级锁

  • 行级锁是 Mysql 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

  • 特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• 表级锁

  • 表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

  • 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

• 页级锁

  • 页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折中的页级，一次锁定相邻的一组记录。

  • 特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

4.3-从锁的类别上分，MySQL 数据库上的锁有哪些？

从锁的类别上来讲，有共享锁和排他锁。

• 共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

• 排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

用上面的例子来说就是用户的行为有两种，一种是来看房，多个用户一起看房是可以接受的。 一种是真正的入住一晚，在这期间，无论是想入住的还是想看房的都不可以。

锁的粒度取决于具体的存储引擎，InnoDB 实现了行级锁，页级锁，表级锁。他们的加锁开销从大到小，并发能力也是从大到小。

4.4-Innodb 存储引擎的行锁是怎么实现的？

答：InnoDB 是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么 InnoDB 将完成表锁，并发将无从谈起

4.5-Innodb存储引擎锁的算法

Record lock：单个行记录上的锁

Gap lock：间隙锁，锁定一个范围，不包括记录本身

Next-key lock：record+gap 锁定一个范围，包含记录本身

相关知识点：

• innodb 对于行的查询使用 next-key lock

• Next-locking keying 为了解决 Phantom Problem幻读问题

• 当查询的索引含有唯一属性时，将 next-key lock 降级为 record key

• Gap 锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生

• 有两种方式显式关闭 gap 锁：（除了外键约束和唯一性检查外，其余情况仅使用 record lock） A. 将事务隔离级别设置为RCB. 将参数 innodb_locks_unsafe_for_binlog 设置为1

4.6-什么是死锁，怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法

• 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

• 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

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

4.7-乐观锁和悲观锁是什么？怎么实现？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过 version 的方式来进行锁定。实现方式：乐观锁一般会使用版本号机制或 CAS 算法实现。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

7.7-隔离级别与锁的关系

在 Read Uncommitted 级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

在 Read Committed 级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在 Repeatable Read 级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

4.8-优化锁方面的意见？

• 使用较低的隔离级别

• 设计索引，尽量使用索引去访问数据，加锁更加精确，从而减少锁冲突

• 选择合理的事务大小，给记录显示加锁时，最好一次性请求足够级别的锁。列如，修改数据的话，最好申请排他锁，而不是先申请共享锁，修改时在申请排他锁，这样会导致死锁

• 不同的程序访问一组表的时候，应尽量约定一个相同的顺序访问各表，对于一个表而言，尽可能的固定顺序的获取表中的行。这样大大的减少死锁的机会。

• 尽量使用相等条件访问数据，这样可以避免间隙锁对并发插入的影响

• 不要申请超过实际需要的锁级别

• 数据查询的时候不是必要，不要使用加锁。MySQL 的 MVCC 可以实现事务中的查询不用加锁，优化事务性能：MVCC 只在 committed read（读提交）和 repeatable read （可重复读）两种隔离级别

• 对于特定的事务，可以使用表锁来提高处理速度活着减少死锁的可能。

5-MVCC

5.1-什么是MVCC？

MVCC， 即多版本并发控制。MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

多版本并发控制（MVCC=Multi-Version Concurrency Control），是一种用来解决读 - 写冲突的无锁并发控制。也就是为事务分配单向增长的时间戳，为每个修改保存一个版本。版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照（复制了一份数据）。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

5.2-MVCC 可以为数据库解决什么问题？

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决丢失修改问题。

5.3-说说 MVCC 的实现原理？

MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3 个隐式字段、undo 日志、Read View 来实现的。

undo 日志版本链与 read view 机制详解 ：undo 日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，MySQL 会保留修改前的数据undo回滚日 志，并且用两个隐藏字段 trx_id 和 roll_pointer 把这些 undo 日志串联起来形成一个历史记录版本链(见下图，需参考视频里的 例子理解)

在可重复读隔离级别，当事务开启，执行任何查询 sql 时会生成当前事务的一致性视图 read-view，该视图在事务结束之前永远都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成read-view)，这个视图由执行查询时所有未提交事务 id 数组(数组里最小的 id 为min_id)和已创建的最大事务id(max_id)组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟 read-view 做比对从而得到最终的快照结果。

版本链比对规则:

1. 如果 row 的 trx_id 落在绿色部分( trx_id < min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的;

2. 如果 row 的 trx_id 落在红色部分( trx_id > max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的);

3. 如果 row 的 trx_id 落在黄色部分(min_id <= trx_id <= max_id)，那就包括两种情况 a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的); b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

对于删除的情况可以认为是 update 的特殊情况，会将版本链上最新的数据复制一份，然后将 trx_id 修改成删除操作的 trx_id，同时在该条记录的头信息(record header)里的(deleted_flag)标记位写上 true，来表示当前记录已经被删除， 在查询时按照上面的规则查到对应的记录如果 delete_flag 标记位为 true，意味着记录已被删除，则不返回数据。

关于 readview 和可见性算法的原理解释

readview 和可见性算法其实就是记录了 sql 查询那个时刻数据库里提交和未提交所有事务的状态。要实现 RR 隔离级别，事务里每次执行查询操作 readview 都是使用第一次查询时生成的 readview，也就是都是以第一次查询时当时数据库里所有事务提交状态来比对数据是否可见，当然可以实现每次查询的可重复读的效果了。 要实现 RC 隔离级别，事务里每次执行查询操作 readview 都会按照数据库当前状态重新生成 readview，也就是每次查询都是跟数据库里当前所有事务提交状态来比对数据是否可见，当然实现的就是每次都能查到已提交的最新数据效果了。 注意:begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作或加排它锁操作(比如 select...for update)的语句，事务才真正启动，才会向 mysql 申请真正的事务 id，mysql 内部是严格按照事务的启动顺序来分配事务 id 的。 总结: MVCC 机制的实现就是通过 read-view 机制与 undo 版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

6-MySQL 篇补充

6.1-SQL 其他优化手段

6.2-简单说一下 drop，delete 和 truncate 的区别

SQL 中的 drop、delete、truncate 都表示删除，但是三者有一些差别,delete 和 truncate 只删除表的数据不删除表的结构

速度,一般来说: drop > truncate > delete

delete 语句是 dml,这个操作会放到 rollback segement 中,事务提交之后才生效; 如果有相应的 trigger,执行的时候将被触发. truncate,drop 是 ddl, 操作立即生效,原数据不放到 rollback segment中,不能回滚,操作不触发 trigger.

6.3-什么是视图？

6.4-什么是内连接，左外连接，右外连接？

6.5-大表如何优化？

6.6-查询操作方法需要使用事务吗？

6.7-B树和B+树的区别

6.8-索引覆盖，全局覆盖