MySQL -非常慢的SQL查询

Question

我正在使用包围测试一个新站点的速度，我发现它只能使用一个AWS RDS小实例-小型数据库每秒处理大约30个并发连接。(我尝试了更大的数据库，获得了更多的连接，但它仍然非常低)。

我做了很多测试来找出薄弱的链接(例如:使用std HTML页面测试nginx/ php -fpm，包括php和memcached会话)，这些都很好.问题在于数据库。

下面有两个查询--第一个只是一个测试，它工作得很好/快速--如果在20秒内运行100个并发连接，我可以得到3500次点击：

  $database_users = new database('dbname');
  $sql='SELECT COUNT(userid) as yes FROM login;';
  $pds=$database_users->pdo->prepare($sql); $pds->execute(array()); $row=$pds->fetch();
  echo $row['yes'];

然而，下面的查询很慢，只有大约70次点击--这是我使用的查询：

      $database_users = new database('dbname');
      $sql='SELECT a.countryCode FROM geoCountry AS a LEFT JOIN geoIPv4 AS b ON a.pid=b.geoCountry_pid WHERE \'2091528364\' BETWEEN startipNum AND endipNum;';
      $pds=$database_users->pdo->prepare($sql); $pds->execute(array()); $row=$pds->fetch();
      echo $row['countryCode'];

当我使用远程查询工具时，第一个查询在0.1秒内运行，第二个查询在0.3秒内运行。

我想弄明白为什么我会在第二次比赛中表现这么差。不会让php/database只等待查询完成，然后再响应。只有0.2秒。

我可以发送其他细节，如果需要，如php配置。

任何建议都将不胜感激-谢谢

CREATE TABLE `geoCountry` (
  `pid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `countryCode` char(2) NOT NULL COMMENT 'Country Code',
  `zipEnabled` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1=Has Zip Codes, 0=No Zip Codes',
  `english` varchar(75) NOT NULL COMMENT 'Language',
  `indonesian` varchar(75) NOT NULL COMMENT 'Language',
  `japanese` varchar(75) NOT NULL COMMENT 'Language',
  PRIMARY KEY (`pid`),
  UNIQUE KEY `countryCode` (`countryCode`),
  KEY `zipEnabled` (`zipEnabled`),
  CONSTRAINT `geoCountry_zipEnabled` FOREIGN KEY (`zipEnabled`) REFERENCES `xfk_generic_binary` (`binary`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=249 DEFAULT CHARSET=utf8 COMMENT='Country Codes linked to Country Names'


CREATE TABLE `geoIPv4` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `geoCountry_pid` tinyint(3) unsigned NOT NULL COMMENT 'geoCountry Pid',
  `startipNum` int(10) unsigned NOT NULL COMMENT 'Start IP Address',
  `endipNum` int(10) unsigned NOT NULL COMMENT 'End IP Address',
  PRIMARY KEY (`pid`),
  KEY `geoCountry_pid` (`geoCountry_pid`),
  CONSTRAINT `geoIPv4_geoCountry_pid` FOREIGN KEY (`geoCountry_pid`) REFERENCES `geoCountry` (`pid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=148890 DEFAULT CHARSET=utf8 COMMENT='IPv4 Ranges linked to Country Codes';

*它的php-fpm是否可能不等待回复回来，或者与围城的工作方式有关？注意:如果并发连接的数量很低，seige似乎可以正常工作。

Answer 1

查询的根本问题是，即使使用索引on (startipNum，endipNum)，B树索引也不是查找值BETWEEN的最佳结构，因为startipNum <=中的每一行都是候选匹配，而endipNum被索引这一事实并没有真正帮助任何事情，因为每个有效startipNum的每个endipNum都必须进行比较，尽管(至少与MaxMind数据库，这大概就是您正在使用的)只有一个匹配行。

您可以通过将LIMIT 1添加到末尾来优化查询，因为您知道只有一个匹配行。一旦找到匹配的行，服务器就会停止查找。我还发现，添加相反的索引(endipNum，startipNum)也会让优化器选择这两个索引中哪一个对任何给定的查询最有效。

一种(潜在的)更好的方法是使用我之前已经讨论过了中的空间扩展构建一个R-树索引(虽然它显然让一些人感到震惊，因为它有点“不受欢迎”)。

另请参阅：

http://blog.jcole.us/2007/11/24/on-efficiently-geo-referencing-ips-with-maxmind-geoip-and-mysql-gis/

Answer 2

也许是我，但是在这种情况下使用LEFT JOIN对我来说是没有意义的。

您的查询应该如下所示

SELECT a.countryCode 
  FROM geoCountry a JOIN geoIPv4 b 
    ON a.pid = b.geoCountry_pid 
 WHERE 2091528364 BETWEEN startipNum AND endipNum

确保在(startipNum, endipNum)上有一个覆盖索引

CREATE INDEX idx_startipNum_endipNum ON geoIPv4 (startipNum, endipNum);