【重难点】【MySQL 01】对 MySQL 的认识、和 Oracle 的区别、MySQL 5 和 MySQL 8 的区别、Explain 命令、MySQL 日志系统

【重难点】【MySQL 01】对 MySQL 的认识、和 Oracle 的区别、MySQL 5 和 MySQL 8 的区别、Explain 详解、redo/undo log、binlog 的区别

一、对 MySQL 的认识

MySQL 是一个关系型数据库，体积小速度快。使用标准的 SQL 语言，可以处理上千万条记录

二、和 Oracle 的区别

对事务的提交

MySQL 默认自动提交；而 Oracle 默认不自动提交，需要写 comit 命令

分页查询

MySQL 在 SQL 语句中使用 limit 就可以实现分页；Oracle 则需要用到伪列 ROWNUM 和嵌套查询

事务隔离级别

MySQL 是 read commited 的隔离级别；而 Oracle 是 repeatable read 的隔离级别

对事务的支持

MySQL 在 InnoDB 存储引擎的行级锁下才可支持事务；Oracle 则是完全支持事务

并发性

MySQL 以表级锁为主，对资源锁定的粒度很大，如果一个 session 对一个表加锁时间过长，会让其它 session 无法更新此表中的数据。虽然 InnoDB 引擎的表可以使用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，或者 SQL 语句没有使用索引，那么仍然使用表级锁；Oracle 使用行级锁，对资源锁定的粒度要小很多，并且加锁是在数据库中的数据行上，不依赖于索引，因此 Oracle 对并发性的支持要好很多

逻辑备份

MySQL 逻辑备份时需要锁定数据才能保证备份的数据是一致的，会影响业务增删改操作的使用；Oracle 逻辑备份时不需要锁定数据

复制

MySQL 复制服务器的配置简单，但主库出问题时，从库可能丢失一定的数据，且需要手动切换主从库；Oracle 既有推或拉式的传统数据复制，也有 dataguard 的双机或多机容灾机制，主库出现问题时，可以自动切换从库，但是配置管理较为复杂

性能诊断

MySQL 的诊断调优方法较少，主要有慢查询日志；Oracle 有各种成熟的性能诊断调优工具，能实现很多自动分析、诊断功能

此外，MySQL 是轻量型数据库，Oracle 是重量型数据库

除了以上几点明显的区别，MySQL 和 Oracle 还有许多不同，在此没有全部列出

三、MySQL 5 和 MySQL 8 的区别

性能

MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍。MySQL 在以下方面带来了更好的性能：读/写工作负载、IO 密集型工作负载、高竞争工作负载

NoSQL

MySQL 从 5.7 版本开始提供 NoSQL 存储功能，目前在 8.0 版本中这部分功能也得到了更大的改进。该项功能消除了对独立的 NoSQL 文档数据库的需求，而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性

窗口函数（Window Functions）

从 MySQL 8.0 开始，新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行当中，即窗口函数不需要 GROUP BY

隐藏索引

在 MySQL 8.0 中，索引可以被 “隐藏” 和 “显示”。当对索引进行隐藏时，它不会被查询优化器所使用。我们可以使用这个特性用于性能调试，例如我们先隐藏一个索引，然后观察其对数据库的影响。如果数据库性能有所下降，说明这个索引是有用的，然后将其 “恢复显示” 即可；如果数据库性能看不出变化，说明这个索引是多余的，可以考虑删除

降序索引

MySQL 8.0 为索引提供按降序方式进行排序的支持，在这种索引中的值也会按降序的方式进行排序

通用表表达式（Common Table Expressions CTE）

在复杂的查询中使用嵌入式表时，使用 CTE 使得查询语句更清晰

UTF-8 编码

从 MySQL 8 开始，使用 utf8mb4 作为 MySQL 的默认字符集

JSON

MySQL 8 大幅改进了对 JSON 的支持，添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数

可靠性

