MySQL 5.7.18 版有问题。早期版本的 MySQL 的行为符合预期。

这里有两个表。表 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”中选择最后一个事件:

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 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 在这两种情况下都使用索引。为什么会这样以及如何处理？

最佳答案

我不知道您在以前和当前的安装中看到的差异，但服务器行为是有道理的。

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 排序之后。该索引可用于排序。但是让我们看看第二个查询。

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

我发现您的设置有几个问题，第一个是没有在 base 上建立索引，如前所述。但是为什么是base varchar呢？您似乎在其中存储整数。

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 只能对一张表使用一个索引进行查询。使用一个或两个索引会好得多。可能是多列索引。

我认为您理想的索引将同时包含 create_time 和 event 字段

关于带有 JOIN 的 MySQL 不使用索引，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/43839078/