MySQL5.x与8.0

大致区别

• MySQL 8.0 在以下方面带来了更好的性能：读/写工作负载、IO 密集型工作负载、以及高竞争（"hot spot"热点竞争问题）工作负载

• 该项功能消除了对独立的 NoSQL 文档数据库的需求，而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性

• 窗口函数与 SUM()、COUNT() 这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行当中

• 即窗口函数不需要 GROUP BY

• 当对索引进行隐藏时，它不会被查询优化器所使用

• 我们可以使用这个特性用于性能调试，例如我们先隐藏一个索引，然后观察其对数据库的影响

• 如果数据库性能有所下降，说明这个索引是有用的，然后将其“恢复显示”即可；

• 如果数据库性能看不出变化，说明这个索引是多余的，可以考虑删掉

• 这些方法更有效地提高了查询性能，同时避免了查询缓存可能引入的问题

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语句必须传送到从服务器并等待操作完成后才能反映数据变化，因此必须容忍数据复制的延迟

• 不再需要重新构建表，而是通过在数据字典（DD）中仅修改元数据的方式来应用变更

• 而且，这种方式不仅仅可以应用于表级别的变更，还可以应用于列级别的变更，从而进一步提高了性能和效率

• 并且不需要获取元数据锁，与以前的算法相比，实现了更快的处理速度

  

• 即使在每个DDL语句中不明确指定算法(ALGORITHM = INSTANT)，MySQL 8.0也会默认支持Instant算法；因此可以省略对算法的明确说明

• 根据以上内容，虽然这是一个相当令人高兴的功能，可以保证数据库操作的瞬间性，但还是建议您考虑下应用新算法相关的限制和可能的副作用

• 如前所述，Instant 算法是通过修改元数据而无需重新构建表格来应用的，这意味着它很难修复数据损坏问题

• 换句话说，在 MySQL 5.7 中，可以通过进行数据定义语言（DDL）操作来修复表格或索引的损坏以确保数据的稳定性，但在 MySQL 8.0 中变得更加困难

• 另外，对于使用Instant算法的列添加操作
  • 只能在表的末尾进行添加
  • 无法适用于被压缩的表