下面的缩短查询选择所有行( entrys
)在给定距离内(根据用户e.altloc=0计算: location
或e.altloc=1: altlocation
).
我有e.uid,al.eid,e.country,e.tmstmp和 id
的是主键。
根据explain的问题,所有行都需要处理查询,而不是我喜欢的限制为2的2行。
我已经读过这个问题,但是我不能在使用连接之前进行限制,因为我需要先连接位置表,然后才能进行限制 limit 2
否则回报就错了。https://dba.stackexchange.com/questions/52079/does-using-limit-improve-the-performance-and-is-it-noticeable
查询:
SELECT
e.id, e.uid, e.title, e.description, l.place, l.placenonce, al.altplace, al.altplacenonce,
IF(e.altloc=0,
6371 * acos( cos( radians(:lat) ) * cos( radians( AES_DECRYPT(lat, UNHEX('###'), latnonce) ) ) * cos( radians( AES_DECRYPT(lng, UNHEX('###'), lngnonce) ) - radians(:lng) ) + sin( radians(:lat) ) * sin(radians(AES_DECRYPT(lat, UNHEX('###'), latnonce))) ) ,
6371 * acos( cos( radians(:lat) ) * cos( radians( AES_DECRYPT(altlat, UNHEX('###'), altlatnonce) ) ) * cos( radians( AES_DECRYPT(altlng, UNHEX('###'), altlngnonce) ) - radians(:lng) ) + sin( radians(:lat) ) * sin(radians(AES_DECRYPT(altlat, UNHEX('###'), altlatnonce))) )
) AS distance
FROM
entrys e
INNER JOIN
location l
ON l.id = e.uid
LEFT JOIN
altlocation al
ON al.eid = e.id
WHERE
IF(:border = 0, e.country = :countryid, e.country != 0 )
HAVING
distance <= 50
ORDER BY
e.tmstmp
DESC
LIMIT 2
第二个固定位置示例:
SELECT
s.id, s.image, s.description, s.title,
(
6371 * acos( cos( radians(:lat) ) * cos( radians( AES_DECRYPT(l.lat, :key, l.latnonce) ) ) * cos( radians( AES_DECRYPT(l.lng, :key, l.lngnonce) ) - radians(:lng) ) + sin( radians(:lat) ) * sin(radians(AES_DECRYPT(l.lat, :key, l.latnonce))) )
) AS distance
FROM
sponsors s
INNER JOIN
location l
ON l.id = s.id
WHERE
s.comp = 1 OR s.comp = 3 AND s.active = 1
HAVING
distance <= 50
ORDER BY
s.rotate
ASC
LIMIT 2
如果我的数据库中有数百万行,如何改进这种基于位置的查询?我只需要输出每个查询的2行
为第一个示例创建表:
CREATE TABLE `entrys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(5) NOT NULL,
`tmstmp` bigint(11) NOT NULL,
`approx_lat` mediumint(9) NOT NULL,
`approx_lng` mediumint(9) NOT NULL,
`altloc` tinyint(4) NOT NULL,
`title` varchar(70) COLLATE latin1_general_ci NOT NULL,
`description` text COLLATE latin1_general_ci NOT NULL,
`country` tinyint(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
KEY `tmstmp` (`tmstmp`),
KEY `country` (`country`),
) ENGINE=MyISAM CHARSET=latin1 COLLATE=latin1_general_ci
CREATE TABLE `location` (
`id` int(5) NOT NULL,
`lat` varbinary(50) NOT NULL,
`latnonce` varbinary(25) NOT NULL,
`lng` varbinary(50) NOT NULL,
`lngnonce` varbinary(25) NOT NULL,
`place` tinyblob NOT NULL,
`placenonce` tinyblob NOT NULL,
UNIQUE KEY `id` (`id`),
KEY `lat` (`lat`),
KEY `lng` (`lng`)
)
CREATE TABLE `altlocation` (
`id` int(5) NOT NULL,
`eid` int(5) NOT NULL,
`altlat` varbinary(50) NOT NULL,
`altlatnonce` varbinary(25) NOT NULL,
`altlng` varbinary(50) NOT NULL,
`altlngnonce` varbinary(25) NOT NULL,
`altplace` tinyblob NOT NULL,
`altplacenonce` tinyblob NOT NULL,
UNIQUE KEY `eid` (`eid`),
KEY `altlat` (`altlat`),
KEY `altlng` (`altlng`)
)
旁注:entrys的引擎应该是innodb,具有大约70%的读取率。位置表都是用innodb运行的。
编辑问题给威廉·伦泽马,让他回答:
这样效率会更高吗?
SELECT
e.id, e.uid, e.title, e.description, l.place, l.placenonce, al.altplace, al.altplacenonce,
IF(e.altloc=0,
6371 * acos( cos( radians(:lat) ) * cos( radians( AES_DECRYPT(lat, UNHEX('###'), latnonce) ) ) * cos( radians( AES_DECRYPT(lng, UNHEX('###'), lngnonce) ) - radians(:lng) ) + sin( radians(:lat) ) * sin(radians(AES_DECRYPT(lat, UNHEX('###'), latnonce))) ) ,
6371 * acos( cos( radians(:lat) ) * cos( radians( AES_DECRYPT(altlat, UNHEX('###'), altlatnonce) ) ) * cos( radians( AES_DECRYPT(altlng, UNHEX('###'), altlngnonce) ) - radians(:lng) ) + sin( radians(:lat) ) * sin(radians(AES_DECRYPT(altlat, UNHEX('###'), altlatnonce))) )
) AS distance
FROM
(
SELECT id, uid, title, description
FROM
entrys
WHERE
approx_lat > :min_lat
AND approx_lat < :max_lat
AND approx_lng > :min_lng
AND approx_lng < :min_lng
ORDER BY
e.tmstmp
DESC
LIMIT 2
) AS e
INNER JOIN
location l
ON l.id = uid
LEFT JOIN
altlocation al
ON al.eid = e.id
HAVING
distance <= 50
如果我在条目表中加上近似纬度和近似液化天然气
线索是将approx_lat和approx_lng移到entry表中,我可以插入altlocation或location only,这样我就可以摆脱了 IF
在查询内部。
是 HAVING distance <= 50
还有必要吗?
2条答案
按热度按时间xtfmy6hx1#
(部分回答。)
子查询的有用提示(有时)。
请注意,有几个(
uid, title, description
)庞大的列在子查询中拖来拖去。有一个
ORDER BY
以及LIMIT
,所以把它们拖来拖去需要一些努力。所以,
使用子查询中的最小列数,确保包含行
id
.在子查询之后,添加
JOIN
(通过id
)得到那些额外的列。此外,还有一个“covering”索引,它包含子查询中保留的所有列:
INDEX(approx_lat, approx_lng, tmstmp, id)
6yoyoihd2#
在查询中使用边界框。
示例(where子句中只有更改):
你可以计算
:min_lat
,:max_lat
,:min_lng
,和:max_lng
在执行查询之前。这些值将从所需半径生成:lat
以及:lng
值(在本例中为50)。如何做到这一点,我建议你读一读互联网上其他答案中的一个,比如这个。只需搜索地理位置边界框即可开始。
然后,您可以通过在
approx_lat
以及approx_lng
柱。当然,您也可以尝试添加几个复合索引(approx_lat,approx_lng)
和/或(approx_lng,approx_lat)
,因为优化器可以利用这些。然而,这些是我强烈建议的基准测试,看看他们是否提供任何改进或没有。额外的列来制作这些覆盖索引可能也会有所帮助,但我现在关注的是最基本的问题。请注意,您尝试优化的内容已经是一个困难的优化问题。事实上,你需要加密你的数据,使之更加困难。但是,只要您能够存储这些近似值,我们就可以绕过大部分额外的困难。
我也强烈建议你
IF
你的逻辑WHERE
条款。通过包含这些,您可以强制优化器查找每个记录,以查看它是否符合该条件。通常,要获得良好的性能,需要限制需要检查的记录数。这个
IF
语句无法优化(它不是可搜索的)。这也是为什么我的答案要求您存储近似值以便有效。如果必须首先解密数据,这意味着必须查找和检查每个记录。这会毁了你的表演。还要注意,在我的示例查询中,我忽略了
altlocation
中的表WHERE
条款。理想情况下,如果location
以及altlocation
如果相同,则应该只有一个数据表,然后从记录位置的记录联接到上的该表id
作为主要的或“候补的”。我希望这至少能帮你找到正确的方向。