MySQL5.x与8.0-Toy模板网

这篇具有很好参考价值的文章主要介绍了MySQL5.x与8.0。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

大致区别

InnoDB

SQL DDL

SQL DML

Indexes

JSON

大致区别
1. 性能：MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍
- MySQL 8.0 在以下方面带来了更好的性能：读/写工作负载、IO 密集型工作负载、以及高竞争（"hot spot"热点竞争问题）工作负载
2. NoSQL：MySQL 从 5.7 版本开始提供 NoSQL 存储功能，目前在 8.0 版本中这部分功能也得到了更大的改进
- 该项功能消除了对独立的 NoSQL 文档数据库的需求，而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性
3. 窗口函数(Window Functions)：从 MySQL 8.0 开始，新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式
- 窗口函数与 SUM()、COUNT() 这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行当中
- 即窗口函数不需要 GROUP BY
4. 隐藏索引：在 MySQL 8.0 中，索引可以被“隐藏”和“显示”
- 当对索引进行隐藏时，它不会被查询优化器所使用
- 我们可以使用这个特性用于性能调试，例如我们先隐藏一个索引，然后观察其对数据库的影响
- 如果数据库性能有所下降，说明这个索引是有用的，然后将其“恢复显示”即可；
- 如果数据库性能看不出变化，说明这个索引是多余的，可以考虑删掉
5. 取消查询缓存：MySQL 8.0 出于性能和可维护性方面的考虑取消了查询缓存，通过使用索引、更好的查询计划优化、以及缓存结果集而不是整个查询
- 这些方法更有效地提高了查询性能，同时避免了查询缓存可能引入的问题
6. 降序索引：MySQL 8.0 为索引提供按降序方式进行排序的支持，在这种索引中的值也会按降序的方式进行排序
7. 通用表达式(Common Table Expressions CTE)：在复杂的查询中使用嵌入式表时，使用 CTE 使得查询语句更清晰
8. UTF-8 编码：从 MySQL 8 开始，使用 utf8mb4 作为 MySQL 的默认字符集
9. JSON：MySQL 8 大幅改进了对 JSON 的支持，添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数
10. 可靠性：InnoDB 现在支持表 DDL 的原子性，也就是 InnoDB 表上的 DDL 也可以实现事务完整性，要么失败回滚，要么成功提交，不至于出现 DDL 时部分成功的问题，此外还支持 crash-safe 特性，元数据存储在单个事务数据字典中
11. 高可用性(High Availability)：InnoDB 集群为您的数据库提供集成的原生 HA 解决方案
12. 安全性：对 OpenSSL 的改进、新的默认身份验证、SQL 角色、密码强度、授权
InnoDB
- 1-对Auto_Increment持久性的更改
  - 将具有 AUTO_INCREMENT 属性的字段添加到 InnoDB 表时，会将AUTO_INCREMENT计数器保留在 InnoDB 表信息存储中
  - 在MySQL 8.0之前，此计数器仅保存在内存中，而不保留在磁盘上，因此必须在重新启动MySQL服务器时对其进行初始化
  - 因此，在MySQL服务器重新启动后执行第一个插入语句时，InnoDB 首先执行上述查询，并将结果增加1后的值作为计数器加载到内存中(如果设置了auto_increment_increment，则按该值递增)
  - 这种 Auto_Increment 的初始化方式在 MySQL 8.0 中发生了改变；现在，每当自动增量值发生变化时，都会被记录到重做日志中，并保存在管理检查点的存储引擎系统表中
  - 因此，当服务器在正常关闭后重新启动时，将使用存储在数据字典系统表中的最大自动增量值来初始化内存中的Auto_Increment计数器
  - 来看一个简单的例子：
  - 这是创建了一个包含具有Auto_Increment属性的id列的表，并插入了2行数据的结果
  - 如果在此示例表中显式声明事务并插入第三行数据，则可以看到上面的结果
  - 此时，如果重启MySQL服务器，再次查看表数据，如果上面执行的事务被回滚，你将会发现数据中id=3的数据已经消失了
  - 我将再次插入具有相同内容的数据
  - 在MySQL 8.0中，通过使用存储在数据字典的系统表中（即使是在MySQL服务器重新启动后也仍然会保留）的最大自动递增值，来初始化内存中的Auto_Increment计数器
  - 因此，在回滚后，id=3之后的值，即4被分配
  - 在MySQL 5.7及之前的版本中，在回滚之后重新启动服务器时，会重新使用与回滚事务相关的自动递增值
  - 但是，在MySQL 8.0中，当前最大的自动递增值会被保留，因此无法重新使用先前已分配的值
