mysql - 在 MySQL 中对内连接表使用索引

Question

我有包含 2 亿条记录的表 Foo 和包含 1000 条记录的表 Bar，它们是多对一连接的。 Foo.someTime 和 Bar.someField 列有索引。同样在 Bar 中，900 条记录的 someField 为 1，100 条记录的 someField 为 2。

(1) 这个查询立即执行:

mysql> select * from Foo f inner join Bar b on f.table_id = b.table_id where f.someTime     between '2008-08-14' and '2018-08-14' and b.someField = 1 limit 20;
...
20 rows in set (0.00 sec)

(2) 这个需要永远(唯一的变化是 b.someField = 2):

mysql> select * from Foo f inner join Bar b on f.table_id = b.table_id where f.someTime     between '2008-08-14' and '2018-08-14' and b.someField = 2 limit 20;

(3) 但是如果我在 someTime 上退出 where 子句，它也会立即执行:

mysql> select * from Foo f inner join Bar b on f.table_id = b.table_id where b.someField = 2 limit 20;
...
20 rows in set (0.00 sec)

(4) 我还可以通过强制使用索引来加快速度:

mysql> select * from Foo f inner join Bar b force index(someField) on f.table_id = b.table_id where f.someTime     between '2008-08-14' and '2018-08-14' and b.someField = 2 limit 20;
...
20 rows in set (0.00 sec)

这是对查询 (2) 的解释(需要永远)

+----+-------------+-------+--------+-------------------------------+-----------+---------+--------------------------+----------+-------------+
| id | select_type | table | type   | possible_keys                 | key       | key_len | ref                      | rows     | Extra       |
+----+-------------+-------+--------+-------------------------------+-----------+---------+--------------------------+----------+-------------+
|  1 | SIMPLE      | g     | range  | bar_id,bar_id_2,someTime      | someTime  | 4       | NULL                     | 95022220 | Using where |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY,someField,bar_id      | PRIMARY   | 4       | db.f.bar_id              |        1 | Using where |
+----+-------------+-------+--------+-------------------------------+-----------+---------+--------------------------+----------+-------------+

这是对(4)的解释(有力指数)

+----+-------------+-------+------+-------------------------------+-----------+---------+--------------------------+----------+-------------+
| id | select_type | table | type | possible_keys                 | key       | key_len | ref                      | rows     | Extra       |
+----+-------------+-------+------+-------------------------------+-----------+---------+--------------------------+----------+-------------+
|  1 | SIMPLE      | t     | ref  | someField                     | someField | 1       |   const                  |       92 |             |
|  1 | SIMPLE      | g     | ref  | bar_id,bar_id_2,someTime      | bar_id    | 4       | db.f.foo_id              | 10558024 | Using where |
+----+-------------+-------+------+-------------------------------+-----------+---------+--------------------------+----------+-------------+

那么问题是如何教会MySQL使用正确的索引呢？查询由 ORM 生成，并不仅限于这两个字段。而且最好避免过多地更改查询(尽管我不确定内部联接是否适合此处)。

更新:

mysql> create index index_name on Foo (bar_id, someTime);

之后查询 (2) 在 0.00 秒后执行。

Answer 1

如果您为 foo(table_id, sometime) 创建复合索引，应该会有很大帮助。这是因为服务器将能够首先通过 table_id 缩小结果集，然后通过 sometime 缩小结果集。

请注意，当使用 LIMIT 时，如果许多行符合您的 WHERE 约束，服务器不保证将获取哪些行。从技术上讲，每次执行都会给您略有不同的结果。如果要避免歧义，在使用 LIMIT 时应始终使用 ORDER BY。但是，这也意味着您在创建适当的索引时应该更加小心。