MySQL新版本特性之：`MySQL 8.0`的`直方图`：`Optimizer`如何使用`直方图`来优化查询。

MySQL 8.0 直方图：Optimizer 的秘密武器

各位朋友，大家好！今天我们来深入探讨 MySQL 8.0 引入的一项重要特性：直方图。我们将重点关注 Optimizer 如何利用直方图来优化查询，提升数据库性能。

什么是直方图？

在数据库领域，直方图是对列中数据分布情况的统计摘要。它将列中的值范围划分为若干个桶（buckets），并记录每个桶内值的数量或频率。

为什么要使用直方图？

传统的统计信息（如最小值、最大值、平均值等）对于 Optimizer 来说，在某些情况下提供的信息不足以做出最佳的查询计划。例如，如果列中的数据分布极不均匀，某些值的出现频率远高于其他值，简单的统计信息就无法准确反映这种偏差。直方图则可以更精细地刻画数据分布，帮助 Optimizer 做出更明智的决策。

直方图的类型

MySQL 8.0 支持三种类型的直方图：

• 等高直方图（HEIGHT BALANCED）： 确保每个桶的高度（即包含的值的数量）大致相等。适用于数据分布相对均匀的情况。
• 等宽直方图（FREQUENCY BALANCED）： 确保每个桶的宽度（即值的范围）相等。适用于数据分布倾斜度较高的情况。
• 单值直方图（SINGLE PRECINCT）： 记录列中每个唯一值的频率。适用于列中存在大量重复值的情况。

直方图的创建与管理

创建直方图

可以使用 ANALYZE TABLE 语句来创建直方图。语法如下：

ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name;

例如，要为 employees 表的 salary 列创建直方图，可以执行：

ANALYZE TABLE employees UPDATE HISTOGRAM ON salary;

可以指定直方图的类型和桶的数量：

ANALYZE TABLE employees UPDATE HISTOGRAM ON salary WITH 10 BUCKETS;  -- 默认类型是等高直方图
ANALYZE TABLE employees UPDATE HISTOGRAM ON salary TYPE FREQUENCY WITH 20 BUCKETS; -- 创建等宽直方图

查看直方图信息

可以使用 INFORMATION_SCHEMA.COLUMN_STATISTICS 表来查看直方图信息。

SELECT
    HISTOGRAM
FROM
    INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE
    TABLE_SCHEMA = 'your_database_name'
    AND TABLE_NAME = 'employees'
    AND COLUMN_NAME = 'salary';

查询结果会返回一个 JSON 字符串，其中包含了直方图的详细信息，例如每个桶的边界值和频率。

删除直方图

可以使用 ANALYZE TABLE 语句来删除直方图。

ANALYZE TABLE table_name DROP HISTOGRAM ON column_name;

例如，要删除 employees 表的 salary 列的直方图，可以执行：

ANALYZE TABLE employees DROP HISTOGRAM ON salary;

Optimizer 如何使用直方图？

Optimizer 在评估查询计划时，会利用直方图来更准确地估计查询的代价，从而选择最优的执行计划。主要体现在以下几个方面：

1. 过滤条件的选择性估计

选择性（Selectivity）是指查询条件能够过滤掉的数据比例。Optimizer 需要准确估计选择性，才能确定是否应该使用索引，以及选择哪种索引。

• 范围查询： 对于范围查询（例如 salary > 50000），Optimizer 可以根据直方图中的桶信息，估算满足条件的行数。例如，如果直方图显示 salary 列的值主要集中在 40000 到 60000 之间，那么 salary > 50000 的选择性就会比较低，Optimizer 可能会选择全表扫描。
• IN 查询： 对于 IN 查询（例如 salary IN (50000, 60000, 70000)），Optimizer 可以根据直方图中每个值的频率，估算满足条件的行数。如果某些值在直方图中出现的频率很高，Optimizer 可能会选择使用索引。
• LIKE 查询： 对于 LIKE 查询（例如 name LIKE 'John%'），虽然直方图不能直接用于 LIKE 查询的选择性估计，但 Optimizer 可以利用直方图中的信息，结合其他统计信息，进行更精确的估计。

代码示例：

假设我们有一个 orders 表，其中包含 order_date 列，我们想要查询最近一个月的订单：

SELECT * FROM orders WHERE order_date >= '2023-10-01';

如果没有直方图，Optimizer 可能会根据 order_date 的最小值和最大值，假设数据分布均匀，从而高估或低估选择性。如果创建了 order_date 列的直方图，Optimizer 可以根据直方图中的桶信息，更准确地估计最近一个月内的订单数量，从而选择更优的执行计划（例如，使用索引或全表扫描）。

2. 连接代价的估计

在执行连接操作时，Optimizer 需要估计连接的代价，包括读取数据的代价和连接的代价。直方图可以帮助 Optimizer 更准确地估计连接结果集的大小，从而选择最优的连接算法（例如，嵌套循环连接、哈希连接、排序合并连接）。

• 连接选择性： 直方图可以帮助 Optimizer 估计连接条件的选择性，即连接后结果集的大小。如果连接条件的选择性很高（即连接后结果集很小），Optimizer 可能会选择嵌套循环连接；如果连接条件的选择性很低（即连接后结果集很大），Optimizer 可能会选择哈希连接或排序合并连接。

