数据库-常见面试题汇总_数据库面试

在外连接中，某些不满条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另一个表的行。分左连接、右连接、全连接三种。

SQL语言包括哪些类型？

数据定义：Create Table,Alter Table,Drop/Truncate Table, Create/Drop Index

数据操纵：Select ,Insert,Update,Delete

数据控制：Grant,Revoke

SQL 约束有哪几种？

• NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
• UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
• PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
• CHECK: 用于控制字段的值范围。

视图

视图的作用，视图可以更改么？

• 创建视图： create view xxx as xxx
• 视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询，不包含任何列或数据；
• 使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据;
• 视图创建后，可以使用与表相同的方式利用它们；
• 视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by则对视图再次order by将被覆盖。

对于某些视图，例如，未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

事务和锁

什么是事务和锁？

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上一个节点。为了确保要么执行，要么不执行，就可以使用事务。要将一组语句作为事务考虑，就需要通过ACID测试，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

锁：在所有的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。共享锁（只读不写）、排他锁（可读可写）等。

事务的四大特性实现原理？

原子性（Atomicity）：主要依靠undo.log日志实现，即在事务失败时执行回滚。undo.log日志会记录事务执行的sql，当事务需要回滚时，通过反向补偿回滚数据库状态。

一致性（Consistency）：就是事务再执行的前和后数据库的状态都是正常的，表现为没有违反数据完整性，参照完整性和用户自定义完整性等等，上面三种特性就是为了保证数据库的有一致性。

隔离性（Isolation）：多线程时，多事务之间互相产生了影响，要避免这个影响，那就加锁。mysql的锁有表锁，行锁，间隙锁等。写写操作通过加锁实现隔离性，写读操作通过MVCC实现。

持久性（Durability）：主要依靠redo.log日志实现。首先，mysql持久化通过缓存来提高效率，即在select时先查缓存，再查磁盘；在update时先更新缓存，再更新磁盘。以减少磁盘io次数，提高效率。但由于缓存断电就没了，所以需要redo.log日志。在执行修改操作时，sql会先写入到redo.log日志，再写入缓存中。这样即使断电，也能保证数据不丢失，达到持久性。采用顺序io，即文件追加方式，更快。

事务的隔离级别？

• 读未提交（脏读）
• 读已提交（不可重复读）
• 可重复读（幻读->间隙锁解决）
• 序列化读

脏读、不可重复读、幻读？

脏读：A事务执行过程中，B事务读取了A事务的修改。但是由于某些原因，A事务可能没有完成提交（读未提交），发生RollBack了操作，则B事务所读取的数据就会是不正确的。

不可重复读：B事务读取了两次数据，在这两次的读取过程中A事务修改了数据（读已提交），B事务的这两次读取出来的数据不一样。B事务这种读取的结果，即为不可重复读。

幻读：事务读取了两次数据，在这两次的读取过程中A事务添加了数据，B事务的这两次读取出来的数据不一样。

锁的分类

• 从数据库系统角度分为三种：X/排他/互斥锁、S/共享/读锁、U/更新锁。
• 从程序员角度分为两种：一种是悲观锁，一种乐观锁。
• 从级别角度分为三种：行（级）锁，表（级）锁，间隙锁。

还有其他锁，见下图：

图片来源：技术面试之：五问乐观锁悲观锁_哔哩哔哩_bilibili

X/排他/互斥锁

如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。

S/读/共享锁

用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。

更新锁

在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。

当使用共享锁时，修改数据的操作分为两步：

1. 首先获得一个共享锁，读取数据，
2. 然后将共享锁升级为排他锁，再执行修改操作。

读写锁

是一种 读共享，写独占的锁（共享锁+互斥锁）。可理解为一本小说有多个作家和读者，只能一个作家在写，允许一个作家和多个读者使用。

当读写锁被加了写锁时，其他线程对该锁加读锁或者写锁都会阻塞。

当读写锁被加了读锁时，其他线程对该锁加写锁会阻塞，加读锁会成功。

悲观锁

