当optimizer_prune_level = 1时，究竟会发生什么？

Question

这个文档链接声明optimizer_prune_level

控制在查询优化期间应用的启发式方法，从优化器搜索空间中剪除不太有希望的部分计划。

有谁能帮助了解什么样的启发式原则是适用的吗？

考虑一下这个简单的模式：

CREATE TABLE `college` (
  `colg_id` int(11) NOT NULL AUTO_INCREMENT,
  `colg_name` varchar(20) DEFAULT NULL,
  `colg_address` varchar(20) DEFAULT NULL,
  `avg_fees` int(11) DEFAULT NULL,
  PRIMARY KEY (`colg_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4

CREATE TABLE `department` (
  `dept_id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(20) DEFAULT NULL,
  `dept_address` varchar(20) DEFAULT NULL,
  `dept_hod` varchar(20) DEFAULT NULL,
  `colg_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`dept_id`),
  KEY `colg_id_fk` (`colg_id`),
  KEY `dept_name` (`dept_name`),
  CONSTRAINT `department_ibfk_1` FOREIGN KEY (`colg_id`) REFERENCES `college` (`colg_id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4

CREATE TABLE `student` (
  `stud_id` int(11) NOT NULL AUTO_INCREMENT,
  `stud_name` varchar(20) DEFAULT NULL,
  `stud_address` varchar(20) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  PRIMARY KEY (`stud_id`),
  KEY `dept_id_fk` (`dept_id`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1201 DEFAULT CHARSET=utf8mb4

Relation college包含10行、department 100行和student 1100行。

对于以下查询

select * from college c, department d, student s where c.colg_id = d.colg_id and d.dept_id = s.dept_id;

附加了-the优化器跟踪：trace.json。

从跟踪(使用我的感知能力)出发，优化器甚至尝试计算了加入college和student的成本。一个跨产品的加入订单计划也被考虑在其他方面。那么跨产品回避不是启发式的一部分吗？至少加入的次数少了？那么启发式会随着联接的数量而改变吗？如果是的话，怎么做，还有什么例子？

Answer 1

EAV (实体属性值)模式可以从..level=1中受益。这是因为混乱的WHERE子句有许多本质上相同的子句。

WHERE a1.e = 123 AND a1.attr = 'color' AND a1.val = 'red'
  AND a2.e = 123 AND a2.attr = 'shape' AND a2.val = 'round'
  AND a3.e = 123 AND a3.attr = 'size'  AND a3.val = 'big'
  AND a4.e = 123 AND a4.attr = 'style' AND a4.val = 'xyz'

通常情况下，优化器会尝试大量不同的订单来查看表。实际上，它们同样有效，将参数设置为1可以避免优化器浪费精力。

在您的示例中，WHERE子句应该在ON子句中，因为它们描述了表之间的关系。并且您的示例没有用于筛选的WHERE子句。

如果不进行过滤，可以从任何一个表开始，由3个具有足够索引的表组成JOIN。对于3个表，将有6 (3 )个阶乘顺序。10张桌子要数以百万计。但是，再一次，优化者不应该浪费时间尝试所有的组合。

如果您启动一个简单的WHERE进行过滤，优化器将很快被吸引到该表中，因为它是最好的开始，并且“级别”将不会发挥那么大的作用。