代码示例：

假设我们有两个表：customers 和 orders，我们需要根据 customer_id 进行连接：

SELECT * FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

如果没有直方图，Optimizer 可能会假设 customer_id 在两个表中均匀分布，从而错误地估计连接结果集的大小。如果创建了 customer_id 列的直方图，Optimizer 可以根据直方图中的信息，更准确地估计连接结果集的大小，从而选择更优的连接算法。例如，如果直方图显示 customers 表中的 customer_id 具有很高的重复性，而 orders 表中的 customer_id 相对唯一，那么 Optimizer 可能会选择哈希连接。

3. 索引选择

当存在多个可用的索引时，Optimizer 需要选择最合适的索引。直方图可以帮助 Optimizer 评估每个索引的选择性，从而选择最优的索引。

• 索引选择性： 直方图可以帮助 Optimizer 估计使用某个索引后，能够过滤掉的数据比例。选择性越高的索引，通常越能提高查询性能。

代码示例：

假设我们有一个 products 表，其中包含 category 和 price 两列，并且分别在两列上创建了索引。现在我们要查询某个类别的特定价格范围内的产品：

SELECT * FROM products WHERE category = 'Electronics' AND price BETWEEN 100 AND 200;

如果没有直方图，Optimizer 可能会简单地选择 category 或 price 索引，而无法确定哪个索引的选择性更高。如果创建了 category 和 price 列的直方图，Optimizer 可以根据直方图中的信息，评估每个索引的选择性，从而选择最优的索引。例如，如果直方图显示 category = 'Electronics' 的选择性较低，而 price BETWEEN 100 AND 200 的选择性较高，那么 Optimizer 可能会选择 price 索引。

表格总结 Optimizer 使用直方图的场景：

场景	直方图的作用	示例 SQL
范围查询	估计满足范围条件的行数，例如 WHERE date BETWEEN '2023-01-01' AND '2023-01-31'。 如果某些日期出现频率远高于其他日期，直方图可以提供更准确的估计。	SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
IN 查询	估计满足 IN 条件的行数，例如 WHERE product_id IN (1, 2, 3)。 如果某些 product_id 的出现频率远高于其他 product_id，直方图可以提供更准确的估计。	SELECT * FROM products WHERE product_id IN (1, 2, 3);
LIKE 查询	虽不能直接用于 LIKE 条件的选择性估计，但可以结合其他统计信息，进行更精确的估计，例如 WHERE product_name LIKE 'A%'。	SELECT * FROM products WHERE product_name LIKE 'A%';
连接操作	估计连接结果集的大小，从而选择最优的连接算法（嵌套循环连接、哈希连接、排序合并连接）。 如果连接键在两个表中分布不均匀，直方图可以提供更准确的估计。	SELECT * FROM customers c JOIN orders o ON c.customer_id = o.customer_id;
索引选择	评估不同索引的选择性，从而选择最优的索引。 例如，当多个索引都可能用于查询时，直方图可以帮助 Optimizer 评估哪个索引能够过滤掉更多的数据。	SELECT * FROM products WHERE category = 'Electronics' AND price BETWEEN 100 AND 200;

直方图的局限性

虽然直方图可以提高查询优化器的准确性，但也存在一些局限性：

• 存储空间： 直方图需要占用额外的存储空间，特别是对于包含大量唯一值的列。
• 维护成本： 当数据发生变化时，需要定期更新直方图，以保持其准确性。更新直方图会带来一定的性能开销。
• 不适用于所有场景： 对于数据分布非常均匀的列，直方图可能无法带来明显的性能提升。
• 只能提供近似估计： 直方图是对数据分布的统计摘要，只能提供近似的估计，而不能保证绝对的准确性。

实践建议

• 选择合适的直方图类型： 根据列的数据分布情况，选择合适的直方图类型。对于数据分布相对均匀的列，可以选择等高直方图；对于数据分布倾斜度较高的列，可以选择等宽直方图；对于列中存在大量重复值的情况，可以选择单值直方图。
• 控制桶的数量： 桶的数量越多，直方图的精度越高，但存储空间和维护成本也会越高。需要根据实际情况，权衡精度和性能。通常情况下，10 到 100 个桶就足够了。
• 定期更新直方图： 当数据发生变化时，需要定期更新直方图，以保持其准确性。可以使用 ANALYZE TABLE 语句来更新直方图。可以考虑使用定时任务或事件调度器来自动更新直方图。
• 监控查询性能： 创建直方图后，需要监控查询性能，以确保直方图能够带来预期的性能提升。可以使用 EXPLAIN 语句来查看查询计划，并分析直方图是否被正确使用。
• 不要过度使用： 直方图并非万能的，不应该为所有列都创建直方图。应该只为那些对查询性能有显著影响的列创建直方图。

总结与展望

MySQL 8.0 的直方图是一项强大的特性，可以帮助 Optimizer 更准确地估计查询代价，从而选择最优的执行计划。 然而，需要根据实际情况，合理地使用直方图，才能充分发挥其优势。 未来，我们可以期待 MySQL 在直方图方面有更多的改进和创新，例如：自动选择直方图类型、动态调整桶的数量、更高效的更新机制等。

希望今天的分享能够帮助大家更好地理解和使用 MySQL 8.0 的直方图特性。 谢谢大家！