使用JOIN的MySQL不使用索引 [英] MySQL with JOIN not using index

问题描述

MySQL版本5.7.18的问题。早期版本的MySQL表现得如此。

Problem with MySQL version 5.7.18. Earlier versions of MySQL behaves as supposed to.

这是两个表。表1：

CREATE TABLE `test_events` (
  `id` int(11) NOT NULL,
  `event` int(11) DEFAULT '0',
  `manager` int(11) DEFAULT '0',
  `base_id` int(11) DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `client` int(11) DEFAULT '0',
  `event_time` datetime DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `test_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `client` (`client`),
  ADD KEY `event_time` (`event_time`),
  ADD KEY `manager` (`manager`),
  ADD KEY `base_id` (`base_id`),
  ADD KEY `create_time` (`create_time`);

第二个表：

CREATE TABLE `test_event_types` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `base` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `test_event_types`
  ADD PRIMARY KEY (`id`);

让我们尝试从基数314中选择最后一个事件：

Let's try to select last event from base "314":

EXPLAIN  SELECT  `test_events`.`create_time`
    FROM  `test_events`
    LEFT JOIN  `test_event_types`
           ON ( `test_events`.`event` = `test_event_types`.`id` )
    WHERE  base = 314
    ORDER BY  `test_events`.`create_time` DESC
    LIMIT  1;

+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test_events      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 434928 |   100.00 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | test_event_types | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |     44 |     2.27 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

MySQL没有使用索引并读取整个表。
没有WHERE语句：

MySQL is not using index and reads the whole table. Without WHERE statement:

EXPLAIN  SELECT  `test_events`.`create_time`
    FROM  `test_events`
    LEFT JOIN  `test_event_types`
          ON ( `test_events`.`event` = `test_event_types`.`id` )
    ORDER BY  `test_events`.`create_time` DESC
    LIMIT  1;

+----+-------------+------------------+------------+--------+---------------+-------------+---------+-----------------------+------+----------+-------------+
| id | select_type | table            | partitions | type   | possible_keys | key         | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+------------------+------------+--------+---------------+-------------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | test_events      | NULL       | index  | NULL          | create_time | 4       | NULL                  |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | test_event_types | NULL       | eq_ref | PRIMARY       | PRIMARY     | 4       | m16.test_events.event |    1 |   100.00 | Using index |
+----+-------------+------------------+------------+--------+---------------+-------------+---------+-----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

现在它使用索引。

MySQL 5.5.55在两种情况下都使用索引。为什么会如此以及怎么做呢？

MySQL 5.5.55 uses index in both cases. Why is it so and what to do with it?

推荐答案

我不知道你在以前和当前安装但服务器行为是有意义的。

I don't know the difference you are seeing in your previous and current installations but the servers behaviour makes sense.

SELECT  test_events.create_time  FROM  test_events  LEFT JOIN  test_event_types ON (  test_events.event =  test_event_types.id )  ORDER BY  test_events.create_time DESC LIMIT 1; 

在此查询中，您没有where子句，但只提取一行。这是在按 create_time 排序之后，恰好有一个索引。该索引可用于排序。但是让我们看看第二个查询。

In this query you do not have a where clause but you are fetching one row only. And that's after sorting by create_time which happens to have an index. And that index can be used for sorting. But let's see the second query.

SELECT  test_events.create_time  FROM  test_events  LEFT JOIN  test_event_types ON (  test_events.event =  test_event_types.id ) WHERE base = 314 ORDER BY  test_events.create_time DESC LIMIT 1

你没有索引基本栏目。因此，没有索引可以用于此。要查找相关记录，mysql必须进行表扫描。确定了相关的行后，需要对它们进行排序。但在这种情况下，查询计划器已经决定在 create_time

You don't have an index on the base column. So no index can be used on that. To find the relevent records mysql has to do a table scan. Having identified the relevent rows, they need to be sorted. But in this case the query planner has decided that it's just not worth it to use the index on create_time

我看到你的设置有几个问题，第一个没有，并且已经提到了 base 的索引。但为什么是基础varchar？您似乎在其中存储整数。

I see several problems with your setup, the first being not having and index on base as already mentioned. But why is base varchar? You appear to be storing integers in it.

ALTER TABLE test_events
  ADD PRIMARY KEY (id),
  ADD KEY client (client),
  ADD KEY event_time (event_time),
  ADD KEY manager (manager),
  ADD KEY base_id (base_id),
  ADD KEY create_time (create_time);

制作这样的多个索引在mysql中没有多大意义。那是因为mysql每个表只能使用一个索引进行查询。使用一个或两个索引会好得多。可能是多列索引。

And making multiple indexes like this doesn't make much sense in mysql. That's because mysql can use only one index per table for queries. You would be far better off with one or two indexes. Possibly multi column indexes.

我认为您理想的索引包含create_time和事件字段

I think your ideal index would contain both create_time and event fields

这篇关于使用JOIN的MySQL不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！