python 統計MySQL大於100萬的表

• 2020 年 1 月 12 日
• 筆記

一、需求分析

線上的MySQL伺服器，最近有很多慢查詢。需要統計出行數大於100萬的表，進行統一優化。

需要篩選出符合條件的表，統計到excel中，格式如下：

二、統計表的行數

統計表的行數，有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文件，效果如下：