php - MySQL - 极慢的 SQL 查询

Question

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

我已经做了很多测试来找到薄弱环节和(例如:使用标准 HTML 页面测试 nginx/php-fpm，包括 php，使用 memcached session )这一切都很好......它的数据库这就是问题所在。

我在下面有 2 个查询 - 第一个只是一个测试，它运行良好/快速 - 如果在 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-fpm 配置。

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

---

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

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

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

一种(可能)更好的方法 I have discussed previously (虽然它显然让一些人大吃一惊，因为它有点“开箱即用”)是使用 spatial extensions 构建 R-Tree 索引。在 MySQL 中。

另见:

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