我有一个遗留应用程序。我想使用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;
我可以清楚地看到 indices order
、item_id
、item_id_2
和大部分相似item_id_3
,item_id_4
但它们略有不同。使用方便的视图从这个 DBA.StackExchange 答案中轻松检测冗余索引有帮助,但作用不大——它确实表明它对item_id_4
所有其他索引都是冗余的,但无助于将它们拆分/合并到更合理的索引中。
问题是: 是否有经验法则可以将这些索引重构为理智的东西,减少重复?
就像,如果我有idx1('a', 'b', 'c')
, idx2('a', 'b', 'd')
, ,idx3('a', 'b', 'e')
那么我必须把它变成idx1('a', 'b')
,idx2('c')
和?idx3('d')
idx4('e')
没看过就无法回答这个问题
SELECTs
。以下是一些部分答案...
唯一明显冗余的查询是
pick_id
anditem_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)
一些查询从中受益;一些来自另一个。另请参阅:关于生成索引的 Cookbook
我建议使用 Percona Toolkit 包中的 pt-duplicate-key-checker 工具。