数据库2021题库

数据库2021题库

1 主键，外键，超键，候选键

2 数据库事务的四个特征及含义

3 视图的作用，可以更改吗

视图是虚拟的表；只包含动态检索数据的查询，不包含数据；简化操作，隐藏细节，保护数据；对视图的更新会作用于基表，一般不更新
视图的优点：查询简单化，数据安全性得到保证。逻辑数据独立性。
缺点：性能和修改限制。

4 游标

是系统为用户开设的一个数据缓存去，存放SQL语句的执行结果，每个游标都有一个名字，用户可以通过游标逐一获取记录并赋给主变量。交由主变量进一步处理。

5 drop,delete和truncate

drop 表级的删除；不能回滚；
truncate 清空表；不记录单行删除日志；无法恢复；只能对于TABLE操
作；不能在带FOREIGN KEY约束的表（被引用的表）中使用；
计数从头开始；
delete 配合where删除数据；会记录日志用于回归；会触发触发器；
不减少索引和表的空间；

6 连接的种类

7 数据库范式

8 触发器的作用

是特殊的储存过程，主要通过事件的触发而执行的，它可以强化约束来维护数据的完整性和一致性。

9 存储过程

可以看成是对一系列SQL操作的批处理。
代码封装，代码复用，可以预先编译，因此具有很高的性能。

10 存储过程与触发器的区别

存储过程和触发器都是SQL语句集；触发器不可用CALL调用，而是在用户执行某些语句后自动调用；

11 分表与分区

12 数据库隔离级别

13 MYSQL的两种存储引擎

14 索引的工作原理和其种类

索引的实现通常采用B树或B+树，加快查询速度也消耗更多空间；
索引是数据库管理系统中一个排序的数据结构，协助快速查询，更新数据库表中数据，索引的实现通常使用B树及其变种B+树。

15 索引的优缺点

16 数据库索引的底层实现？

1 B树是平衡树，平衡树是一颗查找树，并且所有叶子节点位于同一层
2 B+的中间结点只存放索引，数据都存在叶结点中，因此中间结点可以存放更多的数据，让索引树更加矮胖。i/o次数少，范围查询效率高，B树需要中序遍历整个树，只B+树需要遍历叶结点中的链表。
3 B+树同时支持随机检索和顺序检索，B+树的空间利用率更高，可减少i/o次数。B+树的查询效率更加稳定。
4 Hash索引是使用HASH表一样
5 hash索引能以O（1）时间查找，但是只支持精确查找，无法进行范围查找，无法用于排序与分组，B树索引支持范围查找。

17 哪些情况下索引会失效？

以‘%’(表示任意0个或多个字符)‘开头的LIKE语句。
or语句前后没有同时使用索引。
对于多列索引，必须满足最左匹配原则/最左前缀原则（最左优先，eg：多列索引col1,col2,col3,则索引生效的情形包括col1或col1，col2或者col1，col2，col3）
如果MYSQL的全表扫描比索引快，则不使用索引。（比如非常小的表）

18 在那些地方适合创建索引？

某列经常出现在where子句中的列，或者连接子句中指定的列中。
经常作为被查询的字段
经常被作为表连接的字段
经常出现在order by/group by/disdinct后面的字段

19 创建索引需要注意什么？

只应该建立在小字段上，而不是对大文本或图片建立索引。
建立索引的字段应该非空，在mysql中，含有空值的列很难进行查询优化。
选择数据密度大的字段作索引，也就是方差比较大的字段作索引。
符合最左前缀匹配原则，较频繁作为查询条件的字段作为索引。
定义有外键的数据列一定要建立索引。

20 使用索引查询一定能提高查询的性能吗？为什么？

索引需要空间来储存，更新索引需要额外的存储空间的处理，还有时间相关的复杂度。太大的表建立索引代价大

21 MYSQL索引算法

22 聚集索引和非聚集索引

23 两种存储引擎索引的区别

24 如何定位SQL语句的性能问题？

使用explain命令来查看语句的执行计划，看查询语句是否用到了索引以及使用了什么索引，使用索引的相关信息等。可以得到表的读取顺序，数据读取操作的操作类型，哪些索引可以使用，哪些索引被实际使用，表之间的引用以及被扫描的行数等问题。

25 大表查询怎么优化？

优化shema，sql语句+索引
第二加缓存，memcached, redis
主从复制，读写分离；
垂直拆分，分布式系统
水平拆分，做一定的冗余。

26 解决超大分页

其实主要是靠缓存，可预测性的提前查到内容，缓存至redis等k-v数据库中，直接返回即可。

27 慢查询日志

在slow_query_log中设置临界时间，一旦SQL超出了我们设置的临界时间就会被记录到xxx-slow.log。
可能由什么原因造成，是查询条件没有命中索引，是load了不了不需要的数据，还是数据量过大。
针对上述问题进行可能查询了多余的行并且抛弃掉了，加载了许多结果中并不需要的列，分析语句中的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使查询语句尽可能的命中索引。
表太大的话就直接进行横向或者纵向的分表。

28 SQL语句优化

先通过慢查询日志找出，explain进行分析找出可能的原因。
尽量避免在where子句中使用!=, <, >操作符对字段进行空值判断，否则将引擎放弃使用索引而进行全表扫描。只返回必要的行和列，将大连接，查询分解成对每个表进行一次单表查询。然后在应用程序中进行关联。
索引优化，警惕sql失效的情况。

29 数据表结构的优化？

