如何刪除多個冗餘索引？

我有一個舊版應用程序。我想使用Percona 部落格中的腳本找出哪些表是最大的。事實證明，我的一些表的索引比實際數據大 3 倍或更多。我已經從 MySql 伺服器中轉儲了模式，發現有些表有很多看起來是冗餘的索引。

以下是創建最差表之一的方法：

CREATE TABLE `pictures_relations` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `pic_id` int(10) DEFAULT NULL,
 `url_id` int(10) DEFAULT NULL COMMENT 'id урла, с которого файл был/будет скачан',
 `is_stub` tinyint(1) NOT NULL DEFAULT '0',
 `item_id` int(11) NOT NULL,
 `module_id` int(11) NOT NULL,
 `item_type` varchar(8) NOT NULL,
 `is_file` tinyint(1) NOT NULL,
 `is_hide` tinyint(1) NOT NULL,
 `is_cover` tinyint(1) NOT NULL,
 `image_only` tinyint(1) DEFAULT NULL,
 `is_custom` tinyint(1) NOT NULL DEFAULT '0',
 `skip` tinyint(1) NOT NULL DEFAULT '0',
 `order` int(11) NOT NULL,
 `author` int(11) NOT NULL,
 `add_date` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `is_hide` (`is_hide`),
 KEY `is_cover` (`is_cover`),
 KEY `add_date` (`add_date`),
 KEY `item_id_2` (`item_id`,`module_id`,`item_type`,`is_file`,`is_cover`),
 KEY `order` (`item_id`,`module_id`,`item_type`,`is_file`,`order`),
 KEY `item_id` (`item_id`,`module_id`,`item_type`,`is_file`,`is_custom`),
 KEY `pic_id` (`pic_id`),
 KEY `item_id_3` (`item_id`,`module_id`,`item_type`,`is_file`,`skip`),
 KEY `item_id_4` (`item_id`,`module_id`,`item_type`,`is_file`),
 KEY `pic_id_2` (`pic_id`,`skip`),
 KEY `skip` (`skip`,`pic_id`),
 KEY `stub_url` (`is_stub`,`url_id`),
 KEY `stub_skip_url` (`is_stub`,`skip`,`url_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6794944 DEFAULT CHARSET=utf8;

我可以清楚地看到索引、、、order和大多相似item_id，但它們有點不同。使用便捷視圖輕鬆檢測此 DBA.StackExchange 答案中的冗餘索引有幫助，但作用不大——它確實表明這對所有其他人來說都是多餘的，但無助於將它們拆分/合併為更健全的索引。item_id_2``item_id_3``item_id_4``item_id_4

問題是： 是否有經驗法則可以將這些索引重構為合理的東西，減少重複？

就像，如果我有idx1('a', 'b', 'c'), idx2('a', 'b', 'd'), idx3('a', 'b', 'e'), 那麼我必須把它變成idx1('a', 'b'), idx2('c'), idx3('d')and idx4('e')?

這個問題沒有看到就無法回答SELECTs。

以下是部分答案…

• 唯一明顯冗餘的查詢是pick_idand item_id_4。規則很簡單：如果一個索引的所有列都恰好是某個其他索引的第一列，那麼就扔掉它。（注意：這僅適用於非唯一索引。）
• 下一組明顯的啞彈是(is_*)。這些似乎都是真/假的“標誌”。優化器不會使用此類（極少數例外）。另一方面，它會考慮INDEX(is_foo, ...). 那是一個以標誌開頭的複合索引。（而且，不，第一列的低基數還不錯。）
• 索引中的 5 列正在推動它“應該”擁有的最大值。我很想保留以 ( , , , )開頭的 5 列索引*之一。*在某種程度上，我扔掉的那些仍然會受益於我保留的那個。item_id``module_id``item_type``is_file
• 重新排序列取決於SELECTs外觀。如果使用者可以請求“任何”列組合進行過濾，則下注。好吧，不完全是平底船……做一些 2 或 3 列的組合。
• INDEX(a,b,c)不等於, 。_ 一些查詢受益於一個；一些來自另一個。INDEX(a,b)``INDEX(c)

另請參閱：關於生成索引的食譜

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