我有一个非常特殊的实例,我们最近不得不将数据库从 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)
我根据另一位 DBA 的建议解决了这个问题。(感谢用户 @Rick James 建议运行
EXPLAIN ANALYZE SELECT
。)目前还不是 100% 清楚是什么原因导致这个问题,但至少我可以可靠地修复它。如果服务器中没有足够的资源来执行此操作,则似乎索引(...至少是此空间索引。)本身并未填充(或者由于某种原因无法使用)。
此外,尚不清楚为什么使用 DBeaver/MySQL Workbench 能够以某种方式弥补资源的缺乏,而
mysql
命令行应用程序却无法做到这一点。不管怎样,我修改了以下三个全局变量:
max_heap_table_size
被设置为64M
tmp_table_size
被设置为64M
innodb_buffer_pool_size
设置为4G
(至少目前在我的本地 Docker MySQL 服务器中)。目前还不清楚哪一个是灵丹妙药,但效果几乎是立竿见影的。如果我在 CLI 应用程序中运行相同的查询
mysql
,实际上最后一次需要 5 秒(我猜是为了构建临时表和/或以某种方式填充索引?),但在那之后,所有类似的查询都几乎是瞬时的,并且,如果您使用EXPLAIN
表明他们正在使用该空间索引,正如我最初希望的那样。