发布于 由admin

MySQL高阶讲座之：`MySQL`的`Histogram`直方图：其在`Cardinality`估算中的作用。

各位观众老爷们，晚上好！今天咱们聊点MySQL内部的“黑魔法”——直方图 (Histogram)。别怕，听起来高大上，其实就是个更精准的“数字饼图”，能让MySQL在优化查询时更聪明。

一、开场白：Cardinality估算的重要性

在深入直方图之前，咱们先得明白一个概念：Cardinality（基数）。简单来说，就是某列有多少个不同的值。比如，一个gender列，通常只有“男”和“女”两种，那它的Cardinality就是2。而一个user_id列，每个用户都有唯一的ID，那它的Cardinality就接近于表里的总行数。

为啥Cardinality重要？因为它直接影响MySQL的查询优化。MySQL的优化器会根据Cardinality来判断走哪个索引效率更高，甚至决定是否全表扫描。如果Cardinality估算不准，优化器就可能“脑抽”，选一个效率很低的执行计划，导致查询慢如蜗牛。

举个例子，假设我们有个users表，有个city列，建了索引。现在要查居住在“北京”的用户：

SELECT * FROM users WHERE city = '北京';

如果MySQL估算city = '北京'的行数很少，它可能会选择走city索引。但如果实际上大部分用户都住在北京，走索引可能还不如直接全表扫描快。

所以，准确的Cardinality估算是查询优化的基石！

二、传统Cardinality估算的问题：统计信息和采样

MySQL是怎么估算Cardinality的呢？它主要靠两种方式：

  1. 统计信息（Statistics）： MySQL会定期（或者手动）收集表的统计信息，包括总行数、索引的Cardinality等。这些信息存在INFORMATION_SCHEMA.TABLES和INFORMATION_SCHEMA.STATISTICS表中。

  2. 采样（Sampling）： 如果统计信息不够新，或者MySQL觉得需要更精确的估算，它会随机抽取一些行，根据抽样结果来估算Cardinality。

但是，这两种方式都有局限性：

  • 数据倾斜： 如果数据分布不均匀，比如大部分数据集中在少数几个值上，采样很容易“错过”这些值，导致Cardinality估算偏差很大。
  • 统计信息过期： 如果表的数据变化很快，统计信息很快就会过时，失去参考价值。

例如，还是users表的city列，假设90%的用户住在北京，其他城市的用户零星分布。简单的采样可能抽不到北京的用户，或者抽到的比例远低于90%，导致MySQL误以为city = '北京'的行数很少。

三、直方图：更精准的Cardinality估算

为了解决数据倾斜和统计信息过期的问题，MySQL 8.0引入了直方图 (Histogram)。直方图可以更精确地描述列的数据分布情况，让MySQL在估算Cardinality时更有底气。

直方图本质上就是把列的值分成若干个“桶”（bucket），然后记录每个桶里有多少数据。这样，即使数据分布不均匀，也能更准确地反映出来。

MySQL支持两种类型的直方图：

  • 等高直方图（Height-Balanced Histograms）： 每个桶包含的行数大致相同。适合数据分布比较均匀的情况。
  • 等频直方图（Frequency Histograms）： 每个桶包含的值的范围大致相同。适合数据分布不均匀的情况。MySQL默认使用等频直方图。

四、创建和查看直方图

创建直方图的语法很简单：

ANALYZE TABLE users UPDATE HISTOGRAM ON city WITH 10 BUCKETS;

这条SQL的意思是：分析users表，并为city列创建一个包含10个桶的直方图。ANALYZE TABLE命令会触发MySQL去收集统计信息，包括创建直方图。WITH 10 BUCKETS指定了桶的数量，桶越多，直方图越精确，但也会占用更多的存储空间。

查看直方图的信息，需要查询INFORMATION_SCHEMA.COLUMN_STATISTICS表：

SELECT
    COLUMN_NAME,
    HISTOGRAM
FROM
    INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE
    TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'city';

查询结果的HISTOGRAM列会返回一个JSON字符串，包含了直方图的详细信息，包括每个桶的边界值和频率。这个JSON可读性不太好，需要用JSON解析工具才能看懂。

五、直方图的应用：Cardinality估算实例

