MySQL 与主流分支版本上执行 ANALYZE TABLE 安全么？

2023-11-29 05:36
文章标签 mysql 安全 版本 table 执行 主流 分支 database analyze

本文主要是介绍MySQL 与主流分支版本上执行 ANALYZE TABLE 安全么？，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

有时，需要使用 ANALYZE TABLE 命令手动更新表和索引统计信息。在不进一步探讨这种需求的原因的情况下，我想就与在生产系统上运行命令相关的开销来聊聊这个话题。然而，这里讨论的开销与深入表行收集统计信息的通常成本无关，我们可以通过设置样本页数 来控制。

五年前，我的同事 Sveta 发布了一篇不错的博客文章，介绍了 Percona Server for MySQL 中引入的一项改进，以解一些不必要的等待：

《ANALYZE TABLE 不再是阻塞操作》

从历史上看，在 MySQL 中运行 ANALYZE TABLE 命令的问题是查询需要在表的表定义缓存条目上使用排他锁。这使得查询等待任何长时间运行的查询完成，但也可能触发级联等待其他传入请求。简而言之，ANALYZE 可能会导致高负载生产环境中出现较高得延时。

从那时起，MySQL/Percona/MariaDB 都发生了一些变化，但今天仍然存在许多生产系统会受影响的版本。让我们回顾一下这些年来情况的演变。

MySQL

该问题适用于 MySQL 8.0.23 之前的所有版本。5.7 系列没有任何改进（顺便说一句，本月将达到 EOL！），这意味着即使是最新的 5.7.43 也会受到影响。以下是您可能会遇到的示例场景：

mysql > select @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 5.7.43    | MySQL Community Server (GPL) |
+-----------+------------------------------+
1 row in set (0.00 sec)mysql > show processlist;
+----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+
| Id | User     | Host      | db   | Command | Time | State                   | Info                                                           |
+----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+
|  4 | msandbox | localhost | db1  | Query   |   54 | Sending data            | select avg(k) from sbtest1 where pad not like '%f%' group by c |
| 13 | msandbox | localhost | db1  | Query   |   29 | Waiting for table flush | analyze table sbtest1                                          |
| 17 | msandbox | localhost | db1  | Query   |    0 | starting                | show processlist                                               |
| 18 | msandbox | localhost | db1  | Query   |   15 | Waiting for table flush | select * from sbtest1 where id=100                             |
+----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+
4 rows in set (0.00 sec)

一个慢查询导致 ANALYZE 在等待，且另一个通常非常快的查询现在也在等待。

同样的情况也可能发生在 MySQL 8.0 系列，包括 8.0.23。幸运的是，8.0.24 版本中修复了这个问题。我们只能在发行说明 中读到一些关于此问题解决得评论：

事实上，从版本 8.0.24 开始，运行慢查询期间的类似测试会导致即时查询执行：

mysql > select @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 8.0.24    | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)mysql > analyze table sbtest1;
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| db1.sbtest1 | analyze | status   | OK       |
+-------------+---------+----------+----------+
1 row in set (0.00 sec)

不过，即使是 8.1 版本，我们仍然可以在官方文档中找到警告，如下所示：

ANALYZE TABLE removes the table from the table definition cache, which requires a flush lock. If there are long running statements or transactions still using the table, subsequent statements and transactions must wait for those operations to finish before the flush lock is released. Because ANALYZE TABLE itself typically finishes quickly, it may not be apparent that delayed transactions or statements involving the same table are due to the remaining flush lock.

Percona

如上所述，为了解决此错误报告，Percona 引入了修复程序并删除了不必要的表定义缓存锁。

使用 Percona 时，从版本 5.6.38 和 5.7.20 开始运行 ANALYZE TABLE 已经是安全的，因为这些是当时活跃的版本。

自第一个版本（包括第一个 GA 版本 8.0.13-3 ）以来，Percona Server for MySQL 版本 8.0 就没有这个问题，因为改进是从 Percona Server for MySQL 5.7 系列合并而来的。

