数据库面试题_数据库索引面试题-CSDN博客

本文链接：https://blog.csdn.net/weixin_44518077/article/details/117362300

数据库常见面试题整理

数据库
Redis
其他
- - 1、使用的mysql版本
  - 2、常用命令

数据库

链接: MySQL数据库面试题（2020最新版）.

1、谈谈数据库索引，多加索引一定好吗？

参考链接: 腾讯面试官问我：MySQL索引原理是什么？.
回答：
索引是对数据库表中一列或多列的值进行排序的一种数据结构（如mysql使用B+树或hash索引），使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。

优点： (看下即可)
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

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

可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

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

缺点：（要记）
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

添加索引原则

在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
定义为text、image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

什么情况下会索引失效

答：

2、说说数据库事务的四个特性、nosql的CAP+base

事务（Transaction）是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。事务是DBMS中最基础的单位，事务不可分割。
事务具有4个基本特征，分别是：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Duration），简称ACID。
对比nosql的CAP原理；
C：Consistency（强一致性）
A：Availability（可用性）
P：Partition tolerance（分区容错性）

1）原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。而回滚可以通过日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作。
2）一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
3)隔离性：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
4)持久性：一旦事务提交，则其所做的修改将会永远保存到数据库中，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

3、事务并发问题、事务的4个隔离级别

参考链接: 理解事务的4种隔离级别.
在这里插入图片描述

据库事务的隔离级别有4种，由低到高分别为: 读未提交Read uncommitted 、读提交Read committed 、可重复读Repeatable read 、串行化Serializable 。而且，在事务的并发操作中可能会出现脏读，不可重复读，幻读。

1、读未提交：
顾名思义，就是一个事务可以读取另一个未提交事务的数据。此时该事务由于未提交可以回滚，由此导致脏读。

2、读提交：(允许修改update，但修改结果需提交后才能读取)
顾名思义，就是一个事务要等另一个事务提交后才能读取数据。注意在读这一事务开启时是可以进行修改update操作的，只是当再次读取时如果发生update操作，需要等update事务提交以后才能进行读操作，以此解决脏读问题，但由于允许update修改操作，会导致两次读取的结果不同，导致不可重复读问题！

3、重复读：（不允许修改update，但允许新增插入insert）
就是在开始读取数据（事务开启）时，不再允许修改操作！可以解决不可重复读问题
分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

4、串行化Serializable
是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

值得一提的是：大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。

4、介绍一下mysql的MVCC机制（即如何实现隔离级别）（具体会涉及锁？）【不熟】

MVCC是一种多版本并发控制机制，是MySQL的InnoDB存储引擎实现隔离级别的一种具体方式，用于实现读提交和可重复读这两种隔离级别。MVCC是通过保存数据在某个时间点的快照来实现该机制，其在每行记录后面保存两个隐藏的列，分别保存这个行的创建版本号和删除版本号，然后Innodb的MVCC使用到的快照存储在Undo日志中，该日志通过回滚指针把一个数据行所有快照连接起来。

5、行锁、表锁、乐观锁、悲观锁（感觉应该是放在隔离级别那一块后面）

6、Mysql最左匹配原则、联合索引

链接: 最左匹配（原理和例子很好）.
链接: 最左匹配（可看下代码）.
背景知识：
mysql中可以使用explain关键字来查看sql语句的执行计划。
最左前缀原则主要使用在联合索引中

理解：索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

假如建立联合索引（a,b,c）

全值匹配查询时：搜索顺序不影响!
匹配左边的列时：从最左边开始且连续时会用到索引，否则用的是全表扫描!
匹配列前缀：注意字符型的比较，如果a是字符类型，那么前缀匹配用的是索引，后缀和中缀只能全表扫描了！使用like匹配时
匹配范围值：多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到B+树索引。因为此时最左边是有序的，后面的不一定有序！
精确匹配某一列并范围匹配另外一列：a=1的情况下b是有序的，进行范围查找走的是联合索引
排序：文件排序非常慢，但如果order子句用到了索引列，就有可能省去文件排序的步骤，因为b+树索引本身就是按照上述规则排序的！

7、索引优化问题

链接: MySQL性能优化的21个最佳实践.

8、说一说数据库的三大范式【不熟】

