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
。有关详细信息,请参阅第 15.8.10.1 节,“配置持久优化器统计参数”。
如果
innodb_stats_persistent=OFF
,采样的页数由 定义
innodb_stats_transient_sample_pages
。有关更多信息,请参阅第 15.8.10.2 节,“配置非持久优化器统计参数”。
有关估计复杂性的更深入方法ANALYZE
TABLE
,请考虑以下示例。
在Big O notation中,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
是分区数。如果未定义分区,则该表被视为单个分区。
现在,考虑下表 (table 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
持久化索引统计表t
。n_diff_pfx%
统计信息显示为每个索引计算的列。
例如,列
a
和b
被计算为主键索引。对于非唯一索引,除了用户定义的列之外,主键列 (a,b) 也被计算在内。
有关InnoDB
持久统计表的其他信息,请参阅
第 15.8.10.1 节,“配置持久优化器统计参数”
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
和 )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),然后您可以估计bytes
为表读取了20 * 12 * 16384 t
,或大约 4
MiB
。
MiB
可能不会从磁盘读取
所有 4 个,因为一些叶页可能已经缓存在缓冲池中。