MySQL面试题汇总

常规：

1、数据库三大范式

• 1NF : 表中字段的数据不可再拆分。(原子性)
• 2NF : 在满足第一范式的情况下，遵循唯一性，消除部分依赖。即，表中任意一个主键或任意一组联合主键，可以确定除该主键外的所有的非主键值。(一个表只能描述一件事情)
• 3NF : 在满足第二范式的情况下，消除传递依赖。即，在任一主键都可以确定所有非主键字段值的情况下，不能存在某非主键字段 A 可以获取 某非主键字段 B。

参考文章 数据库三大范式

2、主键和外键的区别？

• 主键：唯一标识一条记录。不能有重复的，不允许为空 。
• 外键：表的外键是另一表的主键, 用于和其他表建立联系。外键可以有重复的, 可以是空值。

3、CHAR 和 VARCHAR 的区别是什么？

        两者的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。

• CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。
• VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。

4、DECIMAL 和 FLOAT/DOUBLE 的区别是什么？

        DECIMAL 和 FLOAT 的区别是：

• DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。
• DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

5、NULL 和 ' ' 的区别是什么？

• 类型：null表示的是一个对象的值，而非一个字符串，而""表示的是一个长度为0的空字符串。
• 内存分配：null不分配内存空间;而""会分配内存空间。 如：
  • String aaa = null ;                                                                                                              表示声明一个字符串对象的引用，但指向为null（未指向任何的内存空间）；
  • String bbb = "";
    表示声明一个字符串类型的引用，其值为""空字符串（指向空字符串的内存空间）；
• 查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的。

6、Boolean 类型如何表示？

        MySQL 中没有专门的布尔类型，而是用 TINYINT(1) 类型来表示布尔值。TINYINT(1) 类型可以存储 0 或 1，分别对应 false 或 true。

7、MySQL 基础架构及执行过程

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除）。
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。
• 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
• 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

8、MySQL 存储引擎MyISAM 和 InnoDB 有什么区别？

        MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

        MySQL 5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎，之前默认存储引擎是MyISAM，两者区别如下：

• InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。
• MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。
  • InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别，并可以解决幻读问题（基于 MVCC 和 Next-Key Lock）。
• MyISAM 不支持外键，而 InnoDB 支持。
• MyISAM 不支持 MVCC，而 InnoDB 支持。
• MyISAM 和 InnoDB 都使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。
• MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。
• InnoDB 的性能比 MyISAM 更强大。

9、事务的四大特性

• 原子性(Atomicity)：事务是一个不可分割的工作单位，事务内的操作要么全部执行成功，要么全部回滚。

• 一致性(Consistency)：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

• 隔离性(Isolation)：一个事务所做的修改操作在提交之前，对于其他事务来说是不可见的。

• 持久性(Durability)：事务一旦被提交，它对数据库中数据的改变就是永久性的。

        redo log保证了持久性，undo log保证了原子性和一致性，锁和MVCC机制保证了隔离性。

10、MySQL事务隔离级别

        据库事务隔离级别分为四个等级,分别为:读未提交( read-uncommitted)、读已提交(read committed)、可重复读(repeatable read)、串行序列化(serializable)。

事务隔离要实际解决的问题：

• 脏读：读了其他事务未提交的数据。
• 不可重复读：同一事务先后读取同一条记录，但两次读取的数据不同。
• 幻读：在一个事务中，按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在。

        InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的，主要有下面两种情况：

• 快照读：由 MVCC 机制来保证不出现幻读。
• 当前读：使用 Next-Key Lock 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。

11、undo log和redo log的区别 ？

• redo log（重做日志）：是为了系统崩溃之后恢复数据用的，让数据库照着日志，把没做好的事情重做一遍。 
• undo log（回滚日志）：用于记录被修改前的信息，记录的是逻辑日志，作用：提供事务回滚和MVCC。
• redo log保证了事务的持久性，undo log保证了事务的原子性和一致性。

12、事务中的隔离性是如何保证的呢？

        事务的隔离性是由锁和mvcc实现的。

• 隐藏字段：
  • trx_id(事务id)：记录每一次操作的事务id，是自增的。
  • roll_pointer(回滚指针)：指向上一个事务版本的地址。
• undo log：
  • 回滚日志，存储老版本数据，形成一条版本链。
  • 版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表。
• readView：解决的是一个事务查询选择版本的问题。
  • 根据readView的匹配规则和当前的一些事务id判断该访问哪个版本的数据。
  • 不同的隔离级别快照读也不一样，最终的访问的结果不一样：
    • RC（读已提交） ：每一次执行快照读时生成ReadView。
    • RR（可重复读）：仅在事务中第一次执行快照读时生成ReadView，后续复用。

