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

Question

我有 2 个表(company 和 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(因此有 min 和子查询)

现在，我的问题是:我不明白为什么mysql不能使用company_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

在依赖子查询中，我希望优化器选择 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) 到底是什么样子的？)