数据库面试题_数据库的数据一般不会存在-CSDN博客

本文链接：https://blog.csdn.net/AXIMI/article/details/106634008

文章目录

基础知识

mysql有哪些基本的数据类型

整型：tinyint（1字节），smallint（2字节），mediumint（3字节），int（4字节）
浮点数：float(4字节)，double（8字节），decimal（16字节）
float和double使用二进制存储数据，decimal使用字符串存储数字
decimal可以100%精确表达一个数字，可以用在金融服务行业

数据库的三大范式

第一范式：列的原子性，每个列都不可以再拆分。

例如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。
在这里插入图片描述

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分（即存在联合主键时，要完全依赖于联合主键）。

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

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

1.MySQL中InnoDB和MyISAM与的区别

InnoDB支持事务，MyISAM不支持
InnoDB支持外键，MyISAM不支持
InnoDB支持行级锁和表级锁（默认行级锁），MyISAM支持表级锁
MyISAM的索引都是非聚簇索引，InnoDB的主键索引是聚簇索引

2.MySQL中varchar与char的区别

char长度是固定的，处理速度快，比较费空间
varchar长度是不固定的，处理速度慢，比较节省空间

char(M)每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足。在varchar(M)每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）。

myisam 存储引擎建议使用固定长度，数据列代替可变长度的数据列。
memory存储引擎目前都使用固定数据行存储，因此无论使用char varchar列都没关系，
innodb 存储引擎建意使用varchar 类型

MySQL中的主键有什么作用

主键可以唯一地标识一行
主键是可以被外键有效引用的对象

MySQL外键有什么作用

保持数据的一致性

主表的外键可以有多个，子表必须把外键当做主键。
例如现在有两张表，一张学生信息表，一张学生成绩表；把学生信息表中的学号字段设为外键，连接成绩表中的学号字段，则成绩表中的学号字段要设为主键。

MySQL中的datetime和timestamp有什么区别

datetime时间范围是1001-9999，timestamp时间范围是1970-2038
datetime存储的时间与时区无关，timestamp存储的时间与时区有关，修改时区时timestamp的时间也会改变（例如存进数据库的时间是北京时间下午一点，将时区改为东九区后时间会变为下午两点）
datetime占8字节，timestamp占4字节
datetime默认值是null，timestamp默认值是当前时间

什么是多表查询

就是一次select多张表的数据
union all

delete，drop，truncate区别

drop直接删掉整张表；

truncate删除的是表中的数据，再插入数据时自增长的数据id又重新从1开始；

delete的删除可以在后面添加where条件

truncate、drop是DDL语句，执行后就提交，不能回滚。
delete可以回滚。

事务

什么是事务，使用场景是什么

是一个不可分割的数据库操作序列
执行的结果是将数据库从一种一致性状态变到另一种一致性状态

使用场景：买票、转账

事务的四大特性（ACID）是什么

A（Atomicity）—原子性
事务里面的东西要么全部都做，要么全不不做

C（Consistency ）—一致性
做完一个事务后，会将数据库从一个一致性状态转换到另一个一致性状态。
主要指数据库从一个正确的状态转换到另外一个正确的状态，操作过程是合法的，不会违反我们给数据库定义的一些规则。
例如银行的数据库，客户的账户余额不能是负值；int型字段不能赋予一个字符串等。

I（ Isolation）—隔离性
各个事务事件之间相互隔离，不互相影响

D（Durability）—持久性
持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

事务实现原理：redo log和undo log

事务是通过redo log、undo log和锁来实现的。
事务的ACD是通过redo log和undo log来实现的（重做日志和回滚日志）。隔离性是通过锁来实现的。

Undo Log
undo log保存了数据修改之前的值，方便数据库回滚的时候做undo操作。可以实现事务的原子性。

undo log记录了sql执行的相关信息，需要回滚的时候回执行sql语句相反的操作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。

Redo Log
redo log记录了数据库对buffer pool中数据的修改操作，用于crash recovery。
保证了MySQL宕机也不会影响持久性，实现事务的持久性。

