MySQL面试必备

以下内容会进行不定时持续更新，希望大家可以在评论区对文中错误内容进行评论，谢谢大家。

1.MySQL中一条SQL是如何执行的？

答：MySQL是C/S架构，SQL是从客户端向服务端发送，经过服务端一系列处理，返回给客户端数据；

MySQL服务端的架构大致分为两层数据业务服务层和数据存储引擎；

服务层主要包括连接管理器，缓存，SQL语法解析器，SQL优化器，SQL执行器；

数据存储引擎主要负责数据的存储和读取；

连接管理器的作用：1.验证连接的用户名密码 2.到权限表查询用户拥有的权限

缓存的一些逻辑：1.如果SQL在缓存中返回缓存中的数据 2.如果有对某个表的update操作，将此表的缓存失效

SQL语法解析器：

SQL执行器：

总结：

       1.一条SQL命令会通过客户端先到达数据业务服务层的连接管理器，通过连接管理器来验证客户端是否有权限访问服务端

       2.如果数据业务服务层开启了缓存，那么会查询SQL缓存，如果缓存命中直接返回

       3.缓存未命中，SQL语法解析器开始对SQL进行语法分析，判断SQL是否正确，如果错误直接返回错误信息

       4.SQL优化器开始生成SQL的执行计划，并且选择查询索引

       5.SQL执行器开始执行执行计划，操作底层数据存储引擎返回结果

2.MySQL的日志系统了解过吗，讲一下？

答：redo log和binlog。

当我们执行update操作时，如果你的MySQL数据引擎是InnoDB，MySQL会先将记录写到redo log中，并更新内存，InnoDB引擎会在空闲的时候把数据更新到磁盘。

redo log的日志大小是固定的，并且redo log上存在两个标记write pos和checkpoint，write pos记录当前写入日志的位置，checkpoint记录要开始擦除并将擦除数据更新到磁盘的位置，当write pos到达checkpoint的位置说明日志满了，会进行擦除落盘的操作。有了redo log即使MySQL出现异常挂了，InnoDB引擎也能恢复已经写入到日志的数据。

由于redo log是InnoDB特有的，因此当我们使用其他数据引擎的时候也需要对我们的数据进行保护让他拥有恢复数据的能力，binlog就提供了这个能力，binlog是在Server层实现的，因此支持所有的数据存储引擎，和redo log不同，binlog记录的是逻辑日志，例如给某一行id=1的数据的a字段值加一这样的逻辑。redo log记录的物理存储信息，例如给某个数据页上修改哪些数据。

另外当我们执行更新操作时，如果你的数据存储引擎是InnoDB,那么写redo log和写binlog会有一个两阶段提交的操作，目的是使得这两个日志数据一致！

3.了解MySQL的事务吗？能讲一下？

答：首先事务是指当我们进行一组数据库操作之后，要么全部成功，要么全部失败。在MySQL中事务是由数据存储引擎提供支持的。

