mysql - 使用查询在 MySQL 中查找重复索引

Question

我知道名为 mysqlindexcheck 的 MySQL 实用程序.但是我可以通过 SQL 查询找到重复的索引吗？我试过了，但正在寻找更好的想法或改进

SELECT DISTINCT
    TABLE_NAME,
    COLUMN_NAME,
    GROUP_CONCAT(DISTINCT INDEX_NAME)
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'db_name' 
AND INDEX_NAME NOT IN ('PRIMARY')
GROUP BY COLUMN_NAME
HAVING count(DISTINCT INDEX_NAME)>1
ORDER BY TABLE_NAME ;

Answer 1

试试下面的查询:

SELECT s.INDEXED_COL,GROUP_CONCAT(INDEX_NAME) FROM (
SELECT INDEX_NAME,GROUP_CONCAT(CONCAT(TABLE_NAME,'.',COLUMN_NAME) ORDER BY CONCAT(SEQ_IN_INDEX,COLUMN_NAME)) 'INDEXED_COL' FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'db?' 
GROUP BY INDEX_NAME
)as s GROUP BY INDEXED_COL HAVING COUNT(1)>1

注意:这将根据列顺序区分两个组合键

例如:IND1(col1,col2) 和 IND2(col2,col1) 将被视为不同的(不是重复的)索引

你可以试试这个(但这对复合索引不起作用):

SELECT DISTINCT
        TABLE_NAME,
        COLUMN_NAME,COUNT(1),
        GROUP_CONCAT(DISTINCT INDEX_NAME)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = 'db?' 
    AND INDEX_NAME NOT IN ('PRIMARY')
    AND INDEX_NAME NOT IN (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.`REFERENTIAL_CONSTRAINTS`)
    GROUP BY TABLE_NAME,COLUMN_NAME
    HAVING count(1)>1
    ORDER BY TABLE_NAME ;