Java关系型数据库相关面试题

灰太狼 2023-07-15 11:23 4阅读 0赞

***1、手撕sql***  
有学生表、课程表、成绩表，计算平均成绩大于等于60的学生id、学生姓名和平均成绩。  
select s.s\_id,s.s\_name,avg(sc.score)  
from score sc  
left join student s on sc.s\_id=s.s\_id  
group by s.s\_id having avg(sc.score>=60)

可能引发问题  
**（1）几种join的区别**  
1）.以A，B两张表为例  
A left join B  
选出A的所有记录，B表中没有的以null 代替  
2）.right join 同理  
3）.inner join  
A,B的所有记录都选出，没有的记录以null代替  
4）.cross join (笛卡尔积)  
A中的每一条记录和B中的每一条记录生成一条记录  
例如A中有4条，B中有4条，cross join 就有16条记录  
**（2）常见复杂sql的语法格式**  
select column, group\_function(column)  
from table  
\[where condition\]  
\[group by group\_by\_expression\]  
\[order by column\];  
where -> group by -> having -> order by -> limit （顺序不能错）

***2、Mysql中的索引类型***

*  index ---- 普通索引,数据可以重复，没有任何限制。
 *  unique ---- 唯一索引,要求索引列的值必须唯一，但允许有空值；如果是组合索引，那么列值的组合必须唯一。
 *  primary key ---- 主键索引,是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在创建表的同时创建主键索引。
 *  组合索引 ---- 在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
 *  fulltext ---- 全文索引,是对于大表的文本域：char，varchar，text列才能创建全文索引，主要用于查找文本中的关键字，并不是直接与索引中的值进行比较。fulltext更像是一个搜索引擎，配合match against操作使用，而不是一般的where语句加like。

注:全文索引目前只有MyISAM存储引擎支持全文索引，InnoDB引擎5.6以下版本还不支持全文索引  
　　所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节，索引有两种存储类型，包括B+树索引和哈希索引。  
　　索引可以提高查询的速度，但是创建和维护索引需要耗费时间，同时也会影响插入的速度，如果需要插入大量的数据时，最好是先删除索引，插入数据后再建立索引。  
***3、索引失效条件***

*  不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
 *  存储引擎不能使用索引范围条件右边的列
 *  尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select \*
 *  mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
 *  is null,is not null也无法使用索引
 *  like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作。

假设  
index（a,b,c）

*  最左前缀匹配：模糊查询时，使用%匹配时：’a%‘会使用索引，’%a‘不会使用索引
 *  条件中有or，索引不会生效
 *  a and c，a生效，c不生效
 *  b and c，都不生效
 *  a and b > 5 and c, a和b生效，c不生效。

***4、覆盖索引***  
[参考][Link 1]  
***5、B+树索引和hash索引的比较***

*  Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。  
    由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。
 *  Hash 索引无法被用来避免数据的排序操作。  
    由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
 *  Hash索引不能利用部分索引键查询。  
    对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
 *  Hash 索引在任何时候都不能避免表扫描。  
    前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
 *  Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。  
    对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

***6、索引的底层实现（B+树，为何不采用红黑树，B树）***  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjE0MjA0Mg_size_16_color_FFFFFF_t_70]  
索引使用B+树的原因：

*  索引查找过程中就要产生磁盘I/O消耗,主要看IO次数，和磁盘存取原理有关。
 *  根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入
 *  局部性原理与磁盘预读

***7、Mysql存储引擎 MyISAM 和 InnoDB***  
MyISAM存储引擎的特点是：表级锁、不支持事务和支持全文索引  
InnoDB存储引擎的特点是：行级锁、事务安全（ACID兼容）、支持外键、不支持FULLTEXT类型的索引(5.6.4以后版本开始支持FULLTEXT类型的索引)。InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。InnoDB是为处理巨大量时拥有最大性能而设计的。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。  
注意：  
InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表(锁完以后，判断不符合条件的会逐步解锁)，  
例如update table set num=1 where name like “a%”。  
**适用场景**  
MyISAM适合：  
1). 做很多count 的计算；  
2). 插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择；  
3). 没有事务。  
InnoDB适合：  
1). 可靠性要求比较高，或者要求事务；  
2). 表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建；  
3). 如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表；  
4).DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除；  
5).LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

***8、数据库三范式***  
1）、第一范式：  
当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。（属性的原子性约束，不可在分解）  
2）、第二范式：  
如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。（非主键属性完全依赖于主键属性）  
3）、第三范式：  
设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF。（非主键属性不能出现依赖传递）

