基础知识

数据库三大范式是什么

第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
在设计数据库表结构的时候，要尽量遵守三大范式，如果不遵守，必须有足够的理由。比如性能，事实上我们经常会为了性能而妥协数据库的设计。

MySQL 支持哪些存储引擎?

MySQL 支持多种存储引擎,比如 InnoDB,MyISAM,Memory,Archive 等等.在大多数的情况下,直接选择使用 InnoDB 引擎都是最合适的,InnoDB 也是 MySQL 的默认存储引擎。
MyISAM 和 InnoDB 的区别有哪些：

  • InnoDB 支持事务，MyISAM 不支持
  • InnoDB 支持外键，而 MyISAM 不支持
  • MyISAM 采用表级锁；InnoDB 支持行级锁和表级锁,默认为行级锁。
  • InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高；MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
  • Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高；

  • InnoDB 不保存表的具体行数，MyISAM 用一个变量保存了整个表的行数。

    MySQL 中的 varchar 和 char 有什么区别？

  • char是定长的，根据定义字符串长度分配⾜够的空间，如果插⼊的数据长度⼩于定长，⽤空格填充。（因为长度固定，会占据⼀些多余的空间，空间换时间）

  • varchar是可变长字符串，长度可变。存取速度不如char快，由于长度不固定，不占据多余空间，时间换空间。

    SQL语言分类

    DDL(Data Define Language) 数据定义语言
    DML（Data Manipulation Language）数据操作语言
    DQL(Data Query Language) 数据查询语言
    DCL(Data Control Language)数据控制语言

    SQL的几种连接查询方式（内连接、外连接、全连接、联合查询）

    内连接：典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。
    内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。
    外连接：外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。外连接分三类：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）
    三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。不同点如下：
    左外连接：还返回左表中不符合连接条件单符合查询条件的数据行。
    右外连接：还返回右表中不符合连接条件单符合查询条件的数据行。
    全外连接：还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外 = 左外 UNION 右外”。
    全连接（full join）：两个表的所有数据都展示出来
    联合(合并)查询（union）：MySQL 不识别 FULL join，所以可以通过 union 来实现

    索引

    什么是索引？

    索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

    索引有哪些优缺点？

    索引的优点

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

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

索引的缺点

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

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

    索引的底层数据结构

    为什么MySQL 没有使用其他作为索引的数据结构呢？

    Hash:
    1.Hash 冲突问题 ：我们上面也提到过Hash 冲突了，不过对于数据库来说这还不算最大的缺点。
    2.Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。
    B-Tree:

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

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

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

B 树& B+树

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。
B 树& B+树两者有何异同呢？

  • B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
  • B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

    MyISAM 引擎和 InnoDB 引擎使用B+树的方式

    在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。
    MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
    InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。
    InnoDB支持外键，事务，行级锁，而MyISAM 不支持。

    索引类型

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

    数据表的主键列使用的就是主键索引。
    一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
    在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
    二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
    唯一索引，普通索引，前缀索引等索引属于二级索引。
  1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
  2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
  3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

  4. 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

    聚集索引与非聚集索引

    聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。
    非聚集索引即索引结构和数据分开存放的索引。二级索引属于非聚集索引。
    非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。
    聚集索引的优点
    聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。
    聚集索引的缺点

  5. 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。

  6. 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚集索引的优点
更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的
非聚集索引的缺点

  1. 跟聚集索引一样，非聚集索引也依赖于有序的数据
  2. 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

image.png

覆盖索引

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

最左前缀匹配原则

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

索引下推

索引下推是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

  • 有了索引下推优化，可以在减少回表次数
  • 在InnoDB中只针对二级索引有效

