估算InnoDB表的分析表复杂度
ANALYZE TABLE
InnoDB
表的复杂度取决于:
- 所定义的采样页数
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%
统计显示,计数每个索引的列。例如,列a
和b
被计入主键索引。对于非唯一索引,除用户定义的列外,还对主键列(a,b)进行计数。
注意有关
InnoDB
持久性统计信息表的更多信息,请参见“配置持久性优化器统计信息参数”
mysql>SELECT index_name, stat_name, stat_descriptionFROM mysql.innodb_index_statsWHERE database_name='test' ANDtable_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
g
h
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),则可以预计,20 * 12 * 16384 bytes
被读取为表t
,或约4 MiB
。
注意
MiB
可能无法从磁盘上全部读取4个磁盘,因为某些叶子页可能已经缓存在缓冲池中。