ANALYZE TABLE在MySQL主从复制中的案例

##

ANALYZE TABLE在MySQL主从复制中的案例

1、客户信息反馈

周末客户直接扔了一张图给我，询问主从数据库中一张业务表大小为什么不一致，在数据库和磁盘都不一致，是不是主从复制又不行了。在对业务表和复制状态进行检查之后，发现主从复制工作正常，主从库的数据一致。那是为什么呢？

1.1、故障复现

能复现的故障是可以帮助我们更好的分析问题.

2、INFORMATION_SCHEMA.TABLES分析

首先我们需要知道这些信息从哪里来，首先我们来看INFORMATION_SCHEMA.TABLES，事实上官网的文档给了我们一些解释：

The TABLES table provides information about tables in databases.

Columns in TABLES that represent table statistics hold cached values. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To update cached values at any time for a given table, use ANALYZE TABLE. To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry) to 0.

这里最重要的就是cached values，这是缓存值，默认并不是实时更新的。所以我们在INFORMATION_SCHEMA.TABLES看到的信息，是一个历史值。

同时提到了使用ANALYZE TABLE可以update cached values at any time。所以我们接着分析ANALYZE TABLE。

3、ANALYZE TABLE分析

让我们还是从官网文档开始

ANALYZE TABLEgenerates table statistics:

• ANALYZE TABLEwithout either HISTOGRAM clause performs a key distribution analysis and stores the distribution for the named table or tables. For MyISAM tables, ANALYZE TABLEfor key distribution analysis is equivalent to using myisamchk --analyze.
• ANALYZE TABLEwith the UPDATE HISTOGRAM clause generates histogram statistics for the named table columns and stores them in the data dictionary. Only one table name is permitted for this syntax. MySQL 8.0.31 and later also supports setting the histogram of a single column to a user-defined JSON value.
• ANALYZE TABLE with the DROP HISTOGRAM clause removes histogram statistics for the named table columns from the data dictionary. Only one table name is permitted for this syntax.

这里提到最多的还是直方图，后续我们再将直方图的优化案例。

第一句话也告诉了我们ANALYZE TABLE的作用generates table statistics(生成表的统计信息)

那么我们是不是使用ANALYZE TABLE就可以解决问题呢？

4、注意事项分析

During the analysis, the table is locked with a read lock for InnoDB and MyISAM.

By default, the server writes ANALYZE TABLEstatements to the binary log so that they replicate to replicas. To suppress logging, specify the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

Previously, ANALYZE TABLE required a flush lock. This meant that, when there were long running statements or transactions still using the table when ANALYZE TABLE was invoked, any following statements and transactions had to wait for those operations to finish before the flush lock could be released. This issue is resolved in MySQL 8.0.24 (and later), where ANALYZE TABLE no longer causes subsequent operations to wait.

这里面有一些重要的事：

第一段话：分析期间会有读锁，InnoDB和MyISAM都会有。

第二句话：在主从复制中ANALYZE TABLE语句会被记录到binary log,然后发给从库，但是，这是可以控制的。

第三句话：MySQL 8.0.24之前ANALYZE TABLE会请求flush lock，所以这句话是告诉我们升级。

5、后记

在执行操作之后，我们得到了一致的信息。