我的SQL - 如何索引此查询？

Question

我在索引此查询时遇到问题：

SELECT  *,
        (ROUND(SQRT(
           POW(LEAST(ABS(-12 - wdata.x),
           ABS(401 - ABS(-12 - wdata.x))), 2) +
            POW(LEAST(ABS(45 - wdata.y),
            ABS(401 - ABS(45 - wdata.y))), 2)),3)
         ) AS distance
    FROM  odata
    LEFT JOIN  wdata ON wdata.id=odata.vref
    WHERE  TRUE
    HAVING  distance<4.9497474683058326708059105347339
    ORDER BY  distance
    LIMIT  30

结果是：

+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                         | rows  | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+
|  1 | SIMPLE      | odata | ALL    | NULL          | NULL    | NULL    | NULL                        | 19118 | Using temporary; Using filesort |
|  1 | SIMPLE      | wdata | eq_ref | PRIMARY       | PRIMARY | 4       | mytravia_1000-14.odata.vref |     1 | NULL                            |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+
2 rows in set (0.00 sec)

我知道它显示0.00秒是执行时间，但此查询将运行多次，它显示它会减慢我的数据库，我不知道为什么！

每次我看到行检查是459448这个查询，所以它在某些原因对我的工作相当糟糕。

有人能给个建议吗？我如何为odata表制作合适的索引？或者我可以使用子查询来修复它？

这些表是：

解释odata:

vref    int(10) unsigned    NO  PRI     NULL    
type    tinyint(4)  NO      NULL    
conqured    mediumint(8) unsigned   NO      NULL    
wood    float(12,2) NO      NULL    
iron    float(12,2) NO      NULL    
clay    float(12,2) NO      NULL    
woodp   float(12,2) NO      NULL    
ironp   float(12,2) NO      NULL    
clayp   float(12,2) NO      NULL    
maxstore    mediumint(8) unsigned   NO      NULL    
crop    float(12,2) NO      NULL    
cropp   float(12,2) NO      NULL    
maxcrop     mediumint(8) unsigned   NO      NULL    
lasttrain   int(10) unsigned    NO      NULL    
lastfarmed  int(10) unsigned    NO      NULL    
lastupdated     int(10) unsigned    NO      NULL    
loyalty     tinyint(4)  NO      100 
owner   smallint(5) unsigned    NO      2   
name    char(45)    NO      Oasis   

并解释 wdata：

id  int(10) unsigned    NO  PRI     NULL    auto_increment
fieldtype   tinyint(3)  NO      NULL    
oasistype   tinyint(3)  NO      NULL    
x   smallint(5) NO  MUL     NULL    
y   smallint(5) NO  MUL     NULL    
occupied    tinyint(4)  NO      NULL    
image   char(12)    NO  MUL     NULL    
pos     tinyint(3)  NO  MUL     NULL    

不得不说wdata.id和odata.vref已经被索引了！

表格结构-

