（2020）数据库面试题总结

（2020年）数据库面试知识点总结

1. 关系型数据库和非关系型数据库的区别？**
   • 关系型数据库是建立在关系模型基础上的数据库，借助集合代数等数学概率和方法来处理数据库中的数据。优点：易于理解，易于维护使用通用的sql语句对操作关系型数据库非常方便，易于维护，数据库的ACID属性，大大降低了数据冗余和数据不一致的概率。缺点：海量数据的读写效率较低，主要是磁盘i/o速度慢。
   • 非关系数据库是非关系型的，分布式的，有面向高并发读写的key-value数据库redis，面向海量数据访问的面向文档数据库，例如MongoDB，还有面向可扩展的分布式数据库。优点，基于键值对，数据没有耦合性，容易扩展，基于键值对，无需sql读写性能很高。缺点：无事务支持。
2. 数据库的三大范式？*
   • 第一范式：每个列都不可再拆分。
   • 第二范式：在第一范式的基础，非主属性完全依赖于主属性。
   • 第三范式：在第二范式的基础上，非主属性非传递依赖于主属性。
   • 设计数据库时尽量遵守三大范式，如果不遵守，必须有足够的理由，比如性能。
3. Mysql具有哪些数据类型？
   • tinyint(8),smallint(16),mediumint(24),int(32),float,double
   • 日期类:datetime，timestamp
   • 字符串，二进制 char，varchar
   • char储存定长的字符，varchar储存变长的字符。
4. 主键，外键，超键，候选键？
   • 主键：数据库表中对储存数据对象给予唯一和完整标识的数据列或属性的组合，一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空。
   • 外键：在一个表中存在的另一个表的主键。
   • 超键：是主键和候选键的组合。
5. 什么是储存过程？
   • 可以看成是对一系列SQL操作的批处理。
   • 代码封装，代码复用，可以预先编译，因此具有很高的性能。
6. 触发器的作用？
   • 是特殊的储存过程，主要通过事件的触发而执行的，它可以强化约束来维护数据的完整性和一致性。
7. 什么是视图？为什么要用视图？游标是什么？*
   • 视图是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，行和列数据来自定义视图的查询所引用的基本表，在具体引用视图时动态生成。
   • 视图使开发者只关心感兴趣的某些特定数据和所负责的特点任务，只能看到视图所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。
   • 视图的优点：查询简单化，数据安全性得到保证。逻辑数据独立性。缺点：性能和修改限制。
   • 游标是系统为用户开设的一个数据缓存去，存放SQL语句的执行结果，每个游标都有一个名字，用户可以通过游标逐一获取记录并赋给主变量。交由主变量进一步处理。

SQL语句

1. 创建表，修改表，增，删，改，查？

   create table table_name(
   	id int not null primary key,
   	col1 varchar(45) null,
   )
   //创建表
   

   alter table table_name add col_name char(20);
   //添加列
   

   alter table table_name drop column col_name;
   //删除列
   

   drop table table_name;
   //删除表
   

   insert into mytable(col1, col2) values(val1, val2);
   //插入记录
   

   update mytable set col = val where id = 1;
   //更新
   

   delete from mytable where id = 1;
   //删除表的某一行
   truncate table mytable;
   //删除整个表
   

   select distinct col1, col2 from mytable; //过滤重复列
   select * from mytable limit 2, 3;//限制返回的列，从第2列开始，返回3列到第5列。
   

2. 连接查询？连接查询的效率比子查询的高。**

   • 内连接，将二个表中满足连接条件的行组合起来作为结果集，包括自然连接和等值连接。
   • 外连接，左连接是将左边表的所有数据都有显示出来，右边的表数据只显示共同有的那部分，没有对应的部分补null。左外连接保留左表中没有关联的行。右连接相反。

3. drop，delete, truncate的区别？*

   • delete 表结构还在，删除速度快，逐行删除
   • truncate 表结构还在，删除速度快，不可回滚，直接删除所有数据。
   • Drop从数据库中删除表，所以和此数据表相关都会删除，删除速度快，不可回滚。

4. in和exists，union和union all的区别？

   • exists比in效率高，使用not in 和not exists是，如果查询语句使用了not exists用到了索引，not in 没有用到索引。
   • Union all不会合并重复的记录行，union效率高于union all.

5. sql语句分类？

   • 数据定义语言DDL（Data Definition Language）CREATE,DROP,ALTER
   • 数据查询语言DQL（Data Query Language）SELECT
   • 数据操纵语言DML（Data Manipulation Language）Insert, Update,Delete
   • 数据控制功能DCL（Data Control Language）GRANT REVOKE COMMIT ROLLBACK

事务和引擎

1. 什么是数据库事务，以及其四大特性（ACID）介绍一下？
   • 事务是一个不可分割的数据库操作序列，也是数据库进行并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作。
   • Atomicity（原子性）：事务被视为不可分割的最小单位，事务的所有操作要么全部提交成功，要么全部提交失败回滚，回滚可以用回滚日志（undo log）来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
   • Consistency（一致性）：数据库在事务执行前后都保存一致性状态，在一致性状态下，所有事务对同一个数据的读取结果都是相同的。
   • Isolation（隔离型）：一个事务所做的修改在最终提交之前，对其它事务是不可见的。
   • Durability（持久性）：一旦事务提交，则所做的修改将会永远保存到数据库中，即使系统发生崩溃，事务执行的结果也不会丢失。
   • 四大特性的联系：只有满足一致性，事务的执行结果才是正确的。再无并发的情况下，隔离性一定能够满足，此时只要能够满足原子性，就一定能满足一致性。在有并发的情况下，不仅要满足隔离性，才能满足一致性。持久性是为了能应付系统崩溃的情况。
