探索MySQL 8.0的直方图（Histograms）功能以优化查询计划-mysql教程-PHP中文网

直方图通过提供数据分布的精细视图，解决传统统计信息在数据倾斜时预估不准的问题。传统统计仅依赖min/max、唯一值数量等粗略指标，当列数据严重倾斜（如status列中'active'占99%、'inactive'占1%）时，优化器可能误判选择性，导致错误选择执行计划（如全表扫描而非索引）。直方图将数据划分为桶（SINGLE_VALUE或TARGET类型），记录各值或范围的频率，使优化器能准确预估行数，从而正确选择索引扫描或连接策略。适用于数据倾斜列作为WHERE、JOIN、ORDER BY条件的场景，尤其能显著提升低频值查询的性能。使用时需识别关键列、选择合适类型与桶数（通常100-256），并通过ANALYZE TABLE创建，定期更新以保持统计新鲜。可通过查询mysql.column_stats查看直方图JSON信息。最佳实践包括结合EXPLAIN验证效果、低峰期更新、避免过度使用。挑战在于大表分析开销大、存储累积及过时直方图可能误导优化器，需权衡成本与收益。

探索mysql 8.0的直方图（histograms）功能以优化查询计划

MySQL 8.0的直方图功能，说白了，就是数据库优化器用来更精准地理解数据分布的一种高级“透视镜”。它能帮助优化器在面对那些数据分布不均匀（也就是我们常说的“数据倾斜”）的列时，做出更明智的查询计划决策，从而显著提升查询性能，避免一些看似简单却实际低效的执行路径。

解决方案

要利用MySQL 8.0的直方图，核心就是通过

ANALYZE TABLE

登录后复制

语句来创建和更新它们。这个过程其实挺直观的，但背后的选择和考量却需要一点点经验和理解。

我们可以为特定的列创建直方图，语法大致是这样的：

ANALYZE TABLE your_table_name UPDATE HISTOGRAM ON column_name WITH 100 BUCKETS;

登录后复制

这里，

column_name

登录后复制

是你希望优化器能更深入了解数据分布的列。

WITH 100 BUCKETS

登录后复制

则指定了直方图的桶数。桶数越多，对数据分布的描述就越精细，但同时也会增加一点点存储和分析的开销。通常，MySQL会根据数据类型和实际情况选择合适的桶数，但我们也可以手动指定。

MySQL 8.0提供了两种直方图类型：

SINGLE_VALUE
登录后复制
: 这种类型适用于那些 distinct value 数量相对较少，但某些值出现频率极高的列。它会记录每个独立值的频率。
TARGET
登录后复制
(默认): 这种类型则更适合那些 distinct value 数量较多，但数据分布依然存在倾斜的列。它会把数据范围分成多个桶，记录每个桶的边界和数据量。

创建后，优化器在评估涉及这些列的查询时，就会参考这些更详细的统计信息，而不是仅仅依赖传统的min/max、count、distinct count等粗略指标。你可以通过查询

mysql.column_stats

登录后复制

表来查看已创建的直方图信息，甚至可以看看它们具体长什么样。

SELECT * FROM mysql.column_stats WHERE db_name = 'your_database' AND table_name = 'your_table_name' AND column_name = 'your_column_name';

登录后复制

你会看到一个

histogram

登录后复制

字段，里面包含了直方图的JSON表示。虽然直接阅读可能有点晦涩，但它确实是优化器决策的依据。

MySQL直方图如何解决传统统计信息不足的问题？

在我看来，传统统计信息在很多场景下都表现得相当出色，它们简洁高效，足以让优化器做出八九不离十的判断。但问题在于，“八九不离十”有时候就意味着关键性的错误。传统的统计信息，比如列的最小值、最大值、总行数、唯一值数量等等，对于数据分布非常均匀的列来说，完全够用。然而，一旦数据分布出现明显的“倾斜”，比如某个状态码在几十万行数据中只出现了几次，而另一个状态码却占了绝大多数，这时候传统统计就可能完全“失灵”。

举个例子，假设我们有一个

status

登录后复制

列，其中

'active'

登录后复制

状态有99%的数据，而

'inactive'

登录后复制

只有1%。如果优化器仅仅知道这个列有2个唯一值，它可能会简单地认为每个值各占50%，或者根据一些启发式规则进行猜测。当你的查询是

SELECT * FROM orders WHERE status = 'inactive'

登录后复制

时，优化器可能会错误地估计出这是一个高选择性的查询（因为它认为只占50%），从而选择一个全表扫描而不是走索引（如果

status

登录后复制

列有索引的话），因为全表扫描对它来说成本更低。反之，如果查询是

status = 'active'

登录后复制

，它也可能做出错误的预估。

直方图的引入，就像是给优化器配备了一把“放大镜”。它不再是笼统地看数据范围，而是把数据分布切分成一个个小“桶”，每个桶里有多少数据，数据具体是什么，都清清楚楚。这样，当优化器看到

status = 'inactive'

登录后复制

时，它能准确地知道这个值只占了极小一部分数据，进而判断出这是一个高选择性的查询，毫不犹豫地选择使用索引。这种精细化的认知，正是直方图解决传统统计信息盲区，避免优化器“猜错”的关键所在。

在哪些场景下，MySQL 8.0的直方图能显著提升查询性能？

我个人在实践中发现，直方图的价值主要体现在几个特定的“痛点”场景：

360智图

AI驱动的图片版权查询平台

查看详情

