Mysql数据库面经

1、SQL

1.1 介绍一下数据库分页

MySQL的分页语法：

在MySQL中，SELECT语句默认返回所有匹配的行，它们可能是指定表中的每个行。为了返回第一行或前几行，可使用LIMIT子句，以实现分页查询。LIMIT子句的语法如下：

• 在所有的查询结果中，返回前5行记录。 SELECT prod_name FROM products LIMIT 5;
• 在所有的查询结果中，从第5行开始，返回5行记录。 SELECT prod_name FROM products LIMIT 5,5;

总之，带一个值的LIMIT总是从第一行开始，给出的数为返回的行数。带两个值的LIMIT可以指定从行号为第一个值的位置开始。

1.2 介绍一下SQL中的聚合函数

常用的聚合函数有COUNT()、AVG()、SUM()、MAX()、MIN()，

1.3 表跟表是怎么关联的？

表与表之间常用的关联方式有两种：内连接、外连接，下面以MySQL为例来说明这两种连接方式。

• 内连接：

  内连接通过INNER JOIN来实现，它将返回两张表中满足连接条件的数据，不满足条件的数据不会查询出来。

• 外连接：

  外连接通过OUTER JOIN来实现，它会返回两张表中满足连接条件的数据，同时返回不满足连接条件的数据。外连接有两种形式：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）。

  左外连接：可以简称为左连接（LEFT JOIN），它会返回左表中的所有记录和右表中满足连接条件的记录。

  右外连接：可以简称为右连接（RIGHT JOIN），它会返回右表中的所有记录和左表中满足连接条件的记录。

1.4 说一说你对外连接的了解

1.5 说一说数据库的左连接和右连接

1.6 SQL中怎么将行转成列？

• 使用 CASE…WHEN…THEN 语句实现行转列

• 使用 IF() 函数实现行转列

1.7 谈谈你对SQL注入的理解

如何解决SQL注入

• 严格的参数校验

参数校验就没得说了，在一些不该有特殊字符的参数中提前进行特殊字符校验即可。

• SQL预编译

具体是怎样防止SQL注入的呢？实际上当将绑定的参数传到MySQL服务器，MySQL服务器对参数进行编译，即填充到相应的占位符的过程中，做了转义操作。我们常用的JDBC就有预编译功能，不仅提升性能，而且防止SQL注入。

1.8 将一张表的部分数据更新到另一张表，该如何操作呢？

可以采用关联更新的方式，将一张表的部分数据，更新到另一张表内。

1.9 WHERE和HAVING有什么区别？

WHERE是一个约束声明，使用WHERE约束来自数据库的数据，WHERE是在结果返回之前起作用的，WHERE中不能使用聚合函数。

HAVING是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在HAVING中可以使用聚合函数。另一方面，HAVING子句中不能使用除了分组字段和聚合函数之外的其他字段。

从性能的角度来说，HAVING子句中如果使用了分组字段作为过滤条件，应该替换成WHERE子句。因为WHERE可以在执行分组操作和计算聚合函数之前过滤掉不需要的数据，性能会更好。

2、索引

2.0 为什么索引使用B+树而不是B树（看这个即可）

• B+树中只有叶子节点会带有指向记录的指针；而B树则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。
• B+树中所有叶子节点都是通过指针连接在一起，而B树不会。
  1、非叶子节点不会带上指向记录的指针，这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。
  2、叶子节点之间通过指针来连接，范围扫描将十分简单，而对于B树来说，则需要在叶子节点和内部节点不停的往返移动。具体的来讲，如何想扫描一次所有数据，对于b+树来说，因为他们的叶子结点是连在一起的，所以可以横向的遍历过去。而对于b-树来说，就这能中序遍历了。

B树 的优点： 对于在内部节点的数据，可直接得到，不必根据叶子节点来定位。

2.0 为什么索引使用B+树而不是B树

B树是为了提高磁盘或外部存储设备查找效率而产生的一种多路平衡查找树。

