mysql不在简单连接上使用索引

Question

我想知道为什么 mysql 没有在表 (visit_url) 的以下查询中使用索引:

> describe select sv.url from visit_url sv, result_query rq where rq.result_id = sv.result_id and rq.user_id = 'fred';
+----+-------------+-------+--------+---------------+---------+---------+------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                    | rows   | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------------+--------+-------------+
|  1 | SIMPLE      | vu    | ALL    | result_id     | NULL    | NULL    | NULL                   | 506553 |             |
|  1 | SIMPLE      | rq    | eq_ref | PRIMARY       | PRIMARY | 32      |           vu.result_id |      1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------------------+--------+-------------+

我的索引是:

mysql> show index in visit_url;
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| visit_url |          1 | result_id |            1 | result_id   | A         |      168851 |     NULL | NULL   |      | BTREE      |         |               |
| visit_url |          1 | url       |            1 | url         | A         |      253276 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+---------------+

和

mysql> show index in result_query;
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| result_query |          0 | PRIMARY   |            1 | result_id   | A         |     2718272 |     NULL | NULL   |      | BTREE      |         |               |
| result_query |          1 | query_idx |            1 | query       | A         |      271827 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

visit_url 有 506k 个条目，result_query 有 200 万个

非常感谢!

Answer 1

尝试使用与 where 子句分开的显式连接来编写查询，如下所示:

select sv.url from visit_url sv 
  join result_query rq on rq.result_id = sv.result_id
  where rq.user_id = 'fred';