MySQL高级讲座篇之：`Persistent Statistics`：如何确保查询优化器使用稳定的统计信息？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位老铁，晚上好！我是今晚的主讲人，很高兴和大家一起聊聊MySQL里的“持久化统计信息 (Persistent Statistics)”。这玩意儿，说白了，就是让MySQL的查询优化器，在做决策的时候，手里拿着的“情报”更靠谱、更稳定，避免瞎指挥，最终提升查询效率。

废话不多说，咱们直接进入正题。

一、啥是统计信息？为啥重要？

首先，得搞清楚啥是“统计信息”。你可以把它想象成MySQL对表里数据情况的一个大致“摸底”。优化器根据这些信息，来判断用哪个索引、用啥顺序连接表，才能最快地找到你要的数据。

常见的统计信息包括：

行数 (row count): 表里有多少行数据。
唯一值数量 (NDV – Number of Distinct Values): 某个列有多少个不同的值。比如，gender列可能只有’男’和’女’两个值，NDV就是2。
NULL值数量: 某个列有多少个NULL值。
直方图 (Histogram): 记录某个列的数据分布情况，比如哪些值比较常见，哪些值比较稀有。

举个例子，假设咱们有个 orders 表，记录了用户的订单信息：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    product_category VARCHAR(255),
    INDEX idx_user_id (user_id),
    INDEX idx_order_date (order_date),
    INDEX idx_product_category (product_category)
);

-- 插入一些测试数据
INSERT INTO orders (order_id, user_id, order_date, amount, product_category) VALUES
(1, 101, '2023-01-15', 100.00, 'Electronics'),
(2, 102, '2023-02-20', 250.50, 'Clothing'),
(3, 101, '2023-03-10', 50.00, 'Electronics'),
(4, 103, '2023-04-05', 120.75, 'Books'),
(5, 102, '2023-05-12', 300.00, 'Clothing'),
(6, 104, '2023-06-22', 80.20, 'Electronics'),
(7, 103, '2023-07-01', 150.00, 'Books'),
(8, 105, '2023-08-18', 200.00, 'Home Goods'),
(9, 104, '2023-09-28', 60.50, 'Electronics'),
(10, 106, '2023-10-08', 280.00, 'Clothing'),
(11, 101, '2023-11-15', 90.00, 'Electronics'),
(12, 102, '2023-12-20', 240.50, 'Clothing'),
(13, 107, '2024-01-10', 70.00, 'Electronics'),
(14, 103, '2024-02-05', 110.75, 'Books'),
(15, 102, '2024-03-12', 290.00, 'Clothing');

现在，如果咱们要查某个用户的订单：

SELECT * FROM orders WHERE user_id = 101;

优化器需要考虑：是用 idx_user_id 索引快呢？还是全表扫描更快？这时候，user_id 列的 NDV 就很重要了。

如果 user_id 的 NDV 很高 (比如每个用户都有唯一的ID)，说明每个 user_id 对应的数据行比较少，用索引效率更高。
如果 user_id 的 NDV 很低 (比如大部分订单都属于少数几个用户)，说明每个 user_id 对应的数据行很多，用索引可能还不如全表扫描。

所以，准确的统计信息，是优化器做出正确决策的关键！

二、统计信息更新：MySQL咋做的？

MySQL会定期或者在某些操作后，自动更新统计信息。主要通过 ANALYZE TABLE 命令来完成。当然，MySQL也会自动触发。

自动更新： MySQL有个参数 innodb_stats_auto_recalc (默认是ON)，控制是否自动重新计算统计信息。还有一个参数 innodb_stats_persistent (默认是OFF，稍后会详细说)，控制统计信息是否持久化。
手动更新： 你可以手动执行 ANALYZE TABLE 命令，强制更新表的统计信息。

ANALYZE TABLE orders;

这条命令会扫描 orders 表，重新计算统计信息。

三、问题来了：统计信息“易变心”？

传统的MySQL (5.6之前，以及 innodb_stats_persistent = OFF 的情况)，统计信息是存储在内存里的。这就带来一个问题：

服务器重启： 统计信息丢失！每次重启后，优化器都得重新“摸底”，这需要时间，而且在“摸底”完成前，优化器可能做出错误的决策。
数据变化： 表里的数据一直在变，统计信息会过时。比如你删除了大量数据，但统计信息还没更新，优化器可能还会认为表很大，继续使用效率低的查询计划。

这就好比，你明明换了发型，但你的朋友还以为你还是原来的样子，给你提了不合适的建议。

四、 Persistent Statistics：让统计信息“持久”！

MySQL 5.6 引入了 Persistent Statistics，就是为了解决这个问题。简单来说，它把统计信息存储在磁盘上，而不是仅仅放在内存里。这样，即使服务器重启，统计信息也不会丢失。

要启用 Persistent Statistics，需要设置两个参数：

SET GLOBAL innodb_stats_persistent = ON;
SET GLOBAL innodb_stats_persistent_sample_pages = 20;

