数据库面经-Mysql-必知必会

最新推荐文章于 2024-04-23 23:43:39 发布

东萌潭海，西晓轮月

最新推荐文章于 2024-04-23 23:43:39 发布

阅读量406

点赞数

分类专栏： C++面经文章标签： c++

本文链接：https://blog.csdn.net/weixin_42605958/article/details/116461534

版权

C++面经专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文深入探讨了数据库管理系统（DBMS）的基本概念，包括SQL查询语言、多版本并发控制（MVCC）机制。重点分析了数据库索引的原理、优缺点以及添加索引的原则。此外，详细阐述了不同隔离级别的事务处理，以及InnoDB和MyISAM存储引擎的对比。同时，解释了数据库引擎中的锁粒度和死锁处理，以及InnoDB的MVCC机制。最后，提到了SQL查询优化和数据库查询执行步骤，为数据库性能提升提供了指导。

摘要由CSDN通过智能技术生成

基本名词

DBMS 数据库管理系统
SQL 结构化查询语言
MVCC 多版本并发控制机制

Q1: 数据库索引 & 多加索引一定会好么

定义：

数据库索引是为了增加查询速度而对表字段附加的一种标识，是对数据库表中一列或多列的值进行排序的一种结构，不同于在表中搜索所有的行,使用索引可快速访问数据库表中的特定信息，能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

原理：

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

优点：

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

2、添加索引原则

简写：索引并非是虚无缥缈的，是实实在在的一种数据结构（B+树/hash桶）要占内存、维护它要系统开销，一般的插入删除都要进行结构的调整，这要消耗时间，所以索引太多反而拖慢查找时间。有时候，见数据量不多时，建立索引还不如全表查询。索引加快了检索的速度，但是插入删除修改都需要DBMS动态更新内部索引结构，要耗费开销。

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

底层原理（实现方法，数据结构）：

一般分为B+树索引和哈希索引。
B+树索引：在B-tree上改进得到，其非叶子节点均为key值，叶子节点是key-data键值对。叶子节点前后相连且有序。
哈希索引：通过对key进行hash(crc/MD5/sha1/sha256…)而将记录存储在不同的bucket中，可以做到常数时间的查找，但要注意哈希冲突的避免（链表法、线性探测、二次探测、公共溢出区的方法）。其中MD5 128位，和sha1/256码都较长不太适合作为hash函数。默认无序。
为什么有了B+树索引还要hash索引？
B+树默认有序，hash默认无序，所以哈希索引无法用于排序；
哈希索引O(1)在速度上毋庸置疑要快于B+树近似O(logn);
哈希索引只能进行等值查询（因为他要计算hash(key)再去匹配）而B+树索引可以进行等值、部分前缀、范围查询；
底层实现结构不同：B+树是非线性结构，hash桶是线性结构。
对于某些场景如热点页/活跃查询页，需要借助哈希索引来实现快速查询。

Q2: 数据库事务

1. 定义：

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。可以用来维护数据库的完整性。
事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。
事务是数据库运行中的逻辑工作单位，是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。事务是DBMS中最基础的单位，事务不可分割。由DBMS中的事务管理子系统负责事务的处理。

2. ACID

一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。

1）原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功提交，要么全部失败回滚，不可分割。因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。而回滚可以通过日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作。

2）一致性（Consistency）

数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的.
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

3）隔离性（Isolation）

事务之间彼此互不影响，一个事务在提交之前，对其他事务是不可见的。
隔离性是当多个用户并发访问数据库时，数据库为每一个用户开启的事务，事务之间是隔离的，一个事务不应该影响其它事务运行效果。每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。
即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

4）持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

3. 不同的隔离级别：

Read Uncommitted（读取未提交内容）：最低的隔离级别，什么都不需要做，一个事务在提交之前，对其他事务是可见的，即其他事务可以读取该事务未提交的数据。所有的并发事务问题都会发生，存在脏读问题。
Read Committed（读取提交内容）：事务在提交之前，对其它事务是不可见的。只有在事务提交后，其更新结果才会被其他事务看见。可以解决脏读问题，但存在“不可重复读”问题。
Repeated Read（可重复读）：在一个事务中，多次读取统一数据的结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读问题。存在“幻读”（在事务两次查询间隙，有其他事务又插入或删除了新的记录）。
Serialization（可串行化）：事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题。

