MySql - 查找冗余索引

我认为是 Percona 工具包，但我使用了 Roland Bouman 的这些观点

我在下面添加了它们：

CREATE OR REPLACE VIEW I_S_INDEXES
AS
SELECT TABLE_SCHEMA
, TABLE_NAME
, INDEX_NAME
, INDEX_TYPE
, IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE
, GROUP_CONCAT(
CONCAT('`',COLUMN_NAME,'`')
ORDER BY IF( INDEX_TYPE = 'BTREE' -- when BTREE then
, SEQ_IN_INDEX -- column order is important
, 0) -- else
, COLUMN_NAME -- only column content
) COLUMNS
FROM information_schema.STATISTICS
GROUP BY TABLE_SCHEMA
, TABLE_NAME
, INDEX_NAME
, INDEX_TYPE
, NON_UNIQUE
;

CREATE OR REPLACE VIEW
I_S_REDUNDANT_INDEXES
AS
SELECT l.TABLE_SCHEMA
, l.TABLE_NAME
, CASE
WHEN l.COLUMNS = r.COLUMNS
AND (l.IS_UNIQUE = r.IS_UNIQUE)
THEN GREATEST(l.INDEX_NAME, r.INDEX_NAME)
ELSE l.INDEX_NAME
END REDUNDANT_INDEX_NAME
, GROUP_CONCAT(
DISTINCT
CASE
WHEN l.COLUMNS = r.COLUMNS
AND (l.IS_UNIQUE = r.IS_UNIQUE)
THEN LEAST(l.INDEX_NAME, r.INDEX_NAME)
ELSE r.INDEX_NAME
END
) INDEX_NAME
FROM I_S_INDEXES l
INNER JOIN I_S_INDEXES r
ON l.TABLE_SCHEMA = r.TABLE_SCHEMA -- index on the same table
AND l.TABLE_NAME = r.TABLE_NAME
AND l.INDEX_NAME != r.INDEX_NAME -- but not identical
AND l.INDEX_TYPE = r.INDEX_TYPE
AND CASE
WHEN l.COLUMNS = r.COLUMNS -- we require column equality
AND (l.IS_UNIQUE = 'NO' -- redundant if not unique
OR l.IS_UNIQUE = r.IS_UNIQUE) -- or if same uniqueness as other
THEN TRUE
WHEN l.INDEX_TYPE = 'BTREE' -- when BTREE
AND INSTR(r.COLUMNS, l.COLUMNS) = 1 -- and l is a prefix
AND l.IS_UNIQUE = 'NO' -- redundant if not unique
THEN TRUE
ELSE FALSE
END
GROUP BY l.TABLE_SCHEMA
, l.TABLE_NAME
, REDUNDANT_INDEX_NAME

您可以使用Percona Toolkit中的 pt-duplicate-key-checker 。您还可以使用Shlomi Noach的 common_schema。