SHOW TABLE STATUS显示行数为0但SHOW INDEX却显示几十万？--MySQL InnoDB 统计信息源码解析

俊才

发布于 2025-12-30 20:34:29

1120

“数据明明插进去了，为什么 SHOW TABLE STATUS 说表是空的？” 这是许多 DBA 和开发在批量导入数据后常遇到的“灵异现象”。更诡异的是：SHOW INDEX FROM orders 的主键 Cardinality 却显示有 298,920 条记录！到底谁在说谎？是 MySQL 出了 Bug，还是我们误解了它的行为？

上述情况是我刚刚做测试时偶尔发现的，数据库版本是MYSQL8.0.39社区版，查询的现象如下：

下面我们还原一下过程并从源码中探索原因。

1. 问题复现：一个批量插入数据的存储过程

我使用如下存储过程生成测试订单数据：

DELIMITER ;;
CREATE PROCEDURE GenerateOrderData()
BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION;  -- 关键：开启大事务

    WHILE (i <= 100000) DO
        INSERT INTO `orders` (
            `order_number`, `customer_id`, `product_id`, 
            `quantity`, `amount`, `order_date`, `status`
        ) VALUES (
            CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(i, 8, '0')),
            FLOOR(1 + RAND() * 5000),
            FLOOR(1 + RAND() * 200),
            FLOOR(1 + RAND() * 5),
            ROUND((10 + RAND() * 500) * FLOOR(1 + RAND() * 5), 2),
            DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY),
            FLOOR(1 + RAND() * 4)
        );
        SET i = i + 1;
    END WHILE;

    COMMIT;  -- 提交
END;;
DELIMITER ;
-- 执行
CALL GenerateOrderData();

调用3次存储过程插入数据，执行后验证结果如下：

mysql> show table status like 'orders';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+-----------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment   |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+-----------+
| orders | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        65536 |         0 |              1 | 2025-12-23 18:47:50 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                | 订单表    |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+-----------+
1 row in set (0.00 sec)

mysql> show index from `orders`;
+--------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| orders |          0 | PRIMARY             |            1 | id          | A         |      298920 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | idx_customer_date   |            1 | customer_id | A         |        4978 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | idx_customer_date   |            2 | order_date  | A         |      298920 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | idx_order_date      |            1 | order_date  | A         |        7535 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | idx_customer_status |            1 | customer_id | A         |        5329 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | idx_customer_status |            2 | status      | A         |       20382 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | idx_product_id      |            1 | product_id  | A         |         190 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.01 sec)

矛盾点：数据真实存在，但表状态却说“没数据”！

以上可以发现 show table status的rows是0 ，show index 方式查询结果有298920（接近实际值）。顿感诧异，这两个不是调用同一个方法采样的？于是我也问了几个大佬，他们也没遇到过或者之前没注意到过这个现象，那我们就自己探索一下。

2. 表面差异：两个命令的本质不同

我们已知的知识如下

命令	返回字段	含义	是否精确
SHOW TABLE STATUS	Rows	表的估算行数	估算
SHOW INDEX FROM	Cardinality	索引的唯一值数量估算	估算（但通常更准）

两者都不是实时精确值！它们只是 InnoDB 为优化器提供的“提示”（hints）。但为何一个为 0，一个却接近真实？我们从表面情况来猜测，是不是bug?是不是不是同一个地方取统计数据？是不是进行采样的时机和方式不同？

3. 源码深挖：Rows = 0 从何而来？

3.1 SHOW TABLE STATUS 的调用链

在 MySQL 源码中，该命令最终调用存储引擎的 handler::info() 接口：

// sql/sql_show.cc
table->file->info(HA_STATUS_VARIABLE);

InnoDB 实现如下（storage/innobase/handler/ha_innodb.cc）：

/** Returns statistics information of the table to the MySQL interpreter,
 in various fields of the handle object.
 @return HA_ERR_* error code or 0 */

int ha_innobase::info(uint flag) /*!< in: what information is requested */
{
  return (info_low(flag, false /* not ANALYZE */));
}

上面有个大大的“not ANALYZE”

int ha_innobase::info(uint flag) {
    if (flag & HA_STATUS_VARIABLE) {
        stats.records = table->stat_n_rows;  // ← 核心字段！
    }
}

3.2 stat_n_rows 的生命周期

定义于 dict_table_t 结构体（storage/innobase/include/dict0mem.h）：

/** Statistics for query optimization. @{ */

  /** Creation state of 'stats_latch'. */
  std::atomic<os_once::state_t> stats_latch_created;

  /** This latch protects:
  "dict_table_t::stat_initialized",
  "dict_table_t::stat_n_rows (*)",
  "dict_table_t::stat_clustered_index_size",
  "dict_table_t::stat_sum_of_other_index_sizes",
  "dict_table_t::stat_modified_counter (*)",
  "dict_table_t::indexes*::stat_n_diff_key_vals[]",
  "dict_table_t::indexes*::stat_index_size",
  "dict_table_t::indexes*::stat_n_leaf_pages".
  (*) Those are not always protected for
  performance reasons. */
  rw_lock_t *stats_latch;

