Java面试题-MySQL

1. 什么是MySQL？

MySQL 是⼀种关系型数据库，在Java企业级开发中⾮常常⽤，因为 MySQL 是开源免费的，并且⽅便扩展。MySQL是开放源代码的，因此任何⼈都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进⾏修改。MySQL的默认端⼝号是3306。

2. 什么是SQL？

结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。

作用：用于存取数据、查询、更新和管理关系数据库系统。

3. 数据库三大范式是什么？

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

4. 数据库事务四大特性ACID

1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么完全不起作用；

2. ⼀致性（Consistency）： 执行事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；

3. 隔离性（Isolation）： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独立的；

4. 持久性（Durability）： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

5. 并发事务带来哪些问题?

（1）脏读：指事务读到脏数据，所谓脏数据，指的是不正确的数据，例如事务执行过程中修改了某记录，然后回滚，如果其他事务读到了该记录的中间修改值，则为脏读。（读取到中间回滚的数据）

（2）不可重复读：事务在执行过程中，多次对同一个已经存在的记录进行读取，各次读取的值不同。读提交隔离级别存在不可重复读的问题，事务1、2并发执行，事务2首先读取记录1，然后事务1修改记录1并提交，事务2继续读取记录1，则事务2两次读取到的值不同。
（多个事务对同一个记录进行读取，先的事务读取并修改完，后的各个事务读取的值不同）

（3）幻读：幻读是指使用某个条件读取一批记录时，可能读到的记录数不同。幻读与不可重复读类似。它发⽣在⼀个事务（T1）读取了几行数据，接着另⼀个并发事务（T2）插⼊了⼀些数据时。在随后的查询中，第⼀个事务（T1）就会发现多了⼀些原本不存在的记录，就好像发⽣了幻觉⼀样，所以称为幻读。

（4）丢失修改: 指在⼀个事务读取⼀个数据时，另外⼀个事务也访问了该数据，那么在第⼀个事务中修改了这个数据后，第⼆个事务也修改了这个数据。这样第⼀个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。（前面事务修改的内容被后面事务修改的内容覆盖了）

不可重复读和幻读区别：

不可重复读的重点是修改⽐如多次读取⼀条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除⽐如多次读取⼀条记录发现记录增多或减少了。

6. 事务隔离级别有哪些?MySQL的默认隔离级别是?

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发⽣。
• REPEATABLE-READ(可重复读)： 对同⼀字段的多次读取结果都是⼀致的，除非数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣。
• SERIALIZABLE(可串行化)： 最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰，也就是说，该级别可以防⽌脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重复读）。我们可以通过 SELECT @@tx_isolation; 命令来查看

7. 查看MySQL提供的所有存储引擎

mysql> show engines;

8. MySQL四种存储引擎

innodb、myisam、memory、archive

9. MySQL存储引擎的介绍

详细请参考：https://blog.csdn.net/yjclsx/article/details/81911027

10. MySQL中myISAM与InnoDB的区别

两者的对⽐：

