14.8.11.3 估算 InnoDBtable 的分析 table 复杂度
InnoDB
个 table 的ANALYZE TABLE复杂度取决于:
-
采样的页数,由innodb_stats_persistent_sample_pages定义。
-
table 中索引列的数量
-
分区数。如果 table 没有分区,则分区数被视为 1.
使用这些参数,估算ANALYZE TABLE复杂度的近似公式为:
innodb_stats_persistent_sample_pages的值table 中索引列的数量分区的数量
通常,结果值越大,ANALYZE TABLE的执行时间就越长。
Note
innodb_stats_persistent_sample_pages定义在全局级别采样的页面数。要设置单个 table 的采样页数,请对CREATE TABLE或ALTER TABLE使用STATS_SAMPLE_PAGES
选项。有关更多信息,请参见第 14.8.11.1 节“配置持久性优化器统计参数”。
如果为innodb_stats_persistent=OFF,则采样的页数由innodb_stats_transient_sample_pages定义。有关其他信息,请参见第 14.8.11.2 节,“配置非持久性优化器统计参数”。
有关估计ANALYZE TABLE
复杂度的更深入方法,请考虑以下示例。
在大 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)
where:
-
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
是 table 中非唯一索引的数量 -
n_part
是分区数。如果未定义任何分区,则该 table 被视为单个分区。
现在,考虑下 table(tablet
),该 table 具有一个主键(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 中查询 tablet
。 n_diff_pfx%
统计信息显示为每个索引计数的列。例如,列a
和b
被计为主键索引。对于非唯一索引,除用户定义的列外,还对主键列(a,b)进行计数。
Note
有关InnoDB
永久统计信息 table 的其他信息,请参阅第 14.8.11.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 |
+------------+--------------+------------------+
根据上面显示的索引统计数据和 table 定义,可以确定以下值:
-
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
(table 中的非唯一索引数)为 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),然后可以估计读取了 tablet
的 20 * 12 * 16384 bytes
或大约 4 MiB
。
Note
可能无法从磁盘上读取所有 4 MiB
,因为某些叶子页可能已经缓存在缓冲池中。