在具有〜225万行的单个表上进行选择查询的优化技术？

Question

我有一个在InnoDB引擎上运行的MySQL表，该表squares具有大约2,250,000行，其表结构如下：

`squares` (
   `square_id` int(7) unsigned NOT NULL,
   `ref_coord_lat` double(8,6) NOT NULL,
   `ref_coord_long` double(9,6) NOT NULL,
   PRIMARY KEY (`square_id`),
   KEY `ref_coord_lat` (`ref_coord_lat`),
   KEY `ref_coord_long` (`ref_coord_long`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

第一列square_id保存一个从0到2.25M的简单增量值，而ref_coord_lat＆分别ref_coord_long保存一个点的一组以十进制度表示的纬度和经度坐标。

这是一个只读表。不会添加其他行，并且唯一需要针对它运行的查询如下：

SELECT * FROM `squares` WHERE 
  `ref_coord_lat` BETWEEN :southLat AND :northLat AND 
  `ref_coord_long` BETWEEN :westLong AND :eastLong

…冒号后面的值是PHP PDO占位符。本质上，此查询的目标是获取表中当前位于Google
Maps窗口视口中的所有坐标点，该坐标点由查询中的4个坐标界定。

我已经限制了使用Google Maps API运行该查询的缩放级别，因此可以提取的最大行数为 〜5600
。随着缩放级别的增加，最终的获取总量将显着减少。

直接在PHPMyAdmin中运行这样的示例查询需要1.40-1.45秒。这太长了。我已经在运行标准索引ref_coord_lat，ref_coord_long这使查询时间从〜5秒减少了，但是对于最终用户期望及时响应的地图而言，这仍然太大了。

我的问题很简单：如何进一步优化该表/查询以提高获取结果的速度？

Answer 1

在上创建复合索引(lat, long)应该会很有帮助。

但是，正确的解决方案是看一下MySQL空间扩展。专门创建了空间支持来处理二维数据和针对此类数据的查询。如果创建适当的空间索引，则典型的查询性能应轻松超过上的复合索引的性能(lat, long)。