2. 事务的并发一致性问题？
   • 丢失修改：事务T1和T2二个事务都对一个数据进行修改，T1先修改，T2后修改，T2的修改覆盖了T1的修改。
   • 读脏数据：T1对一个数据进行修改后，T2读取了这个数据。如果T1撤销了这次修改，那么T2读取的数据是脏数据。
   • 不可重复读：T1对数据进行了读取，T2对这个数据进行了修改，T1再次读取这个数据时发现和第一次读取不一样。
   • 幻影读：T1读取某个范围的数据，T2在这个范围内插入了新的数据，T1再次读取这个范围的数据时，此时读取的结果和第一次读取的结果不同。
   • 产生并发不一致性问题的主要原因是破坏了事务的隔离性。并发控制可以通过封锁来实现。
3. 事务的隔离级别？MySQL的默认隔离级别是什么？隔离级别的底层实现？
   • 未提交读：最低的隔离级别，事务中的修改，即使没有提交，对于其他事务也是可见的。
   • 提交读：一个事务只能读取已经提交的事务所做的修改，换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。能阻止脏读。
   • 可重复读：保证同一个事务多次读取同一个数据的结果是一样的。解决脏读，不可重复读问题。
   • 可串行化：强制事务串行化运行，保证多个事务互不干扰，不会出现并发一致性问题。
   • 使用MVCC多版本并发控制实现提交读和可重复读这二种隔离级别，而未提交读隔离级别太低，无需使用MVCC，可串行化隔离级别需要对所有读取的行都加锁。
4. MYSQL储存引擎MyISAM于InnoDB的区别？
   • Innodb引擎:Innodb引擎提供了对于数据库ACID事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。更新操作比较多的时候，使用innodb
   • MyISAM引擎:MyISAM只支持表级锁，读写比较多的时候，使用MyISAM。
   • InnoDB实现了四个标准的隔离级别，主索引是聚簇索引，在索引中保存数据，从而避免了直接读取磁盘，因此对查询性能有很大的提升，4大特性插入缓存，二次写，自适应哈希索引，预读。

封锁

1. 对锁的了解？

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

2. 锁的粒度？

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

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

   • 互斥锁简写为x锁，又称为写锁，加了写锁，事务能对A进行读取和更新，加锁期间其他事务不能对A加任何锁。
   • 共享锁简称为S锁，又称为读锁，加了读锁，只能读取，且其他事务也能对此数据加S锁
   • 异向锁解决了如果事务T想对数据表加表锁，必须遍历整个表查看某行是否有行级锁，这非常的耗时。
   • 意向锁避免了获取锁时需要遍历整个表。
   • 在事务对数据加S锁时必须获取它的IS锁或者更强的锁
   • 在事务对数据加X锁之前，必须先获取表的IX锁

4. 数据库的乐观锁和悲观锁是什么？怎么实现的？

   • 数据管理系统中的并发控制的任务是确保多个事务在同时存取同一个数据时，不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。
   • 悲观锁：假设会发生并发冲突，认为数据随时都可能被修改，因此每次读取数据之前都会上锁，防止其他事务读取或者修改数据。适用于数据更新比较频繁的场景。实现方式：使用数据库中的锁机制。
   • 乐观锁：假设不会发生并发冲突，但是在更新时会判断在此期间有没有别的事务更新这个数据，若被更新过，则失败重试。适用于读多写少的场景。实现方式：加版本号或者时间戳，每次数据更新时同时更新这个字段。

5. 死锁？怎么解决？

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

6. 什么是三级锁协议？

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

7. 什么是二段锁协议？

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

8. 多版本并发控制？

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

   select * from table where ? lock in share mode;S锁
   select * from table where ? for update;X锁
   

数据库索引

1. 什么是索引？有哪些优缺点？

   • 索引是数据库管理系统中一个排序的数据结构，协助快速查询，更新数据库表中数据，索引的实现通常使用B树及其变种B+树。
   • 优点：可以大大加快数据的检索速度，这也是创建索引的最主要原因，减少查询中分组和排序的时间，将随机i/o转化为顺序i/o，提高系统的性能。
   • 缺点：创建和动态的维护索引需要消耗大量的时间，索引需要占物理空间。

2. 数据库中有哪些索引类型？

   • 主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键

   • 唯一索引：一个表允许多个列创建唯一索引。

     alter table table_name add unique (column1, column2);
     

   • 普通索引：基本的索引类型，没有唯一性的限制，允许为NULL。

     alter table table_name add index index_name (column);
     

   • 全文索引：是目前搜索引擎使用的关键技术。

     alter table table_name add fulltext (column);
     

3. 数据库索引的底层实现？

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

4. 哪些情况下索引会失效？

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

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

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

6. 创建索引需要注意什么？

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

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

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

8. 聚集索引和非聚集索引的区别 ？

   • 聚簇索引：将数据存储与索引放在一块，找到索引也就找到了数据
   • 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行。

优化

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

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

2. 大表查询怎么优化？

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

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

4. 慢查询日志？

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

5. sql语句优化？

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

6. 数据表结构的优化？

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

7. 数据库优化？

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

8. 垂直分片和水平分片？

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

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

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

10. 为什么要主从复制？

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