三大范式说的不太清楚，不容易让新手理解。 一范式就是属性不可分割，二范式就是要有主键,其他字段都依赖于主键，三范式就是要消除传递依赖,消除冗余,就是各种信息只在一个地方存储,不出现在多张表中

第一范式：当关系模式R的所有属性都不能再分解为更基本的数据单位时，称R是满足第一范式，即属性不可分
第二范式：如果关系模式R满足第一范式，并且R的所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式

第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，即非主属性不传递依赖于键码

9、请你说一下MySQL引擎和区别【不熟】

链接: 牛客面经.
1)、MySQL引擎：
数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

MySQL存储引擎主要有： MyIsam、InnoDB、Memory、Blackhole、CSV、Performance_Schema、Archive、Federated、Mrg_Myisam。

2)、InnoDB
InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。是MySQL默认的引擎

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。
该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。
但是该引擎不支持FULLTEXT类型的索引(5.6以后支持了？)，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。
当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

适用场景：
经常更新的表，适合处理多重并发的更新请求。
支持事务：
可以从灾难中恢复（通过bin-log日志等）。
外键约束。只有他支持外键。
支持自动增加列属性auto_increment。

索引结构：
InnoDB也是B+Tree索引结构。Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。

InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。

3)、Mylsam
它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT或UPDATE数据时即写操作需要锁定整个表，效率便会低一些。MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。

适用场景：
不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。
MyISAM极度强调快速读取操作。MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

缺点： 就是不能在表损坏后主动恢复数据。

索引结构：
MyISAM索引结构：MyISAM索引用的B+ tree来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据。B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。

4)、InnoDB和Mylsam的区别：

事务：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持，提供事务支持已经外部键等高级数据库功能。
性能：MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快。
行数保存：InnoDB 中不保存表的具体行数，也就是说，执行select count() fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时，两种表的操作是一样的。
索引存储：对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。MyISAM支持全文索引（FULLTEXT）、压缩索引，~~InnoDB不支持~~ 。
MyISAM的索引和数据是分开的**，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中 缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
（跳过）服务器数据备份：InnoDB必须导出SQL来备份，LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
（跳过）MyISAM应对错误编码导致的数据恢复速度快。MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
锁的支持：MyISAM只支持表锁。InnoDB支持表锁、行锁行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

10、请问SQL优化方法有哪些

1）通过建立索引对查询进行优化
2）对查询进行优化，应尽量避免全表扫描

11、说一说内部连接inner join和外部连接left join

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

112222、假设让你设计一个***，需要用到什么表，什么字段

测试服务器性能的工具有哪一些（好像没啥用）

链接: MySQL查看SQL语句执行效率和mysql几种性能测试的工具.
链接: mysql 工具_测试 MySQL 性能的几款工具.
~~### 11111、mysql数据库是CP还是AP~~
根据CAP理论,分布式存储系统最多只能满足其中两项.因为P是必须的,因此往往选择就在CP或者AP中.

链接: link.
一致性(Consistency)、可用性(Availability)、分区容错(Partition Tolerance)

Redis

要记住，redis是一个分布式的内存数据库！

为了避免连接时的时间消耗，和java通过JDBC连接mysql一样，java客户端连接redis的Jedis也有相应的线程池，线程池?中有多个和redis内存数据库的连接，需要的时候直接从池中拿出来用即可！通过工具你就只需要会调用API就可以了！

链接: B站尚硅谷Redis学习笔记.

频繁地访问对数据库伤害是最大的，一般在前面挡一层redis缓存，将一些经常性访问的数据放到缓冲区域，减少访问数据库！
在这里插入图片描述

1、 B 和 B+ 区别

2、Redis 的数据结构及底层实现

Redis的五大数据类型

String（字符串）
- string是redis最基本的类型，你可以理解成与Memcached一模一样的类型，一个key对应一个value。
- string类型是二进制安全的。意思是redis的string可以包含任何数据。比如jpg图片或者序列化的对象。
- 一个redis中字符串value最多可以是512M
Hash（哈希，类似java里的Map）
- Redis hash 是一个键值对集合。
- Redis hash是一个string类型的field和value的映射表，hash特别适合用于存储对象。
- 类似Java里面的Map<String,Object>
List（列表）
- Redis 列表是简单的字符串列表，按照插入顺序排序。你可以添加一个元素导列表的头部（左边）或者尾部（右边）。
- 它的底层实际是个链表
Set（集合）
- Redis的Set是string类型的无序集合。它是通过HashTable实现的
Zset(sorted set：有序集合)
- Redis zset 和 set 一样也是string类型元素的集合，且不允许重复的成员。
- 不同的是每个元素都会关联一个double类型的分数。
- redis正是通过分数来为集合中的成员进行从小到大的排序。zset的成员是唯一的，但分数(score)却可以重复。

