经典SQL面试题

SQL

什么是数据库事务？

数据库事务是指：一组SQL语句，要么全部执行成功，要么全部执行失败

四大特性：（ACID）一致性，原子性，隔 离性，持久性（一旦事务提交，对数据的修改是永久性的）

会出现哪些并发不一致性的问题

丢失数据：一个事务对数据进行修改，在事务提交之前，另一个事务对同一数据进行修改，覆盖之前的修改

脏读：一个事务读取另外一个事务的修改，但未提交（进行了回滚）的数据，造成两个事务得到的数据不一致

不可重复读：在同一事务中，某查询操作在一个时间读取某一行的数据和之后一个时间读取该行数据，发现数据已经发生修改（可能被更新或者删除了）

幻读：当同一查询多次执行时，由于其他事务在这个数据范围内执行了插入操作，会导致每次返回不同的结果集（和不可重复读的区别：针对的是一个数据整体/范围，并且需要是插入操作）

数据库的四种隔离级别

未提交读：在一个事务提交之前，它的执行结果是对其他事务也是可见的，会导致脏读，不可重复读，幻读

提交读：一个事务只能看见已经提交的事务所作的改变，可避免脏读的问题

可重复读：可以确保同一个事务在多次读取同样的数据得到相同的结果，（MySQL默认隔离级别），可以避免不可重复读

可串行化：强制事务串行执行，使之不可能相互冲突，从而解决幻读问题。可能导致大量的超时现象和锁竞争，实际使用很少

什么是索引？有什么作用以及优缺点？

索引是对数据库表中一个或者多个列的值进行排序的结构

作用：在数据库中，索引也允许数据库程序迅速找到表中的数据，而不必扫描整个表

优点：索引加快数据库的检索速度

​ 索引降低插入，删除，修改等维护任务的速度

​ 唯一索引可以确保每一行数据的唯一性

​ 将随机I/O变为顺序I/O（B+Tree索引是有序的，会将相领的数据都存在一起）

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

缺点：索引需要占用一定的物理和数据空间，更新索引很慢

什么是视图

视图是一个虚拟的表，具有和物理表相同的功能，可以对视图进行增改查操作，视图通常是一个表或者多个表的行或者列的子集，对视图的修改不影响基本表，他使得我们获取数据更容易

使用场景：

​ 只暴露部分字段给访问者

​ 查询的数据来源于不同的表，而查询者希望以统一的方式查询

什么是存储过程？有哪些优缺点？

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，想要实现相应功能时，只需要调用这个存储过程就行了（类似于函数，输入具有输出参数）

优点:

• 预先编译，而不需要每次运行时编译，提高数据库的执行效率
• 封装一系列的操作，对于一些数据的交互性比较多的操作，相比于单独执行SQL语句，可以减少网络通信量
• 具有可复用性，减少数据库开发的工作量
• 安全性高，可以没有权限的用户通过存储过程间接操作数据库
• 更易于维护

缺点：

• 可移植性差，存储过程将应用程序绑定到数据库上
• 可发调试复杂，没有很好的IDE
• 修改复杂：需要重新编译，有时还需要更新程序中的代码以更新调用

什么是触发器

触发器是由事件（INSERT/UPDATE/DELETE）来触发运行的操作（不能被直接调用，不能接受参数），在数据库里以独立的对象存储，用于保证数据的完整性（比如可以检验或转换数据）

• 游标：用于定位在查询返回的结果集的特定行，以对特定行进行操作，使用游标可以方便的对结果集进行遍历，根据需要滚动或对浏览/修改任意行的数据，主要用于交互式应用

数据库索引实现（B+树）

使用B树和B+树的比较

InnoDB的索引使用B+树实现，B+树对比B树的好处

• IO次数少，B+树的中间节点只放索引，数据都存在叶节点中，因此中间结点可以存放更多的数据，让索引更加矮胖
• 范围查询效率更高，B树需要中序遍历整个树，只B+树需要遍历叶节点中的链表
• 查询效率更加稳定，每次查询都需要从根节点到叶节点，路径长度相同，所以每次查询的效率都差不多

使用B树索引和哈希索引的比较

哈希表能以O(1)时间进行查找，但是只支持精确查找，无法用于部分查找和范围查找，无法排序和分组，B树索引支持大于小于等于查找，范围查找，哈希表索引遇到大量哈希值相等的情况查找效率会降低，哈希索引不支持数据的排序

MySQL的两种存储引擎InnoDB和MyISAM的区别