事务隔离级别	脏读	不可重复读	幻读
读取未提交（read-uncommitted）	是	是	是
读取提交内容（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
可串行化(serializable)	否	否	否

4. 数据库事务隔离

同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

Q3: 常见的引擎

MySQL引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。
MySQL存储引擎主要有： MyIsam、InnoDB、Memory、Blackhole、CSV、Performance_Schema、Archive、Federated、Mrg_Myisam。
但是最常用的是InnoDB和Mylsam。

1. InnoDB：

最为通用/推荐的引擎，支持可靠的事务处理、行级锁、外键、甚至间隙锁（避免幻读）、支持热备份，MVCC，在并发上占优势，系统资源占用多。不支持全文本搜索（5.6之后也支持了）
InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。 Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这类型的文章。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

适用场景：

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

索引结构：

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

2. MyISAM：

是一个性能极高的引擎，是默认的存储引擎(后来取消了)，支持全文本搜索，不支持事务处理，不支持行级锁和外键，支持表锁，某些场景性能很好：占用存储上优，查询速度上完胜（大概是InnoDB的3倍），系统资源占用少。
但是当INSERT或UPDATE数据时即写操作时需要锁定整个表，效率便会低一些。MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。

适用场景：

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

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

索引结构：

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

3. MEMORY

在功能上等同于MyISAM，但由于数据存储在内存中（不是磁盘），速度很快（特别适合于临时表）

InnoDB和MyISAM的区别

事务：InnoDB支持事务处理等高级处理, MyISAM不支持；
性能：MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快。
锁：InnoDB支持行级锁、表锁；MyISAM只支持表锁；行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
WVCC:：InnoDB支持MVCC，MyISAM不支持；
全文搜索：InnoDB不支持全文索引，MyISAM支持；(5.6之后innodb也支持全文索引)
外键：InnoDB支持外键，MyISAM不支持外键；
索引结构：InnoDB和MyISAM都支持B+树索引，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的时候就相对痛苦了。

大部分情况下，InnoDB都是正确的选择

Q4: inner join和left join

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

Q5: 数据库的三大范式

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

Q6: 锁及粒度

共享锁/读锁：互不阻塞，优先级低
排他锁/写锁：阻塞其他锁，优先级高，即确保在一个事务写入时不受其他事务的影响。
锁粒度：锁定的数据量越少（粒度越小），并发程度越高，但相应的加锁、检测锁、释放锁用的系统开销也随之增大。
锁策略：锁开销与数据安全性之间的平衡
1. 表锁：锁住整张表，读锁互不阻塞，写锁阻塞其他所有读写锁（同一张表）。开销最小。
2. 行级锁：对每一行数据（记录）加锁，开销大，并发程度高。

Q7: InnoDB对死锁的处理

此处死锁与OS（操作系统）死锁类似，多个事务互相持有对方所有要申请资源的锁不释放，造成环路死锁。MySQL InnoDB引擎检测到死锁循环依赖后，回滚持有最少行级锁的事务。

Q8: InnoDB MVCC

MVCC是一种多版本并发控制机制，是MySQL的InnoDB存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别，是为了避免加锁而实现的。一般的实现方法是通过保存数据在某个时间点的快照来实现该机制，其在每行记录后面保存两个隐藏的列，分别保存这个行的事务创建时间、过期时间(实际上是创建版本号和删除版本号)，然后Innodb的MVCC使用到的快照存储在Undo日志中，该日志通过回滚指针把一个数据行所有快照连接起来。
这样一来，INSERT 时加上开始版本号，UPDATE/DELETE时加上过期版本号，这样一来在SELETE时，就只访问开始系统版本号小于当前的事务的版本号、过期时间要么未定义要么在当前版本号之后的记录，这样就可以保证：访问的记录是在本事务开始前就存在而且在本事务期间没有过期（被删除或被修改过的）。可以避免脏读、不可重复读、幻读的问题。

