mysql （面试）系列持续更新中-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43415329/article/details/136479737

本文讲述了事务的ACID特性，InnoDB与MyISAM的区别，包括支持事务、外键、索引结构和性能差异，以及事务隔离级别、幻读和脏读的概念。此外，还详细解释了MySQL查询的生命周期、优化器如何工作，以及优化SQL执行计划的重要性和方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

慢慢来，就很快，一切旨在简易，让天下没有难学的知识

事务的四大特性（ACID）？

原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用
一致性： 事务执行前后，数据保持一致，多个事务对同一个数据读取的结果是相同的
隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

MyISAM 和 InnoDB 的区别？

1）InnoDB 支持事务，而 MyISAM 不支持。

2）InnoDB 支持外键，而 MyISAM 不支持。因此将一个含有外键的 InnoDB 表转为 MyISAM 表会失败。

3）InnoDB 和 MyISAM 均支持 B+ Tree 数据结构的索引。但 InnoDB 是聚集索引，而 MyISAM 是非聚集索引。

4）InnoDB 不保存表中数据行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量记录了整个表的行数，速度相当快（注意不能有 WHERE 子句）。

事务的隔离级别有哪些？

可重复读（Repeatable Read）默认的事务隔离级别
读未提交（Read Uncommitted）
读已提交（Read Committed）
串行化（Serializable）

幻读，脏读，不可重复读

事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄地提交，然后事务A再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。
事务A、B交替执行，事务A被事务B干扰到了，因为事务A读取到事务B未提交的数据,这就是脏读
在一个事务范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。

InnoDB 为什么设计 B+ 树索引？

B+树非叶子节点不存储数据，仅存储键值，能存储更多的关键字信息等（innodb默认16kb)，B+树也会更加矮壮，查询数据进行的磁盘IO次数会再次减少，性能比较稳定olog(n)，效率更快
B+树叶子存储的数据是按照顺序排列的（链表结构），可以进行范围，排序，分组或者去重查询。
B树节点中不仅存储键值，也会存储数据，并且叶子节点之间没有关系，由于B树每个节点都存储了一条记录的所有数据，因此每次IO开销大

mysql查询生命周期

查询生命周期：客服端发送查询请求到服务端解析生成执行计划——>执行——>返回响应结果。

执行周期

客户端发送请求校验用户权限并将一条查询发送给服务器
服务器先查询缓存，如果命中缓存则直接返回数据，否则将执行执行计划
服务器将对SQL进一步解析（主要通过语法规则验证和解析生成解析树）；预处理（预处理会根据MySQL规则进一步检查解析树是否合法，比如判断某一列数据是否存在）
优化器生成执行计划（比如优化索引的使用、表的连接顺序）
调用存储引擎的API来执行查询，并根据执行计划给出的指令逐步执行得出结果
响应数据给客户端，同时缓存一份数据到缓存中

执行周期详解

查询缓存

在分析一个查询语句之前，如果查询缓存是打开的，那么 MySQL 会优先检查这个查询是否命中缓存中的数据（检查是通过一个对大小写敏感的哈希查找实现的）。当命中缓存之后，MySQL 会检查一次用户权限，如果权限没问题，MySQL 会跳过所有其他阶段，直接从缓存中拿到结果并返回。

语法解析器和预处理

MySQL 通过关键字将 SQL 语句进行解析，并生成一棵对应的 “解析树”。MySQL 解析器将使用 MySQL 语法规则校验和解析查询。例如，它验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。

预处理则根据一些 MySQL 规则进一步检查解析树是否合法，例如，这里检查数据表和数据列是否存在，还会解析名字和别名，看看他们是否有歧义。下一步预编译器会验证权限。

查询优化器

当语法树被认为合法时，优化器会将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同结果。优化器的作用就是找到这其中最好的执行计划。

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：

重新定义表的关联顺序(多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序)

优化MIN()和MAX()函数(找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值文)

提前终止查询(比如：使用Limit时，查找到满足数量的结果集后会立即终止查询)

优化排序(在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多)

举例来源：

感谢程序员视界写的很详细

SHOW STATUS LIKE 'Last_query_cost'
+-----------------+------------+
|  Variable_name  | Value      |
+-----------------+------------+
| Last_query_cost | 1030.47800 |
+-----------------+------------+

