数据库面试题

PS：个人面试专用，每个人基础不同，按需自取。

阅读说明

以下的问题解答，默认采用的是Mysql数据库的InnoDB存储引擎，因此所有索引底层结构都是B+Tree

image.png

参考文章：数据库常见面试题

问题：事务的4个特性？

  1. 事务是一系列操作的集合，事务的特性，ACID。
  2. A：Atomicity，原子性，一个事务中的操作要么全部执行，要么全部不执行。
  3. C：Consistency，一致性，事务执行前后，系统从一个正确的状态变为另一个正确的状态。
  4. I：Isolation，隔离性，事务之间的执行应当是独立的，互不影响。
  5. D：Durability，持久性，事务一旦提交，对数据的修改就是永久的。
  7. PS：事务是为了保证数据的最终一致性的，因此C是目的，AID是实现目的的手段。

问题：并发事务引发的问题？

  1. 事务并发的时候可能会引发以下的问题，比如存在事务A、事务B
  2. 1、脏读：B可以读取A修改的但是未提交的数据。
  3. 2、不可重复读：B在A操作数据时，对数据进行了修改，并提交，导致A在条件相同的两次查询，查询出的数据不相同。
  4. 3、幻读：B在A操作数据时，插入/删除了记录，导致A在操作完数据的时候，发现相同查询条件下多出了一些未操作的数据，就像出现了幻觉。

问题：数据库的4个隔离级别？

  1. 事务并发的情况下，可能会存在脏读、不可重复读、幻读，为了解决以上的3个问题，提出了事务的隔离级别。
  2. 比如存在事务A、事务B并发读取数据库
  3. 1、读未提交：允许B读取A修改但是未提交的数据，无法解决任何问题，会出现脏读、不可重复读、幻读的现象。
  4. 2、读已提交：允许B读取A修改的，已经提交的数据，解决了脏读问题，但是依旧会有不可重复读、幻读的现象。
  5. 3、可重复读：确保在同一事务中，相同条件查询得到的数据是相同的，解决了脏读、不可重复读取问题，可能出现幻读
  6. 4、读序列化/串行化：将多个事务串行化，相当于同步操作，只允许1个事务操作数据库，解决了所有问题，效率最低。
  7. PS：Mysql默认的隔离级别为“可重复读”，Oracle默认的隔离级别为“读已提交”。

问题：数据库的3大范式？

参看：数据库范式那些事、数据库设计三大范式

  1. 一、范式是什么？
  2. 范式是数据库设计的一种规范，遵循该规范可以让数据库设计更加合理，实际上存在6大范式，但在开发中一般要求一张表满足第三范式即可。（一张表满足高范式必然满足低范式）
  4. 二、范式有什么用？
  5. 1、减少数据冗余（这是最主要的好处，其他好处都是由此而附带的）
  7. 三、范式有哪些？（一般直接回答以下的三大范式，当然也可以说说BC范式，这里不讨论）
  8. 1、第一范式：要求表中的每一列不可再分。
  9. 2、第二范式：要求表中的每一列必须完全依赖于全部主键，而不是部分主键。（该主键是指联合主键）
  10. 3、第三范式；要求表中的每一列数据都和主键直接相关，而不能间接相关。

第一范式案例： 地址作为字段的表不满足1NF，因为地址就可以分成xx省xx市xx区，这样对各个字段操作才会比较方便。

1NF：正确设计
image.png
PS：这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。

第二范式案例： 订单信息表中主键为（订单编号，商品编号），但是后面的商品名称、数量、单位、价格仅与商品编号有关，而与订单编号无关，因此该设计不满足第2范式，需要对该表进行拆分。

2NF：错误设计
image.png
2NF：正确设计
image.pngimage.pngimage.png
PS：这样如果需要获取订单的商品信息，使用商品编号到商品信息表中查询即可，而不需要连订单表的字段
也包含进来，这样可以减少网络IO中数据的传输。（只传送指定的数据）

第三范式案例： 如订单表与客户信息表，订单表中不应该有客户信息，因为客户信息是间接依赖于该订单记录对应的客户id， 因此需要将订单表的客户信息字段进行拆分，将订单表拆分成两张表，两表之间使用外键进行关联。

3NF：正确设计
image.png
PS：这样，就对订单信息和客户信息单独进行了管理，有效减少网络IO中数据的传输。（只传送指定的数据）

问题：索引的分类？

