• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 估算InnoDB表的分析表复杂度

    ANALYZE TABLEInnoDB表的复杂度取决于:

    • 所定义的采样页数innodb_stats_persistent_sample_pages
    • 表中索引列的数量
    • 分区数。如果表没有分区,则分区数被视为1。

    使用这些参数,估算ANALYZE TABLE复杂度的近似公式为:

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

    通常,结果值越大,的执行时间就越多ANALYZE TABLE

    注意

    innodb_stats_persistent_sample_pages定义在全局级别采样的页面数。要设置单个表的采样页数,请使用STATS_SAMPLE_PAGES带有CREATE TABLE或的选项ALTER TABLE。有关更多信息,请参见“配置持久性优化器统计参数”。

    如果为innodb_stats_persistent=OFF,则采样的页数由定义innodb_stats_transient_sample_pages。有关更多信息,请参见“配置非持久性优化器统计参数”。

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

    用Big O表示法,ANALYZE TABLE复杂度描述为:

     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是分区数。如果未定义任何分区,则该表被视为单个分区。

    现在,考虑下表(表t),该表具有一个主键(2列),一个唯一索引(2列)和两个非唯一索引(每个两列):

    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持久索引统计表中的table t。该n_diff_pfx%统计显示,计数每个索引的列。例如,列ab被计入主键索引。对于非唯一索引,除用户定义的列外,还对主键列(a,b)进行计数。

    注意

    有关InnoDB持久性统计信息表的更多信息,请参见“配置持久性优化器统计信息参数”

    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(cd
    • n_cols_in_non_uniq_i,总数在所有非唯一的索引的所有列的,为4(,,e和)fgh
    • n_cols_in_pk,主键中的列数为2(ab
    • n_non_uniq_i,表格中非唯一索引的数量为2(i2nonuniqi3nonuniq
    • n_part,分区数为1。

    现在,您可以计算innodb_stats_persistent_sample_pages*(2 + 4 + 2 *(1 + 2))* 1来确定要扫描的叶子页数。用innodb_stats_persistent_sample_pages设置为默认值20,和与16的缺省页大小KiBinnodb_page_size= 16384),则可以预计,20 * 12 * 16384 bytes被读取为表t,或约4 MiB

    注意

    MiB可能无法从磁盘上全部读取4个磁盘,因为某些叶子页可能已经缓存在缓冲池中。