上述结果表示 MySQL 的优化器认为大概需要 1030个数据页的随机查找才能完成上述的查询。很多原因会导致 MySQL 优化器选择错误的执行计划，如下：
【1】统计信息不准确：MySQL 依赖存储引擎提供的统计信息来评估成本，但有的偏差可能非常大。例如，InnoDB 因为其 MVCC 的架构，并不能维护一个数据表的行数的精确统计信息。
【2】执行计划中的成本估算不等同实际执行的成本：所以即使统计信息精准，优化器给出的执行计划也可能不是最优的。例如某个执行计划虽然需要读取更多的页面，但是它的成本却更小。因为如果这些页面都是顺序读或者页面都已经在内存中的话，那么它的访问成本将很小。MySQL 层面并不知道哪些页面在内存中、哪些在磁盘上，所以查询实际执行过程中到底需要多少次物理 I/O 是无法得知的。
【3】MySQL 的最优可能和我们想的最优不一样：我们希望执行时间尽可能短，但是MySQL 只是基于其成本模型选择最优的执行计划，有时候并不是最快的执行方式。所以，我们根据执行成本选择执行计划并不是完美的模型。
【4】MySQL 从不考虑其他并发执行的查询：可能会影响到当前查询的速度。
【5】MySQL 也并不是任何时候都是基于成本的优化：有时也基于一些固定的规则。
【6】MySQL 不会考虑不受其控制的操作的成本：例如执行存储过程或者用户自定义函数的成本。
【7】优化器有时候无法估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。
MySQL 的查询优化器使用了很多优化策略来生成一个最优的执行计划。优化侧率可以简单分为两种：静态优化和动态优化。静态优化可以直接对解析树进行分析，并完成优化。动态优化则和查询的上下文有关，也可能和很多其它因素有关，例如WHERE 条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估，可以认为这是 “运行时优化”。下面是一些 MySQL 能够处理的优化类型：
【1】重新定义关联表的顺序：数据表的关联并不总是按照在查询中指定的顺序进行。决定关联的顺序是优化器很重要的一部分功能。
【2】使用等价变换规则：MySQL 可以使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。例如：（5=5 AND a>5）将被改写为 a>5；
【3】将外连接转化成内连接：并不是所有的 OUTER JOIN 语句都必须以外连接的方式执行。
【4】优化 COUNT()、MIN() 和 MAX()：要找到某一列的最小值，只需要查询对应 B-Tree 索引最左端的记录，MySQL 可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点，在 B-Tree 索引中，优化器会将这个表达式作为一个常数对待。
【5】预估并转化为常数表达式：当 MySQL 检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。
【6】覆盖索引扫描：当索引中的列包含所有查询的列时，MySQL 就可以使用索引返回需要的数据，而无须查询对应的数据行。
【7】子查询优化：MySQL 在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问。
【8】提前终止查询：如果发现已经满足查询需求，MySQL 总是能够立刻终止查询。典型的例子就是 LIMIT 子句。
【9】列表IN() 的比较：很多数据库系统中，IN()完全等同于多个 OR 条件子句，因为这两个是完全等价的。在MySQL中不成立，IN()列表中的数据先排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个 O(logn)复杂度的操作，等价地转化成 OR 查询的复杂度为 O(n)，对于 IN() 列表有大量取值的时候，MySQL 的处理速度将会更快。

执行计划

MySQL 并不会生成查询字节码来执行查询。MySQL 生成查询的一颗指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行 EXPLAIN EXTENDED 后，再执行 SHOW WARNINGS，就可以看到重构出的查询。MySQL 总是从一个表开始一直嵌套循环、回溯完成所有表关联。所以，MySQL 的执行计划总是如下图所示：是一颗左侧深度优先的树。

sql优化

explain 分析低效 sql 的执行计划

mysql> explain select sum(amount) from customer a , payment b where a.customer_id= b.customer_id and a.email='JANE.BENNETT@sakilacustomer.org'\G  

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 10.00
        Extra: Using where

select_type: 表示 select 类型，常见的取值有：
- simple：简单表，及不使用表连接或者子查询
- primary：主查询，即外层的查询
- union：union 中的第二个或后面的查询语句
- subquery：子查询中的第一个 select
Extra的值为Using index condition，表示已经使用了索引下推。

Mysql性能优化：什么是索引下推？ - 知乎

table ：输出结果集的表
type ：表示 mysql 在表中找到所需行的方式，或者叫访问类型，常见类型性能由差到最好依次是：

all：全表扫描，mysql 遍历全表来找到匹配的行
index：索引全扫描，mysql 遍历整个索引来查询匹配的行
range：索引范围扫描，常见于<、<=、>、>=、between等操作
ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行
eq_ref：类似 ref，区别就在使用的索引时唯一索引，对于每个索引的键值，表中只要一条记录匹配；简单的说，就是多表连接中使用 primary key 或者 unique index 作为关联条件
const/system：
单表中最多有一个匹配行，查起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key 或者唯一索引 unique index 进行查询。
null：mysql 不用访问表或者索引，直接就能够得到结果

show warnings 命令

执行explain 后再执行 show warnings，可以看到sql 真正被执行之前优化器做了哪些 sql 改写

MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = 'JANE.BENNETT@sakilacustomer.org'\G

MySQL [sakila]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                     |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = 'JANE.BENNETT@sakilacustomer.org')) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)