顾名思义，很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人拿这个数据就会block（阻塞），直到它拿走锁。利用事务的机制，适用临界区有IO操作，代码复杂，竞争激烈的情况。

乐观锁
没有用到锁，修改时认为自己可以拿到资源，修改资源的状态。是使用CAS来进行同步，要修改资源时进行一个compare再swap的操作。是应用层实现的机制，适用于并发写入少，大多是读操作的情况。

行（级）锁

某一行数据有多个修改的，后修改的需要等先修改的提交后再执行。

表（级）锁

一个原因：索引失效（例如，条件语句使用or连接），由行级锁升级为表锁。

间隙锁

条件语句表范围，例如，x列1-9，数据库中x列有4，6，8，那么在提交前就不能插入5。

间隙锁可解决幻读问题：MySQL 间隙锁解决幻读问题_Ronin_88的博客-CSDN博客_间隙锁解决幻读

悲观锁有哪些劣势？

1. 性能：阻塞和唤醒
2. 拥有锁的线程永久阻塞（永远不能释放锁）
3. 优先级，被阻塞的线程优先级高，持有锁的线程优先级低，导致优先级反转问题。

索引

索引的缺点？

索引一般比较大，存在磁盘中，占空间而且IO操作多了会耗时。

对表记录进行修改操作时，对有索引字段需要调整索引，耗时。

索引的分类？

• 主键索引
• 唯一索引
• 普通索引
• 多列索引
• 空间索引

聚簇索引的实现原理，为什么使用聚簇索引?

聚簇索引，索引的顺序就是数据存放的顺序，所以，很容易理解，一张数据表只能有一个聚簇索引。
聚簇索引要比非聚簇索引查询效率高很多，特别是范围查询的时候。

用不用聚簇索引

动作	使用聚簇索引	使用非聚簇索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

性能优化

数据库如何优化性能？

• SQL优化
• 加缓存
• 分库分表
• 读写分离

索引如何优化？

• 表的主键、外键必须有索引；
• 经常与其他表进行连接的表，在连接字段上应该建立索引；
• 频繁进行数据操作的表，不要建立太多的索引；

如何优化关联查询

• 确定ON或者USING子句中是否有索引。
• 确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。

为什么要分库分表？

分表

单表数据量太大，会极大影响sql执行的性能。一般来说，单表到几百万条数据的时候，性能就会相对差一些了，就得分表了。

分表就是把一个表的数据放到多个表中，然后查询的时候你就查一个表。例如按照用户 id 来分表，将一个用户的数据就放在一个表中（例如，有10000个用户，id从1-10000，分为两个表，那么id从1-5000的就放到orders1中，查询id为2000的用户的订单时就从orders1中查）。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内，比如每个表就固定在 200 万以内。

分库

一个库一般最多支撑到并发量2000，超过就要扩容了，一个健康的单库并发，最好保持在每秒 1000 左右。可以将一个库的数据拆分到多个库中，访问的时候就访问一个库好了。

分库分表有几种方法？

• 垂直拆分：把一个有很多字段的表给拆分成多个表，或者是多个库上去。每个库表的结构都不一样，每个库表都包含部分字段。一般来说，会将较少的访问频率很高的字段放到一个表里去，然后将较多的访问频率很低的字段放到另外一个表里去。
• 水平拆分：把一个表的数据给弄到多个库的多个表里去，但是每个库的表结构都一样，只不过每个库表放的数据是不同的，所有库表的数据加起来就是全部数据。

如何保证多个主从数据库之间的数据一致?

• 基于二进制日志复制（完整复制、增量复制）

数据库并发造成的读问题有哪些？如何解决？

• 脏读   -->   读提交
• 不可重复读  —>可重复读
• 幻读 ------>间隙锁/序列化

范式

数据库的三范式是什么？

• 第一范式：强调列的原子性，即数据库表的每一列都是不可分割的。
• 第二范式：要求实体的属性完全依赖主关键字，不能存在部分依赖。
• 第三范式：任何非主属性不依赖于其它非主属性，消除了传递依赖。

日志

数据库日志分为哪几种？

