Python如何識別 MySQL 中的冗餘索引

CREATE TABLE `index_test03` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `create_time` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uqi_name` (`name`),
  KEY `idx_name` (`name`),
  KEY `idx_name_createtime`(name, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT * from information_schema.STATISTICS  where TABLE_SCHEMA = 'test02' and TABLE_NAME = 'index_test03';

import pandas as pd

df_table_level = pd.read_excel('/Users/cooh/Desktop/STATISTICS.xlsx')

table_indexes = df_table_level['INDEX_NAME'].drop_duplicates().tolist()

_indexes = list()
for index_name in table_indexes:
    index_info = {'index_cols': df_table_level[df_table_level['INDEX_NAME'] == index_name]['COLUMN_NAME'].tolist(),
                  'non_unique': df_table_level[df_table_level['INDEX_NAME'] == index_name]['NON_UNIQUE'].tolist()[0],
                  'index_name': index_name
                  }
    _indexes.append(index_info)

content = ''
election_dict = {i['index_name']: 0 for i in _indexes}

while len(_indexes) > 0:
    choice_index_1 = _indexes.pop(0)

    for choice_index_2 in _indexes:
        # 對比兩個索引欄位的個數，使用欄位小的進行迭代
        min_len = min([len(choice_index_1['index_cols']), len(choice_index_2['index_cols'])])

        # 獲得相似欄位的個資料
        similarity_col = 0
        for i in range(min_len):
            # print(i)
            if choice_index_1['index_cols'][i] == choice_index_2['index_cols'][i]:
                similarity_col += 1

        # 然後進行邏輯判斷
        if similarity_col == 0:
            # print('毫無冗餘')
            pass
        else:
            # 兩個索引的欄位包含內容都相同，說明兩個索引完全相同，接下來就需要從中選擇一個刪除
            if len(choice_index_1['index_cols']) == similarity_col and len(
                    choice_index_2['index_cols']) == similarity_col:
                # 等於 0 表示有唯一約束
                if choice_index_1['non_unique'] == 1:
                    content += '索引 {0} 與索引 {1} 重複, '.format(choice_index_2['index_name'], choice_index_1['index_name'])
                    election_dict[choice_index_1['index_name']] += 1
                elif choice_index_2['non_unique'] == 1:
                    content += '索引 {0} 與索引 {1} 重複, '.format(choice_index_1['index_name'], choice_index_2['index_name'])
                    election_dict[choice_index_2['index_name']] += 1
                else:
                    content += '索引 {0} 與索引 {1} 重複, '.format(choice_index_2['index_name'], choice_index_1['index_name'])
                    election_dict[choice_index_1['index_name']] += 1

            elif len(choice_index_1['index_cols']) == similarity_col and choice_index_1['non_unique'] != 0:
                content += '索引 {0} 與索引 {1} 重複, '.format(choice_index_2['index_name'], choice_index_1['index_name'])
                election_dict[choice_index_1['index_name']] += 1

            elif len(choice_index_2['index_cols']) == similarity_col and choice_index_1['non_unique'] != 0:
                content += '索引 {0} 與索引 {1} 重複, '.format(choice_index_1['index_name'], choice_index_2['index_name'])
                election_dict[choice_index_2['index_name']] += 1

redundancy_indexes = list()
for _k_name, _vote in election_dict.items():
    if _vote > 0:
        redundancy_indexes.append(_k_name)

content += '建議刪除索引：{0}'.format(', '.join(redundancy_indexes))

print(content)

select * from sys.schema_redundant_indexes;