mysql - Rows_sent : 851 Rows_examined: 1649439, 查询非常慢

Question

来自 mysql-slow-log 的日志:

查询时间:11.632750 锁定时间:1.317329 已发送行数:851 已检查行数:1649439

select record.name, dept.name, school.name FROM record

inner join record as r2 ON
record.number=r2.number AND record.year=r2.year
AND r2.dept_id=12345 AND r2.type=1 AND r2.year=2015

LEFT JOIN school ON record.school_id=school.id
LEFT JOIN dept ON record.dept_id=dept.id

表record中有大约80万行，但是查询为什么检查超过100万行？

我已经为表dept和表school设置了索引，但我不确定如何为内连接部分创建索引。我读了上一个问题( Rows_sent: 12 Rows_examined: 549024 - how to optimize mySQL query? )，但仍然无法找到我的极慢查询的解决方案。

编辑(为什么内部联接):

我想选择 dept_id=12345 的同一个人的所有行。结果将是黄色和橙色行，如示例图片所示。这就是我使用内连接的原因。

Answer 1

为什么是 1.6M，而不是 0.8M？看来是

1. 读取一张表的全部内容 (0.8M)，
2. 对于每一行，进入另一个表(另外 0.8M)。

对于此统计，两个表是同一个表(“自连接”)是无关紧要的。

如何优化？如果您提供了SHOW CREATE TABLE record，那么回答会更容易，但这是一个猜测。

添加两个索引:

INDEX(dept_id, year, type) -- the fields can be in any order
INDEX(number, year) -- the fields can be in either order

第一个可以非常有效地让您进入r2。第二个使得 JOIN 非常高效。

select record.name, dept.name, school.name FROM record 存在问题 - 您尚未加入表 dept 或 学校。

Building an INDEX from a SELECT