Mysql查询：文件排序时内部连接，限制和排序

我试图优化这个查询：

SELECT articles.id 
FROM articles 
INNER JOIN articles_authors ON articles.id=articles_authors.fk_Articles 
WHERE articles_authors.fk_Authors=586 
ORDER BY articles.publicationDate LIMIT 0,50;

表文章：

引擎：MyISAM

Row_format：动态

行数：1 482 588

数据长度：788 926 672

最大数据长度：281 474 976 710 655

索引长度：127 300 608

数据免费：0

校验和：空

    CREATE TABLE `articles` (
      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL,
    `publicationDate` DATE NOT NULL DEFAULT '1970-01-01',
    PRIMARY KEY (`id`),
    KEY `publicationDate` (`publicationDate`)
    ) ENGINE=MYISAM AUTO_INCREMENT=1498496 DEFAULT CHARSET=utf8 

表articles_authors：

引擎：MyISAM

Row_format：动态

行数：1 970 750

Data_length：45 008 420

最大数据长度：281 474 976 710 655

索引长度：127 300 608

数据免费：0

校验和：空

    CREATE TABLE `articles_authors` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `fk_Articles` int(10) unsigned NOT NULL,
    `fk_Authors` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `fk_Articles_fk_Authors` (`fk_Articles`,`fk_Authors`),
    KEY `fk_Articles` (`fk_Articles`),
    KEY `fk_Authors` (`fk_Authors`),
    ) ENGINE=MyISAM AUTO_INCREMENT=2349047 DEFAULT CHARSET=utf8 

查询解释：

id (1), select_type(SIMPLE), TABLE(articles_authors), TYPE(ref), possible_keys(fk_Articles_fk_Authors, fk_Articles, fk_Authors), KEY (fk_Authors), Key_len(4), ref(const), ROWS(171568), extra (USING TEMPORARY; USING FILE sort)
id (1), select_type(SIMPLE), TABLE(articles), TYPE(eq_ref), possible_keys(PRIMARY), KEY (PRIMARY), Key_len(4), ref(articles_authors.fk_Authors), ROWS(1), extra ()

正如你所看到的，SQL查询不是最优化的（在解释中使用文件排序）。

谢谢你的帮助！

---

它正在使用索引，就像它在解释中所说的那样。

id (1), select_type(SIMPLE), TABLE(articles_authors), TYPE(ref),
possible_keys(fk_Articles_fk_Authors, fk_Articles, fk_Authors),
KEY (fk_Authors), Key_len(4) , ref(const), ROWS(171568),
extra (USING TEMPORARY; USING FILE sort) , ref(const), ROWS(171568),
extra (USING TEMPORARY; USING FILE sort)

只作为它选择的50行的额外数据 ，而不是按发布日期的顺序执行文件。
它创建了一个包含50个项目的临时表。 然后它与桌面排序。
这必须以这种方式完成，因为MySQL不能在那些孤独的50个项目上使用大型索引，IO访问时间会花费很多成本。

在内存中排序50个数字然后访问磁盘上的索引会更快。

你可以做一些事情来加速查询：

optimize table articles, articles_authors

并重新运行查询。

编辑：通过反规范化表文章加快建议

如果你重写这样的查询：

SELECT articles.id FROM articles WHERE articles.id IN (
  SELECT articles_authors.fk_articles WHERE articles_authors.fk_authors = 586 
  LIMIT 0,50
)
ORDER BY articles.publicationDate;

您可能会看到相同的性能，但会突出显示问题。 如果作者586有180,000篇文章，那么MySQL必须从articles_authors中的180k中搜索50个项目，然后在订单表中再次从180k中搜索50项。

如果您合并表articles_authors和文章，您的表文章将被非规范化（假设文章可以有多个作者），但您不必进行连接并为第二个搜索节省自己。

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `publicationDate` date NOT NULL DEFAULT '1970-01-01',
  `title` varchar(255) NOT NULL,
  `fk_Authors` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `Articles_fk_Authors` (`id`,`fk_Authors`),
KEY `fk_Authors` (`fk_Authors`),
KEY `publicationDate` (`publicationDate`)
) ENGINE=MyISAM AUTO_INCREMENT=2349047 DEFAULT CHARSET=utf8 

现在你可以像这样选择它

SELECT articles.id FROM articles WHERE articles.Author = 586 
ORDER BY articles.publicationDate LIMIT 50,0

---

也许这会帮助你：

SELECT articles.id 
    FROM articles 
        INNER JOIN (SELECT fk_Articles FROM articles_authors WHERE articles_authors.fk_Authors=586) sub ON articles.id=sub.fk_Articles 
ORDER BY articles.publicationDate LIMIT 0,50;

---

SELECT articles.id 
FROM articles 
INNER JOIN articles_authors ON articles.id=articles_authors.fk_Articles 
WHERE articles.id=586 
ORDER BY articles.publicationDate LIMIT 0,50;

链接地址: http://www.djcxy.com/p/63887.html

上一篇: Mysql query : file sort when inner join, limit and order by

下一篇: How does QVideoFilterRunnable and OpenGL Context works together?