B+树为B树的变形结构，用于大多数数据库或文件系统的存储而设计。

因为我们要考虑磁盘io的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当我们通过索引查找某行数据的时候，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读入内存。也就是说查询过程中会发生多次磁盘1o，而磁盘rc次数越多，所消耗的时间也就越大。对于树来说，Io次数就是树的高度。
1、B+树的菲叶子结点不存放实际的记录数据，仅存放索引，因为数据量相同的情况下，相比B树，B+树的树高度更低，查询磁盘o的次数会更少。
2、B+树删除一个节点的时候，可以直接从叶子节点中删除，甚至可以不动非叶子节点，这样删除非常快。z树非叶子结点也存放数据，删除数据可能会影响树的变化。
3、B+树叶子结点之间用链表连接起来，有利于范围查找﹔而B树要实现范围查找，只能通过树的中序遍历来完成范围查询，这会涉及多个节点的磁盘ro操作，范围查询效率不如B+树。

2.0 为什么索引使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树?

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

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

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

2.0 补充：B树、B+树、红黑树比较

版本1

• B树相对于红黑树的区别：

在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。B树可以有多个子女，从几十到上千，可以降低树的高度。

• B树和B+树的区别

1. B树则所有节点都带有带有指向记录（数据）的指针（ROWID），B+树中只有叶子节点会带有指向记录(数据)的指针（ROWID）。因为B+树它把所有的卫星数据(或指向数据的指针)都存储在叶节点中，内部节点只存放关键字和孩子指针，不会带上指向记录的指针（ROWID），这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点(优点1)。

2. B+树中每个叶子节点都包含指向下一个叶子节点的指针。所有叶子节点都是通过指针连接在一起，而B树不会。 叶子节点之间通过指针来连接，范围扫描将十分简单(优点2)，而对于B树来说，则需要在叶子节点和内部节点不停的往返移动。
   　 B+树还有一个最大的好处，遍历更加高效，方便扫库（优点2），B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持。这是数据库选用B+树的最主要原因。（B+树的遍历更加高效，B树需要以中序的方式遍历节点，而B+树只需把所有叶子节点串成链表就可以从头到尾遍历）。

3. B+树每个节点的指针和key一样多，B树每个节点指针比key多1。

• 为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？

1. B+的磁盘读写代价更低

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

2. B+tree的查询效率更加稳定

由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

数据库索引采用B+树的主要原因是 B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）

• B树、 B+树各有优点:

B+树的优点：

1. 非叶子节点不会带上指向记录的指针（ROWID），这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。

2. 叶子节点之间通过指针来连接，范围扫描将十分简单，而对于B树来说，则需要在叶子节点和内部节点不停的往返移动。

B树的优点：

对于在内部节点的数据，可直接得到，不必根据叶子节点来定位。

版本2

1、B树与B+树

（1）B树与B+树的区别

1）B+树中只有叶子节点会带有指向记录的指针；而B树则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。

2）B+树中所有叶子节点都是通过指针连接在一起，而B树不会。

（2）B树与B+树的优缺点

1）B树优点：由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

a、因为磁盘查找存取的次数往往由树的高度所决定，磁盘IO是非常耗时的操作，计算机操作系统对此做了优化：预读；每一次IO时，不仅仅把当前磁盘地址的数据加载到内存，同时也把相邻数据也加载到内存缓冲区中。因为局部预读原理说明：当访问一个地址数据的时候，与其相邻的数据很快也会被访问到。每次磁盘IO读取的数据我们称之为一页（page）。一页的大小与操作系统有关，一般为4k或者8k。这也就意味着读取一页内数据的时候，实际上发生了一次磁盘IO。

2）B+树优点：

a、非叶子节点不会带上指向记录的指针，这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。

b、B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

