深入理解MySQL（常见面试题）

1、索引的优缺点是什么？

优点：

• 使用索引可以大大加快数据的检索速度（减少检索量）
• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性

缺点：

• 创建索引和维护索引需要耗费时间
• 索引需要使用物理文件存储，会耗费一定的空间

2、B树和B+树之间的区别？

• B树：一个节点中可以有很多个元素，元素是有序的
• B+树：一个节点中可以有很多个元素；有序的；叶子节点之间有指针指向相邻的叶子节点；非叶子节点的元素都会冗余一份在叶子节点；

在MySQL官网中，是这么介绍的：

将节点比作是page（事实上也确实是以页为单位），root page point to the leaf pages ; leaf pages can also point to each other ，根节点会指向叶子节点，叶子节点之间也会有指针指向彼此（双向的指针 ）

3、InnoDB中的B+树是怎么产生的？

• InnoDB中一页多大：InnoDB_page_size = 16384 ~ 差不多16kb
• InnoDB存数据时，先要在内存中开辟至少一页的内存（16kb），然后当要存入磁盘时，是一页一页的存；从磁盘里读取数据时，也是一页一页的读；使用page这样的逻辑单位，可以减少io次数，提高查询效率
• 当插入多条数据时（这里设置的主键是非自增的），主键大小不按顺序插入时，查询所有数据的结果却发现是按照主键递增的顺序返回结果 ，这是为什么？

因为InnoDB在插入数据时就会进行排序，如下图的某一页的结构。当插入用户数据时，会根据主键的大小按照递增的顺序来插入到用户数据区域，形成一个链表，这样的好处是**当下次查询一个元素时，可以通过比较大小很快确定元素在的位置，不用遍历全部的元素 ** ，但是插入的效率会有所降低；因此用innoDB时，建议设置主键自增，这样会优化插入性能。非自增的话，每次插入都要比较id来插入到数据区域，经常要打乱原来的顺序，这样性能肯定会下降。

InnoDB中的B+树的产生

接回上面，当用户数据区域链表越来越长时，这时候查询效率就会变的很低，如何解决呢？InnoDB的优化策略是加上一个页目录，页目录是将用户数据区域分组，“组长”便是这组中主键值最小的，将它放入到页目录中，这样就将用户数据区域分为一组一组的，当再次查询数据时，先查页目录，和页目录中的值比较大小以确认在哪个分组，然后在数据区域里找到数据；如下图：

当这一页都插满了之后，就会再开辟一页，然后继续按照这样的数据结构来排序，这一页其实就是一个节点 。页与页之间会有指针指向下一页，如下图：

当数据非常多，开辟的页（节点）也越来越多，这个时候又形成一个很长很长的页链表，InnoDB同样选择再加一层来进行优化。再分出一层来存储页的分组。将每一页的页目录的最小值存在新加的一层，当查询数据时，从上往下查，减少遍历次数，提高查询效率！

看得出，这其实就演变成了一颗B+树。这是一个两层的B+树，同理，当数据越来越多，继续往上面加层数！每一页就是一个leaf page ，上面的是root page 。这样就解释了为什么MySQL官网中将节点叫做页，因为页才是最恰当的。同样，这也解释了为什么非叶子节点的元素的值都会在叶子节点冗余一份，因为根节点的值就是取自于叶子节点，查询时从上往下，不断确定分组–确定页。

4、InnoDB如何支持范围查找能走索引？

• 叶子节点存储的是完整数据（称为数据页 ），而非叶子节点存储的是索引（称为索引页 ），如上面生成B+树的例子中，存的是主键索引。找到了索引也就找到了数据，这就是聚簇索引 ，主键索引就是一种聚簇索引，一般情况下，主键会默认创建聚簇索引，且一张表只能有一个聚簇索引。
• 当查询时根据的字段是索引时，是从上往下查询，走索引的，可以迅速确定到页，然后找到数据，这样会非常的快就能查找到数据

非范围查询，如 select * from user where id = 6 ; 根据索引页直接找到id=6在哪一页，然后找到数据

范围查询，如 select * from user where id > 6;先执行id=6时的操作，利用索引，然后在数据页中，大于就往右走小于就往左走----这就是为什么说数据页之间是双向链表 。因此根据索引范围查询也是走索引的

• 而当查询的字段根据的是非索引字段时，不能从索引页查，而是从叶子节点（数据页）查找，一条一条的遍历—这称为全表扫描
• 因此对于经常要查询的字段建议加上索引，能极大的提高查询速度

