MySQL -查询优化:子查询、用户定义变量和连接

Question

我目前正在构建一个查询(但在其上下文中非常重要)，这个查询似乎正在工作(在质量上是可以的)，但我认为它可以运行得更快。

我正在MySQL 5.7.29上运行测试，直到在GPU模式下运行OmnisciDB的盒可用为止(这应该是相对较快的)。虽然我希望切换到不同的DB后端将提高性能，但我也意识到它可能需要对表结构、所使用的查询技术等进行一些调整，但这是以后的工作。

一个小小的背景：

Data被概括为一个非常简单的表：

CREATE TABLE `entities_for_perception` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `pos` POINT NOT NULL,
    `perception` INT(11) NOT NULL DEFAULT '0',
    `stealth` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    SPATIAL INDEX `pos` (`pos`),
    INDEX `perception` (`perception`),
    INDEX `stealth` (`stealth`)
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB
AUTO_INCREMENT=10001
;

其中包含的值如下(显而易见，但有助于可视化:- )：

| id | pos | perception | stealth |
|  1 | ... | 10         | 3       |
|  2 | ... | 6          | 5       |
|  3 | ... | 5          | 5       |
|  4 | ... | 7          | 7       |
etc..

现在我有了这个查询(参见下面)，它的目的是：在一次传递中，获取查看其他实体的“实体”的所有is，并返回"who who"的列表。“一蹴而就”是显而易见的，是限制往返。让我们假设POINT()在笛卡尔系统中。

查询如下：

SHOW WARNINGS;
SET @automatic_perception_distance := 10;

SELECT
    *
FROM (
    SELECT 
        e1.id AS oid,
        e1.perception AS operception,
        @max_perception_distance := e1.perception * 5 AS 'max_perception_distance',
        @dist := ST_DISTANCE(e1.pos, e2.pos) AS 'dist',
        # minimum 0
        @dist_from_auto := GREATEST(@dist - @automatic_perception_distance, 0) AS 'dist_from_auto',
        @effective_perception := (
            @origin_perception - (
                @dist_from_auto
                / (@max_perception_distance - @automatic_perception_distance)
                * @origin_perception
            )
        ) AS 'effective_perception',
        e2.id AS tid,
        e2.stealth AS tstealth
    FROM 
        entities_for_perception e1
    INNER JOIN entities_for_perception e2 ON 
        e1.id != e2.id
    ORDER BY
        oid,
        dist
) AS subquery
WHERE 
    effective_perception >= tstealth
;

它所做的是通过应用以下标准/过滤器来列出“谁似乎是谁”：

确定感知不属于possible

• determining的最大距离的最小距离(未实现yet)

• determining，有效感知值变化(和回归))，distance

• ...and比较“观察者”和“目标”的有效感知。--

这是可行的，但在很少行(~1,000)的表上运行得有点慢(笔记本+虚拟盒+ centos7)。查询时间似乎在0.2到0.29秒之间波动。然而，这比每个“观察者”的一个查询要快几个数量级，而这个查询不会在1,000+观察者中进行缩放。哈哈。:-)

产出实例：

| oid | operception | max_perception_distance | dist               | dist_fromt_auto    | effective_perception | tid | tstleath |
| 1   | 9           | 45                      | 1.4142135623730951 | 0                  | 9                    | 156 | 5        |
| 1   | 9           | 45                      | 11.045361017187261 | 1.0453610171872612 | 8.731192881294705    | 164 | 2        |
| 1   | 9           | 45                      | 13.341664064126334 | 3.341664064126334  | 8.140714954938943    | 163 | 8        |
| 1   | 9           | 45                      | 16.97056274847714  | 6.970562748477139  | 7.207569578963021    | 125 | 7        |
| 1   | 9           | 45                      | 25.019992006393608 | 15.019992006393608 | 5.137716341213072    | 152 | 3        |
| 1   | 9           | 45                      | 25.079872407968907 | 15.079872407968907 | 5.122318523665138    | 191 | 5        |
etc.

我认为原因是反应缓慢吗？

1. 是子查询？
2. 是应用于它们的变量或算法？
3. 是我不知道的

or？

谢谢你的洞察力！

Answer 1

索引可能会有所帮助：CREATE INDEX idx_ID ON entities_for_perception (id);

如果要升级到MySQL版本8，可以利用公共表表达式，如下所示：

with e1 as (
    SELECT 
        e1.id AS oid,
        e1.perception AS operception,
        @max_perception_distance := e1.perception * 5 AS 'max_perception_distance',
        @dist := ST_DISTANCE(e1.pos, e2.pos) AS 'dist',
        # minimum 0
        @dist_from_auto := GREATEST(@dist - @automatic_perception_distance, 0) AS 'dist_from_auto',
        @effective_perception := (
            @origin_perception - (
                @dist_from_auto
                / (@max_perception_distance - @automatic_perception_distance)
                * @origin_perception
            )
        ) AS 'effective_perception',
        e2.id AS tid,
        e2.stealth AS tstealth
    FROM 
        entities_for_perception)
SELECT *
FROM  e1
    INNER JOIN entities_for_perception e2 ON 
        e1.id != e2.id
    ORDER BY
        oid,
        dist
WHERE 
    effective_perception >= tstealth
;