MySQL

---

说一说三大范式

• 「第一范式」：数据库中的字段具有**「原子性」**，不可再分，并且是单一职责
• 「第二范式」：「建立在第一范式的基础上」，第二范式要求数据库表中的每个实例或行必须**「可以被惟一地区分」。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识**。这个惟一属性列被称为主键
• 「第三范式」：「建立在第一，第二范式的基础上」，非主属性既不传递依赖于码，也不部分依赖于码。确保每列都和主键列直接相关，而不是间接相关，不存在其他表的非主键信息

但是在我们的日常开发当中，「并不是所有的表一定要满足三大范式」，有时候冗余几个字段可以少关联几张表，带来的查询效率的提升有可能是质变的

---

事务的四大特性？

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

事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性：事务包含的所有操作要么全部成功，要么全部失败回滚。事务是最小的执行单位，不允许分割。
• 一致性：执行事务前后，数据保持一致。例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的。
• 隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。
• 持久性：一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

会出现哪些并发一致性问题？

• 丢失修改：一个事务对数据进行了修改，在事务提交之前，另一个事务对同一个数据进行了修改，覆盖了之前的修改；

• 脏读：在一个事务处理过程里读取了另一个未提交的事务中的数据。

• 不可重复读：如果一个事务能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值。【强调的是每次都能读到最新数据】

• 幻读：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行，就像产生幻觉一样，这就是发生了幻读。

事务的四种隔离级别？

• Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。 表锁
• Repeatable read (可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决不可重复读问题。行锁
• Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。 行锁
• Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。不加锁

---

什么是索引？

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。

索引的优缺点？

优点：

• 加快数据查找的速度
• 为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度
• 加快表与表之间连接的速度

缺点：

• 建立索引需要占用物理空间
• 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

索引的作用？

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。

什么情况下需要建索引？

1. 经常用于查询的字段
2. 经常用于连接的字段建立索引，可以加快连接的速度
3. 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

什么情况下不建索引？

1. where条件中用不到的字段不适合建立索引
2. 表记录较少
3. 需要经常增删改
4. 参与列计算的列不适合建索引
5. 区分度不高的字段不适合建立索引，如性别等

索引的数据结构

索引的数据结构主要有B+树和哈希表，对应的索引分别为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B+树索引。

B+树索引

B+ 树是基于B 树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的数据项。

MySQL 数据库使用最多的索引类型是BTREE索引，底层基于B+树数据结构来实现。

哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

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

• 哈希索引不支持排序，因为哈希表是无序的。
• 哈希索引不支持范围查找。
• 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
• 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

为什么采用 B+ 树,而不是 B-树

• B+树只有叶节点存放数据，其余节点用来存索引，而B-树是每个索引节点都会有Data域。B+树单一节点存储更多的数据，使得查询的IO次数更少。

• 所有查询都要查找到叶子节点，查询性能稳定。

• B+树所有数据都在叶子节点，所有叶子节点形成有序链表，便于范围查询

为什么推荐使用自增 id 作为主键？

• １.普通索引的 B+ 树上存放的是主键索引的值，如果该值较大，会**「导致普通索引的存储空间较大」**
• ２.使用自增 id 做主键索引新插入数据只要放在该页的最尾端就可以，直接**「按照顺序插入」**，不用刻意维护
• 3.页分裂容易维护，当插入数据的当前页快满时，会发生页分裂的现象，如果主键索引不为自增 id，那么数据就可能从页的中间插入，页的数据会频繁的变动，「导致页分裂维护成本较高」

索引有什么分类？

1、主键索引：名为primary的唯一非空索引，不允许有空值。

2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。

唯一索引和主键索引的区别是：唯一约束的列可以为null且可以存在多个null值。

唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。

3、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。

4、全文索引：只有在MyISAM引擎上才能使用，只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。

什么是最左匹配原则？

如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。

对(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。如果查询条件为a = 1 and b > 2 and c = 3，那么a、b个字两段能用到索引，而c无法使用索引，因为b字段是范围查询，导致后面的字段无法使用索引。在这个范围内的值不是有序的，因此无法使用索引。

---

聚集索引

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

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

• 优点

聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

• 缺点

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

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

非聚集索引

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

二级索引属于非聚集索引。

MYISAM 引擎的表的.MYI文件包含了表的索引， 该表的索引(B+树)的每个叶子非叶子节点存储索引， 叶子节点存储索引和索引对应数据的指针，指向.MYD文件的数据。

非聚集索引的叶子节点并不一定存放数据的指针， 因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

• 优点

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

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

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

• 聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。

• 一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。

• 使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。

---

什么是索引下推?

如果存在某些被索引的列的判断条件时，MySQL 将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，「只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器」。

什么是覆盖索引？

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

MySQL使用b+树索引做覆盖索引。

什么是前缀索引？

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。

---

索引的设计原则？

• 索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。
• 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。
• 索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。
• 利用最左前缀原则。

索引什么时候会失效？

• 最左前缀法则。对于组合索引，不是使用组合索引最左边的字段，则不会使用索引
• 以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引
• 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效
• 判断索引列是否不等于某个值时
• 对索引列进行运算
• 查询条件使用or连接，也会导致索引失效

---

常见的存储引擎有哪些？

MySQL中常用的四种存储引擎分别是：MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5版本后默认的存储引擎为InnoDB。

InnoDB存储引擎

InnoDB是MySQL默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。

• 优点：支持事务和崩溃修复能力；引入了行级锁和外键约束。

• 缺点：占用的数据空间相对较大。

• 适用场景：需要事务支持，并且有较高的并发读写频率。

MyISAM存储引擎

数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用MyISAM引擎。MyISAM会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。

• 优点：访问速度快。

• 缺点：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。

• 适用场景：对事务完整性没有要求；表的数据都会只读的。

MEMORY存储引擎

MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。

MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。

• 优点：访问速度较快。

• 缺点：

1. 哈希索引数据不是按照索引值顺序存储，无法用于排序。
2. 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
3. 只支持等值比较，不支持范围查询。
4. 当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

ARCHIVE存储引擎

ARCHIVE存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差。

MyISAM和InnoDB的区别？

1. 是否支持行级锁 : MyISAM 只有表级锁，而InnoDB 支持行级锁和表级锁，默认为行级锁。
2. 是否支持事务和崩溃后的安全恢复：MyISAM 不提供事务支持。而InnoDB提供事务支持，具有事务、回滚和崩溃修复能力。
3. 是否支持外键： MyISAM不支持，而InnoDB支持。
4. 是否支持MVCC ：MyISAM不支持，InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效。
5. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和索引绑在一起的，必须要有主键。**MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

---

MVCC 实现原理？

多版本控制(MVCC)：是现代数据库(包括 MySQL、Oracle、PostgreSQL 等)引擎实现中常用的处理读写冲突的手段，目的在于**「提高数据库高并发场景下的吞吐性能」**。

• 在 MVCC 协议下，每个读操作会看到一个一致性的快照，「这个快照是基于整个库的」，并且可以实现非阻塞的读，用于**「支持读提交和可重复读隔离级别的实现」**。

• MVCC 允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务 ID，在同一个时间点，不同的事务看到的数据是不同的，这个修改的数据是**「记录在 undo log 中」**的。

• 在内部实现中，InnoDB通过undo log保存每条数据的多个版本，并且能够找回数据历史版本提供给用户读，每个事务读到的数据版本可能是不一样的。在同一个事务中，用户只能看到该事务创建快照之前已经提交的修改和该事务本身做的修改。

MVCC(Multiversion concurrency control) 就是同一份数据保留多版本的一种方式，进而实现并发控制。在查询的时候，通过read view和版本链找到对应版本的数据。

作用：提升并发性能。对于高并发场景，MVCC比行级锁开销更小。

MVCC 实现原理如下：

MVCC 的实现依赖于版本链，版本链是通过表的三个隐藏字段实现。

• DB_TRX_ID：当前事务id，通过事务id的大小判断事务的时间顺序。
• DB_ROLL_PRT：回滚指针，指向当前行记录的上一个版本，通过这个指针将数据的多个版本连接在一起构成undo log版本链。
• DB_ROW_ID：主键，如果数据表没有主键，InnoDB会自动生成主键。

每条表记录大概是这样的：

使用事务更新行记录的时候，就会生成版本链，执行过程如下：

1. 用排他锁锁住该行；
2. 将该行原本的值拷贝到undo log，作为旧版本用于回滚；
3. 修改当前行的值，生成一个新版本，更新事务id，使回滚指针指向旧版本的记录，这样就形成一条版本链。

下面举个例子方便大家理解。

1、初始数据如下，其中DB_ROW_ID和DB_ROLL_PTR为空。

2、事务A对该行数据做了修改，将age修改为12，效果如下：

3、之后事务B也对该行记录做了修改，将age修改为8，效果如下：

4、此时undo log有两行记录，并且通过回滚指针连在一起。

接下来了解下read view的概念。

read view可以理解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻t的数据时，到t时间点拍的“照片”上取数据。

在read view内部维护一个活跃事务链表，表示生成read view的时候还在活跃的事务。这个链表包含在创建read view之前还未提交的事务，不包含创建read view之后提交的事务。

不同隔离级别创建read view的时机不同。

• read committed(读已提交)：每次执行select都会创建新的read_view，保证能读取到其他事务已经提交的修改。
• repeatable read(可重复读)：在一个事务范围内，第一次select时更新这个read_view，以后不会再更新，后续所有的select都是复用之前的read_view。这样可以保证事务范围内每次读取的内容都一样，即可重复读。

read view的记录筛选方式

前提：DATA_TRX_ID 表示每个数据行的最新的事务ID；up_limit_id表示当前快照中的最先开始的事务；low_limit_id表示当前快照中的最慢开始的事务，即最后一个事务。

• 如果DATA_TRX_ID < up_limit_id：说明在创建read view时，修改该数据行的事务已提交，该版本的记录可被当前事务读取到。

• 如果DATA_TRX_ID >= low_limit_id：说明当前版本的记录的事务是在创建read view之后生成的，该版本的数据行不可以被当前事务访问。此时需要通过版本链找到上一个版本，然后重新判断该版本的记录对当前事务的可见性。

• 如果up_limit_id <= DATA_TRX_ID < low_limit_i：

• 1. 需要在活跃事务链表中查找是否存在ID为DATA_TRX_ID的值的事务。
  2. 如果存在，因为在活跃事务链表中的事务是未提交的，所以该记录是不可见的。此时需要通过版本链找到上一个版本，然后重新判断该版本的可见性。
  3. 如果不存在，说明事务trx_id 已经提交了，这行记录是可见的。

总结：InnoDB 的MVCC是通过 read view 和版本链实现的，版本链保存有历史版本记录，通过read view 判断当前版本的数据是否可见，如果不可见，再从版本链中找到上一个版本，继续进行判断，直到找到一个可见的版本。

---

快照读和当前读

表记录有两种读取方式。

• 快照读：读取的是快照版本。普通的SELECT就是快照读。通过mvcc来进行并发控制的，不用加锁。
• 当前读：读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

快照读情况下，InnoDB通过mvcc机制避免了幻读现象。而mvcc机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。

那么MySQL是如何避免幻读？

• 在快照读情况下，MySQL通过mvcc来避免幻读。
• 在当前读情况下，MySQL通过next-key来避免幻读（加行锁和间隙锁来实现的）。

next-key包括两部分：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。

Serializable隔离级别也可以避免幻读，会锁住整张表，并发性极低，一般不会使用。

---

常见的封锁类型？

• 意向锁是 InnoDB 自动加的， 不需用户干预。 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB 会自动给涉及数据集加排他锁（X)； 对于普通 SELECT 语句，InnoDB 不会加任何锁；

• 事务可以通过以下语句显式给记录集加共享锁或排他锁：

  • 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并且也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
  • 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

• 排它锁（Exclusive Lock）/ X锁：事务对数据加上X锁时，只允许此事务读取和修改此数据，并且其它事务不能对该数据加任何锁；

• 共享锁（Shared Lock）/ S锁：加了S锁后，该事务只能对数据进行读取而不能修改，并且其它事务只能加S锁，不能加X锁

• 意向锁（Intention Locks）：

  • 一个事务在获得某个数据行对象的 S 锁之前，必须先获得整个表的 IS 锁（意向共享锁）或更强的锁；
  • 一个事务在获得某个数据行对象的 X 锁之前，必须先获得整个表的 IX 锁（意向排他锁）；
  • IS/IX 锁之间都是兼容的；
  • 好处：如果一个事务想要对整个表加X锁，就需要先检测是否有其它事务对该表或者该表中的某一行加了锁，这种检测非常耗时。有了意向锁之后，只需要检测整个表是否存在IX/IS/X/S锁就行了

锁的作用：用于管理对共享资源的并发访问，保证数据库的完整性和一致性

SELECT... FOR UPDATE 使用注意事项：

1. for update 仅适用于InnoDB，且必须在事务范围内才能生效。
2. 根据主键进行查询，查询条件为like或者不等于，主键字段产生表锁。
3. 根据非索引字段进行查询，会产生表锁。

什么是乐观锁和悲观锁？

• 悲观锁：认为数据随时会被修改，因此每次读取数据之前都会上锁，防止其它事务读取或修改数据；应用于数据更新比较频繁的场景；

• 乐观锁：操作数据时不会上锁，但是更新时会判断在此期间有没有别的事务更新这个数据，若被更新过，则失败重试；适用于

  读多写少的场景。乐观锁的实现方式有：

  • 加一个版本号或者时间戳字段，每次数据更新时同时更新这个字段；
  • 先读取想要更新的字段或者所有字段，更新的时候比较一下，只有字段没有变化才进行更新。

---

说说你的 Sql 调优思路

• 1.「表结构优化」

• • 1.1拆分字段
  • 1.2字段类型的选择
  • 1.3字段类型大小的限制
  • 1.4合理的增加冗余字段
  • 1.5新建字段一定要有默认值

• 2.「索引方面」

• • 2.1索引字段的选择
  • 2.2利用好mysql支持的索引下推，覆盖索引等功能
  • 2.3唯一索引和普通索引的选择

• 3.「查询语句方面」

• • 3.1避免索引失效
  • 3.2合理的书写where条件字段顺序
  • 3.3小表驱动大表
  • 3.4可以使用force index()防止优化器选错索引

• 4.「分库分表」

大表怎么优化？

某个表有近千万数据，查询比较慢，如何优化？

当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：

• 限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内；
• 读写分离：经典的数据库拆分方案，主库负责写，从库负责读；
• 通过分库分表的方式进行优化，主要有垂直拆分和水平拆分。

---

bin log/redo log/undo log

MySQL日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。其中比较重要的是 bin log（二进制日志）和 redo log（重做日志）和 undo log（回滚日志）。

bin log

bin log是MySQL数据库级别的文件，记录对MySQL数据库执行修改的所有操作，不会记录select和show语句，主要用于恢复数据库和同步数据库（主从复制）。

redo log

redo log是InnoDB引擎级别，用来记录InnoDB存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。当数据库发生故障，InnoDB存储引擎会使用redo log恢复到发生故障前的时刻，以此来保证数据的完整性。

undo log

除了记录redo log外，当进行数据修改时还会记录undo log，undo log用于数据的撤回操作，它保留了记录修改前的内容。通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC。

bin log和redo log有什么区别？

1. bin log会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；redo log只记录InnoDB自身的事务日志。
2. bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redo log不断写入磁盘。
3. bin log是逻辑日志，记录的是SQL语句的原始逻辑；redo log是物理日志，记录的是在某个数据页上做了什么修改。

---

讲一下MySQL架构？

MySQL主要分为 Server 层和存储引擎层：

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

Server 层基本组件

• 连接器： 当客户端连接 MySQL 时，server层会对其进行身份认证和权限校验。
• 查询缓存: 执行查询语句的时候，会先查询缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会直接返回给客户端，如果没有命中，就会执行后续的操作。
• 分析器: 没有命中缓存的话，SQL 语句就会经过分析器，主要分为两步，词法分析和语法分析，先看 SQL 语句要做什么，再检查 SQL 语句语法是否正确。
• 优化器： 优化器对查询进行优化，包括重写查询、决定表的读写顺序以及选择合适的索引等，生成执行计划。
• 执行器： 首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会根据执行计划去调用引擎的接口，返回结果。

---

一条查询语句是怎么执行的?

• 1.通过连接器跟客户端**「建立连接」**
• 2.通过查询**「缓存查询」**之前是否有查询过该 SQL
• • 有则直接返回结果
  • 没有则执行第三步
• 3.通过分析器**「分析该 SQL 的语义」**是否正确，包括格式，表等等
• 4.通过优化器**「优化该语句」**，比如选择索引，join 表的连接顺序
• 5.「验证权限」，验证是否有该表的查询权限
• • 没有则返回无权限的错误
  • 有则执行第六步
• 6.通过执行器调用存储引擎执行该 sql，然后返回**「执行结果」**

更新语句执行过程？

更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log（prepare状态）、binlog、redo log（commit状态）

举个例子，更新语句如下：

update user set name = '大彬' where id = 1;

1. 先查询到 id 为1的记录，有缓存会使用缓存。
2. 拿到查询结果，将 name 更新为大彬，然后调用引擎接口，写入更新数据，InnoDB 引擎将数据保存在内存中，同时记录redo log，此时redo log进入 prepare状态。然后告知执行器执行完成了，随时可以提交事务。
3. 执行器收到通知后记录binlog，写入磁盘，然后调用引擎接口，提交redo log为commit状态。
4. 更新完成。

InnoDB 事务为什么要两阶段提交?

为什么记录完redo log，不直接提交，而是先进入prepare状态？

---

• 先写 redolog 后写binlog。假设在 redolog 写完，binlog 还没有写完的时候，MySQL 进程异常重启，这时候 binlog 里面就没有记录这个语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 「binlog 丢失」，这个临时库就会少了这一次更新，恢复出来的数据，与原库不同。
• 先写 binlog 后写 redolog。如果在 binlog 写完之后 crash，由于 redolog 还没写，崩溃恢复以后这个事务无效。但是 binlog 里面已经记录了这个事务提交的日志。所以，在之后用 binlog 来恢复的时候就**「多了一个事务出来」**，恢复出来的数据，与原库不同。

可以看到，「如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致」。

---

分库分表

当单表的数据量达到1000W或100G以后，优化索引、添加从库等可能对数据库性能提升效果不明显，此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担，缩短查询的时间。

数据切分可以分为两种方式：垂直划分和水平划分。

垂直划分

垂直划分数据库是根据业务进行划分，例如购物场景，可以将库中涉及商品、订单、用户的表分别划分成一个库，通过降低单库的大小来提高性能。同样的，分表的情况就是将一个大表根据业务功能拆分成一个个子表，例如商品基本信息和商品描述，商品基本信息一般会展示在商品列表，商品描述在商品详情页，可以将商品基本信息和商品描述拆分成两张表。

优点：行记录变小，数据页可以存放更多记录，在查询时减少I/O次数。

缺点：

• 主键出现冗余，需要管理冗余列；
• 会引起表连接JOIN操作，可以通过在业务服务器上进行join来减少数据库压力；
• 依然存在单表数据量过大的问题。

水平划分

水平划分是根据一定规则，例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致，但是数据得以拆分，从而提升性能。

优点：单库（表）的数据量得以减少，提高性能；切分出的表结构相同，程序改动较少。

缺点：

• 分片事务一致性难以解决
• 跨节点join性能差，逻辑复杂
• 数据分片在扩容时需要迁移

---

什么是MySQL主从同步(主从复制)？

主从复制（Replication）是指数据可以从一个MySQL数据库主服务器复制到一个或多个从服务器，从服务器可以复制主服务器中的所有数据库或者特定的数据库，或者特定的表。默认采用异步模式。

为什么要做主从同步？

• 读写分离，主服务器负责写，从服务器负责读，使数据库能支撑更大的并发。

  • 缓解了锁的争用，即使主服务器中加了锁，依然可以进行读操作；

  • 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；

  • 增加冗余，提高可用性

• 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的I/O性能。

• 数据备份，保证数据的安全。

MySQL 主从之间是怎么同步数据的?

• 1.master 主库将此次更新的事件类型**「写入到主库的 binlog文件」**中
• 2.master「创建 log dump 线程通知 slave」 需要更新数据
• 3.「slave」 向 master节点发送请求，「将该 binlog文件内容存到本地的 relaylog中」
• 4.「slave 开启 sql 线程」读取 relaylog中的内容，「将其中的内容在本地重新执行一遍」，完成主从数据同步

「同步策略」：

• 1.「全同步复制」：主库强制同步日志到从库，等全部从库执行完才返回客户端，性能差
• 2.「半同步复制」：主库收到至少一个从库确认就认为操作成功，从库写入日志成功返回ack确认

主从延迟要怎么解决?

• MySQL 5.6 版本以后，提供了一种**「并行复制」**的方式，通过将 SQL 线程转换为多个 work 线程来进行重放
• 2.「提高机器配置」(王道)
• 3.在业务初期就选择合适的分库、分表策略，**「避免单表单库过大」**带来额外的复制压力
• 4.「避免长事务」
• 5.「避免让数据库进行各种大量运算」
• 6.对于一些对延迟很敏感的业务**「直接使用主库读」**

---

exist和in的区别？

exists用于对外表记录做筛选。exists会遍历外表，将外查询表的每一行，代入内查询进行判断。当exists里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果exists里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。

select a.* from A awhere exists(select 1 from B b where a.id=b.id)

in是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。

select * from Awhere id in(select id from B)

• 子查询的表比较大的时候，使用exists可以有效减少总的循环次数来提升速度；

• 当外查询的表比较大的时候，使用in可以有效减少对外查询表循环遍历来提升速度。

truncate、delete与drop区别？

• Delete用来删除表的全部或者部分数据，执行delete之后，用户需要提交之后才会执行，会触发表上的DELETE触发器，DELETE之后表结构还在，删除很慢，一行一行地删，因为会记录日志，可以利用日志还原数据；
• Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器。操作比DELETE快很多（直接把表drop掉，再创建一个新表，删除的数据不能找回）。如果表中有自增（AUTO_INCREMENT）列，则重置为1；
• Drop命令从数据库中删除表，所有的数据行，索引和约束都会被删除；不能回滚，不会触发触发器；

having和where的区别？

• 二者作用的对象不同，where子句作用于表和视图，having作用于组。
• where在数据分组前进行过滤，having在数据分组后进行过滤。

---

参考：

• MySQL经典36问！
• 《八股文》35道MySQL面试题