MySQL:从MyISAM转换为InnoDB可以使查询更快...? [英] MySQL: Converting from MyISAM to InnoDB makes queries faster...?

问题描述

我有一个表，其中包含3,000,000行，其中包含IP范围及其相应的国家，地区(省)和城市.该表位于MyISAM引擎中.

I had a table with 3,000,000 rows which has IP ranges and their corresponding country, region(province) and city. The table was in MyISAM engine.

我编写了一个脚本，以按用户的IP提取用户的地理位置，其中一些查询最多需要17秒的时间.然后，我将表转换为InnoDB并重试了查询过程，其中一些已知的耗时的问题查询在性能上已得到显着改善，而仅需0.02秒即可.尽管仍然需要2到3秒钟才能完成查询，但是在MyISAM引擎上运行时，查找大约1000个IP地址所花费的全部时间大约是以前的一半.

I wrote a script to fetch the geographic location for users by their IP and some of the queries took up to 17sec. I then converted the table to InnoDB and retried the querying process, some of the problematic queries known to be time consuming before has been improved in performance significantly, taking as short as 0.02 seconds. Although there still were queries took 2 or 3 seconds to complete, the over all time consumed by the look up on about 1000 IP address took around half the time as before when ran on MyISAM engine.

我在线比较了MyISAM和InnoDB，但我阅读的所有文章都认为，在SELECT查询中，MyISAM比InnoDB快.但是，根据我所看到的，这不是我的桌子的情况.有人有任何理论为什么吗?

I did a search online comparing MyISAM and InnoDB, but all of the articles I read judged MyISAM being faster than InnoDB on SELECT queries. However, according to what I have seen this is not the case for my table. Does anyone has any theories why?

P.S，我的表没有FK约束，没有PK，通过将IP与每一行的'ip_start'和'ip_end'列进行比较来完成查找.如果IP在范围内，则该行将匹配.以下是我的脚本中的示例查询:

P.S, My table has no FK constraints, no PK, look up is done by comparing the IP with the 'ip_start' and 'ip_end' columns of each row. The row would match if the IP is within the range. Following is an example query that's in my script:

SELECT country, region, city FROM ip_location WHERE ip_start<=1177798832 AND ip_end>=1177798832 LIMIT 1;

推荐答案

检查两个引擎的配置.与设置MyISAM的方式相比，InnoDB引擎的配置可能对您的搜索更有效.

Check the configuration of the two engines. It is possible that the config of your InnoDB engine is more efficient for your searches than the way you have MyISAM set up.

关键配置值是innodb_buffer_pool_size，这是InnoDB缓存数据(和索引，但是没有任何索引)的地方.

A key config values is innodb_buffer_pool_size, which is where InnoDB caches data (and indexes, but you don't have any indexes).

这篇关于MySQL:从MyISAM转换为InnoDB可以使查询更快...?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！