• InnoDB支持事务，可以进行Commit 和Rollback
• MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及安全性较高的应用
• MyISAM只支持表级锁，而InnoBD还支持行级锁，提高了并发操作的性能
• InnoDB支持外键
• MyISAM崩溃后发生的损坏率比InnoDB高很多，而且恢复速度也更慢
• MyISAM支持压缩表和空间数据索引，InnoDB需要更多的内存和存储
• InnoDB在线热备份
  • 热备份：在数据库运行的情况下备份的方法，优点：可按表或用户备份，备份时数据库仍然可以使用，可恢复至任一时间点，但是不能出错
  • 冷备份：数据库正常关闭后，将关键性文件复制到另一位置的备份方式，优点：操作简单快速，恢复简单

如何优化数据库

SQL语句优化

• 应尽量避免在where子句中使用!=,<,>操作符或对字段进行null值判断，否则将引擎放弃使用索引而进行全表扫描
• 只返回必要的列，最好不适用SELECT *
• 只返回必要的行：使用LIMIT语句来限制返回的数据
• 将大连接查询分解成对每个表的一次单表查询，然后在应用程序中进行关联

索引的优化

• 注意会引起索引失效的情况，以及在适合位置建立索引

数据库表结构优化

• 设计表遵从三大范式
  • 第一范式：属性不应该是可分的
  • 第二范式：每个非主属性完全依赖于 主属性集
  • 第三范式：非主属性不传递依赖于主属性

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

主从复制是指数据可以从一个MySQL数据库主服务器复制到一个或者多个从服务器上，从服务器可以复制主服务器的所有数据或者特定的数据库，或者特定的表，默认采用异步的方式

MySQL主从与MongoDb副本集的区别？

• MongoDB在同一服务器，同时复制备份多个数据库
• MySQL复制备份数据是在多个服务器上（多台机器），复制备份多个数据库
• MongoDB复制备份是同步的，MySQL复制备份是异步的，也可以是主从同步
• MongoDB读写在指定某一个数据库作为主数据库
• MySQL一般主服务器写，从服务器读，读写分离

非关系型数据库Redis和MongoDB数据库的结构有什么区别？

Redis数据库的最基本单元就是键值对的字典，一个数据库中可以独立存在多个字典，打开Redis数据库，默认有16个数据库，从0开始编号，默认连接的是0号数据库

• Redis服务–>数据库（默认16个）—>字典
• 创建键值对数据（字典形式），字典都存在数据库中
• Redis默认支持16个数据库，设置配置文件可以支持更多，并且目前没有限制

MongoDb数据库的最基本单元是文档document，一个文档就类似于一个字典，一组文档组成一个集合collection，一系列集合组成一个数据库，多个数据库就是一个MongoDb实例(MongoDB实例下面可以创建多个数据库)

数据库结构：MongoDB实例—>数据库database(可以创建多个) -->集合collection —>文档document

Redis持久化机制是什么？有哪几种方式？

Redis是一个支持持久化的内存数据库，通过持久化机制把内存中的数据同步到硬盘文件来保证数据持久化，当Redis重启后通过把硬盘文件重新加入内存，来达到持久化的目的

Redis有两种持久化的操作，Redis的一种持久化方式称为快照(snapshotting,RDB),另外一种方式就是追加文件(append-only file ，AOF)

RDB:快照持久化是Redis默认的持久化方式，在redis.conf配置文件中，是Redis创建快照之后，对其快照的备份，可以将快照复制到其他服务器从而创建具有相同数据的服务器副本，还可以将快照留在原地以便重启服务器的时候使用

AOF:AOF持久化的实时性更好，因此已经称为主流的持久化方案，可以通过appendonly参数开启，开启AOF持久化后每执行一条会更改Redis中数据的命令，Redis就会将该命令写入硬盘中的AOF文件，AOF文件的保存位置和RDF位置相同，默认名字是appendonly.aof

为什么使用redis作为缓存？

Redis数据是存储在内存中，性能强大，它支持高性能和高并发

• 高性能：传统数据库都是从硬盘读取数据，Redis直接从缓存读取
• 高并发：缓存的抗压能力 是大于数据库的，请求直接从缓存进行 而不用去数据库，大大提高并发性

Redis为什么是单线程的？

Redis是基于内存的操作，CPU不是Redis的瓶颈，Redis的瓶颈最有可能是机器内存的大小或者网络带宽

单线程容易实现，而且CPU不会成为瓶颈，就直接采用单线程的方法