mysql查看冗余索引

SELECT 
    table_name, 
    index_name, 
    column_name, 
    seq_in_index, 
    COUNT(*) AS cnt
FROM 
    information_schema.statistics
WHERE 
    table_schema = 'your_database_name' AND 
    table_name = 'your_table_name'
GROUP BY 
    table_name, 
    index_name, 
    column_name, 
    seq_in_index
HAVING 
    cnt > 1;

import mysql.connector

# 连接到MySQL数据库
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database_name"
)

cursor = db.cursor()

# 查询冗余索引
cursor.execute("""
SELECT 
    table_name, 
    index_name
FROM 
    information_schema.statistics
WHERE 
    table_schema = %s AND 
    table_name = %s
GROUP BY 
    table_name, 
    index_name, 
    column_name, 
    seq_in_index
HAVING 
    COUNT(*) > 1;
""", (your_database_name, your_table_name))

冗余索引 = cursor.fetchall()

# 删除冗余索引
for table_name, index_name in 冗余索引:
    cursor.execute(f"DROP INDEX {index_name} ON {table_name};")

db.commit()

cursor.close()
db.close()