我有一个简单的SELECT mysql请求,用于按距离对用户进行排序,如下所示:
SELECT
( 6371 * acos( cos( radians(48.85980226) ) * cos( radians( latitude ) ) * cos( radians
( longitude ) - radians(2.29202271) ) + sin( radians(48.85980226) ) * sin( radians( latitude
) ) ) ) AS distance,
id FROM `users`
HAVING distance <= '100'
ORDER BY distance ASC
我的数据库(MySql 5.7)中有大约50000个用户。当我将表设置为MyISAM时,请求速度是合理的,大约为0.2s;但如果我把引擎转到innodb,大约需要8秒!我真的需要使用innodb,因为数据非常随意地写入&read(MyISAM导致大量"MyISAM等待表级锁定")。你知道如何优化查询的速度吗?谢谢
(对不起我的英语)
EDIT2:我将坐标的类型从DECIMAL更改为FLOAT,查询速度更快:5s而不是8s。。。
第3版(来自注释,带边框)
SELECT ( 6371 * acos( cos( radians(48.85980226) ) *
cos( radians( latitude ) ) * cos( radians ( longitude ) -
radians(2.29202271) ) + sin( radians(48.85980226) ) *
sin( radians( latitude ) ) ) ) AS distance,
uid
FROM users
WHERE longitude between 0.089154409442052 AND 4.4948910105579
AND latitude between 47.410526897681 AND 50.309077622319
HAVING distance <= '100'
ORDER BY distance ASC
编辑4:这是我的表格结构:
CREATE TABLE `users`
( `id` mediumint(9) NOT NULL AUTO_INCREMENT,
`uid` varchar(20) NOT NULL,
`token` varchar(70) NOT NULL,
`last_connection` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`email` varchar(255) NOT NULL,
`longitude` float NOT NULL,
`latitude` float NOT NULL,
`presentation` text NOT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
KEY `uid_token` (`uid`,`token`),
KEY `longitude` (`longitude`),
KEY `latitude` (`latitude`)
) ENGINE=InnoDB AUTO_INCREMENT=53004 DEFAULT CHARSET=utf8
字段last_connection
被非常频繁地更新。在线用户越多,查询速度就越慢。。。我想是因为更新,行被临时锁定,查询速度变慢…:/当使用MyISAM时,搜索查询是可以的,但更新会变慢(等待锁定)
编辑5这是我的更新查询:
UPDATE `users` SET `last_connection` = CURRENT_TIMESTAMP WHERE `uid` = 'XXXX';
我更改了它并添加了一个限制1:
UPDATE `users` SET `last_connection` = CURRENT_TIMESTAMP WHERE `uid` = 'XXXX' LIMIT 1;
这似乎更快。我需要等待更多的用户连接,以检查差异是否很大
数据库引擎对每一行进行计算。
那么,将已经计算好的值存储为变量怎么样?
SET @cos_point1 = cos(radians(48.85980226));
SET @rad_point1 = radians(2.29202271);
SET @sin_point1 = sin(radians(48.85980226));
SELECT
( 6371 * acos( @cos_point1 * cos( radians( latitude ) ) * cos( radians
( longitude ) - @rad_point1 ) + @sin_point1 * sin( radians( latitude
) ) ) ) AS distance,
id FROM `users`
HAVING distance <= 100
ORDER BY distance ASC;
我也有个主意!
试试这个:
1) 创建Memory类型的表users_geodata
(因为实际数据将在users
表中,所以让我们对临时表使用最快的引擎):
CREATE TABLE `users_geodata` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=Memory;
2) 执行此操作的时间表同步:
REPLACE INTO users_geodata
SELECT id, latitude, longitude FROM users
3) 运行您的查询:
SELECT
( 6371 * acos( cos( radians(48.85980226) ) * cos( radians( latitude ) ) * cos( radians
( longitude ) - radians(2.29202271) ) + sin( radians(48.85980226) ) * sin( radians( latitude
) ) ) ) AS distance,
id FROM `users_geodata`
HAVING distance <= 100
ORDER BY distance ASC
由于InnoDB和MyISAM必须做等量的工作,我怀疑真正的问题在于缓存。检查以下各项的值:
key_buffer_size
innodb_buffer_pool_size
并记下你有多少RAM。
如果您只使用InnoDB,请检查buffer_pool是否约为可用RAM的70%(对于4GB或以上的机器)更多详细信息
加快速度的下一步是让WHERE
子句包含一个"边界框",再加上INDEX(latitude)
和INDEX(longitude)
。(使用综合指数没有任何好处。)
对于大型lat/lng表。
使用"覆盖"指数
用KEY(latitude, longitude, uid)
替换KEY(latitude)
,用KEY(longitude, latitude, uid)
替换KEY(longitude)
。这些将是"覆盖",因此速度会更快,争议也可能更小。(优化器将根据实际查询中的值,根据统计信息在两个索引之间进行选择。)