MySQL高级讲座篇之：查询优化器的决策：CBO（基于成本优化）的原理与统计信息的角色。

各位观众老爷，晚上好！今天咱们来聊聊MySQL查询优化器里那个神秘又强大的家伙——CBO（Cost-Based Optimizer，基于成本优化）。这玩意儿就像数据库界的“诸葛亮”，专门琢磨怎么用最低的成本把你的SQL查出来。

咱们先从一个“血淋淋”的例子开始，看看没有CBO，数据库会变成什么样：

-- 假设我们有两张表：orders(订单表) 和 customers(客户表)
-- orders 表结构：order_id, customer_id, order_date, total_amount
-- customers 表结构：customer_id, customer_name, city

-- 糟糕的查询，没有索引，CBO不存在，全表扫描警告！
SELECT c.customer_name, SUM(o.total_amount)
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
AND c.city = 'New York'
GROUP BY c.customer_name;

如果数据库没有CBO，或者CBO失效了（比如统计信息严重过时），它很可能会傻乎乎地对 orders 和 customers 表都进行全表扫描，然后做笛卡尔积，再过滤，再分组。这酸爽，简直不敢想象！特别是当这两张表数据量都很大的时候，你的数据库服务器估计会直接躺倒给你看。

所以，CBO的作用就是避免这种惨剧发生，它会根据各种信息（主要是统计信息）来估算不同执行计划的成本，然后选择成本最低的那个。

一、CBO的核心思想：算账！

CBO的核心思想很简单，就是“算账”。它会把每个可能的执行计划都算一遍，看看哪个计划花的“钱”最少。这里的“钱”不是真的钱，而是数据库的资源消耗，比如CPU时间、IO次数、内存使用等等。

为了算账，CBO需要知道一些关键信息，这些信息就是我们接下来要重点讨论的统计信息。

二、统计信息：CBO的“情报”来源

统计信息是CBO的“情报”，告诉它数据库里都有哪些“妖魔鬼怪”，以及它们的实力如何。常见的统计信息包括：

1. 表统计信息：

   • 行数（rows）: 表里有多少行数据。 这就像你知道一个国家有多少人口。
   • 平均行长度（avg_row_length）: 平均每行数据占用多少存储空间。 这就像你知道平均每个人占用的居住面积。
   • 表大小（data_length）: 表数据占用的总存储空间。 这就像你知道整个国家的总居住面积。
   • 索引长度（index_length）: 索引占用的总存储空间。 这就像你知道全国的道路总长度。
   • 自增列的下一个值（auto_increment）: 如果有自增列，下一个要插入的值是多少。
   • 数据校验和(checksum): 表数据的校验和，用于检测数据损坏。

   你可以通过 SHOW TABLE STATUS LIKE 'your_table_name'; 来查看表统计信息。

   SHOW TABLE STATUS LIKE 'orders';

   输出结果类似：

   Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
   orders	InnoDB	10	Dynamic	1000	163	163840	0	0	0	1001	2023-10-27 20:00:00	2023-10-27 20:00:00	NULL	utf8_bin	NULL

2. 索引统计信息：

   • Cardinality (基数): 索引中不同值的数量。 这是最重要的一个！ 这就像你知道一个城市有多少不同的姓氏。 基数越高，索引的选择性越好，CBO就越倾向于使用这个索引。
   • 索引块的数量: 索引占用了多少个数据块。
   • 索引的密度: 索引的紧凑程度。

   你可以通过 SHOW INDEX FROM your_table_name; 来查看索引统计信息。

   SHOW INDEX FROM orders;

   输出结果类似：

   Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
   orders	0	PRIMARY	1	order_id	A	1000	NULL	NULL		BTREE			YES	NULL
   orders	1	customer_id	1	customer_id	A	100	NULL	NULL		BTREE			YES	NULL

   注意看 Cardinality 这一列！