redo log存在的背景：
数据库的数据一般是存在磁盘的，但磁盘IO很慢，所以一般会在缓存池（buffer pool）里面进行读写，再定期将缓存池中的数据刷新到磁盘（这个过程称为刷脏）。读写的过程中如果mysql宕机就会导致数据丢失，失去"持久性"。

为了解决这个问题，修改数据时会先在redo log中记录下操作，再修改缓存池中的数据，事务提交的时候会持久化redo log的数据。如果mysql宕机，重启时可以读取redo log中的数据，对数据库进行恢复。

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：

（1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。

（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

使用事务的场景有哪些

什么是脏读、幻读、不可重复读

脏读（读取了未提交的数据）：一个事务已经更新一份数据，另外一个事务在读取同一份数据，如果第一个事务回滚了，那第二个事务读到的数据就是错误的。

不可重复读（前后读取的结果不一样）：一个事务两次查询的结果不一样。

解决方式：使用行级锁，一个事务修改一条数据的时候锁定这行数据，修改完再解锁。

幻读（前后多次读取，数据总量不一致）：一个事务读了几行数据，另外一个事务插入了几行数据，那第一个事务在后面的查询过程中会发现有几列数据是它之前没查到的。

解决方式：使用表级锁，添加或者删除完数据后再解锁。

幻读和不可重复读有什么区别：

幻读是读取了其他事务新增或删除的数据，针对insert和delete操作
不可重复读是读取了其他事务修改的数据，针对update操作

数据库的隔离级别

在这里插入图片描述
数据库事务的隔离级别有4个，由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable。

Read uncommitted：
一个事务可以读到其他事务还没有提交的数据
会造成脏读

Read committed：
一个事务在执行过程中，读到其他事务已经提交的数据，造成前后的查询数据不一致的问题。
解决了脏读的问题，会造成不可重复读。

“不可重复"指的是"查询的结果不可重复”，事务希望相同的两次查询，查询结果应该是重复的，但是结果查下来是不可重复的。

Repeatable read：
这个等级下，事务可以实现重复读。即一个事务在执行的过程，查询到的数据内容是保持一致的。
这个等级解决了不可重复读的问题，但是会出现幻读的问题。

Serializable：
是最高的事务隔离级别，在该级别下，事务之间完全不干扰，但是代价很高，性能很低。

这个等级解决了脏读、不可重复读、幻读的问题。

隔离级别	含义	会造成的问题	默认隔离级别数据库
Read uncommitted	一个事务可以读其他事务还没有提交的数据	脏读
Read committed	一个事务可以读其他事务提交后的数据	会造成不可重复读	Oracle，SQL Server
Repeatable read	事务可以实现可重复读	会造成幻读	MySQL
Serializable	事务之间完全不会相互影响

为什么mysql的默认隔离级别是RR？

因为mysql之前的版本binlog只支持statment的格式，也就是binlog会记录对数据库执行的sql语句。因为binlog会在事务提交的时候才记录sql语句，所以binlog记录下来的sql语句可能不是sql语句真实的执行顺序。

实现RR的方式–MVCC

MVCC（多版本并发控制），优势是读不加锁，读写不冲突

索引

什么是索引，优缺点是什么

索引：目录，文件查找的结构

优点：

加大检索速度

缺点：

创建、维护索引会花时间，降低了增改删的效率
会占用物理空间

什么地方会使用到索引

where添加条件时：
在这里插入图片描述
上图中，根据id查询记录，因为id字段仅建立了主键索引，因此此SQL执行可选的索引只有主键索引，如果有多个，最终会选一个较优的作为检索的依据

-- 增加一个没有建立索引的字段
alter table innodb1 add sex char(1);
-- 按sex检索时可选的索引为null
EXPLAIN SELECT * from innodb1 where sex='男';

在这里插入图片描述
order by
对某个字段进行排序的时候，如果该字段没有索引的话，会对数据进行外部排序（将数据从硬盘读取到内存使用内部排序，最后合并排序结果）。这个过程会比较耗时，首要要从存储设备里面读取数据，还要对数据进行排序。