• 通用查询日志
• 慢查询日志
• 错误日志
• 二进制日志

Mysql

主键 超键 候选键 外键

• 主键：数据库表中对存储数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值(Null).
• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
• 候选键：是最小超键，即没有冗余元素的超键。
• 外键：在一个表中存在的另一个表的主键称此表的外键。

drop,delete与truncate的区别

drop直接删掉表，不可回滚，truncate删除表中所有数据，或者说截断表，与drop一样，不可回滚，再插入时自增长id又从1开始，delete删除表中数据，可以加where子句，可以回滚。

1.delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行回滚操作。truncate table则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器，执行速度快。

2.表和索引所占空间：当表被truncate后，这个表和索引所占用的空间会恢复到初始大小，而delete操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

3.一般而言，drop>truncate>delete

4.应用范围：truncate只能对table，delete可以是table和view

5.结构和数据：truncate和delete只删除数据，而drop则删除整个表（结构和数据)

6.truncate与不带where的delete:只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束(constrain)，触发器（trigger)，索引(index)；依赖于该表的存储过程/函数将被保留，但其状态会变为:invalid

MySQL中 in 和 exists 区别

MySQL中的in语句是把外表和内表作hash连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。大家一直认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

Mysql默认的事务隔离级别是什么？

可重复读

聊聊MySQL索引的发展过程？从没有索引、hash、二叉排序树、AVL树、B树、B+树 聊

没有索引，全遍历

哈希索引(hash index)，基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code),  哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。Memory引擎默认支持哈希索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储。

二叉排序树，理想情况下是二分，可能创建时比较深，和一条线差不多，性能并不高

AVL树，解决了二叉排序树的问题，但是

1、每次都要检查规则，再把树进行重新平衡，这个是非常消耗时间的

2、数据量大的话，红黑树的深度会比较深，树一旦深就代表着我们读取磁盘次数就会增加

B树，每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。从根节点进行二分查找，影响查询的是树的高度，使用B树，树的高度可以降低，适合单次查询，N个key，高度为h，树的度为d，时间复杂度为，。

B+树**，**每个节点的指针上限为2d而不是2d+1。**非叶子节点不存储data，只存储key，叶子节点不存储指针。**一般B+树的阶数（树节点包含的子节点数）不会超过100，这样一般保证树的高度在3～5层而已，查询速度大大的提升，适合遍历查询。

MySQL 支持哪些存储引擎?

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

MyISAM 和 InnoDB 的区别有哪些：

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

Mysql的Innodb引擎如何实现MVCC的？

MVCC全称是Multi-Version Concurrent Control，即多版本并发控制，在MVCC协议下，每个读操作会看到一个一致性的snapshot（快照），并且可以实现非阻塞的读。MVCC允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务ID，在同一个时间点，不同的事务看到的数据是不同的。

Innodb会为每一行添加两个字段，分别表示该行事务id（创建版本号）和上个版本(回滚指针)，填入的是事务的版本号，这个版本号随着事务的创建不断递增。

undo log记录数据各版本修改历史即事务链

ReadView是读视图，包含未提交的事务ID数组，数组中的最小和最大值组成，用于判断哪些版本可见：不在未提交数组中且事务ID<max或者是自己，都可见。

读已提交：每次都会生成ReadView。

可重复读：一直使用第一次的ReadView。

在repeated read的隔离级别下，具体各种数据库操作的实现：

• select：该行的创建版本号小于等于当前版本号，用于保证在select操作之前所有的操作已经执行落地
• insert：将新插入的行的创建版本号设置为当前系统的版本号。
• delete：将要删除的行的删除版本号设置为当前系统的版本号。
• update：不执行原地update，而是转换成insert + delete。将旧行的删除版本号设置为当前版本号，并将新行insert同时设置创建版本号为当前版本号。

其中，写操作（insert、delete和update）执行时，需要将系统版本号递增。

由于旧数据并不真正的删除，所以必须对这些数据进行清理，Innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。

通过MVCC很好的实现了事务的隔离性，可以达到repeated read级别，要实现serializable还必须加锁。