预读：索引 与 索引数据结构

  1. 1、索引是什么？
  2. 数据库中的索引是一种用于快速查找到记录的一种数据结构，mysql中不同存储引擎实现了不同的索引，即便索引的类型相同，索引的底层实现也完全不同。（如InnoDB和MyISAM引擎都支持B+Tree类型的索引，但底层实现方式不同）
  4. 2、索引的作用？
  5. 优化数据库的查询性能：能够轻易将查询性能提高好几个数量级
  7. 3、索引的种类？
  8. （1）单值索引：只使用1个列建立的索引。
  9. （2）普通索引：用表中的普通列构建的索引，没有任何限制。
  10. （3）主键索引：用主键建立索引，不允许重复，不允许空值，在 InnoDB 里，主键索引也被称为聚簇索引。
  11. （4）辅助索引：用非主键列建立的索引，在 InnoDB 里，非主键索引也被称为二级索引。
  12. （5）唯一索引：用来建立索引的列的值必须是唯一的，允许空值（空值就是不确定，不确定也是唯一的，所以允许空值）
  13. （6）联合索引（组合索引）：用多个列组合构建的索引，这多个列中的值不允许有空值，多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
  14. （7）覆盖索引：MySQL可以根据索引返回select字段的结果而不用根据索引再次查询文件而得出结果，换句话说，select查询的列刚好和使用到的索引的列完全一致。
  15. （8）聚簇索引：当索引的叶子节点存储的是 “键值+该键值对应的记录”，该索引为聚簇索引，一张表只有1个聚簇索引。
  16. （9）非聚簇索引：当索引的叶子节点存储的是 “键值”，需要通过键值再到由该表的聚簇索引中查找相应的记录，该索引为非聚簇索引。
  17. （10）全文索引：用大文本对象的列构建的索引，如text，varchar类型的索引

问题：索引的优缺点？

  1. 优点：极大地提高了数据库的查询速度。
  2. 缺点：过多的索引会导致会产生大量的索引文件，造成磁盘空间的浪费。

问题：索引的失效情况？

  1. 索引失效的情况
  2. 1、使用联合索引，查询条件中丢失联合索引最左的索引。如联合索引（a,b,c），where后的查询条件没有a会使索引失效
  3. 2、模糊查询%在前会导致索引失效，如'%张'会使索引失效，'张%'则不会
  4. 3、查找数据的时候进行分组排序，即order by / group by，select 的列中出现非索引列就会导致索引失效。 
  5. 4、使用or会导致索引失效

问题：索引的使用场景？

参考：浅谈group by和order by的用法和区别

  1. 适合建立索引的场景
  2. 1、可以为表中频繁查询的字段建立索引。
  3. 2、需要进行排序的字段可以建立索引。（因为索引本身就会对记录进行排序，相当于提前排好序）
  5. 不适合建立索引的场景
  6. 1、频繁更新/删除的字段不适合建立索引。（不仅需要更新记录，还需要更新索引）

问题：索引的底层实现？

  1. 索引有多种结构，以下介绍的索引结构为B+Tree树，B+Tree是在B-Tree基础上的一种优化，
  2. 使其更适合实现外存储索引结构。
  3. B-Tree：读作B杠tree，是多路平衡树，而不是二叉树
  4. B+Tree：读作B加tree，也是多路平衡树，只在B-Tree上做了修改，进一步减少了磁盘IO。
  5. 主要有以下的特性
  6. 1、所有的非叶子节点只存储关键字信息。
  7. 2、所有卫星数据（具体数据）都存在叶子结点中。
  8. 3、所有的叶子结点的数据就是整张表的数据。
  9. 4、所有叶子节点之间都有一个链指针。
  10. 5、查询数据的效率都一样，都需要找到叶子节点（叶子节点存放真实数据）

B-Tree的结构

image.png

B+Tree的结构

image.png

问题：聚簇索引与非聚簇索引？

参考：别再一知半解啦，索引其实就这么回事！（这篇文章的非聚簇索引错误）

  1. 聚簇索引：当索引的叶子节点存储的是 “键值 + 该键值对应的记录”，该索引为聚簇索引，一张表只有1个聚簇索引。
  2. 非聚簇索引：当索引的叶子节点存储的是 “键值”，需要通过该键值再到由该表主键建立的聚簇索引中查找相应的记录，
  3. 该索引为非聚簇索引。
  5. PS：在 InnoDB 里，主键索引也被称为聚簇索引，叶子节点存储的是整行数据。

问题：基于主键索引和普通索引的查询？

创建表（为id、k两个字段创建索引）

  1. create table T(
  2. id int primary key, 
  3. k int not null, 
  4. name varchar(16),
  5. index (k))engine=InnoDB;