join
对join语句匹配关系（on）涉及的字段建立索引能够提高效率

索引有哪些类型

可以分为主键索引，唯一索引，普通索引，全文索引

主键索引：
主键约束，唯一标识数据库中的每条记录
必须包含唯一的值
不能包含NULL值
每个表都必须有且仅有一个主键

唯一索引：
唯一索引的目的不是为了提高访问速度，只是为了避免数据出现重复

主键索引和唯一索引的区别

主键索引不允许有null值，唯一索引可以有；
一个表只能有一个主键，可以有多个唯一索引；

索引有什么数据结构

有哈希表，有序数组，N叉树；

InnoDB里面是B+树。

为什么使用B+树：

B+ 树节点只有索引的功能，节点比较小，空间利用率更高，单次查询磁盘访问次数比较少，查询效率高
所有的数据都包含在叶子节点里面，非叶子节点只做索引的功能，查询性能比较稳定
叶子节点都用指针连在一起，形成有序链表，范围查找比较方便

B和B+树的对比：

B树的节点里面同时存键和值，B+树只有叶子节点存放数据（比如如果是非主键索引里面的节点存放的数据就是主键的值）。

对于B树来说，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

对于B+树来说，叶子节点只保存索引，所以一次读取可以从页数据里面获得更多的键，可以更快地缩小查找范围；同时因为数据只在叶子节点，所以查询性能会比较稳定；而且所有的叶子节点都通过链表连接，所以范围查询和排序的时候有很大的优势。

Hash表和B+树的对比：
Hash索引等值查询的时候速度比较快

B+树所有的数值内容都保存在叶子节点，查询性能比较稳定；叶子节点通过指针链接在一起，范围查询、排序的时候很方便；

因为Hash函数的不可预测性，不支持多列索引的最左前缀匹配
Hash肯定需要回表，B+树在主键索引，覆盖索引的时候可以不用回表

创建索引有哪些原则

查询频繁的字段适合建立索引；经常排序、分组的字段适合建立索引
更新频繁的字段不适合建立索引；
尽量考虑创建联合索引（可以过滤筛选的数据更多）

什么是覆盖索引

要查的列就是索引里面的列。在覆盖索引的条件下，就不用查主键索引，直接从非主键索引中就能查到记录。避免回表耗费时间。

例如创建了索引(field1, field2, field3)，然后select field3 from table_name where field1=2, field2=3 这个查询就不会行使回表操作，直接返回结果。

为什么要使用联合索引，而不是创建单独的两个索引

因为MySQL一次查询只能使用一个索引，而不是使用两条索引起到叠加的效果
联合索引可以实现索引覆盖
可以过滤筛选的数据更多

删除一张表中的大量数据

如果一张表里面有很多数据，我需要删除的量在百万以上，应该怎么删除这些数据？

应该先删除索引，再删除需要删除的数据，再把索引加回来。
因为索引文件是单独存在的，增删改的操作都会产生对索引文件的操作，这些操作可能会对磁盘有额外的IO，降低增删改的效率。删除数据的速率和创建的索引数量是成正比的。

所以如果需要删除一张表里面大量的数据的时候，应该先删除里面的索引，再删除数据，最后再把索引加回来。

什么是聚簇索引和非聚簇索引

聚簇索引就是数据行记录和索引放到一起，InnoDB里面只有主键索引是聚簇索引。使用聚簇索引来查数据的时候，可以不用回表。

一张表只能有一个聚簇索引，因为一张表的数据只有一种存储方式。

非聚簇索引就是索引的结果是主键的值，需要做回表的操作。
非聚簇索引也叫二级索引、辅助索引

非聚簇索引一定会回表查询吗

不一定，可以有覆盖索引

什么是前缀索引

使用字段的前几个字符建立索引。
用于密码匹配、文字匹配等场景。
（建立索引的时候，默认是使用字段的全部内容）