3. 列统计信息 (Column Statistics): 从MySQL 8.0开始，还引入了列统计信息，提供了更精细的数据分布信息，比如直方图（Histograms）。直方图可以更准确地描述列中值的分布情况，尤其是在数据倾斜的情况下，可以帮助CBO做出更明智的决策。

   • 直方图 (Histograms): 记录列中值的分布情况，特别是对于非均匀分布的数据。

   你可以通过 ANALYZE TABLE your_table_name UPDATE HISTOGRAM ON your_column_name; 来创建或更新直方图。 注意：创建直方图可能会比较耗时，特别是对于大表。

   ANALYZE TABLE orders UPDATE HISTOGRAM ON total_amount;

   然后，你可以通过 information_schema.column_statistics 表来查看直方图信息。

   SELECT histogram FROM information_schema.column_statistics
   WHERE table_name = 'orders' AND column_name = 'total_amount';

三、CBO如何利用统计信息“算账”？

有了这些统计信息，CBO就可以开始“算账”了。它会估算每个执行计划的成本，成本的计算公式通常比较复杂，涉及到很多因素，但大致可以概括为以下几个方面：

1. IO成本： 从磁盘读取数据的成本。 全表扫描的IO成本通常很高，而使用索引可以大大降低IO成本。 CBO会根据表的行数、索引的基数等信息来估算IO成本。

2. CPU成本： 处理数据的成本，比如比较、排序、过滤等等。 全表扫描需要对每一行数据进行处理，CPU成本也很高。 CBO会根据表的行数、过滤条件、排序规则等信息来估算CPU成本。

3. 内存成本： 使用内存的成本，比如排序、分组等等。 如果数据量太大，内存放不下，就需要使用磁盘进行排序或分组，这会大大增加成本。 CBO会根据数据量和可用内存来估算内存成本。

举个例子，假设我们要执行以下查询：

SELECT * FROM orders WHERE customer_id = 123;

如果 customer_id 列上有索引，CBO可能会考虑以下两种执行计划：

• 计划A： 使用索引查找 customer_id = 123 的行。 IO成本较低，但需要读取索引块。
• 计划B： 全表扫描 orders 表，然后过滤 customer_id = 123 的行。 IO成本较高，但不需要读取索引块。

CBO会根据 orders 表的行数、customer_id 索引的基数等信息来估算这两种计划的成本。 如果 customer_id 索引的基数很高（意味着有很多不同的 customer_id 值），那么计划A的成本可能会更低，因为它可以快速定位到 customer_id = 123 的行。 反之，如果 customer_id 索引的基数很低（意味着很少有不同的 customer_id 值），那么计划B的成本可能会更低，因为索引查找的开销可能比全表扫描还要大。

四、统计信息的重要性：关系到CBO判断的准确性

统计信息是CBO做出正确决策的关键。如果统计信息不准确，CBO就会做出错误的判断，选择错误的执行计划，导致查询性能下降。 这就像你给诸葛亮提供假情报，他再聪明也得打败仗。

以下是一些常见的情况，会导致统计信息不准确：

• 表数据发生了大量修改： 比如插入、删除、更新了大量数据，导致表的行数、索引的基数等信息发生了变化。
• 长时间没有更新统计信息： 数据库不会自动更新统计信息，需要手动执行 ANALYZE TABLE 命令来更新。
• 采样率不足： ANALYZE TABLE 命令通常会采样一部分数据来估算统计信息，如果采样率不足，可能会导致统计信息不准确。

如何更新统计信息？

使用 ANALYZE TABLE 命令来更新统计信息。

ANALYZE TABLE orders;  -- 更新 orders 表的统计信息
ANALYZE TABLE customers; -- 更新 customers 表的统计信息
ANALYZE TABLE orders UPDATE HISTOGRAM ON total_amount; -- 更新 orders 表 total_amount 列的直方图

建议在以下情况下更新统计信息：

• 表数据发生了大量修改之后。
• 定期更新，比如每天或每周更新一次。
• 在查询性能出现问题时，首先尝试更新统计信息。

五、如何查看CBO选择了哪个执行计划？

使用 EXPLAIN 命令可以查看CBO选择的执行计划。

EXPLAIN SELECT c.customer_name, SUM(o.total_amount)
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
AND c.city = 'New York'
GROUP BY c.customer_name;

