mysql join on 索引_MySQL的JOIN不使用索引

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不使用索引,而是读取整个表.

没有WHERE语句：

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