Mysql 多連接性能慢

我正在開發一個搜尋應用程序，該應用程序使用多個可以使用的過濾器查詢大量資訊（100M+ 行），並且在組合多個過濾器時我無法讓它執行。當我有三個或更多連接時，查詢需要幾個小時才能完成。有什麼方法可以更快地進行多個連接，還是我做錯了什麼？下面的 SQL fiddle 解釋了這種困境：

http://sqlfiddle.com/#!9/db1eab8/1

表結構和慢查詢也如下：

-- ----------------------------
-- MariaDB 10.1 Table structures (simplified names and columns for this case)
-- ----------------------------
-- Contains the main information rows - this normally has 100M+ rows
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
-- Other columns would normally be here but were removed to simplify this
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;


-- Contains the text content for a - This has the same amount of rows as `a`
DROP TABLE IF EXISTS `a_content`;
CREATE TABLE `a_content` (
 `id_a` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(2000) NOT NULL,
 `description` varchar(2000) NOT NULL,
 PRIMARY KEY (`id_a`),
 FULLTEXT `f` (`title`,`description`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains whether an `a` ID is hidden for a user ID - This normally has 30M+ rows
DROP TABLE IF EXISTS `a_hidden`;
CREATE TABLE `a_hidden` (
 `id_a` int(11) NOT NULL AUTO_INCREMENT,
 `user` int(11) NOT NULL,
 PRIMARY KEY (`id_a`,`user`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains an `a`'s country ID's - This normally has 300M+ rows
DROP TABLE IF EXISTS `a_country`;
CREATE TABLE `a_country` (
 `id_a` int(11) NOT NULL AUTO_INCREMENT,
 `country` tinyint(4) NOT NULL,
 PRIMARY KEY (`id_a`,`country`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Sample Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('1');
INSERT INTO `a` VALUES ('2');
INSERT INTO `a` VALUES ('3');
INSERT INTO `a` VALUES ('4');
INSERT INTO `a` VALUES ('5');
INSERT INTO `a` VALUES ('6');
INSERT INTO `a` VALUES ('7');
INSERT INTO `a` VALUES ('8');
INSERT INTO `a` VALUES ('9');
INSERT INTO `a` VALUES ('10');

INSERT INTO `a_content` VALUES ('1','aa sadd','aa');
INSERT INTO `a_content` VALUES ('2','aasdadd sads','aa');
INSERT INTO `a_content` VALUES ('3','aa daa','aaf df');
INSERT INTO `a_content` VALUES ('4','aa sad','aadf ');
INSERT INTO `a_content` VALUES ('5','aa v','aaxcv');
INSERT INTO `a_content` VALUES ('6','aa c','aavv');
INSERT INTO `a_content` VALUES ('7','aa df','aaaa');
INSERT INTO `a_content` VALUES ('8','aa df','aa s');
INSERT INTO `a_content` VALUES ('9','aa vvv','a a');
INSERT INTO `a_content` VALUES ('10','ss','aaaa');

INSERT INTO `a_hidden` VALUES ('1','1');
INSERT INTO `a_hidden` VALUES ('3','1');
INSERT INTO `a_hidden` VALUES ('5','1');
INSERT INTO `a_hidden` VALUES ('7','1');
INSERT INTO `a_hidden` VALUES ('9','1');

INSERT INTO `a_country` VALUES ('1',1);
INSERT INTO `a_country` VALUES ('1',2);
INSERT INTO `a_country` VALUES ('2',1);
INSERT INTO `a_country` VALUES ('3',2);
INSERT INTO `a_country` VALUES ('4',4);
INSERT INTO `a_country` VALUES ('5',112);
INSERT INTO `a_country` VALUES ('6',33);
INSERT INTO `a_country` VALUES ('7',44);
INSERT INTO `a_country` VALUES ('8',66);
INSERT INTO `a_country` VALUES ('9',88);
INSERT INTO `a_country` VALUES ('10',99);

詢問：

# Extremely slow and takes hours even though it's using indexes all over. If I remove one join it's instantly fast though
SELECT a.id
FROM  `a` 
LEFT JOIN a_hidden ON a_hidden.id_a = a.id && a_hidden.user=1
LEFT JOIN a_content ON a_content.id_a = a.id
INNER JOIN a_country ON a_country.id_a = a.id && a_country.country IN (1,2,3) 
WHERE a_hidden.id_a IS NULL && MATCH (a_content.title, a_content.description) AGAINST ('search' IN BOOLEAN MODE)
ORDER BY a.id DESC 
LIMIT 0 , 50

上述查詢和表結構的解釋：

• LEFT JOIN礙事 - 它阻止從 開始MATCH，這將是最佳的。
• is_hidden使索引複雜化；要麼從表中刪除這些行，要麼在之後過濾。
• 由於a.id無處不在，而且它是唯一選定的列，因此這不可能是一個現實的查詢。

嗯.. MySQL 不能很好地用作搜尋引擎。我使用了搜尋引擎，並立即使用多個過濾器進行選擇。

引用自：https://dba.stackexchange.com/questions/150569