前缀索引的创建难度在于到底选择多长的前缀以及字段的选取方式。

可以利用select count(*)/count(distinct left(password,prefixLen));，通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录）

什么是最左前缀匹配原则

最左前缀匹配原则用于多列索引的情况，如果索引条件没有覆盖索引的最左前缀，那就不会用到这个索引。

例如创建了（c1, c2, c3）的索引，那（c2, c3, c2+c3）的查询条件是用不了索引的。

mysql会在条件里面一直向右匹配，匹配到范围查询为止。如果将精确查询放到范围查询的后面，该精确查询的索引就用不到了。

什么是联合索引

使用多个字段创建的索引是联合索引，联合索引中，如果想要命中索引要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

因为索引会按照创建索引时的字段顺序来对数据进行排序。

一般情况下，将查询需求比较强的字段放在前面。

是不是加了索引就一定会加快查询速度

不一定，如果查询的数据范围很大，例如最终的查询结果可能占了整体数据的80，那还是不加索引比较好，不然加载索引再回表的时间也很长

B+树的时间复杂度是多少

B+树，假设数据量为n，一个节点假设它有m个子节点，树高就是log(m)n，每一层平均遍历m/2个节点，时间复杂度是(m/2)*log(m)n

唯一索引和普通索引应该怎么选？

尽量选择普通索引，修改数据的时候，普通索引可以通过change buffer实现快速的数据修改；
但是因为唯一索引需要确保数据的唯一性，所以如果当前buffer pool中没有该条记录对应的索引，还要进行磁盘IO来对数据进行修改。

MyISAM和InnoDB的索引有什么区别？

MyISAM的索引和行记录是分开的，主键索引和普通索引没有本质区别；
InnoDB的主键索引是聚簇索引，而普通索引的数据块保存的是主键值。

数据库逻辑架构与日志

数据库逻辑架构

在这里插入图片描述

MySQL从概念上可以分为四层。

顶层：接入层，不同语言的客户端通过mysql的协议与mysql服务器进行连接通信，接入层进行权限验证、连接池管理、线程管理等。
mysql服务层：包括sql解析器、sql优化器、数据缓冲、缓存等
mysql存储引擎：mysql中存储引擎是基于表的
系统文件层：保存数据、索引、日志等。

描述一条更新语句的执行过程

update name = "new_name" from table_name where name = "old_name"

首先执行器先找通过存储引擎找到名字是old_name的这一行。如果name这个字段有索引会通过索引来找；
如果这一行的数据在buffer pool里，可以直接修改buffer pool中的数据，否则从磁盘中加载该数据页到buffer pool再修改；
修改之后将更新操作同步到redo log；
执行器生成操作的bin log，并把bin log写入磁盘；
事务提交的时候，会对buffer pool中的数据刷脏（数据持久化）

redo log和binlog 区别

	redo log	binlog
	存储引擎级别	server级别
日志类型	物理日志，记录数据页更新的内容（而不是某一行数据修改成什么样子）	逻辑日志，记录sql语句原始逻辑
写方式	循环写，日志空间大小固定	追加写，一份文件写到一定大小后会换下一个文件写
功能	可以用作宕机后的crash recovery	主从复制搭建：从库利用主库上的binlog进行重播，实现主从同步

binlog的格式
binlog的格式有三种：
statement:记录的是修改SQL语句
row：记录的是每行实际数据的变更
mixed：statement和row模式的混合

mysql中有哪些日志文件？

redo log和undo log。

还有err log，Genaral Query Log、 Bin Log 和 Slow Query Log。

err log
记录err和warning信息

General Query Log
记录了查询、修改、更新等的sql语句，一般调试的时候开启。

bin log
记录了一些建表、数据改动的操作，一般用于主从备份

slow query log
慢查询日志，记录了执行时间比较长的sql语句。

调优

除了加索引，还有哪些优化的方式？

