统计信息的作用
对于一条SQL，数据库选择何种方式执行，需要根据统计信息进行估算，计算出代价最低的执行计划。收集统计信息主要是为了让优化器做出正确的判断，选择最佳的执行计划。
PG的统计信息收集
PG的统计信息相关表
在PostgreSQL里面，统计信息存放于pg_statistics系统表中，由于pg_statistics里面的内容人为不易阅读，因此便有了pg_stats视图。
pg_class看pages和tuples
postgres=# select relname,relpages,reltuples::bigint from pg_class where relname='test'\gx
-[ RECORD 1 ]-----
relname | test
relpages | 443
reltuples | 100000
pg_stat_all_tables看活元组、死元组，上次统计信息收集时间
postgres=# select * from pg_stat_all_tables where relname='test'\gx
-[ RECORD 1 ]-------+------------------------------
relid | 16388
schemaname | public
relname | test
seq_scan | 0
last_seq_scan |
seq_tup_read | 0
idx_scan |
last_idx_scan |
idx_tup_fetch |
n_tup_ins | 100000
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_tup_newpage_upd | 0
n_live_tup | 100000
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum | 2025-01-21 10:46:51.330118+08
last_analyze |
last_autoanalyze | 2025-01-21 10:46:51.353753+08
vacuum_count | 0
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 1
pg_stats看列的统计信息
\d pg_stats
View "pg_catalog.pg_stats"
Column | Type | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
schemaname | name | | |
tablename | name | | |
attname | name | | |
inherited | boolean | | | ---是否是继承列
null_frac | real | | | ---null空值的比率
avg_width | integer | | | ---平均宽度，字节
n_distinct | real | | | ---大于零就是非重复值的数量，小于零则是非重复值的个数除以行数
most_common_vals | anyarray | | | ---高频值
most_common_freqs | real[] | | | ---高频值的频率
histogram_bounds | anyarray | | | ---直方图
correlation | real | | | ---物理顺序和逻辑顺序的关联性
most_common_elems | anyarray | | | ---高频元素，比如数组
most_common_elem_freqs | real[] | | | ---高频元素的频率
elem_count_histogram | real[] | | | ---直方图（元素）
PG自动收集统计信息
• 触发vacuum analyze–>
• 表上新增(insert,update,delte) >= autovacuum_analyze_scale_factor* reltuples(表上记录数) + autovacuum_analyze_threshold
postgres=# show autovacuum_analyze_scale_factor;
autovacuum_analyze_scale_factor
---------------------------------
0.1
(1 row)
postgres=# show autovacuum_analyze_threshold;
autovacuum_analyze_threshold
------------------------------
50
(1 row)
PG手动收集统计信息
手动收集统计信息的命令是analyze命令，analyze的语法格式：
analyze [verbose] [table[(column[,…])]]
verbose：显示处理的进度，以及表的一些统计信息
table：要分析的表名，如果不指定，则对整个数据库中的所有表作分析
column：要分析的特定字段的名字默认是分析所有字段
analyze 命令 会在表上加读锁，不影响表上其它SQL并发执行，对于大表只会读取表中部分数据。
MySQL的统计信息收集
MySQL的统计信息相关表
• 收集的表的统计信息存放在mysql数据库的innodb_table_stats表中。
• 索引的统计信息存放在mysql数据库的innodb_index_stats表中。
mysql> select * from mysql.innodb_table_stats where table_name='actor';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| sakila | actor | 2025-01-21 16:06:31 | 200 | 1 | 1 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)
mysql> select * from mysql.innodb_index_stats where table_name='actor';
+---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| sakila | actor | PRIMARY | 2025-01-21 16:06:31 | n_diff_pfx01 | 200 | 1 | actor_id |
| sakila | actor | PRIMARY | 2025-01-21 16:06:31 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| sakila | actor | PRIMARY | 2025-01-21 16:06:31 | size | 1 | NULL | Number of pages in the index |
| sakila | actor | idx_actor_last_name | 2025-01-21 16:06:31 | n_diff_pfx01 | 121 | 1 | last_name |
| sakila | actor | idx_actor_last_name | 2025-01-21 16:06:31 | n_diff_pfx02 | 200 | 1 | last_name,actor_id |
| sakila | actor | idx_actor_last_name | 2025-01-21 16:06:31 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| sakila | actor | idx_actor_last_name | 2025-01-21 16:06:31 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)
MySQL自动收集统计信息
• innodb_stats_persistent
是否把统计信息持久化。
对应表选项STATS_PERSISTENT
• innodb_stats_auto_recalc
当一个表的数据变化超过10%时是否自动收集统计信息，两次统计信息收集之间时间间隔不能少10秒。
对应的表选项STATS_AUTO_RECALC
• innodb_stats_on_metadata：其触发条件是表的元数据发生变化，如执行 ALTER TABLE 等操作修改表结构时，会触发统计信息的自动更新。
• innodb_stats_persistent_sample_pages
统计索引时的抽样页数，这个值设置得越大，收集的统计信息越准确，但收集时消耗的资源越大。
对应的表选项STATS_SAMPLE_PAGES
mysql> show variables like 'innodb_stat%';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
+--------------------------------------+-------------+
9 rows in set (0.00 sec)
对应的表选项可以这样设置
alter table actor stats_auto_recalc=0;
MySQL手动收集统计信息
analyze local table actor,rental;
analyze table 加MDL读锁，不影响DML的并行操作。
PG vs MySQL
在自动收集统计信息的方法上，PG比MySQL更加灵活，例如在表统计信息更新触发条件上， PG可以通过调整autovacuum_analyze_scale_factor的大小，来调整更新触发条件的数据量比例，而MySQL只能是10%，而且，因为PG还有autovacuum_analyze_threshold这个最小更新量保护机制，避免小表被频发触发统计信息收集影响性能。
在手动收集统计信息的方式上，PG和MySQL类似，都会加上读锁，MySQL加元数据读锁，不影响DML并行，PG加共享更新独占（SHARE UPDATE EXCLUSIVE），也不影响DML并行。
另外PG统计信息收集还有两个优势
统计信息的精度
MySQL统计信息的精度相对较低，尤其是在数据量较大且分布不均匀的情况下，可能无法准确地反映数据的实际情况，从而影响查询优化器的选择；而PG除了包含与 MySQL 类似的基本统计信息外，还提供了更丰富的统计内容，如多字段统计信息，可以对多个列的组合进行统计分析，为复杂的查询提供更精确的优化依据。
对性能的影响
MySQL自动收集统计信息可能会在一定程度上增加系统的负载，尤其是在数据量较大且修改频繁的情况下；而PostgreSQL的autovacuum 进程在后台自动运行，对系统性能的影响相对较小。但在进行大规模的数据操作或系统负载较高时，可能会导致一定的性能波动。
