Python如何识别 MySQL 中的冗余索引
作者:Bing@DBA 发布时间:2024-01-13 13:44:46
标签:python,mysql,冗余索引
前言
最近在搞标准化巡检平台,通过 MySQL 的元数据分析一些潜在的问题。冗余索引也是一个非常重要的巡检目,表中索引过多,会导致表空间占用较大,索引的数量与表的写入速度与索引数成线性关系(微秒级),如果发现有冗余索引,建议立即审核删除。
PS:之前见过一个客户的数据库上面竟然创建 300 多个索引!?当时的想法是 “他们在玩排列组合呢” 表写入非常慢,严重影响性能和表维护的复杂度。
脚本介绍
表结构
下方是演示的表结构:
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 元数据
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:
# 对比两个索引字段的个数,使用字段小的进行迭代
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)
输出结果:
索引 uqi_name 与索引 idx_name 重复, 索引 idx_name_createtime 与索引 idx_name 重复, 建议删除索引:idx_name
SQL 查询冗余索引
MySQL 5.7 是可以直接通过 sys 元数据库中的视图来查冗余索引的,但是云上 RDS 用户看不到 sys 库。所以才被迫写这个脚本,因为实例太多了,一个一个看不现实。如果你是自建的 MySQL,就不用费那么大劲了,直接使用下面 SQL 来统计。
select * from sys.schema_redundant_indexes;
后记
删除索引属于高危操作,删除前需要多次 check 后再删除。上面是一个 demo 可以包装成函数,使用 pandas 以表为粒度传入数据,就可以嵌入到程序中。有问题欢迎评论沟通。
来源:https://blog.csdn.net/qq_42768234/article/details/127366182
0
投稿
猜你喜欢
- 网页链接:https://www.huya.com/g/4079 这里的主要步骤其实还是和我们之前分析的一样,如下图所示:这里再简单带大家看
- 前言如果你急需一个简单的Web Server,但你又不想去下载并安装那些复杂的HTTP服务程序,比如:Apache,ISS等。那么, Pyt
- 先来看看绘制的动态水球图:没有安装PyEcharts的,先安装PyEcharts:# 安装pyecharts模块,直接安装就是最新的版本pi
- django-admin基本介绍Django 提供了基于 web 的管理工具。Django 自动管理工具是 django.contrib 的
- 系统环境:Centos6.5 64位一. 安装java环境 这里安装的是jdk1.7.60在/usr/local目录下创建java目录,可到
- 本文介绍了asp中 adpbe.stream 的语法,各种参数使用说明,方便大家查阅。更多请看:VBScript 速查手册(语言参考) ch
- 目录1.利用 while True: + sleep() 实现定时任务2.使用 Timeloop 库运行定时任务3.利用 threading
- 介绍lambdaPython用于支持将函数赋值给变量的一个操作符 默认是返回的,所以不用再加return关键字,不然会报错result =
- 在使用Keras搭建验证码识别模型时,需要大量的验证码图片。在这里,使用captcha模块生成验证码图片,验证码图片名称为验证码上显示的字符
- 国际象棋是当今国际上最流行的智力体育运动项目。青年人下棋可以锻炼思维、增强记忆力和培养坚强的意志;中年人下棋可以享受美学;老年下棋可以很好的
- pycharm创建新文件自动添加文件头注释背景我们平时在使用pycharm发现有些大神创建一个新文件的时候会自动在文件头添加一些注释,像是有
- 近段时间看了一些论坛上面关于分页的ASP程序依然有许多的关注者,但里面只有代码,没有详细的解释,对于初学者来说,这样总是得不到真正的掌握,此
- hashlib 模块hashlib 模块的介绍hashlib 模块中拥有很多的加密算法,我们并不需要关心加密算法的实现方法。只需要调用我们需
- <% '#######以下是一个类文件,下面的注解是调用类的方法####################
- def Dijkstra(network,s,d):#迪杰斯特拉算法算s-d的最短路径,并返回该路径和代价 print(&quo
- 适用环境: PHP5.2.x / mysql 5.0.xclass Mysql { priva
- 迭代器迭代器协议对象必须提供一个next方法,执行该方法要么返回迭代中的下一项,要么返回一个异常来终止本次迭代。(只能往前走,不能往后退!)
- QQ邮箱/163邮箱的邮件发送:py文件发送邮件内容相当于一个第三方的客户端,借助于QQ/163邮箱服务器来发送的邮件。主要配置:导入模块—
- ./当前目录 /网站主目录 ../上层目录 ~/网站虚拟目录 如果当前的网站目录为E:\wwwroot 应用程序虚拟目录为E:\wwwroo
- 目录你有过摸鱼时间吗实现思路运行环境界面布局定时刷新剩余时间完整代码你有过摸鱼时间吗在互联网圈子里,常常说996上班制,但是也不乏965的,