官方文档中给的例子和解释如下：
在 people_table中有一个二级索引(zipcode，lastname，address)，查询是SELECT * FROM people WHERE 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掉。

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

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

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

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

    创建索引的原则？索引设计的原则？

    1.⼀张表⼀般都要去建主键，所以主键索引⼏乎是每张表必备的，这个就不多说了。
    2.选择性⾼的列，也就是重复度低的列。⽐如⼥⼦学校学⽣表中的性别列，所有数据的值都是⼥，这样的列就不适合建索引。⽐如学⽣表中的⾝份证号列，选择性就很⾼，就适合建索引。
    3.经常⽤于查询的列（出现在where条件中的列）。不过如果不符合上⼀条的条件，即便是出现在where条件中也不适合建索引，甚⾄就不应该出现在where条件中。
    4.多表关联查询时作为关联条件的列。⽐如学⽣表中有班级ID的列⽤于和班级表关联查询时作为关联条件，这个列就适合建索引。
    5.值会频繁变化的列不适合建索引。因为在数据发⽣变化时是需要针对索引做⼀些处理的，所以如果不是有⾮常必要的原因，不要值会频繁变化的列上建索引，会影响数据更新的性能。反过来也就是说索引要建在值⽐较固定不变的列上。
    6.⼀张表上不要建太多的索引。和上⼀条的原因类似，如果⼀张表上的索引太多，会严重影响数据增删改的性能。也会耗费很⼤的磁盘空间。

    事务

    什么是数据库事务？

    事务是逻辑上的一组操作，要么都执行，要么都不执行。

    事务的四大特性(ACID)介绍一下

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

    什么是脏读？不可重复读？幻读？

    MySQL - 图2
  • 脏读(Dirty Read)：一个事务读取到另外一个事务未提交的数据。举例：一个事务1读取了被另一个事务2修改但还未提交的数据。由于某种异常事务2回滚，则事务1读取的是无效数据。


MySQL - 图3

  • 不可重复读(Non-repeatable read)：一个事务读取同一条记录2次，得到的结果不一致。这可能是两次查询过程中间，另一个事务更新了这条记录。

MySQL - 图4

  • 幻读(Phantom Read)：幻读发生在两个完全相同的查询，得到的结果不一致。这可能是两次查询过程中间，另一个事务增加或者减少了行记录。

不可重复度和幻读区别
不可重复读的重点是修改，幻读的重点在于新增或者删除。

SQL 标准定义了哪些事务隔离级别?

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

  • READ-UNCOMMITTED(读取未提交) ： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交) ： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读) ： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化) ： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | | —- | —- | —- | —- | | READ-UNCOMMITTED | √ | √ | √ | | READ-COMMITTED | × | √ | √ | | REPEATABLE-READ | × | × | √ | | SERIALIZABLE | × | × | × |


这里需要注意的是：MySQL 默认采用的 REPEATABLE_READ隔离级别，Oracle 默认采用的 READ_COMMITTED隔离级别

事务的实现原理

原⼦性、⼀致性和持久性是通过数据库的redo log和undo log来完成。redo log称为重做⽇志，⽤来保证事务的原⼦性和持久性。undo log⽤来保证事务的⼀致性。⽽隔离性是通过锁实现的。

三种日志的区别和作用？

redo log：恢复提交事务修改的页操作；通常是物理日志，记录的是页的物理修改操作。
uodo log：回滚记录到某个特定版本；通常是逻辑日志，根据每行记录进行记录。
bin log：⽤来进⾏Point-In-Time(PIT)的恢复及主从复制环境的建⽴。
这⾥⾯试官可能会接着问binlog和redolog的区别？
参考答案：
（1）重做⽇志是在InnoDB存储引擎层产⽣的，⽽⼆进制⽇志是在MySQL数据库上层产⽣的，⼆进制⽇志不仅仅针对InnoDB存储引擎，任何存储引擎都会产⽣⼆进制⽇志。
（2）两种⽇志的记录内容形式不同。⼆进制⽇志是⼀种逻辑⽇志，记录的是SQL语句；⽽InnoDB存储引擎层⾯的重做⽇志是物理格式⽇志，记录的是对于每个页的修改。
（3）写⼊磁盘的时间不同，⼆进制⽇志只在事务提交完成后进⾏⼀次写⼊，⽽redo log在事务进⾏中不断的写⼊。

什么是一致性非锁定读(MVCC)

MVCC实现原理【MVCC多版本并发控制，指的是⼀种提⾼并发的技术。】最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引⼊多版本之后，只有写写之间相互阻塞，其他三种操作都可以并⾏，这样⼤幅度提⾼了InnoDB的并发度。
核心概念：
1.Read view⼀致性视图【 主要是⽤来做可见性判断的, ⽐较普遍的解释便是”本事务不可见的当前其他活跃事务”】
2.read view快照的⽣成时机, 也⾮常关键, 正是因为⽣成时机的不同, 造成了RC,RR两种隔离级别的不同可见性;

