MySQL查询速度慢。我应该索引什么？ [英] Slow MySQL query. What should I index?

问题描述

每次保存页面编辑时，PHPWiki都会有5秒的慢速查询。经常在mysql-slow.log中捕获的查询是：

  INSERT INTO wikiscore 
 SELECT w1.topage ，COUNT（*）
来自wikilinks AS w1，wikilinks AS w2 
 WHERE w2.topage = w1.frompage 
 GROUP BY w1.topage; 
  

当前指数如下：

 表wikilinks在frompage和topage上有一个主索引
表wikiscore在pagename和score上有一个主索引
  

我如何重新构造SELECT查询以更快地返回相同的结果？我怎么能改变索引，这样查询会更快？我的想法是它可能是OVER-indexed？

我只计算了查询的SELECT部分​​的结果，单独需要1-2秒。 INSERT必须占用剩余的时间。

保存我想消除的页面时存在延迟。由于已经完成了大量的修改，我没有选择升级到另一个wiki引擎（或PHPwiki的版本）。

任何想法？

编辑---

查询的SELECT部分​​的EXPLAIN结果为：

  SIMPLE 
 w2 
 index 
 PRIMARY 
 204 
 31871 
使用索引;使用临时;使用filesort 
 
 SIMPLE 
 w1 
 ref 
 PRIMARY 
 PRIMARY 
 102 
 phpwiki.w2.topage 
 14 
使用索引
  

解决方案

表wikilinks在frompage和topage上有一个主要索引

  WHERE w2.topage = w1.frompage 
  

无法通过上述复合索引搜索此条件。 / p>

更改订单（在 topage上创建索引，frompage ）或在 topage 。

P. S.问题的根源在于系统中每个页面的排名都会随着每次编辑而更新。

这个排名系统对我来说有点奇怪：它会计算链接的链接，而不是链接本身。

如果1000页链接到莫斯科且仅莫斯科链接到 Beket池，然后池塘将获得1000点，莫斯科将完全没有积分，尽管每个人都知道莫斯科而且没有池塘。

我认为这不是你的意思。最有可能它应该是这样的：

  INSERT INTO 
 wikiscore 
 SELECT 
链接。 topage，COUNT（*）AS cnt 
 FROM wikilinks current，wikilinks linked 
 WHERE current.frompage=@current_page 
 AND linked.topage = current.topage 
 GROUP BY 
 linked.topage 
 ON DUPLICATE KEY UPDATE 
得分= cnt; 
  

这将汇总当前页面引用的所有页面的所有链接，这似乎是你想要的。

你需要在 PRIMARY KEY 得分在这种情况下，$ c>在 wikiscore 上，但无论如何我认为没有任何意义。

如果你想要加快排名查询，你创建这样的索引：

  ALTER TABLE wikilinks ADD CONSTRAINT pk_wikilinkes_fromto PRIMARY KEY（frompage，topage） ; 
 
 CREATE INDEX ix_wikilinks_topage ON wikilinks（topage）; 
 
 ALTER TABLE wikiscore ADD CONSTRAINT pk_wikiscore_pagename PRIMARY KEY（pagename）; 
 
 CREATE INDEX ix_wikiscore_score ON wikiscore（得分）; 
  

PHPWiki has a 5 second slow query each time you save a page edit. The query caught often in the "mysql-slow.log" is:

INSERT INTO wikiscore 
SELECT w1.topage, COUNT(*) 
FROM wikilinks AS w1, wikilinks AS w2 
WHERE w2.topage=w1.frompage 
GROUP BY w1.topage;

The current indexes are as follows:

table "wikilinks" has a primary index on "frompage" and "topage" 
table "wikiscore" has a primary index on "pagename" and "score"

How could I reformulate the SELECT query to return the same results faster? How could I change the indexes so this query would be faster? My thought is it might be OVER-indexed?

I've timed the result of the SELECT part of the query only and it takes 1-2 seconds alone. The INSERT must take up the rest of that time.

There is a lag when saving pages that I would like to eliminate. I do not have the option of upgrading to another wiki engine (or version of PHPwiki) due to the amount of modifications that have been done.

Any ideas?

edit---

The results of "EXPLAIN" on the SELECT part of the query were:

SIMPLE
w2
index
PRIMARY
204
31871   
Using index; Using temporary; Using filesort

SIMPLE
w1
ref
PRIMARY
PRIMARY
102 
phpwiki.w2.topage   
14
Using index

解决方案

table "wikilinks" has a primary index on "frompage" and "topage"

WHERE w2.topage=w1.frompage

This condition cannot be searched over the composite index described above.

Either change order (create an index on topage, frompage) or create an additional index on topage.

P. S. The root of them problem is that the ranks of each and every page in the system are updated with every edit.

This ranking system seems a little bit weird to me: it counts link to links, not the links themselves.

If 1000 pages link to Moscow and only Moscow links to Beket pond, then the pond will get 1000 points and Moscow will get no points at all, though everyone knows of Moscow and none of the pond.

I think it's not what you meant. Most probably it should look like that:

INSERT INTO
       wikiscore 
SELECT
       linked.topage, COUNT(*) AS cnt
FROM   wikilinks current, wikilinks linked
WHERE  current.frompage=@current_page
       AND linked.topage = current.topage
GROUP BY
       linked.topage
ON DUPLICATE KEY UPDATE
       score = cnt;

This will sum all links to all pages referenced from the current page, that seems to be what you want.

You will need to get rid of score in PRIMARY KEY on wikiscore in this case, but I see no point in putting it there anyway.

If you want to speed up ranking queries, you create indices like that:

ALTER TABLE wikilinks ADD CONSTRAINT pk_wikilinkes_fromto PRIMARY KEY (frompage, topage);

CREATE INDEX ix_wikilinks_topage ON wikilinks (topage);

ALTER TABLE wikiscore ADD CONSTRAINT pk_wikiscore_pagename PRIMARY KEY (pagename);

CREATE INDEX ix_wikiscore_score ON wikiscore (score);

这篇关于MySQL查询速度慢。我应该索引什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！