- 2-NOWAIT and SKIP LOCKED
  - 其次，是使用设计用于支持锁定处理的两个新功能
  - 我们将研究 MySQL 8.0 如何处理热行（Hot rows）
  - 在 MySQL 5.7 版本以前，当应用程序尝试访问被锁定的行时，如果在有限的时间内无法获得访问权限，那么就会超时并且需要重新尝试事务
  - 在 MySQL 8.0 中，通过引入两个新功能(NOWAIT、SKIP LOCKED)，可以更精细地实现锁处理场景，并更有效地处理超时情况
  - 首先，使用 NOWAIT 可使应用程序在访问被锁定的行时无需等待锁释放或超时
  - 即使设置了会话级 innodb_lock_wait_timeout = 1，也可以获得类似的效果，使得查询不等待行锁，而是立即失败并引发错误
  - SKIP LOCKED 是一个设置，它基于 WHERE 子句请求处理剩余行而不等待行锁，并将锁定的行从结果集中移除
  - 因此，虽然可以获取结果而不引发错误，但始终无法确保结果集的一致性
SQL DDL
- MySQL 8.0引入了名为Instant的新算法，用于某些DDL操作
- Instant算法可以立即完成某些DDL操作，而无需长时间等待
- 在早期版本中使用的Inplace算法，在某些DDL操作中需要在内部重新构建表，并在进程启动和关闭时获取元数据锁
- 因此，随着表的尺寸增大，不仅需要花费更长的时间，还需要时刻关注在DDL操作期间执行的长事务
- 另外，由于DDL语句必须传送到从服务器并等待操作完成后才能反映数据变化，因此必须容忍数据复制的延迟
对于新的"Instant"算法来说
- 不再需要重新构建表，而是通过在数据字典（DD）中仅修改元数据的方式来应用变更
- 而且，这种方式不仅仅可以应用于表级别的变更，还可以应用于列级别的变更，从而进一步提高了性能和效率
- 并且不需要获取元数据锁，与以前的算法相比，实现了更快的处理速度
- 即使在每个DDL语句中不明确指定算法(ALGORITHM = INSTANT)，MySQL 8.0也会默认支持Instant算法；因此可以省略对算法的明确说明
- 根据以上内容，虽然这是一个相当令人高兴的功能，可以保证数据库操作的瞬间性，但还是建议您考虑下应用新算法相关的限制和可能的副作用
- 如前所述，Instant 算法是通过修改元数据而无需重新构建表格来应用的，这意味着它很难修复数据损坏问题
- 换句话说，在 MySQL 5.7 中，可以通过进行数据定义语言（DDL）操作来修复表格或索引的损坏以确保数据的稳定性，但在 MySQL 8.0 中变得更加困难
- 另外，对于使用Instant算法的列添加操作
  - 只能在表的末尾进行添加
  - 无法适用于被压缩的表
SQL DML
- 1-窗口函数
  - 首先，这是一种被称为窗口函数、分析函数或排名函数的函数
  - 为了帮助理解，让我们从聚合函数开始解释
  - 左侧是Sales表的全部12行数据的输出结果
  - 如果使用聚合函数SUM()来计算整个利润的总和，可以看到它会将所有12行数据的计算结果汇总成单行
  - 如果使用GROUP BY子句，可以对行的子集应用聚合函数；例如，计算每个国家的利润总和，可以看到输出结果中包含了每个国家的利润总和，共有3个国家
  - 通过这两个示例，我们可以了解到聚合函数可以减少查询返回的行数
  - 如果要保持输出整个Sales表的结果，并且还要同时输出在前一个例子中通过聚合函数计算得到的total_profit和country_profit，您可以使用窗口函数来实现；窗口函数允许在查询结果中的每一行上执行聚合计算，而不会改变查询结果的行数
  - 窗口函数类似于聚合函数，它在当前行及其相关的行集合上执行计算
  - 不同于聚合函数，窗口函数不会将计算结果归纳为单个输出行，而是将计算结果作用于每个单独的行，并在多行上执行聚合操作
