python 统计MySQL大于100万的表,pythonmysql,一、需求分析线上的M
python 统计MySQL大于100万的表,pythonmysql,一、需求分析线上的M
一、需求分析
线上的MySQL服务器,最近有很多慢查询。需要统计出行数大于100万的表,进行统一优化。
需要筛选出符合条件的表,统计到excel中,格式如下:
库名 | 表名 | 行数 |
db1 | users | 1234567 |
二、统计表的行数
统计表的行数,有2中方法:
1. 通过查询mysql的information_schema数据库中INFODB_SYS_TABLESTATS表,它记录了innodb类型每个表大致的数据行数
2. select count(1) from 库名.表名
下面来分析一下这2种方案。
第一种方案,不是精确记录的。虽然效率快,但是表会有遗漏!
第二钟方案,才是准确的。虽然慢,但是表不会遗漏。
备注:
count(1)其实这个1,并不是表示第一个字段,而是表示一个固定值。
count(1),其实就是计算一共有多少符合条件的行。
1并不是表示第一个字段,而是表示一个固定值。
其实就可以想成表中有这么一个字段,这个字段就是固定值1,count(1),就是计算一共有多少个1.
写入json文件
下面这段代码,是参考我之前写的一篇文章:
https://www.cnblogs.com/xiao987334176/p/9901692.html
在此基础上,做了部分修改,完整代码如下:
#!/usr/bin/env python3# coding: utf-8import pymysqlimport jsonconn = pymysql.connect( host="192.168.91.128", # mysql ip地址 user="root", passwd="root", port=3306, # mysql 端口号,注意:必须是int类型 connect_timeout = 3 # 超时时间)cur = conn.cursor() # 创建游标# 获取mysql中所有数据库cur.execute(‘SHOW DATABASES‘)data_all = cur.fetchall() # 获取执行的返回结果# print(data_all)dic = {} # 大字典,第一层for i in data_all: if i[0] not in dic: # 判断库名不在dic中时 # 排序列表,排除mysql自带的数据库 exclude_list = ["sys", "information_schema", "mysql", "performance_schema"] if i[0] not in exclude_list: # 判断不在列表中时 # 写入第二层数据 dic[i[0]] = {‘name‘: i[0], ‘table_list‘: []} conn.select_db(i[0]) # 切换到指定的库中 cur.execute(‘SHOW TABLES‘) # 查看库中所有的表 ret = cur.fetchall() # 获取执行结果 for j in ret: # 查询表的行数 cur.execute(‘select count(1) from `%s`;‘% j[0]) ret = cur.fetchall() # print(ret) for k in ret: print({‘tname‘: j[0], ‘rows‘: k[0]}) dic[i[0]][‘table_list‘].append({‘tname‘: j[0], ‘rows‘: k[0]})with open(‘tj.json‘,‘w‘,encoding=‘utf-8‘) as f: f.write(json.dumps(dic))View Code
三、写入excel中
直接读取tj.json文件,进行写入,完整代码如下:
#!/usr/bin/env python3# coding: utf-8import xlwtimport jsonfrom collections import OrderedDictf = xlwt.Workbook()sheet1 = f.add_sheet(‘统计‘, cell_overwrite_ok=True)row0 = ["库名", "表名", "行数"]# 写第一行for i in range(0, len(row0)): sheet1.write(0, i, row0[i])# 加载json文件with open("tj.json", ‘r‘) as load_f: load_dict = json.load(load_f) # 反序列化文件 order_dic = OrderedDict() # 有序字典 for key in sorted(load_dict): # 先对普通字典key做排序 order_dic[key] = load_dict[key] # 再写入key num = 0 # 计数器 for i in order_dic: # 遍历所有表 for j in order_dic[i]["table_list"]: # 判断行数大于100万时 if j[‘rows‘] > 1000000: # 写入库名 sheet1.write(num + 1, 0, i) # 写入表名 sheet1.write(num + 1, 1, j[‘tname‘]) # 写入行数 sheet1.write(num + 1, 2, j[‘rows‘]) num += 1 # 自增1 f.save(‘test1.xls‘)View Code
执行程序,打开excel文件,效果如下:
python 统计MySQL大于100万的表
相关内容
- 41个有关Python的小技巧【转】,41python小技巧,内容来自
- python内置类型:列表,包括 list 和 tuple,pythontuple,列表
- python-微博模拟登陆,,微博的的模拟登陆是比
- python基础之列表拷贝,python列表拷贝,我先定义一个列表
- python基础 split 和 join函数比较,pythonjoin,split:拆分字符
- Python基础:关于__name__,python基础__name_,__name__ 的
- Python基础 - 命名规范,python基础命名规范,以双下划线开
- python基础 之 装饰器详解,python详解,紧跟装饰器声明的
- python基础之列表解析,python基础解析,python列表解析
- python3使用PyMysql连接mysql数据库,python3pymysql,1.pymysql安
评论关闭