数据严重倾斜的列作为过滤条件（
```
WHERE
```
登录后复制
子句）：这是最典型的应用场景。例如，一个
```
user_id
```
登录后复制
列，可能少数几个管理员账号的记录数非常多，而普通用户的记录数相对较少且分散。或者一个
```
category
```
登录后复制
列，某个品类占据了绝大部分商品，其他品类则很少。当查询条件涉及到这些倾斜的列时，直方图能帮助优化器准确预估返回的行数，从而选择正确的索引扫描（range scan, ref access等）或决定全表扫描。没有直方图，优化器可能因为错误的行数预估，放弃一个本该使用的索引，转而进行低效的全表扫描。
连接操作（
```
JOIN
```
登录后复制
条件）中的倾斜列：当两个表通过一个倾斜的列进行连接时，优化器需要精确地知道连接键的分布情况，才能选择最高效的连接算法（嵌套循环、哈希连接等）和连接顺序。如果一个表的一个连接列有很多重复值，而另一个表的对应列也有类似情况，传统统计信息可能导致优化器选择一个次优的连接策略。直方图能提供更细致的基数估计，帮助优化器找到最佳的连接路径。
多列索引中，前导列选择性不高但后续列有倾斜：虽然直方图主要针对单列，但在某些复杂查询中，如果优化器能通过直方图更好地理解某个列的分布，即使它不是复合索引的前导列，也可能间接影响优化器的决策，尤其是在涉及谓词下推或者复杂过滤条件时。
```
ORDER BY
```
登录后复制
或
GROUP BY
登录后复制
操作涉及的倾斜列：虽然不直接影响索引选择，但优化器在处理排序或分组时，如果能更准确地预估中间结果集的大小，也能更好地分配内存资源，或者选择更合适的排序算法。

举个例子，假设你有一个

events

登录后复制

表，其中有一个

event_type

登录后复制

列，大部分是

'page_view'

登录后复制

，但

'purchase'

登录后复制

事件很少。如果你经常查询

SELECT * FROM events WHERE event_type = 'purchase'

登录后复制

，并且

event_type

登录后复制

上有索引，但优化器却总是选择全表扫描。这时候，为

event_type

登录后复制

列创建直方图，优化器就能准确地知道

'purchase'

登录后复制

事件的行数非常少，从而选择使用索引，查询速度会得到显著提升。我见过不少这样的案例，一个简单的直方图就能把几秒的查询优化到几十毫秒。

创建和维护MySQL直方图有哪些最佳实践和潜在挑战？

在我多年的数据库优化工作中，直方图确实是个利器，但它也并非万能药，创建和维护上需要一些策略和考量。

最佳实践方面：

识别真正的“痛点”列：不是所有列都需要直方图。我们应该把精力放在那些确实存在数据倾斜，并且经常出现在
```
WHERE
```
登录后复制
、
```
JOIN
```
登录后复制
条件中，导致查询性能问题的列上。通过慢查询日志、
```
EXPLAIN
```
登录后复制
分析来定位这些列是关键。
选择合适的直方图类型和桶数：
- 如果列的唯一值很少，但某些值出现频率极高（比如状态码、性别），
```
SINGLE_VALUE
```
  登录后复制
  直方图是首选，它能精确记录每个值的频率。
- 如果列的唯一值很多，但数据在某个范围内密集分布（比如某个时间段的订单量特别大），
```
TARGET
```
  登录后复制
  直方图更合适。桶数（
```
BUCKETS
```
  登录后复制
  ）的选择通常不需要太纠结，默认值或者100-256个桶对大多数情况都够用了。过多的桶数会增加分析和存储开销，收益却不一定线性增长。
定期更新直方图：数据是会变化的，直方图也需要保持新鲜。对于数据变化频繁的表和列，你需要制定一个策略来定期更新直方图。这可以通过MySQL事件调度器（
```
CREATE EVENT
```
登录后复制
）或者外部的调度工具（如Cron）来完成。更新频率取决于数据变化的速率和对查询性能的敏感度。我通常建议在业务低峰期进行更新，以减少对生产环境的影响。
监控和验证：创建直方图后，务必通过
```
EXPLAIN
```
登录后复制
再次检查受影响的查询计划，看看优化器是否真的选择了更优的路径。同时，也要关注查询性能指标是否有实际的提升。

潜在挑战方面：

创建和更新的开销：
```
ANALYZE TABLE ... UPDATE HISTOGRAM
```
登录后复制
操作需要扫描表的数据，对于非常大的表，这个过程可能会消耗显著的I/O和CPU资源，甚至可能导致表被锁定（尽管MySQL 8.0在某些情况下可以无锁执行）。因此，选择合适的时机（比如业务低峰期）和策略（比如分批处理）至关重要。
存储开销：直方图数据存储在
```
mysql.column_stats
```
登录后复制
表中。虽然单个直方图的存储量不大，但如果为大量表的大量列都创建直方图，累积起来也可能占用一定的空间。不过，通常这并不是一个主要问题。
过度使用可能适得其反：并不是所有列都需要直方图，也不是所有的查询性能问题都能靠直方图解决。如果盲目地为所有列创建直方图，不仅增加了维护负担，还可能因为优化器需要处理更多统计信息而略微增加查询编译时间（虽然通常微乎其微）。更重要的是，如果直方图没有被正确更新，过时的直方图数据反而可能误导优化器，导致性能下降。
复杂查询的局限性：直方图主要针对单列的分布。对于涉及多列之间复杂关联关系、函数计算或非常规操作的查询，直方图的作用可能会受到限制。优化器依然需要依赖其他统计信息和启发式规则。