Início / user-280427

Offlein's questions

Martin Hope
Offlein
Asked: 2023-10-19 08:02:15 +0800 CST
  • 5

Estou tendo um caso muito peculiar em que recentemente tivemos que atualizar nosso banco de dados do MySQL 5.7 para 8.0 e tive que reescrever algumas consultas.

Se minha consulta for complicada o suficiente, geralmente preparo a escrita no DBeaver e, eventualmente, coloco-a em nosso aplicativo baseado em Laravel (PHP), que obviamente usa PDO para se conectar ao MySQL.

Percebi que essa consulta frequentemente demorava de 4 a 5 segundos para ser executada, enquanto leva cerca de 30 ms no DBeaver. (Simplifiquei o original e mantive apenas a parte que o torna lento.)

A consulta lenta é esta:

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

Descobri também que, se eu me conectar diretamente ao meu host de banco de dados via SSH e executar o mysqlcliente de linha de comando, a consulta será igualmente lenta.

Se eu remover COUNT() e o grupo por, e até mesmo as chamadas de função ST_Latitude/ST_Longitude, ainda será lento - então parece ser com a própria condição geoespacial where.

A sintaxe da tabela CREATE (cortada de colunas/índices irrelevantes) é semelhante a esta:

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

Para deixar claro, estou acessando o mesmo servidor MySQL a partir de PHP, linha de comando e DBeaver, que está sendo executado em um contêiner Docker local (imagem mysql/mysql-server:8.0.32). Tentei usar mysqlo programa de linha de comando do meu computador host, fazer SSH no contêiner do docker e executar seu mysqlcomando local, mas ambos são lentos.

Mostrei isso para algumas pessoas que nunca tinham ouvido falar de uma consulta lenta apenas usando PHP e rápida em minha GUI, e eles recomendaram instalar o MySQL Workbench e tentar lá. Na verdade: no MySQL Workbench também foi rápido!

Usar EXPLAINna consulta no DBeaver vs CLI fornece a mesma explicação. (Ambos especificam a location_spatialindexchave como possible_keymas nenhum deles a usa na keycoluna por algum motivo?)

Meu único palpite é que há algo diferente no driver MySQL que o Workbench/DBeaver usa em comparação com o PDO/CLI, mas estou realmente perdido aqui.

Qualquer ajuda será apreciada! Desde já, obrigado!

EDIT: A tabela tem aproximadamente 700.000 linhas. E eu nunca tinha ouvido falar EXPLAIN ANALYZE SELECT(Obrigado @RickJames), mas o resultado é este:

-> 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)
mysql