Java面经——数据库

所有java面经系列已同步到我的github，欢迎访问https://github.com/tzfun/Java-Interview-experience，记得给颗星星支持一下哦~~

因为笔者学习的是mysql，所以本篇文章的面试问题主要以mysql为主。

基础部分

1.mysql有哪些数据类型？

• 数值类型：TINYINT、SMALLINT、MEDIUMINT、INT或INTEGER、BIGINT、FLOAT、DOUBLE、DECIMAL
• 日期和时间类型：DATE、TIME、YEAR、DATETIME、TIMESTAMP
• 字符串类型：CHAR、VARCHAR、TINYBLOB、TINYTEXT、BLOB、TEXT、MEDIUMBLOB、MEDIUMTEXT、LONGBLOB、LONGTEXT

2.char和varchar的区别是什么？

varchar是变长而char的长度是固定的。

3.FLOAT和DOUBLE的区别是什么？

• FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
• DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

4.varchar和text的区别是什么？

• varchar可指定字符数，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
• text类型不能有默认值。
• varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引几乎不起作用。
• 查询text需要创建临时表。

5.DROP、TRUNCATE、DELETE的区别是什么？

DROP:

1. drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
2. drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
3. drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

TRUNCATE:

1. truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
2. truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。
3. 对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
4. truncate table不能用于参与了索引视图的表。

DELETE:

1. delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。
2. delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。
3. delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

6.创建索引的SQL语句

CREATE INDEX index_name ON table_name (column_name)

7.什么是触发器？

触发器是指一段代码，当触发某个事件时，自动执行这些代码。

8.创建触发器的SQL语句

例如：在插入之前执行批量SQL

CREATE TRIGGER trigger_name BEFORE INSERT
ON table_name
FOR EACH ROW
BEGIN
    SQL1;
    SQL2;
    SQL3;
END

9.mysql触发器的六中触发条件

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

10.创建视图的SQL语句

CREATE VIEW v AS SELECT * FROM table;

11.创建视图之后的表真实存在吗？

不是，视图只是将查询的复杂SQL语句进行记录，方便后续使用，使用时用视图可直接替代复杂的SQL语句，不会开辟空间存储表结果。

12.内连接、左连接、右连接、全连接的区别

内连接：

SELECT * FROM table_a INNER JOIN table_b ON a = b 把表连接时table_a与table_b之间匹配的数据行查询出来，两张表之间数据行均满足匹配，即同时满足ON语句后面的条件。

左（外）连接：

SELECT * FROM table_a LEFT JOIN table_b ON a = b 查询结果以table_a表数据为主，无论是否满足ON的条件，均会查询出左表（table_a）的数据，而右表（table_b）只有满足ON的条件才会被查询出，不满足左表的数据项用NULL填充。

右（外）连接：

SELECT * FROM table_a RIGHT JOIN table_b ON a = b 查询结果以table_b表数据为主，无论是否满足ON的条件，均会查询出右表（table_b）的数据，而左表（table_a）只有满足ON的条件才会被查询出，不满足右表的数据项用NULL填充。

全连接

(SELECT * from table_a ) UNION (SELECT * from table_b ) 求两个表的并集。

13.常用关键字

• GROUP BY xxx ON xxx：按条件分组
• ORDER BY xxx ASC：按xxx字段升序排序
• ORDER BY xxx DESC：按xxx字段降序排序
• LIKE xxx：模糊查询，%匹配任意字符
• LIMIT n,m：从第n条数据开始向后遍历m条数据

索引部分

14.二叉查找树索引的原理及其缺点

原理：使用二叉查找树作为索引存储的数据结构，平均查询时间复杂度为O(logn)。

缺点：存储数据量小，无法保证树的高度。树越高查询效率越低，数据库磁盘IO操作越多，极端情况下查询时间复杂度为O(n)。

15.B树索引的原理及其优缺点

原理：B树又称平衡多路查找树，底层采用B树作为索引存储的数据结构，一个节点可存储多个索引，并且满足二叉查找树的条件。检索深度不确定，可能检索到非叶子节点就结束，也可能检索到叶子节点才结束。

优缺点：由于B树的特性，控制了树的平衡度，减少了磁盘IO的操作次数，存储索引的容量增大，平均查询时间复杂度为O(logn)，B树为许多数据库引擎所使用。缺点是不支持范围查询。

16.B+树索引的原理及其与B树的区别

原理：使用B+树为索引存储的数据结构，数据全部存储在叶子节点，非叶子节点仅用来存储索引不存储数据，所有的检索都必须从根部检索到叶子节点才能结束。叶子节点尾部有一个指针，指向下一叶子结点的尾部，有利于范围查找。

与B树区别：

• B+树存储容量比B树更大；
• B+树的高度相对于B树更矮，磁盘IO读取更少；
• B+树查询效率更加稳定，每次查询都必须从根部检索到叶子节点才能结束；
• B+树支持范围查询，B树不支持。

17.Hash索引的原理及其优缺点

原理：Hash索引内部使用Hash表进行存储数据，其原理和Java中的HashMap类似。通过对Keys进行求hash散列值对应到相应的bucket位置，然后再将bucket的数据全部加载到内存中，在bucket中进行线性查询目标数据位置。理论上查询效率比B树和B+树效率都高。

优点：

• 平均查询效率高

缺点：

• 仅仅满足“=”和“IN”等等值过滤的查询，不支持范围查询
• 无法对数据进行排序
• 不能使用部分索引键查询（组合索引无法使用）
• 不能避免表扫描
• 哈希碰撞严重时，查询效率会大大降低（同一bucket中是线性查询O(n)）

18.密集索引和稀疏索引的区别

