MySQL 性能

Question

最近，我在缓存到内存缓存之前的查询一直需要很长时间才能处理!在这个例子中，它花费了 10 秒。在这种情况下，我要做的就是获得 10 个最近的点击。

我感觉它加载了所有 125,592 行然后只返回 10，对吗？

# User@Host: root[root] @ localhost []# Query_time: 10  Lock_time: 0  Rows_sent: 10  Rows_examined: 125592SELECT * FROM hits WHERE campaign_id = 30 ORDER BY id DESC LIMIT 10;

这是另一个慢查询:

# Time: 090214  5:00:40# User@Host: root[root] @ localhost []# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 128879SELECT count(DISTINCT(ip_address)) AS count_distinct_ip_address FROM `hits` WHERE (campaign_id = 30);

当运行查询 phpMyAdmin 时，需要 1.3395 秒。虽然只是执行 SELECT * FROM hits 只需要 0.0001 秒。我觉得很奇怪，返回所有匹配项比对它们进行排序花费的时间更少，或者只是我正在对它们进行排序？

对于那些想看我的表的人:

CREATE TABLE `hits` (  `id` int(11) unsigned NOT NULL auto_increment,  `hostname` varchar(255) NOT NULL,  `url` tinytext NOT NULL,  `user_agent` tinytext NOT NULL,  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,  `ip_address` varchar(15) NOT NULL,  `campaign_id` int(11) NOT NULL,  PRIMARY KEY  (`id`),  KEY `campaign_id` (`campaign_id`),  KEY `ip_address` (`ip_address`));

Answer 1

您的 campaign_id 索引似乎选择性较低，我。 e.有很多记录有这个值。

订购这么多记录需要很多时间。

尝试在 PRIMARY KEY 上使用 INDEX SCAN 进行排序:

/* Edited, as MySQL does not use live feed from the derived source with ORDER BY */
SELECT *
FROM hits
WHERE IFNULL(campaign_id, campaing_id) = 30
ORDER BY id DESC
LIMIT 10;

至于您的第二个查询，可以做的事情并不多，因为无论如何您都需要对整个 campaign_id = 30 进行完整扫描，无论是 TABLE SCAN 还是索引扫描。

事实上，TABLE SCAN 可以更快:

SELECT count(DISTINCT(ip_address)) AS count_distinct_ip_address
FROM `hits`
WHERE IFNULL(campaign_id, campaign_id)  = 30;

如果不是，您可以在 (campaign_id, ip_address) 上创建一个索引，并使用技巧在该索引上模仿 INDEX GROUP BY:

CREATE INDEX ix_hits_campaign_ip ON hits(campaign_id, ip_address)

SELECT SUM(cnt)
FROM (
SELECT CASE WHEN @r = ip_address THEN 0 ELSE 1 END AS cnt,
  @r := ip_address
FROM
  (SELECT @r:='') r,
  (
  SELECT ip_address
  FROM hits
  WHERE campaign_id = 30
  ORDER BY ip_address
  ) i
) o

这里的技巧很简单:我们不需要结果，只需要计数，因此不需要扫描实际值。索引扫描就足够了。

不幸的是，尽管 MySQL 文档怎么说 here在松散索引扫描中，它们实际上不适用于复合索引。这就是为什么我们需要模仿 INDEX SCAN WITH GROUP BY。

我们通过强制 MySQL 使用 INDEX RANGE SCAN 检索所有具有 campaign_id = 30 且按 ip_address 排序的记录来做到这一点。然后我们使用在第一个子查询中初始化为空字符串的 session 变量 @r 计算 DISTINCT ip_address。

在第一个字段中，当前一个ip_address(存储在变量中)等于当前值时，我们将变量设置为0；否则我们将其设置为 1。在第二个字段中，我们将 ip_address 的当前值分配给变量。

最后，我们在第一个字段中检索 SUM，这当然会给我们 COUNT (DISTINCT ip_address)。