c、在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针；另外一般来说，索引很大的话往往会以索引文件的形式存储的磁盘上，索引查找时产生磁盘I/O消耗相对于内存存取更加耗时，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的时间复杂度。树高度越小，I/O次数越少。因为B+树内节点不存储data，这样一个节点就可以存储更多的key。

2、红黑树与B+树的区别：

（1）红黑树用在内部排序，即全放在内存中的，map和set的内部实现就是红黑树；B+树多用于外存上时，B+被称为一个磁盘友好的数据结构。

（2）红黑树的思想

1）设计红黑树目的：主要针对于平衡二叉树的维护问题，红黑树读取略逊于AVL，维护强于AVL，每次插入和删除的平均旋转次数应该是远小于平衡树。所以自平衡的二叉查找树，在进行插入和删除等可能会破坏树的平衡的操作时，需要重新自处理达到平衡状态。

2）红黑树不是一个完美平衡二叉查找树，根结点的左子树与右子树的层数差可以大于1，但左子树和右子树的黑结点的层数是相等的，红黑树叫黑色完美平衡树

版本3

• 红黑树 和 b+树的用途有什么区别？
  红黑树多用在内部排序，即全放在内存中的，java的map和set的内部实现就是红黑树。

B+树多用于外存上时，B+也被成为一个磁盘友好的数据结构。

• B+树：

B+ 树是一种树数据结构，是一个特殊的二叉树，每个节点通常有多个子节点，一棵B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上孩子节点的节点。

1.非叶结点仅具有索引作用，跟记录有关的信息均存放在叶结点中。
2.树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录，便于区间查找和遍历。
3.B+ 树的优点在于：由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。因此访问叶子节点上关联的数据也具有更好的缓存命中率。B+树的叶子结点都是相连的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。
4.而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。但是B树也有优点，其优点在于，由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。下面是B 树和B+树的区别图：

如图所示，区别有以下两点：

1、B+树中只有叶子节点会带有指向记录的指针，而B树则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。
2、B+树中所有叶子节点都是通过指针连接在一起，而B树不会。

B+树 的优点：

1、非叶子节点不会带上指向记录的指针，这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。
2、叶子节点之间通过指针来连接，范围扫描将十分简单，而对于B树来说，则需要在叶子节点和内部节点不停的往返移动。具体的来讲，如何想扫描一次所有数据，对于b+树来说，可以从因为他们的叶子结点是连在一起的，所以可以横向的遍历过去。而对于b-树来说，就这能中序遍历了。

B树 的优点： 对于在内部节点的数据，可直接得到，不必根据叶子节点来定位。

b+树的应用场景:

B/B+树是为了磁盘或其它存储设备而设计的一种平衡多路查找树，适用于数据库存储数据.
二叉查找树的结构不适合数据库，因为它的查找效率与层数相关。越处在下层的数据，就需要越多次比较。

为什么b+磁盘友好？

磁盘读写代价更低
树的非叶子结点里面没有数据，这样索引比较小，可以放在一个blcok（或者尽可能少的blcok）里面。避免了树形结构不断的向下查找，然后磁盘不停的寻道，读数据。这样的设计，可以降低io的次数。

查询效率更加稳定
非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

遍历所有的数据更方便
B+树只要遍历叶子节点就可以实现整棵树的遍历，而其他的树形结构 要中序遍历才可以访问所有的数据。

2.0数据库三大范式

https://blog.csdn.net/ddzzz_/article/details/119277573

2.0 SQL约束有哪几种

2.1 说一说你对MySQL索引的理解

2.2 索引有哪几种？

• 普通索引和唯一索引

普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。

唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引是一种特殊的唯一索引，不允许有空值。

• 单列索引和组合索引

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

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

• 全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。

• 空间索引
  空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

2.3 如何创建及保存MySQL的索引？

MySQL支持多种方法在单个或多个列上创建索引：

• 在创建表的时候创建索引：

使用CREATE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。创建表时创建索引的基本语法如下：

CREATE TABLE table_name [col_name data_type]
 [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]