5、为什么要遵守最左前缀原则才能利用到索引？

• 什么是最左前缀匹配原则？

最左前缀匹配原则是指，在使用联合索引时，MySQL会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在 与联合索引中最左侧字段相匹配的字段，就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成。所以与where语句后面的查询字段的顺序没有关系，是根据联合索引里从最左边的字段来看查询条件里有没有相匹配的字段

• 在使用联合索引时，B+树的存储时这样的，这也叫作非聚簇索引（辅助索引）

用innoDB时，建议用主键自增，这样会优化插入性能。非自增的话，每次插入都要比较id来插入到数据区域，经常要打乱原来的顺序，这样性能肯定会下降。

例如现在联合索引的字段是（a，b，c），那么索引页存储的是联合字段的值，数据页存储的是 联合索引索引的值和主键值。这样的目的是什么呢？

当查询 select * from user where （a=1and b=1and c=1）时，先根据索引页找到索引为111的页，然后在数据页中，根据主键1，回表到主键索引表中的数据页找到这条数据行。辅助索引访问数据总是需要二次查找，第一次找到主键值，第二次根据主键值找到具体的数据行

那么为什么不遵循最左前缀原则就会导致索引失效（用不上索引）？

不遵循最左前缀原则，其实就是你给的查询条件的字段都不匹配联合索引的最左的字段。也就是说，当联合索引是（a，b，c）时，你的查询条件里没有根据a字段，例如（b，c），这时是无法从索引页开始查找的，因为是从联合索引的第一个字段开始查询，而第一个字段就不匹配，就走不了索引页了 ，只能从数据页一条一条查找–全表扫描。

注意：查询条件的字段顺序和联合索引的字段顺序不一致是没有关系的，因为是根据联合索引的字段开始从最左端匹配，只要提供了联合索引最左端得到字段，就遵循了最左前缀原则

6、为什么范围查找会导致索引失效？

对于查询，并不一定就是走索引快。

例如联合索引（b，c ，d），现在要查找 select * from user where b>1。如果从索引开始找可不可以呢？当然可以，这是遵循最左原则。查找时，先从索引页开始找，找到b=1的，然后到数据页，找b>1的主键的值，然后回表查询这些主键所对应的数据的全部字段。这样一定是最快的吗？要知道，任何查询都有一种方式是全表查询，在这个查询情况下，如果直接全表查询，就是将所有的数据页一页一页的遍历，遍历完就能查询到，在这种情况，全表查询效率要比索引查询还快，因此索引就失效了，InnoDB会选择走全表查询

所以是否走索引要根据情况而定，并不是什么时候走索引都是最快的

范围查找时更精确一点，利用索引会更快！

7、覆盖索引是什么

例子还是上面的，联合索引（b，c，d），当查询条件为 select b from user where b>1;

对于这样的查询，走索引的话，先从索引页找到b=1，然后在数据页中找到b>1的页，这个时候，因为我们要查找的是b，而不是要所有。而b字段的值就是在数据页中保存，不需要通过主键去主键索引里回表查询，直接就能查找到b>1的b的数据，这就是覆盖索引。这样是很快的。如果select a 呢？也是覆盖索引！a是主键，在数据页中存的是主键的值和联合索引的值，因此查a的话也不需要去回表，可以直接查找到，这就是覆盖索引。（不需要二次回表查询 ）

当查询语句为 select b from user; 这种情况数据库又是如何查询的呢？

没有查询条件，那么是不是就一定不用索引走全表扫描？其实不是的。InnoDB会比较性能。第一种当然就是全表扫描，扫描主键索引的叶子节点，一页一页的扫描，然后将字段b的所有数据查询出来；第二种，是在（b,c,d）联合索引中，叶子节点存储的联合索引的值和主键值，扫描联合索引的叶子节点同样可以将b的所有数据查询到。但是联合索引中的叶子节点保存的是不完整的字段—只有联合索引字段和主键，而主键索引的叶子节点保存的是完整的数据，那么联合索引的叶子节点相比主键索引页数会更少，查询当然会更快。因此，这种情况下，InnoDB依旧是走索引查询–bcd联合索引。

8、order by 为什么会导致索引失效？

当查询语句为 select * from user order by (b,c,d);

一样的，会有两种走法，比较性能然后选择性能高的：

