代码之家 › 专栏 › 技术社区 › guigoz

mysql没有按预期使用索引

subquery indexing mysql

-1

guigoz · 技术社区 · 8 年前

EXPLAIN 关于这个问题

select v.type,sum(c.rank)
from
  (select distinct power,color,type from vehicle) v
  join configuration c using (power,color)
group by v.type

给予

+----+-------------+---------------+------------+-------+---------------+-------------+---------+-----------------------------------------+---------+----------+---------------------------------+
| id | select_type |     table     | partitions | type  | possible_keys |     key     | key_len |                   ref                   |  rows   | filtered |              Extra              |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+-----------------------------------------+---------+----------+---------------------------------+
|  1 | PRIMARY     | configuration | NULL       | ALL   | veh           | NULL        | NULL    | NULL                                    |   76658 |   100.00 | Using temporary; Using filesort |
|  1 | PRIMARY     | <derived2>    | NULL       | ref   | <auto_key0>   | <auto_key0> | 6       | configuration.power,configuration.color |      65 |   100.00 | NULL                            |
|  2 | DERIVED     | vehicle       | NULL       | index | cov           | cov         | 20      | NULL                                    | 5058658 |   100.00 | Using index                     |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+-----------------------------------------+---------+----------+---------------------------------+

即使我设置了 force index

如果我使用表而不是子查询

create table tmp select distinct power,color,type from vehicle

然后 Explain 关于“相同”查询

select v.type,sum(c.rank)
from
  tmp v 
  join configuration c using (power,color)
group by type

变成

+----+-------------+---------------+------------+------+---------------+------+---------+---------------------+---------+----------+---------------------------------+
| id | select_type |     table     | partitions | type | possible_keys | key  | key_len |         ref         |  rows   | filtered |              Extra              |
+----+-------------+---------------+------------+------+---------------+------+---------+---------------------+---------+----------+---------------------------------+
|  1 | SIMPLE      | tmp           | NULL       | ALL  | NULL          | NULL | NULL    | NULL                | 1016144 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | configuration | NULL       | ref  | veh           | veh  | 6       | tmp.power,tmp.color |       2 |   100.00 | NULL                            |
+----+-------------+---------------+------------+------+---------------+------+---------+---------------------+---------+----------+---------------------------------+

这是4倍的速度

如何避免使用硬桌子?

1 回复 | 直到 8 年前

Vatev 8 年前

在第一种情况下,优化器认为最好使用派生表中的自动生成键以另一种方式执行。

在第二种情况下,temp表中没有键,因此最好的计划是先使用tmp。

您应该可以使用 STRAIGHT_JOIN 而不是 JOIN .