设计表的时候遵循三大范式
选择合适的数据类型，尽量不要存储null字段。
表的水平切分（sharding），将同一个表中的记录拆分到多个结构相同的表中（哈希取模）
表的垂直切分，将经常使用的字段放在一张表中，垂直切分之后业务更加清晰，系统之间整合或扩展容易。数据维护简单。

30 数据库优化？

系统的吞吐量瓶颈往往出现在数据库访问速度上，数据库中数据增多，处理时间会变得相应的慢，数据是存放在磁盘上的，读写速度无法和内存进行相比。
大表操作的时候的一些优化措施，限定数据的范围，读/写分离，缓存。

31 垂直分片和水平分片？

垂直分片的优点，可以将一个表中某些列常用，另外一些列不常用。缺点是扩展性差。
水平分片，记录了常用数据和不常用数据的区别。缺点，查询所有数据都需UNION操作。
数据库分片的二种常见方案，客户端代理：当当网的Sharding-JDBC，阿里的TDDL是二种比较常用的实现。中间件代理：在应用和数据中间加了一个代理层，分片逻辑统一维护在中间件服务中。主要包含Mycat，360的Atlas，网易的DDB。
分库和分表后面临的问题
事务支持 分库分表后，就成了分布式事务。
跨库join，解决方案有跨库join。
跨节点的count，order by，group by以及聚合函数问题
数据迁移，容量规划，扩容等问题。id问题。

32 什么是主从复制？实现原理是什么？

是指数据可以从一个MYSQL数据库主服务器复制到一个或多个从服务器，从服务器可以复制主服务器中的所有数据库或者特点的数据库
主服务器binary log dump线程：将主服务器中的数据更改日志写入Binary log中；
从服务器I/O线程：负责从主服务器binary log，并写入本地的Relay log；
从服务器的SQL线程，在从服务器中读取Relay log，并将主服务器的更改写入从服务器，从而维护主从服务器的数据一致性。

33 为什么要主从复制？

读写分离，主服务器进行写，从服务器进行读。缓解了锁的争用，即使主服务器用了写锁，从服务器也能进行查询操作。
增加冗余，提高可用性。
数据实时备份，系统某个节点发生故障，可以方便的进行故障切换。
降低了单个服务器磁盘的I/O访问的频率，提高单个机器的I/O性能。

34 数据库的主从复制

一个服务器作为主服务器，一个或多个服务器作为从服务器，主服务器将更新写到二进制日志，当一个从服务器连接到主服务器时，通知主服务器读取日志，接收从那时起发生的所有更新。解决：数据分布，负载平衡，备份，高可用性和容错性

35 数据库连接池

为数据库连接建立一个缓冲池，防止过于大量的连接的建立与管理；

36 对锁的了解

当数据库有并发一致性问题时，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就这样的一个机制。

37 锁的粒度？

mysql中提供了二种封锁粒度：行级锁和表级锁和页面锁

。

38 MySQL有哪几种锁，分别怎么实现

互斥锁简写为x锁，又称为写锁，加了写锁，事务能对A进行读取和更新，加锁期间其他事务不能对A加任何锁。
共享锁简称为S锁，又称为读锁，加了读锁，只能读取，且其他事务也能对此数据加S锁
异向锁解决了如果事务T想对数据表加表锁，必须遍历整个表查看某行是否有行级锁，这非常的耗时。

39 死锁？怎么解决？

指二个或者多个事务在同一个资源上互相占用，并请求锁定对方的资源，从而导致恶性循环的现象。
在同一个事务中，尽可能做到一次锁定所以资源。对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒读，通过表级锁来减少死锁产生的概率。
也可以使用分布式事务锁或者使用乐观锁。

40 什么是三级锁协议？

一级锁协议：事务在修改数据之前必须先对其加X锁，直到事务结束才释放，可以解决丢失修改问题。（二个事务不能同时对一个数据加X锁，避免了修改被覆盖）
二级封锁协议：在一级的基础上，事务读取数据之前必须加S锁，读完释放，解决了读脏数据的问题。
三级封锁协议：在二级的基础上，事务在读取数据之前必须先加S锁，直到事务结束才能释放。可以解决不可重复读问题。

41 数据库乐观锁和悲观锁

乐观锁 假定不会发生并发冲突，只在提交时检查，
若有其他数据更新了数据，则回滚；使用数据版本标示数据（时间戳，版本号）
悲观锁 假定会发生并发冲突，屏蔽一切破坏数据库一致性的操作，
主要用于数据争用激烈的环境，以及锁成本低于回滚成本时；排他锁；

42 什么是二段锁协议？

加锁和解锁分为二个阶段进行
可串行化调度指，通过事务的并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰。不会出现并发一致性问题。二段锁协议是可串行化调度的前提。

43 多版本并发控制

多版本并发控制，MVCC在每行记录后面都保存有二个隐藏的列，用来存储创建版本号和删除版本号
创建版本号：创建一个数据行时的事务版本号（事务版本号：事务开始时的系统版本号；系统版本号：每开始一个新的事务，系统版本号就回自动递增）
删除操作时的事务版本号。
关注查询操作：要要符合以下条件才能被查询出来，删除版本号未定义或大于当前事务版本号（删除操作是在当前事务启动之后做的），创建版本号小雨或者等于此事务的版本号（表明创建操作是事务完成或者在事务启动之前完成）
通过版本号减少了锁的争用，提高了系统性能，可以实现提交读和可重复读二种隔离级别，未提交读无需使用MVCC。