mysql - 我怎样才能加快我的 SQL 查询？

Question

这里是查询:

SELECT name, SUM(  `count` ) AS Total
FROM  `identdb` 
WHERE MBRCONTAINS( GEOMFROMTEXT(  'LineString(34.4 -119.9, 34.5 -119.8)' ) , latlng ) 
AND MOD( DAYOFYEAR( CURDATE( ) ) - DAYOFYEAR(  `date` ) +365, 365 ) <=14
OR MOD( DAYOFYEAR(  `date` ) - DAYOFYEAR( CURDATE( ) ) +365, 365 ) <=14
AND MBRCONTAINS( GEOMFROMTEXT(  'LineString(34.4 -119.9, 34.5 -119.8)' ) , latlng ) 
GROUP BY  `name`

它基本上会找到一年中的某一天是今天正负 14 号的任何行，以及 latlng 空间列位于矩形中的行。

这是我的数据库的样子:

#   Column  Type        Collation   
1   name    varchar(66) utf8_general_ci 
2   count   tinyint(3)
3   date    date    
4   latlng  geometry
5   lat1    varchar(15) latin1_swedish_ci
6   long1   varchar(15) latin1_swedish_ci

Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
PRIMARY BTREE   Yes No  name    0   A       
                        count   0   A   
                        date    0   A   
                        lat1    0   A   
                        long1   6976936 A   
sp_index SPATIAL    No  No  latlng (32) 0   A

有 700 万条记录，查询大约需要 7 秒。我不知道如何加快速度，在此先感谢!

解释:

id  select_type table   type    possible_keys   key     key_len ref  rows       Extra
1   SIMPLE      identdb ALL     sp_index        NULL    NULL    NULL 6976936    Using where; Using temporary; Using filesort

查询的更新说明:我相信 MBRCONTAINS 创建了一个矩形，我可以在其中比较 latlng 空间点是否在内部。日期部分是寻找 dayofyear + 或 - 14 天。它使用模块化算法，这样它就不会在新的一年里搞砸了。由于使用了 OR，我不得不将 MBRCONTAINS 部分放入两次。

我的查询需求是找出所有 name 有一年中的某一天 + 或 - 14 天，并且在给定的纬度/经度对内，然后计算总计数对于每个。

我在这方面很笨，所以如果我做的很笨，请纠正我。谢谢大家!

Answer 1

重写它，以便您的计算对每个查询 进行一次，而不是对每个行 进行一次，方法是表达您的谓词，使该列不是计算的一部分。

例如，这个表达式:

MOD( DAYOFYEAR( CURDATE( ) ) - DAYOFYEAR(  `date` ) +365, 365 ) <= 14

需要在date上进行700万次计算，可以表示为

`date` between SUBDATE( CURDATE( ), 14) and ADDDATE( CURDATE( ), 14)

这仅需要 1 次计算，并且还允许使用 date 列上的索引。
仅此一项更改就会加快您的查询速度。

如果你没有关于日期的索引，放一个，你的查询就会飞起来:

create index mytable_date on mytable(`date`);

我不知道 MBRCONTAINS 做了什么，但也尝试重构它，以便列值不在计算中。