1. 是否⽀持行级锁 : MyISAM 只有表级锁(table-level locking)，⽽InnoDB ⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁。
2. 是否⽀持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原⼦性,其执⾏速度⽐InnoDB类型更快，但是不提供事务⽀持。但是InnoDB 提供事务⽀持事务，外部键等⾼级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能⼒(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
3. 是否⽀持外键： MyISAM不⽀持，⽽InnoDB⽀持。
4. 是否⽀持MVCC ：仅 InnoDB ⽀持。应对⾼并发事务, MVCC⽐单纯的加锁更⾼效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下⼯作;MVCC可以使⽤ 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统⼀。

11. MySQL索引

MySQL索引使⽤的数据结构主要有BTree索引 和 哈希索引 。

⼤多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；
其余⼤部分场景，建议选择BTree索引。

优点
提高查询速度

缺点
更新数据时效率低，因为要同时更新索引

对数据进行频繁查询进建立索引，如果要频繁更改数据不建议使用索引。

12. 缓存

my.cnf加入以下配置，重启Mysql开启查询缓存

query_cache_type=1
query_cache_size=600000

Mysql执行以下命令也可以开启查询缓存

set global  query_cache_type=1;
set global  query_cache_size=600000;

开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。

这⾥的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等⼀些可能影响结果的信息。因此任何两个查询在任何字符上的不同都会导致缓存不命中。此外，如果查询中包含任何⽤户⾃定义函数、存储函数、⽤户变量、临时表、MySQL库中的系统表，其查询结果也不会被缓存。

缓存建立之后，MySQL的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发⽣变化，那么和这张表相关的所有缓存数据都将失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做⼀次缓存操作，失效后还要销毁。 因此，开启缓存查询要谨慎，尤其对于写密集的应⽤来说更是如此。如果开启，要注意合理控制缓存空间⼤⼩，⼀般来说其⼤⼩设置为⼏⼗MB⽐较合适。此外，还可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要缓存：

select sql_no_cache count(*) from usr;

13. 锁机制与InnoDB锁算法

MyISAM和InnoDB存储引擎使⽤的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB⽀持行级锁(row-level locking)和表级锁,默认为⾏级锁

表级锁和行级锁对比：

• 表级锁： MySQL中锁定粒度最大的⼀种锁，对当前操作的整张表加锁，实现简单，资源消耗也⽐较少，加锁快，不会出现死锁。其锁定粒度最⼤，触发锁冲突的概率最⾼，并发度最低，MyISAM和 InnoDB引擎都⽀持表级锁。
• 行级锁： MySQL中锁定粒度最小的⼀种锁，只针对当前操作的行进⾏加锁。 行级锁能⼤⼤减少数据库操作的冲突。其加锁粒度最⼩，并发度⾼，但加锁的开销也最⼤，加锁慢，会出现死锁。

InnoDB存储引擎的锁的算法有三种：

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定⼀个范围，不包括记录本身
• Next-key lock：record+gap 锁定⼀个范围，包含记录本身

14. Mysql的行级锁 – 共享锁和排他锁

共享锁

有称之为S锁、读锁。
当前线程对共享资源加共享锁，其他线程可以读取此资源、可以继续追加共享锁，但是不能修改此资源、不能追加排他锁。
语法：select id from t_table lock in share mode;
多个共享锁可以共存，共享锁与排他锁不能共存。

排他锁

又称之为X锁、写锁。
当前线程对共享资源加排他锁，其他线程不允许读取此资源，不允许追加共享锁，不允许修改此资源，不允许追加排他锁。
语法：
update t_table set a =1; // 数据库的增删改操作默认都会加排他锁
select * from t_table for update;// for update也是一种增删改
排他锁是独占的，不会与其他锁共存。

15. 乐观锁和悲观锁

乐观锁与悲观锁是逻辑上的锁。

乐观锁

乐观锁：乐观地认为，并发问题很难发生。
乐观锁虽然认为并发问题很难发生，但并不是不会发生，所以也会有措施防止问题真的产生：每次数据修改都自增版本号version。
进行数据读取时，并不加锁，而是同时读取当前的版本号version1；在对数据进行修改时，要判断当前的版本号version2是否等于之前的版本号version1。
版本号不匹配，则代表着并发问题已产生，所以需要回滚此次操作。
实现方式：版本号机制、CAS。

悲观锁

悲观锁：悲观地认为，并发问题极易发生。
悲观锁认为并发问题极易发生，所以每次操作，无论读写，都会对记录加锁，以防止其他线程对数据进行修改。
实现方式：数据库的行锁、读锁和写锁。

16.索引的底层实现(B+树，为何不采用红黑树，B树)

MySQL索引并没有采用B树而是采用B+树，B+树相对于B树具有以下特点：
①非叶子节点不存储data,只存储索引(冗余)，可以放更多的索引。
②叶子节点包含所有索引字段。
③叶子节点用指针连接，提高区间访问的性能。

在Mysql中，索引一般为bigint类型占8个字节，对于索引后面的指针占用6个字节，而Mysql给第一层大节点的限制内存大小为16KB，因此大概可以存储16KB/(6+8)B=1170个索引，同样第二层也可以存储1170个，而对于高度为3的B+树来说，假设第三层的索引和数据占用1KB大小，可以存放16KB/1KB=16个索引(加数据)，因此总共可以存储1170 x 1170 x 16 = 21,902,400个数据，2000多万条数据，令人诧异！！

内容参考链接：https://blog.csdn.net/qq_37989738/article/details/105045096

17. 简单介绍一下你的MySQL内外连接的了解

外连接分为内连接、左连接、右连接

内连接 inner join
根据某个条件连接两个表共有的数据所有字段；
语句：

select 表1查询的字段，表2查询的字段 from 表1 inner join 表2 on 条件；

左连接 left join
根据某个条件以及左边的表连接数据，右边的表没数据的话则填null；
语句：

select 表1查询的字段，表2查询的字段 from 表1 left join 表2 on 条件； // 只改变了连接的语句，其他写法相同

右连接 right join
根据某个条件以及右边的表连接数据，左边的表没数据的话则填null；
语句：

select 表1查询的字段，表2查询的字段 from 表1 right join 表2 on 条件； // 只改变了连接的语句，其他写法相同

18. 史上最全的select加锁分析(Mysql)

详情内容请参考：https://blog.csdn.net/fuqianming/article/details/99936967