Python如何識別 MySQL 中的冗余索引
更新時間：2022年10月18日 09:01:13 作者：Bing@DBA
冗余索引也是一個非常重要的巡檢目，表中索引過多，會導(dǎo)致表空間占用較大，索引的數(shù)量與表的寫入速度與索引數(shù)成線性關(guān)系(微秒級)，如果發(fā)現(xiàn)有冗余索引，建議立即審核刪除，這篇文章主要介紹了Python 識別 MySQL 中的冗余索引,需要的朋友可以參考下
前言
最近在搞標(biāo)準(zhǔn)化巡檢平臺，通過 MySQL 的元數(shù)據(jù)分析一些潛在的問題。冗余索引也是一個非常重要的巡檢目，表中索引過多，會導(dǎo)致表空間占用較大，索引的數(shù)量與表的寫入速度與索引數(shù)成線性關(guān)系(微秒級)，如果發(fā)現(xiàn)有冗余索引，建議立即審核刪除。
PS：之前見過一個客戶的數(shù)據(jù)庫上面竟然創(chuàng)建 300 多個索引??？當(dāng)時的想法是 “他們在玩排列組合呢” 表寫入非常慢，嚴(yán)重影響性能和表維護(hù)的復(fù)雜度。
腳本介紹
表結(jié)構(gòu)
下方是演示的表結(jié)構(gòu)：
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;
MySQL 元數(shù)據(jù)
MySQL 可以通過 information_schema.STATISTICS 表查詢索引信息：
SELECT * from information_schema.STATISTICS where TABLE_SCHEMA = 'test02' and TABLE_NAME = 'index_test03';
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| def | test02 | index_test03 | 0 | test02 | PRIMARY | 1 | id | A | 0 | NULL | NULL | BTREE | |||
| def | test02 | index_test03 | 0 | test02 | uqi_name | 1 | name | A | 0 | NULL | NULL | BTREE | |||
| def | test02 | index_test03 | 1 | test02 | idx_name | 1 | name | A | 0 | NULL | NULL | BTREE | |||
| def | test02 | index_test03 | 1 | test02 | idx_name_createtime | 1 | name | A | 0 | NULL | NULL | BTREE | |||
| def | test02 | index_test03 | 1 | test02 | idx_name_createtime | 2 | create_time | A | 0 | NULL | NULL | BTREE |
腳本通過獲得 STATISTICS 表中的索引信息來分析表中是否存在冗余索引，分析粒度為表級別。
DEMO 演示
需要使用 pandas 模塊。
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:
# 對比兩個索引字段的個數(shù)，使用字段小的進(jìn)行迭代
min_len = min([len(choice_index_1['index_cols']), len(choice_index_2['index_cols'])])
# 獲得相似字段的個數(shù)據(jù)
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
# 然后進(jìn)行邏輯判斷
if similarity_col == 0:
# print('毫無冗余')
pass
else:
# 兩個索引的字段包含內(nèi)容都相同，說明兩個索引完全相同，接下來就需要從中選擇一個刪除
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} 重復(fù), '.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} 重復(fù), '.format(choice_index_1['index_name'], choice_index_2['index_name'])
election_dict[choice_index_2['index_name']] += 1
else:
content += '索引 {0} 與索引 {1} 重復(fù), '.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} 重復(fù), '.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} 重復(fù), '.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)
輸出結(jié)果：
索引 uqi_name 與索引 idx_name 重復(fù), 索引 idx_name_createtime 與索引 idx_name 重復(fù), 建議刪除索引：idx_name
SQL 查詢?nèi)哂嗨饕?/h2>
MySQL 5.7 是可以直接通過 sys 元數(shù)據(jù)庫中的視圖來查冗余索引的，但是云上 RDS 用戶看不到 sys 庫。所以才被迫寫這個腳本，因?yàn)閷?shí)例太多了，一個一個看不現(xiàn)實(shí)。如果你是自建的 MySQL，就不用費(fèi)那么大勁了，直接使用下面 SQL 來統(tǒng)計(jì)。
select * from sys.schema_redundant_indexes;
后記
刪除索引屬于高危操作，刪除前需要多次 check 后再刪除。上面是一個 demo 可以包裝成函數(shù)，使用 pandas 以表為粒度傳入數(shù)據(jù)，就可以嵌入到程序中。有問題歡迎評論溝通。
到此這篇關(guān)于Python 識別 MySQL 中的冗余索引的文章就介紹到這了,更多相關(guān)MySQL冗余索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
pycharm進(jìn)行Git關(guān)聯(lián)和取消方式
這篇文章主要介紹了pycharm進(jìn)行Git關(guān)聯(lián)和取消方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-06-06
python數(shù)組轉(zhuǎn)換為矩陣的方法實(shí)現(xiàn)
本文主要介紹了python數(shù)組轉(zhuǎn)換為矩陣的方法實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-07-07
python中requests使用代理proxies方法介紹
這篇文章主要介紹了python中requests使用代理proxies方法介紹，具有一定參考價值，需要的朋友可以了解下。2017-10-10
這篇文章主要介紹了python動態(tài)加載變量示例,需要的朋友可以參考下2014-02-02
Python中數(shù)字(Number)數(shù)據(jù)類型常用操作
本文主要介紹了Python中數(shù)字(Number)數(shù)據(jù)類型常用操作，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-02-02
這篇文章主要介紹了Python根據(jù)服務(wù)獲取端口號,文中給大家提到了linux查看端口開啟端口的方法，需要的朋友可以參考下2019-09-09
Python獲取CPU、內(nèi)存使用率以及網(wǎng)絡(luò)使用狀態(tài)代碼
這篇文章主要介紹了Python獲取CPU使用率、內(nèi)存使用率、網(wǎng)絡(luò)使用狀態(tài)的相關(guān)代碼，對此有需要的朋友一起測試下。2018-02-02
Python實(shí)現(xiàn)正整數(shù)分解質(zhì)因數(shù)操作示例
這篇文章主要介紹了Python實(shí)現(xiàn)正整數(shù)分解質(zhì)因數(shù)操作,結(jié)合實(shí)例形式分析了Python循環(huán)與遞歸兩種操作方法實(shí)現(xiàn)分解質(zhì)因數(shù)功能的相關(guān)操作技巧,需要的朋友可以參考下2018-08-08
這篇文章主要介紹了學(xué)好python基本數(shù)據(jù)類型,學(xué)習(xí)python基本數(shù)據(jù)類型我們需要了解基本數(shù)據(jù)類型有數(shù)字int、布爾值bool、字符串str、列表list、元組tuple、字典dict等，其中包括他們的基本用法和其常用的方法，下面來看看文章的具體介紹吧2021-12-12
這篇文章主要介紹了利用Python的Django框架中的ORM建立查詢API,對Managers和QuerySets進(jìn)行了著重介紹,需要的朋友可以參考下2015-04-04