MySQL-面试知识点汇总

1. DQL相关

 

2. DDL、DML、DCL相关

 

3.架构相关

3.1 MySQL的复制原理以及流程

主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行。

主从复制的作用

• 主数据库出现问题，可以切换到从数据库。
• 可以进行数据库层面的读写分离。
• 可以在从数据库上进行日常备份。

MySQL主从复制解决的问题

• 数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
• 负载均衡：降低单个服务器的压力
• 高可用和故障切换：帮助应用程序避免单点失败
• 升级测试：可以用更高版本的MySQL作为从库

MySQL主从复制工作原理

• 在主库上把数据操作记录到二进制日志
• 从库将主库的日志复制到自己的中继日志
• 从库读取中继日志的事件，将其重放到从库执行

3个线程以及之间的关联

• 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中
• 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中
• 从：sql执行线程——执行relay log中的语句

 

binlog：主数据库的二进制日志(Binary log)

binlog 有三种格式：

• Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL语句都会记录在 binlog 中。
• Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，记录某一条记录被修改成什么样子了。
• Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。

Relay log：从服务器的中继日志

基本原理流程

第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。

第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。

第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

 

4.存储引擎相关

4.1 简述MyISAM和InnoDB的区别

MyISAM：不支持事务；支持表锁；支持全文索引；count()有变量存储，无需查全表；非聚簇索引；不支持外键；不支持MVCC；

InnoDB：支持事务；支持行级锁；聚簇索引；支持外键；支持MVCC；

 

5.事务和锁相关

5.1 事务的ACID特性

(1) 原子性(Atomicity)：原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。

(2) 一致性(Consistency)：一致性是指事务执行前后，数据从一个合法性状态变换到另一个合法性状态。

(3) 隔离性(Isolation)：一个事务内部的操作及使用的数据对并发的其他事务是隔离的。

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

5.2 事务的四种特性是基于什么机制实现的

(1) 事务的隔离性是由锁机制实现的。

(2) 事务的原子性、一致性由undo log实现。

(3) 事务的持久性由redo log实现。

5.3 InnoDB支持的四种事务隔离级别

(1) 读未提交：可避免脏写，无法避免脏读、不可重复读、幻读。

(2) 读已提交（利用MVCC）：可避免脏写、脏读。

(3) 可重复读（利用MVCC）：可避免脏写、脏读、不可重复读、幻读。（InnoDB默认隔离级别）

(4) 串行化（利用加互斥读写锁）：可避免脏写、脏读、不可重复读、幻读。   

5.4 数据并发产生的问题

(1) 脏写：A事务修改了未提交的B事务修改过的数据

(2) 脏读：A事务读取到了未提交的B事务修改过的数据

(3) 不可重复读：A事务读取数据，B事务更新数据，A事务再次读的时候读取到了B事务更新过的数据

(4) 幻读：A事务从表中读取了一些行数据，B事务在该表中插入了一些新的行，A事务再次读取同一个表，发现多出几行。

5.5 MySQL中有哪几种锁

(1) 按照数据的操作类型划分：共享锁、排他(独占)锁。

(2) 按照数据操作粒度划分：表级锁、行级锁、页级锁。

(3) 按照对待锁的态度划分：乐观锁、悲观锁。

5.6 MySQL中InnoDB引擎的行锁是通过加在什么上实现的

   InnoDB是基于索引来完成行锁。

 

6.索引及底层原理相关

6.1 索引分类

(1) 普通索引：仅增加查询速度；

(2) 唯一索引：加速查询+列值唯一（允许null）；

(3) 主键索引：加速查询+列值唯一+全表只有一个（不允许null）；

(4) 组合(联合)索引：多个列组成一个索引。

6.2 索引失效的情况

(1) 对于联合索引，未遵循最左前缀规则导致索引失效；

(2) 计算、函数、类型转换(自动或手动)导致索引失效；

(3) 范围条件右边列索引失效；

(4) 不等于(!=)会导致索引失效；

(5) is null可以用到索引，is not null不能用到索引；

(6) like以通配符%开头索引失效；

(7) or 前的列建立了索引 or后面的列没有建立索引 会导致索引失效；

(8) 不同字符集进行比较前需要进行转换，会导致索引失效;

(9) 当MySQL分析全表扫描比使用索引快的时候不使用索引。

6.3 hash索引和B+索引的区别

(1) hash索引：hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据

(2) B+索引：B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

hash索引和B+索引的区别：

