当在具有2个值(使用IN或OR结构)的PRIMARY键上进行INNER JOIN时,我在EXPLAIN SELECT中获得“检查每个记录的范围(索引映射:0x1)”
这是查询:
SELECT *
FROM message AS m
INNER JOIN user AS u
ON u.id = m.sender_id OR u.id = m.receiver_id
在做解释时,它给了我:
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------------------------+
| 1 | SIMPLE | u | ALL | PRIMARY | null | null | null | 75000 | Range checked for each record (index map: 0x1)|
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------------------------+
它不可能……
如果我尝试这个,我会得到相同的结果:
SELECT *
FROM message AS m
INNER JOIN user AS u
ON u.id IN(m.sender_id, m.receiver_id)
但是,如果我这样做,它工作正常,我只解析了1行:
SELECT *
FROM message AS m
INNER JOIN user AS u
ON u.id = m.sender_id
这怎么可能?我正在加入具有相同类型值的主键. (实际的查询是“有点”更复杂,但没有什么花哨,2个内连接,最后一个左连接)
它应该是2行,周期.
感谢您的任何意见(进行了一些研究,但除了“请添加索引”之外没有找到任何有价值的东西,这显然不适用于此处)
编辑:是的,我尝试了USE INDEX声明,但仍然没有运气
编辑:这是一个非常简单的架构来重现MySQL的这种奇怪的行为:
CREATE TABLE test_user (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
PRIMARY KEY (id)
);
CREATE TABLE test_message (
id INT NOT NULL AUTO_INCREMENT,
sender_id INT NOT NULL,
receiver_id INT NOT NULL,
PRIMARY KEY (id),
INDEX idx_sender (sender_id),
INDEX idx_receiver (receiver_id)
);
EXPLAIN SELECT *
FROM test_message AS m
INNER JOIN test_user AS u
ON u.id = m.sender_id OR u.id = m.receiver_id;