• 走全表扫描—主键索引的叶子节点，需要额外排序，但是不用回表
• 走索引扫描—联合索引（b,c,d）的叶子节点，因为没有where查询，所以不能从上往下，只能扫描叶子节点。此时是不需要排序的，因为叶子节点就是按照索引排序的，但是因为查询的是*，需要回表查询，找到相应的主键然后回表查出所有数据，八条数据就需要回八次表！

那么哪种更快呢？当数据量少的时候，显然全表扫描会更快一点，虽然要额外排序但是因为数据少，所以排序的时间非常短，和回八次表相比是很微小的，因此这种情况InnoDB也会选择走全表扫描，索引失效

但是当改为 select b from user order by (b,c,d);

这个时候，全表扫描还是像上面那样的步骤；但是索引扫描就省去了回表这一操作，所以肯定会走索引

9、关于字段的数据类型转换导致索引失效

int a

varchar b

现在建立b的索引

select * from user where a = 1；—会走索引

select * from user where b = “1”；—会走索引

select * from user where a = “1”；—会走索引

select * from user where b = 1；—不会走索引

对于第一种和第二组当然没问题，会走索引，a是int类型，查询a=1的数据，走主键索引；b是字符串类型，查询b=“1”的数据，走b索引；

而对于第三种情况，a是int类型，现在查询条件是有没有a等于一个字符串的数据，这个时候，InnoDB会将“1”转化为数值类型的1，然后查询a=1，所以也会走主键索引；

对于第四种情况，同样要将字符转换为数值，这里要做的是把这个“字段”转化为从varchar转换为int，如何把字段转化为数值呢，需要把b字段的所有字符数据都转换为int类型 ，这可不是一个小工程，是很难办到的，并且会改变索引的b+树，因为b索引的b+树里存的是字符，现在要改为对于的int数字，这样显然是非常耗内存和性能的。因此InnoDB走的是全表扫描，不走索引。

在InnoDB中，涉及对字段进行操作（包括查询条件包含a+1之类的字段操作），都会导致用不了索引

10、MySQL聚簇索引和非聚簇索引的区别

都是B+树的数据结构。

• 聚簇索引：将数据存储和索引放在一起，如根节点（索引页）放索引，叶子节点（数据页）放数据 ，并且是按照顺序组织，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的。即：只要索引是相邻的，那么对应的数据一定是相邻地存放在磁盘上的。主键索引就是聚簇索引，InnoDB一定有主键—如果不主动设置，则会使用unique索引，没有unique索引则会使用数据库内部的一个行的隐藏id来当做主键索引。

优势：

查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要二次查询效率（没有索引覆盖的情况）更高

聚簇索引对于范围查询的效率更高，因为其数据是按照大小排列的

聚簇索引适合用在排序的场合

劣势：

维护索引很昂贵，特别是插入新行或者主键被更新导致要分页的时候

• 非聚簇索引：也叫作辅助索引，叶子节点存储的不是数据，而是数据行的地址（存储索引和主键），根据索引查找到数据行再根据数据行的位置去磁盘查找数据。即先根据索引找到叶子节点中的位置，然后根据主键回表（主键索引）查询。这种相当于是建立在主键索引之上，因此也叫作辅助索引。

MyISAM使用的是非聚簇索引，没有聚簇索引。如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势，因为索引所占空间小

11、事务的ACID特性如何得到保证？

事务 —逻辑上的一组操作，要么都执行，要么都不执行

例如在转账操作中，A给B转账，使得A的钱减少，B的钱增加，事务会把这两个操作可以看成逻辑上的一个整体，要么都成功（A减少B增加），要么就都不成功。不存在A减少了B却增加的情况，那样也不合理

事务的ACID特性

• Atomicity：原子性。事务是最小的执行单位，不可分割。确保动作要么全部完成，要么完全不起作用（如果能拆解，会出现动作没完成但是里面的小动作完成）
• Consistency：一致性。执行事务前后，数据保持一致。数据不会凭空消失，A钱减少，那么相应的B钱就会增加
• Isolation：隔离性。并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
• Durability：持久性。一个事务被提交后，它对数据库中的数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

如何保证ACID特性？

InnoDB引擎有两个日志（特有的）： undo log和redo log ，mysql服务端有日志 bin log

• Atomicity：由undo log 日志保证，它记录了需要回滚的日志消息，事务回滚时撤销已经执行成功的sql；