3、Redis持久化【相当于“本地”处理？】

redis是KV+Cache+persistence

appendonly.aof 和 dump.rdb是可以共存的，启动服务将备份加载到内存是先找的appendonly.aof ；
但是由于appendonly.aof 记录的是写操作，写操作语句如果有问题也会导致出错，此时可以对appendonly.aof 进行修复！
【对比的点】：数据集大小，保存数据完整性，(备份与恢复)速度，备份文件的大小
1）RDB（Redis Datebase）
虽然redis是在内存中的数据库，但也在背后偷偷帮你备份数据到磁盘！

总结：

备份：每隔一段时间将内存的数据压缩写入磁盘
恢复：将快照文件直接读到内存
原理：redis父进程fork一个子进程来持久化，父进程不用进行任何IO操作，性能高！
优点：如果需要大规模数据的恢复，而对数据恢复的完整性不是很敏感，那么RDB方式要比AOF高效！
缺点：最后一次持久化后的数据可能丢失（即数据丢失风险）；RDB需要经常fork子进程来保存数据集到硬盘，当数据集比较大的时候，fork的过程非常耗时！
（下面可跳）

AOF(Append only file)
默认每秒执行一次记录保存，可以在配置中修改！AOF文件大小膨胀问题

总结：

备份：每秒/每修改同步，以日志形式将redis执行过的所有写指令记录下来（读操作不记录，只需追加文件，但不可以改写文件）
恢复：redis重启后根据日志文件内容将写指令从头到尾执行一次完成数据恢复
优点：同步（备份）较快，数据完整性较好
缺点：（文件大小）相同数据集而言aof文件要远大于rdb文件，恢复速度慢于rdb
rewrite：解决aof文件过大，超过阈值进行内容压缩，只保留可以恢复数据的最小指令集！
（下面可跳）

对比 （可跳）

4、redis部分支持事务、乐观锁、悲观锁

之所以说redis部分支持事务，就是因为它不保证原子性！
一个队列中，一次性、顺序性、排他性的执行一系列命令。
在这里插入图片描述
Watch指令，类似乐观锁，事务提交时，如果Key的值已被别的客户端改变，比如某个list已被别的客户端push/pop过了，整个事务队列都不会被执行
【注意在Linux下打开两个dos窗口相当于打开两个进程对redis数据库进行操作！】
悲观锁/乐观锁/CAS(Check And Set)
悲观锁
悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
乐观锁
乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量。
乐观锁策略:提交版本必须大于记录当前版本才能执行更新
CAS

5、主从复制(读写分离)、哨兵模式sentinel

【对比持久化，主从复制需要多个主机，即分布式？】
1) 主从复制 【注意是对于Mysql而言】
链接: B站尚硅谷Redis学习笔记.
在这里插入图片描述

从机用命令连接主机即可作为slaver

SLAVEROF 127.0.0.1 6379 //需要给出对应主机IP和端口

在这里插入图片描述

3) 哨兵模式(sentinel)
一组sentinel能同时监控多个master

反客为主的自动版，能够后台监控主机是否故障，如果故障了根据投票数自动将从库转换为主库

6、关系型数据库与非关系型数据库的对比【看图即可】

以一个电商客户、订单、订购、地址模型来对比关系型数据库与非关系型数据库！
传统关系型数据库如何设计
1、先想一下先需要哪些表（核心就是一对多的关系：客户customer、订单order、订单明细order_detail、收货地址）
2、这一张表都有那些主外键关系
3、思考这样的传统数据库转换为nosql是怎么实现的！
非关系型数据库nosql如何设计
1、合：一个BSon串就能描述整个电商模型
2、分：BSon串可以单独拿出来写作一个BSon串
总结：nosql数据模型比起传统的关系型模型表达上对这种多数据源、多样的处理支持度和灵活性是更高的！
两者对比，问题和难点
在这里插入图片描述