mysql8自动优化,mysql8 参考手册--优化器统计

该column_statistics数据字典表存储直方图统计有关列值，以供在构建查询执行计划的优化。要执行直方图管理，请使用以下ANALYZE TABLE语句；请参见第13.7.3.1节“ ANALYZE TABLE语句”。

column_statistics表具有以下特征：

1、该表包含除几何类型(空间数据)和之外的所有数据类型的列的统计信息 JSON。

2、该表是持久性的，因此不必在每次服务器启动时都创建列统计信息。

3、服务器对表执行更新；用户没有。

column_statistics用户无法直接访问 该表，因为它是数据字典的一部分。可以使用来获取直方图信息 INFORMATION_SCHEMA.COLUMN_STATISTICS，将其实现为数据字典表上的视图。 COLUMN_STATISTICS具有以下列：

1、SCHEMA_NAME， TABLE_NAME， COLUMN_NAME：架构，表和列的为其统计应用的名称。

2、HISTOGRAM： JSON描述列统计信息的值，存储为直方图。

列直方图包含存储在列中的部分值范围的存储桶。直方图是 JSON允许灵活地表示列统计信息的对象。这是一个示例直方图对象：

{

"buckets": [

[

1,

0.3333333333333333

],

[

2,

0.6666666666666666

],

[

3,

1

]

],

"null-values": 0,

"last-updated": "2017-03-24 13:32:40.000000",

"sampling-rate": 1,

"histogram-type": "singleton",

"number-of-buckets-specified": 128,

"data-type": "int",

"collation-id": 8

}

直方图对象具有以下键：

1、buckets：直方图桶。铲斗结构取决于直方图类型。

对于singleton直方图，存储桶包含两个值：

(1)、值1：值区的值。类型取决于列数据类型。

(2)、值2：一个双精度值，代表该值的累积频率。例如，.25和.75表示该列中的值的25％和75％小于或等于存储桶值。

对于equi-height直方图，存储桶包含四个值：

(1)、值1：2：存储桶的下限和上限。类型取决于列数据类型。

(2)、值3：双精度值，代表该值的累积频率。例如，.25和.75表示该列中的值的25％和75％小于或等于存储桶上限值。

(3)、值4：从存储区下限值到上限值的范围内的不同值的数量。

2、null-values：0.0到1.0之间的数字，表示作为SQL NULL值的列值的分数 。如果为0，则该列不包含任何 NULL值。

3、last-updated：生成直方图时，YYYY-MM-DD hh:mm:ss.uuuuuu格式为UTC值。

4、sampling-rate：0.0到1.0之间的数字，表示为创建直方图而采样的数据比例。值为1表示已读取所有数据(无采样)。

5、histogram-type：直方图类型：

singleton：一个存储桶代表该列中的一个单一值。当列中不同值的数量小于或等于ANALYZE TABLE 生成直方图的语句中指定的存储桶数时，将创建此直方图类型。

equi-height：一个存储桶代表一系列值。当列中不同值的数量大于ANALYZE TABLE生成直方图的语句中指定的存储桶数时，将创建此直方图类型 。

6、number-of-buckets-specified：在ANALYZE TABLE生成直方图的语句中指定的存储桶数。

7、data-type：此直方图包含的数据类型。从持久性存储读取直方图并将其解析到内存中时，这是必需的。值之一int，uint (无符号整数)， ，double， decimal，datetime或 string(包括字符和二进制串)。

8、collation-id：直方图数据的归类ID。data-type值为 时，这最有意义 string。值对应 ID于INFORMATION_SCHEMA.COLLATIONS 表中的列值 。

要从直方图对象中提取特定值，可以使用JSON操作。例如：

mysql> SELECT

TABLE_NAME, COLUMN_NAME,

HISTOGRAM->>'$."data-type"' AS 'data-type',

JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'bucket-count'

FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;

+-----------------+-------------+-----------+--------------+

| TABLE_NAME | COLUMN_NAME | data-type | bucket-count |

+-----------------+-------------+-----------+--------------+

| country | Population | int | 226 |

| city | Population | int | 1024 |

| countrylanguage | Language | string | 457 |

+-----------------+-------------+-----------+--------------+

优化程序将直方图统计信息(如果适用)用于要收集其统计信息的任何数据类型的列。优化器基于与固定值比较的列值比较的选择性(过滤效果)，应用直方图统计信息来确定行估计。这些形式的谓词可用于直方图：

col_name = constant

col_name <> constant

col_name != constant

col_name > constant

col_name < constant

col_name >= constant

col_name <= constant

col_name IS NULL

col_name IS NOT NULL

col_name BETWEEN constant AND constant

col_name NOT BETWEEN constant AND constant

col_name IN (constant[, constant] ...)

col_name NOT IN (constant[, constant] ...)

例如，这些语句包含符合直方图使用条件的谓词：

SELECT * FROM orders WHERE amount BETWEEN 100.0 AND 300.0;

SELECT * FROM tbl WHERE col1 = 15 AND col2 > 100;

与常数值进行比较的要求包括常数函数，例如 ABS()和 FLOOR()：

SELECT * FROM tbl WHERE col1 < ABS(-34);

直方图统计信息主要用于非索引列。向适用直方图统计信息的列添加索引也可能有助于优化器进行行估计。权衡是：

修改表数据时必须更新索引。

直方图仅根据需要创建或更新，因此在修改表数据时不会增加任何开销。另一方面，当进行表修改时，统计信息将逐渐变得过时，直到下次对其进行更新为止。

与从直方图统计数据中获得的估计相比，优化器更喜欢范围优化器的行估计。如果优化器确定范围优化器适用，则它不使用直方图统计信息。

对于被索引的列，可以使用索引转换获得行估计以进行相等性比较(请参见 第8.2.1.2节“范围优化”)。在这种情况下，直方图统计数据不一定有用，因为索引下潜可以产生更好的估计。

在某些情况下，使用直方图统计信息可能不会改善查询执行(例如，如果统计信息已过时)。要检查是否存在这种情况，请使用ANALYZE TABLE来重新生成直方图统计信息，然后再次运行查询。

或者，要禁用直方图统计信息，请使用 ANALYZE TABLE将其删除。禁用直方图统计信息的另一种方法是关闭系统变量的condition_fanout_filter标志 optimizer_switch(尽管这也可能会禁用其他优化)：

SET optimizer_switch='condition_fanout_filter=off';

如果使用直方图统计信息，则使用可以看到结果EXPLAIN。考虑以下查询，其中没有索引可用于column col1：

SELECT * FROM t1 WHERE col1 < 24;

如果直方图统计信息表明57％的行 t1满足该col1 < 24谓词，则即使没有索引也可以进行过滤，并EXPLAIN在该filtered列中显示57.00 。