MySQL Check redundant index code

Took over a new requirement check the company all the library table redundant index, save code reuse.

#!/usr/bin/python2.7

#-*-Coding:utf-8-*-

#[email protected]

Import MySQLdb

Import Sys

Import OS

Import Time,datetime

Import logging

Import Mysqlcommon

Path = Sys.path[0]

Fpath = os.path.dirname (path) + "/"

Sys.path.append (Fpath)

From instance import base

From Lib Import mysqllib

From Lib Import maillib

From Lib Import configgetter

Getter = Configgetter. Configuration ("conf/global.cnf")

Luser = Getter.get ("Mysqlha", "user")

LPASSWD = Getter.get ("Mysqlha", "passwd")

email_addr = ' [email protected] '

def mysql_conn (Host,port,db,sql):

db = MySQLdb.connect (Host=host, Port=port, User=luser, PASSWD=LPASSWD, db=db)

cursor = Db.cursor ()

Cursor.execute (SQL)

result = Cursor.fetchall ()

return result

def get_mysql (host,port,db,tname):

#最长的索引数, i.e. the number of cycles

sql = "Select Max (Seq_in_index) from INFORMATION_SCHEMA. STATISTICS where table_schema= '%s ' \

and Table_name= '%s '; "% (Db,tname)

num = Mysql_conn (host,port,db,sql)

num = num[0][0]

Index_na_all=[]

Status = []

If num! = None:

For numb in range (num):

A = Numb+1

b = Numb +2

#查询第一个位置重复的字段名

If a = = 1:

SQL1 = "SELECT column_name from INFORMATION_SCHEMA. STATISTICS where table_schema= '%s ' and table_name= '%s ' \

and Seq_in_index =%s and index_name! = ' PRIMARY ' GROUP by COLUMN_NAME have Count (*) >1; "% (db,tname,a)

Else

SQL1 = "SELECT column_name from INFORMATION_SCHEMA. STATISTICS where table_schema= '%s ' and table_name= '%s ' \

and Seq_in_index =%s and index_name in (%s) group BY COLUMN_NAME have Count (*) >1; "% (DB,TNAME,A,IDX_TW1)

data = Mysql_conn (HOST,PORT,DB,SQL1)

if data = = ():

Break

Alist = []

For data1 in data:

Alist.append (Data1[0])

Colu = ' \ ', \ '. Join (Alist)

Colu = "\ '" + Colu + "\"

#查询第一个位置相同的索引名

If a = = 1:

SQL2 = "Select Index_name from INFORMATION_SCHEMA. STATISTICS where table_schema= '%s ' and table_name= '%s ' \

and Seq_in_index =%s and column_name in (%s); "% (Db,tname,a,colu)

Else

SQL2 = "Select Index_name from INFORMATION_SCHEMA. STATISTICS where table_schema= '%s ' and table_name= '%s ' \

and Seq_in_index =%s and column_name in (%s) and index_name in (%s); "% (DB,TNAME,A,COLU,IDX_TW1)

In_name = []

Index_name = Mysql_conn (HOST,PORT,DB,SQL2)

Index_na = [I[0] for i in index_name]

if Index_na = = []:

Continue

For Iname in Index_na:

In_name.append (Iname)

Inname = ' \ ', \ '. Join (In_name)

Inname = "\ '" + inname + "\"

#第一个位置相同而且有第二个位置的索引名

SQL4 = "Select Index_name from INFORMATION_SCHEMA. STATISTICS where table_schema= '%s ' and table_name= '%s ' \

and Index_name in (%s) and seq_in_index=%s; "% (Db,tname,inname,b)

Idx_two = Mysql_conn (HOST,PORT,DB,SQL4)

IDX_TW = [I[0] for i in Idx_two]

IDX_TW1 = ' \ ', \ '. Join (IDX_TW)

IDX_TW1 = "\ '" + IDX_TW1 + "\"

For Idna in IDX_TW:

If Idna in Index_na:

Index_na.remove (IDNA)

If index_na! = []:

Index_na_all.append (Index_na)

If index_na_all! = []:

Produc = Base.getproductbyport (port)

Prod = produc[' result '].values ()

If prod:

Produc = prod[0]

Else

Produc = ' '

If index_na_all[0]! = []:

Status.append ([port,produc,db,tname,index_na_all[0]])

return Status[0]

else:

Return 123

else:

Return 123

Def call_mysql ():

db = ' backup '

Host = ' 10.20.6.5 '

Port = 3440

sql = "Select Host,port from Policy;"

Allph = Mysql_conn (host,port,db,sql)

showdb = ' MySQL '

Showsql = "Show databases;"

System_lib = ((' Information_schema ',), (' mondmm ',), (' MySQL ',), (' Performance_schema ',))

Statu = []

For I in Allph:

Port = i[1]

Host = I[0]

dbname = Mysql_conn (host,port,showdb,showsql)

Rdbname = Set (dbname)-Set (System_lib)

For Rname in Rdbname:

Namesql = "SELECT table_name from INFORMATION_SCHEMA. TABLES WHERE table_schema = '%s '; "% rname

Rtable_name = Mysql_conn (host,port,rname[0],namesql)

For table_name in Rtable_name:

result = Get_mysql (Host,int (port), rname[0],table_name[0])

If result! = 123:

Statu.append (Result)

SendMail (Statu)

def SendMail (Statu):

title = ' Report (database redundant index _%s) '% (Datetime.datetime.now (). Strftime ('%y-%m-%d '))

Col_name = [' Port ', ' product line ', ' Library name ', ' table name ', ' Index name ']

Maillib.sendhtml (Email_addr,title,statu,col_name)

Call_mysql ()

This article is from the "Wandering Wind" blog, please be sure to keep this source http://774148515.blog.51cto.com/10937712/1858066

MySQL Check redundant index code