java面试八股文（MySQL篇）

MySQL常见面试题总结

一、数据库存储引擎

        MySQL数据库支持的存储引擎有InnoDB、MyISAM、CSV、Memory、Merge、archive等等，可以使用show engines查看系统所支持的引擎类型。

        InnoDB是mysql数据库存储引擎的首选，InnoDB是事务型数据库的首选，支持事务安全表（ACID）,InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎，InnoDB是为处理巨大数据量的最大性能设计，InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主表内存中缓存数据和索引二维持它自己的缓冲池，InnoDB支持外键完整性约束(FOREIGN KEY)

特点：

行锁设计

支持MVCC

支持外键

提供一致性非锁定读

被设计用来最有效地利用以及使用内存和CPU

二、数据库三范式

1、第一范式

        规定表中的每个列都应该是不可分割的最小单元；

2、第二范式

        是在满足第一范式的基础上，规定表中非主键列不存在对主键的部分依赖，也就是说一张表只描述一件事情；

3、第三范式

        是在满足第一第二范式的基础上，规定表中的列不存在对非主键列的传递依赖；

总结:

        使用数据库三范式的优势是：表的结构更简单、优雅，表的逻辑和条理性更强，并且使用三范式可以很大程度的减少表中的冗余数据，很好的节省了数据库的存储资源。

三、SQL优化

基础

1、查询SQL尽量不要使用select *，而是具体字段；

2、避免在where子句中使用or来连接条件，可以使用union all分成两条语句写；

        使用or可能会使索引失效，从而全表扫描，对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的。

3、使用varchar代替char；

4、尽量使用数值替代字符串类型；

5、查询尽量避免返回大量数据，通常采用分页；

6、优化like语句：模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效；

7、索引不宜太多，一般5个以内；

8、where限定查询的数据；

9、避免在where中对字段进行表达式操作；

高级

10、批量插入性能提升；

11、批量删除优化；

12、伪删除设计；

13、提高group by语句的效率，先分组再过滤；

14、删除冗余和重复的索引；

15、inner join 、left join、right join，优先使用inner join；

16、in子查询的优化；

17、尽量使用union all替代union。

四、简单说一说drop、delete与truncate的区别

        速度,一般来说: drop> truncate >delete

        drop(丢弃数据): drop table 表名 ，直接将表都删除掉，在删除表的时候使用。

        truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长id又从1开始，在清空表中数据的时候使用。

        delete（删除数据） : delete from 表名 where 列名=值，删除某一列的数据，如果不加 where 子句和truncate table 表名作用类似。

        truncate 和不带 where 子句的 delete、以及 drop 都会删除表内的数据，但是 truncate 和 delete 只删除数据不删除表的结构(定义)，执行drop语句，此表的结构也会删除，也就是执行 drop 之后对应的表不复存在。  

五、索引的优缺点

优点：

# 创建索引可以大大提高系统的性能。

# 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

# 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

# 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

# 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

# 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

# 创建和维护索引需要耗费时间，这种时间随着数据量的增加而增加，这样就降低了数据的维护速度。

# 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

六、索引分类

        普通索引：MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建；

        唯一索引：索引列中的值必须是唯一的，但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建；

        主键索引：特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建；组合索引：组合表中多个字段创建的索引，遵守最左前缀匹配规则；

        全文索引：只有在 MyISAM 引擎上才能使用，同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。

七、事务

数据库的事务

        数据库的事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。\n\n事务的典型应用场景，如转账。

事务的四大特性（ACID）

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

        一致性： 事务执行前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；

        隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

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

事务的并发问题

        脏读、幻读和不可重复读。

脏读、幻读和不可重复读

        脏读：一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。

        不可重复读：一个事务中两次读取的数据的内容不一致。 事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。

        幻读：一个事务中两次读取的数据量不一致。 系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

        不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

八、 存储过程

1、存储过程

        存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

2、存储过程和函数的区别

1）返回值的区别：函数有1个返回值，而存储过程是通过参数返回的，可以有多个或者没有。

2）调用的区别：函数可以在查询语句中直接调用，而存储过程必须单独调用。

九、Union 和 Union All 的区别

        Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序

        Union All：对两个结果集进行并集操作，包括重复行，不进行排序\n\nUnion 因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All。

更多内容获取可以关注博主，记得评论加收藏哦，感谢支持！

后续内容更精彩！