MySQL性能

最近我在缓存到memcache之前的查询一直在处理!在这个例子中,花了10秒钟.我想要做的就是在这种情况下获得最近的10次点击.

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

# User@Host: root[root] @ localhost []
# Query_time: 10  Lock_time: 0  Rows_sent: 10  Rows_examined: 125592
SELECT * 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: 128879
SELECT 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`)
);

Quassnoi.. 5

看起来您的campaign_id索引具有低选择性,即有很多具有此值的记录.

订购这么多记录需要花费很多时间.

尝试使用INDEX SCAN上PRIMARY KEY的排序:

/* 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或INDEX 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文档在松散的索引扫描中说这些,但它们实际上并不适用于复合索引.这就是我们需要模仿的原因INDEX SCAN WITH GROUP BY.

我们通过强制MySQL使用INDEX RANGE SCAN它来检索campaign_id = 30排序依据的所有记录ip_address.然后我们DISTINCT ip_address使用@r在第一个子查询中初始化为空字符串的会话变量来计算.

在第一个字段中,我们将变量设置为0前一个ip_address(存储在变量中)等于当前变量; 否则我们将其设置为1.在第二个字段中,我们将当前值ip_address赋给变量.

最后,我们检索SUM第一个字段,这当然会给我们COUNT (DISTINCT ip_address).

看起来您的campaign_id索引具有低选择性,即有很多具有此值的记录.

订购这么多记录需要花费很多时间.

尝试使用INDEX SCAN上PRIMARY KEY的排序:

/* 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或INDEX 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文档在松散的索引扫描中说这些,但它们实际上并不适用于复合索引.这就是我们需要模仿的原因INDEX SCAN WITH GROUP BY.

我们通过强制MySQL使用INDEX RANGE SCAN它来检索campaign_id = 30排序依据的所有记录ip_address.然后我们DISTINCT ip_address使用@r在第一个子查询中初始化为空字符串的会话变量来计算.

在第一个字段中,我们将变量设置为0前一个ip_address(存储在变量中)等于当前变量; 否则我们将其设置为1.在第二个字段中,我们将当前值ip_address赋给变量.

最后,我们检索SUM第一个字段,这当然会给我们COUNT (DISTINCT ip_address).