优化sql语句：
满足最左前缀匹配；
不要让列参与计算；
注意避免出现隐式类型转换：
select * from t where id = 1如果id是字符类型的，1是数字类型的，你用explain会发现走了全表扫描，根本用不上索引，为啥呢？因为MySQL底层会对你的比较进行转换，相当于加了 CAST( id AS signed int) 这样的一个函数，上面说过函数会导致走不上索引。
设计好的表结构类型
根据范式来设计表；
使用能存下数据的最小数据类型
字段的值设置不要存在null，避免索引失效

什么是慢查询？怎么使用慢查询进行优化？

在日志中记录运行比较慢的SQL语句，这个功能需要在my.cnf配置才能使用
在这里插入图片描述
第一个参数是查询超=超时条件，这里是10秒
第二个参数是日志存放的地址

使用慢查询进行优化
0. 首先设置SQL_NO_CACHE，看哪些SQL运行地比较慢
1.

锁

这篇文档

视图

视图的作用

视图就是一个隐藏的sql查询，我们可以在这个sql查询建立起来的表上做更进一步的查询操作。

作用：简化sql语句，避免直接操作数据库的原始数据。

视图应用场景？

数据访问权限管理：比如我作为实习生，有的数据不方便给我看，就创建一个视图给我提供正常字段的访问权限。

修改视图中的数据会影响原数据吗？

（不确定）简单的单表视图数据可修改，视图来自多个表的话可能不可修改

视图的优缺点

优点：
简化sql查询，权限管理

缺点：
如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询也需要花费一定的时间。

其他

buffer pool和change buffer

buffer pool是加快读的过程，加载数据的时候先从磁盘加载到缓冲池，再读
change buffer是方便写的过程，目的是为了减少写操作的磁盘IO。如果需要修改的数据当前不在buffer pool，就先将修改保存到change buffer，未来读取到对应数据页的时候再merge到原数据里面去。

什么时候适合写缓冲？
写多读少
数据库中大部分是非唯一索引

什么时候会触发change buffer的merge操作？
读操作需要访问到这个数据页时
系统后台线程定期merge
数据库正常关闭的时候

mongodb

关系型数据库和非关系型数据库之间的对比

关系型数据库：
非关系型数据库：not only sql，数据之间的关联比较少，更容易分散存储

为什么mongodb使用B树

因为mongodb的文档存储结构是json，mongodb是聚合型的数据库，一般就想要通过key得到value，而B树的结构就是每个节点都有Data域，比较符合mongodb的数据库特性。

mongodb的存储引擎

wiredtiger：
支持document级别的锁
因此多个客户端可以同时更新一个colleciton中的不同document
支持MVCC

mmap：
支持到collection级别的锁

mongodb中的索引

mongodb中有单字段索引、复合索引、多key索引、文本索引、唯一索引、TTL索引、稀疏索引。
普通索引和稀疏索引的区别是，普通索引对于不在该字段的documents会假定该documents的索引字段为null
而稀疏索引则会直接跳过该document，效率更高。

mongodb中没有聚簇索引的概念，数据和索引是单独分离的。

mongodb怎么实现事务

mongodb目前只支持单文档事务：文档

原子性：支持单文档正删改的原子性
一致性和持久性：journal和oplog
隔离性：$Isolated操作符，保证与其他的写操作错开

mongodb的journal和oplog

journal是存储引擎层面的概念，用于保证数据库的持久性。比如wiredtiger写入数据的时候，不会马上持久化数据，而是每分钟做一次checkpoint，来持久化数据。如果不配置journal，这一分钟期间出现宕机，数据库的数据只能恢复到上一次checkpoint，而最近一分钟内修改的数据就会丢失。
开启journal之后，会把增删改的操作记录到journal，如果出现宕机，首先数据会保存到上一次checkpoint，然后数据库会重放journal操作日志来恢复数据。

oplog是capped collection，是主从复制层面上的用法，一般是通过oplog来实现节点数据同步。首先客户端先把数据写到主节点，主节点写入数据后会记录一条oplog，副节点可以从主节点或者其他副节点拉取oplog，重放，来确保自己当前的数据和其他节点保持同步。

参考