插入数据(100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，生成的两棵索引树结构如下。 PS：InnoDB中会为主键创建聚簇索引。（叶子节点存放整行数据）， K索引的叶子节点仅仅只是存放k的值与k值所对应的一行数据的键值。（而不是整行数据）

image.png

解答开篇

  1. 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
  2. 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表
  4. PS：也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

问题：InnoDB的主键索引叶子节点存的是什么？

  1. 索引的叶子节点存储的不是一行数据，而是页（page），一个页可以存储多行数据。
  2. PS：以下是主键索引的查询过程。
  3. （1）从B+Tree的树根开始搜索，找到对应的叶子节点
  4. （2）将叶子节点的多行数据（页所包含的数据）读入到内存
  5. （3）采用类似遍历有序链表的操作获取查询的结果。
  6. 注意：mysql的页面大小是16K，操作系统的页面大小为4K

问题：主键索引被删除导致普通索引失效？

  1. 主键索引的叶子节点存储的是实际的数据（页，包含多行数据），可以直接获取数据。
  2. 普通索引的叶子节点存储的是索引列的值 和 改行记录的主键值，需要再到主键索引中查找才能获取到完整的记录。
  3. 这个过程称之为回表。
  5. 解答开篇：删除主键索引，会使得普通索引找到主键后，无法再通过主键索引获取完整记录，也就是影响了回表的过程，
  6. 因此删除主键索引会导致普通索引失效，而其他索引不会导致索引失效。

问题：InnoDB索引和MyISAM索引的区别？

  1. 首先，InnoDB和MyISAM的索引实现结构都是B+Tree，只是两者在具体的实现方式上不同。
  2. InnoDB的主键索引是聚簇索引，这样就意味着叶子节点存放的是真实的数据，二级索引的叶子节点存储的是主键值，需要再通过主键索引才能找到对应的数据。
  3. MyISAM的主键索引是非聚簇索引，和二级索引一样，因此叶子节点存放的是该行数据在物理磁盘上的地址，两个索引都不能直接获取数据，需要从磁盘中获取。

问题：索引的最左匹配原则？

  1. 索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，
  2. 必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
  4. PS：索引的最左匹配原则是基于底层采用的是B+Tree结构，当节点为复合的节点时，比如节点类型为(name,age,sex)，
  5. 那么比较的顺序就是从做到右，因此也就有了索引的“最左匹配原则”。

问题：数据库连接池的作用？

和线程池的作用类似。

  1. 数据库频繁的建立、关闭连接，会极大的减低系统的性能，因为对于连接的使用成了系统性能的瓶颈。
  2. 为了解决这个问题，可以预先创建好连接，这个技术就是数据库连接池。
  4. 优点：资源复用、响应速度更快，系统不需要频繁地创建、销毁连接

问题：SQL的执行过程？

Mysql的基础架构图

image.png

PS：可以看到Mysql主要可以分为3大部分：客户端、服务端、存储引擎

  1. 1、客户端：mysql提供的用于与服务器进行连接的工具
  2. 2、服务层：负责执行客户端的SQL语句，返回结果给客户端。
  3. 3、存储引擎：负责存储数据。
  5. 解答开篇：SQL语句的执行如下（以下是大致的步骤，详情需要参看极客时间的mysql实战）
  6. 1、客户端发送连接请求，一般是 mysql -uroot -p，然后输入密码
  7. 2、连接器负责跟客户端建立连接、获取权限、维持和管理连接
  8. ===连接建立之后可以准备执行SQL语句，步骤如下
  9. 3、先查询缓存，key是sql语句，value是结果，若命中，则返回；若没命中，则继续下一步
  10. 4、接着分析器对SQL语句进行一个词法、语法分析（有没有语法错误，需要查那张表，哪些列）
  11. 5、接着优化器对SQL语句进行优化（使用哪个索引，多表连接时join的顺序）
  12. 6、接着执行器开始执行具体的逻辑，若没有索引，则从表的第一行开始，逐个比较，满足条件则加入结果集，接着比较西一行；不满足同样接着比较下一行。用了索引执行的逻辑也一样，只不过比较的次数变少了（B+Tree结构）
  14. PS：基础架构分为3大部分，重点是执行SQL语句的服务层，服务层分为5小部分：连接器、缓存、分析器、优化器、执行器

问题：Select语句执行过程

详解一条sql语句的执行过程
1、from 将表的的数据加载到内存，生成临时表；
2、where 将临时表的记录进行过滤；
3、group by 将过滤后的数据进行分组；
4、select 只取出各个分组的指定列（同时计算聚合函数）；
5、distinct 可能需要对取出的数据进行去重
6、having 筛选符合条件的分组；
7、order by 对结果集进行排序。
8、LIMIT 只取限定的几条记录
SELECT XXX FROM XXX WHERE XXX GROUP BY XXX HAVING XXX ORDER BY XXX LIMIT XXX;

问题：SQL的查询优化？

一般SQL的优化主要关心两种：有无使用索引？多表连接时的顺序？

分析查询是否使用使用索引
参考：MySQL 性能优化神器 Explain 使用分析

  1. 以下是个人目前使用过的一些字段，其余字段的解释可以参看以上文章。
  2. explain的关键字段：type、extra、rows……
  3. 1、type字段：可以用来判断此次查询是 全表扫描 还是 索引扫描 ，主要属性如下
  4. （1）const：使用主键索引/唯一索引进行查询，结果为1行数据
  5. （2）range：使用索引列进行范围查询，结果为多行数据，向>、<、between、like等如何用到了索引列，值就为range
  6. （3）index：当查询的列为索引列，type值就为index，表示扫描整个索引，不扫描整张表，相当于覆盖索引的效果
  7. （4）ref：使用非唯一索引查询，比如非主键索引，type值就是ref，在多表级联的时候也经常会碰到！
  8. （5）all：没有使用任何索引，直接扫描整张数据表。
  9. PS：type字段还有其他的属性，暂时没用到，可以查看以上文章。
  11. 2、extra字段：
  12. （1）using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大
  13. （2）using index："覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
  14. （3）using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。
  15. （4）Using temporary：查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.
  17. 3、rows字段：记录执行器扫描了多少行记录，扫一次+1（不是精确值，因为执行器一次可以扫描多行记录）

问题：RBAC2权限系统的设计

参考：可能是史上最全的权限系统设计

个人面试专用

  1. RBAC2模型：Role-Based Access Control，基于角色的访问控制。
  2. 5张表实现简单的权限管理系统
  3. user-role-menu都是多对多的关系。
  4. 1、user（用户表）：主要字段（user_id）,主键（user_id）
  5. 2、role（角色表）：主要字段（role_id，role_level），主键（role_id）
  6. 3、menu（菜单表）：主要字段（menu_id，path），主键（menu_id）
  7. 4、user_role：主要字段（user_id,role_id），主键（user_id,role_id）
  8. 5、role_menu：主要字段（role_id,menu_id），主键（role_id,menu_id）

问题：drop,delete与truncate的区别

drop：删除整张表的结构与数据
truncat：清空表的数据，id从1开始
delete：删除表中的数据，id不会清0

问题：多个单列、联合索引的区别

参看：https://blog.csdn.net/Abysscarry/article/details/80792876

通俗理解：
利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

重点：

多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！

最左前缀原则

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上，

注：如果第一个字段是范围查询需要单独建一个索引 注：在创建联合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。这样的话扩展性较好，比如 userid 经常需要作为查询条件，而 mobile 不常常用，则需要把 userid 放在联合索引的第一位置，即最左边

同时存在联合索引和单列索引（字段有重复的），这个时候查询mysql会怎么用索引呢？

这个涉及到mysql本身的查询优化器策略了，当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引；

有人说where查询是按照从左到右的顺序，所以筛选力度大的条件尽量放前面。网上百度过，很多都是这种说法，但是据我研究，mysql执行优化器会对其进行优化，当不考虑索引时，where条件顺序对效率没有影响，真正有影响的是是否用到了索引！

联合索引本质

当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引
想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，我们上面测试过，a,c组合也可以，但实际上只用到了a的索引，c并没有用到！
注：这个可以结合上边的 通俗理解 来思考！

其他知识点：

1、需要加索引的字段，要在where条件中
2、数据量少的字段不需要加索引；因为建索引有一定开销，如果数据量小则没必要建索引（速度反而慢）
3、避免在where子句中使用or来连接条件,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描
4、联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

问题：建索引的场景题

https://www.cnblogs.com/rjzheng/p/12557314.html

题型一

如果sql为

  1. SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;

如何建立索引?
如果此题回答为对(a,b,c)建立索引，那都可以回去等通知了。
此题正确答法是，(a,b,c)或者(c,b,a)或者(b,a,c)都可以，重点要的是将区分度高的字段放在前面，区分度低的字段放后面。像性别、状态这种字段区分度就很低，我们一般放后面。

题型二

如果sql为

  1. SELECT * FROM table WHERE a > 1 and b = 2;

如何建立索引?
如果此题回答为对(a,b)建立索引，那都可以回去等通知了。
此题正确答法是，对(b,a)建立索引。如果你建立的是(a,b)索引，那么只有a字段能用得上索引，毕竟最左匹配原则遇到范围查询就停止匹配。
如果对(b,a)建立索引那么两个字段都能用上，优化器会帮我们调整where后a,b的顺序，让我们用上索引。

题型三

如果sql为

  1. SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3;

如何建立索引?
此题回答也是不一定，(b,a)或者(b,c)都可以，要结合具体情况具体分析。

题型四

  1. SELECT * FROM `table` WHERE a = 1 ORDER BY b;

如何建立索引？
这还需要想？一看就是对(a,b)建索引，当a = 1的时候，b相对有序，可以避免再次排序！
那么

  1. SELECT * FROM `table` WHERE a > 1 ORDER BY b;

如何建立索引？
对(a)建立索引，因为a的值是一个范围，这个范围内b值是无序的，没有必要对(a,b)建立索引。