mysql - 优化按时间戳排序的大表的 SQL 查询

Question

我们有一个大表，其表结构如下:

CREATE TABLE `location_data` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `device_sn` char(30) NOT NULL,
  `data` char(20) NOT NULL,
  `gps_date` datetime NOT NULL,
  `lat` double(30,10) DEFAULT NULL,
  `lng` double(30,10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dt` (`dt`),
  KEY `data` (`data`),
  KEY `device_sn` (`device_sn`,`data`,`dt`),
  KEY `device_sn_2` (`device_sn`,`dt`)
) ENGINE=MyISAM AUTO_INCREMENT=721453698 DEFAULT CHARSET=latin1

我们多次执行如下查询:

SELECT * FROM location_data WHERE device_sn = 'XXX' AND data = 'location' ORDER BY dt DESC LIMIT 1;

或者

SELECT * FROM location_data WHERE device_sn = 'XXX' AND data = 'location' AND dt >= '2014-01-01 00:00:00 ' AND dt <= '2014-01-01 23:00:00' ORDER BY dt DESC;

我们一直在通过几种方式对此进行优化:

1. 通过添加索引并在 device_sn 上使用 FORCE INDEX。
2. 根据日期将表分成多个表(例如 location_data_20140101)，并预先检查是否存在基于特定日期的数据，然后我们将单独提取该特定表。该表由 cron 每天创建一次，并且 location_data 中该特定日期的数据将被删除。

表location_data为高写和低读。

但是，有几次，查询运行得非常慢。我想知道是否还有其他方法/方式/重构数据，使我们能够根据给定的device_sn以顺序日期方式读取数据。

非常欢迎任何提示。

解释第一个查询语句:

+----+-------------+--------------+------+----------------------------+-----------+---------+-------------+------+-------------+
| id | select_type | table        | type | possible_keys              | key       | key_len | ref         | rows | Extra       |
+----+-------------+--------------+------+----------------------------+-----------+---------+-------------+------+-------------+
|  1 | SIMPLE      | location_dat | ref  | data,device_sn,device_sn_2 | device_sn | 50      | const,const |    1 | Using where |
+----+-------------+--------------+------+----------------------------+-----------+---------+-------------+------+-------------+

解释第二个查询语句:

+----+-------------+--------------+-------+-------------------------------+------+---------+------+------+-------------+
| id | select_type | table        | type  | possible_keys                 | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+-------+-------------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_udp_new | range | dt,data,device_sn,device_sn_2 | dt   | 4       | NULL |    1 | Using where |
+----+-------------+--------------+-------+-------------------------------+------+---------+------+------+-------------+

Answer 1

索引device_sn (device_sn,data,dt) 很好。 MySQL 应该使用它而不需要执行任何 FORCE INDEX。您可以通过运行“explain select ...”来验证它

但是，您的表是MyISAM，它仅支持表级锁。如果表写入量很大，则速度可能会很慢。我建议将其转换为 InnoDB。