MySQL8.0 中文参考手册
MySQL8.0 中文参考手册  / 第15章 InnoDB 存储引擎  / 15.8 InnoDB配置  / 15.8.10 为 InnoDB 配置优化器统计信息  /  15.8.10.3 估计 InnoDB 表的 ANALYZE TABLE 复杂性

15.8.10.3 估计 InnoDB 表的 ANALYZE TABLE 复杂性

ANALYZE TABLE表的复杂性 InnoDB取决于：

使用这些参数，估计 ANALYZE TABLE复杂性的近似公式为：

innodb_stats_persistent_sample_pages *表中索引列数*分区数的 值

通常，结果值越大，执行时间越长ANALYZE TABLE。

笔记

innodb_stats_persistent_sample_pages 定义在全局级别采样的页面数。要设置为单个表格采样的页数，请使用 STATS_SAMPLE_PAGES带有 CREATE TABLE或 的选项ALTER TABLE。有关详细信息，请参阅第 15.8.10.1 节，“配置持久优化器统计参数”。

如果 innodb_stats_persistent=OFF，采样的页数由 定义 innodb_stats_transient_sample_pages。有关更多信息，请参阅第 15.8.10.2 节，“配置非持久优化器统计参数”。

有关估计复杂性的更深入方法ANALYZE TABLE，请考虑以下示例。

在Big O notation中，ANALYZE TABLE 复杂性被描述为： 

Press CTRL+C to copy
 
 O(n_sample
  * (n_cols_in_uniq_i
     + n_cols_in_non_uniq_i
     + n_cols_in_pk * (1 + n_non_uniq_i))
  * n_part)

在哪里：

  • n_sample是抽样的页数（由 定义 innodb_stats_persistent_sample_pages）

  • n_cols_in_uniq_i是所有唯一索引中所有列的总数（不包括主键列）

  • n_cols_in_non_uniq_i是所有非唯一索引中所有列的总数

  • n_cols_in_pk是主键中的列数（如果未定义主键，则在 InnoDB内部创建单列主键）

  • n_non_uniq_i是表中非唯一索引的数量

  • n_part是分区数。如果未定义分区，则该表被视为单个分区。

现在，考虑下表 (table t)，它有一个主键（2 列）、一个唯一索引（2 列）和两个非唯一索引（每个索引两列）： 

Press CTRL+C to copy
 
CREATE TABLE t (
  a INT,
  b INT,
  c INT,
  d INT,
  e INT,
  f INT,
  g INT,
  h INT,
  PRIMARY KEY (a, b),
  UNIQUE KEY i1uniq (c, d),
  KEY i2nonuniq (e, f),
  KEY i3nonuniq (g, h)
);

对于上述算法需要的列和索引数据，查询表的 mysql.innodb_index_stats持久化索引统计表t。n_diff_pfx%统计信息显示为每个索引计算的列。 例如，列 a和b被计算为主键索引。对于非唯一索引，除了用户定义的列之外，主键列 (a,b) 也被计算在内。

笔记

有关InnoDB 持久统计表的其他信息，请参阅 第 15.8.10.1 节，“配置持久优化器统计参数” 

Press CTRL+C to copy
 
mysql> SELECT index_name, stat_name, stat_description
       FROM mysql.innodb_index_stats WHERE
       database_name='test' AND
       table_name='t' AND
       stat_name like 'n_diff_pfx%';
  +------------+--------------+------------------+
  | index_name | stat_name    | stat_description |
  +------------+--------------+------------------+
  | PRIMARY    | n_diff_pfx01 | a                |
  | PRIMARY    | n_diff_pfx02 | a,b              |
  | i1uniq     | n_diff_pfx01 | c                |
  | i1uniq     | n_diff_pfx02 | c,d              |
  | i2nonuniq  | n_diff_pfx01 | e                |
  | i2nonuniq  | n_diff_pfx02 | e,f              |
  | i2nonuniq  | n_diff_pfx03 | e,f,a            |
  | i2nonuniq  | n_diff_pfx04 | e,f,a,b          |
  | i3nonuniq  | n_diff_pfx01 | g                |
  | i3nonuniq  | n_diff_pfx02 | g,h              |
  | i3nonuniq  | n_diff_pfx03 | g,h,a            |
  | i3nonuniq  | n_diff_pfx04 | g,h,a,b          |
  +------------+--------------+------------------+

根据上面显示的索引统计数据和表定义，可以确定以下值：

  • n_cols_in_uniq_i, 不包括主键列的所有唯一索引中的所有列的总数是 2 (c和 d)

  • n_cols_in_non_uniq_i，所有非唯一索引中所有列的总数为 4（e、f和 ） gh

  • n_cols_in_pk，主键中的列数为 2（a和 b）

  • n_non_uniq_i，表中非唯一索引的数量为 2（i2nonuniq和 i3nonuniq））

  • n_part，分区数，为 1。

您现在可以计算 innodb_stats_persistent_sample_pages* (2 + 4 + 2 * (1 + 2)) * 1 以确定扫描的页数。innodb_stats_persistent_sample_pages设置为默认值， 20默认页面大小为 16 KiB ( innodb_page_size=16384)，然后您可以估计bytes 为表读取了20 * 12 * 16384 t，或大约 4 MiB。

笔记

MiB可能不会从磁盘读取 所有 4 个，因为一些叶页可能已经缓存在缓冲池中。