Q9: SQL优化

通过建立索引对查询进行优化，在经常性的检索列上，建立必要索引，以加快搜索速率，避免全表扫描（索引覆盖扫描）
多次查询同样的数据，可以考虑缓存该组数据；
审视select * form tables, 你需要所有列数据吗？
切分查询（大查询切分成为小查询，避免一次性锁住大量数据）
分解关联查询（单表查询，结果在应用程序中进行关联，可以减少处理过程中的锁争用）
尽量先做单表查询；

Q10: MySQL查询的步骤

客户端发送查询到服务器；
服务器检查查询缓存query ***（大小写敏感的哈希查找，常数时间）。如果命中，返回缓存中的结果，否则下一步；
解析语句，生成执行计划；（SQL解析，预处理，优化器生成执行计划）；
根据执行计划，根据存储引擎的不同调用API，执行查询（一棵指令树）；
结果返回客户端。

Q11: profile 的作用和用法

用于保存SQL语句执行状态，需要手动开启，才可以查看。
set profiling = 1; 开启
show profiles; 显示SQL查询的profiles概况
show profile all for query X; 查看第X条语句的所有执行情况。
show profile cpu, block io, memory for query X; 查看部分profile信息。

Q12: 如何删除表中的一行数据

首先如果有主键，则直接利用主键确定某一行就可以了。
DELETE FROM 表名 WHERE 主键 = ‘具体值’
其次确定能够唯一确定你那一行数据的字段或字段组合是哪些，
DELETE FROM 表名 WHERE 字段1 = ‘’ and 字段2 = ‘’ and …
字段1，…为能够唯一确定某一行数据的字段组合，‘’中填写你要删除的字段具体值就可以了

补充：数据库底层原理

数据库采用B树（B-tree）格式储存数据
B树的特点也有三个。

一个节点可以容纳多个值。比如上图中，最多的一个节点容纳了4个值。
除非数据已经填满，否则不会增加新的层。也就是说，B树追求"层"越少越好。
子节点中的值，与父节点中的值，有严格的大小对应关系。一般来说，如果父节点有a个值，那么就有a+1个子节点。

这种数据结构，非常有利于减少读取硬盘的次数。假定一个节点可以容纳100个值，那么3层的B树可以容纳100万个数据，如果换成二叉查找树，则需要20层！假定操作系统一次读取一个节点，并且根节点保留在内存中，那么B树在100万个数据中查找目标值，只需要读取两次硬盘。

为什么不用二叉树呢？

因为我们要考虑磁盘IO的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数，对于树来说，IO次数就是树的高度，而“矮胖”就是b树的特征之一，它的每个节点最多包含m个孩子，m称为b树的阶，m的大小取决于磁盘页的大小。
B树这种数据结构，非常有利于减少读取硬盘的次数。假定一个节点可以容纳100个值，那么3层的B树可以容纳100万个数据，如果换成二叉查找树，则需要20层！假定操作系统一次读取一个节点，并且根节点保留在内存中，那么B树在100万个数据中查找目标值，只需要读取两次硬盘。

索引

数据库以B树格式储存，只解决了按照"主键"查找数据的问题。如果想查找其他字段，就需要建立索引（index）。
所谓索引，就是以某个字段为关键字的B树文件。假定有一张"雇员表"，包含了员工号（主键）和姓名两个字段。可以对姓名建立索引文件，该文件以B树格式对姓名进行储存，每个姓名后面是其在数据库中的位置（即第几条记录）。查找姓名的时候，先从索引中找到对应第几条记录，然后再从表格中读取。
这种索引查找方法，叫做"索引顺序存取方法"（Indexed Sequential Access Method），缩写为ISAM。它已经有多种实现（比如C-ISAM库和D-ISAM库），只要使用这些代码库，就能自己写一个最简单的数据库。