事务的四大特性ACID(Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

当多个数据库事务同时执行时，会出现，脏读，幻读，不可重复读等问题，因此产生了隔离级别这个概念。

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ），他们的执行效率依次降低。

读未提交：一个事务还没有提交他的数据变更就能够被另外一个事务所看到

读已提交：一个事务提交后他的变更才能被其他事务看到

可重复读：一个事务执行过程中看到的数据和他最初看到看到的数据一致

串行化：事务加锁顺序执行。

当我们开启事务，数据库会为我们创建视图，当事务隔离级别为可重复读时，视图会在事务启动时创建；读已提交时，会在SQL开始执行时创建；读未提交不存在视图直接返回记录的最新值；串行化会直接加锁。

事务隔离的实现：每条记录更新时都会记录一个可回滚的日志，日志上记录了此条记录的各个状态，当事务执行失败是就会回滚。

MySQL在实现MVCC时用到了一致性读视图，用于支持read committed和Repeatable Read两种隔离级别的实现。

InnoDB的行数据会有多个版本，在Repeatable Read级别下，事务启动时会先生成自己的事务id，每次事务更新数据时都会生成一个新的数据版本然后把事务id赋值给这个数据版本的事务id。当我们更新数据时，会判断这个数据的id是不是我们的id如果是我们就可以更新，如果不是，那么我们就会阻塞知道另外一个持有最新版本的数据的事务提交以后我们才能够使用最新版的数据，而且更新数据时读取的数据都是采用当前读的方式类似于CAS。

4.讲讲索引吧（InnoDB）？

答：在InnoDB中每一个索引都会建立一颗索引树，索引树采用的数据结构是B+树。那么为什么innoDB要采用B+树这种数据结构来作为索引数据结构呢？主要是因为对于操作系统来讲我们的索引树的深度可以看作为我们对磁盘的IO次数，普通的平衡二叉树随着数据量的增加深度会非常大，从而导致我们对IO的次数增加，而B+树采类似于跳表的形式以及是一颗多叉树，有效的减少了树的深度，从而降低了IO的访问次数。

索引的类型分为两种：主键索引和非主键索引，主键索引树的叶子节点存放的是整行数据，称为聚簇索引；而非主键索引树的叶子节点存放的内容是主键的值。

当我们进行查询的时候如果使用到了非主键索引会先查询到数据的主键Id，然后再通过主键索引再进行一次查询，我们称为回表。这时候我们可能回想能不能优化查询使得查询次数减少到一次，我们通常会查询某个数据的id例如：select id from xxx where k=1假设我们为k建立了索引，那么我们可以直接通过k索引树查询到id的值，因此只需要查询一个索引树，这个过程称为索引覆盖。

当我们查询过程需要建立索引，但是又不是每一个查询都很频繁，单独为它建立索引就会有一些浪费，我们会考虑使用联合索引，而且由于我们的索引树B+树结构，使得我们只要满足最左前缀原则就能够很高效的查询到我们的数据。当我们的数据满足最左前缀时，可能还需要判断其他字段是否满足条件，在MySQL5.6之前会通过回表查询到那一行数据然后再进行字段值比较，在MySQL5.6之后增加了索引下推的优化，可以在遍历索引过程中对索引包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

普通索引和唯一索引：对于普通索引，当我们查询到第一个满足条件的记录后需要继续查找下个记录，直到碰到第一个不满足条件的记录；对于唯一索引，当我们查询到第一个满足条件的记录后会立即停止查询。

另外普通索引可以使用change  buffer（内存更新缓存），当更新操作频繁的时候，所有的操作都先在内存中进行修改，当merge后才进行磁盘写入，提升速度。但是当写入后立马进行查询，会在更新change buffer后立即写入磁盘，触发merge过程，非但没有减少io次数还增加了change buffer的维护代价。

MySQL对查询是索引的选择：MySQL在进行查询优化时会对查询的扫描行数进行判断，并对索引类型判断是主键索引还是非主键索引是否需要额外开销，因此有时我们虽然建立了索引但是优化器并没有选择我们想要的索引进行遍历查询。所以我们可以通过explain进行sql执行计划分析，使用force index来强行选择一个索引。

5.讲一下数据库的锁吧？

首先我们的数据库锁分为全局锁，表锁，行锁。

全局锁常用于全库备份。

表锁有两种一种是表数据锁，一种是表元数据锁。

行锁在InnoDB事务中在需要的时候会加上，但并不是不需要了立即释放，而是等到事务结束才会释放。因此最有可能影响并发度的锁尽量往后放。当一个事务要更新一行，如果有另一个事务拥有这一行的锁，那么这个事务就会被阻塞，直到另外一个事务释放锁。

6.count（*）为什么那么慢，count（1），count（id）怎么选择？

在不同的MySQL引擎中，count(*)的实现方式不同,MyISAM引擎会把一个表的总行数存储到磁盘上，因此执行count（*）直接返回个数，效率很高。

在InnoDB中count（*）就比较慢了，因为它需要把数据一行一行的从存储引擎里读取出来然后判断数据的事务版本id找到自己的版本的数据再进行条件判断，最后进行累加，因此效率非常低。

count（*），count(1)，count(id)，count(字段)怎么选择：

count（主键id）：InnoDB会遍历整张表，把每一行的id都取出来，server层获取到id后进行累加。

count（1）:InnoDB会遍历整张表，但不去取值，server层按行累加。

count（字段）:InnoDB会先查询出字段的值然后进行空判断，然后再累加。

count（*）：InnoDB进行了专门的优化，不取值，不做空校验，按行累加。

因此效率排序:count(字段)<count(主键id)<count(1)≈count(*)