MySQL8中PRIMARY.n_diff_pfx01与主键唯一计数不匹配问题咨询

表结构信息 #

create table employees
(
    emp_no int not null primary key,
    birth_date datetime not null,
    first_name varchar(14) not null,
    last_name varchar(16) not null,
    gender enum('M', 'F') not null,
    hire_date datetime not null,
    full_name varchar(50) as (concat(`first_name`,_utf8mb4' ',`last_name`))
)
collate=utf8mb4_general_ci;

create index ix_firstname
    on employees (first_name);

create index ix_fullname
    on employees (full_name);

create index ix_gender_birthdate
    on employees (gender, birth_date);

create index ix_hiredate
    on employees (hire_date);

create index ix_lastname_firstname
    on employees (last_name, first_name);

create definer = root@localhost trigger on_delete
    before delete
    on employees
    for each row
    BEGIN
         DELETE FROM salaries WHERE emp_no=OLD.emp_no; 
       END;

该表共包含300,204行数据，已设置STATS_PERSISTENT=1启用持久化统计。

异常现象 #

主键emp_no为唯一值，按逻辑PRIMARY索引的n_diff_pfx01（即emp_no的唯一值数量）应与表行数完全一致，但实际统计结果显示PRIMARY.n_diff_pfx01=299,101，与表行数300,024不匹配。

统计结果详情 #

//Primary.n_diff_pfx01 is 299,101 but table row count is 300,024

原因分析 #

1. 采样统计偏差：InnoDB持久化统计默认采用采样方式计算唯一值数量，从统计结果可见sample_size=20，仅采样了20个索引页。对于主键这种全唯一值的索引，少量采样页无法覆盖所有数据，导致统计值与实际行数存在偏差。
2. 统计信息未及时更新：若表发生大量数据插入、删除操作后，统计信息未自动触发更新，也会导致统计值滞后于实际数据。

解决办法 #

1. 增大采样页面数：调整采样页数量，让统计采样更全面。可全局设置或针对单表设置：

   -- 全局设置
   SET GLOBAL innodb_stats_persistent_sample_pages = 100;
   -- 单表设置
   ALTER TABLE employees STATS_SAMPLE_PAGES = 100;
   

2. 手动刷新统计信息：执行命令强制更新表的统计数据，确保统计值与实际数据一致：

   ANALYZE TABLE employees;
   

3. 验证结果：重新查询统计信息，确认PRIMARY.n_diff_pfx01是否与表行数匹配。

内容的提问来源于stack exchange，提问作者haedoang