rows何时被设为 0的呢？有以下情况：

表刚创建时
执行 TRUNCATE TABLE（本质是 drop + recreate）

可在源码 storage/innobase/dict/dict0stats.cc中发现

/** Write all zeros (or 1 where it makes sense) into a table and its indexes'
 statistics members. The resulting stats correspond to an empty table. */
static void dict_stats_empty_table(dict_table_t *table) /*!< in/out: table */
{
  /* Zero the stats members */

  dict_table_stats_lock(table, RW_X_LATCH);

  table->stat_n_rows = 0;
  table->stat_clustered_index_size = 1;
  /* 1 page for each index, not counting the clustered */
  table->stat_sum_of_other_index_sizes = UT_LIST_GET_LEN(table->indexes) - 1;
  table->stat_modified_counter = 0;

  dict_index_t *index;

  for (index = table->first_index(); index != nullptr; index = index->next()) {
    if (index->type & DICT_FTS) {
      continue;
    }

    ut_ad(!dict_index_is_ibuf(index));

    dict_stats_empty_index(index);
  }

  table->stat_initialized = true;

  dict_table_stats_unlock(table, RW_X_LATCH);
}

何时更新？

手动进行analyze table收集时

调用analyze时，会调用ha_innobase::info_low(uint flag, bool is_analyze)进行收集

/** Returns statistics information of the table to the MySQL interpreter, in
various fields of the handle object.
@param[in]      flag            what information is requested
@param[in]      is_analyze      True if called from "::analyze()".
@return HA_ERR_* error code or 0 */
intha_innobase::info_low(uint flag, bool is_analyze){
dict_table_t *ib_table;
uint64_t n_rows;
  DBUG_TRACE;
DEBUG_SYNC_C("ha_innobase_info_low");

首次打开表时
后台满足阈值进行触发时 代码截断部分展示

// 来自 dict0stats.cc
dberr_t dict_stats_update(dict_table_t *table, dict_stats_upd_option_t stats_upd_option)
{
    switch (stats_upd_option) {
        case DICT_STATS_RECALC_PERSISTENT:
            // ... 被 ANALYZE TABLE 或后台线程调用
            err = dict_stats_update_persistent(table);
            return dict_stats_save(table, nullptr);

        case DICT_STATS_FETCH_ONLY_IF_NOT_IN_MEMORY:
            // 当表统计未初始化时进入
            if (!table->stat_initialized) {
                return DB_SUCCESS;
            }
            // 尝试从持久化存储加载
            err = dict_stats_fetch_from_ps(t);
            if (err == DB_STATS_DO_NOT_EXIST) {
                if (dict_stats_auto_recalc_is_enabled(table)) {
                    //  如果启用自动重算 且 统计不存在，则触发重算！
                    return dict_stats_update(table, DICT_STATS_RECALC_PERSISTENT);
                }
            }
            // ...
    }
}

如果触发的时候优先选择第一个索引，通常是聚簇索引（Clustered Index），也就是主键索引进行统计

index = table->first_index();
  table->stat_n_rows =
      index->stat_n_diff_key_vals[dict_index_get_n_unique(index) - 1];

注： 普通 INSERT/UPDATE/DELETE 不会更新 stat_n_rows！即使你插入 100 万行，只要没触发统计更新，它就一直保持旧值（如 0）。

4. Cardinality 是怎么算出来的？

4.1 SHOW INDEX 的行为特殊

当你执行 SHOW INDEX，MySQL 需要返回 Cardinality，因此会主动检查并可能刷新索引统计。

if (need_index_stats) {
    innodb_stats_update_if_needed(table);  
}

源码路径（ha_innodb.cc）：


/** Match index columns between MySQL and InnoDB.
This function checks whether the index column information
is consistent between KEY info from mysql and that from innodb index.
@param[in]      key_info        Index info from mysql
@param[in]      index_info      Index info from InnoDB
@return true if all column types match. */
bool innobase_match_index_columns(const KEY *key_info,
                                  const dict_index_t *index_info) {
  const KEY_PART_INFO *key_part;
  const KEY_PART_INFO *key_end;
  const dict_field_t *innodb_idx_fld;
  const dict_field_t *innodb_idx_fld_end;

  DBUG_TRACE;

  /* Check whether user defined index column count matches */
  if (key_info->user_defined_key_parts != index_info->n_user_defined_cols) {
    return false;
  }

4.2 InnoDB 如何估算 Cardinality

InnoDB 使用页采样（Page Sampling）机制：