mysql - 多个 id 匹配和 Join 语句的 SQL 性能

Question

考虑这个查询:

SELECT DISTINCT (linkindex_tags.link_id)
   , links_sorted.link_title
   , links_sorted.link_url
FROM linkindex_tags
INNER JOIN links_sorted ON links_sorted.link_id = linkindex_tags.link_id
ORDER BY
(
IF (word_id = 400, 1,0)+
IF (word_id = 177, 1,0)+
IF (word_id = 114, 1,0)+
IF (word_id = 9, 1,0)+
IF (word_id = 270, 1,0)+
IF (word_id = 715, 1,0)+
IF (word_id = 279, 1,0)+
IF (word_id = 1, 1,0)+
IF (word_id = 1748, 1,0)
)  DESC
LIMIT 0,15;

因此寻找与一系列 word_id 的匹配项并根据这些匹配项的分数排序(例如，找到具有 5 个 word_ids 的链接，那么它的分数为 5)

linkindex_tags 表目前有 552,196 行 (33 MB)，但会扩展到数百万行link_sorted 表目前有 823,600(558MB - 每行数据更多)行，但还会扩展到更多。linkindex_tags 表可能比 links_sorted 表大 8-12 倍。

执行时间:在本地 i3 核心 windows 7 机器上为 7.069 秒。我的服务器是 CentOs 64 位 8GB ram Intel Xeon 3470(四核)——所以我想这会稍微帮助解决这个问题，因为它可以分配合适的 RAM 分配。

它运行缓慢，想知道我的方法是否完全错误。这是配置文件分割中的慢位:

复制到 tmp 表 -(时间)3.88124 -(%)55.08438
复制到磁盘上的 tmp 表 - (time) 2.683123 -(%) 8.08010
将 HEAP 转换为 MyISAM - (time) 0.37656 - (%) 5.34432

这是解释:

id -    1
select_type -   SIMPLE
table - linkindex_tags
type -  index
possible_keys - link_id,link_id_2
key -   link_id
key_len -   8
ref -   \N
rows -  552196
Extra - Using index; Using temporary; Using filesort

2nd row

id -    1
select_type -   SIMPLE
table - links_sorted
type -  eq_ref
possible_keys - link_id
key -   link_id
key_len -   4
ref -   flinksdb.linkindex_tags.link_id
rows -  1
Extra - 

最后是 2 表模式:

CREATE TABLE IF NOT EXISTS `linkindex_tags` (
  `linkindex_tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `link_id` int(10) unsigned NOT NULL,
  `word_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`linkindex_tag_id`),
  UNIQUE KEY `link_id` (`link_id`,`word_id`),
  KEY `link_id_2` (`link_id`),
  KEY `word_id` (`word_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

CREATE TABLE IF NOT EXISTS `links_sorted` (
  `link_sorted_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(10) unsigned NOT NULL,
  `link_id` int(10) unsigned NOT NULL,
  `link_title` char(255) NOT NULL,
  `link_duration` char(20) NOT NULL,
  `link_url` char(255) NOT NULL,
  `active` tinyint(4) NOT NULL,
  PRIMARY KEY (`link_sorted_id`),
  UNIQUE KEY `link_id` (`link_id`),
  KEY `link_title` (`link_title`,`link_url`,`active`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

必须坚持使用 INT，因为它可能会进入比 MEDIUMINT 更大的范围。没有连接，现在我已经提高了一些 MySQL 设置，只需获取 ids 查询就很快了。

不太了解 MySQL 设置及其影响，所以如果您需要我更改一些设置并运行一些测试，请务必开火!

哦，我玩过 mysql.ini 设置，所以它们就像这样 - 真的只是猜测和玩弄!

key_buffer = 512M 
max_allowed_packet = 1M 
table_cache = 512M 
sort_buffer_size = 512M 
net_buffer_length = 8K 
read_buffer_size = 512M 
read_rnd_buffer_size = 512K 

我怎样才能加快这个查询？

Answer 1

一些评论:

不同
SELECT DISTINCT 适用于所有选定的字段，无论您使用多少个 ()，如果您只需要 1 个，请使用 GROUP BY 子句字段是不同的。
请注意，这将使您的查询结果不确定!
如果您想防止这种情况，请保留不同的字段，或者将其他字段聚合到 GROUP_CONCAT 中。

订购方式
一个字段一次只能有一个值，将不同的 IF 加在一起，当只有一个匹配是浪费时间时，请改用 IN。
bool 值 = 1 表示真，0 表示假，您不需要额外的 IF 来断言。

地点
如果您有很多行，请考虑添加一个 where 以减少正在考虑的行数，而不会改变结果。

?
系列:400,177,114,9,270,715,279,1,1748 与《迷失》中的 4-8-15-16-23-42 是同一种魔法结构吗？

SELECT lt.link_id
   , GROUP_CONCAT(ls.link_title) as link_titles
   , GROUP_CONCAT(ls.link_url) as link_urls
FROM linkindex_tags lt
INNER JOIN links_sorted ls ON ls.link_id = lt.link_id
WHERE lt.word_id <= 1748
GROUP BY lt.link_id
ORDER BY
(
  lt.word_id IN (400,177,114,9,270,715,279,1,1748)
)  DESC
LIMIT 15 OFFSET 0;