- 2-公共表表达式（CTE）
  - 下面将介绍常被称为“WITH 查询”的 CTE（公共表表达式，Common Table Expressions）
  - CTE（公共表表达式，Common Table Expressions）是一种类似于派生表（Derived Table）的概念，可用作子查询
  - 它是一个在一个查询语句结束之前持续存在的临时结果集，可以在其他 SELECT、INSERT、UPDATE 或 DELETE 语句中多次引用
  - 从 MySQL 8.0 版本开始，引入了 CTE 功能
  - CTE 可以简化复杂的连接和子查询，并允许对数据进行层次化处理
  - CTE的语法非常简单；CTE在查询的第一部分指定，关键字WITH后跟着CTE别名，关键字AS，然后在括号中定义要引用的语句
  - 您可以使用别名(alias)和关键字AS来定义多个CTE，它们用逗号分隔
  - 所有的CTE都可以引用之前定义的CTE，从而简化复杂的SQL语句
  - 虽然CTE的用途类似于派生表，但是如果正确使用CTE，可以更好地简化复杂的SQL查询，并提供更好的可读性和维护性
  - 接下来，我们将探讨正确利用CTE所能获得的性能优势
  - 两者都是相同的查询
  - 我们将在标记的部分分别参照视图和CTE来比较性能(左侧是将用作派生表的查询以视图的形式存储在数据库对象中使用的形式)
  - 当首次执行使用视图的查询时，可以通过查看执行计划在FROM子句中引用的连接表部分和在WHERE条件中用作子查询的部分，发现这些部分都被具体化（Materialised），也就是执行了视图并将结果暂时保存起来
  - 查看使用CTE的查询执行计划时，可以看到与之前的视图相类似，即在FROM子句中引用的连接表部分仅在特定位置进行了一次具体化
  - 换句话说，在MySQL中，由于CTE的多个引用使用了单个临时表，因此在WHERE条件中被子查询引用的部分省略了具体化的过程
  - 由于这个查询中的具体化过程是执行时间最长的部分，因此相较于使用视图，我们几乎将执行时间缩短了一半
  - 利用CTE使用单个临时表的特性可以期望提高性能
  - 然而需要谨记的是，如果滥用这种特性，查询性能可能会下降
  - 因此在使用之前，请务必进行充分的测试

Indexes

为更好地理解该术语的定义，先简单看一下图示：
根据数据在索引的叶子节点中的排序顺序，可以将索引分为升序索引和降序索引
根据扫描的方向将其分为正向索引扫描和反向索引扫描

1-降序索引

首先我们来看一下 Descending 索引，顾名思义，它指的是以降序排列的索引
在 MySQL 5.7 及以下版本中，由于仅支持语法形式，所以尽管可以创建 Descending 索引，但实际上创建的却是没有按照降序排列的升序（Ascending）索引，并没有产生错误
在这种情况下，系统内部可能要通过 Backward Index Scan 来达到 Descending 索引的要求，但这就必须承担一些性能上的损失
接下来，我们将通过一个简单的示例来进行验证
这是一个查询，它从 Rentals 表中筛选出 language=‘Italian’ 的记录，并按 rental_datetime 降序排列数据后进行输出
为了执行上述查询，我们创建了两个索引，并将分别使用这两个索引时的执行计划和执行时间进行了比较
首先，使用第一个索引的情况下，我们创建了一个计划，通过向后扫描升序（Ascending）索引来输出降序排列的结果，其执行时间为175ms
对于第二个索引，由于 rental_datetime 已经是按降序排列的，所以只用了 7ms 就能输出相同的结果
如果我们在 MySQL 5.7 中执行相同的测试，即使是 ix2，也会因为和 ix1 有相同的排序顺序而被创建出来，因此使用 ix1 和 ix2 的执行计划和执行时间不会有差异

2-不可见索引