13、MySQL主从同步原理

        MySQL主从复制的核心就是二进制日志（binlog），里面记录了所有DDL和DML语句。

• 主库在事务提交时，会将数据变更记录在binlog中。
• 从库读取主库的binlog，写入从库的中继日志Relay log中。
• 从库读取中继日志，写入到自己的数据库中。

14、MySQL分库分表

• 水平分库：将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题。
• 水平分表：解决单表存储和性能的问题。
• 垂直分库：根据业务进行拆分，高并发下提高磁盘IO和网络连接数。
• 垂直分表：冷热数据分离，多表互不影响。

索引：

        索引是一种用于快速查询和检索数据的数据结构，本质可以看成是一种排序好的数据结构。

索引的优缺点：

• 优点：大大加快 数据的检索速度，降低数据库的IO成本。
• 缺点：创建索引和维护索引需要耗费大量时间，索引的存储也需要消耗一定的空间。

1、索引的底层数据结构选型

        索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

        下面分别介绍几种索引结构以及为什么要选择B+树做索引结构。

• 哈希表是键值对的集合，通过key可以快速地检索到对应的值，因此哈希表可以快速检索数据。然而，因为hash索引是精确查找，不支持范围查询，因此不使用hash作为索引结构。
• AVL树(平衡二叉查找树)解决了二叉查找树最坏情形退化成链表的情况，但是需要不断地旋转操作来保持树地平衡，I/O磁盘操作次数较多。
• 红黑树不和AVL树一样追求绝对平衡，只追求大致平衡，但因此红黑树的高度也会较高，检索数据时也需要较多的IO磁盘。
• B树，全称多路平衡查找树，B+ 树是 B 树的一种变体，二者区别如下：
  • B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他节点只存放 key，相当于索引。
  • B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  • B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

        选用B+树的主要原因:

• 阶数更多，路径更短。
• 磁盘读写代价 更低，非叶子节点只存储指针，叶子节点存储数据。
• B+ 树便于扫库和区间查询，叶子节点是一个双向链表。

2、MySQL中，如何定位慢查询?

       MySQL中也提供了慢日志查询的功能，可以在MySQL的系统配置文件中开启这个慢日志的功能，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中。

3、那这个SQL语句执行很慢, 如何分析呢？

        可以使用MySQL自带的explain来查看这条SQL语句的执行计划，如下图：

可以从以下三个方面进行分析：

• 通过key和key_len检查是否命中了索引。
• 通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描。
  • type 性能由好到差为：
  • system：查询系统中的表。
  • const：根据主键查询。
  • eq ref：主键索引查询或唯一索引查询。
  • ref：索引查询。
  • range：范围查询。
  • index：全索引扫描。
  • all：全盘扫描。
• 通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复。

4、什么是聚簇索引什么是非聚簇索引 ?

• 聚簇索引（聚集索引）：数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个，一般是主键索引。
• 非聚簇索引（二级索引）：数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个。

5、什么是回表查询?

        通过二级索引找到对应的主键值，再通过主键值到聚集索引中查找整行数据的过程。

6、什么是覆盖索引?

        覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。

• 使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
• 如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *

7、MYSQL超大分页怎么处理 ?

8、索引创建原则有哪些？

• 针对数据量较大，且查询比较频繁的表。(10w数据量)
• 针对常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
• 尽量使用联合索引，避免回表，提升效率。
• 控制索引的数量，索引越多，维护索引的成本越高，会影响到增删改的效率。
• 尽量选择区分度高的列作为索引。
• 如果是字符串类型的字段作为索引，且字段较长，建立前缀索引。
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。

9、什么情况下索引会失效 ?

• 违反最左前缀法则。（主要针对联合索引）
• 范围查询右边的列，不能使用索引 。
• 索引列上进行运算操作， 索引将失效。
• 字符串不加单引号，造成索引失效。(发生了类型转换)
• 以%开头的Like模糊查询，索引失效。（%结尾不会失效）

10、谈一谈你对sql的优化的经验

• 表的设计优化。
  • 如设置合适的数值类型（int、bigint、tinyint），字符串类型（char、varchar）
• 索引优化：索引创建原则、避免索引失效。
  • 见以上第8、9点。
• SQL语句优化。
  • 如：select语句指明具体字段，尽量避免使用select * ，防止回表查询。
  • SQL语句尽量避免造成索引失效的写法。
  • 尽量用union all代替union，union会去重，多一次过滤操作，效率低。
  • Join优化 能用inner join 就不用left join right join，如必须使用 一定要以小表为驱动。
• 主从复制、读写分离。
  • 数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。
• 分库分表。