SQL其他高频面试题

MySQL中索引的高频面试问题

MySQL中锁的高频面试问题

MySQL中事务高频面试题

SQL其他高频面试题

1、数据库的三大范式？

回答：

第一范式：每个字段都不能再拆分

第二范式：在第一范式的基础上，非主键字段列完全依赖于主键

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

在设计数据库结构时，一般都要遵循上述三个范式，如果不遵守，需要有充足的理由。

2、MySQL数据库有哪些数据类型？

回答：大致可分整数类型、小数类型、日期类型、文本类型。

(如：int、double、time、date、varchar等)

追问：char和varchar的区别？

回答：

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

回答：这里主要是考察索引相关的内容。由于索引的维护需要额外的存储空间存放索引文件，对数据库进行增删改时同时也会对索引文件产生对应的操作，这些操作需要进行IO操作，会影响效率。关于百万级别的数据库删除数据时，MySQL官方手册给出了下面的步骤

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

回答：这个问题可以从下面几个角度来分析或者回答。

MySQL中索引的高频面试问题

1.请简述常用的索引有哪些种类?

回答：

普通索引: 即针对数据库表创建索引

唯一索引: 与普通索引类似，不同的就是：MySQL 数据库索引列的值必须唯一，但允许有空值

主键索引: 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

组合索引(联合索引): 为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。

即将数据库表中的多个字段联合起来作为一个组合索引。

2 MySQL数据库中索引的工作机制是什么？

回答：数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B 树及其变种 B+树

3 唯一和主键索引的区别，唯一与主与聚簇、非聚簇的关系？

唯一索引 与 主键索引

唯一索引是在表上一个或者多个字段组合建立的索引，这个（或这几个）字段的值组合起来在表中不可以重复。一张表可以建立任意多个唯一索引，但一般只建立一个。

主键是一种特殊的唯一索引，区别在于，唯一索引列允许null值，而主键列不允许为null值。一张表最多建立一个主键，也可以不建立主键。

聚簇索引、非聚簇索引、主键

在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的：

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

怎么理解呢？

聚簇索引的顺序，就是数据在硬盘上的物理顺序。一般情况下主键就是默认的聚簇索引。

一张表只允许存在一个聚簇索引，因为真实数据的物理顺序只能有一种。如果一张表上还没有聚簇索引，为它新创建聚簇索引时，就需要对已有数据重新进行排序，所以对表进行修改速度较慢是聚簇索引的缺点，对于经常更新的列不宜建立聚簇索引。

聚簇索引性能最好，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理地紧跟其后。一张表只能有一个聚簇索引，所以非常珍贵，必须慎重设置，一般要根据这个表最常用的SQL查询方式选择某个（或多个）字段作为聚簇索引（或复合聚簇索引）。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB[1]会选择一个唯一的非空索引代替（“唯一的非空索引”是指列不能出现null值的唯一索引，跟主键性质一样）。如果没有这样的索引，InnoDB会隐式地定义一个主键来作为聚簇索引。

聚簇索引 与 唯一索引
严格来说，聚簇索引不一定是唯一索引，聚簇索引的索引值并不要求是唯一的，唯一聚簇索引才是！在一个有聚簇索引的列上是可以插入两个或多个相同值的，这些相同值在硬盘上的物理排序与聚簇索引的排序相同，仅此而已。

4  InnoDB存储引擎有哪些常见的索引？

回答：B+树索引、全文索引和哈希索引。

5 说一下B+树与B树的区别？

6 为什么MySQL数据库使用B+树不使用B树？

回答：当存储同数量级的数据的时候，B+树的高度比B树的高度小，这样的话进程IO操作的次数就少，效果就高。因为B+树的所有非叶子节点只存索引，数据存在叶子节点，一般3层的树高度，即可存千万级别的数据，而B数不行。(具体的计算可以到网上去看看，有面试官可能会问你怎么算出来的。)

7 Hash 索引和 B+ 树索引区别是什么？你在设计索引是怎么抉择的？

8 如何选择在哪些列上建索引？

回答：一张表一般都要去建主键，所以主键索引几乎是每张表必备的，这个就不多说了。

选择性高的列，也就是重复度低的列。比如女子学校学生表中的性别列，所有数据的值都是女，这样的列就不适合建索引。比如学生表中的身份证号列，选择性就很高，就适合建索引。

经常用于查询的列（出现在where条件中的列）。不过如果不符合上一条的条件，即便是出现在where条件中也不适合建索引，甚至就不应该出现在where条件中。

多表关联查询时作为关联条件的列。比如学生表中有班级ID的列用于和班级表关联查询时作为关联条件，这个列就适合建索引。

值会频繁变化的列不适合建索引。因为在数据发生变化时是需要针对索引做一些处理的，所以如果不是有非常必要的原因，不要值会频繁变化的列上建索引，会影响数据更新的性能。反过来也就是说索引要建在值比较固定不变的列上。