其中，UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；INDEX与KEY为同义词，两者作用相同，用来指定创建索引。

例如，可以按照如下方式，在id字段上使用UNIQUE关键字创建唯一索引：

CREATE TABLE t1 (  id INT NOT NULL,     name CHAR(30) NOT NULL,    
 UNIQUE INDEX UniqIdx(id) );

2.4 MySQL怎么判断要不要加索引？

当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

2.5 只要创建了索引，就一定会走索引吗？

不一定。

比如，在使用组合索引的时候，如果没有遵从“最左前缀”的原则进行搜索，则索引是不起作用的。

举例，假设在id、name、age字段上已经成功建立了一个名为MultiIdx的组合索引。索引行中按id、name、age的顺序存放，索引可以搜索id、（id,name）、（id, name, age）字段组合。如果列不构成索引最左面的前缀，那么MySQL不能使用局部索引，如（age）或者（name,age）组合则不能使用该索引查询。### 2.6 如何判断数据库的索引有没有生效？
可以使用EXPLAIN语句查看索引是否正在使用。

2.7 如何评估一个索引创建的是否合理？

建议按照如下的原则来设计索引：

• 避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。

• 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

2.8 索引是越多越好吗？

2.9 数据库索引失效了怎么办？

可以采用以下几种方式，来避免索引失效：

• 使用组合索引时，需要遵循“最左前缀”原则；

• 不在索引列上做任何操作，例如计算、函数、类型转换，会导致索引失效而转向全表扫描；

• 尽量使用覆盖索引（之访问索引列的查询），减少 select * 覆盖索引能减少回表次数；

• 少用or，用它来连接时会索引失效。

2.10 所有的字段都适合创建索引吗？

不是。下列几种情况，是不适合创建索引的：

• 频繁更新的字段不适合建立索引；

• where条件中用不到的字段不适合建立索引；

• 数据比较少的表不需要建索引；

• 数据重复且分布比较均匀的的字段不适合建索引，例如性别、真假值；

• 参与列计算的列不适合建索引。

2.11 说一说索引的实现原理

在MySQL中，索引是在存储引擎层实现的，不同存储引擎对索引的实现方式是不同的，下面我们探讨一下MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM索引实现：MyISAM的索引文件仅仅保存数据记录的地址。

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，MyISAM索引的原理图如下。这里假设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别辅助索引，同样也是一颗B+Tree，data域保存数据记录的地址。只是主索引要求key是唯一的，而辅助索引的key可以重复。

InnoDB索引实现：InnoDB的数据文件本身就是索引文件,叶节点data域保存了完整的数据记录。

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

下图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

2.12 介绍一下数据库索引的重构过程

什么时候需要重建索引呢？

表上频繁发生update,delete操作；

表上发生了alter table …move操作（move操作导致了rowid变化）。

怎么判断索引是否应该重建？

一般看索引是否倾斜的严重，是否浪费了空间，对索引进行结构分析：

analyze index index_name validate structure;

在相同的session中查询index_stats表：

select height,DEL_LF_ROWS/LF_ROWS from index_stats;

当查询的height>=4（索引的深度，即从根到叶节点的高度）或DEL_LF_ROWS/LF_ROWS>0.2的情况下，就应该考虑重建该索引。

如何重建索引？

• drop原索引，然后再创建索引：这种方式相当耗时，一般不建议使用。

drop index index_name;
create index index_name on table_name (index_column);

• 直接重建索引：此方法较快，建议使用。

rebuild是快速重建索引的一种有效的办法，因为它是一种使用现有索引项来重建新索引的方法。

alter index indexname rebuild;
alter index indexname rebuild online;

2.13 MySQL的索引为什么用B+树？

2.14 联合索引的存储结构是什么，它的有效方式是什么？

从本质上来说，联合索引还是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2，参考下图。另外，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，所以使用联合索引时遵循最左前缀集合。

2.15 MySQL的Hash索引和B树索引有什么区别？

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。它们有以下的不同：

