mysql - MySQL联接优化-绕过filesort

优化MySQL左连接仍然有问题。该查询需要0.13秒才能完成，而下一个查询（简化查询）需要0.00秒。
我想实现0.00左右的查询。
我试着创建索引和组合索引。变化不大。基本上只要EXPLAIN中有文件排序，就很慢。我不知道该怎么办。。。跨表创建索引？它甚至存在吗？
谢谢您。
罪魁祸首：

SELECT
  SQL_NO_CACHE p.id
FROM 1_posts p
  INNER JOIN 1_topics t
    ON (p.cid = t.cid && p.container = t.id)
WHERE
  t.cid = 1010699
ORDER BY
  p.id DESC
LIMIT 1;

解释输出：

+----+-------------+-------+------+-------------------+-------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys     | key   | key_len | ref                 | rows | Extra                                        |
+----+-------------+-------+------+-------------------+-------+---------+---------------------+------+----------------------------------------------+
|  1 | SIMPLE      | t     | ref  | PRIMARY,cid,cid_2 | cid   | 4       | const               |  216 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ref  | PRIMARY,cid,cid_2 | cid_2 | 8       | const,forumdb.t.id |   12 |                                              |
+----+-------------+-------+------+-------------------+-------+---------+---------------------+------+----------------------------------------------+

Now, the same simplified query that works fine (uses indexes etc. The only difference is between the brackets):

SELECT
  SQL_NO_CACHE p.id
FROM
  1_posts p
  INNER JOIN 1_topics t
    ON (p.cid = t.cid)
WHERE
  t.cid = 1010699
ORDER BY
  p.id DESC
LIMIT 1;

解释：

+----+-------------+-------+-------+-------------------+---------+---------+-------+-------+--------------------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows  | Extra                    |
+----+-------------+-------+-------+-------------------+---------+---------+-------+-------+--------------------------+
|  1 | SIMPLE      | p     | range | PRIMARY,cid,cid_2 | PRIMARY | 4       | NULL  | 31720 | Using where; Using index |
|  1 | SIMPLE      | t     | ref   | PRIMARY,cid,cid_2 | cid_2   | 4       | const |   194 | Using index              |
+----+-------------+-------+-------+-------------------+---------+---------+-------+-------+--------------------------+

Tables:

CREATE TABLE `1_posts` (
  `cid` int(20) unsigned NOT NULL DEFAULT '0',
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `container` int(20) unsigned NOT NULL DEFAULT '0',
  `creator` int(20) unsigned NOT NULL DEFAULT '0',
  `ref` int(20) unsigned DEFAULT NULL,
  `timestamp` int(20) unsigned NOT NULL DEFAULT '0',
  `posticon` tinyint(11) DEFAULT NULL,
  `last_edited_ts` int(10) unsigned DEFAULT NULL,
  `last_edited_by` int(20) unsigned DEFAULT NULL,
  `signature` varchar(250) DEFAULT NULL,
  `client_ip` int(10) unsigned NOT NULL DEFAULT '0',
  `data_format` tinyint(20) unsigned DEFAULT NULL,
  `use_bbcode` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `use_smileys` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `topic_hash` int(10) unsigned NOT NULL DEFAULT '0',
  `del_ts` int(10) unsigned NOT NULL DEFAULT '0',
  `del_reason` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`cid`,`id`),
  UNIQUE KEY `cid` (`cid`,`topic_hash`,`container`,`id`,`del_ts`),
  KEY `cid_2` (`cid`,`container`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


CREATE TABLE `1_topics` (
  `cid` int(10) unsigned NOT NULL DEFAULT '0',
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `container` int(20) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL DEFAULT '',
  `creator` int(20) unsigned NOT NULL DEFAULT '0',
  `last_modified` int(20) unsigned NOT NULL DEFAULT '0',
  `views` int(11) NOT NULL DEFAULT '0',
  `closed` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `sticky` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `last_post_id` int(20) unsigned DEFAULT NULL,
  `num_posts` int(10) unsigned DEFAULT NULL,
  `lp_ts` int(20) unsigned NOT NULL DEFAULT '0',
  `posticon` smallint(5) unsigned DEFAULT NULL,
  `hidden` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `topic_change_ts` int(10) unsigned NOT NULL DEFAULT '0',
  `topic_hash` int(10) unsigned NOT NULL DEFAULT '0',
  `forum_hash` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`cid`,`id`),
  KEY `container` (`container`),
  KEY `last_modified` (`last_modified`),
  KEY `sticky` (`sticky`),
  KEY `topic_hash` (`topic_hash`),
  KEY `forum_hash` (`forum_hash`),
  KEY `cid` (`cid`,`id`),
  KEY `cid_2` (`cid`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=211963 DEFAULT CHARSET=latin1

这是Gordon's index添加后的EXPLAIN输出：
+----+-------------+-------+------+-------------------------------+-------+---------+---------------------+------+----------------------------------------------+
|id |选择|类型|表格|类型|可能的|键|键|长度|参考|行|额外|
+----+-------------+-------+------+-------------------------------+-------+---------+---------------------+------+----------------------------------------------+
|1 | SIMPLE | t | ref | PRIMARY，cid，cid | cid | 4 | const | 212 |使用索引；使用临时；使用文件排序|
|1 | SIMPLE | p | ref | PRIMARY，cid，cid|u 2，cid|u 3，cid|u 4 | cid|u 3 | 8 | const，forumdb.t.id | 11 |使用索引|
+----+-------------+-------+------+-------------------------------+-------+---------+---------------------+------+----------------------------------------------+

最佳答案

此版本使用正确的索引：

SELECT SQL_NO_CACHE p.id
FROM 1_posts p INNER JOIN
     1_topics t
     ON (p.cid = t.cid)
WHERE t.cid = 1010699
ORDER BY p.id DESC LIMIT 1;

此版本不：

SELECT SQL_NO_CACHE p.id
FROM 1_posts p INNER JOIN
     1_topics t
     ON (p.cid = t.cid && p.container = t.id);
WHERE t.cid = 1010699
ORDER BY p.id DESC
LIMIT 1;

首先，MySQL可以先对l_posts(cid, id)子句使用where上的索引（cid列在索引中位于第一位），然后对join（同一列）使用索引。然后它可以使用相同的索引进行排序--id是索引中的下一列。（顺便说一下，这是使用MySQL优化器的一个特性，它将=子句中的where条件从t传播到p）
其次，MySQL可以对l_posts(cid, container)和where使用join索引。但同一索引不能用于排序。引擎决定文件排序比合并两个不同的索引要好。
要使第二个版本使用索引，请在l_posts(cid, container, id)上定义一个索引。

关于mysql - MySQL联接优化-绕过filesort，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18206819/