我有一个非常特殊的实例,我们最近不得不将数据库从 MySQL 5.7 升级到 8.0,并且我不得不重写一些查询。
如果我的查询足够复杂,我通常准备在 DBeaver 中编写它,然后最终将其放入我们基于 Laravel 的(PHP)应用程序中,当然该应用程序使用 PDO 来连接到 MySQL。
我注意到这个查询经常需要 4-5 秒才能运行,而在 DBeaver 中则需要大约 30 毫秒。(我对原始版本进行了简化,只保留了使其变慢的部分。)
慢查询是这样的:
select
COUNT(location) as count,
ST_Latitude(location) as lat,
ST_Longitude(location) as lng
from
`LocationCache`
where
MBRContains(ST_GeomFromText('Polygon((
-116.41480990949 40.923245158437,
-116.41480990949 38.44220035439,
-120.68677260991 38.44220035439,
-120.68677260991 40.923245158437,
-116.41480990949 40.923245158437
))', 4326, 'axis-order=long-lat'),
location)
group by
`location`;
我还发现,如果我通过 SSH 直接连接到我的数据库主机并运行mysql
命令行客户端,查询同样慢。
如果我删除 COUNT() 和分组依据,甚至 ST_Latitude/ST_Longitude 函数调用,它仍然很慢 - 所以它似乎与地理空间条件本身有关。
CREATE 表语法(删除不相关的列/索引)如下所示:
CREATE TABLE `LocationCache` (
`media_id` bigint unsigned NOT NULL,
`location` point NOT NULL /*!80003 SRID 4326 */ COMMENT 'Location point',
UNIQUE KEY `media_id_unique` (`media_id`),
SPATIAL KEY `location_spatialindex` (`location`),
CONSTRAINT `1` FOREIGN KEY (`media_id`) REFERENCES `Media` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
需要明确的是,我正在从 PHP、命令行和 DBeaver 访问同一个 MySQL 服务器,该服务器在本地 Docker 容器中运行(图像mysql/mysql-server:8.0.32
)。我尝试过使用主机的mysql
命令行程序,并通过 SSH 连接到 docker 容器并运行其本地mysql
命令,但两者都很慢。
我向一些从未听说过仅使用 PHP 查询速度慢而在 GUI 中查询速度快的人展示了这一点,他们建议安装 MySQL Workbench 并在那里尝试。事实上:在 MySQL Workbench 中它也很快!
EXPLAIN
在 DBeaver 与 CLI 中的查询上使用给出了相同的解释。(他们都将location_spatialindex
键指定为 apossible_key
但key
由于某种原因都没有在列中使用它?)
我唯一的猜测是,Workbench/DBeaver 使用的 MySQL 驱动程序与 PDO/CLI 相比有一些不同,但我在这里真的很困惑。
任何帮助表示赞赏!提前致谢!
编辑:表有〜700,000行。我从来没有听说过EXPLAIN ANALYZE SELECT
(感谢@RickJames),但输出是这样的:
-> Table scan on <temporary> (actual time=5032.824..5032.824 rows=6 loops=1)
-> Aggregate using temporary table (actual time=5032.823..5032.823 rows=6 loops=1)
-> Filter: mbrcontains(<cache>(st_geomfromtext('Polygon((\n -116.41480990949 40.923245158437,\n -116.41480990949 38.44220035439,\n -120.68677260991 38.44220035439,\n -120.68677260991 40.923245158437,\n -116.41480990949 40.923245158437\n ))',4326,'axis-order=long-lat')),LocationCache.location) (cost=73576.76 rows=135833) (actual time=301.622..5024.938 rows=1343 loops=1)
-> Table scan on LocationCache (cost=73576.76 rows=663100) (actual time=0.022..511.299 rows=703852 loops=1)