在innodb中(默认repeatable read级别), 事务在begin/start transaction之后的第⼀条select读操作后, 会创建⼀个快照(read view), 将当前系统中活跃的其他事务记录记录起来; 在innodb中(默认repeatable committed级别), 事务中每条select语句都会创建⼀个快照(read view);

3.undo-log 【回滚⽇志，通过undo读取之前的版本信息，以此实现非锁定读取！】 是MVCC的重要组成部分！

Undo记录中存储的是⽼版本数据，当⼀个旧的事务需要读取数据时，为了能读取到⽼版本的数据，需要顺着undo链找到满⾜其可见性的记录。 另外, 在回滚段中的undo logs分为: insert undo log和update undo log insert undo log : 事务对insert新记录时产⽣的undolog, 只在事务回滚时需要, 并且在事务提交后就可以⽴即丢弃。 update undo log: 事务对记录进⾏delete和update操作时产⽣的undo log, 不仅在事务回滚时需要,⼀致性读也需要，所以不能随便删除，只有当数据库所使⽤的快照中不涉及该⽇志记录，对应的回滚⽇志才会被purge线程删除。

4.InnoDB存储引擎在数据库每⾏数据的后⾯添加了三个字段

  • DB_ROW_ID：隐藏的自增 ID，如果表没有主键，InnoDB 会自动按 ROW ID 产生一个聚集索引树。
  • 事务 ID：记录最后一次修改该记录的事务 ID。
  • 回滚指针：指向这条记录的上一个版本。

5.可见性⽐较算法（这⾥每个⽐较算法后⾯的描述是建⽴在rr级别下，rc级别也是使⽤该⽐较算法,此处未做描述）
MVCC 最大的好处是读不加锁，读写不冲突，极大地增加了 MySQL 的并发性。通过 MVCC，保证了事务 ACID 中的 I（隔离性）特性。

锁

对MySQL的锁了解吗

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

表级锁和行级锁了解吗？有什么区别？

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。
InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。
页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

共享锁和排他锁呢？

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

  • 共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

  • 排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

    意向锁有什么作用？

    如果需要用到表锁的话，如何判断表中的记录没有行锁呢？一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。
    意向锁属于表级别的锁，又可以分为意向共享锁(IS Lock)和意向排他锁(IX Lock)。

  • 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。

  • 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

    ⼀致性(非)锁定读

  • ⼀致性非锁定读：指InnoDB存储引擎通过MVCC的⽅式来读取当前执⾏时间数据库中⾏的数据。如果读取的⾏正在执⾏DELETE或UPDATE操作，这时读取操作不会因此等待行上锁的释放，相反的，InnoDB存储引擎会读取⼀个快照数据。

  • ⼀致性锁定读：InnoDB存储引擎对于SELECT语句⽀持两种⼀致性锁定读的操作：select … for update和select … lock in share mode。

    什么是死锁？怎么解决？

    死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。
    常见的解决死锁的方法
    1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
    2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
    3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
    如果业务处理不好可以用分布式事务锁或者使用乐观锁。

    InnoDB 有哪几类行锁？（了解）

    MySQL InnoDB 支持三种行锁定方式：

  • 记录锁（Record Lock） ：也被称为记录锁，属于单个行记录上的锁。

  • 间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。
  • 临键锁（Next-key Lock） ：Record Lock+Gap Lock，锁定一个范围，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

InnoDB 的默认隔离级别 REPEATABLE-READ（可重读）是可以解决幻读问题发生的，主要有下面两种情况：

  • 快照读 ：由 MVCC 机制来保证不出现幻读。

  • 当前读 ： 使用 Next-Key Lock 进行加锁来保证不出现幻读。

    SQL语句在MySQL中的执行过程

    MySQL 基本架构

  • 连接器： 身份认证和权限相关(登录 MySQL 的时候)。

  • 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
  • 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
  • 优化器： 按照 MySQL 认为最优的方案去执行。
  • 执行器： 执行语句，然后从存储引擎返回数据。