InnoDB 现在支持表 DDL（数据库定义语言） 的原子性，也就是 InnoDB 表上的 DDL 也可以实现事务完整性，要么失败回滚，要么成功提交，不至于出现 DDL 时部分成功的问题，此外还支持 crash-safe 特性，元数据存储在单个事务数据字典中

高可用性（High Availability）

InnoDB 集群为您的数据库提供集成的原生 HA 解决方案

安全性

对 OpenSSL 的改进、新的默认身份验证、SQL 角色、密码强度、授权

四、Explain 命令

在工作中，我们用于捕捉性能问题最常用的就是打开慢查询，定位执行效率差的 SQL，定位之后我们还需要知道该 SQL 的执行计划，比如是全表扫描还是索引扫描，这些都需要通过 Explain 命令去完成。Explain 命令是查看优化器如何决定执行查询的主要方法，可以帮助我们深入了解 MySQL 的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行 SQL 语句时哪种策略预计会被优化器采用

语法

1. EXPLAIN SELECT ……
2. EXPLAIN EXTENDED SELECT …… 将执行计划“反编译”成SELECT语句，运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句。Explain 只能解释 SELECT 操作，其它操作要重写为 SELECT 后查看执行计划
3. EXPLAIN PARTITIONS SELECT …… 用于分区表的 EXPLAIN

执行以下命令

explain select d1.age, t2.id from (select age,name from t1 where id in (1,2))d1, t2
where d1.age=t2.age group by d1.age, t2.id order by t2.id;

结果为

+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref    | rows | Extra                           |
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL   |    2 | Using temporary; Using filesort |
|  1 | PRIMARY     | t2         | ref   | age           | age     | 5       | d1.age |    1 | Using where; Using index        |
|  2 | DERIVED     | t1         | range | PRIMARY       | PRIMARY | 4       | NULL   |    2 | Using where                     |
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
3 rows in set (0.00 sec)

从中我们可以看到执行计划包含的信息有：id、select_type、type、possible_keys、key、key_len、ref、rows、Extra，接下来对它们进行简要介绍

id

包含一组数字，表示查询中执行 select 子句或操作表的顺序

select_type

表示查询中每个 select 子句的类型

select_type	说明
SIMPLE	查询中不包含子查询或者 UNION
PRIMARY	查询中包含复杂的子部分
SUBQUERY	在 SELECT 或 WHERE 列表中包含了子查询
DERIVED	用来表示包含在 FROM 子句中的子查询的 SELECT，MySQL 会递归执行并将结果放到一个临时表中。服务器内部称为 “派生表”，因为该临时表是从子查询中派生出来的
UNION	若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION
UNION RESULT	从 UNION 表获取结果的 SELECT 被标记为 UNION RESULT

type

表示 MySQL 在表中找到所需行的方式，又称 “访问类型”，常见类型如下（从上至下，性能从最差到最好）

1. ALL：Full Table Scan，MySQL 将遍历全表以找到匹配的行
2. index：Full Index Scan，index 与 ALL 的区别为 index 类型只遍历索引树
3. range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行
4. ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值得记录行
5. eq_ref：类似 ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用 primary key 或者 unique key 作为关联条件
6. const、system：当 MySQL 对查询某部分进行优化，并转化为一个常量时，使用这些类型访问。比如，将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。system 是 const 类型的特例，当查询的表只有一行的情况下 type 为 system
7. NULL：MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引里选取最小值可以通过单独索引查找完成

possible_keys

指出 MySQL 能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询是哟个

key

显示 MySQL 在查询中实际使用的索引，若没有使用索引，显示为 NULL

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的）

ref

表示上述表的连接匹配条件，即哪些列或变量被用于查找索引列上的值

rows

表示 MySQL 根据表统计信息及索引选用情况，估算地找到所需地记录所需要读取的行数

Extra

包含不适合在其它列中显示但十分重要的额外信息

