专栏首页python3python 统计MySQL大于100万的表

python 统计MySQL大于100万的表

一、需求分析

线上的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-8
import pymysql
import json
conn = 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))

三、写入excel中

直接读取tj.json文件,进行写入,完整代码如下:

#!/usr/bin/env python3
# coding: utf-8
import xlwt
import json
from collections import OrderedDict
f = 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')

执行程序,打开excel文件,效果如下:

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 见过的最垃圾的代码,是怎么样的?

    某网友在 Hacker News 上发起了一个 “你见过的最糟糕的代码是什么? ” 的问题,引起了广泛关注和讨论,评论数已接近600条。

    芋道源码
  • Mysql查询语句使用select.. for update导致的数据库死锁分析

    近期有一个业务需求,多台机器需要同时从Mysql一个表里查询数据并做后续业务逻辑,为了防止多台机器同时拿到一样的数据,每台机器需要在获取时锁住获取数据的数据段,...

    李红
  • 初识 redo log 和 binlog

    InnoDB 存储引擎是以页为单位来管理存储空间的, 我们的增删改查操作本质上都是在访问页面, 如读取一条数据, 会把这个数据所在的页加载到内存中, 而不仅仅是...

    一份执着✘
  • Oracle 11G

    在上面图片出来Execute Configuration scripts之后 切换到root下执行以下脚本

    A2Data
  • 悲观锁、乐观锁,浅析

    悲观锁和乐观锁是并发控制常用的两种技术手段。 并发控制是用来确保 多个事务同时读写DB中同一条数据时不破坏事务的隔离性、统一性以及数据库的统一性。

    许杨淼淼
  • 年底了,DB fighting 阵地的转换

    今年算是 PG 针对其他传统数据库fight比较激烈的一年,也算是开始,后面的戏还长着呢,从气势上看,有些数据库,听闻在今年各种大小的数据库会议上,已经没有了声...

    AustinDatabases
  • JanusGraph数据导入到Gephi的方法

    对于支持Gremlin语法的图数据库,可以通过Gephi中的Graph Streaming插件将输入导入到Gephi中,进行数据可视化展示。

    咻咻ing
  • 系统化服务构建-软件工程分层

    本文主要探讨软件项目开发中的工程,涉及软件分层,业务分离等概念。软件工程通常是说以工程的原理,原则和方法指导软件开发,以解决软件危机。

    needrunning
  • 如何解决mysqli_query() expects at least 2 parameters, 1 given in...line ?

    $userName = addslashes($_POST['userName']);

    用户6860623
  • 工作日、工作小时的一种非常简单的计算方式

    有些业务流程复杂,环节多样。为了看清整个业务的进展,往往需要对各个环节设定预计完成时间开销,然后在用这个是时间去考核实际业务开展的效率。

    普通程序员

扫码关注云+社区

领取腾讯云代金券