MySQL - 图5
简单来说 MySQL 主要分为 Server 层和存储引擎层：

  • Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。

  • 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始就被当做默认存储引擎了。

    一条查询语句的执行流程是怎样的呢？

    1. select * from tb_student  A where A.age='18' and A.name=' 张三 ';

  • 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 SQL 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

  • 通过分析器进行词法分析，提取 SQL 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id=’1’。然后判断这个 SQL 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
  • 接下来就是优化器进行确定执行方案，上面的 SQL 语句，可以有两种执行方案： a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。 b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。 那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

  • 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

    一条更新语句的执行流程又是怎样的呢？

  • 先查询到张三这一条数据，如果有缓存，也是会用到缓存。

  • 然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
  • 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
  • 更新完成。

这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗?
这是因为最开始 MySQL 并没有 InnoDB 引擎（InnoDB 引擎是其他公司以插件形式插入 MySQL 的），MySQL 自带的引擎是 MyISAM，但是我们知道 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。
并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做？

  • 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
  • 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binlog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binlog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

  • 判断 redo log 是否完整，如果判断是完整的，就立即提交。
  • 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

这样就解决了数据一致性的问题。

MySQL优化

百万级别以上的数据如何删除？

这⾥主要是考察索引相关的内容。由于索引的维护需要额外的存储空间存放索引⽂件，对数据库进⾏增删改时同时也会对索引⽂件产⽣对应的操作，这些操作需要进⾏IO操作，会影响效率。关于百万级别的数据库删除数据时，MySQL官⽅⼿册给出了下⾯的步骤：
（1）先删除索引（3min）
（2）删除无用数据（2min）
（3）重新创建索引，此时数据少，创建索引快（10min）

大表数据查询，怎么优化？

1、优化SQL语句+索引
2、使⽤缓存，如Redis
3、主从复制和读写分离
4、将表拆分（垂直或⽔平拆分），将大系统分为小系统。

分库分表是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？

1.垂直分表：
垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。如下图所示，这样来说大家应该就更容易理解了。
MySQL - 图6

  1. 适用场景：如果一个表中某些列常用，另外一些列不常用垂直拆分的优点：可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。对于应用层来说，逻辑算法增加开发成本。此外，垂直分区会让事务变得更加复杂；

  2. 水平分表：保持数据表结构不变，通过某种策略进行存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

MySQL - 图7
水平拆分可以支持非常大的数据量。需要注意的一点是：水平分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。
适用场景：支持非常大的数据量存储
水平拆分优点：支持非常大的数据量存储
水平拆分缺点：给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作；分片事务难以解决 ，跨库join性能较差，逻辑复杂。

MySQL的主从复制原理以及流程

主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行，从而使得从数据库的数据与主数据库保持一致。
主从复制的作用

  1. 高可用和故障切换：主数据库出现问题，可以切换到从数据库。
  2. 负载均衡：可以进行数据库层面的读写分离。
  3. 数据备份：可以在从数据库上进行日常备份。

复制过程
MySQL - 图8
Binary log：主数据库的二进制日志
Relay log：从数据库的中继日志
第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。
第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，将这些事件写入到中继日志中。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。
第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

MySQL主从同步延时问题如何解决？

MySQL 实际上在有两个同步机制，一个是半同步复制，用来 解决主库数据丢失问题；一个是并行复制，用来 解决主从同步延时问题。

  • 半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。
  • 并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

    读写分离有哪些解决方案？

    读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。主从复制要求slave不能写只能读
    方案一
    利用中间件来做代理，使用mysql-proxy代理，负责对数据库的请求识别出读还是写，并分发到不同的数据库中。
    优点：直接实现读写分离和负载均衡，不用修改代码，数据库和应用程序弱耦合，master和slave用一样的帐号，mysql官方不建议实际生产中使用
    缺点：降低性能， 不支持事务，代理存在性能瓶颈和可靠性风险增加。
    方案二
    使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源。
    如果采用了mybatis， 可以将读写分离放在ORM层，比如mybatis可以通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，所有的select 都访问salve库，这样对于dao层都是透明。plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。
    不过这样依然有一个问题， 也就是不支持事务， 所以我们还需要重写一下DataSourceTransactionManager， 将read-only的事务扔进读库， 其余的有读有写的扔进写库。
    方案三
    使用AbstractRoutingDataSource+aop+annotation在service层决定数据源，可以支持事务
    缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理。