• 一般情况下,hash索引进行等值查询更快，但是却无法进行范围查询。
  因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。

• hash索引不支持使用索引进行排序，原理同上。

• hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为hash函数的不可预测。

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

2.16 聚簇索引和非聚簇索引有什么区别？

在InnoDB存储引擎中，可以将B+树索引分为聚簇索引和辅助索引（非聚簇索引）。无论是何种索引，每个页的大小都为16KB，且不能更改。

聚簇索引是根据主键创建的一棵B+树，聚簇索引的叶子节点存放了表中的所有记录。辅助索引是根据索引键创建的一棵B+树，与聚簇索引不同的是，其叶子节点仅存放索引键值，以及该索引键值指向的主键。也就是说，如果通过辅助索引来查找数据，那么当找到辅助索引的叶子节点后，很有可能还需要根据主键值查找聚簇索引来得到数据，这种查找方式又被称为书签查找。因为辅助索引不包含行记录的所有数据，这就意味着每页可以存放更多的键值，因此其高度一般都要小于聚簇索引。

2.17 什么是联合索引？

2.18 select in语句中如何使用索引？

索引是否起作用，主要取决于字段类型：

如果字段类型为字符串，需要给in查询中的数值与字符串值都需要添加引号，索引才能起作用。

如果字段类型为int，则in查询中的值不需要添加引号，索引也会起作用。

IN的字段，在联合索引中，按以上方法，也会起作用。

2.19 模糊查询语句中如何使用索引？

在MySQL中模糊查询 mobile like ‘%8765’，这种情况是不能使用 mobile 上的索引的，那么如果需要根据手机号码后四位进行模糊查询，可以用一下方法进行改造。

我们可以加入冗余列（MySQL5.7之后加入了虚拟列，使用虚拟列更合适，思路相同），比如 mobile_reverse，内部存储为 mobile 的倒叙文本，如 mobile为17312345678，那么 mobile_reverse 存储 87654321371，为 mobile_reverse 列建立索引，查询中使用语句 mobile_reverse like reverse(’%5678’) 即可。

reverse 是 MySQL 中的反转函数，这条语句相当于 mobile_reverse like ‘8765%’ ，这种语句是可以使用索引的。

3、 事务

3.1 说一说你对数据库事务的了解

事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。在事务中的操作，要么都执行修改，要么都不执行，这就是事务的目的，也是事务模型区别于文件系统的重要特征之一。

事务需遵循ACID四个特性：

• 原子性（Atomicity）：事务是一个原子操作，由一系列动作组成。事务的原子性确保动作要么全部完成，要么完全不起作用。(不可分割，要么都成功，要么都失败)

• 一致性（Consistency）：一旦事务完成（不管成功还是失败），系统必须确保它所建模的业务处于一致的状态，而不会是部分完成部分失败。在现实中的数据不应该被破坏。（操作前操作后总钱数不变，加起来还是200，也就是转账前转账后总量都是200）

• 隔离性（Isolation）：可能有许多事务会同时处理相同的数据，因此每个事务都应该与其他事务隔离开来，防止数据损坏。（多事务操作时它们之间不会产生影响，两个人都操作同一条记录不会产生影响）

• 持久性（Durability）：一旦事务完成，无论发生什么系统错误，它的结果都不应该受到影响，这样就能从任何系统崩溃中恢复过来。通常情况下，事务的结果被写到持久化存储器中。(事务要提交，提交了表中数据发生了变化)

3.2 事务有哪几种类型，它们之间有什么区别？

事务可以分为以下几种类型：

扁平事务：是事务类型中最简单的一种，而在实际生产环境中，这可能是使用最为频繁的事务。在扁平事务中，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束。处于之间的操作是原子的，要么都执行，要么都回滚。

带有保存点的扁平事务：除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态，这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效，放弃整个事务不合乎要求，开销也太大。保存点（savepoint）用来通知系统应该记住事务当前的状态，以便以后发生错误时，事务能回到该状态。