EXPLAIN 命令会输出一个表格，其中包含了执行计划的各个步骤的信息，比如使用的索引、扫描的行数、连接的类型等等。通过分析 EXPLAIN 的输出，你可以了解CBO是如何执行你的查询的，以及是否存在性能瓶颈。

EXPLAIN 输出结果各列的含义：

列名	含义
id	查询的序列号，表示查询中执行select子句或操作表的顺序。id值越大优先级越高，id值相同则从上往下执行，id值为NULL最后执行。
select_type	查询类型，常见的值有 SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT 等。
table	正在访问的表名。
partitions	如果查询是基于分区表的，显示查询将访问的分区。
type	访问类型，表示MySQL决定如何查找表中的行。从最优到最差的类型依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
possible_keys	可能使用的索引，指出MySQL能使用哪个索引在该表中找到行。如果该列是NULL，则没有相关的索引。
key	实际使用的索引。如果为NULL，则表示没有使用索引。
key_len	使用的索引的长度，表示在索引里使用的字节数。通过该值，可以计算具体使用了索引中的哪些字段。
ref	显示了之前的表在key列记录的索引中查找值所用的列或常量。
rows	估算的需要检查的行数。这个值只是一个估计值，并不总是准确的。
filtered	表示返回结果的行数占需要读取行数的百分比。filtered 的值越大越好。
Extra	包含MySQL解决查询的详细信息，常见的值有：Using index、Using where、Using temporary、Using filesort、Using join buffer 等。

六、一些优化建议：

1. 确保统计信息是最新的： 定期更新统计信息，尤其是在表数据发生大量修改之后。

2. 创建合适的索引： 根据查询的需要，创建合适的索引。 索引可以大大提高查询性能，但过多的索引也会增加维护成本。

3. 避免全表扫描： 尽量避免全表扫描，可以使用索引来优化查询。 可以通过 EXPLAIN 命令来检查查询是否使用了全表扫描。

4. 优化SQL语句： 编写高效的SQL语句，避免不必要的计算和数据转换。

5. 使用分区表： 对于大表，可以使用分区表来提高查询性能。

6. 考虑使用物化视图： 对于复杂的查询，可以考虑使用物化视图来预先计算结果，提高查询性能。

7. 关注慢查询日志： MySQL的慢查询日志可以记录执行时间超过指定阈值的查询，通过分析慢查询日志，可以找到需要优化的查询。

七、CBO的局限性

虽然CBO很强大，但它也不是万能的。CBO的决策是基于统计信息的，如果统计信息不准确，或者查询过于复杂，CBO可能会做出错误的判断。此外，CBO的成本模型也不是完美的，它只能估算成本，而不能精确计算成本。

• 统计信息不准确： 如前所述，统计信息是CBO的基础。 如果统计信息过时或不准确，CBO会做出错误的决策。

• 复杂的查询： 对于复杂的查询，CBO可能无法找到最优的执行计划。 例如，包含多个JOIN、子查询、UNION等的查询。

• 成本模型不完善： CBO的成本模型只是一个近似模型，它无法精确计算每个操作的成本。 例如，对于某些特定的硬件环境或数据分布，CBO的成本估算可能不准确。

• 隐藏的成本： CBO通常只考虑CPU、IO、内存等直接成本，而忽略了一些隐藏的成本，例如网络延迟、锁竞争等。

• 动态数据： 对于数据频繁变化的表，CBO的统计信息很快就会过时。

八、总结

CBO是MySQL查询优化器的核心组件，它通过“算账”的方式选择最佳的执行计划，提高查询性能。统计信息是CBO的“情报”，准确的统计信息是CBO做出正确决策的关键。通过了解CBO的原理和统计信息的作用，我们可以更好地优化SQL语句，提高数据库性能。

记住，CBO不是万能的，需要我们结合实际情况，不断学习和实践，才能真正掌握数据库优化的精髓。

好了，今天的讲座就到这里，希望大家有所收获！下次有机会再和大家分享更多数据库的“内幕”。 散会！