接下来要介绍的是 Invisible（不可见）索引的新特性
从 MySQL 8.0 版本开始，可以通过索引定义中的 VISIBLE 或 INVISIBLE 关键字来控制其可见性
(这个功能可以理解为是决定是否告知优化器索引是否存在的功能)
默认情况下，所有创建的索引都具有 VISIBLE（可见）属性，但即便是 INVISIBLE（不可见）状态，它也并非被删除，因此与 VISIBLE 索引一样，会根据数据变更进行维护
如果将属性更改为 INVISIBLE，而它是一个必要的索引会发生什么情况呢？
如果在提示中使用的索引属性被更改为 INVISIBLE，从优化器的角度来看，这就变成了必须使用一个（无法使用的）不存在的索引的情况，因此会引发错误
接下来让我们看一个简单的例子
在上述查询中，如果创建了与所需排序顺序相匹配的 ix3 索引，可以在 21 毫秒内得到所希望的结果
如果将这个索引更改为 INVISIBLE 属性，以使优化器无法引用它，那么优化器会认为没有可用的索引，因此必须执行全表扫描后再进行 Filesort 来进行排序
因此，与使用索引时相比，执行时间增加了大约 25 倍以上
通过更改索引属性，如果立即意识到查询性能下降，那么可以通过将属性再改回 VISIBLE 来迅速恢复
像上面的例子一样，将索引属性更改为 INVISIBLE 时，这一操作可以在需要删除不必要的索引时，由于性能或可用性等问题，而不影响服务的检查改变的效率
这是因为一旦删除（DROP）索引后才认识到该索引是必需的，那么就必须重新创建（CREATE）索引才能恢复，而且随着表的大小增加，恢复所需的时间也会更长，这可能导致对服务的影响更加严重

3-功能性索引

最后，新增了对“索引表达式值”而不是列或列前缀值的功能
这是一个索引没有直接存储在表中的值的功能，也称为功能性键
为了帮助理解，我们将通过一个例子来详细查看
上述查询中，在 where 条件的左侧使用了函数
虽然可以预期已经预先创建的 ix4 索引将被充分地利用，但是如果查看执行计划，可以看到它通过索引全扫描方式从头到尾读取 ix4 索引，在存储引擎层面首先进行范围限定处理，然后传递给 MySQL 引擎进行过滤
在 MySQL 5.7 或更低版本中，如果 where 子句的左侧列没有被优化以充分利用索引，可能需要进行修改（例如：WHERE rental_datetime >= ‘2006–01–01’ AND rental_datetime < ‘2007–01–01’）
但是从 MySQL 8.0 开始，由于可以创建应用了函数本身的 functional 索引，因此可以生成并使用像 ix5 这样的索引

JSON

1-JSON函数相关变更/新增事项

随着存储像JSON这样的非结构化数据的需求增加，MongoDB等文档存储数据库变得流行，并且至今仍被广泛使用
MySQL也跟随这一趋势，从5.7版本开始支持储存文档的JSON数据类型，并且可以使用SQL函数来创建/修改/查询这些数据
因此，在保持RDBMS模式的同时，还可以额外利用存储JSON的文档存储
而在MySQL 8.0中，增加了几项功能，既包括了为了处理JSON类型数据的函数相关变更，也包括了用于性能改进的功能

MySQL5.x与8.0,MySQL,mysql,java,后端

在MySQL 8.0中，包括显示JSON数据作为关系表的JSON_TABLE函数在内，有一些函数被新增和改进了
关于JSON_TABLE()函数的内容，稍后将通过示例来进行查看
关于其他新增加的函数的详细内容，请参阅MySQL官方文档（12.17.1 JSON函数参考）

2-更新速度更快

MySQL5.x与8.0,MySQL,mysql,java,后端

在更新JSON数据的情况下
在MySQL 5.7中，是通过先删除数据然后进行插入的方式来处理的
但在MySQL 8.0中，可以只更新想要改变的数据部分
如果在各个版本上执行相同的更新10万条数据的查询，可以通过简单比较执行时间来确认性能得到了提升
由于支持了部分更新，binlog不再记录整个JSON文档，只记录变化的部分，这样binlog的大小就减少了，这种减少可以减少日志记录和复制的IO需求，因此可以期待复制性能的提升
这种部分更新并不适用于所有JSON数据的更新
像上述的三种函数那样添加新字段的情况不包含在内，只有在修改现有对象时，才支持部分更新，且有一个限制是，变更的数据大小不能超过原数据的大小

3-将JSON数据转换为关系型数据

MySQL5.x与8.0,MySQL,mysql,java,后端