MySQL用一个连接(性能)释放更新的记录
MySQL用一个连接(性能)释放更新的记录
提问于 2013-01-12 18:06:30
我在MySQL表中查询特定x/y/z坐标所记录的事件(对于游戏插件fyi)。此查询是用右联接编写的，以排除在完全相同坐标下具有较新条目的记录。
不过，这是一场表演之战。
例如，一个查询返回大约32k的结果(通常不会太高，但我需要解决基本问题)-如果没有正确的连接比较，它需要: 33373行(0.19秒)。
在正确的联接中，它需要设置32955行(3分7.99秒)
3分钟太长了。
下面是一个包含正确联接的示例查询：
SELECT prism_actions.id, prism_actions.action_time, prism_actions.action_type, prism_actions.player, prism_actions.world, prism_actions.x, prism_actions.y, prism_actions.z, prism_actions.data, DATE_FORMAT(prism_actions.action_time, '%c/%e/%y') display_date, DATE_FORMAT(prism_actions.action_time, '%l:%i%p') display_time
FROM prism_actions
RIGHT JOIN (SELECT action_type, x, y, z, max(action_time) as action_time FROM prism_actions GROUP BY action_type, x, y, z) latest
ON prism_actions.action_time = latest.action_time
AND prism_actions.x = latest.x
AND prism_actions.y = latest.y
AND prism_actions.z = latest.z
AND prism_actions.action_type = latest.action_type
WHERE world = 'world'
AND (prism_actions.action_type = 'creeper-explode' OR prism_actions.action_type = 'entity-explode' OR prism_actions.action_type = 'tnt-explode' OR prism_actions.action_type = 'block-burn')
AND LEFT(prism_actions.action_type,5) != 'prism'
AND (prism_actions.x BETWEEN 2412.0286793077976 AND 2612.0286793077976)
AND (prism_actions.y BETWEEN -25.5 AND 174.5)
AND (prism_actions.z BETWEEN -2650.697295131335 AND -2450.697295131335)
ORDER BY prism_actions.action_time ASC, x ASC, z ASC, y ASC, id ASC
LIMIT 0,1000000如果没有，则只是移除正确的联接。
所以我的问题是：：
- 这是处理排除具有较新条目的记录的最佳方法吗？
- 如果是的话，我能做些什么来提高业绩吗？
我之所以使用这样的查询，是因为我的软件需要按时间戳排序的结果，但应该始终只知道每个x/y/z上最近的活动，否则就会发生冲突。
表结构：
CREATE TABLE IF NOT EXISTS `prism_actions` (
`id` int(11) unsigned NOT NULL auto_increment,
`action_time` datetime NOT NULL,
`action_type` varchar(25) NOT NULL,
`player` varchar(16) NOT NULL,
`world` varchar(255) NOT NULL,
`x` int(11) NOT NULL,
`y` int(11) NOT NULL,
`z` int(11) NOT NULL,
`data` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;解释上述查询非常缩小的区域的结果：
+----+-------------+---------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1870 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | prism_actions | ALL | NULL | NULL | NULL | NULL | 1970 | Using where |
| 2 | DERIVED | prism_actions | ALL | NULL | NULL | NULL | NULL | 1970 | Using temporary; Using filesort |
+----+-------------+---------------+------+---------------+------+---------+------+------+----------------------------------------------+
3 rows in set (0.04 sec)回答 1
Stack Overflow用户
发布于 2013-01-12 18:35:27
我的猜测是，下面的工作会稍微好一点。假设它给出了正确的答案，但对性能没有多大帮助，请考虑为相关表提供显示CREATE语句，并在此查询上发布解释的输出.
SELECT pa.id
, pa.action_time
, pa.action_type
, pa.player
, pa.world
, pa.x
, pa.y
, pa.z
, pa.data
, DATE_FORMAT(pa.action_time, '%c/%e/%y') display_date
, DATE_FORMAT(pa.action_time, '%l:%i%p') display_time
FROM prism_actions pa
JOIN
( SELECT action_type
, x
, y
, z
, MAX(action_time) max_action_time
FROM prism_actions
GROUP
BY action_type
, x
, y
, z
) latest
ON latest.max_action_time = pa.action_time
AND pa.x = latest.x
AND pa.y = latest.y
AND pa.z = latest.z
AND pa.action_type = latest.action_type
WHERE world = 'world'
AND pa.action_type IN('creeper-explode','entity-explode','tnt-explode','block-burn')
AND pa.action_type NOT LIKE 'prism%'
AND pa.x BETWEEN 2412.0286793077976 AND 2612.0286793077976
AND pa.y BETWEEN -25.5 AND 174.5
AND pa.z BETWEEN -2650.697295131335 AND -2450.697295131335
ORDER
BY pa.action_time ASC
, x ASC
, z ASC
, y ASC
, id ASC
LIMIT 0,1000000;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14296325
复制相关文章
相似问题
腾讯云开发者
