我有两个包含链接事件和事件时间的数据库表:

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 行。 非常感谢!

最佳答案

要在查询中强制使用索引，请使用表名指定它:

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 Index Hints

我会说我从来没有机会去猜测默认索引，所以没有实际使用它的经验!

关于MySQL 空间键不工作，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/34133918/