例如，当服务器错误、操作系统崩溃等不可抗因素，或者事务执行过程中使用rollback指令回滚，对于这种执行了一半的事务，可能已经修改了很多数据，而InnoDB则会通过undo log 将只完成一半的事务回滚，撤销已经执行了的sql，没有完成的事务必须回滚到执行前的状态，来保证事务的原子性

• Consistency：由其他三大特征保证，保证了原子性、持久性、隔离性----数据库的一致性才能得到保证；数据的一致性通过程序代码来保证业务上的一致性从而得到保证

• Isolation：由MVCC保证

MVCC是什么

MVCC即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。MVCC使得大部分支持行锁的事务引擎，不再单纯的使用行锁来进行数据库的并发控制，取而代之的是把数据库的行锁与行的多个版本结合起来，只需要很小的开销，就可以实现非锁定读，从而大大提高数据库系统的并发性能。

MVCC使用了一种不同的手段，每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照 ，写者写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读者来说是不可见的。

MVCC实现的读写不阻塞正如其名：多版本并发控制---->通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本。

InnoDB存储引擎对MVCC的实现

在Repeatable Read 和 Read Committed 两个隔离级别下，如果是执行普通的select语句，则会使用一致性非锁定锁（MVCC），在Repeatable Read 下MVCC实现了可重复读和防止部分幻读

MVCC的实现依赖于：隐藏字段、Read View 、undo log 。在内部实现中，InnoDB通过数据行的DB_TRX_ID和Read View 来判断数据的可见性，如不可见，则通过数据行的DB_ROLL_PTR找到undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建Read View之前已经提交的修改和该事务本身做的修改

隐藏字段：在内部，InnoDB存储引擎为每行数据添加了三个隐藏字段—当前行创建时的版本号和删除时的版本号，还有一列是回滚指针用于回滚事务。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

ReadView：主要用来做可见性判断，里面保存了“当前对本事务不可见的其他活跃事务”

undo-log:当事务回滚时用于将数据恢复到修改前的样子；MVCC，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过undo log 读取之前的版本数据，以此实现非锁定读

• Durability：内存 + redo log来保证，mysql修改数据时，同时在内存和redo log记录这次操作，如果出现宕机时可以从redo log恢复，具体如下：

当执行更新年龄为19的sql语句时：

…，拿到查询的语句，把age改为19，然后调用引擎API接口，写入这一行数据，InnoDB引擎把数据保存在内存中，同时记录redo log，此时redo log进入prepare状态，然后告诉执行器，执行完了随时可以提交，执行器收到通知后记录binlog，然后调用引擎接口，提交，redo log为提交状态，更新完成。

为什么redo log要先进入prepare状态，然后再是提交状态？

现在的顺序是，先写redo log，但是不立马提交，而是记录binlog之后，再提交redo log。

如果写完redo log后立马提交，然后再写binlog，假设写完redo log后立马提交，然后机器挂了，binlog日志并没有写入 ，那么重启，通过redo log可以恢复数据，但是binlog没有记录该数据，后续机器备份时会丢失这一条数据(主从复制，备份也是从binlog日志中读取数据)

如果写完binlog 然后写redolog，写完binlog时机器异常重启，由于没有redolog本机无法恢复这一条记录，但是binlog又有记录，会造成数据不一致

写完 binlog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性 （恢复也能恢复，备份也有记录）

12、并发事务的控制方式有哪些

MySQL中并发事务的控制方式无非就两种：锁和MVCC。锁可以看做是悲观控制的模式，MVCC可以看作是乐观控制的模式

• 锁 控制方式下会通过锁来显示控制共享资源而不是通过调度手段，MySQL主要通过读写锁来实现并发控制

  • 共享锁：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取
  • 排他锁：又称写锁/独占锁，事务在修改记录的时候获取排它锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，其他事务不能再对这条记录加任何类型的锁

  读写锁可以做到读读并行，但是无法做到写读、写写并行。

• MVCC（多版本并发控制） ，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本，通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号唯一

13、MySQL锁有哪些，如何理解

• 按锁粒度分类：

行锁：锁某行数据，锁粒度最小，并发度高

表锁：锁整张表，锁粒度最大，并发度低

间隙锁：锁的是一个区间

• 按对数据操作的类型分类：

共享锁：也就是读锁，一个事务给某行数据加了读锁，其他事务可以给它再加读锁，但是不能加写锁。多个读操作可以同时进行不会互相影响