链事务：可视为保存点模式的一个变种。链事务的思想是：在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务。注意，提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行的。

嵌套事务：是一个层次结构框架。有一个顶层事务（top-level transaction）控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务（subtransaction），其控制每一个局部的变换。

分布式事务：通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。对于分布式事务，同样需要满足ACID特性，要么都发生，要么都失效。

对于MySQL的InnoDB存储引擎来说，它支持扁平事务、带有保存点的扁平事务、链事务、分布式事务。对于嵌套事务，MySQL数据库并不是原生的，因此对于有并行事务需求的用户来说MySQL就无能为力了，但是用户可以通过带有保存点的事务来模拟串行的嵌套事务。

3.3 MySQL的ACID特性分别是怎么实现的？

3.4 谈谈MySQL的事务隔离级别

SQL 标准定义了四种隔离级别，这四种隔离级别分别是：

• 读未提交（READ UNCOMMITTED）；

• 读提交 （READ COMMITTED）；

• 可重复读 （REPEATABLE READ）；

• 串行化 （SERIALIZABLE）。

事务隔离是为了解决脏读、不可重复读、幻读问题，下表展示了 4 种隔离级别对这三个问题的解决程度：

上述4种隔离级别MySQL都支持，并且InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ，但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock的锁算法，因此避免了幻读的产生。所以，InnoDB存储引擎在默认的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABLE隔离级别。

• 扩展阅读

并发情况下，读操作可能存在的三类问题：

脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。

不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。

幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

3.5 MySQL的事务隔离级别是怎么实现的？

3.6 事务可以嵌套吗？

可以，因为嵌套事务也是众多事务分类中的一种，它是一个层次结构框架。有一个顶层事务控制着各个层次的事务，顶层事务之下嵌套的事务被称为子事务，它控制每一个局部的变换。

需要注意的是，MySQL数据库不支持嵌套事务。

3.7 如何实现可重复读？

参考答案

MySQL的InnoDB引擎，在默认的REPEATABLE READ的隔离级别下，实现了可重复读，同时也解决了幻读问题。它使用Next-Key Lock算法实现了行锁，并且不允许读取已提交的数据，所以解决了不可重复读的问题。另外，该算法包含了间隙锁，会锁定一个范围，因此也解决了幻读的问题。

3.8 如何解决幻读问题？

参考答案

MySQL的InnoDB引擎，在默认的REPEATABLE READ的隔离级别下，实现了可重复读，同时也解决了幻读问题。它使用Next-Key Lock算法实现了行锁，并且不允许读取已提交的数据，所以解决了不可重复读的问题。另外，该算法包含了间隙锁，会锁定一个范围，因此也解决了幻读的问题。

3.9 MySQL事务如何回滚？

在MySQL默认的配置下，事务都是自动提交和回滚的。当显示地开启一个事务时，可以使用ROLLBACK语句进行回滚。该语句有两种用法：

ROLLBACK：要使用这个语句的最简形式，只需发出ROLLBACK。同样地，也可以写为ROLLBACK WORK，但是二者几乎是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

ROLLBACK TO [SAVEPOINT] identifier ：这个语句与SAVEPOINT命令一起使用。可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作。

4、锁

4.0 为什么要加锁?

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

4.0 锁分类

• 根据锁的性质分

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

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

• 根据锁的粒度分

  1、表锁：锁住整张表

  2、行锁：锁住单独数据行

  意向锁：当要给数据表加表锁时，先要检查表中每一行是否加行锁，这样就会涉及到性能极低的遍历，MySQL 中在添加行锁时，还会为表添加意向锁，用来标记表中是否存在行锁

  锁升级：粒度小的锁升级为粒度大的锁，InnoDB 中，行锁默认是添加在索引上的，当没有命中索引时，就会使用表锁

