代码之家 › 专栏 › 技术社区 › Alex

包含350万个条目的数据库表-如何提高性能?

mysql

Alex · 技术社区 · 16 年前

我们有一个mysql表,大约有350万个IP条目。

结构:

CREATE TABLE IF NOT EXISTS `geoip_blocks` (
  `uid` int(11) NOT NULL auto_increment,
  `pid` int(11) NOT NULL,
  `startipnum` int(12) unsigned NOT NULL,
  `endipnum` int(12) unsigned NOT NULL,
  `locid` int(11) NOT NULL,
  PRIMARY KEY  (`uid`),
  KEY `startipnum` (`startipnum`),
  KEY `endipnum` (`endipnum`)
) TYPE=MyISAM  AUTO_INCREMENT=3538967 ;

问题:查询需要3秒钟以上。

SELECT uid FROM `geoip_blocks` WHERE 1406658569 BETWEEN geoip_blocks.startipnum AND geoip_blocks.endipnum LIMIT 1

-大约3秒

SELECT uid FROM `geoip_blocks` WHERE startipnum < 1406658569 and endipnum > 1406658569 limit 1

-没有增益,大约3秒

如何改进?

6 回复 | 直到 16 年前

Will Hartung 16 年前

解决方法是获取btree/isam库并使用它(如Berkelydb)。使用isam这是一项微不足道的任务。

使用isam,您可以将起始键设置为数字,执行“查找下一个”,(查找大于或等于您的数字的块),如果不等于,您将“查找上一个”并检查该块。3-4次磁盘命中,shazam,瞬间完成。

好吧,这是个解决办法。

这里发生的问题是,没有“足够智能的优化器”的SQL在这种查询上做得很糟糕。

例如,您的查询:

SELECT uid FROM `geoip_blocks` WHERE startipnum < 1406658569 and endipnum > 1406658569 limit 1

它将“查看”所有“小于”1406658569的行。所有的行,然后扫描它们,寻找所有符合第二个条件的行。

对于一个3.5米的行表,假设“平均”(也就是说它在中间),欢迎使用1.75米的行表扫描。更糟的是,索引表扫描。理想情况下,MySQL将“放弃”和“只是”表扫描,因为它更快。

显然,这不是你想要的。

@Andomar的解决方案基本上是通过“网络”标准强制您“阻塞”到数据空间。有效地把你的桌子破成255块。因此,不扫描1.75米的行,而是扫描6800行,这是一个显著的改进,代价是打破网络边界上的障碍。

SQL中的范围查询没有任何错误。

SELECT * FROM table WHERE id between X and Y

通常是一个快速查询,因为优化器可以很容易地使用索引分隔行的范围。

但是,这不是您的查询,因为在本例中,您的范围不是主ID(startipnum)。

如果您“知道”您的块大小在某个范围内(例如,您的块中没有一个块的IP超过1000),则可以通过添加“where startipnum between ipnum-1000 and ipnum+1000”来阻止查询。这与提议的网络阻塞没有什么不同,但是这里您不必维护太多。当然,您可以通过以下方式学习:

SELECT max(endipnum - startipnum) FROM table

了解您的最大范围是什么。

另一个选择,我已经看到,从来没有使用过,但实际上,嗯,这是完美的,是看看 MySql's Spatial Extensions 因为这就是事实。

这是专为地理信息系统应用而设计的,但是你在搜索范围内的东西,这是地理信息系统应用所做的很多工作。所以,这可能也是一个解决方案。

jishi 16 年前

startip和endip应该是一个组合索引。MySQL不能在一个查询中对同一个表使用多个索引。

我不确定语法,但有点像

键(startipnum,endipnum)

Andomar 16 年前

看起来您正在尝试查找IP地址所属的范围。问题是MySQL不能充分利用索引进行中间操作。索引在使用=操作时效果更好。

向查询中添加=操作的一种方法是 network part of the address 在桌子旁边。例如:

numeric 1406658569
ip address 83.215.232.9
class A with 8 bit network part
network part = 83

索引打开时 (networkpart, startipnum, endipnum, uid) 这样的查询将变得非常快:

SELECT  uid 
FROM    `geoip_blocks` 
WHERE   networkpart = 83
        AND 1406658569 BETWEEN startipnum AND endipnum

如果一个geoip块跨越多个网络类,则必须将其拆分为每个网络类一行。

lstntjss 16 年前

根据您问题中的信息,我假设您所做的是从MaxMind®实现geoip®产品。我下载了免费版本的geoip®数据,将其加载到一个mysql数据库中,并做了一些快速的实验。

使用startipnum上的索引,查询执行时间从0.15到0.25秒不等。在startipnum和endipnum上创建复合索引不会更改查询性能。这让我相信您的性能问题是由于硬件不足、MySQL调优不当或两者兼而有之。我在其上运行测试的服务器有8g RAM,这远远超过了获得与索引文件仅28m相同的性能所需的内存。

我的建议是以下两个选项之一。

花些时间优化您的MySQL服务器。MySQL在线文档将是一个合理的起点。 http://dev.mysql.com/doc/refman/5.0/en/optimizing-the-server.html 如果MySQL文档不够的话,互联网搜索会产生大量的书籍、论坛、文章等。
如果我的假设是正确的,您使用的是geoip®产品,那么第二个选项是使用由maxmind®提供的二进制文件格式。自定义文件格式已针对速度、内存使用和数据库大小进行了优化。为多种语言提供访问数据的API。 http://www.maxmind.com/app/api

顺便说一句,您提出的两个查询是不等价的。中间运算符包含在内。第二个查询将需要使用<=>=运算符来等效于使用between运算符的查询。

Daniel Schneller 16 年前

也许您想看看对表进行分区。这项功能从MySQL5.1开始就可用了,因此您没有指定要使用的版本,如果您坚持使用旧版本,这可能对您不起作用。

由于已知IP地址的可能值范围(至少对于IPv4),您可以将表分解为多个大小相同的分区(如果数据分布不均匀,甚至可能不相等)。有了这个MySQL,可以很容易地跳过表的大部分,如果仍然需要的话,可以加快扫描速度。

参见 MySQL manual on partitioning 以获取可用的选项和语法。

Alex 16 年前

谢谢你的评论,我真的很感激。

目前,我们最终使用了缓存机制,并减少了昂贵的查询。