有大量行的MySQL性能问题
我正在开发基于XenForo引擎的网站，并且在查询中存在问题，该查询会提取所有线程并加入张贴表和论坛表，以获取线索所属的第一篇文章和论坛的一些信息。 该查询如下所示：
SELECT thread . *
FROM xf_thread AS thread
INNER JOIN xf_node AS node ON (node.node_id = thread.node_id)
INNER JOIN xf_post AS post ON (post.post_id = thread.first_post_id)
WHERE thread.node_id IN ('295', '296', '297', '298', '299', '300', '301', '302', '256', '2575', '258', '259', '260', '253', '254', '255', '127', '163', '159', '144', '145', '146', '147', '148', '164', '165', '166', '167', '168', '169', '170', '162', '171', '173', '172', '128', '129', '130', '131', '132', '133', '134', '135', '136', '137', '138', '139', '140', '141', '142', '143', '151', '152', '153', '154', '155', '157', '156', '158', '161', '160', '149', '227', '232', '237', '233', '236', '234', '235', '238', '248', '240', '241', '242', '239', '246', '247', '243', '244', '245', '228', '229', '230', '231', '249', '250', '251', '174', '190', '195', '199', '193', '191', '197', '198', '192', '200', '204', '207', '205', '203', '206', '202', '208', '201', '187', '176', '177', '178', '189', '188', '180', '186', '184', '185', '182', '183', '181', '179', '209', '211', '217', '218', '219', '210', '212', '213', '214', '215', '216', '220', '222', '223', '224', '221', '225', '261', '291', '276', '272', '270', '265', '277', '267', '286', '292', '289', '274', '264', '287', '278', '282', '279', '281', '280', '283', '284', '285', '290', '275', '268', '263', '266', '294', '262', '293', '269', '273', '288', '271')
ORDER BY thread.last_post_date DESC
LIMIT 10
解释查询的结果是：
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE node index PRIMARY PRIMARY 4 NULL 199 Using where; Using index; Using temporary; Using filesort 1 SIMPLE thread ref node_id_last_post_date,node_id_sticky_last_post_date node_id_last_post_date 4 node.node_id 221 1 SIMPLE post eq_ref PRIMARY PRIMARY 4 thread.first_post_id 1 Using index
查询需要9秒以上才能执行。
删除xf_node表的连接，在0.01秒内运行查询。 解释看起来像
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE thread index node_id_last_post_date,node_id_sticky_last_post_da... last_post_date 4 NULL 69970 Using where 1 SIMPLE post eq_ref PRIMARY PRIMARY 4 thread.first_post_id 1 Using index
删除xf_post表的连接在0.01秒内运行查询，解释看起来像
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE thread index node_id_last_post_date,node_id_sticky_last_post_da... last_post_date 4 NULL 70840 Using where 1 SIMPLE node eq_ref PRIMARY PRIMARY 4 thread.node_id 1 Using index
所以，这个问题只有在两个表都加入时才存在，但加入自己似乎是完全正确的，并且完全分开工作。
表中的行数 - xf_thread：71,855，xf_node：178，xf_post：2,977,326
我的假设是，当两个表都加入时，MySQL开始使用不正确的索引，并且强制索引可以解决问题？
您的帮助和建议，以找到一种方法来解决这个问题，高度赞赏。
编辑：这里是所有表涉及创建表语句
xf_node
CREATE TABLE `xf_node` (
`node_id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(50) NOT NULL,
`description` text NOT NULL,
`node_name` varchar(50) default NULL COMMENT 'Unique column used as string ID by some node types',
`node_type_id` varbinary(25) NOT NULL,
`parent_node_id` int(10) unsigned NOT NULL default '0',
`display_order` int(10) unsigned NOT NULL default '1',
`display_in_list` tinyint(3) unsigned NOT NULL default '1' COMMENT 'If 0, hidden from node list. Still counts for lft/rgt.',
`lft` int(10) unsigned NOT NULL default '0' COMMENT 'Nested set info ''left'' value',
`rgt` int(10) unsigned NOT NULL default '0' COMMENT 'Nested set info ''right'' value',
`depth` int(10) unsigned NOT NULL default '0' COMMENT 'Depth = 0: no parent',
`style_id` int(10) unsigned NOT NULL default '0' COMMENT 'Style override for specific node',
`effective_style_id` int(10) unsigned NOT NULL default '0' COMMENT 'Style override; pushed down tree',
PRIMARY KEY (`node_id`),
UNIQUE KEY `node_name_unique` (`node_name`,`node_type_id`),
KEY `parent_node_id` (`parent_node_id`),
KEY `display_order` (`display_order`),
KEY `display_in_list` (`display_in_list`,`lft`),
KEY `lft` (`lft`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=304 ;
xf_post
CREATE TABLE `xf_post` (
`post_id` int(10) unsigned NOT NULL auto_increment,
`thread_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`username` varchar(50) NOT NULL,
`post_date` int(10) unsigned NOT NULL,
`message` mediumtext NOT NULL,
`ip_id` int(10) unsigned NOT NULL default '0',
`message_state` enum('visible','moderated','deleted') NOT NULL default 'visible',
`attach_count` smallint(5) unsigned NOT NULL default '0',
`position` int(10) unsigned NOT NULL,
`likes` int(10) unsigned NOT NULL default '0',
`like_users` blob NOT NULL,
`warning_id` int(10) unsigned NOT NULL default '0',
`warning_message` varchar(255) NOT NULL default '',
PRIMARY KEY (`post_id`),
KEY `thread_id_post_date` (`thread_id`,`post_date`),
KEY `thread_id_position` (`thread_id`,`position`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3123657 ;
xf_thread
CREATE TABLE `xf_thread` (
`thread_id` int(10) unsigned NOT NULL auto_increment,
`node_id` int(10) unsigned NOT NULL,
`title` varchar(150) NOT NULL,
`reply_count` int(10) unsigned NOT NULL default '0',
`view_count` int(10) unsigned NOT NULL default '0',
`user_id` int(10) unsigned NOT NULL,
`username` varchar(50) NOT NULL,
`post_date` int(10) unsigned NOT NULL,
`sticky` tinyint(3) unsigned NOT NULL default '0',
`discussion_state` enum('visible','moderated','deleted') NOT NULL default 'visible',
`discussion_open` tinyint(3) unsigned NOT NULL default '1',
`discussion_type` varchar(25) NOT NULL default '',
`first_post_id` int(10) unsigned NOT NULL,
`first_post_likes` int(10) unsigned NOT NULL default '0',
`last_post_date` int(10) unsigned NOT NULL,
`last_post_id` int(10) unsigned NOT NULL,
`last_post_user_id` int(10) unsigned NOT NULL,
`last_post_username` varchar(50) NOT NULL,
`prefix_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`thread_id`),
KEY `node_id_last_post_date` (`node_id`,`last_post_date`),
KEY `node_id_sticky_last_post_date` (`node_id`,`sticky`,`last_post_date`),
KEY `last_post_date` (`last_post_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=76301 ;
谢谢。
有时，mysql中的order by子句将使用临时表对结果进行排序。 对于大数据可能需要很长时间。 避免使用“通过desc命令”和MySQL以外的数据排序。
您发布的查询不使用帖子表中的任何列，也不用于过滤器。 我会说你可以放弃。
对于DESC排序（这非常耗时）：如果您创建一个附加列并将其填入
MAX_INT - unix_timestamp(thread.last_post_date)
这会给你后面的线程更小的数字，所以ASC顺序就可以让你真正按时完成DESC。
在这种情况下，还必须创建新的索引。
（这个例子是伪语言，检查mysql的语法）
如果两个thread列（ node_id和first_post_id ）不可为空，并且您有可信任的FOREIGN KEY约束，则可以从thread(node_id)朝向xf_node (node_id) ，从thread(first_post_id)朝向xf_post(post_id) ，您可以移除加入到xf_node和xf_post ：
SELECT thread . *
FROM xf_thread AS thread
WHERE thread.node_id IN (295, 296, ..., 271)
ORDER BY thread.last_post_date DESC
LIMIT 10 ;
(last_post_date, node_id)上的索引也可以帮助提高效率：
ALTER TABLE xf_thread
ADD INDEX last_post_date_node_id
(last_post_date, node_id) ;