Python 识别 MySQL 中的冗余索引

文若2年前技术文章753

前言

最近在搞标准化巡检平台,通过 MySQL 的元数据分析一些潜在的问题。冗余索引也是一个非常重要的巡检目,表中索引过多,会导致表空间占用较大,索引的数量与表的写入速度与索引数成线性关系(微秒级),如果发现有冗余索引,建议立即审核删除。

PS:之前见过一个客户的数据库上面竟然创建 100 多个索引!?当时的想法是 “他们在玩排列组合呢” 表写入非常慢,严重影响性能和表维护的复杂度。

脚本介绍

表结构

下方是演示的表结构:

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_CATALOGTABLE_SCHEMATABLE_NAMENON_UNIQUEINDEX_SCHEMAINDEX_NAMESEQ_IN_INDEXCOLUMN_NAMECOLLATIONCARDINALITYSUB_PARTPACKEDNULLABLEINDEX_TYPECOMMENTINDEX_COMMENT
deftest02index_test030test02PRIMARY1idA0NULLNULL
BTREE

deftest02index_test030test02uqi_name1nameA0NULLNULL
BTREE

deftest02index_test031test02idx_name1nameA0NULLNULL
BTREE

deftest02index_test031test02idx_name_createtime1nameA0NULLNULL
BTREE

deftest02index_test031test02idx_name_createtime2create_timeA0NULLNULL
BTREE

脚本通过获得 STATISTICS 表中的索引信息来分析表中是否存在冗余索引,分析粒度为表级别。

DEMO 演示

需要使用 pandas 模块。

import pandas as pd

df_table_level = pd.read_csv('/Users/cooh/Desktop/sqlresult_6162675.csv')

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'])
                   # 两个索引都是唯一索引
                   if choice_index_1['index_name'] != 'PRIMARY':
                       election_dict[choice_index_1['index_name']] += 1
                   elif choice_index_2['index_name'] != 'PRIMARY':
                       election_dict[choice_index_2['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_2['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 以表为粒度传入数据,就可以嵌入到程序中。有问题欢迎评论沟通。


相关文章

linux下xfs文件系统类型/目录扩容

1、查看分区信息[root@172-16-121-112 ~]# fdisk -lDisk /dev/vda: 107.4 GB, 107374182400 bytes, 209715200 sect...

mysql表结构对比工具介绍

mysql表结构对比工具介绍

一、AmpNmp.DatabaseCompare工具1、工具特点:优点:比较两个数据库全部表结构的差异,包括表名、存储引擎、字符集、注释的不同,以及每张表中的字段名、数据类型、字符集、默认值、注释的不...

远程DEBUG HADOOP源码方法

远程DEBUG HADOOP源码方法

1. 安装IDEA2. 下载hadoop源码,必须与集群服务代码版本一致,否则会导致有的类无法找到3. 将源码导入IDEA工程并完成build4. 点击 菜单栏--运行--编辑配置 进行相关debug...

CDH-集群节点下线

CDH-集群节点下线

1、前期准备确认下线节点确认节点组件信息确认下线节点数据存储大小确定剩余节点存储大小如果下线节点数据存储大小大于剩余节点存储大小,则不能进行下线,可能存在数据丢失的情况2、操作首先确认待下线节点中是否...

ES模糊查询(Wildcard Query)导致CPU打满问题

ES模糊查询(Wildcard Query)导致CPU打满问题

一、概述    Wildcard Query是es中实现模糊查询的一种方式,尤其对有SQL经验的人,会常常习惯于它,毕竟这是和SQL里like操作最相似的查询方式,最近一个客户的es集群就在这上面踩了...

kafka启动失败

kafka启动失败

问题现象客户重启kafka服务,页面显示run fail        问题报错问题原因  &nbs...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。