(1) hash索引无法进行范围查询，因为是直接用hash函数定位到主键id；而B+树因为维护了主键顺序（树结构的天然优势+非叶子结点按页存储主键值+叶子结点链表相连），所以可以进行范围查询。

(2) hash索引不支持使用索引进行排序，原理同上。

(3) hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测性。

(4) hash索引任何时候都避免不了回表查询数据，而B+索引在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。

(5) hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+索引的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

6.4 红黑树、B树和B+树的区别

（1）红黑树：

特点：左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

问题：尽量不让一棵树的单边变得太长而退化成链表，能有效地减少高度，高度变小降低了查找I/O的次数，性能比二叉树要好。但红黑树一个节点只能有两个子节点，虽然平衡了链表退化问题，但高度总体来看还是太高。

 

（2）B树：

特点：每个节点存储key和data。

问题：B-Tree在每一个节点存储了索引和数据，导致进行搜索的时候需要把索引和数据都加载到内存中，这样就不是很划算，内存资源这么宝贵，多存些索引岂不是更好。

（3）B+树：

 

特点：

(1) 非叶子节点不存储数据，只存索引（冗余），这样可以保证存放更多的索引

(2) 叶子节点存储所有索引字段

(3) 叶子节点用指针连接，提高区间访问性能

MyISAM存储引擎索引实现：

 

InnoDB底层存储引擎索引实现：

 

7.日志相关

7.1 MySQL有哪些日志

(1) undo 日志：用于支持事务的原子性、一致性，用于支持事务回滚以及MVCC

(2) redo 日志：用于支持事务的持久化

(3) 查询日志：记录所有对数据库请求的信息。

(4) 慢查询日志：将运行时间超过阈值的所有SQL语句都记录到慢查询的日志文件中

(5) 二进制日志：记录对数据库执行更改的所有操作。

(6) 中继日志：用于从机复制主机信息。

7.2 redo log和undo log的区别

(1) redo log：记录的是物理级别上的页修改操作，比如页号，偏移量，写入的数据，主要是为了保证数据的可靠性。

(2) undo log：记录的是逻辑操作日志，比如对某一行数据进行了insert操作，那么undo log就记录一条与之相反的delete操作。主要用于事务的回滚和一致性非锁定读。

 

8.调优相关

 

9.其他问题

9.1 MySQL中char与varchar的区别

字符串(文本)类型	特点	长度	长度范围	占用的存储空间
char(M)	固定长度	M	0<=M<=255	M个字节
varchar(M)	可变长度	M	0<=M<=65535	(实际长度+1)个字节

char：

(1) char(M)类型一般需要预先定义字符串长度。如果不指定(M)，则长度默认1个字符。

(2) 保存时如果数据的实际长度比char类型声明的长度小，则会在右侧填充空格已达到指定的长度。当检索char类型数据时，会自动去掉尾部的空格。

(3) 定义char类型字段时，声明字段的长度即为char类型字段所占的存储空间的字节数。

varchar：

(1) varchar定义时，必须制定长(M)，否则报错。

(2) MySQL5.0版本以上，varchar(20)；指的是20个字符。

(3) 检索varchar类型数据时会保留尾部空格。

(4) varchar类型的字段所占存储空间为字符串实际长度+1个字符。

        对于InnoDB表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分（所有数据行共用一个表头部分，这个标头部分存放着指向各有关数据列的指针），而且主要影响性能的因素是因为数据行使用的存储总量，所以使用char类型不见得会比使用varchar类型好。事实上，因为char类型通常要比varchar类型占用更多的空间，所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利。

 

9.2 数据库三大范式

第一范式：要求数据库表中的每一列必须是不可拆分的最小单元，也就是确保每一列的原子性，而不是集合。

比如A表中有一个"地址"字段，而"地址"可以再分为省、市、地区(县)、街道、详细地址，这样就违反了第一范式。

第二范式：满足1NF的基础上，要求：表中的所有列，都必需依赖于主键，而不能有任何一列与主键没有关系（一个表只描述一件事情）。第二范式负责消除表的无关数据。

比如用户表中有一列为"天气"，此表的主键"用户id"与"天气"字段并无联系，违反第二范式。

第三范式：满足2NF的基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

比如一个"学生表"，学号，姓名，所在院校，院校地址，院校电话等字段，这样此表就存在传递关，学号--> 所在院校 --> (院校地址，院校电话)。应该拆成两个表：

学生表：学号，姓名，所在院校

院校信息表：所在院校，院校地址，院校电话