select子句中子查询的mysql性能优化

Question

我有两个表(公司和companies_locations)和一个非常简单的查询

SELECT 
  c.name, 
  (SELECT MIN(ST_Distance_Sphere(l.coords, POINT(4.985173, 45.001672)) from companies_locations l where l.companyId = c.id
FROM companies c
WHERE MATCH (c.name) AGAINST ('+rand*' IN BOOLEAN MODE) 

注意:经度、纬度和全文是通过我的代码中的参数给出的。

注意:对于给定的公司，可能存在多个companies_locations (因此是最小查询和子查询)。

现在，我的问题是：--我不明白为什么mysql不能为companies_locations(companyId，coords)使用覆盖索引

下面是sql的详细信息：

drop table IF exists companies;
drop table IF exists companies_locations;

CREATE TABLE `companies` (
  `id` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `idx_ft_provider_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `companies_locations` (
  `id` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `companyId` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `coords` point DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_providerId` (`companyId`,`coords`(25)),
  KEY `idx_providerIdCoords` (`companyId`,`coords`(25))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO companies (id, name) ...;
INSERT INTO companies_locations (id, companyId, coords) ...;

explain SELECT 
      c.name, 
      (SELECT MIN(ST_Distance_Sphere(l.coords, POINT(4.985173, 45.001672))) from companies_locations l where l.companyId = c.id) as dist
    FROM companies c
    WHERE MATCH (c.name) AGAINST ('+rand*' IN BOOLEAN MODE) 

解释计划是：

'1', 'PRIMARY', 'c', NULL, 'fulltext', 'idx_ft_provider_name', 'idx_ft_provider_name', '0', 'const', '1', '100.00', 'Using where; Ft_hints: no_ranking'
'2', 'DEPENDENT SUBQUERY', 'l', NULL, 'ref', 'idx_providerId,idx_providerIdCoords', 'idx_providerId', '162', 'beasyness.c.id', '1', '100.00', NULL

在依赖的SUBQUERY中，我希望优化器选择idx_providerIdCoords和“使用索引:true”

即使我删除了另一个索引，优化器似乎仍在执行“简单”引用(即需要转到表中读取同弦)。

Answer 1

几乎在所有情况下，当MySQL遇到“前缀索引”(INDEX(..., coords(25)) )时，它都会进行投机。具体来说，它不能声称“使用指数”，因为该指数不是“覆盖”。也就是说，它可能需要前25个之后的字节。

由于idx_providerIdCoords和idx_providerId是相同的，所以您不应该期望优化器使用一个而不是另一个。

以下可能是对您的情况的优化。变化

PRIMARY KEY (`id`),
KEY `idx_providerId` (`companyId`,`coords`(25)),
KEY `idx_providerIdCoords` (`companyId`,`coords`(25))

至

PRIMARY KEY(`companyId`,`coords`),
KEY (`id`)

这个“集群”在companyId (加上coords)上，从而将给定公司的所有位置“相邻”放在表中。但是，它确实拆分了id值。( id VARCHAR(40)到底是什么样子？)