优化 SQL 查询

Question

我有一个优化这个查询的问题：

SET @SEARCH = "dokumentalne";
SELECT SQL_NO_CACHE
`AA`.`version` AS `版本`, 
`AA`.`contents` AS `内容`, 
`AA`.`idarticle` AS `SQL地址`, 
`AA` .`topic` AS `主题`,
MATCH (`AA`.`topic` , `AA`.`contents`) AGAINST (@SEARCH) AS `相关度`, 
`IA`.`url` AS `URL`
FROM `xv_article` AS `AA`
INNER JOIN `xv_articleindex` AS `IA` ON ( `AA`.`idarticle` = `IA`.`adressinsql` )
INNER JOIN (
    SELECT `idarticle` , MAX( `version` ) AS `版本`
    FROM `xv_article`
    WHERE MATCH (`topic` , `contents`) AGAINST (@SEARCH)
    GROUP BY `idarticle`
) AS `MG`
ON ( `AA`.`idarticle` = `MG`.`idarticle` ) 
WHERE `IA`.`accepted` = "yes"
AND `AA`.`version` = `MG`.`version`
ORDER BY `相关度` DESC
LIMIT 0 , 30

现在，这个查询需要 ^ 20 秒。如何进行优化？

EXPLAIN 的结果如下：

1  PRIMARY  AA  ALL  NULL  NULL  NULL  NULL  11169  Using temporary; Using filesort
1  PRIMARY    ALL  NULL  NULL  NULL  NULL  681  Using where
1  PRIMARY  IA  ALL  accepted  NULL  NULL  NULL  11967  Using where
2  DERIVED  xv_article  fulltext  topic  topic  0     1  Using where; Using temporary; Using filesort

这是我的数据示例服务器：

用户：bordeux_4prog
密码：4prog
phpMyAdmin：http://phpmyadmin.bordeux.net/
Chive：http://chive.bordeux.net/

- bordeux

---

你真的希望这些细节在互联网上公开展示供所有人看吗？ - John Parker

为SO群体设置测试服务器，点赞（+1）。 - too

你知道只有在想将MySQL语句用作变量或表格等时（例如“order”），才需要使用 `backtags` 吗？在我看来，用 `s 来输入所有内容看起来非常烦人。 - Alec

内部带有group by子句的查询可能非常昂贵。你能解释一下你想做什么吗？此外，请更改您的服务器密码。 - O. Jones

这些数据是开放许可的；D - bordeux

Answer 1

看起来你的数据库崩了。消除内部查询是优化的关键部分。请尝试这个（未经测试）查询：

SET @SEARCH = "dokumentalne";

SELECT SQL_NO_CACHE
  aa.idarticle AS `AdressInSQL`,
  aa.contents AS `Contents`,
  aa.topic AS `Topic`,
  MATCH(aa.topic , aa.contents) AGAINST (@SEARCH) AS `Relevance`,
  ia.url AS `URL`,
  MAX(aa.version) AS `Version`
FROM
  xv_article AS aa,
  xv_articleindex AS ia
WHERE
  aa.idarticle = ia.adressinsql
  AND ia.accepted = "yes"
  AND MATCH(aa.topic , aa.contents) AGAINST (@SEARCH)
GROUP BY
  aa.idarticle,
  aa.contents,
  `Relevance`,
  ia.url
ORDER BY
  `Relevance` DESC
LIMIT
  0, 30

为了进一步优化您的查询，您还可以将获取最新版本的文章与全文搜索分开，因为后者是最昂贵的。这可以通过子查询来完成（也未在您的数据库上进行测试）：

SELECT SQL_NO_CACHE
  iq.idarticle AS `AdressInSQL`,
  iq.topic AS `Topic`,
  iq.contents AS `Contents`,
  iq.url AS `URL`,
  MATCH(iq.topic, iq.contents) AGAINST (@SEARCH) AS `Relevance`
FROM (
   SELECT
     a.idarticle,
     a.topic,
     a.contents,
     i.url,
     MAX(a.version) AS version
   FROM
     xv_article AS a,
     xv_articleindex AS i
   WHERE
     i.accepted = "yes"
     AND a.idarticle = i.adressinsql
   GROUP BY
     a.idarticle AS id,
     a.topic,
     a.contents,
     i.url
) AS iq
WHERE
  MATCH(iq.topic, iq.contents) AGAINST (@SEARCH)
ORDER BY
  `Relevance` DESC
LIMIT
  0, 30

- too

Answer 2

我在你的数据库中注意到的第一件事是，你没有在xv_articleindex.adressinsql上建立索引。添加它，这应该会显著提高查询性能。此外，一个表是MyISAM，而另一个表是InnoDb。使用一个引擎（通常情况下，我建议使用InnoDB）。

- a1ex07

---

我无法使用InnoDB，因为它没有全文搜索模块。 - bordeux