php - MySQL 奇怪的慢查询

Question

我有表 A，其中包含 IP 范围(列 startIpNum、endIpNum、locId)和表 A_location(列 localId 和其他不重要的列)。有以下索引 - A 上的 startIpNum 和 endIpNum，以及 A_location 上的 locId。

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

# 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;

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

解释结果:

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

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;

Answer 1

我已将您当前的查询更新如下，请运行下面的查询并检查响应时间，我 99.99% 确定它会给您最好的结果

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 field DESC，并使用ALTER TABLE A ORDER BY startIpNum DESC;

更改了表