mysql基础-常见面试题-基础篇

这是我参与8月更文挑战的第6天，活动详情查看：8月更文挑战

概述

mysql面试在后端面试中是必不可少的环节之一，作为曾经被面试过以及面试了100+来人的我来说，掌握以下的基础面试还是很有必要。

面试题

1、描述下什么是数据三范式

- 第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
- 第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
- 第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y
复制代码

2、分别描述下DML、DDL、DCL

- DML（data manipulation language）：数据管理语言，它们是SELECT、UPDATE、INSERT、DELETE，这4条命令是用来对数据库里的数据进行操作的语言
- DDL（data definition language): 数据定义语言，DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接  和约束等初始化工作上，他们大多在建立表时使用
- DCL（Data Control Language) : 数据控制语言，是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL
复制代码

3、myisam与innodb的区别

- InnoDB支持事物，而MyISAM不支持事物
- InnoDB支持行级锁，而MyISAM支持表级锁
- InnoDB支持MVCC, 而MyISAM不支持
- InnoDB支持外键，而MyISAM不支持
- InnoDB不支持全文索引，而MyISAM支持。
复制代码

4、超键、候选键、主键、外键分别是什么？

- 1、超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
- 2、候选键：是最小超键，即没有冗余元素的超键。
- 3、主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
- 4、外键：在一个表中存在的另一个表的主键称此表的外键。
复制代码

5、一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

- 如果表的引擎是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；
- 如果表的引擎是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。
复制代码

6、哪个存储引擎执行 select count(*) 更快，为什么？

- MyISAM更快，因为MyISAM内部维护了一个计数器，把表的总行数存储在磁盘上，可以直接调取；
- InnoDB没有将表的总行数存储在磁盘上，需要全表扫描累加行数，所以数据表越大越耗时；InnoDB这样做的苦衷与其支持事务有关，由于多版本并发控制（MVCC）的原因，InnoDB表“应该返回多少行”是不确定的。
复制代码

7、count(*) 、count(1) 和 count(列名) 的区别？

执行效果对比
- count(*) 包括了所有列，相当于行数，在统计结果的时候，不会忽略列值为NULL；
- count(1) 包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL；
- count(列名) 只包括列名那一列，在统计结果的时候，会忽略列值为NULL的计数；

执行效率对比
- 列名为主键，count(列名) 效率最优；
- 如果表只有一个字段（不为主键），则count(*) 效率最优；
- 列名不为主键，count(1) 比 count(列名)快；
- 如果表有多个列并且没有主键，则count(1) 比count(*)快；
复制代码

8、MySQL中 in 和 exists 的区别？

