多对多表中的MySQL ORDER BY优化

Question

表：

CREATE TABLE IF NOT EXISTS `posts` (

  `post_n` int(10) NOT NULL auto_increment,
  `id` int(10) default NULL,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',

  PRIMARY KEY  (`post_n`,`visibility`),
  KEY `id` (`id`),
  KEY `date` (`date`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `subscriptions` (

  `subscription_n` int(10) NOT NULL auto_increment,
  `id` int(10) NOT NULL,
  `subscribe_id` int(10) NOT NULL,

  PRIMARY KEY  (`subscription_n`),
  KEY `id` (`id`),
  KEY `subscribe_id` (`subscribe_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

查询：

SELECT posts.* FROM posts, subscriptions 

WHERE posts.id=subscriptions.subscribe_id AND subscriptions.id=1 

ORDER BY date DESC LIMIT 0, 15

它之所以如此慢，是因为使用了索引"id"，"subscribe_id“，而不是索引"date”，因此排序非常慢。

是否有任何选项可以更改查询、索引、体系结构？

Answer 1

可能的改进：

首先，如果您命名字段而不是使用SELECT posts.*，则每次查询将获得几微秒的时间，这将导致模式查找。将您的查询更改为：

SELECT posts.post_n, posts.id, posts.date 
FROM posts, subscriptions 
WHERE posts.id=subscriptions.subscribe_id 
  AND subscriptions.id=1 
ORDER BY date DESC 
LIMIT 0, 15

接下来，这需要MySQL 5.1或更高版本，但是您可能需要考虑对表进行分区。您可以考虑对这两个表进行键分区。

这应该可以让你开始学习了。http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html

例如。

SET SQL_MODE = 'ANSI';
-- to allow default date
CREATE TABLE IF NOT EXISTS `posts` (
  `post_n` int(10) NOT NULL auto_increment,
  `id` int(10) default NULL,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`post_n`,`id`),
  KEY `id` (`id`),
  KEY `date` (`date`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin
PARTITION BY KEY(id) PARTITIONS 32;
--
CREATE TABLE IF NOT EXISTS `subscriptions` (
  `subscription_n` int(10) NOT NULL auto_increment,
  `id` int(10) NOT NULL,
  `subscribe_id` int(10) NOT NULL,
  PRIMARY KEY  (`subscription_n`,`subscribe_id`),
  KEY `id` (`id`),
  KEY `subscribe_id` (`subscribe_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin
PARTITION BY KEY(subscribe_id) PARTITIONS 32;

我不得不稍微调整一下你的主键。所以，当心，这可能对你不起作用。请对其进行测试并确保。不过，我希望这能做到。确保对新旧结构/查询运行sysbench，以便在投入生产之前比较结果。:-)

Answer 2

如果您能够修改该表，则可以添加一个同时包含ID和date的多字段索引。(或修改其中一个现有键以同时包含这两个键)。

如果您不能对数据库进行更改，并且您知道结果集将很小，那么可以通过USE KEY(name)强制它使用特定的命名键。排序将在事实发生后完成，只需返回结果即可。

希望这能有所帮助。