undo log

id	name	事务id（创建版本号）	上个版本(回滚指针)
1	王五	107	106
1	李四	101	100
1	张三	100	99

事务100已提交，事务101和107未提交，有一事务id为105的，查询当前name。ReadView  101,[101,107],107，107>105，不可读，101<105但在未提交事务数组，不可读，100<105,且不在未提交事务数组，读取100对应name张三。当101提交，在可重复读下，仍然为张三，因为ReadView没有变。可以通过间隙锁(select name for table where id = 1 for update)的方式解决幻读问题，即读取id为1所在行数据，添加间隙锁，使id为1所在行数据不可改变，读取后才能提交。

MyISAM和InnoDB

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

MySQL 执行查询的过程

1. 客户端通过 TCP 连接发送连接请求到 MySQL 连接器，连接器会对该请求进行权限验证及连接资源分配
2. 查缓存。（当判断缓存是否命中时，MySQL 不会进行解析查询语句，而是直接使用 SQL 语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。）
3. 语法分析（SQL 语法是否写错了）。 如何把语句给到预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义。
4. 优化。是否使用索引，生成执行计划。
5. 交给执行器，将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。

什么是MySQL的 binlog？

MySQL的 binlog 是记录所有数据库表结构变更（例如 CREATE、ALTER TABLE）以及表数据修改（INSERT、UPDATE、DELETE）的二进制日志。

Redis

Redis是什么？简述它的优缺点？

Redis本质上是一个Key-Value类型的内存数据库，很像Memcached，整个数据库加载在内存当中操作，定期通过异步操作把数据库中的数据flush到硬盘上进行保存。

因为是纯内存操作，Redis的性能非常出色，每秒可以处理超过 10万次读写操作，是已知性能最快的Key-Value 数据库。

优点：

• 读写性能极高， Redis能读的速度是110000次/s，写的速度是81000次/s。
• 支持数据持久化，支持AOF和RDB两种持久化方式。
• 支持事务， Redis的所有操作都是原子性的，意思就是要么成功执行要么失败完全不执行。单个操作是原子性的。多个操作也支持事务，即原子性，通过MULTI和EXEC指令包起来。
• 数据结构丰富，除了支持string类型的value外，还支持hash、set、zset、list等数据结构。
• 支持主从复制，主机会自动将数据同步到从机，可以进行读写分离。
• 丰富的特性 – Redis还支持 publish/subscribe， 通知， key 过期等特性。

缺点：

• 数据库容量受到物理内存的限制，不能用作海量数据的高性能读写，因此Redis适合的场景主要局限在较小数据量的高性能操作和运算上。
• 主机宕机，宕机前有部分数据未能及时同步到从机，切换IP后还会引入数据不一致的问题，降低了系统的可用性。

Redis的常用场景有哪些?

1、缓存

缓存现在几乎是所有中大型网站都在用的必杀技，合理的利用缓存不仅能够提升网站访问速度，还能大大降低数据库的压力。Redis提供了键过期功能，也提供了灵活的键淘汰策略，所以，现在Redis用在缓存的场合非常多。

2、排行榜

很多网站都有排行榜应用的，如京东的月度销量榜单、商品按时间的上新排行榜等。Redis提供的有序集合数据类构能实现各种复杂的排行榜应用。

写在最后

在结束之际，我想重申的是，学习并非如攀登险峻高峰，而是如滴水穿石般的持久累积。尤其当我们步入工作岗位之后，持之以恒的学习变得愈发不易，如同在茫茫大海中独自划舟，稍有松懈便可能被巨浪吞噬。然而，对于我们程序员而言，学习是生存之本，是我们在激烈市场竞争中立于不败之地的关键。一旦停止学习，我们便如同逆水行舟，不进则退，终将被时代的洪流所淘汰。因此，不断汲取新知识，不仅是对自己的提升，更是对自己的一份珍贵投资。让我们不断磨砺自己，与时代共同进步，书写属于我们的辉煌篇章。

需要完整版PDF学习资源私我

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以点击这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！