如何进一步优化这个MySQL表的单个查询

问题描述：

我有一个InnoDB MySql地理标识表，有大约100万行。该表的结构是这样的：如何进一步优化这个MySQL表的单个查询

CREATE TABLE `geoid` (
    `start_ip` int(11) NOT NULL, 
    `end_ip` int(11) NOT NULL, 
    `city` varchar(64) NOT NULL, 
    `region` char(2) NOT NULL, 
    PRIMARY KEY (`start_ip`,`end_ip`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

只会有一种类型的查询运行对这个表：

SELECT city, region FROM geoid WHERE 1259650516 BETWEEN start_ip AND end_ip

这个查询约需〜0.4228秒，这是不超慢，但不是快得令人难以置信醚。

我的问题是：如何进一步优化我的表，这个单一的查询？

我曾尝试以下的事情：

改变存储引擎MyISAM数据，本作的查询需要大约1.9秒。
使用WHERE语句'WHERE geoid.start_ip < = 1259650516 AND 1259650516 < = geoid.end_ip'。但这需要约0.5秒来执行，而不是.4 ish。

我已经从表中删除了所有无用的行，使它变小。我需要所有100万行。

UPDATE /解决方案

由于下面的文章，这里是我做过什么来解决这个问题。（只是为了完成这个答案对于任何人感兴趣）

我添加了一个新的列上表：

ALTER TABLE `geoid` ADD `geoip` LINESTRING NOT NULL

然后，我从start_ip地理数据填充新列和起点ip-终点

GeomFromText(CONCAT('LINESTRING(', start_ip, ' -1, ', end_ip, ' 1)'))

我然后创建空间索引对新列

CREATE SPATIAL INDEX geoip_index ON geoid(geoip);

从那里，所有的你必须做的是改变你的查询为：

SELECT city, region FROM geoid WHERE MBRContains(geoip, GeomFromText(CONCAT('POINT(', 1259650516, ' 0)')));

和你的完成。这将查询从.42秒降至.0003秒!!!!!!!

我喜欢这个INDEX。谢谢。希望能帮助到你。

您在'InnoDB'表上创建了一个'SPATIAL'索引？ – Quassnoi

我先将它转换为MyISAM。 – RonSper

答

尝试在end_ip上添加索引。在某些情况下，这应该使查询快两倍。

对于更好的性能，您需要使用SPATIAL索引，如this article中所述。

除了主要？像：PRIMARY KEY（'start_ip'，'end_ip'）， KEY'start_ip'（'start_ip'）， KEY'end_ip'（'end_ip'）。这并没有改变任何事情。 – RonSper

@Ron的Sper：那么它不会加快在所有情况下的查询，并只给在其他情况下，一个相对较小的速度了。如果你想要更好的表现，你可能需要考虑一个SPATIAL索引。看到这篇文章：http://explainextended.com/2009/09/29/adjacency-list-vs-nested-sets-mysql/。但它不适合初学者。 –

WOW，那是完美的。这正是我想要的。谢谢。 – RonSper

答

尝试在查询中包含的所有字段上创建索引。在这个特殊情况下在两个字段（start_ip和end_ip）上创建一个索引

如何进一步优化这个MySQL表的单个查询

相关推荐