首页 文章

使用JOIN的MySQL不使用索引

提问于
浏览 次
3

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

这是两张 table . 表格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在两种情况下都使用索引 . 为什么会这样，怎么做呢？

mysql join optimization indexing

2 回答

  • 2

    我不知道您在之前和当前安装中看到的差异，但服务器行为是有意义的 .

    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

    你'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'只是不值得使用 create_time 上的索引

    我看到你的设置有几个问题，第一个没有和 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字段

    回复于
  • 2

    带有 base VARCHAR... 的 base = 314 是一个性能问题 . 要么在 314 附近加上引号，要么使 base 为某种整数类型 .

    你似乎不需要 LEFT . 如果没有，那么执行一个简单的 JOIN ，以便优化器可以自由地启动 INDEX(base) ，然后将其丢失并需要 .

    至于5.5和5.6以及5.7之间的差异，已经有一些优化变化;你可能遇到过回归 . 但是，在您改进查询和索引之前，我不想追逐它 .

    回复于

相关问题