***9、事务ACID***

*  原子性(Atomic)：不可分割的操作单元，事务中所有操作，要么全部成功；要么撤回到执行事务之前的状态
 *  一致性(Consistency)：如果在执行事务之前数据库是一致的，那么在执行事务之后数据库也还是一致的；
 *  隔离性(Isolation)：事务操作之间彼此独立和透明互不影响。事务独立运行。这通常使用锁来实现。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
 *  持久性(Durability)：事务一旦提交，其结果就是永久的。即便发生系统故障，也能恢复。

***10、事务隔离级别***

*  未提交读(Read Uncommitted)：允许脏读，其他事务只要修改了数据，即使未提交，本事务也能看到修改后的数据值。也就是可能读取到其他会话中未提交事务修改的数据
 *  提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。
 *  可重复读(Repeated Read)：可重复读。无论其他事务是否修改并提交了数据，在这个事务中看到的数据值始终不受其他事务影响。存在幻读问题
 *  串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

MySQL数据库(InnoDB引擎)默认使用可重复读（ Repeatable read)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjE0MjA0Mg_size_16_color_FFFFFF_t_70 1]  
**事务并发问题：**  
1）、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据  
2）、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。  
3）、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。  
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表  
　　  
***11、大表优化***

*  限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。
 *  读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
 *  缓存：使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
 *  垂直分区：  
    根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。  
    简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjE0MjA0Mg_size_16_color_FFFFFF_t_70 2]  
    垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。  
    垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；
 *  水平分区  
    保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。  
    水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjE0MjA0Mg_size_16_color_FFFFFF_t_70 3]  
    水平拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL 并发能力没有什么意义，所以水平拆分最好分库 。  
    水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨界点Join 性能较差，逻辑复杂。

***12、分库分表带来的问题***

*  事务一致性问题
 *  跨节点关联查询 join 问题
 *  跨节点分页、排序、函数问题
 *  全局主键避重问题  
    [雪花算法][Link 2]
 *  数据迁移、扩容问题

***13、慢查询排查与优化***  
[参考][Link 3]

***14、mysql主从复制***  
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。主服务器对数据库修改记录二进制日志（binlog），从服务器通过主服务器的二进制日志自动执行更新。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjE0MjA0Mg_size_16_color_FFFFFF_t_70 4]  
[参考][Link 4]

参考资料：  
[MySQL常见面试题][MySQL]  
[面试题：索引失效的几种情况（MySQL）][MySQL 1]  
[mysql 联合索引生效的条件、索引失效的条件][mysql]  
[MySQL的btree索引和hash索引的区别][MySQL_btree_hash]  
[MySQL存储引擎MyISAM与InnoDB区别总结整理][MySQL_MyISAM_InnoDB]  
[大表优化][Link 5]  
[数据库分库分表思路][Link 6]

[Link 1]: https://www.cnblogs.com/happyflyingpig/p/7662881.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjE0MjA0Mg_size_16_color_FFFFFF_t_70]: /images/20230528/76e4b4c163d54ad981462c2296783537.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjE0MjA0Mg_size_16_color_FFFFFF_t_70 1]: /images/20230528/b733aa03dc424f38adeeb192fc0a0e0d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjE0MjA0Mg_size_16_color_FFFFFF_t_70 2]: /images/20230528/2996b381efd74d6badd559ecf9eeb3ae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjE0MjA0Mg_size_16_color_FFFFFF_t_70 3]: /images/20230528/f31a484692714808beb35d8dabc0201c.png
[Link 2]: https://www.jianshu.com/p/9d7ebe37215e
[Link 3]: https://blog.csdn.net/qq_35571554/article/details/82800463
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjE0MjA0Mg_size_16_color_FFFFFF_t_70 4]: /images/20230528/8cf4312a1bf940368549e38c0992a128.png
[Link 4]: https://blog.csdn.net/u010982507/article/details/93361592?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task
[MySQL]: https://www.cnblogs.com/hsmwlyl/p/10719152.html
[MySQL 1]: https://blog.csdn.net/weixin_42228338/article/details/97655100
[mysql]: https://blog.csdn.net/qq_35275233/article/details/87888809
[MySQL_btree_hash]: https://www.cnblogs.com/AndyAo/p/8183383.html
[MySQL_MyISAM_InnoDB]: https://www.cnblogs.com/Jtianlin/p/8954841.html
[Link 5]: https://blog.csdn.net/u014209205/article/details/83051001
[Link 6]: https://www.cnblogs.com/butterfly100/p/9034281.html