MySQL 加入 MUL 键会影响性能吗？

Question

我正在使用一个 MySQL 查询，该查询对四个表执行三个表连接。其中两个连接位于一对 MUL key 上。第三个连接是在一对主键上。查询需要一整分钟才能获取 100 行。

我相信两对 MUL key 都是唯一的；所以我想知道，如果这些键被索引为唯一键，或者被制成外键，我会注意到显着的性能提升吗？

MUL 键上的连接可能是这里的罪魁祸首吗？

编辑

好的，这是架构。我用字母代替了实际的表/列名称。

mysql> describe table A;+-------------------+------------------+------+-----+---------+----------------+| Field             | Type             | Null | Key | Default | Extra          |+-------------------+------------------+------+-----+---------+----------------+| pkey              | int(10) unsigned | NO   | PRI | NULL    | auto_increment || mkey              | int(10) unsigned | NO   | MUL | NULL    |                || a                 | int(10) unsigned | NO   |     | NULL    |                || b                 | int(10) unsigned | NO   |     | NULL    |                |

mysql> describe table B;+-------------------+------------------+------+-----+---------+----------------+| Field             | Type             | Null | Key | Default | Extra          |+-------------------+------------------+------+-----+---------+----------------+| pkey              | int(10) unsigned | NO   | PRI | NULL    | auto_increment || mkey1             | int(10) unsigned | NO   | MUL | NULL    |                || mkey2             | int(10) unsigned | NO   | MUL | NULL    |                || a                 | int(10) unsigned | NO   |     | NULL    |                || b                 | int(10) unsigned | NO   |     | NULL    |                || c                 | int(10) unsigned | NO   |     | NULL    |                |

mysql> describe table C;+---------------+------------------+------+-----+---------+-------+| Field         | Type             | Null | Key | Default | Extra |+---------------+------------------+------+-----+---------+-------+| pkey          | int(10) unsigned | NO   | PRI | NULL    |       || mkey          | varchar(128)     | NO   | MUL | NULL    |       |

mysql> describe table D;+---------------+------------------+------+-----+---------+-------+| Field         | Type             | Null | Key | Default | Extra |+---------------+------------------+------+-----+---------+-------+| pkey          | int(10) unsigned | NO   | PRI | NULL    |       || mkey          | varchar(128)     | NO   | MUL | NULL    |       |

Query:

select
A.a, A.b, B.c, A.mkey, C.pkey, D.mkey
from
A, B, C, D
where
A.pkey=C.pkey and
A.mkey=B.mkey1 and
B.mkey2=D.pkey and
B.a <= A.a and
B.b >= A.b
D.mkey in ('str1', 'str2', ...);

返回 77 行。

Answer 1

使用显式连接

select
A.a, A.b, B.c, A.mkey, C.pkey, D.mkey
from
A INNER JOIN C ON (A.pkey = C.pkey)
INNER JOIN B ON (A.mkey = B.mkey1)
INNER JOIN D ON (B.mkey2 = D.pkey)
where
B.a <= A.a and
B.b >= A.b
D.mkey in ('str1', 'str2', ...);