innodb_stats_persistent = ON：开启持久化统计信息。
innodb_stats_persistent_sample_pages：指定采样页的数量。MySQL不会扫描整个表来计算统计信息，而是随机抽取一些页进行分析。这个值越大，统计信息越准确，但分析时间也越长。默认值是20。

注意，这两个参数是全局参数，需要重启MySQL才能生效。当然，也可以只对特定表开启持久化统计：

ALTER TABLE orders STATS_PERSISTENT = 1;

这样，只有 orders 表的统计信息会被持久化。如果要关闭，可以设置为0：

ALTER TABLE orders STATS_PERSISTENT = 0;

五、 Persistent Statistics 的好处：

更稳定的查询计划： 由于统计信息不会丢失，优化器可以始终使用相对准确的信息来生成查询计划，避免因为统计信息不准确而导致性能波动。
减少重启后的性能下降： 服务器重启后，不需要重新计算统计信息，优化器可以立即使用已有的信息，减少了重启后的性能下降。
更可预测的性能： 由于统计信息更加稳定，查询性能也更加可预测，方便我们进行性能调优。

六、 Persistent Statistics 的一些注意事项：

存储空间： 持久化统计信息会占用一些磁盘空间，但通常来说，这个空间占用很小，可以忽略不计。
更新频率： 即使启用了持久化统计信息，也需要定期更新统计信息，以保证其准确性。可以通过 ANALYZE TABLE 命令手动更新，或者依赖MySQL的自动更新机制。自动更新的频率由 innodb_stats_auto_recalc 和 innodb_stats_persistent_sample_pages 参数共同决定。
采样页数量： innodb_stats_persistent_sample_pages 参数决定了采样页的数量。数量越大，统计信息越准确，但分析时间也越长。需要根据实际情况进行调整。对于数据量很大的表，可以适当增加采样页数量。
数据倾斜： 如果表里的数据分布非常不均匀 (比如某个列只有少数几个值，但这些值对应的行数非常多)，Persistent Statistics 可能无法准确地反映数据的真实情况。这时候，可以考虑使用直方图 (Histogram) 来记录数据的分布情况。

七、直方图 (Histogram)：更精细的数据分布描述

直方图可以更详细地描述某个列的数据分布情况，尤其是在数据倾斜比较严重的情况下。 MySQL支持两种类型的直方图：

等高直方图 (Equi-Height Histogram): 将数据分成若干个桶 (bucket)，每个桶的高度 (包含的行数) 大致相等。
等宽直方图 (Equi-Width Histogram): 将数据分成若干个桶，每个桶的宽度 (值的范围) 相等。

要创建直方图，可以使用 ANALYZE TABLE ... UPDATE HISTOGRAM 命令：

ANALYZE TABLE orders UPDATE HISTOGRAM ON amount WITH 10 BUCKETS;

这条命令会在 amount 列上创建一个包含10个桶的直方图。

创建直方图后，优化器在执行查询时，会参考直方图的信息，做出更准确的决策。比如，如果 amount 列的数据分布很不均匀，大部分订单的金额都很小，只有少数订单的金额很大，那么直方图可以帮助优化器更好地评估不同金额范围的订单数量，从而选择更合适的索引或连接方式。

八、一些实战技巧

监控统计信息： 可以通过查询 INFORMATION_SCHEMA.TABLES 表，查看表的统计信息更新时间：

SELECT TABLE_NAME, UPDATE_TIME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'orders';

定期更新统计信息： 建议定期执行 ANALYZE TABLE 命令，更新表的统计信息。可以通过定时任务来实现。
关注慢查询： 如果发现有慢查询，可以先检查一下统计信息是否准确。如果统计信息不准确，可以尝试手动更新统计信息，看看是否能解决问题。
分析执行计划： 使用 EXPLAIN 命令，分析查询的执行计划，看看优化器是否选择了合适的索引和连接方式。如果优化器选择了错误的执行计划，可能是因为统计信息不准确，或者索引设计不合理。
测试不同参数： 可以尝试调整 innodb_stats_persistent_sample_pages 参数，看看是否能提升查询性能。但要注意，调整参数前，一定要进行充分的测试，避免引入新的问题。

九、总结

Persistent Statistics 是MySQL的一项重要特性，它可以让优化器使用更稳定的统计信息，从而生成更可靠的查询计划，提升查询性能。启用 Persistent Statistics，并定期更新统计信息，是保证MySQL数据库性能的重要手段。

但是，Persistent Statistics 并不是万能的。在某些情况下，比如数据倾斜严重，或者统计信息更新不及时，Persistent Statistics 可能无法完全解决性能问题。这时候，需要结合其他技术，比如直方图、索引优化、查询重写等，才能达到更好的效果。

记住，优化数据库性能是一个持续不断的过程，需要不断学习和实践，才能掌握其中的奥秘。

好了，今天的讲座就到这里。感谢大家的聆听！如果有什么问题，欢迎随时提问。祝大家工作顺利，早日成为MySQL大神！

发表回复 取消回复

发表回复取消回复