排它锁：也就是写锁，一个事务对某行数据加了写锁，在当前操作没完成之前，其他事务不能再加读锁和写锁。

注意的是：即便某行数据加了读锁或者写锁，但如果现在一个事务只是对数据去读而不是加锁，是可以读到的

• 还可以分为：

悲观锁：上面的行锁、表锁都是悲观锁，一旦要用就加锁，不管其他事务要不要用

乐观锁：并不会真正的去锁某行记录，CAS机制/版本号

InnoDB有哪几类行锁？

• 记录锁（Record Lock）：单个行记录上的锁
• 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身
• 临键锁（Next-Key Lock）：记录锁+间隙锁。锁定一个范围，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

14、MySQL慢查询如何优化？

• 检查是否走了索引，如果没有则优化SQL利用索引
• 检查所利用的索引，是否是最优索引
• 检查所查字段是否都是必须的，是否查询了过多字段，查出来了多余数据
• 检查表中数据是否过多，是否应该进行分库分表
• 检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源

15、如何实现分库分表

• 当数据库不足以支撑并发量，当表无法装下再多的数据，这时候采用分库分表来进行优化----前提是，SQL优化都已经用过了，还需要优化的情况下，才选择实现分库分表

• 分库分表：将原本存储于单个数据库上的数据拆分到多个数据库，把原来存储在单张数据表的数据拆分到多张数据表中，实现数据拆分，从而提升数据库操作性能。分为两种方式：

  • 水平拆分：将数据分散到多张表，设计分区键。每张表的字段都是一样，是将数据分散。

    ​ 分库：每个库结构一样，数据不一样，没有交集。库多了可以缓解io和cpu压力

    ​ 分表：每个表结构一样，数据不一样，没有交集。表数量减少可以提高sql执行效率、减轻cpu压力

  ​

  • 垂直拆分：将字段拆分为多张表，需要一定的重构 ----将一张表里的多个字段拆到几个表中去

    ​ 分库：每个库结构、数据都不一样，所有库的并集为全量数据

    ​ 分表：每个表结构、数据都不一样，至少有一列交集用于关联数据，所有表的并集为全量数据

例如，当一个表有几百万条数据，有十几个字段，水平拆分就是，将几百万条数据分出一部分在另一个分库里，分库和原库结构完全一样，分表和原表结构也一样，就是数据不同；垂直拆分则是，将表中的字段分出一部分到分表里，这样分库和原库的结构和数据都不一样，分表和原表的结构和数据也不一样。

一个是按表的数据来分；一个是按字段来分

尽量不做分库分表，会带来很多问题，如查询等都要重新排序

16、MyISAM和InnoDB存储引擎有什么区别

• 是否支持行级锁

MyISAM支持表级锁，不支持行级锁；而InnoDB两者都支持，默认是行级锁。MyISAM一锁就是锁住了整张表，并发性非常不好；相比之下InnoDB在并发的情况下性能更好

• 是否支持事务

MyISAM不提供事务支持。

InnoDB提供事务支持，实现了SQL标准定义的四个隔离级别，具有commit和rollback事务的能力。并且，InnoDB默认使用的REPEATABLE-READ（可重复读） 隔离级别是可以解决幻读问题发生的

SQL标准定义了四个隔离级别：

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

​ 脏读：读到了其他事务未提交的数据，未提交意味着这些数据可能回滚，最终并不会存在数据库中，而读到了这些未提交的数据，相当于读到了不存在的数据

​ 幻读：某一次的查询操作所得到的数据状态无法支撑后续的业务。例如，查询某记录是否存在，结果是不存在，于是insert，但是插入时却发现记录存在，无法插入，此时就发生了幻读

​ 不可重复读：在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据不一致

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

​ REPEATABLE-READ（可重复读） ：对同一字段的多次读取结果是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍可能发生

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

• 是否支持外键

MyISAM不支持，而InnoDB支持

• 是否支持数据库异常崩溃后的安全恢复

MyISAM不支持，而InnoDB支持

使用InnoDB的数据库在异常崩溃后，数据库重新启动时会保证数据库恢复到崩溃前的状态，依赖于redo log

• 是否支持MVVC

MyISAM不支持，InnoDB支持

• 索引实现不一样

MyISAM和InnoDB都是使用B+Tree作为索引结构，但是两者的实现方式不太一样

• 性能

InnoDB的性能比MyISA更强大