• next-key 锁解决幻读问题

  gap 锁：当事务在索引上进行范围操作时，会对满足条件的索引值开区间（A，B）中的所有索引加锁，包括不存在索引，保证在事务进行过程中，不会由其他事务加入或删除操作范围内的事务

  record 锁：当使用唯一索引进行精确查找且数据存在时，会为数据加 record 锁

隔离级别的锁实现

排他锁通过在事务执行写操作时，事务提交前，禁止其他事务进行写操作，从而解决更新丢失问题，禁止其他事务进行读操作，从而解决脏读问题

共享锁通过在事务进行读操作时，禁止其他事务进行写操作，保证事务中多次读取数据一致，从而解决不可重复读问题

通过 next-key 锁和表锁解决了幻读问题，表锁本质上就是为所有数据都加上了行锁，从而实现串行化，效率低但不会产生死锁

4.0 什么是死锁?如何解决死锁?

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

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。

常见的解决死锁的方法:
1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率;
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率;

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

4.2 介绍一下间隙锁

InnoDB存储引擎有3种行锁的算法，间隙锁（Gap Lock）是其中之一。间隙锁用于锁定一个范围，但不包含记录本身。它的作用是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生。

4.3 InnoDB中行级锁是怎么实现的？

InnoDB行级锁是通过给索引上的索引项加锁来实现的。只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

当表中锁定其中的某几行时，不同的事务可以使用不同的索引锁定不同的行。另外，不论使用主键索引、唯一索引还是普通索引，InnoDB都会使用行锁来对数据加锁。

4.4 数据库在什么情况下会发生死锁？

参考答案

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。下图演示了死锁的一种经典的情况，即A等待B、B等待A，这种死锁问题被称为AB-BA死锁。

5、 优化

5.1 说一说你对数据库优化的理解

MySQL数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。例如，通过优化文件系统，提高磁盘I\O的读写速度；通过优化操作系统调度策略，提高MySQL在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。

• 针对查询，我们可以通过使用索引、使用连接代替子查询的方式来提高查询速度。

• 针对慢查询，我们可以通过分析慢查询日志，来发现引起慢查询的原因，从而有针对性的进行优化。

• 针对插入，我们可以通过禁用索引、禁用检查等方式来提高插入速度，在插入之后再启用索引和检查。

• 针对数据库结构，我们可以通过将字段很多的表拆分成多张表、增加中间表、增加冗余字段等方式进行优化。

5.2 该如何优化MySQL的查询？

使用索引：

如果查询时没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数，达到提高查询速度的目的。

5.3 怎样插入数据才能更高效？

影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。针对这些情况，可以分别进行优化。

• 对于MyISAM引擎的表，常见的优化方法如下：

  禁用索引

  禁用唯一性检查

  使用批量插入

• 对于InnoDB引擎的表，常见的优化方法如下：

  禁用唯一性检查

  禁用外键检查

  禁用自动提交

5.4 表中包含几千万条数据该怎么办？

建议按照如下顺序进行优化：

• 优化SQL和索引；

• 增加缓存，如memcached、redis；

• 读写分离，可以采用主从复制，也可以采用主主复制；

• 使用MySQL自带的分区表，这对应用是透明的，无需改代码，但SQL语句是要针对分区表做优化的；

• 做垂直拆分，即根据模块的耦合度，将一个大的系统分为多个小的系统；

• 做水平拆分，要选择一个合理的sharding key，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表。

5.5 MySQL的慢查询优化有了解吗？

就是比较慢的查询

优化SQL来减少慢查询

1. 索引优化
   慢查询非常多的情况是由于SQL没有走适当的索引导致的。但是，即使加上索引，如果使用不正确，SQL语句在执行时依然不会走索引。哪些情况下SQL语句不走索引呢？

• WHERE条件中的1=1，查询条件永远为真，可能导致WHERE条件失效，进行全表查询；
• 使用函数或者隐式转化会导致不走索引；
• LIKE匹配通配符号在前面的时候，不走索引；
• 使用了否定条件；
• OR其中一个有索引，另一个没有的情况；
• 多列索引需要满足最左匹配原则；
• 两张表字符集不一样或者编码不一样，联表查询时；
• IN的内容过多，会不走索引；

