一、需求分析

線上的MySQL服務(wù)器，最近有很多慢查詢。需要統(tǒng)計(jì)出行數(shù)大于100萬的表，進(jìn)行統(tǒng)一優(yōu)化。

成都創(chuàng)新互聯(lián)公司是一家集網(wǎng)站建設(shè),漣水企業(yè)網(wǎng)站建設(shè),漣水品牌網(wǎng)站建設(shè),網(wǎng)站定制,漣水網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,漣水網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競爭力。可充分滿足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

需要篩選出符合條件的表，統(tǒng)計(jì)到excel中，格式如下：

二、統(tǒng)計(jì)表的行數(shù)

統(tǒng)計(jì)表的行數(shù)，有2中方法：

1. 通過查詢mysql的information_schema數(shù)據(jù)庫中INFODB_SYS_TABLESTATS表,它記錄了innodb類 型每個(gè)表大致的數(shù)據(jù)行數(shù)

2. select count(1) from 庫名.表名

下面來分析一下這2種方案。

第一種方案，不是精確記錄的。雖然效率快，但是表會(huì)有遺漏！

第二鐘方案，才是準(zhǔn)確的。雖然慢，但是表不會(huì)遺漏。

備注：

count(1)其實(shí)這個(gè)1，并不是表示第一個(gè)字段，而是表示一個(gè)固定值。

count(1)，其實(shí)就是計(jì)算一共有多少符合條件的行。
1并不是表示第一個(gè)字段，而是表示一個(gè)固定值。
其實(shí)就可以想成表中有這么一個(gè)字段，這個(gè)字段就是固定值1，count(1)，就是計(jì)算一共有多少個(gè)1.

寫入json文件
下面這段代碼，是參考我之前寫的一篇文章：

https://www.cnblogs.com/xiao987334176/p/9901692.html

在此基礎(chǔ)上，做了部分修改，完整代碼如下：

#!/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 端口號(hào),注意：必須是int類型
    connect_timeout = 3  # 超時(shí)時(shí)間
)
cur = conn.cursor()  # 創(chuàng)建游標(biāo)
# 獲取mysql中所有數(shù)據(jù)庫
cur.execute('SHOW DATABASES')
data_all = cur.fetchall()  # 獲取執(zhí)行的返回結(jié)果
# print(data_all)
dic = {}  # 大字典，第一層
for i in data_all:
    if i[0] not in dic:  # 判斷庫名不在dic中時(shí)
        # 排序列表,排除mysql自帶的數(shù)據(jù)庫
        exclude_list = ["sys", "information_schema", "mysql", "performance_schema"]
        if i[0] not in exclude_list:  # 判斷不在列表中時(shí)
            # 寫入第二層數(shù)據(jù)
            dic[i[0]] = {'name': i[0], 'table_list': []}
            conn.select_db(i[0])  # 切換到指定的庫中
            cur.execute('SHOW TABLES')  # 查看庫中所有的表
            ret = cur.fetchall()  # 獲取執(zhí)行結(jié)果
            for j in ret:
                # 查詢表的行數(shù)
                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文件，進(jìn)行寫入，完整代碼如下：

#!/usr/bin/env python3
# coding: utf-8
import xlwt
import json
from collections import OrderedDict
f = xlwt.Workbook()
sheet1 = f.add_sheet('統(tǒng)計(jì)', cell_overwrite_ok=True)
row0 = ["庫名", "表名", "行數(shù)"]
# 寫第一行
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  # 計(jì)數(shù)器
    for i in order_dic:
        # 遍歷所有表
        for j in order_dic[i]["table_list"]:
            # 判斷行數(shù)大于100萬時(shí)
            if j['rows'] > 1000000:
                # 寫入庫名
                sheet1.write(num + 1, 0, i)
                # 寫入表名
                sheet1.write(num + 1, 1, j['tname'])
                # 寫入行數(shù)
                sheet1.write(num + 1, 2, j['rows'])
                num += 1  # 自增1
    f.save('test1.xls')

執(zhí)行程序，打開excel文件，效果如下：

歡迎大家一起來玩好PY，一起交流。QQ群：198447500