在具有〜225万行的单个表上进行选择查询的优化技术? [英] Optimization techniques for select query on single table with ~2.25M rows?

问题描述

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

I have a MySQL table running on the InnoDB engine called squares that has roughly 2,250,000 rows with the following table structure:

`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分别保存一个点的一组以十进制度表示的纬度和经度坐标.

The first column square_id holds a simple incrementing value from 0 - 2.25M, while ref_coord_lat & ref_coord_long hold a set of latitude and longitude coordinates in decimal degrees for a point, respectively.

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

This is a read-only table. No additional rows will be added, and the only query which needs to be run against it is the following:

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

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

...where the values following the colons are PHP PDO placeholders. Essentially, the goal of this query is to fetch all coordinate points in the table that are currently in the viewport of a Google Maps window which is bounded by the 4 coordinates in the query.

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

I've limited the zoom level where this query is run with the Google Maps API, so that the maximum amount of rows that can be fetched is ~5600. As the zoom level increases, the resultant fetch total decreases significantly.

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

Running such an example query directly in PHPMyAdmin takes 1.40-1.45 seconds. This is far too long. I'm already running standard indices on ref_coord_lat and ref_coord_long which brought the query time down from ~5 seconds, but this is still much too large for a map where an end user expects a timely response.

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

My question is simply: How can I further optimize this table/query to increase the speed at which results are fetched?

推荐答案

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

Creating compound index on (lat, long) should help a lot.

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

However, right solution is to take a look at MySQL spatial extensions. Spatial support was specifically created to deal with two-dimensional data and queries against such data. If you create appropriate spatial indexes, your typical query performance should easily exceed performance of compound index on (lat, long).

这篇关于在具有〜225万行的单个表上进行选择查询的优化技术?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！