Java面试之MySQL篇（2021更新）

1、三范式

• 1NF：列不可分，强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
• 2NF：有主键，非主键字段依赖主键，强调的是唯一性，一个表只说明一个事物。
• 3NF：非主键字段不能相互依赖，每列都与主键有直接关系，不存在传递依赖，即非主键字段不能依赖非主键字段。

2、反三范式

• 没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于DML的比例。但是反范式化一定要适度，并且在原本已满足三范式的基础上再做调整的。

3、说一下 ACID 是什么？

• 数据库事物的4个特性。
• Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
• Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
• Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

4、char 和 varchar 的区别是什么？

• char(n) ：固定长度类型，比如订阅 char(10)，当你输入"abc"三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。
• char 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。
• varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。
• 所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

5、MySQL 的内连接、左连接、右连接有什么区别？

• 内连接关键字：inner join；左连接：left join；右连接：right join。
• 内连接是把匹配的关联数据显示出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好与左连接相反。

6、说一下乐观锁和悲观锁？

• 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
• 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。
• 数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

7、SQL优化

• 在表中建立索引，优先考虑where、group by使用到的字段。
• 尽量避免使用select *，返回无用的字段会降低查询效率。
• 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描，优化方式：尽量在字段后面使用模糊查询。
• 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描，优化方式：可以给字段添加默认值0，对0值进行判断。
• 添加缓存。
• 定期将历史数据进行转储，当前表/查询历史表。
• …

8、创建索引的方式

--直接创建索引
CREATE INDEX indexName ON table_name (column_name)

--修改表结构添加索引
ALTER table tableName ADD INDEX indexName(columnName)

--创建表的时候直接指定索引
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  

9、索引查询失效的几种情况

• like 以%开头，索引无效（后缀可以有%）
• or语句前后字段没有同时建立索引，索引无效（当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效）
• 复合（组合）索引中，违背最左缀原则，索引失效（如复合索引ABC中，生效情况：A或AB或ABC，其它均失效）
• 数据类型出现隐式转化，索引无效（如varchar不加单引号的话可能会自动转换为int型）
• 对索引字段进行计算操作、字段上使用函数，索引无效（如DATE_FORMAT(create_time,'%Y-%m-%d') = '2021-11-17'）
• 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。