什麼會減慢通過重新導入修復的 InnoDB 數據庫？

我最近注意到具有數百萬條記錄的 MySQL 5.6 (InnoDB) 數據庫的性能下降。這個特定的查詢在第一次執行時大約需要 20 秒（顯然數據是從磁碟載入的），而在後續執行時需要 4 秒（顯然數據是從記憶體中載入的）：

SELECT
COUNT(DISTINCT `t3`.`node_id`) AS '__value'
FROM `hd2_ont_class_url` AS `t3`
INNER JOIN `hd2_ont_link` AS `t4` ON ((`t4`.`passive`=`t3`.`node_id`) AND (`t4`.`linktype`='tagset'))
INNER JOIN `hd2_ont_rel` AS `t5` ON ((`t5`.`passive`=`t4`.`active`) AND (`t5`.`active`='28530') AND (`t5`.`linktype`='tagup'))
WHERE
(`t3`.`deleted` = 0) AND
(MATCH (`t3`.`title`) AGAINST ('+php*' IN BOOLEAN MODE));

在另一個環境中對同一個數據庫進行相同的查詢只需要大約 0.02 秒，所以我開始調查阻塞的來源。嘗試了不同的版本、配置——都一樣。EXPLAIN輸出也到處都是相同的。

最後，我只是轉儲並重新導入原始數據庫。就是這樣！現在查詢時間不超過 0.05 秒！

所以這裡有問題：

1. 是什麼導致了性能下降？如何預防？
2. 是否有某種維護程序可以像重新導入那樣解決問題？

提示：數據庫中的記錄經常輪換——每天大約有 20% 的記錄被刪除並創建新記錄。

1. 性能下降很可能是由於每天插入和刪除許多行而導致表變得越來越碎片化。
2. 轉儲您的表，刪除它並在多次刪除後重新載入將壓縮磁碟空間並減小 .ibd 文件的大小，假設它使用自己的表空間。

您可以使用 null 更改完成相同的操作，如下所示：

ALTER TABLE sometable FORCE;

這將重建表。

這可能會擺脫文件排序和JOIN+的爆炸內爆DISTINCT：

SELECT  COUNT(*) AS '__value'
   FROM  `hd2_ont_class_url` AS `t3`
   WHERE  (`t3`.`deleted` = 0)
     AND  (MATCH (`t3`.`title`) AGAINST ('+php*' IN BOOLEAN MODE))
     AND  EXISTS 
       ( SELECT  *
           FROM  `hd2_ont_link`
           WHERE  `passive` = `t3`.`node_id`
             AND  `linktype` = 'tagset'
             AND  EXISTS (
                    SELECT  *
                        FROM  `hd2_ont_rel`
                        WHERE  `passive` = `t4`.`active`
                          AND  `active`='28530'
                          AND  `linktype`='tagup'
                         )
       )

另外，這些複合索引（按任何順序）：

hd2_ont_link: INDEX(passive, linktype)
hd2_ont_rel: INDEX(passive, active, linktype)

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