MySQL奇怪的慢查询 [英] Strange slow queries with MySQL

问题描述

我有一个表 A ，其中包含IP范围(列startIpNum，endIpNum，locId)和表 A_location (列locaId和其他不重要的列).有以下索引-A上的startIpNum和endIpNum，A_location上的locId.

I have table A which contains IP ranges (columns startIpNum, endIpNum, locId) and table A_location (column locaId and other not important columns). There are following indexes - startIpNum and endIpNum on A, and locId on A_location.

问题在于，有时查询执行速度非常慢.下面是一个mysql-slow日志文件，其中包含两个查询，其中有些不返回任何内容.

The problem is that sometimes queries are performed very slowly. Below there is a mysql-slow logfile, which contains two queries, bith of them return nothing.

# Time: 140001 21:18:45    
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.023001  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1394367480;
SELECT * FROM A_location AS location, (SELECT * FROM A WHERE (3998482191 BETWEEN startIpNum AND endIpNum) ORDER BY startIpNum DESC LIMIT 1) AS blocks WHERE location.locId = blocks.locId;

# Time: 140309 21:18:45
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 54.893140  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1394367525;
SELECT * FROM A_location AS location, (SELECT * FROM A WHERE (2463400155 BETWEEN startIpNum AND endIpNum) ORDER BY startIpNum DESC LIMIT 1) AS blocks WHERE location.locId = blocks.locId;

这种行为的原因可能是什么?

What could be the reason of such behaviour?

EXPLAIN结果:

EXPLAIN results:

更新: 问题解决了，最终查询

UPDATE: The question was solved, final query

ALTER TABLE A ORDER BY startIpNum ASC;
SELECT A_location.* FROM A_location AS location,
(SELECT A.* FROM A as blocks,
(SELECT * FROM A WHERE startIpNum < 24465138 ORDER BY startIpNum DESC LIMIT 1) AS startipnumquery
WHERE blocks.startIpNum = startipnumquery.startIpNum AND blocks.endIpNum > 24465138
ORDER BY blocks.endIpNum ASC LIMIT 1) as subresult
WHERE location.locId = subresult.locId;

推荐答案

您当前的查询已更新如下，请在查询下方运行并检查响应时间，我99.99％确信它将为您提供最佳结果

Your current query i have updated as below please run below query and check response time, i 99.99% sure it will giving to you best result

Step 1: ALTER TABLE A ORDER BY startIpNum DESC;


Step 2: SET timestamp=1394367480;
SELECT location.*, (SELECT * FROM A WHERE (3998482191 BETWEEN startIpNum AND endIpNum) LIMIT 1) AS blocks FROM A_location AS location, WHERE location.locId = blocks.locId;

Step 1: ALTER TABLE A ORDER BY startIpNum DESC;

Step 2: SET timestamp=1394367525;
SELECT location.*, (SELECT * FROM A WHERE (2463400155 BETWEEN startIpNum AND endIpNum) LIMIT 1) AS blocks FROM A_location AS location WHERE location.locId = blocks.locId;

我刚刚从查询中删除了 ORDER BY字段DESC ，并使用 ALTER TABLE A ORDER BY startIpNum DESC更改了表；

i have just remove ORDER BY field DESC from your query and table alter with ALTER TABLE A ORDER BY startIpNum DESC;

这篇关于MySQL奇怪的慢查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！