我有两个表：
CREATE TABLE `articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(1000) DEFAULT NULL,
`last_updated` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `last_updated` (`last_updated`),
) ENGINE=InnoDB AUTO_INCREMENT=799681 DEFAULT CHARSET=utf8
CREATE TABLE `article_categories` (
`article_id` int(11) NOT NULL DEFAULT '0',
`category_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`article_id`,`category_id`),
KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
这是我的查询：
SELECT a.*
FROM
articles AS a,
article_categories AS c
WHERE
a.id = c.article_id
AND c.category_id = 78
AND a.comment_cnt > 0
AND a.deleted = 0
ORDER BY a.last_updated
LIMIT 100, 20
和EXPLAIN吧：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: index
possible_keys: PRIMARY
key: last_updated
key_len: 9
ref: NULL
rows: 2040
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
type: eq_ref
possible_keys: PRIMARY,fandom_id
key: PRIMARY
key_len: 8
ref: db.a.id,const
rows: 1
Extra: Using index
它采用的全索引扫描last_updated上排序的第一个表，但加入不使用Y索引（ type: index中的解释）。 这是非常糟糕的性能和杀死整个数据库服务器，因为这是一个很常见的查询。
我试图扭转表的顺序与STRAIGHT_JOIN ，但是这给filesort, using_temporary ，这更是雪上加霜。
有没有什么办法，使对参加并在同一时间排序的MySQL使用索引？
===更新===
我在此真的绝望。 也许某种非规范化有助于解决这个问题？
Answer 1:
如果你有很多类别，此查询无法进行有效的。 没有一个单一的指标可以覆盖在一次两个表MySQL 。
你所要做的非规范化：添加last_updated ， has_comments和deleted到article_categories ：
CREATE TABLE `article_categories` (
`article_id` int(11) NOT NULL DEFAULT '0',
`category_id` int(11) NOT NULL DEFAULT '0',
`last_updated` timestamp NOT NULL,
`has_comments` boolean NOT NULL,
`deleted` boolean NOT NULL,
PRIMARY KEY (`article_id`,`category_id`),
KEY `category_id` (`category_id`),
KEY `ix_articlecategories_category_comments_deleted_updated` (category_id, has_comments, deleted, last_updated)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
并运行此查询：
SELECT *
FROM (
SELECT article_id
FROM article_categories
WHERE (category_id, has_comments, deleted) = (78, 1, 0)
ORDER BY
last_updated DESC
LIMIT 100, 20
) q
JOIN articles a
ON a.id = q.article_id
当然，你应该更新article_categories只要您在更新相关栏目以及article 。 这可以在触发器来实现。
请注意，列has_comments是布尔：这将允许使用相等谓词进行了索引的单个范围扫描。
还要注意的是， LIMIT进入子查询。 这使得MySQL使用它默认不使用后期行查找。 请参阅本文中我对为什么他们提高性能博客：
- MySQL的ORDER BY / LIMIT性能：迟到的行查找
如果你在SQL Server上，你可以让在你的查询，这实质上会使得一个非规范化的索引副本可索引视图article_categories与其他字段，由服务器自动mainained。
不幸的是， MySQL不支持这一点，你必须手动创建这样一个表，并编写额外的代码来与基表保持同步。
Answer 2:
让您的具体查询之前，必须了解索引是如何工作是很重要的。
有了适当的统计数据，这个查询：
select * from foo where bar = 'bar'
...将使用索引foo(bar)如果它是有选择性的。 这意味着，如果bar = 'bar'达选择最表的行，它会走得更快，只是读表并消除行不适用。 相反，如果bar = 'bar'是指只选择行屈指可数，读取索引是有意义的。
假设我们现在的订单子句中辗转反侧，你在每个所指标foo(bar)和foo(baz) ：
select * from foo where bar = 'bar' order by baz
如果bar = 'bar'是非常有选择性的，它的价格便宜，以抓住这符合所有的行，并把它们在内存中进行排序。 如果不是在所有的选择性，对指数foo(baz)没有什么意义，因为你无论如何读取整个表：使用这将意味着来回磁盘上的页面安排妥当，这是非常昂贵的读取行。
折腾的限制条款，然而， foo(baz)可能会突然意义：
select * from foo where bar = 'bar' order by baz limit 10
如果bar = 'bar'是非常有选择性的，它仍然是一个不错的选择。 如果不是在所有的选择，你会很快通过扫描索引找到10点相匹配的行foo(baz) -你可能会读10行，或50，但你会发现10个好的很快。
假设与指标，后者查询foo(bar, baz)和foo(baz, bar)代替。 索引是由左边读到右边。 做一个非常好的感觉这个潜在的查询，对方可能使没有。 想想看，这样的：
bar baz baz bar
--------- ---------
bad aaa aaa bad
bad bbb aaa bar
bar aaa bbb bad
bar bbb bbb bar
正如你所看到的，在指数foo(bar, baz)允许开始阅读在('bar', 'aaa')并从该点向前，以获取行。
该指数foo(baz, bar) ，相反，产生排序行baz不论什么bar会举行。 如果bar = 'bar'是不是在所有选择的标准，你很快就会碰到您的查询的匹配行，在这种情况下是有意义的使用它。 如果它是非常有选择性的，你可能会发现，以至于赛前迭代行gazillions bar = 'bar' -这可能仍然是一个不错的选择，但它是最佳的。
有了这样解决的，让我们回到你原来的查询......
你需要加入文章与类别，以过滤特定类别，与一个以上的评论文章，未删除，然后按日期排序，然后抓住他们了一把。
我认为，大多数文章都没有删除，所以对标准的指数不会有太大的用途 - 它只会减慢写入和查询规划。
我相信大多数文章的评论或更多，这样就不会被选择或者。 即有一点需要索引，要么。
没有你的类别过滤器，指数期权是相当明显的： articles(last_updated) ; 可能与评论数列的权利，并删除标志左侧。
有了您的类别过滤器，这一切都取决于...
如果您的类别过滤器是非常有选择性的，它实际上使得感非常好，选择是该类别中的所有行，在内存中对它们进行排序，并挑选顶级匹配行。
如果您的类别过滤器是不是在所有的选择性和产量几乎文章，对指数articles(last_update)是有道理的：有效行是所有的地方，所以，直到你找到足够的那场比赛就万事大吉了顺序读取行。
在更普遍的情况下，它只是隐约有选择性的。 据我所知，所收集的统计数据不考虑相关性很大。 因此，规划必须估计是否会找到合适的类别足够快的文章，是值得一读后者指数没有什么好办法。 加入并在内存通常会更便宜的排序，所以规划师去这一点。
无论如何，你两个选项来强制使用索引。
一是承认，查询规划是不完美的，并使用一个提示：
http://dev.mysql.com/doc/refman/5.5/en/index-hints.html
警惕不过，因为有时规划者实际上是正确的不想用你喜欢它或副版本的索引。 此外，也可以在MySQL的未来版本变成正确的，所以记住这一点你保持你的代码在过去几年。
编辑： STRAIGHT_JOIN ，由DRAP点出来的作品也有类似的警告。
另一种是维持一个额外的列标记经常选择的物品（例如一个TINYINT场，它们属于特定类别被设定为1），然后添加上如索引articles(cat_78, last_updated) 维护它使用触发器，你会做得很好。
Answer 3:
非覆盖索引的使用是昂贵的。 对于每一行，任何未覆盖列必须从基表中检索，使用主键。 所以我第一次尝试，使该指数articles覆盖。 这可能有助于说服MySQL查询优化器索引是有用的。 例如：
KEY IX_Articles_last_updated (last_updated, id, title, comment_cnt, deleted),
如果没有帮助，你可以玩弄FORCE INDEX ：
SELECT a.*
FROM article_categories AS c FORCE INDEX (IX_Articles_last_updated)
JOIN articles AS a FORCE INDEX (PRIMARY)
ON a.id = c.article_id
WHERE c.category_id = 78
AND a.comment_cnt > 0
AND a.deleted = 0
ORDER BY
a.last_updated
LIMIT 100, 20
索引执行主键的名称始终是“主”。
Answer 4:
您可以使用影响MySQL使用按键或指标
对于
对于额外的信息，请按照此链接 。 我打算用这个连接（即USE INDEX FOR JOIN (My_Index)但如预期没有奏效。卸下FOR JOIN部分加速了我的查询显著，从超过3.5小时，1-2秒。很简单，因为MySQL的被迫使用正确的索引。
Answer 5:
我想有以下指标可用
文章表 - INDEX（删除，LAST_UPDATED，comment_cnt）
article_categories表 - INDEX（article_id的，CATEGORY_ID） - 你已经有了这个指标
然后加入STRAIGHT_JOIN强制执行为上市而不是它试图通过使用article_categories表任何统计数据可能有帮助查询的查询。
SELECT STRAIGHT_JOIN
a.*
FROM
articles AS a
JOIN article_categories AS c
ON a.id = c.article_id
AND c.category_id = 78
WHERE
a.deleted = 0
AND a.comment_cnt > 0
ORDER BY
a.last_updated
LIMIT
100, 20
按照评论/反馈，我会倒车根据设定的考虑，如果类别记录小得多基础...如
SELECT STRAIGHT_JOIN
a.*
FROM
article_categories AS c
JOIN articles as a
ON c.article_id = a.id
AND a.deleted = 0
AND a.Comment_cnt > 0
WHERE
c.category_id = 78
ORDER BY
a.last_updated
LIMIT
100, 20
在这种情况下，我会确保在文章表中的索引
指数 - （ID，删除LAST_UPDATED）
Answer 6:
首先，我会推荐阅读文章3种方式的MySQL使用索引 。
而现在，当你知道的基本知识，可以优化这个特定的查询。
MySQL不能在索引的顺序排序，它只是能够输出数据的使用索引。 由于MySQL使用嵌套循环加盟，你想要的领域由应在第一个表中加入（你看到加入EXPLAIN结果的顺序来排序，并且可以通过创建特定的指标，（如果它不利于对其产生影响）通过强制要求的指标）。
另一个重要的事情是，在订货之前，你从获取的所有过滤的行中的所有列a表，然后可能跳过其中的大多数。 这是更effifient获得所需的行ID的列表，并获取只有那些行。
为了使这项工作，你需要一个覆盖索引(deleted, comment_cnt, last_updated)上表中a ，现在你可以按照如下重写查询：
SELECT *
FROM (
SELECT a.id
FROM articles AS a,
JOIN article_categories AS c
ON a.id = c.article_id AND c.category_id = 78
WHERE a.comment_cnt > 0 AND a.deleted = 0
ORDER BY a.last_updated
LIMIT 100, 20
) as ids
JOIN articles USING (id);
PS为表你的表定义a不包含comment_cnt列;）
文章来源: How to make JOIN query use index?