MariaDB

ANALYZE TABLE 的问题发生于 10.5.3 之前的所有 MariaDB 版本。在版本 10.5.4 中，Percona 得以解决。

因此，当您在 10.5.3 或更低版本以及任何以前的系列（甚至是最新的 10.4.31）中运行查询时，可能会出现类似的情况：

mysql > select @@version,@@version_comment;
+----------------+-------------------+
| @@version      | @@version_comment |
+----------------+-------------------+
| 10.5.3-MariaDB | MariaDB Server    |
+----------------+-------------------+
1 row in set (0.000 sec)mysql > show processlist;
+----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+----------+
| Id | User     | Host      | db   | Command | Time | State                   | Info                                                           | Progress |
+----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+----------+
|  4 | msandbox | localhost | db1  | Query   |   18 | Sending data            | select avg(k) from sbtest1 where pad not like '%f%' group by c |    0.000 |
| 13 | msandbox | localhost | db1  | Query   |   16 | Waiting for table flush | analyze table sbtest1                                          |    0.000 |
| 14 | msandbox | localhost | db1  | Query   |   14 | Waiting for table flush | select * from sbtest1 where id=100                             |    0.000 |
| 15 | msandbox | localhost | NULL | Query   |    0 | starting                | show processlist                                               |    0.000 |
+----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+----------+
4 rows in set (0.000 sec)mysql > select @@version,@@version_comment;
+-----------------+-------------------+
| @@version       | @@version_comment |
+-----------------+-------------------+
| 10.4.31-MariaDB | MariaDB Server    |
+-----------------+-------------------+
1 row in set (0.000 sec)mysql > show processlist;
+----+-------------+-----------+------+---------+------+--------------------------+----------------------------------------------------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                    | Info                                                           | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+----------------------------------------------------------------+----------+
|  1 | system user |           | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL                                                           |    0.000 |
|  2 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                                                           |    0.000 |
|  3 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                                                           |    0.000 |
|  4 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                                                           |    0.000 |
|  5 | system user |           | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL                                                           |    0.000 |
|  9 | msandbox    | localhost | db1  | Query   |   18 | Sending data             | select avg(k) from sbtest1 where pad not like '%f%' group by c |    0.000 |
| 18 | msandbox    | localhost | db1  | Query   |   16 | Waiting for table flush  | analyze table sbtest1                                          |    0.000 |
| 19 | msandbox    | localhost | db1  | Query   |   12 | Waiting for table flush  | select * from sbtest1 where id=100                             |    0.000 |
| 22 | msandbox    | localhost | NULL | Query   |    0 | Init                     | show processlist                                               |    0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+----------------------------------------------------------------+----------+
9 rows in set (0.000 sec)

总结

只要您的数据库在最新版本的 MySQL 或 MariaDB 上运行，运行 ANALYZE TABLE 就应该绝对安全，不会导致任何意外的停顿。

Percona Server for MySQL 系列：5.6.38+、5.7.20+ 和 8.0.x 的用户都是安全的。

MariaDB 用户必须升级到 10.5.4 或更高版本以避免锁定问题。

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

SQLE 获取

类型地址
版本库https://github.com/actiontech/sqle
文档https://actiontech.github.io/sqle-docs/
发布信息https://github.com/actiontech/sqle/releases
数据审核插件开发文档https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse

这篇关于MySQL 与主流分支版本上执行 ANALYZE TABLE 安全么？的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

原文地址:
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.chinasem.cn/article/431656。

相关文章

MySQL 中的 CAST 函数详解及常见用法

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字
阅读更多...
Mysql实现范围分区表(新增、删除、重组、查看)

Mysql实现范围分区表(新增、删除、重组、查看)