一张表上不要建太多的索引。和上一条的原因类似，如果一张表上的索引太多，会严重影响数据增删改的性能。也会耗费很大的磁盘空间。

9 什么是最左匹配原则？

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。

就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。

可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。、

组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

10 说一下覆盖索引？

覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

举例子：

假设我们只需要查询商品的名称、价格信息，我们有什么方式来避免回表呢？我们可以建立一个组合索引，即商品编码、名称、价格作为一个组合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

从辅助索引中查询得到记录，而不需要通过聚族索引查询获得，MySQL 中将其称为覆盖索引。使用覆盖索引的好处很明显，我们不需要查询出包含整行记录的所有信息，因此可以减少大量的 I/O 操作。

通常在 InnoDB 中，除了查询部分字段可以使用覆盖索引来优化查询性能之外，统计数量也会用到。例如， SELECT COUNT(*) 时，如果不存在辅助索引，此时会

通过查询聚族索引来统计行数，如果此时正好存在一个辅助索引，则会通过查询辅助索引来统计行数，减少 I/O 操作。

MySQL中锁的高频面试问题

1.什么是锁，锁的作用是什么？

回答：锁是数据库系统区别文件系统的一个关键特性，锁机制用于管理对共享资源的并发访问，保持数据的完整性和一致性。【摘自：MySQL技术内幕InnoDB存储引擎】

2.数据库有哪些锁？lock和latch的区别

回答：数据库中有表锁和行锁等

lock锁：锁的对象是事务，用于锁定数据库中的对象，如表、页、行等，并且lock锁一般在commit或rollback后释放，有死锁机制。

latch锁：一般称为轻量级锁，要求锁定的时间必须非常短，在InnoDB中又可以分为mutex(互斥量)和rwlock(读写锁)。目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

3.InnoDB存储引擎中的锁都有哪些类型？

回答：可以分为共享锁、排他锁、意向锁、一致性非锁定读和一致性锁定读。

其中共享锁和排他锁均属于行级锁。

共享锁(S Lock)：运行事务读一行数据。

排他锁(X Lock)：允许事务删除或更新一行数据。

行锁的三种算法：

Record Lock：单个行记录上的锁

Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。

Next-Key Lock：Gap+Record Lock锁定一个范围，并且锁定记录本身。

意向锁属于表级别的锁，又可以分为意向共享锁(IS Lock)和意向排他锁(IX Lock)。

意向共享锁(IS Lock)：事务想要获得一张表中某几行的共享锁。

意向排他锁(IX Lock)：事务想要获得一张表中某几行的排他锁。

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

一致性锁定读：InnoDB存储引擎对于SELECT语句支持两种一致性锁定读的操作：

select ... for update和select ... lock in share mode。

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

什么是MVCC？

MVCC实现原理【MVCC多版本并发控制，指的是一种提高并发的技术。】
Multi-Version Concurrency Control。
最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

MVCC能解决什么问题，好处是？

数据库并发场景有三种，分别为：
读-读：不存在任何问题，也不需要并发控制
读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

MVCC带来的好处是？

MVCC可以为数据库解决以下问题

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题
MVCC只在读取已提交和可重复 读两种隔离级别下有作用

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记录默认被记录到系统表空间(ibdata)中，但从5.6开始，也可以使用独立的Undo 表空间。

Undo记录中存储的是老版本数据，当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链找到满足其可见性的记录。

另外, 在回滚段中的undo logs分为: insert undo log 和 update undo log insert undo
insert undo log : 事务对insert新记录时产生的undolog, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。 update undo
update undo log : 事务对记录进行delete和update操作时产生的undo log, 不仅在事务回滚时需要,
一致性读也需要，所以不能随便删除，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被purge线程删除。

4.InnoDB存储引擎在数据库每行数据的后面添加了三个字段
分别是事务ID、回滚指针和

6字节的DB_ROW_ID字段: 包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。

5.可见性比较算法（这里每个比较算法后面的描述是建立在rr级别下，rc级别也是使用该比较算法,此处未做描述）

设要读取的行的最后提交事务id(即当前数据行的稳定事务id)为 trx_id_current

当前新开事务id为 new_id

当前新开事务创建的快照read view 中最早的事务id为up_limit_id, 最迟的事务id为low_limit_id(注意这个low_limit_id=未开启的事务id=当前最大事务id+1)

比较:

1. trx_id_current < up_limit_id, 这种情况比较好理解, 表示, 新事务在读取该行记录时, 该行记录的稳定事务ID是小于, 系统当前所有活跃的事务, 所以当前行稳定数据对新事务可见, 跳到步骤5.

