带JOIN的MySQL不使用索引
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都使用索引。 为什么是这样以及如何处理它？
我不知道你在以前和当前的安装中看到的不同，但服务器的行为是有道理的。
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字段
base = 314 with base VARCHAR...是一个性能问题。 要么将引号放在314左右，要么以某种整数类型为base 。
你似乎不需要LEFT 。 如果没有，那么做一个简单的JOIN以便优化器可以自由地开始INDEX(base) ，然后丢失和需要。
至于5.5和5.6和5.7之间的差异，有一些优化的变化; 你可能遇到了回归。 但是我不想在查询和索引得到改进之前追逐它。
链接地址: http://www.djcxy.com/p/25087.html