CREATE TABLE IF NOT EXISTS `odata` (
  `vref` int(10) unsigned NOT NULL,
  `type` tinyint(4) NOT NULL,
  `conqured` mediumint(8) unsigned NOT NULL,
  `wood` float(12,2) NOT NULL,
  `iron` float(12,2) NOT NULL,
  `clay` float(12,2) NOT NULL,
  `woodp` float(12,2) NOT NULL,
  `ironp` float(12,2) NOT NULL,
  `clayp` float(12,2) NOT NULL,
  `maxstore` mediumint(8) unsigned NOT NULL,
  `crop` float(12,2) NOT NULL,
  `cropp` float(12,2) NOT NULL,
  `maxcrop` mediumint(8) unsigned NOT NULL,
  `lasttrain` int(10) unsigned NOT NULL,
  `lastfarmed` int(10) unsigned NOT NULL,
  `lastupdated` int(10) unsigned NOT NULL,
  `loyalty` tinyint(4) NOT NULL DEFAULT '100',
  `owner` smallint(5) unsigned NOT NULL DEFAULT '2',
  `name` char(45) NOT NULL DEFAULT 'Unoccupied Oasis',
  PRIMARY KEY (`vref`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

对于 wdata 是 -

CREATE TABLE IF NOT EXISTS `wdata` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fieldtype` tinyint(3) NOT NULL,
  `oasistype` tinyint(3) NOT NULL,
  `x` smallint(5) NOT NULL,
  `y` smallint(5) NOT NULL,
  `occupied` tinyint(4) NOT NULL,
  `image` char(12) NOT NULL,
  `pos` tinyint(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `x` (`x`),
  KEY `y` (`y`),
  KEY `image` (`image`),
  KEY `pos` (`pos`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=160802 ;

顺致敬意，

Answer 1

正如瑞克·詹姆斯所说，你不能索引< code >距离，因为它是动态计算的。

这就给你出了两个问题:1，它慢，你也知道。第二，你在数据层做逻辑计算，这是我不喜欢的。

我认为这里最好的解决方案是不要像你正在做的那样计算飞行距离。为什么不在插入/更新 x 和/或 y 的同时将距离存储在 wdata 中呢？将其放在名为“距离”的列中。然后，您可以为该列编制索引，一切都将非常快。此外，您不会一遍又一遍地重做计算，从而使事情变得更有效率。最后，您将能够删除数据层的计算，并将其放在应用程序级别的更合适的位置。

CREATE TABLE IF NOT EXISTS `wdata` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fieldtype` tinyint(3) NOT NULL,
  `oasistype` tinyint(3) NOT NULL,
  `x` smallint(5) NOT NULL,
  `y` smallint(5) NOT NULL,
  `distance` decimal(32, 24) NOT NULL,
  `occupied` tinyint(4) NOT NULL,
  `image` char(12) NOT NULL,
  `pos` tinyint(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `x` (`x`),
  KEY `y` (`y`),
  KEY `distance` (`distance`),
  KEY `image` (`image`),
  KEY `pos` (`pos`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=160802;

（距离的数据类型可以是您认为合适的任何数据类型。我使用了 decimal（32， 24），它将存储最多 24 位在小数点右边和最多 12 个位置的数字。根据需要进行调整。

然后，您可以将插入内容更改为如下所示:

（示例数据：

• 字段类型=1
• oasistype=1
• x=10
• y=11
• 占用=1
• 图片='abcde fg hi j kl'
• pos=1

)

insert into wdata (fieldtype,oasistype,x,y,distance,occupied, image, pos)
values (1, 1, 10, 11, (ROUND(SQRT(
           POW(LEAST(ABS(-12 - 10),
           ABS(401 - ABS(-12 - 10))), 2) +
            POW(LEAST(ABS(45 - 11),
            ABS(401 - ABS(45 - 11))), 2)),3)
         ), 1, 'abcdefghijkl', 1)

您的select语句将是：

SELECT  * FROM  odata
    LEFT JOIN  wdata ON wdata.id=odata.vref
    where wdata.distance<4.9497474683058326708059105347339
    ORDER BY  wdata.distance
    LIMIT  30

如果wdata表中已经有一组数据，并且无法重新插入，则可以执行此操作，一次更新所有行（在添加新的距离列后）：

update wdata set distance = 
   (ROUND(SQRT(
    POW(LEAST(ABS(-12 - x),
    ABS(401 - ABS(-12 - x))), 2) +
    POW(LEAST(ABS(45 - y),
    ABS(401 - ABS(45 - y))), 2)),3))

另外值得注意的是，我会从MySQL中删除数学运算，让你的应用程序去做。

例如，在PHP中:

$distance = (round(sqrt(pow(min(abs(-12 - 10), abs(401 - abs(-12 - 10))), 2) + pow(min(abs(45 - 11), abs(401 - abs(45 - 11))), 2)),3));

$sql = "insert into wdata (fieldtype, oasistype, x, y, distance, occupied, image, pos)
        values (1, 1, 10, 11, $distance, 1, 'abcdefghijkl', 1)";

Answer 2

目前没有索引可以加快查询速度。它当前必须为 JOIN 化两个表的结果中的每一行计算该 SQRT。

在执行任何< code > join 操作之前，先找到closes 30，您会得到一些改进:

SELECT  *, distance
  FROM  ( SELECT id,
    (ROUND(SQRT(
       POW(LEAST(ABS(-12 - wdata.x),
       ABS(401 - ABS(-12 - wdata.x))), 2) +
        POW(LEAST(ABS(45 - wdata.y),
        ABS(401 - ABS(45 - wdata.y))), 2)),3)
     ) AS distance
    FROM  wdata
    HAVING  distance<4.9497474683058326708059105347339
    ORDER BY  distance
    LIMIT 30
      ) w
  JOIN odata ON w.id=odata.vref
  ORDER BY  w.distance

这将需要id和vref索引。

下一个改进是将搜索限制在至少一个方向：

AND x >= -12 - 4.94...
AND x <= -12 + 4.94...

并在wdata中具有复合索引index（x，id）。（对不起，我不知道“401”在公式中的位置。）

如果这还不够快，解决方案会变得更加复杂。