• 密集索引文件中每个搜索码的值都对应一个索引值
• 稀疏索引只为数据文件的每个存储块设一个键-指针对,它比稠密索引节省了更多的存储空间，但查找给定值的记录需更多的时间。

在mysql中InnoDB引擎使用的是密集索引，MyIsam使用的稀疏索引。InnoDB中一定会有一个密集索引，如果定义了主键那么它就是密集索引，如果没有那就取第一个唯一非空索引作为密集索引，如果都没有那么内部会生成一个隐藏索引作为密集索引。

19.mysql中有哪些索引？

• 普通索引：加速查询
• 唯一索引：加速查询 + 列值唯一（可以有null）
• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
• 全文索引：对文本的内容进行分词，进行搜索

20.为什么要使用索引？

索引可以避免全表扫描去查找数据，提升检索效率。

21.什么样的数据可以成为索引？

主键、唯一键等，能区分字段唯一性的数据。（字段重复太多不宜建立索引，反而会降低检索速率，比如性别）

22.如何定位并优化Sql？

• 根据慢日志定位查询Sql（超出设定执行时间的sql语句会被记录到慢日志中）
• 使用explain分析Sql（type字段为index或者all时表示是全表扫描，建议优化）
• 修改Sql，尝试使用索引优化查询

23.索引是建立的越多越好吗？

答案是否定的，数据量比较小的数据不需要建立索引，建立索引会增加额外的开销，并且需要更多消耗去维护索引，建立索引越多维护成本越多，同时索引也需要存储空间，索引越多需要消耗的存储空间也就越大。

数据库事务

24.MyIsam和InnoDB在锁方面的区别是什么？

• MyIsam支持表级锁不支持行级锁，即操作表中某一条数据就会锁上整张表。
• InnoDB即支持行级锁又支持表级锁。当WHERE走索引时默认开启行级锁，即仅锁住该行数据，表中其他数据不受影响；当不走索引时默认开启表级锁，即锁住整张表。

25.数据库中锁的分类

• 按锁粒度划分：行级锁、表级锁、页级锁
• 按锁级别划分：排它锁、共享锁
• 按加锁方式划分：隐式锁、显示锁
• 按使用方式划分：悲观锁、乐观锁

26.解释一下排它锁和共享锁

• 共享锁：不堵塞，多个用户可以同时读一个资源，互不干扰。
• 排它锁：一个写锁会阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。

27.排它锁和共享锁的兼容性

仅共享锁和共享锁是兼容，其余所有情况不兼容。在mysql中默认select为共享锁，update、delete、insert为排它锁。当然在执行select命令时也可设定锁类型。

select * from table for update; # 排他查，使用排他锁
select * from table lock in share mode; # 共享查，使用共享锁

X：排它锁。S：共享锁

28.MyIsam和InnoDB各自使用的场景是什么？

MyIsam：

• 频繁执行全表count语句。MyIsam中专门有一个字段存储全表数据量，而InnoDB则每次都需要全表扫描。
• 对数据进行增删改的频率不高，查询非常频繁。因为其锁粒度支持不高，增删改会影响性能。
• 不支持事务的场景。

InnoDB：

• 数据增删改查都比较频繁
• 需要事务支持，并且可靠性要求较高。

29.数据库事务的四大特性（ACID）是什么？

• 原子性（Atomicity）： 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
• 一致性（Consistency）：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
• 隔离性（Isolation）：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
• 持久性（Durability）：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

30.介绍一下四大隔离级别？

1. 读未提交（Read uncommitted），就是一个事务能够看到其他事务尚未提交的修改，这是最低的隔离水平，允许脏读出现。
2. 读已提交（Read committed），事务能够看到的数据都是其他事务已经提交的修改，也就是保证不会看到任何中间性状态，当然脏读也不会出现。读已提交仍然是比较低级别的隔离，并不保证再次读取时能够获取同样的数据，也就是允许其他事务并发修改数据，允许不可重复读和幻象读（Phantom Read）出现。
3. 可重复读（Repeatable reads），保证同一个事务中多次读取的数据是一致的，这是 MySQL InnoDB 引擎的默认隔离级别，但是和一些其他数据库实现不同的是，可以简单认为 MySQL 在可重复读级别不会出现幻象读。
4. 串行化（Serializable），并发事务之间是串行化的，通常意味着读取需要获取共享读锁，更新需要获取排他写锁，如果 SQL 使用 WHERE 语句，还会获取区间锁（MySQL 以 GAP 锁形式实现，可重复读级别中默认也会使用），这是最高的隔离级别。

其他

31.关系数据库的三大范式是什么？

1. 第一范式（1NF）：在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。简而言之，第一范式就是无重复的列。
2. 第二范式（2NF）：满足第二范式（2NF）必须先满足第一范式（1NF），第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。第二范式（2NF）要求实体的属性完全依赖于主关键字，简而言之，第二范式就是非主属性非部分依赖于主关键字。
3. 第三范式（3NF）：满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

32.简述一下关系型数据库和非关系型数据库的优劣

关系型数据库的优势：

1. 保持数据的一致性（事务处理）
2. 由于以标准化为前提，数据更新的开销很小（相同的字段基本上都只有一处）
3. 可以进行Join等复杂查询

关系型数据库的不足：

1. 大量数据的写入处理
2. 为有数据更新的表做索引或表结构（schema）变更
3. 字段不固定时应用
4. 对简单查询需要快速返回结果的处理

非关系型数据库的优势：

1. 性能NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
2. 可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

非关系型数据库的不足：

1. 不提供关系型数据库对事务的处理。
2. 不支持复杂查询

33.SQL语句优化

1. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

1. 很多时候用 exists 代替 in 是一个好的选择
2. 用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤
3. 尽量使用join连表查询，减少数据遍历次数
4. 查询时能走索引就走索引