提高四个巨型(~100M行) MySQL表的JOINed SELECT查询速度

Question

我有以下查询，该查询根据给定的linker编号查找每个相关记录的额外信息。例如，下面的查询为使用linker=86sgv_ksg:0040608 linkTo单个文档的所有文档查找Author、Date和linkTo。

SELECT 
    `r`.`linker`, 
    IF(`s`.`isSecond`='1', `c2`.`title`, `c1`.`title`) AS `Title`,
    IF(`s`.`isSecond`='1', `c2`.`author`, `c1`.`author`) AS `Author`,
    IF(`s`.`isSecond`='1', `c2`.`date`, `c1`.`date`) AS `Date`
FROM 
    (SELECT `linker` FROM `my_rel` WHERE `linkTo`='86sgv_ksg:0040608') `r` 
    INNER JOIN `my_stat` `s` ON `r`.`linker`=`s`.`linker`
    LEFT JOIN `my_content_1` `c1` ON (`s`.`isSecond`='0' AND `s`.`linker`=`c1`.`linker`)
    LEFT JOIN `my_content_2` `c2` ON (`s`.`isSecond`='1' AND `s`.`linker`=`c2`.`linker`);

这是EXPLAIN结果：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    38702   NULL    
1   PRIMARY s   eq_ref  Unique  Unique  767 r.linker    1   NULL    
1   PRIMARY c1  ref linker  linker  767 r.linker    1   Using where 
1   PRIMARY c2  ref linker  linker  767 r.linker    1   Using where 
2   DERIVED my_rel  ref Link    Link    767 const   38702   Using index condition   

根据找到的记录数量，这个查询需要几秒钟的时间(几乎每1000行就有一秒)

# Query_time: 20.393228  Lock_time: 0.000115 Rows_sent: 19917  Rows_examined: 99672

使用此服务器：

CPU: Intel® Core™ i7-6700
RAM: 64 GB DDR4
Hard Drive: 2 x 500 GB SATA 6 Gb/s 
    |_ SSD: Software-RAID 0 = 1000GB

我的操作系统(Linux)和MySQL DB都在固态硬盘上。但是查询仍然需要几秒钟的时间。

表my_rel (大约200M行)保存文档之间的所有关系，这些文档根据它们的类型被分隔在两个表中：my_content_1 (大约5M行)和my_content_2 (大约65M行)。表my_stat (大约70M行)标识每个文档的位置(在my_content_1或my_content_2中)。这四个表的SHOW CREATE TABLE如下：

CREATE TABLE `my_content_1` /*similarly `my_content_2`*/ (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `title` text COLLATE utf8_general_ci NOT NULL,
 `author` tinytext COLLATE utf8_general_ci NOT NULL,
 `date` date NOT NULL,
 `linker` varchar(255) COLLATE utf8_general_ci NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `linker` (`linker`) USING BTREE,
 KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=67654117 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=COMPRESSED 

CREATE TABLE `my_rel` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `linker` varchar(255) COLLATE utf8_general_ci NOT NULL,
 `order` int(10) unsigned NOT NULL,
 `linkTo` varchar(255) COLLATE utf8_general_ci NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `Unique` (`linker`, `order`) USING BTREE,
 KEY `Link` (`linkTo`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=248383246 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=COMPRESSED

CREATE TABLE `my_stat` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `linker` varchar(255) COLLATE utf8_general_ci NOT NULL,
 `isSecond` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `Unique` (`linker`) USING BTREE,
 KEY `isSecond` (`isSecond`)
) ENGINE=InnoDB AUTO_INCREMENT=111412100 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=COMPRESSED

最初我的查询是这样的：

SELECT 
    `r`.`linker`, 
    IF(`s`.`isSecond`='1', `c2`.`title`, `c1`.`title`) AS `Title`,
    IF(`s`.`isSecond`='1', `c2`.`author`, `c1`.`author`) AS `Author`,
    IF(`s`.`isSecond`='1', `c2`.`date`, `c1`.`date`) AS `Date`
FROM `my_rel` `r` 
    LEFT JOIN `my_stat` `s` ON `r`.`linker`=`s`.`linker`
    LEFT JOIN `my_content_1` `c1` ON (`s`.`isSecond`='0' AND `s`.`linker`=`c1`.`linker`)
    LEFT JOIN `my_content_2` `c2` ON (`s`.`isSecond`='1' AND `s`.`linker`=`c2`.`linker`)
WHERE `r`.`linkTo`='86sgv_ksg:0040608' AND `r`.`linker`!='86sgv_ksg:0040608' 
GROUP BY `r`.`linker` 
ORDER BY `Date` DESC;

通过分组和排序，它比我当前的查询花了一点时间，我去掉了它们，以获得更快的速度。当前查询仍然需要很长时间才能完成所有链接。

我的查询能更快吗？

Answer 1

当您同时拥有AUTO_INCREMENT和UNIQUE密钥时，请考虑去掉AUTO_INCREMENT并将UNIQUE提升为PRIMARY。通常这将是

对表执行许多查询时，缩小磁盘footprint

• Speed up
• 。

对于my_rel，..。

SELECT `linker` FROM `my_rel` WHERE `linkTo`='86sgv_ksg:0040608'

FROM `my_rel` `r` 
... ON `r`.`linker`...
WHERE `r`.`linkTo`='86sgv_ksg:0040608'
  AND `r`.`linker`!='86sgv_ksg:0040608' 

这两种方法都需要一个带有(linker, linkTo)的索引，通过从

PRIMARY KEY (`id`),
UNIQUE KEY `Unique` (`linker`, `order`) USING BTREE,
KEY `Link` (`linkTo`) USING BTREE

至

PRIMARY KEY (`linker`, `order`),
KEY (`linkTo`, linker)

由于我们正在查看200M中的38K记录，从辅键到主键的跳跃可能会造成超过38K的磁盘命中率，这在旋转的驱动器上可能需要380秒。(由于缓存的原因减少了一些，它可能会减少到您正在经历的整个20秒。)

通过我提出的“复合”键，该索引是“覆盖”的，因此可能会命中磁盘380次，而不是38000次。

PRIMARY KEY (`id`),
UNIQUE KEY `linker` (`linker`) USING BTREE,
KEY `date` (`date`)

-->

PRIMARY KEY `linker` (`linker`),
KEY `date` (`date`)

在进行这些更改时，请考虑降低任何VARCHARs中的255。此外，如果链接器的值类似于'86sgv_ksg:0040608'，请考虑linker和link_to是否可以为CHARACTER SET ascii。

还有，对于my_stat..。

PRIMARY KEY (`id`),
UNIQUE KEY `Unique` (`linker`) USING BTREE,
KEY `isSecond` (`isSecond`)

-->

PRIMARY KEY (`linker`)

注意:标志上的单列索引(isSecond)不太可能使用。

( InnoDB索引的默认值为BTree。唯一的例外是FULLTEXT和SPATIAL。)

Answer 2

从解释来看，这里的主要问题似乎是子查询没有被正确地索引。我将添加以下索引：

ALTER TABLE `my_rel` ADD INDEX `my_rel_index_1` (`linkTo`, `linker`);

此外，我还可以看到查询中的许多地方，您正在将数字列与字符串进行比较。例如：

`s`.`isSecond` = '0'

我会避免这一点，并删除'0‘周围的引号(在所有你做同样事情的地方)。这样的比较可能导致隐式强制转换，这可能会阻止这些筛选器正确使用索引。