有了直方图，MySQL在估算Cardinality时就有了更可靠的依据。还是以users表的city列为例，假设我们已经创建了直方图。

当我们执行以下查询时：

SELECT * FROM users WHERE city = '北京';

MySQL会先查看city列的直方图，找到包含“北京”这个值的桶，然后根据这个桶的频率来估算city = '北京'的行数。

如果直方图显示“北京”这个值所在的桶的频率很高，MySQL就会估算city = '北京'的行数很多，从而可能选择全表扫描。反之，如果频率很低，MySQL就会选择走city索引。

六、直方图的局限性

虽然直方图很强大，但也不是万能的。它也有一些局限性：

  • 存储空间： 直方图需要占用额外的存储空间，尤其是当桶的数量很多时。
  • 维护成本： 当表的数据发生变化时，直方图也需要更新，否则就会过时。更新直方图需要消耗一定的资源。
  • 只支持部分数据类型： MySQL的直方图只支持数值类型、字符串类型和日期类型。不支持JSON类型等复杂数据类型。
  • 只用于单列： 直方图只能针对单个列创建，不能用于多列组合。如果要估算多个列的组合条件，直方图就无能为力了。

七、直方图的最佳实践

为了充分发挥直方图的作用，需要遵循一些最佳实践：

  • 选择合适的桶的数量： 桶的数量越多，直方图越精确，但也会占用更多的存储空间。一般来说，10-100个桶就足够了。
  • 定期更新直方图： 当表的数据发生变化时，要及时更新直方图，以保证其准确性。可以设置定时任务来自动更新直方图。
  • 只为重要的列创建直方图： 不是所有的列都需要创建直方图。只需要为那些数据倾斜严重、经常用于查询条件的列创建直方图。
  • 监控直方图的使用情况： 可以通过查询performance_schema数据库来监控直方图的使用情况，例如直方图被用于查询的次数、直方图的更新时间等。

八、代码示例：手动更新直方图的存储过程

为了方便定期更新直方图，我们可以创建一个存储过程：

DELIMITER //

CREATE PROCEDURE update_histograms(IN table_schema VARCHAR(64), IN table_name VARCHAR(64))
BEGIN
    DECLARE column_name VARCHAR(64);
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
        WHERE TABLE_SCHEMA = table_schema AND TABLE_NAME = table_name AND HISTOGRAM IS NOT NULL;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO column_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @sql = CONCAT('ANALYZE TABLE `', table_schema, '`.`', table_name, '` UPDATE HISTOGRAM ON `', column_name, '`;');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

-- 调用存储过程更新 users 表的直方图
CALL update_histograms('your_database_name', 'users');

这个存储过程会遍历指定表中所有已经创建了直方图的列，然后逐个更新直方图。可以结合事件调度器（Event Scheduler）来定期调用这个存储过程。

九、案例分析：直方图解决慢查询问题

假设我们有一个orders表，记录了用户的订单信息，其中order_status列表示订单状态，例如“待付款”、“已付款”、“已发货”、“已完成”、“已取消”等。

由于某些原因，大部分订单都是“已完成”状态，导致order_status列的数据倾斜非常严重。

如果没有直方图，当我们执行以下查询时：

SELECT * FROM orders WHERE order_status = '待付款';

MySQL可能会误以为order_status = '待付款'的行数很多，从而选择全表扫描，导致查询很慢。

但是，如果我们为order_status列创建了直方图，MySQL就能更准确地估算order_status = '待付款'的行数很少，从而选择走索引，提高查询效率。

十、总结：直方图是优化利器，但要用对地方

总而言之，直方图是MySQL优化器中的一个利器，可以更精确地估算Cardinality，从而优化查询计划，提高查询效率。但是，直方图也不是万能的，需要根据实际情况选择合适的桶的数量、定期更新，并且只为重要的列创建直方图。

记住，优化没有银弹，只有最适合你的解决方案！

最后，给大家留个思考题：

如果你的MySQL版本不支持直方图，或者直方图的效果不理想，还有哪些方法可以解决数据倾斜导致的查询慢问题？

希望今天的讲座对大家有所帮助！下次有机会再跟大家分享MySQL的其他“黑魔法”。祝大家工作顺利，生活愉快！ 拜拜！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注