从大型表中获得8-10条记录的MySQL查询优化

Question

我们有一个小型的移动应用程序，它不断地发送团队在实地工作的位置。我们有基于网络的管理面板，以查看每个团队在外地的最后位置，有8-10个团队。

现在，保存位置的表变得更大(大约800 K记录)，从数据库获取信息需要大约10秒。

我们不能简单地删除旧的记录，因为我们想要保留的历史小组访问在不同的地点。

在视图中，我们在管理面板中使用以下SQL查询

SELECT w.ID, w.DaynTime, team_Desc, co_Nome, w.team_Lat, w.team_Long
FROM ( SELECT MAX(ID) AS maxID FROM VlocationTab GROUP BY UserID) AS aux
INNER JOIN VlocationTab AS w ON w.ID = aux.maxID;

以下是create语句

CREATE TABLE `TableName` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `UserID` varchar(15) NOT NULL,
 `Lat` double(8,6) NOT NULL,
 `Long` double(8,6) NOT NULL,
 `DayTime` datetime NOT NULL,
 `User` varchar(15) DEFAULT NULL,
 `Date` datetime DEFAULT NULL,
`AUser` varchar(15) DEFAULT NULL,
`ADate` datetime DEFAULT NULL,
 PRIMARY KEY (`ID`),
KEY `DataTime` (`DayTime`),
KEY `Coordenates` (`Lat`,`Long`)
) ENGINE=MyISAM AUTO_INCREMENT=1040384 DEFAULT CHARSET=utf8;

是否需要优化此查询以尽量减少执行时间？

Answer 1

我用1000000行(1000个用户和每个用户1000行)填充一个测试表。

以下是初步计划：

mysql> explain SELECT w.ID, w.DayTime, User, Lat, `Long` FROM ( SELECT MAX(ID) AS maxID FROM TableName GROUP BY UserID) AS aux INNER JOIN TableName AS w ON w.ID = aux.maxID;
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------+---------+----------+---------------------------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref       | rows    | filtered | Extra                           |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------+---------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL      | 1000000 |   100.00 | Using where                     |
|  1 | PRIMARY     | w          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | aux.maxID |       1 |   100.00 | NULL                            |
|  2 | DERIVED     | TableName  | NULL       | ALL    | NULL          | NULL    | NULL    | NULL      | 1000000 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------+---------+----------+---------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> SELECT count(*) FROM ( SELECT MAX(ID) AS maxID FROM TableName GROUP BY UserID) AS aux INNER JOIN TableName AS w ON w.ID = aux.maxID;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (1.07 sec)

您的子查询

从maxID组中选择MAX(ID)作为TableName组中的UserID不能使用任何索引，因此您可以执行完整扫描来搜索每个用户的max(id)，然后使用主键进行连接。

我添加了一个包含用户和id两列的索引。当索引被指定时，它允许直接获取每个用户的max(id)：

mysql> alter table TableName add index UserID_ID(UserID,ID);
Query OK, 1000000 rows affected (10.60 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

新计划和新时间：

mysql> explain SELECT w.ID, w.DayTime, User, Lat, `Long` FROM ( SELECT MAX(ID) AS maxID FROM TableName GROUP BY UserID) AS aux INNER JOIN TableName AS w ON w.ID = aux.maxID;
+----+-------------+------------+------------+--------+---------------+-----------+---------+-----------+------+----------+--------------------------+
| id | select_type | table      | partitions | type   | possible_keys | key       | key_len | ref       | rows | filtered | Extra                    |
+----+-------------+------------+------------+--------+---------------+-----------+---------+-----------+------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL      | NULL    | NULL      | 1001 |   100.00 | Using where              |
|  1 | PRIMARY     | w          | NULL       | eq_ref | PRIMARY       | PRIMARY   | 4       | aux.maxID |    1 |   100.00 | NULL                     |
|  2 | DERIVED     | TableName  | NULL       | range  | UserID_ID     | UserID_ID | 47      | NULL      | 1001 |   100.00 | Using index for group-by |
+----+-------------+------------+------------+--------+---------------+-----------+---------+-----------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> SELECT count(*) FROM ( SELECT MAX(ID) AS maxID FROM TableName GROUP BY UserID) AS aux INNER JOIN TableName AS w ON w.ID = aux.maxID;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.04 sec)

PS :但是最好的方法是重写您的请求，首先过滤日期，例如，行不超过一天。