17、一条SQL语句在MySQL中具体的查询/更新流程

具体功能：

• 连接器：身份认证和权限相关 （登录MySQL）的时候

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作。如果账号密码校验通过，就会到权限表中查询该用户的所有权限。

• 查询缓存：执行查询语句时，会先从缓存中查询，如果命中缓存，直接返回查询结果

缓存是以key-value的形式缓存在内存中，key是查询语句，value是结果集。如果缓存击中，直接返回结果集给客户端；如果没击中继续走后面的组件，完成后也会将结果缓存起来供下一次使用

MySQL查询不建议使用缓存，因为查询缓存失效在实际业务场景中会非常频繁，当你对一个表更新时，这个表上的所有缓存数据都会消失，因为该功能的使用场景很少，所以在MySQL8.0版本后将缓存的功能删除了

• 分析器：没有命中缓存，sql语句会经过分析器，分析器主要是看这条sql语句要干什么，再检查语法是否错误

词法分析：一条sql语句有多少个字符串组成，提取关键字，select，查询的表，字段名等等

语法分析:判断你输入的sql语句是否正确，是否符合MySQL的语法

• 优化器：按照mysql认为最优的方案执行

按照它认为的最优执行方案去执行。例如多个索引的时候该如何选择索引，多表查询时如何选择关联顺序等

• 执行器：执行语句，然后从存储引擎返回数据

执行前先校验该用户有没有权限，没有权限会返回错误消息，有权限则调用引擎的接口，返回接口执行的结果

前面五个组件的功能都可以归于Service层

存储引擎层：

• 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB，MyISAM，Memory等多个存储引擎，其中InnoDB引擎有自有的日志模块redolog模块。现在最常用的存储引擎就是InnoDB，它从MySQL5.5版本开始就被当做默认存储引擎了

eg：查询

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

• 首先校验账户密码、权限，校验失败直接返回错误消息，校验成功，进入查询缓存，如果缓存击中，直接返回结果集，如果没击中，进入分析器
• 分析器进行词法分析，提取SQL语句的关键元素，例如上面语句，提取 select ,提取表名 tb_student，查询所有的列，查询条件等等，然后再判断这个SQL语句是否有语法错误，比如关键字是否正确，检查没问题进入优化器
• 优化器进行确定执行方案，优化器根据自己的优化算法进行选择执行效率最好的一个方案（有时候并不是最好的），确认后就准备开始执行
• 执行前先校验该用户有没有权限，没有权限会返回错误消息，有权限则调用引擎的接口，返回接口执行的结果

eg：更新 ，InnoDB模式

update tb_student A set A.age='19' where A.name=' 张三 ';

• 首先是按着查询的流程走，先查询张三这一条数据，当然如果有缓存会直接缓存返回，没击中缓存就按前面查询的步骤来查到这条数据
• 拿到查询的语句，把age改为19，然后调用引擎API接口，写入这一行数据，InnoDB引擎把数据保存在内存中，同时记录redo log，此时redo log进入prepare状态，然后告诉执行器，执行完了随时可以提交
• 执行器收到通知后记录binlog，然后调用引擎接口，提交，redo log为提交状态
• 更新完成

为什么用两个日志

最开始MySQL并没有InnoDB引擎，（InnoDB引擎是其他公司以插件形式插入MySQL的），之前MySQL就是采用binlog的日志模块。现在引入InnoDB引擎后，redo log是InnoDB引擎特有的（自带的），InnoDB引擎就是通过redo log来支持事务的，有crash—safe的能力（数据库发生重启时之前提交的记录会恢复），所以才有了两个日志模块

为什么redo log要先进入prepare状态，然后再是提交状态？

现在的顺序是，先写redo log，但是不立马提交，而是记录binlog之后，再提交redo log。

如果写完redo log后立马提交，然后再写binlog，假设写完redo log后立马提交，然后机器挂了，binlog日志并没有写入，那么重启，通过redo log可以恢复数据，但是binlog没有记录该数据，后续机器备份时会丢失这一条数据

如果写完binlog 然后写redolog，写完binlog时机器异常重启，由于没有redolog本机无法恢复这一条记录，但是binlog又有记录，会造成数据不一致

写完 binlog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性 （恢复也能恢复，备份也有记录）。

假设 redo log 处于预提交状态，binlog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

• 判断 redo log 是否完整，如果判断是完整的，就立即提交。
• 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。