mysql - 强制 mysql 查询优化器在 JOIN 之前使用 WHERE 条件

Question

我的查询连接两个表，并应用如下条件:

select b.address FROM  
address_partitions as c 
JOIN hop_addresses as b 
ON b.address=c.address 
AND c.npartition = 19;

+-----------------------------------+
| address                           |
+-----------------------------------+
| 111112UgMrsKETroaurM8YGf2R7SkN2wz |
+-----------------------------------+
1 row in set (10.08 sec)

mysql优化器总是先执行JOIN，然后执行条件:

| id   | select_type | table | type  | possible_keys      | key        | key_len | ref   | rows     | Extra                                                        |
|    1 | SIMPLE      | c     | ref   | PRIMARY,npartition | npartition | 4       | const |        1 | Using index                                                  |
|    1 | SIMPLE      | b     | index | NULL               | PRIMARY    | 38      | NULL  | 25249276 | Using where; Using index; Using join buffer (flat, BNL join) |

查询需要10秒

如果我手动分两步执行查询，首先是条件:

1. 条件优先

从address_partitions中选择地址作为c WHERE c.npartition = 19;

+-----------------------------------+
| address                           |
+-----------------------------------+
| 111112UgMrsKETroaurM8YGf2R7SkN2wz |
+-----------------------------------+
1 row in set (0.00 sec)

然后加入

从hop_addresses中选择*，其中地址=“111112UgMrsKETroaurM8YGf2R7SkN2wz”；

+-----------------------------------+---------+-----------+--------------+
| address                           | no_hops | no_inputs | no_addresses |
+-----------------------------------+---------+-----------+--------------+
| 111112UgMrsKETroaurM8YGf2R7SkN2wz |       2 |        35 |            5 |
+-----------------------------------+---------+-----------+--------------+
1 row in set (0.00 sec)

查询需要 0.00 秒

如何强制 mysql 查询优化器在 JOIN 之前执行 WHERE 条件？

address_partitions | CREATE TABLE `address_partitions` (
  `address` varchar(36) NOT NULL,
  `npartition` int(10) unsigned NOT NULL,
  PRIMARY KEY (`address`),
  KEY `npartition` (`npartition`)
) ENGINE=InnoDB

hop_addresses | CREATE TABLE `hop_addresses` (
  `address` varchar(36) NOT NULL,
  `no_hops` int(10) unsigned NOT NULL,
  `no_inputs` int(10) unsigned DEFAULT NULL,
  `no_addresses` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`address`)
) ENGINE=InnoDB

Answer 1

您可以在address_partitions表上尝试使用条件子查询:

select b.address FROM (SELECT a.address FROM 
address_partitions as a 
WHERE a.npartition = 19) c
JOIN hop_addresses as b 
ON b.address=c.address;