【GreatSQL优化器-13】直方图

【GreatSQL优化器-13】直方图

一、直方图介绍

GreatSQL的优化器负责将SQL查询转换为尽可能高效的执行计划，但因为数据环境不断变化有可能导致优化器对查询数据了解不够充足，可能无法生成最优的执行计划进而影响查询效率，因此推出了直方图(histogram)功能来解决该问题。

直方图用于统计字段值的分布情况，向优化器提供统计信息。利用直方图，可以对一张表的一列数据做分布统计，估算WHERE条件中过滤字段的选择率，从而帮助优化器更准确地估计查询过程中的行数，选择更高效的查询计划。

直方图以灵活的JSON的格式存储。ANALYZE TABLE会基于表大小自动判断是否要进行取样操作。

ANALYZE TABLE也会基于表中列的数据分布情况以及bucket的数量来决定是否要建立等宽直方图（singleton）还是等高直方图（equi-height）。

下面用一个简单的例子来说明直方图是什么。

greatsql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT,date1 DATETIME);
greatsql> INSERT INTO t1 VALUES (1,10,'2021-03-25 16:44:00.123456'),(2,1,'2022-03-26 16:44:00.123456'),(3,4,'2023-03-27 16:44:00.123456'),(5,5,'2024-03-25 16:44:00.123456'),(7,null,'2020-03-25 16:44:00.123456'),(8,10,'2020-10-25 16:44:00.123456'),(11,16,'2023-03-25 16:44:00.123456');
greatsql> CREATE TABLE t2 (cc1 INT PRIMARY KEY, cc2 INT);
greatsql> INSERT INTO t2 VALUES (1,3),(2,1),(3,2),(4,3),(5,15);
greatsql> CREATE TABLE t3 (ccc1 INT, ccc2 varchar(100));
greatsql> INSERT INTO t3 VALUES (1,'aa1'),(2,'bb1'),(3,'cc1'),(4,'dd1'),(null,'ee');
greatsql> CREATE INDEX idx1 ON t1(c2);
greatsql> CREATE INDEX idx2 ON t1(c2,date1);
greatsql> CREATE INDEX idx2_1 ON t2(cc2);
greatsql> CREATE INDEX idx3_1 ON t3(ccc1);

系统自动创建buckets：
greatsql> ANALYZE TABLE t1 UPDATE HISTOGRAM ON c2,date1 WITH 3 BUCKETS;
greatsql> SELECT json_pretty(histogram)result from information_schema.column_statistics where table_name = 't1';
| {
  "buckets": [
    [
      1, 最小值
      5, 最大值
      0.42857142857142855, 频率
      3 key个数
    ],
    [
      10,
      10,
      0.7142857142857143,
      1
    ],
    [
      16,
      16,
      0.8571428571428571,
      1
    ]
  ],
  "data-type": "int",
  "null-values": 0.14285714285714285,
  "collation-id": 8,
  "last-updated": "2024-10-22 08:38:48.858099",
  "sampling-rate": 1.0,
  "histogram-type": "equi-height",
  "number-of-buckets-specified": 3
}                                                                                                                                     |
| {
  "buckets": [
    [
      "2020-03-25 16:44:00.000000",
      "2021-03-25 16:44:00.000000",
      0.42857142857142855,
      3
    ],
    [
      "2022-03-26 16:44:00.000000",
      "2023-03-27 16:44:00.000000",
      0.8571428571428571,
      3
    ],
    [
      "2024-03-25 16:44:00.000000",
      "2024-03-25 16:44:00.000000",
      1.0,
      1
    ]
  ],
  "data-type": "datetime",
  "null-values": 0.0,
  "collation-id": 8,
  "last-updated": "2024-10-22 08:38:48.859681",
  "sampling-rate": 1.0,
  "histogram-type": "equi-height",
  "number-of-buckets-specified": 3
} |

用户手动指定buckets：
greatsql> ANALYZE TABLE t2 UPDATE HISTOGRAM ON cc2 USING DATA '{"buckets": [[