2. 拆解分批
   如果数据量比较大，则可以尝试拆解分批的方式来减少慢查询：

• 多层嵌套查询改为多次查询；
• IN子查询影响查询性能，用JOIN方式代替；
• 一次查询数量过于庞大，拆成多次查询、拼装。
• 用了反向查询（比如NOT IN）或者IN语句参数集太多，可能会导致全表扫描，这种情况尽量拆分语句，或者内存中过滤解决；
• 将⼤字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据；
• 分库分表；
• 归档；

5.6 说一说你对explain的了解

MySQL中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句，EXPLAIN语句的基本语法如下：

EXPLAIN [EXTENDED] SELECT select_options

使用EXTENED关键字，EXPLAIN语句将产生附加信息。执行该语句，可以分析EXPLAIN后面SELECT语句的执行情况，并且能够分析出所查询表的一些特征。下面对查询结果进行解释：

• id：SELECT识别符。这是SELECT的查询序列号。

• select_type：表示SELECT语句的类型。

• table：表示查询的表。

DESCRIBE语句的使用方法与EXPLAIN语句是一样的，分析结果也是一样的，并且可以缩写成DESC。。DESCRIBE语句的语法形式如下：

DESCRIBE SELECT select_options

5.7 explain关注什么？

重点要关注如下几列：

6、其他

6.1 介绍一下数据库设计的三大范式

6.2 说一说你对MySQL引擎的了解

6.3 (日志)说一说你对redo log、undo log、binlog的了解

redo log用来保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能。

• binlog（Binary Log）：

  二进制日志文件就是常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录在日志文件中，其中还包括每条语句所执行的时间和所消耗的资源，以及相关的事务信息。

  默认情况下，二进制日志功能是开启的，启动时可以重新配置–log-bin[=file_name]选项，修改二进制日志存放的目录和文件名称。

• redo log：

  重做日志用来实现事务的持久性，即事务ACID中的D。它由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是易失的；二是重做日志文件（redo log file），它是持久的。

  InnoDB是事务的存储引擎，它通过Force Log at Commit机制实现事务的持久性，即当事务提交（COMMIT）时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。这里的日志是指重做日志，在InnoDB存储引擎中，由两部分组成，即redo log和undo log。

  redo log用来保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能。redo log基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作。而undo log是需要进行随机读写的。

• undo log：

  重做日志记录了事务的行为，可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要undo。因此在对数据库进行修改时，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。

  redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段（segment）中，这个段称为undo段（undo segment），undo段位于共享表空间内。

6.4 谈谈你对MVCC的了解

InnoDB默认的隔离级别是RR（REPEATABLE READ），RR解决脏读、不可重复读、幻读等问题，使用的是MVCC。MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议。它最大的优点是读不加锁，因此读写不冲突，并发性能好。InnoDB实现MVCC，多个版本的数据可以共存，主要基于以下技术及数据结构：

隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的事务id、指向undo log的指针等。

基于undo log的版本链：每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。

ReadView：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本。但是具体要恢复到哪个版本，则需要根据ReadView来确定。所谓ReadView，是指事务（记做事务A）在某一时刻给整个事务系统（trx_sys）打快照，之后再进行读操作时，会将读取到的数据中的事务id与trx_sys快照比较，从而判断数据对该ReadView是否可见，即对事务A是否可见。

6.5 MySQL主从同步是如何实现的？

复制（replication）是MySQL数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。总体来说，replication的工作原理分为以下3个步骤：

主服务器（master）把数据更改记录到二进制日志（binlog）中。

从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。

从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

复制的工作原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是，复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务器之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大。复制的工作原理如下图所示，其中从服务器有2个线程，一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL线程，复制执行中继日志。