MySQL会加入MUL密钥来影响性能？
我正在使用一个MySQL查询，在四个表上执行三个表连接。 其中两个连接位于一对MUL键上。 第三次连接位于一对主键上。 查询需要一整分钟来抓取100行。
我相信这两对MUL键是独一无二的; 所以我想知道，如果这些键被索引为唯一的，或者做成外键，我会注意到一个显着的性能增强？
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 | |
查询：
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行。
使用显式连接
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', ...);