Início / dba / Perguntas / 209576
Accepted
Eduard Sukharev
Asked: 2018-06-14 08:49:39 +0800 CST

Como remover vários índices redundantes?

  • 772

Eu tenho um aplicativo legado. Eu queria saber quais tabelas são as maiores usando script do blog Percona . Acontece que algumas das minhas tabelas têm índices maiores que os dados reais por fator de 3 ou mais. Eu despejei o esquema do servidor MySql e descobri que algumas tabelas têm muitos índices que parecem ser redundantes.

Veja como uma das piores tabelas é criada:

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;

Posso ver claramente que os índices order, item_id, item_id_2e são item_id_3em item_id_4sua maioria semelhantes, mas diferem um pouco. O uso de visualizações de conveniência para detectar facilmente índices redundantes desta resposta do DBA.StackExchange ajuda, mas não muito - mostra que item_id_4é redundante para todos os outros, mas não ajuda a dividi-los/mesclá-los em índices mais sensatos.

A questão é: existe uma regra prática para refatorar esses índices em algo sensato, com menos duplicação?

Tipo, se eu tiver idx1('a', 'b', 'c'), idx2('a', 'b', 'd'), idx3('a', 'b', 'e'), então eu tenho que transformá-lo em idx1('a', 'b'), idx2('c'), idx3('d')e idx4('e')?

mysql index

3 respostas

  1. Best Answer

    A pergunta não pode ser respondida sem ver o arquivo SELECTs.

    Aqui estão algumas respostas parciais...

    • As únicas consultas obviamente redundantes são pick_ide item_id_4. A regra é simples: se todas as colunas de um índice são exatamente a(s) primeira(s) coluna(s) de algum outro índice, jogue-a fora. (Observação: isso se aplica apenas a índices não exclusivos.)

    • O próximo conjunto de insucessos aparentes são (is_*). Todos esses parecem ser "bandeiras" verdadeiras/falsas. O Optimizer não usará tal (com raras exceções). Por outro lado, considerará INDEX(is_foo, ...). Esse é um índice composto começando com um sinalizador. (E, não, a baixa cardinalidade da primeira coluna não é ruim.)

    • 5 colunas em um índice estão empurrando o máximo que "deveria" ter. Eu ficaria tentado a manter um dos índices de 5 colunas começando com ( item_id, module_id, item_type, is_file). Os que eu joguei ainda seriam beneficiados, em algum nível, pelo que eu mantenho.

    • A reordenação das colunas depende da SELECTsaparência. Se o usuário puder solicitar "qualquer" combinação de colunas para filtrar, então punt. Bem, não totalmente punt... Faça algumas combinações de 2 ou 3 colunas.

    • INDEX(a,b,c)não é o mesmo que INDEX(a,b), INDEX(c). Algumas consultas se beneficiam de um; uns dos outros.

    Veja também: Cookbook sobre como gerar índices

    • 2

  2. Eu recomendaria usar a ferramenta pt-duplicate-key-checker do pacote Percona Toolkit.

    pt-duplicate-key-checker —user=root —ask-pass —databases dbname
    • 1 
  3. SELECT tab_left.TABLE_NAME, 
CASE 
WHEN tab_left.COLUMNS = tab_right.COLUMNS AND (tab_left.IS_UNIQUE = tab_right.IS_UNIQUE) 
THEN GREATEST(tab_left.INDEX_NAME, tab_right.INDEX_NAME) 
ELSE tab_left.INDEX_NAME END REDUNDANT_INDEX_NAME, 
GROUP_CONCAT( DISTINCT CASE WHEN tab_left.COLUMNS = tab_right.COLUMNS 
AND (tab_left.IS_UNIQUE = tab_right.IS_UNIQUE) 
THEN LEAST(tab_left.INDEX_NAME, tab_right.INDEX_NAME) 
ELSE tab_right.INDEX_NAME END SEPARATOR ', ') INDEX_NAME 
FROM (SELECT TABLE_NAME, CONCAT('`', INDEX_NAME, '`') AS INDEX_NAME, 
INDEX_TYPE, 
IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE, 
GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') 
ORDER BY IF( INDEX_TYPE = 'BTREE', SEQ_IN_INDEX, 0), COLUMN_NAME) COLUMNS 
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'your_schema' 
AND TABLE_NAME = TABLE_NAME 
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE) AS tab_left 
INNER JOIN (SELECT TABLE_NAME, CONCAT('`', INDEX_NAME, '`') AS INDEX_NAME, 
INDEX_TYPE, 
IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE, 
GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') ORDER BY IF(INDEX_TYPE = 'BTREE', SEQ_IN_INDEX, 0), COLUMN_NAME) COLUMNS 
FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_schema' 
AND TABLE_NAME = TABLE_NAME GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE) AS tab_right ON tab_left.TABLE_NAME = tab_right.TABLE_NAME 
AND tab_left.INDEX_NAME != tab_right.INDEX_NAME 
AND tab_left.INDEX_TYPE = tab_right.INDEX_TYPE 
AND CASE WHEN tab_left.COLUMNS = tab_right.COLUMNS 
AND (tab_left.IS_UNIQUE = 'NO' OR tab_left.IS_UNIQUE = tab_right.IS_UNIQUE) THEN TRUE 
WHEN tab_left.INDEX_TYPE = 'BTREE' AND INSTR(tab_right.COLUMNS, tab_left.COLUMNS) = 1 AND tab_left.IS_UNIQUE = 'NO' THEN TRUE 
ELSE FALSE END 
GROUP BY tab_left.TABLE_NAME, REDUNDANT_INDEX_NAME;
    • -2

relate perguntas