• Using index：该值表示相应的 select 操作中使用了覆盖索引。MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件，包含所有满足查询需要的数据的索引称为覆盖索引
• Using where：表示 MySQL 服务器将在存储引擎检索行后再进行过滤。许多 where 条件里涉及索引中的列，当它读取索引时，就能被存储引擎检验，因此不是所有带 where 子句的查询都会显示 Using where，有时 Using where 的出现就是暗示查询可受益于不同的索引
• Using temporary：表示 MySQL 需要使用临时表来存储结果集，常见于排序和分组查询
• Using filesort：MySQL 中无法利用索引完成的排序操作称为 “文件排序”
• Using join buffer：该值强调了再获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能
• Impossible where：该值强调了 where 语句会导致没有符合条件的行
• Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
• Index merges：当 MySQL 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一种：
  • Using sort_union(…)
  • Using union(…)
  • Using intersect(…)

关于 Explain 的补充说明

• Explain 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响
• Explain 不考虑各种 Cache
• Explain 不能显示 MySQL 在执行查询时所做的优化工作
• 部分统计信息是估算的，并非精确值

五、MySQL 日志系统

MySQL 日志系统是数据库的重要组件，用于记录数据库的更新和修改。若数据库发生故障，可通过不同日志记录恢复数据库的原来数据。因此实际上日志系统直接决定着 MySQL 运行的稳健性

MySQL 的日志有很多种，如二进制日志（binlog）、错误日志、查询日志等，此外 InnoDB 存储引擎还提供了两种日志：redo log（重做日志）和 undo log（回滚日志）

1.重做日志（redo log）

重做日志（redo log）是 InnoDB 引擎层的日志，用来记录事务操作引起数据的变化，记录的是数据页的物理修改

重做日志的作用其实很好理解，我打个比方。数据库中数据的修改就好比你写的论文，万一哪天论文丢了怎么办呢？以防这种意外发生，我们可以在写论文的时候，每一次修改都拿个小本子记录一下，记录什么时间对某一页进行了怎么样的修改，这就是重做日志

InnoDB 引擎对数据的更新，是先将更新记录写入 redo log 日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。这就是所谓的预写式技术（Write Ahead Logging）。这种技术可以大大减少 IO 操作的频率，提升数据刷新的效率

值得注意的是，redo log 日志的大小是固定的，为了能够持续不断地对更新记录进行写入，在 redo log 日志中设置了两个标志位置，checkpoint 和 write_pos，分别表示记录擦除的位置和记录写入的位置。redo log 日志的数据写入示意图如下所示：

当 write_pos 标志到了日志结尾时，会从结尾跳至日志头部进行重新循环写入。所以 redo log 的逻辑结构并不是线性的，而是可看作一个圆周运动。write_pos 与 checkpoint 中间的空间可用于写入新数据，写入和擦除都是往后推移，循环往复的

当 write_pos 追上 checkpoint 时，表示 redo log 日志已经写满。这时不能继续执行新的数据库更新语句，需要停下来先删除一些记录，执行 checkpoint 规则腾出可写空间。checkpoint 触发后，会将 buffer 中的脏数据页和脏日志页都刷到磁盘。脏数据是指未刷到磁盘的数据

buffer pool

redo log 中最重要的概念就是缓冲池 buffer pool，这是在内存中分配的一个区域，包含了磁盘中部分数据页的映射，作为访问数据库的缓冲。当请求读取数据时，会先判断是否在缓冲池命中，如果未命中才会在磁盘上进行检索后放入缓冲池。当请求写入数据时，会先写入缓冲池，缓冲池中修改的数据会定期刷新到磁盘中，这一过程也称之为刷脏。因此，当数据修改时，除了修改 buffer pool 中的数据，还会在 redo log 中记录这次操作。当事务提交时，会根据 redo log 的记录对数据进行刷盘。如果 MySQL 宕机，重启时可以读取 redo log 中的数据，对数据库进行恢复，从而保证了事务的持久性，使得数据库获得 crash-safe 能力

