mysql的information_schema库下COLUMN_STATISTICS表详解

`information_schema.COLUMN_STATISTICS` 表是 MySQL 8.0.16 及更高版本中引入的一个元数据表，用于存储关于表列的统计信息，特别是直方图数据。这些统计信息有助于优化器更准确地估计查询的成本，从而选择更高效的执行计划。

表结构

下面是 `information_schema.COLUMN_STATISTICS` 表的主要字段及其含义：

| 字段名 | 数据类型 | 描述 |
|---------------|--------------------|--------------------------------------------------------------|
| SCHEMA_NAME | varchar(64) | 表所在的数据库名称。 |
| TABLE_NAME | varchar(64) | 表的名称。 |
| COLUMN_NAME | varchar(64) | 列的名称。 |
| HISTOGRAM | json | 存储关于列值分布的直方图数据。 |

字段说明

- **`SCHEMA_NAME`**: 表所在的数据库名称。
- **`TABLE_NAME`**: 表的名称。
- **`COLUMN_NAME`**: 列的名称。
- **`HISTOGRAM`**: 以 JSON 格式存储的直方图数据，它包含了关于列值分布的信息。

直方图数据

直方图数据通常包括以下信息：
- **边界值**（boundary values）：每个区间内的最小和最大值。
- **区间计数**（bucket counts）：每个区间中的行数。
- **空值计数**（null count）：列中空值的数量。
- **非空值计数**（non-null count）：列中非空值的数量。
- **总行数**（total row count）：表中的总行数。

JSON 结构说明

以下是一个可能的 `HISTOGRAM` JSON 结构示例：

{
"buckets": [
{
"low_value": "1",
"high_value": "10",
"repeat_count": 1000,
"null_count": 0
},
{
"low_value": "11",
"high_value": "20",
"repeat_count": 1500,
"null_count": 0
},
{
"low_value": "21",
"high_value": "30",
"repeat_count": 1200,
"null_count": 0
}
],
"null_count": 100,
"totalRowCount": 3800
}

 

在这个示例中，`buckets` 数组包含了三个区间的数据，每个区间都包括了最小值 `low_value`、最大值 `high_value`、重复次数 `repeat_count` 和空值数量 `null_count`。此外，还有整个列的空值计数 `null_count` 和表中的总行数 `totalRowCount`。

示例

以下是一些使用 `information_schema.COLUMN_STATISTICS` 表的示例查询：

1. **获取特定数据库中所有表的列统计信息**:

SELECT TABLE_NAME, COLUMN_NAME, HISTOGRAM
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'your_database_name';

2. **获取特定表的所有列统计信息**:

SELECT TABLE_NAME, COLUMN_NAME, HISTOGRAM
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'your_database_name' AND TABLE_NAME = 'your_table_name';

3. **获取特定列的统计信息**:

SELECT TABLE_NAME, COLUMN_NAME, HISTOGRAM
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'your_database_name' AND TABLE_NAME = 'your_table_name' AND COLUMN_NAME = 'your_column_name';

 

 注意事项
- 直方图数据是由 MySQL 自动收集并维护的，可以通过运行 `ANALYZE TABLE` 或 `ANALYZE TABLE ... UPDATE INDEX STATISTICS` 命令来更新这些统计数据。
- `information_schema` 是一个只读模式的数据库，不能对其进行修改。
- 查询 `information_schema` 通常不需要锁定表，因此性能影响较小。
- 在查询 `information_schema` 时，确保替换 `'your_database_name'`, `'your_table_name'`, 和 `'your_column_name'` 为您具体的数据库、表和列名。

总结

`information_schema.COLUMN_STATISTICS` 表提供了有关表列的统计信息，特别是直方图数据。这些信息对于优化器来说非常重要，因为它可以根据这些统计信息来做出更好的查询优化决策。