《Mysql实现范围分区表(新增、删除、重组、查看)》MySQL分区表的四种类型（范围、哈希、列表、键值）,主要介绍了范围分区的创建、查询、添加、删除及重组织操作,具有一定的参考价值,感兴趣的可以了解... 目录一、mysql分区表分类二、范围分区（Range Partitioning1、新建分区表：2、分
阅读更多...
MySQL 定时新增分区的实现示例

MySQL 定时新增分区的实现示例

《MySQL定时新增分区的实现示例》本文主要介绍了通过存储过程和定时任务实现MySQL分区的自动创建,解决大数据量下手动维护的繁琐问题,具有一定的参考价值,感兴趣的可以了解一下... mysql创建好分区之后，有时候会需要自动创建分区。比如，一些表数据量非常大，有些数据是热点数据，按照日期分区MululbU
阅读更多...
SQL Server配置管理器无法打开的四种解决方法

SQL Server配置管理器无法打开的四种解决方法

《SQLServer配置管理器无法打开的四种解决方法》本文总结了SQLServer配置管理器无法打开的四种解决方法,文中通过图文示例介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的... 目录方法一：桌面图标进入方法二：运行窗口进入检查版本号对照表php方法三：查找文件路径方法四：检查 S
阅读更多...
MySQL 删除数据详解(最新整理)

MySQL 删除数据详解(最新整理)

《MySQL删除数据详解(最新整理)》：本文主要介绍MySQL删除数据的相关知识,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、前言二、mysql 中的三种删除方式1.DELETE语句✅ 基本语法： 示例：2.TRUNCATE语句✅ 基本语
阅读更多...
MySQL中查找重复值的实现

MySQL中查找重复值的实现

《MySQL中查找重复值的实现》查找重复值是一项常见需求,比如在数据清理、数据分析、数据质量检查等场景下,我们常常需要找出表中某列或多列的重复值,具有一定的参考价值,感兴趣的可以了解一下... 目录技术背景实现步骤方法一：使用GROUP BY和HAVING子句方法二：仅返回重复值方法三：返回完整记录方法四：
阅读更多...
IDEA中新建/切换Git分支的实现步骤

IDEA中新建/切换Git分支的实现步骤

《IDEA中新建/切换Git分支的实现步骤》本文主要介绍了IDEA中新建/切换Git分支的实现步骤,通过菜单创建新分支并选择是否切换,创建后在Git详情或右键Checkout中切换分支,感兴趣的可以了... 前提：项目已被Git托管1、点击上方栏Git->NewBrancjsh...2、输入新的分支的
阅读更多...
从入门到精通MySQL联合查询

从入门到精通MySQL联合查询

《从入门到精通MySQL联合查询》：本文主要介绍从入门到精通MySQL联合查询,本文通过实例代码给大家介绍的非常详细,需要的朋友可以参考下... 目录摘要1. 多表联合查询时mysql内部原理2. 内连接3. 外连接4. 自连接5. 子查询6. 合并查询7. 插入查询结果摘要前面我们学习了数据库设计时要满
阅读更多...
MySQL查询JSON数组字段包含特定字符串的方法

MySQL查询JSON数组字段包含特定字符串的方法

《MySQL查询JSON数组字段包含特定字符串的方法》在MySQL数据库中,当某个字段存储的是JSON数组,需要查询数组中包含特定字符串的记录时传统的LIKE语句无法直接使用,下面小编就为大家介绍两种... 目录问题背景解决方案对比1. 精确匹配方案（推荐）2. 模糊匹配方案参数化查询示例使用场景建议性能优
阅读更多...
Java 线程安全与 volatile与单例模式问题及解决方案

Java 线程安全与 volatile与单例模式问题及解决方案

《Java线程安全与volatile与单例模式问题及解决方案》文章主要讲解线程安全问题的五个成因（调度随机、变量修改、非原子操作、内存可见性、指令重排序）及解决方案,强调使用volatile关键字... 目录什么是线程安全线程安全问题的产生与解决方案线程的调度是随机的多个线程对同一个变量进行修改线程的修改操
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2