MySQL空间索引无法工作。

Question

我有两个数据库表，分别包含链接事件和事件时间：

mysql> show create table link_events 
    *************************** 1. row ***************************
           Table: link_events
    Create Table: CREATE TABLE `link_events` (
      `device_name` varchar(32) DEFAULT NULL,
      `link_name` varchar(32) DEFAULT NULL,
      `event_type` varchar(32) DEFAULT NULL,
      `link_event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`link_event_id`),
      UNIQUE KEY `link_events` (`device_name`,`link_name`,`event_type`)
    ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

mysql> show create table event_times
*************************** 1. row ***************************
       Table: event_times
Create Table: CREATE TABLE `event_times` (
  `down_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `up_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `span` geometry NOT NULL,
  `link_event_id` int(10) unsigned NOT NULL,
  `event_time_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`event_time_id`),
  SPATIAL KEY `span` (`span`),
  KEY `fk_link_events` (`link_event_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

INSERT INTO link_events(device_name,link_name, event_type)
    VALUES('d1','l1','e1') on duplicate key update link_event_id = link_event_id;

INSERT INTO link_events(device_name,link_name, event_type)
    VALUES('d1','l1','e2') on duplicate key update link_event_id = link_event_id;

INSERT INTO event_times(down_time, up_time, span, link_event_id)
VALUES('2015-01-01 00:00:04', '2015-01-01 00:00:08', 
    linestring(point(UNIX_TIMESTAMP(STR_TO_DATE('2015-01-01 00:00:08','%Y-%m-%d %H:%i:%s')),0),point(UNIX_TIMESTAMP(STR_TO_DATE('2015-01-01 00:00:04','%Y-%m-%d %H:%i:%s')),0)),
    (SELECT link_event_id FROM link_events WHERE device_name = 'd1' AND link_name = 'l1' AND event_type = 'e1'));

INSERT INTO event_times(down_time, up_time, span, link_event_id)
VALUES('2015-01-01 00:00:07', '2015-01-01 00:00:09', 
    linestring(point(UNIX_TIMESTAMP(STR_TO_DATE('2015-01-01 00:00:09','%Y-%m-%d %H:%i:%s')),0),point(UNIX_TIMESTAMP(STR_TO_DATE('2015-01-01 00:00:07','%Y-%m-%d %H:%i:%s')),0)),
    (SELECT link_event_id FROM link_events WHERE device_name = 'd1' AND link_name = 'l1' AND event_type = 'e2'));

我想查找在时间上重叠的link_events，并使用以下查询：

EXPLAIN

SELECT 

CONCAT('Link1','-', 'Link2') overlaps,
GREATEST(a.down_time,b.down_time) AS downtime, 
LEAST(a.up_time,b.up_time) AS uptime,
TIME_TO_SEC(TIMEDIFF( LEAST(a.up_time,b.up_time),
    GREATEST(a.down_time,b.down_time))) AS duration

FROM event_times a 
JOIN event_times b 

ON  Intersects (a.span, b.span) 

WHERE a.link_event_id = (SELECT link_event_id FROM link_events WHERE device_name = 'd1' AND link_name = 'l1' AND event_type = 'e1')
AND   b.link_event_id = (SELECT link_event_id FROM link_events WHERE device_name = 'd1' AND link_name = 'l1' AND event_type = 'e2');

+----+-------------+-------------+-------+---------------------+----------------+---------+-------------------+------+-------------+
| id | select_type | table       | type  | possible_keys       | key            | key_len | ref               | rows | Extra       |
+----+-------------+-------------+-------+---------------------+----------------+---------+-------------------+------+-------------+
|  1 | PRIMARY     | a           | ref   | span,fk_link_events | fk_link_events | 4       | const             |    1 | Using where |
|  1 | PRIMARY     | b           | ref   | span,fk_link_events | fk_link_events | 4       | const             |    1 | Using where |
|  3 | SUBQUERY    | link_events | const | link_events         | link_events    | 297     | const,const,const |    1 | NULL        |
|  2 | SUBQUERY    | link_events | const | link_events         | link_events    | 297     | const,const,const |    1 | NULL        |
+----+-------------+-------------+-------+---------------------+----------------+---------+-------------------+------+-------------+
4 rows in set (0.00 sec)

如何将span列变为关键列以优化查询时间？

link_events包含2k行，而event_times有900k行。

非常感谢！

- user2149346

Answer 1

为了强制在查询中使用索引，请在表名后指定它：

SELECT 

CONCAT('Link1','-', 'Link2') overlaps,
GREATEST(a.down_time,b.down_time) AS downtime, 
LEAST(a.up_time,b.up_time) AS uptime,
TIME_TO_SEC(TIMEDIFF( LEAST(a.up_time,b.up_time),
    GREATEST(a.down_time,b.down_time))) AS duration

FROM event_times a USE KEY (span)
JOIN event_times b USE KEY (span)

ON  Intersects (a.span, b.span) 

WHERE a.link_event_id = (SELECT link_event_id FROM link_events WHERE device_name = 'd1' AND link_name = 'l1' AND event_type = 'e1')
AND   b.link_event_id = (SELECT link_event_id FROM link_events WHERE device_name = 'd1' AND link_name = 'l1' AND event_type = 'e2');

参考：8.9.4 索引提示

我要说的是，我从来没有怀疑过默认索引，所以实际上没有使用过这个功能！

- miken32