实验:从 JOIN SQL 到执行计划

在隔离的 v4.2.5 实例运行。下面的小数据只验证结果和计划入口,不证明分布式执行或性能收益。

DROP DATABASE IF EXISTS kernel_plan_518;
CREATE DATABASE kernel_plan_518;
USE kernel_plan_518;
CREATE TABLE customers(id INT PRIMARY KEY, region VARCHAR(10));
CREATE TABLE orders(id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2));
CREATE INDEX idx_customer ON orders(customer_id);
INSERT INTO customers VALUES(1,'east'),(2,'west');
INSERT INTO orders VALUES(10,1,10.00),(11,1,20.00),(12,2,5.00);
EXPLAIN SELECT id FROM orders WHERE customer_id=1;
EXPLAIN SELECT id FROM orders FORCE INDEX(idx_customer) WHERE customer_id=1;
SELECT c.region,SUM(o.amount) AS total FROM customers c JOIN orders o ON c.id=o.customer_id GROUP BY c.region ORDER BY c.region;
DROP DATABASE kernel_plan_518;

预期汇总为 east=30.00、west=5.00。保存两个 EXPLAIN,比较 Table Scan/索引相关节点、Filter、连接和输出列。若小表普通计划不选索引,这是合法成本选择;FORCE 的效果须从计划确认。

扩展实验时按固定脚本放大订单表,并构造均匀/偏斜数据。每次保持 JOIN 结果一致,记录统计更新时间、估计与实际行数、扫描量、运行统计。再用 EXPLAIN ANALYZE 对实际查询测量,比较冷/热缓存;不要只按耗时推断原因。

清理已包含在脚本中。机制见SQL 规划。