python 统计MySQL大于100万的表

发布时间:2019-08-14 22:40:45编辑:admin阅读(2940)

    一、需求分析

    线上的MySQL服务器,最近有很多慢查询。需要统计出行数大于100万的表,进行统一优化。

    需要筛选出符合条件的表,统计到excel中,格式如下:

    库名表名行数
    db1users1234567

     

     

     

    二、统计表的行数

    统计表的行数,有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文件,效果如下:

    1341090-20181126171812003-1216343547.png


关键字