- exists：对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录时，条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录，则当前loop到的这条记录被丢弃；exists的条件就像一个 bool 条件，当能返回结果集则 为true，不能返回结果集则为false。
- select * from A where exists (select * from B where A.id in：in查询相当于多个or条件的叠加。select * from A where A.id in (select id from B);
-  如果查询的两个表大小相当，那么用 in 和 exists 差别不大；
-  如果两个表一个较小，另一个是大表，则子查询表大的用exists，子查询表小的用 in;
复制代码

9、UNION 和 UNION ALL的区别？

- 两者都是将结果集合并为一个，两个要联合的SQL语句，字段个数必须一样，而且字段类型要“相容”（一致）；
- UNION 在进行表连接后会去掉重复的数据记录（效率较低），而UNION ALL则不会去掉重复的数据记录；
- UNION会按照字段的顺序进行排序，而UNION ALL只是简单的将两个结果合并就返回；
复制代码

10、为什么要用索引？

- 通过唯一性索引可确保数据的唯一性。 
- 加快数据的检索速度（大大减少检索的数据量），这是建索引最主要的原因。 
- 加快表之间的连接。 
- 在使用分组和排序字句进行数据检索的时候，可以减少分组和排序时间。 
- 可以在查询过程中，使用优化隐藏器，提供系统的性能。
复制代码

11、索引的分类

详细可以参考我的前一篇文章：mysql基础-索引篇

物理存储角度
- 聚簇索引
- 非聚簇索引，也叫辅助索引

数据结构角度
- B+数索引
- Hash索引
- 全文索引（5.6之前仅在存储引擎为MyISam时可以）
- R-tree索引

逻辑角度
- 主键索引：是一种唯一索引，不允许空值
- 单列索引：每个索引只能包含单个列，一个表中可以多个单列索引
- 组合索引：每个索引至少包含2个列字段，查询服从最左分配原则
- 唯一索引：增加此索引的列在表中的值必须唯一
- 空间索引：针对空间列字段加的索引
复制代码

12、数据库索引的原理，为什么要用 B+树，为什么不用二叉树？

- 当从算法逻辑上讲，二叉树的查找速度和比较次数是最小的；
- 但是由于数据库索引是存储在磁盘上的，所以必须考虑磁盘IO的问题，磁盘IO是比较耗时的操作； 
- 当数据量比较大的时候，索引的大小可能有几个G，是不可能全部加载到内存中的； 
- 做法是逐一加载每一个磁盘页，这里的磁盘页对应着索引树的节点；
- 索引树的高度（层级）就是需要的磁盘IO次数； 
- 在相同数据量的情况下，B+**树的高度是小于二叉树的，数据量越大差距越明显。
复制代码

13、哪些情况需要创建索引？

1、主键自动建立唯一索引；
2、频繁昨晚查询条件的字段；
3、查询中与其他表关联的字段，外键关系建立索引； 
4、单键/组合索引的选择问题，高并发下倾向创建组合索引； 
5、查询中排序的字段，排序字段通过索引访问大幅度提高排序速度； 
6、查询中统计或分组字段；
复制代码

14、哪些情况不需要创建索引？

- 表记录太少；  
- 经常增删改的表；  
- 数据重复且分布均匀的表字段；  
- 频繁更新的字段不适合创建索引（会加重IO负担）；  
- where 条件里用不到的字段不创建索引；
复制代码

15、锁的分类

详细的可以见我的另外一篇文章：mysql基础-锁

对数据操作的类型分类
1、读锁（共享锁）:针对同一份数据，多个读操作可以同时进行，不会互相影响。
2、写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

对数据操作的粒度分类
1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率高，并发度最低。（MyISAM和Memory存储引擎采用的是表级锁）
2、行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小；发生锁冲突的概率最低，并发度最高。（InnoDB支持行级锁，也支持表级锁，默认行级锁）
3、页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。
4、适用：从锁的角度来说，表锁适合已查询为主，只有少量按索引条件更新数据的应用。行锁适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。
复制代码

16、描述下悲观锁和乐观锁

- 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
- 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
复制代码

17、InnoDB的行锁分类

1、InnoDB实现了2种行锁类型

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁。

2、为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（都是表锁）

意向共享锁（IS）：事务打算给数据行加行共享锁，必须先取得该表的IS锁。

意向排它锁（IX）：事务打算给数据行加排他锁，必须先取得该表的IX锁。
复制代码

18、事务的基本要素

原子性（Atomicity） ：整个事务中的所有操作，要么全部完成，要么全部不完成，不能停滞在中间某个环节。事务执行过程中发生错误，会被回滚到事务开始前的状态。
致性（Consistency） ：在事务开始之前和事务结束后，数据库的完整性约束没有被破坏。
隔离性（Isolation） ：一个事务的执行不会被其他事务干扰。即一个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不会互相干扰。
持久性（Durability：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库中，不会被回滚。
复制代码

19、事务隔离级别

1、Read-Uncommitted（读未提交）：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

2、Read-Committed（读已提交）：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

3、Repeatable-read（可重复读）：对同一个字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。MySQL默认隔离级别。

4、Serializable（可串行化）：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰。该级别可以防止脏读、不可重复读、幻读。
复制代码

20、事务日志的分类

redo log（重做日志）
undo log（回滚日志）
复制代码

21、redo log（重做日志）实现持久化和原子性

- 在InnoDB存储引擎中，事务日志通过redo log 和日志缓存（InnoDB Log Buffer）实现
- 事务开启时，事务中的操作，都会先写入存储引擎的日志缓存中，在事务提交之前，这些缓存的日志都需要提前刷新到磁盘上持久化，这就是“日志先行”（Write-Ahead logging）.
- 当事务提交之后，在Buffer Pool中影射的数据文件才会慢慢刷新到磁盘。此时如果宕机，那么当系统重启进行恢复时，可以根据redo log中记录的日志，把数据库恢复到奔溃前的一个状态。未完成的事务，可以继续提交或者选择回滚，这基于恢复的策略而定。
- 在系统启动的时候，就已经为redo log分配了一块连续的存储空间，以顺序追加的方式记录redo log，通过顺序IO改善性能。所有事务共享redo log的存储空间，它们的redo log按语句的执行顺序，一次交替的记录在一起。
复制代码

22、mysql日志种类

1、错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
2、查询日志：记录所有对数据库请求的信息，不论这些请求是否得到正确的执行。
3、慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。
4、二进制日志：记录数据库执行更改的所有操作。
5、中继日志：也是二进制日志，用来给slave库恢复。
6、事务日志：重做日志（redo log）和回滚日志（undo log）。
复制代码