redo log 日志写入

除了上面提到的对于脏数据的刷盘，实际上 redo log 日志在记录时，为了保证日志文件的持久化，也需要经历将日志记录从内存写入到磁盘的过程。redo log 日志可分为两部分，一是存在易失性内存中的缓存日志 redo log buff，二是保存在磁盘上的 redo log 日志文件 redo log file。为了确保每次记录都能够写入到磁盘中的日志中，每次将 redo log buffer 中的日志写入 redo log file 的过程中都会调用一次操作系统的 fsync 操作。fsync 操作用于同步内存中所有已修改的文件数据到存储设备

在写入的过程中，还需要经过操作系统内核空间的 os buffer，redo log 日志的写入过程如下所示：

2.二进制日志（binlog）

二进制日志 binlog 是服务层的日志，还被称为归档日志。binlog 主要记录数据库的变化情况，内容包括数据库所有的更新操作。所有涉及数据变动的操作，都要记录进二进制日志中。因此有了 binlog 可以很方便地对数据进行复制和备份，因而也常用作主从库的同步

这里 binlog 所存储的内容看起来似乎与 redo log 很相似，但是其实不然。redo log 是一种物理日志，记录的是实际上对某个数据进行了怎么样的修改。而 binlog 是逻辑日志，记录的是 SQL 语句的原始逻辑，只是 binlog 日志中的内容是二进制的，根据日志格式参数的不同，可能基于 SQL 语句、基于数据本身或者二者的混合。一般常用记录的都是 SQL 语句。物理的日志可看作是实际数据库中数据页上的变化信息，只看重结果，而不关注过程。逻辑的日志可看作是通过了某一种方法或者操作手段导致数据发生了变化，存储的是逻辑性的操作

同时，redo log 是基于 crash recovery，保证 MySQL 宕机后的数据恢复；而 binlog 是基于 point-in-time recovery，保证服务器可以基于时间点对数据进行恢复，或者对数据进行备份

事实上最开始 MySQL 是没有 redo 日志的，因为最开始 MySQL 是没有 InnoDB 引擎的，自带的引擎是 MyISAM。binlog 是服务层的日志，因此所有引擎都能够使用。但是光靠 binlog 日志只能提供归档的作用，无法提供 crash-safe 能力，所以 InnoDB 引擎就采用了 Oracle 的技术，也就是 redo log，这才拥有了 crash-safe 能力

在 MySQL 执行更新语句时，都会涉及到 redo log 日志和 binlog 日志的读写，一条更新语句的执行过程如下：

从上图可以看出，MySQL 在执行更新语句时候，在服务层进行语句的解析和执行，在引擎层进行数据的提取和存储。同时在服务层对 binlog 进行写入，在 InnoDB 内进行 redo log 的写入

不仅如此，在对 redo log 写入时有两个阶段的提交，一是 binlog 写入之前 prepare 状态的写入，二是 binlog 写入之后 commit 状态的写入

3.回滚日志（undo log）

回滚日志同样也是 InnoDB 引擎提供的日志，顾名思义，回滚日志的作用就是对数据进行回滚。当事务对数据库进行修改，InnoDB 引擎不仅会记录 redo log，还会生成对应的 undo log 日志。如果事务执行失败或调用了 rollback，导致事务需要回滚，就可以利用 undo log 中的信息将数据回滚到修改之前的样子

但是 undo log 和 redo log 不一样，它属于逻辑日志。它对 SQL 语句执行的相关信息进行记录。当发生回滚时，InnoDB 引擎会根据 undo log 日志中的记录做与之前相反的工作。比如对于每个数据的插入操作，回滚时会执行数据删除操作；对于每个数据的删除操作，回滚时会执行数据插入操作；对于每个数据的更新操作，回滚时会执行一个相反的数据更新操作。undo log 有两个作用，一是提供回滚，二是实现 MVCC（Multi-Version Concurrency Control，多版本并发控制）