2. trx_id_current >= trx_id_last, 这种情况也比较好理解, 表示, 该行记录的稳定事务id是在本次新事务创建之后才开启的,但是却在本次新事务执行第二个select前就commit了，所以该行记录的当前值不可见, 跳到步骤4

3. trx_id_current <= trx_id_current <= trx_id_last, 表示: 该行记录所在事务在本次新事务创建的时候处于活动状态，从up_limit_id到low_limit_id进行遍历，如果trx_id_current等于他们之中的某个事务id的话，那么不可见,调到步骤4,否则表示可见。

4. 从该行记录的 DB_ROLL_PTR 指针所指向的回滚段中取出最新的undo-log的版本号, 将它赋值该 trx_id_current，然后跳到步骤1重新开始判断。

5. 将该可见行的值返回。

5.锁可能会带来什么问题？

回答：通过锁机制实现了事务的隔离性，使得事务可以并发的工作，但同时也会有一些潜在的问题。锁会带来如下问题：脏读、不可重复度、丢失修改和幻读。

不可重复读和幻读区别：

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。【摘自MySQL技术内幕：InnoDB存储引擎可以使用Next-Key Locking机制来避免Phantom Problem问题】

6.数据库中的死锁概念你知道吗？

回答：死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。

解决死锁的办法：一种是超时回滚，一种是采用死锁检测机制（wait-for graph等待图）

如果面试官让你举例子，可以举例下面的例子：

在 MySQL 中，gap lock 默认是开启的，即innodb_locks_unsafe_for_binlog 参数值是disable 的，且 MySQL 中默认的是 RR 事务隔离级别。

当我们执行以下查询 SQL 时，由于 order_no 列为非唯一索引，此时又是 RR 事务隔离级别，所以 SELECT 的加锁类型为 gap lock，这里的 gap 范围是 (4,+∞）。

执行查询 SQL 语句获取的 gap lock 并不会导致阻塞，而当我们执行以下插入 SQL 时，会在插入间隙上再次获取插入意向锁。插入意向锁其实也是一种 gap 锁，它与 gap lock 是冲突的，所以当其它事务持有该间隙的 gap lock 时，需要等待其它事务释放 gap lock 之后，才能获取到插入意向锁。

以上事务 A 和事务 B 都持有间隙 (4,+∞）的 gap 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的 gap 锁释放，于是就造成了循环等待，导致死锁。

最后送上锁之间的兼容性表格：

MySQL中事务高频面试题

1.什么是事务？

事务是数据库区别于文件系统的重要特性之一，事务可以一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成，事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作，要么都做修改，要么都不做，这就是事务的主要目的。

2.事务的特性有哪些？分别是怎么保证的？

回答：事务的四大特性分别是原子性、一致性、隔离性和持久性。

下面这段话摘自《MySQL技术内幕-InnoDB存储引擎》

原子性、一致性和持久性是通过数据库的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在事务进行中不断的写入。

3.事务的隔离级别有哪些？InnoDB存储引擎默认的是什么存储引擎？为什么？

回答：首先回答一些数据库中并发事务带来的问题

1.数据丢失

2.脏读

3.不可重复读

4.幻读

不同的隔离级别

分别可以解决并发事务产生的几个问题，对应如下：

未提交读（Read Uncommitted）：在事务 A 读取数据时，事务 B 读取和修改数据加了共享锁。这种隔离级别，会导致脏读、不可重复读以及幻读。

已提交读（Read Committed）：在事务 A 读取数据时增加了共享锁，一旦读取，立即释放锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁。也就是说，事务 A 在读取数据时，事务 B 只能读取数据，不能修改。当事务 A 读取到数据后，事务 B才能修改。这种隔离级别，可以避免脏读，但依然存在不可重复读以及幻读的问题。

可重复读（Repeatable Read）：在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁。也就是说，事务A 在没有结束事务时，事务 B 只能读取数据，不能修改。当事务 A 结束事务，事务 B 才能修改。这种隔离级别，可以避免脏读、不可重复读，但依然存在幻读的问题。

可序列化（Serializable）：在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事务 B 读取修改数据时增加了表级排他锁，直到事务结束才释放锁。可序列化解决了脏读、不可重复读、幻读等问题，但隔离级别越来越高的同时，并发性会越来越低。

建议:这块内容可以看一下《阿里调优手册》中的第33讲

需要阿里调优手册的后台回复：阿里调优手册

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

至于InnoDB为什么选用可重复读，我的个人理解是：在InnoDB存储引擎中，使用可重复读可以解决脏读、不可重复读，而幻读也有可能发生，但是是可以避免的，通过加Next-Key Lock锁可以解决幻读问题。并且并非隔离级别越高越好，隔离级别越高的话，并发性能越低，所以在实际的开发中，需要根据业务场景进行选择事务的隔离级别。

顶一下

(0)

100%

订阅 回复

踩一下

(0)

100%